I Need help and support on a formula I am using

Jennifer Henry

=IF(HAS({1st day of the week}, Date@row), OR(HAS({2nd day of the week}, Date@row), INDEX(COLLECT({Clinical Placement Tool Status}, {Hospital Site Name}, [Hospital Name]@row, {Unit Name}, [Unit Name]@row), 0)))

Is the formula I am using it is partially working. When I look at the results in column where it should be pulling data it is coming back as invalid data. See Below:

The row that is stating "OPEN" is where the formula is working perfectly but the two rows beneath have the same formula and should be pulling back "Approved" but it's pulling an invalid data type. Please help

Paul Newcome

You would use another IFERROR.

=IFERROR(original_formula, "Open")

Paul Newcome

Try this instead:

=IF(OR(HAS({1st day of the week}, Date@row), HAS({2nd day of the week}, Date@row)), INDEX(COLLECT({Clinical Placement Tool Status}, {Hospital Site Name}, [Hospital Name]@row, {Unit Name}, [Unit Name]@row), 1)))

If that doesn't work, what exactly are you wanting the formula to do?

Jennifer Henry

It's working on the first half of the sheet but now I am noticing it's pulling invalid data type for the 2nd half of the sheet.

First Half it's pulling exactly how it should

but in the same sheet further down I have the same dates but this time for a different unit and it's pulling invalid value

On my cross reference sheet I have request with several dates for several different units. I need the formula to pull over the status of the request if it matches any of the dates but as long as it also matches the unit.

Let me know if I need to send any other details. I really appreciate the help!

Paul Newcome

Can you provide screenshots of both sheets to include the column headers with some of the data?

Jennifer Henry

First Sheet where we need the data to pull over to

Second Sheet where the data lives. The Status column is the column I need to pull data from. As long as the hospital name in the primary sheet matches the hospital names in the second sheet, if the unit in the primary sheet matches the unit in the secondary sheet, if the matches any of the dates in the date columns.

Paul Newcome

Ok. This helps. Now… I notice you have a cross sheet reference for {1st day of the week}, and you are looking for a match on Date@row. Is that cross sheet reference pointing at a date column, or is it pointing at one of those columns there in the screenshot of the reference sheet?

Jennifer Henry

The cross sheet reference is pointing at the 1st day of the Week Date column in the second sheet. As you can see it does contain a formula and column properties are set as a Date

Paul Newcome

Ah. Ok. Sorry about that. for some reason that screenshot in the middle didn't load the first time I looked at it. Try this:

=IFERROR(INDEX(COLLECT({Clinical Placement Tool Status}, {Hospital Site Name}, @cell = [Hospital Site]@row, {Unit Name}, @cell = [Unit Name]@row, {1st day of the week}, @cell = Date@row), 1), INDEX(COLLECT({Clinical Placement Tool Status}, {Hospital Site Name}, @cell = [Hospital Site]@row, {Unit Name}, @cell = [Unit Name]@row, {2nd day of the week}, @cell = Date@row), 1))

Jennifer Henry

Coming back as Invalid Value

I really do appreciate you working with me on this. I've tried to figure this one out for several weeks now and can not get it to pull correctly

Paul Newcome

Is it returning that error even when it should be returning a status?

Jennifer Henry

No when it returns a status it's good it's returning the invalid value when there is no status to return? How can I either get it to return "" or "Open"? I tried to add this at the end but then it's saying incorrect algorithm

Paul Newcome

You would use another IFERROR.

=IFERROR(original_formula, "Open")

Jennifer Henry

Thank you Paul. This formula is working

Paul Newcome

Happy to help. 👍️