Feb 26 2018
09:55 AM
- last edited on
Jul 25 2018
11:11 AM
by
TechCommunityAP
Feb 26 2018
09:55 AM
- last edited on
Jul 25 2018
11:11 AM
by
TechCommunityAP
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.
Mar 05 2018 03:38 PM
Please see detailed guide on Excel table Structural references both relative and obsolete.
Mar 08 2018 09:51 AM
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.
Mar 08 2018 10:12 AM
Nov 08 2018 10:09 AM
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