Excel – How to create a summary of the same few cells on many different sheets in Excel 2013

cellsmicrosoft excelworksheet-function

I have an Excel workbook with 140 sheets. I'm trying to summarize the same 3 cells in each sheet on a summary sheet. How do I grab the value of the respective cells on each sheet without manually linking?

Best Answer

=SUM('*'!A1)

Substitute A1 for the cell you want. This will pull cell A1 from every sheet in the workbook. Safe to use in your summary sheet in A1 as it doesn't create a circular reference.

=SUM('Sheet1:Sheet150'!A1) accomplishes the same thing, but you'll have to change it if you add a sheet to the left of 1 or right of 150. You can use it for names as well.

To explain it further, suppose this example spreadsheet:

Sheet: Tom, Value of A1: 3 | Sheet: Sue, Value of A1: 5 | Sheet: Bob, Value of A1: 7

Using =SUM('Tom:Bob'!A1) would give you a result of 15, summing cell A1 on the sheets Tom through Bob. If you rearrange the sheets moving Bob between Tom and Sue (Tom-Bob-Sue), now =SUM('Tom:Bob'!A1) gives you 10, summing cell A1 on the sheets between Tom and Bob.

Related Question