Reference in worksheet file name to a cell value

Please take a look at this formula:

=('C:\DATA-HOME\Investing\Stocks - Prospects\[IBM.xls]Data'!$G$37)

As you can see, it references a value in another worksheet, IBM.xls.

I would like the reference to the worksheet to depend on the value of a cell and not be the hard file name.

For example, something like this:

=('C:\DATA-HOME\Investing\Stocks - Prospects\[*A1*.xls]Data'!$G$37)

Where the value of cell A1 is IBM (note the asterisks are my notation to indicate that what is between them is a cell reference).

Thanks in advance for your thoughts.

Only way to do that is with the INDIRECT function.

=INDIRECT("'C:\DATA-HOME\Investing\Stocks - Prospects\["&A1&".xls]Data'!$G$37")

One point to make though, INDIRECT can only get data from open workbooks.

Is there a way to get around the open workbook restriction? I am picking those values from a bunch of worksheets (one for each company stock symbol), so much prefer to pick the values without opening the workbook.

See if this works for you.

Enter the formula in this format, then, assuming that you have a long list of company names in column A, fill down as needed.

="='C:\DATA-HOME\Investing\Stocks - Prospects\["&A1&".xls]Data'!$G$37"

IMPORTANT BIT! - Only the cells containing this formula should be selected for the next part, if you have any other cells selected that contain different formula, then they will be lost!

After filling down, with the all of, and only the cells containing the formula selected, press Ctrl c, followed by Ctrl Alt v, v, enter (copy and pastespecial values), effectively converting each formula back to the same format as your original.

At this point, the formula will appear in the cell as a text string rather than an actual formula, to fix this, press Ctrl h (find and replace), enter = into both boxes, then hit 'replace all'.

Now your sheet will be set up as if you had edited every formula by hand, but it's less labour intensive.

Will that work, or do you need something that is more flexible?

I did a test on a new worksheet.

I put IBM in cell A1.

Then I cut and pasted the formula

="='C:\DATA-HOME\Investing\Stocks - Prospects\["&A1&".xls]Data'!$G$37"

to a couple of other cells. Those cells display

='C:\DATA-HOME\Investing\Stocks - Prospects\["&A1&".xls]Data'!$G$37"

Originally Posted by leobueno

Then I cut and pasted the formula

And that is why it didn't work.

My suggestion was copy and pastespecial.

Correction:

The cells display this:
='C:\DATA-HOME\Investing\Stocks - Prospects\[IBM.xls]Data'!$G$37

The value of A1 (ie, IBM) is substituted in the file name. Obviously what I want is the value of cell G37 in IBM.xls.

Originally Posted by leobueno

The cells display this:
='C:\DATA-HOME\Investing\Stocks - Prospects\[IBM.xls]Data'!$G$37

The value of A1 (ie, IBM) is substituted in the file name.

I did say that would happen, and also what to do next to make that work as a formula. Obviously you didn't read it properly.

Originally Posted by jason.b75

At this point, the formula will appear in the cell as a text string rather than an actual formula, to fix this, press Ctrl h (find and replace), enter = into both boxes, then hit 'replace all'.

Thanks again for following up. I tried what you suggested and it did not work. Please see attached file.

Originally Posted by leobueno

Thanks again for following up. I tried what you suggested and it did not work. Please see attached file.

I tried the steps I suggested with your file and it works fine for me. At the moment, my best guess would be that you are using Paste, not PasteSpecial as suggested.

Originally Posted by Tony Valko

It has a function called INDIRECT.EXT which works much like the built-in INDIRECT function except that the referenced file(s) do not need to be open.

It would be more accurate to say that it doesn't require you to open them, the function opens any closed workbooks referred to in a hidden instance of excel. Not really practical if you're looking at a number of closed workbooks as indicated in post #4, which is why I didn't suggest it in post #5.

I tried using Paste Special; radio button window pops up; I pick text. Nothing happens.

Please post your updated version of the sample file I posted. That way I should see a different result.

I have browser issues that make posting files a near impossible task at the present time.

In my version of the file the cells show #REF! because the file path that the formula refers to does not exist on my computer.

Before continuing with this, as it looks like I need to fully rewrite post #5 with more detail, see Tony Valko's suggestion in post #11, my reason for not suggesting it in post #12, and Tony Valko's follow up in post #13 to establish whether or not it might be practical.

I will appreciate receiving the updated test file by email.

We will all appreciate a little common courtesy, you have received another suggestion which you have not acknowledged in any way. There could be several reasons for that, all of which are discouraging to anyone who might be willing to offer you assistance.

Sending you the file would be pointless, it will only show you that it works. It will already have the end result, so will be of no use in helping you set up your real sheet.

I tried using Paste Special; radio button window pops up; I pick text. Nothing happens.

The fact that you are seeing the text option tells me that you haven't followed the steps in my original suggestion correctly. There are 2 different boxes that pop up when using pastespecial, which one you see depends on the format of the copied data in your clipboard. If you had followed the process correctly then you would be seeing the other version of the box, which has no radio button for text.

This is my last attempt to explain this. Follow all steps in order. Do not attempt to skip any steps. Any deviation will cause the process to fail.

Step 1: Enter a list of company names in A1:A10
Step 2: Copy this formula.

Formula:

Please Login or Register  to view this content.

Step 3: Enter (paste) the formula into B1
Step 4: Autofill the formula to the range B1:B10.
Step 5: Copy the range B1:B10.
Step 6: Pastespecial, set the radio button to 'Values'
Step 7: Use 'Find and Replace' to replace = with =

If it didn't work return to step 1.