Using value from an existing cell inside a formula

Question

I am using the "Camera" feature in excel 2010.

My goal is to have a cell which would have a date that I can manually enter and just below it the formula would get the updated value which represents the name of the sheet inside another excel file and shows me the updated screenshot.

For example: Cell A1 has: 12.25

Just below it, I have: ='C:\My_Excel_Files\[excelDataFile.xlsx]12.25'!$A$1:$D$20

So if I were to change the value of Cell A1 to 12.26 the formula would pick that up automatically.

I tried using the indirect function like this: ='C:\My_Excel_Files\[excelDataFile.xlsx]INDIRECT(A1)'!$A$1:$D$20

but it didn't work.

Answer 1

As mentioned on this forum and this website, you can use the free MOREFUNC.XLL add-in. It provides the INDIRECT.EXT function that will do exactly what you're requesting. The formula you need in your case would be along the lines of:

=INDIRECT.EXT("'C:\My_Excel_Files\[excelDataFile.xlsx]" & A1 & "'!$A$1:$D$20")

Where you concatinate the value of A1 in the middle of the string.

Note: the links in those sites seem to not always work, you can either google for "MOREFUNC download" or try one of these links:

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html
http://www.freewarefiles.com/Morefunc_program_14922.html