updating of cells automaticly based on first cell

hello!

i have a simple problem... thats..

in sheet1 I have data from cell A1 to A1000 and i can link that data to Sheet2 cell A1 to A1000. I do that now by entering =Sheet1!A1 and drag that untill cell A1000. but i wanted to change only the first cell of Sheet2 and hoped rest of the cell update based on the first cell A1... so, if i change the first cell of the Sheet2 to =Sheet1!A9 rest of the cell changes too like Sheet1!A10, Sheet1!A11, Sheet1!A12, Sheet1!A13...

I hope i am able to clearly present my question with my limited language skill :-)

thank you for your kind help

Change A1, then drag copy across and down.

If the dataset is huge, a formula solution using OFFSET might not be the best idea - maybe you need a VBA solution.

Ali, he is trying to get a formula in Sheet2 A2 to A1001 based on the reference i.e. Sheet1!A1 or Sheet1!A2 etc. He need a formula in A2 based on the formula in A1. If he put the reference/formula like Sheet1!A9 in Sheet2!A1, the next cell (A2) should generate Sheet1!A10. We need a formula to sum the last part of the formula. Do you have anything in your mind ?

Perhaps look at using INDIRECT()?

I tried to make it, couldn't complete.
Assume the Sheet2!A1 has the formula =Sheet1A4

I used the below formula.
=INDIRECT("Sheet1!A"&"4"+COLUMN(A:A))

But we need to get a formula for "4"- how to extract 4 from Sheet1!A4?

Originally Posted by Shareez Saleem

Ali, he is trying to get a formula in Sheet2 A2 to A1001 based on the reference i.e. Sheet1!A1 or Sheet1!A2 etc.

I understand perfectly what he wants. However, in post #3, he mentions "huge data", which is why I suggested that a VBA approach might be preferable to a formulaic one. Formulae would need to include either INDIRECT or OFFSET, both of which can slow things down on large datasets. If he wants VBA, I can move the thread for him.

=INDIRECT("Sheet1!A"&MID(FORMULATEXT($A$1),SEARCH("!A",FORMULATEXT($A$1))+2,99)+ROWS($A$1:A1))
Enter this formula in Sheet2 Cell A2 and drag down.

Please close this thread.