IF/HAS/INDEX Formula still continues to index cells even when criteria are not met?

WCantrill

Hi,

I am using the following formula to index which clinician has completed a particular type of service. The formula seems to be pulling the correct clinician when the criteria are met but continues to pull a clinician even when criteria are not met (ie for other service types):

=IF(HAS(Service Type-Source Sheet}, A$1:A$3), INDEX({Clinician}, [Row ID]@row))

I want the formula to ignore indexing cells where the criteria have not been met in the same row, but that doesn't seem to be happening at the moment and I'm not sure why.

I've attached images and removed sensitive information

WCantrill

I was able to find an alternative solution by instead collecting all unfiltered data with a simple index formula and creating a report on that sheet with the filtered information I needed.

Zachary Hall

I believe this isn't performing the function correctly due to how the HAS() was built. Try:

• =IF(HAS(A$1:A$3, [Service Type]@row), INDEX({Clinician}, [Row ID]@row))

WCantrill

Hi Zachary,

Thankyou for responding. I have tried the above, it still runs but returns all cells blank. Doesn't the criteria (A$1:A$3) have to sit in the 2nd part of the HAS function? The formula you showed above has this as the search range.

I need the formula to search through the "Service Provided" Column in the first image, for the criteria outlined in Column "A" in the 2nd image, and where it finds those terms it should indicate the clinician responsible.

Zachary Hall

What I was attempting to provide was a column formula so that for each row where the criteria ([Service Type]@row) matches one of the values in the range (A$1:A$3) it would return the Clinician's name. The criteria does need to be in the second part, but you need that to be a single data point and not a range. The criteria should be some data point extracted from the row if you want it to generate other data from that row. If you were set on comparing those values to your initial range you could amend it to:

• =IF(OR(HAS({Service Type-Source Sheet}, A$1), HAS(Service Type-Source Sheet}, A$2), HAS(Service Type-Source Sheet}, A$3)), INDEX({Clinician}, [Row ID]@row))

It is possible I misunderstood what you were looking for though. What was your initial {Service Type-Source Sheet} range? (side note - the formula you posted initially was missing the starting bracket "{" )

WCantrill

Thanks Zach,

I think I understand what you meant by your first formula, and I realise, in my earlier formula I mislabeled the column I wanted to pull data from as "Service Type" (on the target sheet), when I meant to say "Service Provided" (on the source sheet).

So my original formula should have read:

=IF(HAS({Service Provided-Source Sheet}, A$1:A$3), INDEX({Name of Clinician}, [Row ID]@row))

Understanding this I have been able to use your formula to correctly display a clinician (or Not) when I reference the "Service Type" Column on my target sheet (by physically typing different options into that column). However, doing it this way means I still need to filter types of service from my source sheet on my target sheet. I should mention the source sheet (1st image) is a form filled in by clinicians where they can choose up to 16 different "services provided". I only want to filter 3 of these (Clinical Meeting, Clinical Audit and Training Service) into the target sheet and then name the clinician. The target sheet will be turned into a report that will be displayed on a dashboard.

So now I'm using your first formula, which seems to work when the correct information is displayed in the "Service Type" Column (Target Sheet):

=IF(HAS(A$1:A$3, [Service Type]@row), INDEX({Clinician}, [Row ID]@row))

and now I need to work out how to filter the 3 service types I want of the 16 available into the "Service Type" Column (on the target sheet) FROM the Service Provided Column in the Source Sheet. I have the original problem again where it displays whatever service is listed in each row rather than only the ones I want.

I apologise if I am being confusing. Still wrapping my head around smartsheet formulas and which ones to use in which circumstance

Zachary Hall

Okay. So how do you determine the 3 service types you want to pull from the Service Provided Column? It sounds like you need those to be variable. Is it the three most common types? I'd be happy to assist with that part of the formula if you still need some guidance.

WCantrill

When the clinician selects one of those 3 service types on the form (source sheet). The formula should identify that service type and link it to the target sheet (the target sheet will become a report that will be displayed on a dashboard).

They aren't common any more than other types of service per say. The form is filled in weekly, there would be at least one entry of that type a week.

WCantrill

I was able to find an alternative solution by instead collecting all unfiltered data with a simple index formula and creating a report on that sheet with the filtered information I needed.