IFERROR, Index and help!!

I have two sheets. The first is the detail:

The area could repeat multiple times throughout the process.

The second sheet shows a summary:

To bring the summary in I'm using this:

=IFERROR(INDEX({Area}, SMALL({Area Index#}, Index@row)), "")

Two things are happening:

1.) It is bringing up an area twice if it appears a second time. The Area Index # is my 'helper' column" that is giving me a new index number if the area changes. I'm not sure how to exclude it from listing twice.

2.) If I add a line after like the DICH, it is not pulling it into totals.


Any assistance is appreciated.

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @Jen S. if the Area Index is a non-repeating unique ID, you could just populate a column with a list of numbers that goes as high as you would expect to ever need to go. Call it RowNum. Then, use this formula to pull data:

    =INDEX(COLLECT({Area CD} ,{Area Index #}, RowNum@row),1)

    Does this work?

  • Jen S.
    Jen S. ✭✭
    edited 03/02/23

    @Lucas Rayala Fortunately, I already had a row in the table that does the RowNum function

    I created a reference to the Row ID:

    And used this:

    Using this I get Unparseable:

    =INDEX(COLLECT({Area CD}, {Area Index #}, {Row_Num}@row), 1)

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @Jen S., sorry I wasn't notified for some reason that you @mentioned me -- the reason the Index/Collect isn't working is because you have {} around Row_Num -- if you don't intend to refer to another sheet, then you need to replace those with [ ]. If you are actually referring to a column on another sheet, then that's the problem, because that value must either refer to something on your current sheet or be a static value. So either:

    =INDEX(COLLECT({Area CD}, {Area Index #}, [Row_Num]@row), 1)

    =INDEX(COLLECT({Area CD}, {Area Index #}, "static value", 1)

    If you want a column formula, you basically need another column to refer to in order to populate that. In your case, you can create a helper column like this on your target page:

    On your same page, your formula will look like this:

    =INDEX(COLLECT({Area CD}, {Area Index #}, [HelperRowNumber]@row), 1)

  • Jen S.
    Jen S. ✭✭

    @Lucas Rayala I didn't get a notification either, so hopefully that's fixed. So, I added the helper row and used this formula:

    =IFERROR(INDEX(COLLECT({Area}, {Area Index#}, Index@row), 1), "")

    This is working beautifully. However, back in my Master, if I add a row with a diff CD index between two existing rows, it is breaking my numbering formula and then my totals. This is the formula I'm using and even though it I can't make it a column formula it is auto filling:

    =IF(COUNT(DISTINCT([Area CD]$1:[Area CD]@row)) <> COUNT(DISTINCT([Area CD]$1:[Area CD]15)), COUNT(DISTINCT([Area CD]$1:[Area CD]@row)))

    Any thoughts?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!