Forum Discussion

DavidHeath's avatar
DavidHeath
Copper Contributor
Dec 26, 2021

relative cell references in a formula

I have a worksheet that performs a lookup to another sheet to count how many items of the type are there.   Anyone familiar with disc golf will recognise what I have in the image below. The only fo...
excel
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 27, 2021
Do you have the data in an Excel Table? That only allows entire records to be sorted and a unit and tends to be more meaningful.
DavidHeath's avatar
DavidHeath
Copper Contributor
Dec 27, 2021
I'll take a look. I've heard of tables, but never used them.

Thanks.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 27, 2021
    Ctrl/L or Ctrl/T are shortcuts to creating a table when a cell within the would be Table is selected. Make sure there is blank space around the table.
    • DavidHeath's avatar
      DavidHeath
      Copper Contributor
      Dec 27, 2021

      PeterBartholomew1 sorry, that didn't work.  I created the table (those alternating stripes are annoying... but, no matter), but when I sorted on a column with the automatically applied filter tool, the cell retained the formula reference for the location it had before the sort, rather than the cell five to the right of its current position.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Dec 27, 2021

        DavidHeath 

        It appears that we have very different work practices, but my best guess as to the formula you require might be as shown in the image

        I have applied your Sheet names to the Tables and used them in preference to references to the Tabs.  As you might observe, I always format Tables to 'White, Table Style Light 1' and deselect Banded Rows.  I would normally deselect Filter Buttons too but that is not what you require.  The criterion value is looked up by column name and the particular value is part of the current record.  The criterion Range is the entire column of the Master Table.

         

        This should be robust to any sorting or the insertion of rows or columns, but If it is not what you require then either I or NowshadAhmed may provide further assistance.

Resources