Relative reference not working with array formula in structured table

Copper Contributor

I have an array formula that works (structured table references).  I need a mix of absolute and relative references but there is nothing I can do that seems to maintain integrity of a relative refgerence

This is the formula I am working with:

{=T.TEST(IF((Anthropometrics[Group]=$E$60)*(Anthropometrics[[Exclude]:[Exclude]]=""),Anthropometrics[Age],""),IF((Anthropometrics[Group]=$E$61)*(Anthropometrics[[Exclude]:[Exclude]]=""),Anthropometrics[Age],""),$E$62,$E$63)}


Group, Exclude are both supposed to be absolute references, (and they behave as such)

Age is supposed to be a relative reference.  However, whether I copy / Paste OR drag, the relative references do not change...


I have many sheets to work on, I really do not want to change all these manually.

Any help would be appreciated.

4 Replies

Please see detailed guide on Excel table Structural references both relative and obsolete.

Unfortunately, this was not helpful.  I do not have a problem with absolute references.  The problem is that if I leave things as a relative reference, they do not behave as such with copying / pasting (or dragging) across to neighboring rows.  I need need the reference to maintain a relative nature in an array formula.  It seems the array formula changes how these things behave.

 

It appears to be that solution could be very simple for this, but my hands are tied if i do not see the sample file.

when you say that Age should be relative reference then in that case looking at your formula

It should be Anthropometrics[@Age] Not Anthropometrics[Age]

without that @ sign is the whole column reference with that @ sign becomes relative reference.

also you could use LOOKUP(2,1/(Range=Condition)/(2ndRange=Condition)/(so on instead of IF formula that way you would not need to use control shift enter. again, it depends how the actual data looks like.

plz try the Anthropometrics[@Age] and if it does not work, please provide a dummy sample, so that it can be looked at.

I had a similar issue where I used array formulas in the totals row of a table. The formula references the (corresponding) data column above each totals row cell. Dragging the formula from
Table[[#Totals],[Column1]] to e.g. Table[[#Totals],[Column5]] ends up with a formula in the latter cell that references Table[Column1]...not quite what I want!

For me, following workaround was acceptable: render the formula in a non-array formula and drag/copy it across. It behaved as relative references usually behave. Then I enter every cell and hit ctrl+shift=enter. Much faster than updating the references in every single formula!

HTH, Florian