Excel Formula Advice Needed

[Galant]

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 specific 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".

As always, thanks for your help!

[Phage]

I'm no expert but couldn't you use a COUNTIF function ?

[Galant]

How?

I'm not sure COUNTIF provides the option to dictate a response depending on the result.

I've tried this formula using COUNTIF: =COUNTIF(('Active Service'!C; Spare!A:A; 'In Repair'!A:A; Retired!A:A);A6)

But it doesn't like at least one value in the formula.

[blueball]

I would use

=IF(OR('Active Service'!$C:$C=$A$6,Spare!$A:$A=$A$6,'In Repair'!$A:$A=$A$6,Retired!$A:$A=$A$6),,"Not Found")

The $ signs mean I can drag the formula around without losing the target cells.
You need to separate the checking values with commas not semi-colons.
Also I prefer "Not Found" to "No Data" as it is a more truthful answer

[Galant]

Okay, it doesn't like the commas, but if I divide them with semi-colons I then get a working formula - sort of. In my first test it returned the value "Not found" when it shouldn't have.

=IF(OR('Active Service'!$C:$C=$A$6;Spare!$A:$A=$A$6;'In Repair'!$A:$A=$A$6;Retired!$A:$A=$A$6);;"Not Found")

???

[TheAnimus]

Hard to really help without having the sheet or making a dummy one.

If you also use Named Ranges it will make reading the formula a lot easier.

The ; and , are due to localisation settings, I'm guessing your not using English

[sweatherly]

Not sure whether I entirely understand your problem but wouldn't something like this suffice?
=IF(COUNTIF(Sheet2!B:B,A6)+COUNTIF(Sheet3!C:C,A6)<1, "not present", "")

[sweatherly]

You could also check whether a particular value existed is every column using:
=IF(PRODUCT(COUNTIF(Sheet2!B:B,A6),COUNTIF(Sheet3!C:C,A6)),0)

[oldboy47]

Ctrl F then polp1003 with the search on the book rather than the sheet.

[Galant]

Thanks for your input guys. It's now solved. The solution was:

=IF(OR(COUNTIF(Retired!A:A;'Births & Deaths'!A6)>0;COUNTIF('Active Service'!C;'Births & Deaths'!A6)>0;COUNTIF(Spare!A:A;'Births & Deaths'!A6)>0;COUNTIF('In Repair'!A:A;'Births & Deaths'!A6)>0);"Check Status";"Not found")


PS - Looks like we need an option to disable smiley code. For C : D .