How to check 3 cells in a row to reply yes or if first 2 are yes and 3rd is no reply no

Ok so first off.. my first post.. new member so ill say hi here for now and find the checkin forum later...


ok so i have an xls now that is pretty simple but keeping it simple requires so many repetitive tasks...

this part will be overal why and how for info then off to the needed help.

i can give this to you in a nutshell.
We use Altiris Client Management suite to manage our computers and it does a nice job. We just upgraded to the latest version and now we found an issue. a sql table that records information has a bug and there is a fix. well that fixes that for us but we lost the installed records for some machines. There are other tables that have this information too but different pieces.

so I took an xls and did this.

1. took computers and put them in row a of sheet 1
2. ran sql query against the table to get individual software like MS13-001, MS13-002, etc.
3. placed all of those into new sheets
4. back on sheet 1 i made the rows of ms13-001(b1), ms13-002(b2), etc

for say ms13-001 I went to cell B2 as the first computer name starts there and I said

=IF(ISNA(VLOOKUP(A2,'MS13-001'!A:A,1,FALSE)),"No","Yes")

drug that down the list for all cells in that row.. this works great.. even setup the conditional formatting for yes = green and no = orange.

now this worked but is out dated now and I have oh say 50-60 sheets to update..

so what I would like to do is this..

start over but have sheet 1 the same as above.
take sheet 2 and drop the entire list of all the software

what I cant figure out is how to then back on sheet 1 is to put a yes for say....

if computer listed in A2 is in sheet 2 row a
and
row b contains MS13-001 (i say that because microsoft has so many .exe and .msu so would be easier to do a contains ???)
row c contains success

so hopefully I didnt loose ya there..


Machine Name AdvertisementName Status End
MXL1340GKB Windows6.1-KB2769369-x86.msu for MS13-001 Command executed 2013-01-16 03:02:54.000

Thank you very much. Ok lets see if this has enough information..

attached has 3 sheets.
Overview
Overview Breakdown
Full Report

__________________________________
Overview - would like it to show a count of unique computers from full report that have installed so success only but if a computer lists it 5 times only want to count it once
Overview Breakdown - lists each computer and yes/no if they received a success of the patch
Full Report - page for me to just paste in the report after I run it with no modifications


so I was able to do this with simply breaking down my sheets to the specific patch but that takes too long and especially when you have a ton of them. to update takes forever. would be nice to just paste into one sheet and have it calculate them all.

so i just dont know how to do the yes/no when a computer is in row A, patch in row B, and Success in row D.


Thanks!

looks good.. ill try and put my full report in and try and get real computer names in there to see.. but yes that appears to be the multi rows im looking to do on this.

im starting slow. full report and 5 computers and have been getting weird results. I then sorted the report by computer name and seems to be a bit better.
is there a trick you should do?

boy this report takes time to run when it updates so i have it set to manual calculate

this is odd.. at line 489.. it all changes to no.. no matter what i try.. up to this the entire sheet works.. but after line 489.. no way..
is there a forumula limit?
office 2007???

Originally Posted by martindwilson

nope cant see why it wouldn't work after 489
mind you i dont know how big your sheet is
B$1:$B$10000 change all those ranges to the smallest needed
what does your actual formula say in 489

i didnt actually notice it was set to 10,000 as i thought it was 100,000 so that was my issue!!!!

so now i have my report done my way.. very slow way..

so now im going to try and convert it over to how yours is... only downside i see here is that it still takes 10 minutes roughly to update!!! and that is not with all added yet...