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
-
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?
-
@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)
-
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)
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!