Incremental numbering with other characters.

Question

Incremental numbering with other characters.

I wish to automate (so far as possible) the naming of sheets in my template document.

I'm not referring to the sheet names across the top of the numbers window under the toolbar (although it would be great to do that too). Rather, I use cell A1 on each sheet to name that particular sheet, and it's this cell that I wish to automate.

The name of the first sheet is z0/01, and I wish for the next sheet to be named z0/02 and so on.

I'm looking for a way that references A1 on the previous sheet and maintains the characters before the slash and increments the numerical part after it. I am able to create a formula that does this without referencing the previous sheet, but this isn't what I need because of the following:

Generally speaking, the numerical part after the slash will increase by one from one sheet to the next. However, I also want to be able to manually overwrite the formula on some sheets and the numerical part after the slash on the sheets following this to increase by one based on this manual input.

By way of example: A1(1st sheet) will display the value 'z0/01'. A1(2nd sheet) will automatically return the value 'z0/02'. Lets say I get to the 14th sheet, which returns the value 'z0/14' and I manually overwrite this to display 'z1/01', I then want the 15th sheet to automatically return the value 'z1/02'.

The point at which I overwrite the formula will change each time I use the template.

I hope this makes sense

iMac 24″, macOS 12.6

Posted on Sep 17, 2022 6:03 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,300 points

Sep 17, 2022 5:32 PM in response to Winston Churchill

=TEXTBEFORE(INDIRECT(RIGHT("0"&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1),2)&"::Zone Summary::A1"),"/")&"/"&RIGHT("0"&(TEXTAFTER(INDIRECT(RIGHT("0"&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1),2)&"::Zone Summary::A1"),"/")+1),2)

or

=TEXTBEFORE(INDIRECT(RIGHT("0"&TEXTBEFORE(REFERENCE.NAME($A$1,2),":")−1,2)&"::Zone Summary::A1"),"/")&"/"&RIGHT("0"&(TEXTAFTER(INDIRECT(RIGHT("0"&TEXTBEFORE(REFERENCE.NAME($A$1,2),":")−1,2)&"::Zone Summary::A1"),"/")+1),2)

Reply

Answer 2

Sep 17, 2022 9:13 AM in response to SGIII

Below is the top of my sheet. The sheet is much longer than the screenshot, but the cell I'm referring to is in the top left, so the rest isn't relevant.

There are currently several tables on a sheet, but I'm toying with the idea of merging them into one. The benefit of doing so is that it makes less work when duplicating a sheet if all the calculations within it reference cells from the same table. One disadvantage is that I'm stuck with the same cell widths the whole way down.

However, I can work around the latter by having lots of 10pt cells and merging a lot. This allows me to work around the issue this post is about by splitting up the text in z0/01, as you can see from the table I'm experimenting with below. However, I still think it looks untidy, and while I could live with this, I'd still prefer to have the solution I sought.

Reply

Answer 3

Sep 17, 2022 3:56 PM in response to Badunit

Many thanks for that. It will take time to work through that to understand how to include my sheet nomenclature. My sheets are named "01", "02", "03" etc. Simply removing the word SHEET won't work. Also, the Table name is "Zone Summary".

On the other points you raise: I won't be duplicating or creating any new sheets once the template is complete. I'm making a template with 40 sheets. So when I create a new spreadsheet from the template chooser, A1 will already be named z0/01 through z0/40 on consecutive sheets.

Each sheet is quite complex; duplicating sheets is time-consuming as I need to change some of the formulae that reference external tables after duplication and then test each part works as expected. Therefore, it's best to have already these extra sheets prepared in a template rather than create them when I'm collecting and recording the data.

Reply

Answer 4

SGIII

Level 8

36,134 points

Sep 17, 2022 6:23 AM in response to Winston Churchill

Sheets in Numbers are the designated by the "tabs" across the top.

Sheets in Numbers do not have a cell A1, etc. Only tables have cells.

It might be possible to automate something like what you describe using a script in a Shortcut.

But, unless you have dozens sheets and tables, and you are constantly adding more every few minutes, wouldn't it be quicker and more efficient to simply name them manually according to your desired scheme?

SG

Reply

Answer 5

Sep 17, 2022 7:10 AM in response to SGIII

SGIII wrote:

Sheets in Numbers are the designated by the "tabs" across the top.

Sheets in Numbers do not have a cell A1, etc. Only tables have cells.

I understand this. I did say that I wasn't referring to the tabs across the top and perhaps I should have said, "Rather, I use cell A1 in Table1 on each sheet to name that particular sheet...."

But, unless you have dozens sheets and tables, and you are constantly adding more every few minutes, wouldn't it be quicker and more efficient to simply name them manually according to your desired scheme?

I have a template which currently has 40 sheets. I use this template regularly, sometimes once a day. I collect data in it for commercial buildings. The building floors determine the z0, z1, z2 part, and the room numbers on each floor determine the number after the slash.

I may survey a building with 36 rooms and only one floor one day and the next a building with 25 rooms over three floors. I also survey buildings with more than 40 rooms, which requires me to use the template a second time and name sheet 1 (for example) z0/41.

My template needs updating from time to time. Sometimes because the methodology used in my calculations changes, sometimes because I see a more efficient way to enter the data or perform the calculations. Each time I update the template, I take the time to add a few more sheets, but they take time to add, so I only do a few for each update.

Previously, I created a new document from the template for each floor, and I could automate the numbering because of this. Unfortunately, the calculations I need to make have changed, and it's now necessary to use data from multiple floors in the same calculations, so having just one document for each building is much more efficient.

So yes, I believe that not having to make these manual entries will save much time over the years, but while saving time is desirable, it also makes it less likely to make mistakes.

Reply

Answer 6

SGIII

Level 8

36,134 points

Sep 17, 2022 8:34 AM in response to Winston Churchill

Understood. You are really putting Numbers through its paces!

It strikes me that this may be worth automating via a Shortcut, but haven't though the problem enough. Meanwhile, others may have ideas for a formula-based solution.

It might help if you could provide a screenshot or two as illustration.

SG

Reply

Answer 7

Badunit

Level 7

28,300 points

Sep 17, 2022 2:06 PM in response to Winston Churchill

Below is an answer but it may not be a good idea. Unlike a database app that would actually create and hardcode the number in the cell/field, the only way to do it in a spreadsheet (other than by a script) is to use a formula. If the formula gets messed up or deleted in one sheet, all sheets that follow it will be messed up (up until the next time you override the formula).

If you name your sheets as you create them so they are in numerical order as in Sheet 1, Sheet 2, Sheet 3, etc. or any other convention that is text followed by 1,2,3, etc then the formula below will work (after adjusting it to your sheet and table naming convention). But, then again, if you have to rename the sheet anyway you might as well name it the same as what you want in cell A1. The formula to get cell A1 to repeat the name of the sheet is pretty simple, unlike the formula below.

=TEXTBEFORE(INDIRECT("SHEET "&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1)&"::Table 1::A1"),"/")&"/"&RIGHT("0"&(TEXTAFTER(INDIRECT("SHEET "&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1)&"::Table 1::A1"),"/")+1),2)

If your sheet names are something other than Sheet 1, Sheet 2, etc. then change the word "sheet" in the formula to the name you are using (in two places).

If your table name is something other than Table 1 then change "Table 1" in the formula to the name of your table (in two places).

If you override the formula in your last sheet, you cannot duplicate that one to create the next sheet unless you copy/paste the formula into the table on the new sheet.

And here is how it works:

REFERENCE.NAME($A$1,2) gets the address of cell A1 of the table that has the formula, in the form of a string. Example: Sheet 2::Table 1::A1

=(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+") extracts the first set of numbers from that cell address string, which happen to be the numbers after the word "Sheet". In this case it would be the number 2

"SHEET "&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1)&"::Table 1::A1") creates a string representation of the address of cell A1 in previous sheet (the sheet that is 1 back from this one numerically). In this case it would be Sheet 1::Table 1::A1

INDIRECT("SHEET "&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1)&"::Table 1::A1") uses that string to make an actual cell reference to cell A1 of the previous sheet.

Now that we have a cell reference for cell A1 of the table on the the previous sheet,

TEXTBEFORE(INDIRECT("SHEET "&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1)&"::Table 1::A1"),"/") gets the part before the "/". This would be the "z0" part of the string.

TEXTAFTER(INDIRECT("SHEET "&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1)&"::Table 1::A1"),"/")+1) gets the part after the "/" and increments it by 1.

RIGHT("0"&(TEXTAFTER(INDIRECT("SHEET "&(REGEX.EXTRACT(REFERENCE.NAME($A$1,2),"\d+")−1)&"::Table 1::A1"),"/")+1),2) appends a "0" to the front of that number then truncates the result to the rightmost two digits.

The rest of it assembles the two parts with the "/" in the middle.

Reply

Answer 8

Sep 17, 2022 5:35 PM in response to Badunit

Yes, that works well. Thank you ever so much.

I'm not sure I fully understand it yet, but I will keep re-reading what you have written. The fact it's working is significant for now, but it's nice to know why in the more extended scheme of things.

Many thanks, once again.

Reply

Incremental numbering with other characters.

Similar questions