Need to Create Separate Opens List from Data Sheet

PrismPSG

Summary: I have a data sheet with a primary column listing different equipment items. Going across, there are a number of columns, 15 of which have drop downs with options N, N/A, Y, and Open Item... I need to populate a list, to a different sheet, showing only the columns, where that piece of equipment shows, Open Item.

Issue: I need a way for the Open's list to populate a cell with the title of the column designated as open. I also need the list to automate the open when triggered. There may be multiple opens on one row in different columns during different phases, however, each "open" needs to be tracked separately.

Please help! I have researched all types of formulas but non of which seem to fit what I need.

(See attached snippet of sample sheet, and example of what I would like the opens list to look like.)

Paul Newcome

The first thing you will need is a helper column with a formula to list out all column names that have "Open Item" on each row.

=IF([Column A]@row = "Open Item", "Column A", "") + IF([Column B]@row = "Open Item", "Column B", "") + IF([Column C]@row = "Open Item", "Column C", "")

Then you would create a report that pulls in rows where this helper column is not blank along with whatever other columns you want to display.

PrismPSG

AMAZING! Thank you so much for the quick response! That worked!!! You saved me so much time! I need to take some courses on formulas!

Thank you!!!

Paul Newcome

You would add the character after each of the column names. I would suggest a line break and then apply text wrapping on the column.

=IF([Column A]@row = "Open Item", "Column A" + CHAR(10), "") + IF([Column B]@row = "Open Item", "Column B" + CHAR(10), "") + IF([Column C]@row = "Open Item", "Column C" + CHAR(10), "")

Paul Newcome

The first thing you will need is a helper column with a formula to list out all column names that have "Open Item" on each row.

=IF([Column A]@row = "Open Item", "Column A", "") + IF([Column B]@row = "Open Item", "Column B", "") + IF([Column C]@row = "Open Item", "Column C", "")

Then you would create a report that pulls in rows where this helper column is not blank along with whatever other columns you want to display.

PrismPSG

AMAZING! Thank you so much for the quick response! That worked!!! You saved me so much time! I need to take some courses on formulas!

Thank you!!!

PrismPSG

Can you tell me how to add a space or comma in the formula, to separate the items that populate in the column?

I have only added the first 2 columns to the formula so far.

Andrée Starå

Hi @PrismPSG

I hope you're well and safe!

Are your columns after each other, or could they be spread out in the sheet?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You would add the character after each of the column names. I would suggest a line break and then apply text wrapping on the column.

=IF([Column A]@row = "Open Item", "Column A" + CHAR(10), "") + IF([Column B]@row = "Open Item", "Column B" + CHAR(10), "") + IF([Column C]@row = "Open Item", "Column C" + CHAR(10), "")

PrismPSG

Thank you @Paul Newcome, I applied that to my formula and it did exactly what I needed it to!

PrismPSG

@Paul Newcome, I am trying to get this project complete by tomorrow with a lot more automation and formatting to get it perfect.

With the items for each piece of equipment being listed in the report in a single column, I have now added a column to the main sheet where I need a formula to count the amount of opens in the row so the report can account for 2-3 opens being on one item rather than counting it as 1, however, there are a number of scenarios it needs to account for...

**Note: There are 3 stages to each row, scenarios will be the same for all stages, other than the column names. Scenarios as labeled in snippet. The key to the formula will mainly be determined on the last column of each stage being checked. SOME STAGES COULD BE COMPLETE BEFORE THE PRIOR, so there may be opens in stage 1, when stage 2 has been marked complete.

Example: If column 'Stage 2 Complete' is checked, then the new column should populate back to '0', even if there technically still opens for that stage. I have already programmed that 'Open item' to automate to 'WIP' if this should ever happen and I can track and differentiate opens that were handled vs opens that were left open but the Client has decided to override and not officially close.

If the stage complete box is unchecked, the new column should populate the actual number of columns in the row that say 'Open Item'.

Paul Newcome

I understand all of that except...

In the row for "Test 3" you have the number 2 in this new column. None of the Complete boxes are checked, but there are three opens in that row. Should that be 3, or (if it is supposed to be 2) can you explain that logic a little more?

PrismPSG

@Paul Newcome I understand that confusion. If the client overrides any 'Open Items' left in the row by checking the Stage 3 column, 'Overall Completion' then I will have the sheet auto check the stage 1 and 2 boxes and replace the cell text with WIP. Therefore, if the stage 3 'Overall completion' is checked, the new column should show a value of '0'. Does that make sense?

Paul Newcome

I understand that, but I am talking about the row that specifically says "Test 03" in the Equipment Name column. You have no complete boxes checked, there are 3 open items, but in the # of Opens column you have the number 2.

PrismPSG

Oh I see. I caught that yesterday, there was a mistake in my formula, that has now been corrected. So yes, that new column should show '3'.

PrismPSG

@Paul Newcome, Any chance you can help me with the final formula for the # of opens column? Appreciate all your help!

With the items for each piece of equipment being listed in the report in a single column, I have now added a column to the main sheet where I need a formula to count the amount of opens in the row so the report can account for 2-3 opens being on one item rather than counting it as 1, however, there are a number of scenarios it needs to account for...

**Note: There are 3 stages to each row, scenarios will be the same for all stages, other than the column names. Scenarios as labeled in snippet. The key to the formula will mainly be determined on the last column of each stage being checked. SOME STAGES COULD BE COMPLETE BEFORE THE PRIOR, so there may be opens in stage 1, when stage 2 has been marked complete.

Example: If column 'Stage 2 Complete' is checked, then the new column should populate back to '0', even if there technically still opens for that stage. I have already programmed that 'Open item' to automate to 'WIP' if this should ever happen and I can track and differentiate opens that were handled vs opens that were left open but the Client has decided to override and not officially close.

If the stage complete box is unchecked, the new column should populate the actual number of columns in the row that say 'Open Item'.

Paul Newcome

My apologies for the delay. I have been traveling and at a workshop since Friday.

Here is my suggestion (double check the column names):

=IF([Overall Complete]@row <> 1, IF([Stage 1 Complete]@row <> 1, COUNTIFS([Field Check and Equipment Data Gathered]@row:[Cable Code Check]@row, @cell = "Open Item"), 0) + IF([Stage 2 Complete]@row <> 1, COUNTIFS([Arc Flash Analysis Complete]@row:[Report Reviewed]@row, @cell = "Open Item"), 0) + COUNTIFS([Label Generated]@row:[Label Applied]@row, @cell = "Open Item")))

PrismPSG

I checked this a few times and will continue to play around with it, but it doesn't seem to be working. Here is what I put in. Do you see anything wrong with it? Thank you!

=IF([Overall Completion]@row < > 1, IF([Stage 1 Complete]@row < > 1, COUNTIFS([Field Check and Equipment Data Gathered]@row:[Cable Code Check]@row, @cell = "Open Item"), 0) + IF([Stage 2 Complete]@row < > 1, COUNTIFS([Arc Flash analysis complete]@row:[Report reviewed]@row, @cell = "Open Item"), 0) + COUNTIFS([Label Generated/Printed]@row:[Label Applied/ Delivered]@row, @cell = "Open Item")))

Paul Newcome

Are you getting an error message or an unexpected output? Are you able to expand more on what you mean by it isn't working?