Formula to check presence of value between sheets
 

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 :)

Can I be a pain?

I know this isn't what I originally requested but now I've played with it an additional function would be useful.

Is it possible, instead of leaving the IF TRUE value blank, to have it return the name of the sheet on which the searched value (in this case A6) was found?

To summarise, if the value is false it will return "No data", but if the value is true, then it will return either, "Active Service", "Spare", "Retired", "In Repair", depending upon where the value was found?

That would be really helpful.

Thanks.

Hi,

Is it alright for you to use a VBA custom function which works in an exactly the same way. It is definitely possible to return what you are asking for. Here is a formula that would return the sheet name in the sheet it is used.
=RIGHT(CELL("filename",B1),LEN(CELL("filename",B1) )-FIND("]",CELL("filename",B1),1))

As you see it is a cumbersome process to get such a simple information from built in functions. Also looking at the existing way the formula is return there can be some issues later such as what happens when someone inserts a new sheet. You will have to modify your formula.

I would suggest a VBA function for such a task but want to check with you if you are happy with this before I can create a custom function for you.

Let me know your thoughts.

Prashant

EDIT - Okay, a bit of self-education and a custom VBA would be fine. Let me know if you need anything from me. This is really kind of you. Thank you!

Hi Jack, are you able to proceed with this?

If not, would anyone else able to help me out with a solution for this?

Thanks.