Forum Discussion
Relative reference not working with array formula in structured table
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((Anthropometri
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
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, FlorianPlease 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.
