IF cell is Blank

Morena

Hi

I have formula here:

IFERROR(INDEX(JOIN(COLLECT({Truck Dispatches (Ongoing Jobs)_Status}; {Truck Dispatches (Ongoing Jobs)_Truck}; [Truck reg. number]@row); UNICHAR(10)); 1)

This formula looks at the sheet {Truck Dispatches (Ongoing Jobs)_Status} and returns the Status, by comparing the Truck reg. number from two sheets.

There are 6 sheets {Truck Dispatches (Ongoing Jobs)_Status} & {Truck Dispatches (Ongoing Jobs)_Status1} & {Truck Dispatches (Ongoing Jobs)_Status2} & {Truck Dispatches (Ongoing Jobs)_Status3} & {Truck Dispatches (Ongoing Jobs)_Status4} & {Truck Dispatches (Ongoing Jobs)_Status5}

Now i want the formula to check if the cell is blank, if true to look at all sheets and return status, each sheet has unique Truck reg. number that can't be duplicated in other sheets.

Now I have created the following formula but it only looks at the 1st sheet and ignores the other:

IFERROR(INDEX(JOIN(COLLECT({Truck Dispatches (Ongoing Jobs)_Status}; {Truck Dispatches (Ongoing Jobs)_Truck}; [Truck reg. number]@row); UNICHAR(10)); 1); IFERROR(INDEX(JOIN(COLLECT({Truck Dispatches (Ongoing Jobs)_Status1}; {Truck Dispatches (Ongoing Jobs)_Truck1}; [Truck reg. number]@row); UNICHAR(10)); 1); IFERROR(INDEX(JOIN(COLLECT({Truck Dispatches (Ongoing Jobs)_Status2}; {Truck Dispatches (Ongoing Jobs)_Truck2}; [Truck reg. number]@row); UNICHAR(10)); 1); IFERROR(INDEX(JOIN(COLLECT({Truck Dispatches (Ongoing Jobs)_Status3}; {Truck Dispatches (Ongoing Jobs)_Truck3}; [Truck reg. number]@row); UNICHAR(10)); 1); IFERROR(INDEX(JOIN(COLLECT({Truck Dispatches (Ongoing Jobs)_Status4}; {Truck Dispatches (Ongoing Jobs)_Truck4}; [Truck reg. number]@row); UNICHAR(10)); 1); IFERROR(INDEX(JOIN(COLLECT({Truck Dispatches (Ongoing Jobs)_Status5}; {Truck Dispatches (Ongoing Jobs)_Truck5}; [Truck reg. number]@row); UNICHAR(10)); 1); ""))))))

Please advise

Morena

@Paul Newcome

Hi Paul, Can you advise, need your help again

Eric Law

@Morena Would creating 6 helper columns on your sheet help? Primarily, to index/match the status of each vehicle from the 6 separate sheets. Then you can just refer to those 6 columns in an easy peasy formula. 😅 Also, if you don't want to see them, you can just hide them, or create a report. Just food for thought.