Forum Discussion
Advanced Excel Formula discussion - Problem with dynamic range
This may not be of much assistance for your current challenge but there are no issues with using defined names in conjunction with dynamic arrays. A defined name is simply a name given to a snippet of code that, when used within a worksheet formula will execute to formula to which it refers. The most widely used defined names refer to simple range references, e.g.
fixed range = $A$1:$B$40in which case you have an absolute range reference. However, it is quite possible to name the anchor cell of a dynamic range, in which case using the name followed by the # operator is the dynamic range. An alternative is to define the name to refer to the dynamic range by including the # within its definition.
anchor = Sheet1!$A:$1 Worksheet formula = anchor# or dynamicRange =Sheet1!$A$1# Worksheet formula = dynamicRange returns a reference to the same range.Not only that, but
finalRow =TAKE(anchor#,-1)is a reference to the final row of a dynamic range and will move up and down the sheet as the data changes. Please accept my apologies if this is obvious to you, but it is likely that there will be other users that have never had cause to explore the possibilities.
My approaches to returning the landlord as contact where they have no agent were
1. to trap the error
= IFNA(XLOOKUP(landlord, landlords, agent), landlord)2. to look up agent for each house individually using MAP
= MAP(landlord, LAMBDA(name,
XLOOKUP(name, landlords, agent, name))
)[I realise the programming style may cause communication difficulties but I stopped using direct cell referencing 10 years ago]
Range Reference Array Defined Names IFERROR Correct Correct Correct XLOOKUP #VALUE! Wrong #VALUE! IFNA #VALUE! #VALUE! Correct MAP Correct Correct Correct Thanks Peter. I just tested and it's interesting to see these different results. The most annoying combination is XLOOKUP+Array, which gives a wrong result, instead of just fail.
I guess one problem of defined names is it does not recognize spilled array like A1# so it's extremely easy to miss new lines.
This may not be of much assistance for your current challenge but there are no issues with using defined names in conjunction with dynamic arrays. A defined name is simply a name given to a snippet of code that, when used within a worksheet formula will execute to formula to which it refers. The most widely used defined names refer to simple range references, e.g.
fixed range = $A$1:$B$40in which case you have an absolute range reference. However, it is quite possible to name the anchor cell of a dynamic range, in which case using the name followed by the # operator is the dynamic range. An alternative is to define the name to refer to the dynamic range by including the # within its definition.
anchor = Sheet1!$A:$1 Worksheet formula = anchor# or dynamicRange =Sheet1!$A$1# Worksheet formula = dynamicRange returns a reference to the same range.Not only that, but
finalRow =TAKE(anchor#,-1)is a reference to the final row of a dynamic range and will move up and down the sheet as the data changes. Please accept my apologies if this is obvious to you, but it is likely that there will be other users that have never had cause to explore the possibilities.
