• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Structured references with expanding range

1. The formulas I'm trying to work on are in yellow. All I want to do is replace the 2nd argument of the FIND( ) with a structured reference in column S, so that S7 is always referenced, then the part after the ":" will automatically expand to the current row, like what you would do if you were calculating a running total.

2. I don't understand why I'm getting a #SPILL error.

3. Why does the formula copy down to F15:F22?

In short, for any given row in column F, I'm trying to do this:

Look to the corresponding value in column S and reference the parent project code (which is already extracted in column E), then use FIND( ) to check if the parent charge code appears anywhere in column S in the cells above.

For example, in cell F11, use FIND( ) to check if the value in E11 appears anywhere in S7:S10.

I'm new to the SPILL feature and while I thought I understood it earlier today, I feel like I'm starting all over now.
 

Attachments

  • Chandoo.org - Structured References with Expanding Range.xlsx
    14.2 KB · Views: 8
Last edited:
#SPILL error means you have an array formula that "spills" many results in x rows by y columns, but there is something in the way blocking excel to spill.
E.g. you can uses these formulas inside a table.
But would this formula work =ISNUMBER(XMATCH(E8:E15;Table3[PROJECT CODE]))*1
 

Attachments

  • Chandoo.org - Structured References with Expanding Range.xlsx
    12.6 KB · Views: 6
For example, in cell F11, use FIND( ) to check if the value in E11 appears anywhere in S7:S10.
Do you remember in a recent thread of yours ( https://chandoo.org/forum/threads/find-applied-to-a-column.51023/ ):
If you want a single celled result, showing TRUE if the lookup value is anywhere in the list, but you don't need to know where, then try:
=SUM(IFERROR(FIND(F7,$I$7:$I$17),0))>0
So in F8:
=SUM(IFERROR(FIND(E8,$S$7:$S7),0))>0
copied down.

edit: I note that @GraH - Guido's and my formula give different results, so I'm not so sure I've properly understood the requirement.
 
Last edited:
Back
Top