How to write a formula with a reference that adapts to new columns

numbers

When writing a reference in a formula like:

HLOOKUP(Table1::B$2$;Table2::G$3:K$82;46)

have the area referenced adapt to new columns added to Table2.

E.g. Table2 has columns from A to K, and every column contains the same formula. When I add extend the table with new columns, until AA the reference should target Table2::G$3:AA$82.

I am writing a complex solution involving COLUMNS() and ADDRESS(), but I'm looking for the general accepted way of doing this.

Best Answer

Sadly, there simply is no feature in Numbers resembling open-ended cell-range references.

The only manageable workaround I observed actually working as intended involved either using entire rows, or marking as headers all of the columns/rows to be ignored.

"Whole rows" tack, example procedure:

  1. Normalize and rearrange your data to inhabit the entire set of rows in question, rather than starting in column G.
  2. in your formulas' references to those areas, don't use any column letters. For example, rather than COUNTA(G3:AA82,">50") use instead COUNTA(3:82,">50")

"Headers" approach, example procedure:

  1. select Table2 column A
  2. choose 'Add Header Column Before' from the 'Table' menu (may differ between versions?)
  3. select cells in the new column A, from A3 down to A82
  4. from the 'Table' menu, choose 'Merge Cells'
  5. enter a fitting name for that group of rows into A3
  6. use that name in any formulas needing those rows

All data following the header column/row is referred to by its chosen label. You'll see Numbers offering it in the auto-complete choices as you type it into formulas, and when chosen that area will be highligted.

They're both means to the same end, and behave similarly in practice. With both, you'll see the desired behaviour that when adding additional columns/rows, the new area will be no exception—the formulas with references to whole rows/columns or names from headers will also see any new data.

Again, particular to your described spreadsheet, note that since you're starting your data in column G, you may need to…
• alter your formulas to ignore non-numbers
• alter your formulas to ignore data that doesn't pass some other test
• rearrange your overall layout so the data starts in column B

Related Question