Cell = sheet -1 Fill a cell with contents from the next sheet.

[VShane]

Currently I have sheet 1 and sheet 2. I have a cell in sheet 1 Cell A2 that I would like to be equal to the next sheet cell A2. However when I copy sheet 1 to become sheet 3 and copy sheet 2 to become sheet 4 I would like Cell A2 in Sheet 3 to point to a new reference cell in sheet 4. Attached an example sheet. Thanks!

[PCI]

How you would like this to happen?
A macro to update the formula for odd number sheet making reference to next even number sheet ??
A macro launched by a key ??

[VShane]

Well essentially when I copy the sheet I would like it to point to the next sheet cell number. So rather than looking for the name of the sheet (sheet 4 cell A2) it will just look at the next sheet for the data. Can it be done without a macro and just do it with a formula? that would be ideal. If not a button or a macro would suffice.

[VShane]

Ideally I would like to have an option to put in something line like =sheet(next, A2). This would automatically look at the sheet to the right of whatever the sheet the formula is contained in.

[VShane]

Essentially I want to create my own function if it doesn't already exist in excel.

[PCI]

Perhaps next code ...?
In the sheet code put

Please Login or Register  to view this content.

in a module put

Please Login or Register  to view this content.

[VShane]

Almost but I don't want to have to write a sheet name, I can do that already. I want to only write the cell name that will be on the next sheet. So I want to write =NextSheet(A2) and it returns cell A2 in the sheet after the sheet the formula is written in. So the function would need to take the current sheet and add one to it then get the cell between () and return that to the cell in the original sheet.

[VShane]

I think I got it. I used INDIRECT() and added this to a module. Now I just have to type =INDIRECT(NextSheetName()&"!A2") Thanks for the help!

Please Login or Register  to view this content.