Forum Discussion
Dragging formulas with fixed references & non fixed references
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.
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.
