Cell reference based on variable text string

  • Thread starter Thread starter hl
  • Start date Start date
H

hl

I am trying to make the following formula change based on values
(text) in other cells:
='M:\RAPP\[ATL08.xls]200803'!$G99

In Cell A1 I have ATL, and in cell A2 I have 200803. When I change in
cell A1 to TOL and cell A2 to 200804, I want the formula to be:
='M:\RAPP\[TOL08.xls]200804'!$G99

Tried: ='M:\RAPP\[&A1&08.xls]&A2&'!$G99 but does not work. Also can
not get Indirect to work, and even if I did, would like to find
something that does not require the external workbook to be open.

Anyone have ideas?

Brgds,
Harald
 
The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

I am trying to make the following formula change based on values
(text) in other cells:
='M:\RAPP\[ATL08.xls]200803'!$G99

In Cell A1 I have ATL, and in cell A2 I have 200803. When I change in
cell A1 to TOL and cell A2 to 200804, I want the formula to be:
='M:\RAPP\[TOL08.xls]200804'!$G99

Tried: ='M:\RAPP\[&A1&08.xls]&A2&'!$G99 but does not work. Also can
not get Indirect to work, and even if I did, would like to find
something that does not require the external workbook to be open.

Anyone have ideas?

Brgds,
Harald
Click to expand...
 
Back
Top