# IF cell is Blank

✭✭✭

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); ""))))))

Tags:

• ✭✭✭