INDIRECT HELP!

  • Thread starter Thread starter KENNY
  • Start date Start date
K

KENNY

I have a workbook that makes an external reference
(DATA.xls) to different worksheets. I would like to be
able to have the worksheet referenced change based on the
contents of a single sell (Sheet 1, 2, 3, etc.).

Any suggestions?

TIA!



'[DATA.XLS]Sheet1'!O15
 
Hi
if your other workbook is open try
=INDIRECT("'[DATA.XLS]Sheet" & A1 & "'!O15")

Note: works only, if data.xls is open!
 
Success! One last part: any suggestions on how I can drag
the formula to change the cell reference? As I understand
it, with this formula I would have to manually change each
cell, for example,
=INDIRECT("[DATA.XLS]"&$A$1&"!O15")
=INDIRECT("[DATA.XLS]"&$A$1&"!O16")
=INDIRECT("[DATA.XLS]"&$A$1&"!O17")


-----Original Message-----
=INDIRECT("[DATA.XLS]"&A1&"!O15")

where A1 contains the sheet name.

--

Vasant



I have a workbook that makes an external reference
(DATA.xls) to different worksheets. I would like to be
able to have the worksheet referenced change based on the
contents of a single sell (Sheet 1, 2, 3, etc.).

Any suggestions?

TIA!



'[DATA.XLS]Sheet1'!O15
Click to expand...


.
Click to expand...
 
Hi
if your formula starts in row 1 try
=INDIRECT("[DATA.XLS]"&$A$1&"!O" & ROW()+14)
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany

Success! One last part: any suggestions on how I can drag
the formula to change the cell reference? As I understand
it, with this formula I would have to manually change each
cell, for example,
=INDIRECT("[DATA.XLS]"&$A$1&"!O15")
=INDIRECT("[DATA.XLS]"&$A$1&"!O16")
=INDIRECT("[DATA.XLS]"&$A$1&"!O17")


-----Original Message-----
=INDIRECT("[DATA.XLS]"&A1&"!O15")

where A1 contains the sheet name.

--

Vasant



KENNY said:
I have a workbook that makes an external reference
(DATA.xls) to different worksheets. I would like to be
able to have the worksheet referenced change based on the
contents of a single sell (Sheet 1, 2, 3, etc.).

Any suggestions?

TIA!



'[DATA.XLS]Sheet1'!O15
Click to expand...


.
Click to expand...
Click to expand...
 
Success! One last part: any suggestions on how I can drag
the formula to change the cell reference? As I understand
it, with this formula I would have to manually change each
cell, for example,
=INDIRECT("[DATA.XLS]"&$A$1&"!O15")
Click to expand...
Click to expand...
...

Make this formula

=INDIRECT("'[DATA.XLS]"&$A$1&"'!"&CELL("Address",O15))

fill this down or right wherever you want.
 
Back
Top