I am wondering how to "send" data from one cell so that it automatically adds its contents to a cell, in another sheet, which contains ongoing totals.

In Sheet 1, I have a cell called TOTAL APPLES with another cell value: 22

In Sheet 2, I have client who bought 10 apples.
In Sheet 3, I have a client who bought 12 apples.
The 10 from Sheet 2 and 12 from Sheet 3 are added together to make the 22 in Sheet 1.

But the key to me query is this:

As I get more clients, I keep duplicating sheets. Now I've duplicated Sheet 3 to become Sheet 4, where client 4 has now bought 8 apples. This automatically adds those 8 to Sheet 1, bringing my total to 30.

Then I duplicate the Sheet for Client 4 to become Sheet 5, where this client bought 2 apples.

Now the Sheet 1 TOTAL APPLES automatically updates to 32.

Is this possible? I'm continually duplicating sheets with changing names, but I need a TOTALS in Sheet 1 to update automatically.

1 Answer 1


It is possible, using the INDIRECT function.

  • In sheet 1, create a list with the names of the sheets where the client information is.

  • Then, use the following formula =INDIRECT(A1 & "1!B15") ,where A1 has the name of the sheet where you want to copy the data from and B15 for the cell where the information you are looking for is on the client sheet

  • For multiple clients, just create more rows and copy the formula

  • Thank you! But what about the fact that I will not know the names of the new clients (new sheets) in advance? I'll be duplicating sheets, renaming them per the client's name, and would rather not tweak formulas every time I duplicate.
    – Jeremy
    Apr 14, 2022 at 5:56
  • just add a line to the "totals" sheet with the name of the client, and the formula should reference to the new sheet. Every time you add a new client, after you add a sheet, add also a line to your "totals" sheet
    – Oren
    Apr 15, 2022 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.