Page 1 of 2 12 LastLast
Results 1 to 15 of 29

Thread: Match (2003)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Match (2003)

    Facts: Rows 1-12 of Column A of "Budget.XLS" file contains months of the year in the MMMM/YYYY format. In addition, I have 12 other workbooks named January.XLS, February.XLS, etc., through December.XLS. I would like to insert the cell contents from cell A1 from each of the other twelve workbooks into the corresponding cell location in column B of Budget.XLS. Stated another way, if the month in column A of Budget.XLS matches the filename of one of the other 12 files, I would like to insert the data from cell A1 of the monthly workbook into the corresponding cell in column B of Budget.XLS where the name of the file matches the month in column A of Budget.XLS.
    Any ideas?
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match (2003)

    You can use the INDIRECT function to refer to a range whose address is provided as a string that can be assembled from several parts, such as the month name in a cell, but it only works with cells in another workbook if that workbook is open. The formula would look like this:
    <code>
    =INDIRECT("'C:Test["&TEXT(A1,"mmmm")&".xls]Sheet1'!$A$1")
    </code>
    C:Test is the path of the workbooks. I assumed that the sheet you need is named Sheet1 in all workbooks. This formula will retrieve cell A1 from the monthly workbook if it is open, but display #REF if that workbook is closed.

    You can download and install the free add-in Morefunc for Excel. It contains a function INDIRECT.EXT that works like INDIRECT but that handles closed workbooks. The formula would become
    <code>
    =INDIRECT.EXT("'C:Test["&TEXT(A1,"mmmm")&".xls]Sheet1'!$A$1")</code>

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match (2003)

    Thanks, Hans, but unfortunately it doesnt load properly. Whenever I try and embed the Morefunc program into my excel spreadsheet, it gives me a Runtime Erroe 1004: Method 'Select' of Object 'Sheets' failed. When I went toi the website to inquire, it is in french, and I was unable to post. Any other ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match (2003)

    Embed MoreFunc in a spreadsheet? What do you mean by that? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match (2003)

    That's the drop-down it gives me when I go to Tools/Morefunc. In addition, when I go to tools/add-ins, it shows morefunc as being there, but when I try and insert a morefunc function, the function drop-down doesn't show morefunc as being present...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match (2003)

    I wouldn't include Morefunc in the workbook now (it increases the size of the workbook), but test first.
    If Morefunc is listed in Tools | Add-Ins..., you should have a new category in the Insert Function wizard, at the bottom of the Category dropdown.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match (2003)

    Nope, not there!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match (2003)

    Something must have gone wrong when you installed it. I'd try uninstalling MoreFunc (in the Add/Remove Programs control panel), then reinstalling it.

    Or if you'd rather avoid using it, import the twelve monthly sheets into the summary workbook and name the sheets January, February etc. You can then use the built-in INDIRECT function.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match (2003)

    Hans,
    I'm not sure I made myself clear: what I want to accomplish is to insert the contents of a cell from a workbook called, say, "January.XLS", into a column, B1:B12, of workbook "Budget.XLS", in the row where the contents of cell A1:A12 of that workbook is the same as the name of the workbook from which I am pulling the cell data, i. e., "January.XLS". For example, if I have the names January-December listed in cells A1:A12 in the workbook "Budget.XLS", I would like the cell contents from cell A1 of workbook "January.XLS" to be inserted in cell B1 in "Budget.XLS" (i. e., in the cell in column B where the name in column A is the same as the name of the workbook from which I am pulling the cell data), the cell contents of cell A1 of workbook "February.XLS" inserted in cell B2 in "Budget.XLS", the cell contents of cell A1 of workbook "March.XLS" inserted in cell B3 in "Budget.XLS", etc. Does this help?
    Thanks again.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match (2003)

    That's what I understood your question to mean, so the previous replies still apply.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match (2003)

    Installed and uninstalled 3 times-still doesn't work! Were you able to download it?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match (2003)

    I've had MoreFunc for years, and it works without problems.

    If you can't get it to work, I'd use my suggestion from <post:=679,761>post 679,761</post:> - import the sheets into the Budget workbook.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match (2003)

    Thanks.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match (2003)

    Hi Hans,
    Well, I finally got the MoreFunc program loaded and running. One additional question for you: what if I wanted instead of picking up the one piece of data from the remote file (i. e., "January.XLS"), I wanted to find the maximum value in a range that spans A1180 in that file?
    Thanks.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match (2003)

    Never mind-I figured it out...thanks for all of your help!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •