Use Structured Reference to locate value in cell in previous row of table

[cmissal]

I know how to get the value of an cell in a table if the cell is in the same row using the structure Reference "special item specifier" [#This Row], but I was wondering if anyone had a quick/efficient way of locating a value 1 (or more) rows above (or below) the current row.


I figure I will have to write a function that identifies the address of the current row and then subtracts (or adds) 1 (or more) to locate the cell address desired, then pull the value from that addressed cell.


My Psudocode is

IF(TestColumnPreviousRowValue=MyTable[[#Headers],[TestColumn]],"First Row in Table","Not First Row in Table")


I need the code for "TestColumnPreviousRowValue" in the above.


I will work on it now, but if you have this readily available, Please post! I will monitor this topic as I work!


Thanks,


Casey

 

[cmissal]

This is what I came up with. Please let me know if you have a more efficient method.


The formula for the "TestColumnPreviousRowValue" in the original psudocode is:


INDIRECT(ADDRESS(ROW(MyTable[AnotherTestColumn])+MATCH(MyTable[[#This Row],[AnotherTestColumn]],MyTable[AnotherTestColumn],FALSE)-2,COLUMN(MyTable[TestColumn])))


THanks.


Casey

 

[SirJB7]

Hi, cmissal!


Tried using ROW and ROWS Excel functions?


To perform the action described in your first post pseudocode, you can to this:

=IF(ROW()=ROW(MyTable),"First Row in Table","Not First Row in Table")

Add a "+1" unquoted before the first comma if MyTable has a header row and you want to exclude it as first row.


To check if a row is within a table range, you can do this:

=IF(AND(ROW()>=ROW(MyTable),ROW()<=ROW(MyTable)+ROWS(MyTable)-1),"Yes","No")


Regards!

 

[Kyle McGhee]

Hi cmissal,


Copy this into A1:A9 and convert to a table

[pre]

A
1
50
49
25
48
13
62
3
Copy this into B2, it should enter all the way to B9


=INDEX(Table1[#All],ROW(Table1[@])-1,1)


A	PriorRowA
1	A
50	1
49	50
25	49
48	25
13	48
62	13
3	62

[/pre]
I believe that is what you wanted.


Kyle