How to make relative cell reference using INDIRECT function in Numbers?

Question

Level 1

8 points

How to make relative cell reference using INDIRECT function in Numbers?

I am using the indirect function in numbers for my monthly budget spreadsheet. I have a sheet for each month of the year. I also have sheets for various items that pulls data from each month. I am using the indirect function to pull various data from each montly sheet. For example I pull the ending balance of my checking account each month. The name of each sheet matches the column name instead of having to type the sheet name in every individual cell. My issue is that using the indirect function as I am below, the cell that the ending balance is being pulled from (B74) will likely change as more rows are added to that table in the January sheet. As it is below, B74 is an absolute cell reference. Is there a way to make B74 a relative cell reference so that if a row is added and the ending balance ends up being in cell B75, the function below will automatically change to B75?

iMac 27″, macOS 15.6

Posted on Dec 18, 2025 8:38 AM

Reply

Answer 1

Badunit

Level 7

30,432 points

Dec 18, 2025 2:36 PM in response to trentsmac

If that cell is always going to be in the last row, one formula to do it is

=CHOOSEROWS(INDIRECT(B1&"::Ledger::B"),−1)

You can change the -1 to -2 to return the second to last row, and so on.

If that cell is in an unknown number of rows from the top or bottom of the table and if it has a unique header (like "Ending Monthly Balance"), you can use XLOOKUP along with two INDIRECT functions.

Reply

Answer 2

Camelot

Level 9

58,305 points

Dec 18, 2025 3:08 PM in response to trentsmac

There are a couple of ways of doing this.

The easiest (at least to understand) is via XLOOKUP

=XLOOKUP("*",INDIRECT(B1&"::Ledger::B"),INDIRECT(B1&"::Ledger::B"),,2,-1)

For a breakdown:

"*" - a wildcard for any value - this is what you're searching for, and anything goes.

INDIRECT(B1&"::Ledger::B") - this returns a reference to column B of the Ledger table on whatever sheet is named in cell B1

,, - this is what's returned for a empty result. Doesn't matter what you put here since the wildcard will always match

2 - this is what tells XLOOKUP to perform a wildcard search, rather than a specific string/value

-1 - search last to first. Since we're performing a wildcard search, this will return the first match (i.e. the last item in the search array, since we're starting at the bottom)

This doesn't require you to know the row number at all, it will just grab the last cell from the row.

Now, if you're using footers, or other empty content below the last value, there are more games to be played, but hopefully this will get you started.

Reply