Need help with script to automate copying data

Question

Level 2

168 points

Need help with script to automate copying data

I've got a Numbers file with a lot of tabs, each named by year: 2024, 2023, 2022, etc. Within each tab are 6 tables, all identical in format. The tables are named May, June, July, August, September October, respectively. Within each table is a header row, then 31 or 32 rows depending upon month. Column A is integer for day, Col B is max temperature for that day, Col C is average temperature that day, and Col D is min temperature for that day. Last row has word Avg in Col A, followed by average of the max in B, average of the average in C and average of min in D. Example below.

I have another tab called Plots. Within Plots are 2 tables, one with average of max temperatures and the other with the average of the average. Example below

Manually copying the data from the respective tab to the table is tedious. I need help with a script to automate the copying. I'd manually insert a blank row above 2014, for example, and enter 2013 in A2 The script would need to loop through each table in tab 2013 and copy the last entry in Col B from the respective table. Same for a second table named Average Monthly Average Temperature. Repeat the process for 2012 and so on. I don't know enough about Automator to make this happen.

Thanks in advance for the help.

Mac mini, macOS 14.6

Posted on Aug 7, 2024 1:12 PM

Reply

Answer 1

Camelot

Level 9

55,335 points

Aug 7, 2024 2:03 PM in response to DesertRatR

I think you're approaching this the wrong way. This isn't a matter of scripting (although scripting could probably do it), but more some creative functions.

Spreadsheets were literally designed for this kind of thing - the ability to tabulate and summarize data from multiple sources.

In this case it sounds like you need some formulas on your summary page that simply =LOOKUP() values on the other tables. Assuming everything is consistent, you can use things like =INDIRECT to build dynamic references based on the year names.

Without seeing your sheets, it's hard to be more specific, but using formulas would be much faster and reliable.

Reply

Answer 2

DesertRatR Author

Level 2

168 points

Aug 8, 2024 1:41 PM in response to Camelot

Thanks for the simple approach reminder.

INDIRECT("2013::May::B33") in the summary table Col B, for example, does what I want. Just change May::B33 to June::B32, etc, moving across the summary table. I'd like to get the year in the INDIRECT function from Col A of the summary table for the row I am on. It seems like INDIRECT("INDIRECT("A3")::May::B33") where A3 of the summary table contains the year 2013 ought to work. However there is a syntax error that way. What am I missing?

Reply