linking to external file based on cell value

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hello folks,

I am wondering if it is possible to set up a link within
excel that references an external file using a cell value
in the main spreadsheet.

For example:

this is the current setup
=[expense_allocation.xls]Jan04!$G$5

instead of the Jan04 in the link, I would like a cell
reference

I tried this but it does not seem to work:

="[expense_allocation.xls]" & A1 & "!$G$5"

where A1 = Jan04

Any ideas would be appreciated.

Thanks,

Jason
 
Hi Jason,
Instead of typing it in, open up both files and select each cell as you're
creating the formula to make sure all the references are valid as it seems
at first look that the ! and " marks in your formula are incorrect. It will
probably also make a reference to the name of the sheet that A1 is on.

=[expense_allocation.xls]" & A1 & "!$G$5

should probably be something like....
=[expense_allocation.xls]Sheet1! A1 & $G$5

Rob
 
Hi Jason
you can use INDIRECT for this
=INDIRECT("'[expense_allocation.xls]" & A1 & "'!$G$5")
note the multiple apostrophes: " ' [ and " ' !

This will work only, if the workbook expense_allocation.xls is open. If
it is closed see
http://tinyurl.com/2c62u
for 4 alternative solutions

Frank
 
Back
Top