Issue with If and Index
Hello,
I have two sheets, and I am attempting to use Index/Match to reference a cell on another sheet. My issue is due to the multiple if statements in the cell already.
The following formula is in the cell:
=IF(Confirmation4 = 1, "STOPPED", IF(Confirmation21 = 1, "EXPIRED - PROVISIONAL", IF(Confirmation37 = 1, "REJECTED – APPEALING", IF(Confirmation38 = 1, "APPROVED", IF(Confirmation65 = 1, "EXPIRED - FULL", IF(Confirmation6 = 1, "IN PROGRESS", "PENDING"))))))
My Index formula is:
=INDEX({Master Patents Projects Status}, MATCH(Hierarchy1, {Master Patents Projects Range 2}, 0))
I need help to nest the formulas, I have tried, and I keep receiving a #unparseable.
Thanks!
SA
Best Answer
-
@Shahara Anderson Ok. And do you have more than 50 Lines/Hiearchies? The reason I ask is because you can only have 100 cross sheet references to other sheets and you'll be using 2 references per sheet.
Another approach to this would be to create a Sheet Summary field (right hand icon list) that indicates the Current "Status" of that detail sheet (based on the checkboxes). Then create a report that brings all of those sheets together and shows the Current status of each.
I don't know what other detail you have on your current summary sheet to know if that's a viable option or not, but that truly is the purpose of the report; to list data points from many different sheets.
Is that a possible option?
Answers
-
Hi @Shahara Anderson You'll replace your cell reference with the index formula...
Confirmation4 should be replaced by INDEX({Master Patents Projects Status}, MATCH(Hierarchy1, {Master Patents Projects Range 2}, 0))
And then change the Hierarchy1 to match the Confirmation4 (or whatever you're wanting to use to lookup on the other sheet).
So...
=IF(INDEX({Master Patents Projects Status}, MATCH(Hierarchy1, {Master Patents Projects Range 2}, 0)) = 1, "STOPPED", IF(.......
Does that help?
-
Hi, @Ryan Sides . Thank you for answering so quickly. I tried the solution, but it is not working for me. Heirarchy1 is what I am using to look up information on the other sheet. I pasted two snips below. The first snip is a Master file that lists summary information for each sheet, and the second snip contains the detail. When a box is checked in the detail sheet, I want to update the summary sheet by matching the Line and the Hierarchy1(SA04)
Thanks,
Shahara
-
@Shahara Anderson do you have different sheets for SA04, SA11, SA12, etc? Or are they all listed on the same "detail" (second snip) sheet?
-
@Ryan Sides yes, they all have a sheet. I have not linked them to the Master Summary sheet yet. Every time a new detail sheet is created, it is listed on the summary sheet
-
@Shahara Anderson Ok. And do you have more than 50 Lines/Hiearchies? The reason I ask is because you can only have 100 cross sheet references to other sheets and you'll be using 2 references per sheet.
Another approach to this would be to create a Sheet Summary field (right hand icon list) that indicates the Current "Status" of that detail sheet (based on the checkboxes). Then create a report that brings all of those sheets together and shows the Current status of each.
I don't know what other detail you have on your current summary sheet to know if that's a viable option or not, but that truly is the purpose of the report; to list data points from many different sheets.
Is that a possible option?
-
@Ryan Sides, thank you for your help and time. The Summary Sheet option worked perfectly. I appreciate your time and patience. Have a great evening!
Best,
Shahara
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!