reference a cell then reference the next row and overwrite the new cell reference

[alleyb]

I have a database in excel in sheet1. in another sheet2 from the active cell I reference a cell in the database in sheet1 which happens to be a date eg . =Sheet1!A7 and this will display 22-Jan
I want to remain in sheet2 A1 and have it access and essentially overwrite =Sheet1!A8 and therefore give the result 23-Jan
I have searched everywhere but the offset command seems to refer to the active cell moving rather than the referenced cell and I am totalyl stuck
TIA

[teylyn]

I'm not quite sure I understand.

you have a formula in Sheet2!A1 that is

=Sheet1!A7

so far, I'm with you. Then, what happens? You want the formula to change to

=Sheet1!A8

What should trigger this change?

[alleyb]

Up till now I manually change the cell ref to sheet1 a8 but the reality is I have at least 12 other cells in sheet2 that reference sheet 1 and it is a case that all of them need to increment the same column but one line lower.
The trigger is a daily event but as I am dealing with a spreadsheet/database that is downloaded from my website on a global basis I dont want to set a timer event to enact the change but have it run when I say

[teylyn]

OK, I got you.

unless you want to resort to a macro to change the formulae in the cells, you could use the INDIRECT() function for your cell. You'll need a helper cell where you specify the row number that you are interested in. Let's say you want to look at row 7 in Sheet1. Grab a cell and put a 7 in it. For the sake of this exercise, let's say Sheet2!X1 is the cell where you enter the 7. Then change your formula in Sheet2!A1 to be

=indirect("Sheet1!A"&X1)

As soon as you change the number in cell X1, the reference in A1 will change to the row you specified.

Does that help?

[alleyb]

Thankyou took me a while to figure the logic but yes it works and does what I need. Very much appreciated. considering that the file is now getting to be 28Mb and the database is starting to be quite wieldy I need to effectively automate as much as possible and this is a great start. THANKYOU again