Discussion Re: relative cell references in a formula in Excel
https://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046097#M127258
I'll take a look. I've heard of tables, but never used them.<BR /><BR />Thanks.Mon, 27 Dec 2021 00:05:45 GMTDavidHeath2021-12-27T00:05:45Zrelative cell references in a formula
https://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046095#M127256
<P>I have a worksheet that performs a lookup to another sheet to count how many items of the type are there.</P><P> </P><P>Anyone familiar with disc golf will recognise what I have in the image below. The only formula is for stock, everything else is static data. For instance cell G2 contains: =COUNTIF(Master!B:B,'Flight Numbers'!B2) The sheet we're looking at is 'Flight Numbers' and 'Master' contains one line for each item currently in stock.</P><P> </P><P>I regularly want to re-order the page using the filter buttons, but when I do, the stock values get mucked up as they stick with the row number they had prior to any re-order.</P><P> </P><P>What I'm seeking is a way to structure the formula so that it has some kind of reference to "current column minus 5" - is that possible?</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="DavidHeath_0-1640562184764.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/335978iBF6BFBBC65552756/image-size/medium?v=v2&px=400" role="button" title="DavidHeath_0-1640562184764.png" alt="DavidHeath_0-1640562184764.png" /></span></P><P> </P><P>Thanks...</P><P> </P>Sun, 26 Dec 2021 23:49:04 GMThttps://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046095#M127256DavidHeath2021-12-26T23:49:04ZRe: relative cell references in a formula
https://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046096#M127257
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.Mon, 27 Dec 2021 00:04:13 GMThttps://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046096#M127257Peter Bartholomew2021-12-27T00:04:13ZRe: relative cell references in a formula
https://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046097#M127258
I'll take a look. I've heard of tables, but never used them.<BR /><BR />Thanks.Mon, 27 Dec 2021 00:05:45 GMThttps://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046097#M127258DavidHeath2021-12-27T00:05:45ZRe: relative cell references in a formula
https://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046098#M127259
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.Mon, 27 Dec 2021 00:10:21 GMThttps://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046098#M127259Peter Bartholomew2021-12-27T00:10:21ZRe: relative cell references in a formula
https://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046112#M127264
<P><LI-USER uid="214174"></LI-USER> 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.</P>Mon, 27 Dec 2021 01:10:46 GMThttps://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046112#M127264DavidHeath2021-12-27T01:10:46ZRe: relative cell references in a formula
https://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046132#M127266
You can go to the Table Tools tab and deactivate the coloured bands/stripes (yes, it is annoying at times).<BR />After applying the formatted table, re-enter the formula in the first cell of the stock column (you might want to clear the old formula from the enyire column first). Excell will update all the rows with the new formula. Now sorting won't be the problem because you are referring to a data in the same row.<BR />Another note: while creating the formatted table, select the entire data manually including the headers. When given an option, select 'my table has headers' option and continue.<BR />Alternately: To solve the reference, you can make use of INDIRECT function. From what I understand, you don't need it but if nothing works, this might help.Mon, 27 Dec 2021 02:35:38 GMThttps://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046132#M127266NowshadAhmed2021-12-27T02:35:38ZRe: relative cell references in a formula
https://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046214#M127291
<P><LI-USER uid="1259762"></LI-USER> </P><P>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</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 782px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/335996i5A1D84C74F0D05EB/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P><P>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.</P><P> </P><P>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 <LI-USER uid="1217170"></LI-USER> may provide further assistance.</P>Mon, 27 Dec 2021 10:17:21 GMThttps://techcommunity.microsoft.com/t5/excel/relative-cell-references-in-a-formula/m-p/3046214#M127291Peter Bartholomew2021-12-27T10:17:21Z