Help with a formula to determine data presence in other worksheets in the same workbook

[chadmjohn@gmail.com]

I have a workbook with 20+ worksheets in it. Each worksheet contains a
series of columns where generally the 'key' (not a real key but the
term will suffice for this example) is in the first column and various
attributes of that key exist in subsequent columns.

I have a summary sheet as the first one and what I want is a base
formula that will tell me if the value I'm looking for exists in a
target worksheet (the 'key' column) and if it does, does the value in
one of the subsequent 'value' columns match some pattern.

I have searched around but I don't really know what features, methods
etc I am looking for so it's a little hard. Below are some examples of
what I had tried.


In this example "C$1" contains the name of a server. This works to
find the name but I wanted to be able to link the PASS/FAIL status to
some additional attribute in the subsequent columns in
"$B$23:$B$65536".
IF((VLOOKUP(C$1,$B$23:$B$65536,1,FALSE)=C1),"P","N")

I have also tried the above formula combined with an 'AND' but that did
not yield what I was after.

As a basic example, I have a list of server names in Worksheet1!B1 - G1
and want to check each server name for presence in Worksheet2!A2-A100.
If the server exists in the list on Worksheet2!A2-A100 AND it's
corresponding (same row) value in Worksheet2!B2-B100 is equal to some
value, return true.

Any help is truly appreciated.

[Ardus Petus]

=IF(AND(NOT(ISNA(MATCH(B1,Sheet2!$A$2:$A$100))),VLOOKUP(B1,Sheet2!$A$2:$B$10
0,2,FALSE)=7),TRUE,FALSE)

HTH
--
AP

<chadmjohn@gmail.com> a écrit dans le message de
news:1146076780.456512.201390@t31g2000cwb.googlegroups.com...
> I have a workbook with 20+ worksheets in it. Each worksheet contains a
> series of columns where generally the 'key' (not a real key but the
> term will suffice for this example) is in the first column and various
> attributes of that key exist in subsequent columns.
>
> I have a summary sheet as the first one and what I want is a base
> formula that will tell me if the value I'm looking for exists in a
> target worksheet (the 'key' column) and if it does, does the value in
> one of the subsequent 'value' columns match some pattern.
>
> I have searched around but I don't really know what features, methods
> etc I am looking for so it's a little hard. Below are some examples of
> what I had tried.
>
>
> In this example "C$1" contains the name of a server. This works to
> find the name but I wanted to be able to link the PASS/FAIL status to
> some additional attribute in the subsequent columns in
> "$B$23:$B$65536".
> IF((VLOOKUP(C$1,$B$23:$B$65536,1,FALSE)=C1),"P","N")
>
> I have also tried the above formula combined with an 'AND' but that did
> not yield what I was after.
>
> As a basic example, I have a list of server names in Worksheet1!B1 - G1
> and want to check each server name for presence in Worksheet2!A2-A100.
> If the server exists in the list on Worksheet2!A2-A100 AND it's
> corresponding (same row) value in Worksheet2!B2-B100 is equal to some
> value, return true.
>
> Any help is truly appreciated.
>

[chadmjohn@gmail.com]

Excellent, this provided exactly what I needed. I had to tweak it a
bit but I ended up with this:

=IF(AND(NOT(ISNA(FIND(C$1,VLOOKUP(C$1,'Worksheet2'!$A$2:$B$107,1,FALSE)))),NOT(ISNA(FIND("-r--------
1
root",VLOOKUP(C$1,'Worksheet2'!$A$2:$B$107,2,FALSE))))),"Pass","Fail")

Maybe not the most visually apealing, but it works!

Now that this is working it brings up another issue (just discovered
once this worked) is that the secondary worksheet where the values are
held can contain multiple rows for each server name (C$1) in the
example above. Is there a way to make this single statement evaluate
to a summary TRUE / FALSE (Pass / Fail)?

By that I mean I want to do the above evaluation, but for every row
which matches C$1 in Worksheet2, so if there are 10 rows and 9 of them
match I want a summary "FALSE / Fail" returned.