Formula to check presence of value between sheets

[Galant Koh]

Hi guys,

I need some help to figure out the formula to be used on an inventor
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 t
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 on
sheet, Column A in the next worksheet, Column A in the third workshee
and finally Column A in the fourth worksheet. If that value does no
appear in any of the worksheets, I need, "No data".

Thanks

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

Hi guys,

I need some help to figure out the formula to be used on an inventor
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 t
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 on
sheet, Column A in the next worksheet, Column A in the third workshee
and finally Column A in the fourth worksheet. If that value does no
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 o
the columns?

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Galant Koh]

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

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

Much appreciated

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

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

Much appreciated!

Not a problem.
Glad to help :

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Galant Koh]

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 wit
it an additional function would be useful.

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

To summarise, if the value is false it will return "No data", but if th
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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[jack_n_bub]

Can I be a pain?

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

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

To summarise, if the value is false it will return "No data", but if th
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 a
exactly the same way. It is definitely possible to return what you ar
asking for. Here is a formula that would return the sheet name in th
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 informatio
from built in functions. Also looking at the existing way the formula i
return there can be some issues later such as what happens when someon
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 wit
you if you are happy with this before I can create a custom function fo
you.

Let me know your thoughts.

Prashan

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Galant Koh]

Hi,

Is it alright for you to use a VBA custom function which works in a
exactly the same way. It is definitely possible to return what you ar
asking for. Here is a formula that would return the sheet name in th
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 informatio
from built in functions. Also looking at the existing way the formula i
return there can be some issues later such as what happens when someon
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 wit
you if you are happy with this before I can create a custom function fo
you.

Let me know your thoughts.

Prashant

I don't actually know what a custom VBA function is. I don't imagine I'
have any problems with it. Can you clarifiy?

Thanks for all your help

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Galant Koh]

EDIT - Okay, a bit of self-education and a custom VBA would be fine. Le
me know if you need anything from me. This is really kind of you. Than
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

+-------------------------------------------------------------------
+-------------------------------------------------------------------