Automatic progressive formula through different spreadsheets (absolute ref.?)

Hello everybody,

I am looking for a way to fix a problem like this.

I am monitoring costs and other stuff of a project and I set a spreadsheet per day.
Each sheet is built like the others and everyone has in the end to calculate the progressive increase of costs and all the other things depending on what has of course happened the day/sheet before.

Let's say:

Sheet 1
[A1] day cost = 10

Sheet 2
[A1] day cost = 12
Tot costs = [A1+'Sheet 1'A1] = 22

Sheet 3
[A1] day cost = 4
Tot costs = [A1+'Sheet 2'A1] = 26

..and so on...

Avoiding VBA, I was wondering if it is possible to "copy and paste" this easy formula through several sheets asking excel to update the second value of the sum ('NAME OF PREVIOUS SHEET'$A$1, or like 'SHEET'+1$A$1) otherwise I got to do it manually, for lots of sheets...

Is there any absolute reference able to make the sheet's cell don't change but move to the next/previous sheet?
Does it make sense? I hope I succeeded in explaining the problem.

Any help would be really appreciated.
Thanks

Gianni

Can you add a new sheet?

yes sure....what solution are you thinking about?

1) Create a new sheet call it Totals
2) in A1 column header: name of sheet
3) in B1 column header: day value
4) in A2 type in the name of your first sheet
5) in B2 put in the following formula :

Please Login or Register  to view this content.

6) make as many rows in this column as you need, fill in the formula on all the rows
7) you will have a table with all your day values, create a third column with cumulative values
8) you will have cumulative values in one nice table
9) refer each sheet to the same totals sheet but incrementing the row id.

hope this helps

If you don't mind using VBA there is a much simpler solution:

You will need VBA to get the sheet index

add the following function code to a VBA module:

Please Login or Register  to view this content.

then in the total cell in any sheet:

Please Login or Register  to view this content.

basically the sheet2 index is 2 so toget sheet1 in the formula we add -1

The formula is the same in all the sheets
cheers !

thanks for you replies

I am not that confident with Indirect formula actually... Am I supposed to interpret that "!" as "name of the sheet" or something?

gip.pisano,
your problem is that in each sheet you need to have a cell containing a reference to the sheet before so if you are on sheet2, your "totals" cell needs to contain a formula =A1+sheet1!A1.
in sheet3 this formula becomes =A1+sheet2!A1 and so on.
sheet2, sheet3 are the names of the sheets, the "!" is used to reference the sheets in a formula.

in the solution i gave you, sheetnum() returns the index of the sheet you are currently on. so if you are on sheet2, sheetnum() returns 2.

then the formula creates a string "Sheet" & (2-1) & "!A1" so this gives a string sheet1!A1.
the indirect() function translates this string into the actual value contained in the string address.

If you are still having difficulties, i suggest you do some more reading on excel functions and how to implement them.
You can find a lot of info in the following link.

http://www.excelforum.com/excel-gene...additions.html