Summary Page

Question

Summary Page

I suspect the answer is no, but just in case...

I'm wondering if there is a less time-consuming way of achieving a summary page for a numbers document.

I've created a summary page on the first tab of the document. Each line on that summary page summarises data contained on tables within another tab within the document. So, for example, line 3 (Zone z0/01) summarises the data in the tab "01", line 4 (Zone z0/02) summarises the data in the tab "02".

I started by creating individual functions for each column in row 3 (z0/01) which accessed and manipulated data within the tab "01". Then I selected the whole row and dragged it down to duplicate it to row 4. I then went along to each column in row 4 and changed each expression from "01::xxxxx" to 02::xxxxx". I then dragged and copied row 4 to row 5, which I'm up to right now on the image below. This particular document will have 10 tabs, but I'll be making some with more (up to 30).

I was hoping there was a quicker way to change every expression in the table (around 250 for a 10 tab document).

iMac 24″, macOS 12.0

Posted on Nov 14, 2021 4:15 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,702 points

Nov 14, 2021 4:55 PM in response to Winston Churchill

This is two questions in one I think.

The idea in another thread might make it easier to make references to the cells so you can fill across and down without retyping everything.

https://discussions.apple.com/thread/253365594

Add a header column on the left and a header row on the top (row 2 maybe) to be able to construct the sheet:table::cell address for each cell. Then use INDIRECT to refer to the cell. You can hide the column and row later if you want to.

The formula you posted looks like it needs to strip the number from the front of the text in the cell. If all of your formulas are that way and all the text strings start with a number, it can be done a lot easier with REGEX.EXTRACT. All it takes is one function call vs all those IFs.

=REGEX.EXTRACT(INDIRECT(celladdresstext) ,"^[0-9.,]+")

This will extract a number from the beginning of the text, stopping as soon as it finds a character that is not 0-9, a point, or a comma.

Reply

Answer 2

Nov 16, 2021 3:59 AM in response to Badunit

I'll give this a try and see where it gets me. It's a bit of work but not as much as changing the sheet numbers asI'm doing just now. I'll see if I can get it to work.

Reply