How can I Permanently Save Data to master spreadsheet from a weekly summary table

Question

Level 1

4 points

How can I Permanently Save Data to master spreadsheet from a weekly summary table

Hi all,

I'm currently driving taxi's and am trying to create for myself a master spreadsheet compiled from 5 daily spreadsheets.

I have one daily sheet for each day I work and "reset" the sheets each week after saving as a pdf.

Each daily sheet feeds the data I want to save into a weekly summary table.

I would like to be able to create a formula that would allow me to insert (transfer) and save the data from the weekly summary table into my master table that only activates when the date in the summary table matches the relevant date field in the master table.

(the data is all different ... some numeric, some text, some date/times and some currency)

I can successfully make the master spreadsheet display the data from the summary sheet using the (IF) formula when the date range matches, but once the date range changes I don't know what formula I need to permanently save the data in the master table. (Each time I "reset" the daily sheets it clears all the data in the summary table, which then creates errors in the master table)

I have been manually copying it from summary-master since I started but am tiring of that and figure there must be a way to automate it.

Does anyone have any suggestions?

Thank you

Posted on May 24, 2021 6:14 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,023 points

May 24, 2021 1:59 PM in response to Urbane Al

"I'm looking for a formula to permanently save the information from the orange summary table into the table beneath it (the master)"

Simply put, formulas don't work that way.

Formulas have no memory of the previous states of the cells they reference to collect the data they use to determine the content of the cell containing the formula.

When the data in one or more of the referenced cells changes, the formula immediately recalculates, using the current content of the referenced cell(s).

What you can do:

As I read your description, the daily data for each week is collected (by formula?) on the Weekly Summary table, then is transferred from that table to the Annual Master table, where it is expected to remain.

If that is an accurate description, and given that the layout of each week of the Annual Master table matches the layout of the weekly summary table, I'd suggest this procedure:

At the end of each week, BEFORE clearing the daily (and consequently the Weekly summary) tables:

Select all cells in annual master table containing the week's data (D124:T128 at the end of the week shown in the screen shot), then press command-C to copy.

With the cells still selected, go to the Edit menu and choose Paste Formula Results.

This paste strips the formulas and pastes only the fixed data back into the cells, where it will remain as time moves on.

Then set up the Weekly Summary table to collect the results from the following week.

Done.

Notes:

You may find that the empty cells forming borders in the annual Master table receive zeros when the data is pasted into them.

Regards,

Barry

Reply

Answer 2

Urbane Al Author

Level 1

4 points

May 24, 2021 7:25 AM in response to SGIII

Here is the Daily sheet I am using:

I take the selected Info I want from here and it is transferred to the weekly summary:

A bit confusing I know,

The weekly summary is the orange table that floats above the annual Master table

I'm looking for a formula to permanently save the information from the orange summary table into the table beneath it (the master)

Thanks

Reply

Answer 3

SGIII

Level 8

37,550 points

May 24, 2021 6:59 AM in response to Urbane Al

Can you post screenshots?

SG

Reply

Answer 4

Urbane Al Author

Level 1

4 points

May 26, 2021 9:45 AM in response to Barry

Hi Barry,

Thanks for answering.

When the data in one or more of the referenced cells changes, the formula immediately recalculates, using the current content of the referenced cell(s).

What you said makes perfect sense and yeah, that's exactly what was happening and I just couldn't figure out how to get around it. But as you explained there is no way around it.

Your solution to strip copy and paste the results is simple enough and I can cope with that.

It's a useful one to know.

Thank you.

I played around with your comment about the empty zeros consequently pasted in the borders as a result of the copy and paste action and managed a workaround by creating a custom cell data format where if the value is less than or equal to 0 it returns an empty cell.

Thanks

Reply

How can I Permanently Save Data to master spreadsheet from a weekly summary table

Similar questions