Remove hidden quote from cell reference in formula

[martinrrrr]

I have a folder with 12 files named Jan.xlsx, Feb.xlsx etc. Each file has a worksheet named Sheet1.

In Test.xlsx file I have a formula in cell B1 that reference's one of the files mentioned above. The formula is:
='C:\temp\[Aug.xlsx]Sheet1'!A1.
This works fine and returns the value in cell A1 of the referenced file.

I want to change the formula in Test.xlsx to reference a word in cell C1 in Test.xlsx. C1 will have the word Jan or Feb Mar etc typed in it. So my formula in Test.xlsx cell B1 will now look like:
="'C:\temp\["&C1&".xlsx]Sheet1'!$B$1"

The problem is, Excel evaluates the word in cell C1 and adds quotes to the formula. When I type this formula into cell B1, the displayed result is:
'C:\temp\[Aug.xlsx]Sheet1'!$B$1, not a number from the referenced file. If you click inside of the formula, highlight just C1 and hit F9, you see that Excel is actually adding quotes around word the Aug, so Excel thinks that the formula is really:
='C:\temp\["Aug".xlsx]Sheet1'!$B$1.

Is there a way to prevent Excel from adding the quotes around the word in the cell reference?

Thanks for any help

 

[yky]

Excel thinks it is a string, not a formula, of "='C:\temp\[Aug.xlsx]Sheet1'!A1".

Try this simple formula:

="A" & 1

and you'll get the string "A1" in the cell, not the value of A1.

In short, you cannot use a formula to enter a formula. For that, you'll need VBA.

 

[yky]

Oops! Wrong example. Should be "="=A" & 1" and you'll get "=A1" in the cell, not the value of A1.

 

[martinrrrr]

In short, you cannot use a formula to enter a formula. For that, you'll need VBA.

That's what I was afraid of, it can not be done w/o VBA code. Code-wise I know how to do it, I was just trying to simplify the process for a Team Member that is challenged by using macros.

Thanks for the help though.

 

[Fluff]

You can use
=INDIRECT("'C:\temp\["&C1&".xlsx]Sheet1'!$B$1")
BUT it doesn't work on closed workbooks.

 

[martinrrrr]

You can use
=INDIRECT("'C:\temp\["&C1&".xlsx]Sheet1'!$B$1")
BUT it doesn't work on closed workbooks.

I had tried that and noticed that it did work only when the file was open. However, the files will always be closed.

But at least I now know that I can not use a formula to enter a formula w/o VBA.