Hi guys,

I need some help to figure out the formula to be used on an inventory sheet.

I need to check if a certain value (text/numeric combo, eg. "POLP1003") appears in a specifici column on several different sheets and then to return a value of "No data" if the value does not appear at all.

The pseudo-formula I have so far is:

=IF(OR('Active Service'!C:C=A6; Spare!A:A=A6; 'In Repair'!A:A=A6; Retired!A:A=A6),,"No data")

(Where A6 is the cell containing the item name I am searching for)

eg. A6 = "POLP1003" I want to check for that value in Column C in one sheet, Column A in the next worksheet, Column A in the third worksheet and finally Column A in the fourth worksheet. If that value does not appear in any of the worksheets, I need, "No data".

Thanks.

What do you want the result to be if the value in A6 IS found in one of the columns?

What about if it appears in more than one of the columns?

If a value is returned it can be left blank.

If a value appears more than once it doesn't matter. The idea of this formula is to check for the existence of a physical item in one or more locations where the item could move between locations. Therefore, if an item exists, it will appear on one of the lists. If it does not appear on the list then it means that it no longer exists and I need to be notified.

Have a look at the attached. This is one way to do it.

Brilliant! Thank you for your help, especially so quickly!

Much appreciated!

Not a problem.
Glad to help :)