Forum Discussion
Dragging formulas with fixed references & non fixed references
Hi,
I am needing to populate cells in a row with a couple of formulas. Here is an example:
In cell L2 (V6 2019) =INDEX('2019'!$G$2:'2019'!$G$4, MATCH(A2, '2019'!$A$2:'2019'!$A$4, 0)) and next to this in cell M2 (V6 (2023) ==INDEX('2023'!$G$2:'2023'!$G$4, MATCH(A2, '2023'!$A$2:'2023'!$A$4, 0))
I need to drag this across other couplets in the row. So the INDEX reference needs to change from G to H in both and the MATCH Lookup value (A2) needs to stay the same.
Obviously the challenge is needing to change fixed references whilst dragging and keeping non fixed references consistent...
Thanks!
It is not clear to me whether A2 on the formula (summary) sheet is part of an array? I assume it is, and your requirement is to look the value up on multiple sheets before moving to look up the next value, B2.
The concept of relative referencing is no more than a primitive approach to scanning a list or elements of an array with scalar formulas without needing to recognise the existence of the array as an entity. To make it work when the references do not move one cell when dragging the formula one cell is a problem. Typically you would need helper ranges that point to the correct references.
If you use 365 then relative referencing is no longer required as a concept. Functions like REDUCE and HSTACK allow entire tables of results to be generated as a single dynamic array.
3 Replies
It is not clear to me whether A2 on the formula (summary) sheet is part of an array? I assume it is, and your requirement is to look the value up on multiple sheets before moving to look up the next value, B2.
The concept of relative referencing is no more than a primitive approach to scanning a list or elements of an array with scalar formulas without needing to recognise the existence of the array as an entity. To make it work when the references do not move one cell when dragging the formula one cell is a problem. Typically you would need helper ranges that point to the correct references.
If you use 365 then relative referencing is no longer required as a concept. Functions like REDUCE and HSTACK allow entire tables of results to be generated as a single dynamic array.
- Hi Peter,
Thank you for your reply.
You are correct, A2 is part of an array.
Ok, great, I'll look into those! Thank you very much.If you have 365 (or 2021) then the days of relative referencing and dragging formulae are over.
For example, it would be possible to combine the results from two distinct Table lookups using
= LET( result1, XLOOKUP(target, Table1[Letter], Table1[Value]), result2, XLOOKUP(target, Table2[Letter], Table2[Value]), combined, HSTACK(TOCOL(result1), TOCOL(result2)), WRAPROWS(TOCOL(combined), 4) )Endless variations of the theme are possible but the underlying thought processes are the same.
