Is there a way look up sheet index or name?

The formula that can tell you the sheet name is REFERENCE.NAME . It will give the full reference to a cell as text as in January::Table 1::A1. You can parse the string using the LEFT function to get just the sheet name. If your sheets are month names, you can use MONTH to turn the name into a number. As an example, I have a table named "Table 1" on a sheet named "January" and in cell B2 I have this formula to get me the month number:

=MONTH(LEFT(REFERENCE.NAME(A1,2),FIND(":",REFERENCE.NAME(A1,2))−1))

You can use that number with the INDEX function to access your "database". I have a sheet named "Database" with a table named "Table 1" that has five columns (A:E) and one header row. Because of the one header row, January is in row 2. The formula I would use to access the data in column 1 for the month is:

=INDEX(Database::Table 1::$A:$E,$B2+1,1)

To access the next column you change the final 1 to a 2, and so on. You can use the COLUMN function to advance the number vs hardcoding 1,2,3 etc. into the formula. If my formula was in cell C2 (column #3) the formula would be

=INDEX(Database::Table 1::$A:$E,$B2+1,COLUMN()-2)

This formula can be filled to the right to get the data from the other columns of data.

Hi Mathiasaf,

No problem. I shall ask our hosts to move this discussion to the Numbers for Mac forum.

In the meantime, please reply with screen shots of a small part of your screen (shift command 4). Full screen shots (shift command 3) are often difficult to read.

To take a screen shot:

1. Remove or hide any personal details before taking the screen shot.

2. Hold down the shift and command keys, then type 4. The cursor will change to crosshairs.

3. Drag over the relevant part of your screen and then release the mouse/trackpad. You will hear a "camera shutter" sound. A screen shot will appear on your desktop. It will be named Screen Shot with a date & time.

4. In a reply to a message, click on the "Mountains" icon in the Toolbar below your reply: 

5. Navigate to your Desktop, click on a screen shot file, then click on Choose.

Regards,

Ian.

Hi Mathiasaf,

You mention Pages. Pages does not have sheets. Numbers has sheets. Is this a question about Numbers?

Some screen shots of parts of your document will help us to see what you see.

Regards,

Ian.

AppleScript has access to the order they appear in the tabs but no functions do.

Another way to parse the sheet name is

=REGEX.EXTRACT(REFERENCE.NAME(A1,2),"[^:]+",1)

If you change the 1 to 2 it gives you the table name. 3 gives you the cell address.

Nice, Badunit! Didn't know about REFERENCE.NAME().

Yet another way to get the sheet name:

=TEXTBEFORE(REFERENCE.NAME(A1,2,1),":")

SG

TEXTBEFORE is certainly easier to understand than the jargon required in REGEX.EXTRACT, unless one is well versed on said jargon (which I am not).