Formula dependant on worksheet position within workbook

[AnneM]

I have a workbook consisting of 14 worksheets. The position of the first ("Monthly") and last worksheet ("Holidays") is fixed. However the remaining 12 ws (Jan - Dec) are re-ordered through a user form, whereby the user selects their start month. The selected start month ws then becomes the 2nd ws in the workbook and all the others follow chronologically.

I would like to include a formula in C16 of every ws (Jan-Dec) that queries whether or not the particular sheet is the first of the monthly sheets. If it is, then it simply copies the contents of another cell i.e = C3. However, if the ws is in 3rd position or greater, then C16 would contain a formula to retrieve the value from the last filled cell in row 16 of the previous sheet + C3.

The last filled cell in row 16 of any sheet will be dependent on the no. working days in the month, so not a fixed point.

I see it as being something like:

= IF(this ws in position >2, get the value from the previous ws last filled cell in row16 + C3, else C3)

Can anybody advise if there is a way to instruct based on ws position within the workbook, and how to do it. And also how I would fetch the value from the last filled cell of the previous workbook.

As the ws will always be in chronological order, it is possible to refer to the previous ws by name though i.e. for March, it is possible to use =February!

Any advice greatly appreciated.

 

[Tetra201]

If you have the SHEET function in your version of Excel, then try the following on the Jan ws:

=C3+IF(SHEET()>2,Dec!C16,0)

For the Feb ws, change the reference in the formula to Jan, etc.

 

[AnneM]

Hi, thank you for your help.

The formula certainly works to determine the position of the sheet, but if the SHEET( )>2, and assuming we are working on the "Jan" ws, then the next part of the formula is to retrieve the value from the last filled cell in row 16 of the "Dec" sheet. The range would be C16:X16. The formula itself is in C16 of "Jan".

I didn't explain it very well in my first post, sorry!

 

[Tetra201]

Here is an updated formula that would look for the last numerical value in range C16:X16

=C3+IF(SHEET()>2,LOOKUP(2^1023,Dec!C16:X16),0)

 

[AnneM]

Here is an updated formula that would look for the last numerical value in range C16:X16

=C3+IF(SHEET()>2,LOOKUP(2^1023,Dec!C16:X16),0)

Thank you so much. Everything is working perfectly now

 

[Joe4]

Anne,
In the future, when marking a post as the solution, please mark the original post containing the solution, not your own post acknowledging that another post was the solution.
I have updated this thread for you.

Thanks

 

[Tetra201]

@ AnneM: Glad to have helped.

@ Joe4: thank you for making those corrections and keeping the forum well-organized.