Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Quote:
What about if it appears in more than one of the columns? |
#3
|
|||
|
|||
Quote:
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. |
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
Brilliant! Thank you for your help, especially so quickly!
Much appreciated! |
#6
|
|||
|
|||
Quote:
Glad to help :) |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
Quote:
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 |
#9
|
|||
|
|||
Quote:
Last edited by Galant Koh : November 9th 12 at 12:50 PM |
#10
|
|||
|
|||
Quote:
If not, would anyone else able to help me out with a solution for this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for NON-presence of a string in another string | Excel Worksheet Functions | |||
Detect Presence Formula | Excel Worksheet Functions | |||
please help me mark their presence. | Excel Worksheet Functions | |||
count their presence | Excel Worksheet Functions | |||
Sheet presence? | Excel Programming |