INDEX COLLECT Formula Help

Michelle Fayed

Good afternoon!

I have the following formula written to index collect a number from my master sheet and I am utilizing 2 sheets as references in the formula:

=INDEX(COLLECT({PSTrax SR #}, {PST Station #}, [ASSIGNED TO]@row, {PST Issue}, Issue@row, {PST Problem Type}, [Issue Helper]@row, {PSTrax Type Master}, TYPE@row, {PST Open Date}, >=DATE(2023, 1, 1), {PST Open Date}, <=DATE(2023, 2, 1))), 1)

It is coming back #UNPARSEABLE. I tried adding =IF([ASSIGN TO]@row, 1, to the front of the formula, but it only returns a 1. Help?

Thank you in advance!

Samuel Mueller

@Michelle Fayed I think you have 1 too many parentheses. try below

=INDEX(COLLECT({PSTrax SR #}, {PST Station #}, [ASSIGNED TO]@row, {PST Issue}, Issue@row, {PST Problem Type}, [Issue Helper]@row, {PSTrax Type Master}, TYPE@row, {PST Open Date}, >=DATE(2023, 1, 1), {PST Open Date}, <=DATE(2023, 2, 1)), 1)

Emilio Wright

Hello,

Fistly, I tossed your function in VSCode and noticed you had an extra parenthesis.

Try it like this instead:

=INDEX(COLLECT({PSTrax SR #}, {PST Station #}, [ASSIGNED TO]@row, {PST Issue}, Issue@row, {PST Problem Type}, [Issue Helper]@row, {PSTrax Type Master}, TYPE@row, {PST Open Date}, >=DATE(2023, 1, 1), {PST Open Date}, <=DATE(2023, 2, 1)),1)

Michelle Fayed

@Samuel Mueller @Emilio Wright Thank you both for taking the time to help me. I corrected the issue with the parenthesis, but now have a #INVALID VALUE error. Any ideas?

Here are some pictures from those sheets

Image 1 and 2: The Master Sheet which is referenced in the formula. All field referenced are filled in:

Image 3: The master match sheet referenced in the formula ({PSTrax Type Master})

Image 4: The sheet you have been kind enough to help me build the formula for:

I really appreciate your assistance with this! Thank you SO much for your time and attention!

Paul Newcome

What number comes up when you enter this formula?

=COUNTIFS({PST Station #}, [ASSIGNED TO]@row, {PST Issue}, Issue@row, {PST Problem Type}, [Issue Helper]@row, {PSTrax Type Master}, TYPE@row, {PST Open Date}, >=DATE(2023, 1, 1), {PST Open Date}, <=DATE(2023, 2, 1))

Michelle Fayed

@Paul Newcome "0" comes up? I added some images to my post from earlier this morning if it helps?

Samuel Mueller

@Michelle Fayed If I'm reading this right, you can't have 2 different ranges in the same formula. It appears that you have a range on the first sheet, and then a range on the second sheet ({PSTrax Type Master}). remove the PStrax type piece and then what do you get?

=INDEX(COLLECT({PSTrax SR #}, {PST Station #}, [ASSIGNED TO]@row, {PST Issue}, Issue@row, {PST Problem Type}, [Issue Helper]@row, {PST Open Date}, >=DATE(2023, 1, 1), {PST Open Date}, <=DATE(2023, 2, 1)), 1)

Paul Newcome

I agree with @Samuel Mueller that you cannot reference two different sheets within the same function, but that means there should have been an error from the COUNTIFS instead of a zero.

The zero leads me to believe that all ranges are referencing the same sheet but that there are no rows that match.

What exactly is it you are wanting to accomplish between these three sheets? Knowing the end goal will help in finding the appropriate solution.

Michelle Fayed

@Samuel Mueller @Paul Newcome

When I use the formula you gave me it populates the correct number, however, I need the formula to reference for Dryer on the second sheet because there are multiple PM- Appliance types for Station 57. The master sheet does not identify the specific type.

Do you think adding a helper column to my master sheet would be helpful? So if the Problem column contains Dryer PM the helper column has a formula to match it to Dryer? See below (don't judge all my open windows please)

And if so, what kind of formula would you use given the fact that there are over 30 types of PM in the system (image above of the 27 I have entered for now)?

Samuel Mueller

@Paul Newcome I bet the ranges were the same length by chance (50) making the countifs work..

Michelle could you add a dropdown that includes the 30 different PM types in addition to the description? if it's just free text you will likely miss some due to spelling

@Paul Newcome I did confirm that as long as the ranges are the same size it will not throw an error, even if they are on a different sheet. However this doesn't solve anyones problem but it makese sense why 0 was returned.

Michelle Fayed

@Samuel Mueller so add a dropdown helper column, but what would the formula be? IF(CONTAINS) ?

Samuel Mueller

@Michelle Fayed how are you receiving the information? Is someone filling out a form or filling out the grid. Thinking of someone was specifically choosing the PS Trax Type - then if they choose the drop down (no formula), you can reference that column in your collect formula.

Paul Newcome

@Samuel Mueller It should still throw an error even if they are the same size/shape since they are on two different sheets. At least that's how it has always worked for me.

@Michelle Fayed I would definitely recommend getting as much data onto a single sheet as possible, but I'm not sure I follow the workflow to be able to make a suggestion on how.

How are each of these sheets being used/populated, and what is the expectation?

Michelle Fayed

@Samuel Mueller the information in populated daily through a data shuttle. It's not hand populated.

Samuel Mueller

@Michelle Fayed I am a little confused I think. What sheet are you putting this collect formula on? On the master sheet you already have "Type" why can't you use that?

Samuel Mueller

Can you provide like a short start to finish of your process?