Linkage

[SGH]

OK here goes...

Spreadsheet 'A' is a summary of & is linked to Spreadsheet 'B'.
A' has columns labelled 561,562 etc which correspond to tabs in 'B'.
In 'A' when I copy formula across I have to use the Replace function to make sure its linking to the right tab in 'B'
Is there an easy way of incorporating my column labels in 'A' into the formula, which would mean I wouldn't have to use the Replace function ?

 

[Andrew Poulsom]

You can use the INDIRECT function but both workbooks must be open. Can you post one of your formulas?

 

[SGH]

This is the formula in spreadsheet 'A'

='[A_OHSUM.AS.DEPTS.xls]557'!$C$221

I want to replace the 557 bit with the column number on the spreadsheet I'm in

 

[fairwinds]

Hi,

Try:

=INDIRECT("'[A_OHSUM.AS.DEPTS.xls]"&A1&"'!$C$221")

With 557 in cell A1

 

[SGH]

Yup that works,

Thanks guys

 

[SGH]

Being picky now !

Is there a way of this working with A_OHSUM.AS.DEPTS.xls closed ?

Thanks

 

[PaddyD]

use indirect.ext() instead - you'll need the morefunc addin though.

 

[fairwinds]

Not with a standard formula.

Maybe something like this...

http://j-walk.com/ss/excel/tips/tip82.htm

 

[SGH]

Dowloaded Morefunc addin & used Indirect.Ext & this works.

Thanks again