Spilled INDIRECT to another sheet.

[PrettyGood_Not Great]

Hi,

I am trying to find a 365 solution to the following equation that will allow for a vertical spill. Seems that INDIRECTS doesn't work well in dynamic array formulas.

@INDIRECT("'"&$C11&"'!"&@CELL("address",$F$8))

Where $C11 is the sheet name, and $F$8 is the target cell to be returned from each sheet.

 

[Fluff]

Maybe

=MAP(C11:C15,LAMBDA(m,INDIRECT("'"&m&"'!F8")))

 

[PrettyGood_Not Great]

Maybe

=MAP(C11:C15,LAMBDA(m,INDIRECT("'"&m&"'!F8")))

Hi Fluff, super close. If the array (C11:C15 in your example) is set exactly to the list of tab names then it works, however if that range is larger than the list (i.e. this list can change in size due to user inputs... the file is a template for any user), then I get the #SPILL Error.

Is there any error handling that could be incorporated to allow for blanks in the C11# range?

 

[Fluff]

How about

=MAP(C11:C15,LAMBDA(m,IF(m="","",INDIRECT("'"&m&"'!F8"))))

 

[PrettyGood_Not Great]

How about

=MAP(C11:C15,LAMBDA(m,IF(m="","",INDIRECT("'"&m&"'!F8"))))

Hi Fluff, thanks for this. Turns out both of your solutions work, the issue seems to be, for some reason, that because the C range is a Named Range located on another sheet and referenced here (i.e column C = 'Named Range'). So it has nothing to do with blank rows. odd, as I thought named ranges worked fluently with dynamic arrays.

Have you see something like this before? aware of any workarounds (other than removing the named range as it's used in many locations within the file)

 

[Fluff]

Works quite happily for me with a named range. How are you defining the name range?

 

[PrettyGood_Not Great]

Works quite happily for me with a named range. How are you defining the name range?

I fluff, I got it. there was errant data below the spilled equation! Still odd as when I used a limited range in column C, it spilled. No matter, it works! thanks again.

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[Peter_SSs]

@PrettyGood_Not Great
In your future questions, please mark the post as the solution that actually answered your question, instead of your response message as it will help future readers. No further action is required for this thread as the marked solution has been changed from post 3 to post 2.

 

[PrettyGood_Not Great]

@PrettyGood_Not Great
In your future questions, please mark the post as the solution that actually answered your question, instead of your response message as it will help future readers. No further action is required for this thread as the marked solution has been changed from post 3 to post 2.

Thanks for the correction, accidents happen.