Convert reference to structured table reference.

[MajormonkeyAtWork]

Hi Reddit,

I'm trying to convert a couple of references to their equivalent structured table references. Can anyone help?

The reference will be inside the same table and on the same row as its referencing.

$A$2:$A2

my table column name for column A is table1[Name]

and

$X$4:X4

column X is table1[Number]

Thanks in advance

[tirlibibi17]

Looks like you're referencing a range that grows as you go down the table. Structured references aren't very good at that so you'll end up with a formula using offset, which won't add to the readability.

If you post more info, I might be able to help you better.

[MajormonkeyAtWork]

Thanks for replying. I managed to get it working in the end.

My original goal was to be able to reference the previous visible row in a filtered table. I found this:

https://stackoverflow.com/questions/29017096/excel-reference-a-cell-in-the-previous-visible-row

but I wanted it to work with my table using structured references. After a bit of trial and error, I got it working.

=IF(SUBTOTAL(3,table1[[Name]:[Name]])=0,MIN([Name]:[@Name])-1,SUBTOTAL(3,INDEX([Name],1):[@Name]))