Ask Your Question
0

Can I reference sheets and/or row/column IDs in calculations from other cells?

asked 2020-01-14 06:30:50 +0200

marvel gravatar image

I have a large spreadsheet ("totals") that references many other sheets in calculations. Example cell: =SUM($sheet1.M77,$sheet2.M77,$sheet3.M77,$sheet4.M77)

Maintaining this is a pain; every time I add a new sheet, I have to update every cell in "totals" to reference it.

Is it possible to use the value of other cells as reference IDs in a calculation? If I could put all of the sheet names in a cell or group of cells and reference them in my SUM, that would be ideal. Even better if I could also reference the row/column ID from another cell. Such as:

=SUM($A1.B1) where A1="sheet1,sheet2,sheet3, ..." and B1="M77".

or =SUM($A1:$A5.M77) where A1-A5 are "sheet1", "sheet2", etc.

Any advice would be most appreciated.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-01-14 08:11:54 +0200

Opaque gravatar image

updated 2020-01-14 08:18:37 +0200

Hello,

in your special case you can do the following:

  • put string Sheet1.M77:Sheet4.M77 into let's say cell A1 and use
  • formula =SUM(INDIRECT(A1) to calculate the total of all sheets

If you now add a new Sheet5 change the text in A1 to Sheet1.M77:Sheet5.M77.

Attention

  • This only works in your special case where all cells to be added are in the same cell on all sheets (M77). It will produce unexpected results, if this is not the case. So don't try, if this condition isn't met.
  • There is no automatic update on references if you add/delete columns
  • This does not work, if you add a sheet between first and last sheet, having a value in cell M77, which should not be included in total sum.
edit flag offensive delete link more

Comments

That helped, thank you!

marvel gravatar imagemarvel ( 2020-01-15 00:26:51 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-01-14 06:30:50 +0200

Seen: 46 times

Last updated: Jan 14