Cell function not working w/o recalc

[Guest]

I've tried a few times to set up a sheet which can use the INDIRECT function
with the CELL function to automatically know the sheet name of the tab the
formula is in (this also requires MID and FIND). The formula:

=INDIRECT("'[prelim
reonciliation7-1-05.xls]"&MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)&"'!"&CHAR(COLUMN(B$1)+64)&ROW())

returns the correct answer that I'm looking for. However, when I have the
same formula in multiple sheets, the formula for every sheet will list the
results of the most recently calculated sheet. If I'm on sheet 1 and hit
calculate, the correct answers appear. Next I click on a 2nd sheet with
similar formulas and they show the same answers (which on the 2nd sheet are
incorrect). I then hit recalculate again and the correct answers for the 2nd
sheet show up.

It's my guess that this can't be fixed outside of a new version of Excel,
but any help would be appreciated.

tia,
Dave

PS I'm using Excel 2003 SP3

Ultimately, I'd like to have a summary tab in an additional tab that reads
results from each individual tab with the formula, but this gives the same
answers for everybody.

 

[Guest]

Try including a reference within your CELL functions.

CELL("filename",A1)

The contents of cell A1 don't matter. The presence of some sort of
reference to any cell on that sheet will keep the formula on that sheet
rather than whichever sheet was last calculated.

HTH,
Elkar

 

[Guest]

Perfect...thanks!

Try including a reference within your CELL functions.

CELL("filename",A1)

The contents of cell A1 don't matter. The presence of some sort of
reference to any cell on that sheet will keep the formula on that sheet
rather than whichever sheet was last calculated.

HTH,
Elkar

I've tried a few times to set up a sheet which can use the INDIRECT function
with the CELL function to automatically know the sheet name of the tab the
formula is in (this also requires MID and FIND). The formula:

=INDIRECT("'[prelim
reonciliation7-1-05.xls]"&MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)&"'!"&CHAR(COLUMN(B$1)+64)&ROW())

returns the correct answer that I'm looking for. However, when I have the
same formula in multiple sheets, the formula for every sheet will list the
results of the most recently calculated sheet. If I'm on sheet 1 and hit
calculate, the correct answers appear. Next I click on a 2nd sheet with
similar formulas and they show the same answers (which on the 2nd sheet are
incorrect). I then hit recalculate again and the correct answers for the 2nd
sheet show up.

It's my guess that this can't be fixed outside of a new version of Excel,
but any help would be appreciated.

tia,
Dave

PS I'm using Excel 2003 SP3

Ultimately, I'd like to have a summary tab in an additional tab that reads
results from each individual tab with the formula, but this gives the same
answers for everybody.