#REF error

[anurag.d]

Hi,
Every 2 months i have to prepare a financial forecast.
This forecast is to be presented in various ways.
This activity becomes tedious since the same info is to be presented i
a number of ways.
So i have linked the forecast sheet with another "Master Input Sheet".
This "master input sheet" enables me to present the forecast data in
variety of ways with ease.

The "Master Input Sheet" and "forecast sheet" are kept in the sam
file.

However,the forecast undergoes changes every 2 months.

I tried deleting the previous months forecast sheet and inserting th
new forecast sheet.
But the "Master Input sheet" throws up the #REF error.

Linking the "Master Input sheet" again is a tedious process.

Can I retain the links in the "Master Input Sheet" and keep on addin
new forecast sheets?

Thanks

 

[Arien]

When your data is in sheet2 cell B4.
and you want this data in sheet1 you can make this link dynamic.

Sheet 1=
in cell A1 you type: sheet2
in cell A2 you type: =INDIRECT(A1&"!B4")

By changing cell A1 you can change the origin of the data.

regards,
Arie

 

[anurag.d]

Dear Arien,
Many Thanx.

However their is a slight problem.
As u said i have tried out the solution.
However,my sheet 1 has been structured in such a format that when th
cells are dragged horizontally the links are automatically created fo
the corresponding cells in sheet 2.

To make it more clear:
By dragging cell A2 only cell A1 changes to A2,however cell B4 does no
change to B5.
I have put $sign to cell A1 i.e $A$1.
But can you suggest a way to change cell B4 to B5 and so on.


Thanks,
Anura

 

[Arien]

Dear Anurag,

You can use the same formula:

in sheet1 you type:
Cell A1: Sheet2
Cell B1: B4 or B5 or whatever link you like
Cell B2: =INDIRECT($A$1&"!"&B1)

you can drag formulas in column B horizontaly and your links chang
verticaly.
I hope I understood you correct. Otherwise please let me know.

regards,
Arie

 

[anurag.d]

Dear Arien,
U hav understood my problem correctly.
But i cant type in cell "B1:B4 or B5" since that would be a lot o
typing.I have got multiple data in numerous rows and columns.
For each row i will hav to type the reference cell in Sheet2

Cant the B4 cell change automatically on dragging?

Many Thanks,
Nilay

 

[Arien]

Dear Nilay,

You can amend the same formula again

in Sheet 1:
Cell A1= sheet2
Cell B3 = B
Cell C3 = C or whatever link you like
Cell A4 = 4
Cell A5 = 11 or whatever link you like

Row 3 and column A can both be dragged further

Cell B4 = =INDIRECT($A$1&"!"&B$3&$A4)
This formula can be copied to B4..C5 and further.

Hope this helps you

regards,
Arie

 

[anurag.d]

Thanks!
But still i hav to type each and every reference in the cells in shee
1

 

[Arien]

Dear Nilay,

OK, I understand.
One other way would be to use the edit!replace command from the toolba
to replace the sheetname in all links.

regards,
Arie

 

[anurag.d]

Dear Arien,
Thanks a lot.U hav made my life easier.This last option is very easy t
follow