Issue with If and Index

Shahara Anderson

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

Ryan Sides

@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

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?

Shahara Anderson

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

Ryan Sides

@Shahara Anderson do you have different sheets for SA04, SA11, SA12, etc? Or are they all listed on the same "detail" (second snip) sheet?

Shahara Anderson

@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

Ryan Sides

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

Shahara Anderson

@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

Ryan Sides

You too! Enjoy.