Filename in a cell to use as reference for formulas

[cybersandokan]

I really need help with this one,
I have two books, the first one references the second one in many cells. The second one can change its name, so I should change all the formulas in my
first book to reference the second one correctly. As this happens every month. I thought if I could store in one cell or wherever the name of the second book and reference all the formulas to that cell, in this case when the book name changes I only have to change one cell. I tried to do this but it doesn't work. Any ideas??? when I wrote the formula that references the cell with the book name, it tries to open that book.

Is is possible to do this in other ways. It is impossible for me to change all the cell references every month as they are thouthands.

tHE ONLY RESTRICION IS NOT TO USE MACROS.

Help is appreciated,
Thanks,
Cybersandokan

[Bryan Hessey]

I really need help with this one,
I have two books, the first one references the second one in many cells. The second one can change its name, so I should change all the formulas in my
first book to reference the second one correctly. As this happens every month. I thought if I could store in one cell or wherever the name of the second book and reference all the formulas to that cell, in this case when the book name changes I only have to change one cell. I tried to do this but it doesn't work. Any ideas??? when I wrote the formula that references the cell with the book name, it tries to open that book.

Is is possible to do this in other ways. It is impossible for me to change all the cell references every month as they are thouthands.

Help is appreciated,
Thanks,
Cybersandokan

Hi,

you need to checkout the Indirect function, such that for

=Sheet1!A1

you can store the sheet number in (say) Z1 and use

=Indirect("Sheet"&Z1&"!A1")

to vary the sheet number. There are many variation of 'text' and Cell contents etc that can be used to form a formula reference.

hth
---