I need a formula to accumulate results from sheet to sheet

Question

Level 1

13 points

I need a formula to accumulate results from sheet to sheet

How do I copy & paste a row of formulas from one sheet to the next so that the amounts accumulate? I have a great spreadsheet that I've used for years [that I didn't create] to track weekly sales. I'd like to start a new one for 2020 without having to change every formula.

One of the formulas is =SUM(‘Jun 9-15’ :: 'Table 1' :: C33). It doesn't work to copy and paste either one formula at a time or a whole row of formulas at a time (which is what I'd like to be able to do). I'm thinking this must be simple for anyone who understands formulas.

In the two pictures below, what I need is to have the values created in Row 33 from sheet Jun 16-22 appear in Row 32 on the next sheet, Jun 23-29. What I've been doing (which is WAY to time-consuming) is copy and pasting Row 32 from one sheet to the next, but then I have to go in each cell and change the name of the sheet. There are 11 of those cells on each sheet/week for 52 weeks. I also tried copying Row 33 and pasting it into the next sheet's Row 32 and got error messages.

I know someone out there can help me. Please?

iMac 21.5", macOS 10.13

Posted on Jan 10, 2020 3:02 PM

Reply

Answer 1

Jan 13, 2020 6:43 PM in response to Wayne Contello

I'm sorry. I misunderstood your question. I suggest "regularizing" the sheet name to make the specific days of the year irrelevant.

What I mean is... name the Sheets "Week 1", "Week 2", ext.

then when you create a new sheet, update the week number (which is a new cell I am proposing) and make the formula in cell C32 use that information to get the previous weeks value like this:

enter the week number in cell A8

then the formula in C32 could be:

=IF($A8>1, INDIRECT("Week "&$A8−1&"::Table 1::C31",addr-style), 0)

This relies on two things:

1) each table is name "Table 1"

2) you update the week number in the sheet name AND in the cell A8 (or whatever cell you choose)

Reply

Answer 2

Jan 13, 2020 5:49 PM in response to pinkforme

I suggest changing you formulas to avoid specific references to the table name of column names.

However, I think, before starting that change I recommend making a "general" change to your Numbers settings to disable using header names as cell references. Open preferences for Numbers by selecting the Numbers menu item "Numbers > Preferences"

Then uncheck the option "User header names as labels":

FOR THE TABLE IN YOU SCREENSHOT, you will need to change the table to be limited be 32 row and set the last three (3) rows as footer rows:

Now change the formula in cell C32 to be:

=sum (C)

notice this indicates to sum column C (excluding headers and footer) for the same table (no mention of sheets or table names).

Reply

Answer 3

brenden dv

Community Specialist

Jan 13, 2020 5:10 PM in response to pinkforme

Hello pinkforme and welcome to Apple Support Communities.

I see you have some specific questions about using formulas in the Numbers app. You may find these resources helpful.

Numbers User Guide for iCloud - Apple Support

Calculate values in Numbers on Mac - Apple Support

The second resource is for Numbers on a Mac but the info should also be helpful for the version of the app at iCloud.com.

Cheers.

Reply

I need a formula to accumulate results from sheet to sheet

Similar questions