Add a number to previous worksheet

I have made a worksheet with totals that have to update whenever I copy the last sheet. On Sheet 1 I have a cell with the number 1 in it.

I made Sheet 2, and in the same cell I have a formula that adds 1 to the cell from Sheet 1, showing a 2 (=Sheet1!J10+1).

When I copy Sheet 2 to make Sheet 3 I want that cell to automatically add 1 to the cell from Sheet 2, and read 3, but it it doesn't. It adds it to the cell from Sheet 1 again, and reads 2. I would have to update the formula by hand.

As I am likely to have 50 or more sheets, this is a pain.

I need a formula that means "Previous Sheet", intead of "Sheet 1". I have spent a frustrating hour or two with Help, and a couple of other people in the office, to no avail. Any suggestions?

Old Shep

Thanks for the quick reply.

=INDIRECT("Sheet1"&VALUE(A1-1,0)&"!J10")+1

This gets me a friendly message which might as well be in Greek (I'm not Greek):

"You've entered too many arguments for this function.
To get help with this function, click OK to close this message. Then click the Insert Function button located to the left of the equal sign in your formula."

I clicked the button, still Greek.

Isn't there a formula ="Previous Sheet"+1?

No, not working. Am I doing something wrong? I have attached a file - Exp.xlsx.

Old Shep

AAARRRGGHHHH!!!!

See attached.

I think the answer is that there is no automatic way to have running totals from sheet to sheet. No formula for "previous sheet". If I have to rename all my sheets, or type in a 1, 2, 3 every time in sheets 1, 2, 3, etc then it defeats the purpose.

In my next life I am going to be a wildlife biologist in a national park, but I'll still probably have to use Excel!

Maybe if I was wildlife ....

AARRGGHHH!!! How do I get consecutive numbers automatically?

"But creating a user-defined function and calling it in J10 sounds easy enough. I mean, once the macro is in place you just do =PrevSheet(J10)+1
or something like that."

Sounds easy enough, huh? The word "just" just means it's easy for you.

I copied and pasted the things from Chip Pearson in the cell and got nothing. That's as far as I'm going.

I wondered why, if you copy a sum formula from Row 10 to Row 11, it automatically updates the formula to the new row, but it doesn't from sheet to sheet.

I am a user in an office, picked up Excel myself over time by asking around and reading help. I don't know what a macro or a function is, and I have spent too much time on this. One day, during a slow time, I will find someone in the office who knows this stuff and learn macros and functions. Although no one I have asked seems to so far.

I really like Excel, it's very useful and lots of fun. Thanks for trying, teylen.