IFERROR, Index and help!!

Jen S.

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.

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.

@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

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.

@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?