Add a number to previous worksheet

**dsheppard** · 11-02-2009, 06:39 PM

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

**teylyn** · 11-02-2009, 06:53 PM

Hi,

You could include a number in your sheet name and a number for the sheet itself.

say in A1 on MySheet1 you have a 1
in A1 on MySheet2 you have a 2
in A1 on MySheet3 you have a 3
and so on, thus numbering the sheets.

In all sheets you want to add J10 from the previous sheet + 1 in cell J10

then, starting on MySheet2 and following you can use a formula like this in J10

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

hth

**dsheppard** · 11-03-2009, 10:49 AM

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?

**teylyn** · 11-03-2009, 01:45 PM

oops, try this one instead. unlike the first one, I tested this and it works in my book!

=INDIRECT("Sheet"&TEXT(A1-1,0)&"!J10")+1

**dsheppard** · 11-03-2009, 03:56 PM

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

Old Shep

**teylyn** · 11-03-2009, 04:07 PM

Am I doing something wrong?

You don't have a number in A1, so there's nothing to add
Your sheets are not called Sheet1, Sheet2, Sheet3, but Sheet1, Sheet1 (2), Sheet1 (3), but the formula creates a reference to Sheet1, Sheet2, etc. You either must rename the sheets or you must change the formula to reflect the real sheet name.

**dsheppard** · 11-03-2009, 04:38 PM

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 ....

**teylyn** · 11-03-2009, 05:19 PM

AAARRRGHHHH!!! is because your cells A1 all contain a 1 and not consecutive numbers.

Maybe, if you're comfortable with creating a macro, this link will help

http://excel.tips.net/Pages/T003088_...eferences.html

and this

http://www.cpearson.com/excel/sheetref.htm

**dsheppard** · 11-04-2009, 09:10 AM

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

**teylyn** · 11-04-2009, 01:44 PM

If Chip Pearson had to come up with a macro to do it, it means that there is no native Excel functionality to do it 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.

**dsheppard** · 11-10-2009, 09:16 AM

"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.