You have a table below and want to refer to a cell above for Sales column (The business case is that you want to have difference in Sales for current month compared to previous month)
Quick and easy way is to use OFFSET formula
=OFFSET([@Sales],-1,)
But in Row 2, it will bring the column heading (Sales) also. Hence, the formula can be revised to
=IF(ROW()>2,OFFSET([@Sales],-1,),0)
But if table starts in some other row, then you will need to adjust ROW()>2 part appropriately. Let's make it dynamic so that you need not adjust it. You can use following formula in this case
=IF(ROW()>ROW([#Headers])+1,OFFSET([@Sales],-1,),0)
Now, OFFSET is considered a volatile function, hence it will recalculate every time, the sheet is recalculated dragging the performance if the table is very big.
Hence, we can use INDEX in this case
=IF(ROW()>ROW([#Headers])+1,INDEX([Sales],ROW()-ROW([#Headers])-1),0)
Note – If both formulas, I have used 0 as the output for FALSE in IF. You can replace is appropriately by blanks or any other value.
I'd forever want to be update on new posts on this internet site, saved to favorites! .
Thanks for the solution using (non-volatile) functions!
Anyone know how to make lambda functions where the input is a table column name? I can't figure out the syntax….
I have a complex workbook that uses many "running total" functions:
ex: =SUM(INDEX([COL_Name],1):[@[COL_Name]])
And especially previous row references, like the tip and trick #185 here. Now I am using, for example, in row 50 (=IFERROR(BC49/1,0) but that won't auto fill when adding rows… the posted solution should work, but makes the formulas even longer and more complicated.
Hence, my goal of creating LAMBDA functions where the input is the column name.
ex: YTD(COL_Name) – to get a running total from row 1 to current row of values in COL_Name
PREV(COL_Name) – to get the value in the previous row of COL_Name
I am having trouble figuring out the syntax to create a lambda function that will work with structured table references. My google searches bring up many great sites, but can't find the answer yet.
Thanks in advance for help ….. even a link to another site where this is covered would be appreciated !