Use which Formula to capture the 'result=Disable' in two seperate sheet?

1. Use which Formula to capture the 'result=Disable' in two seperate sheet?

I have "sheet1=Tue 6 11 2013 9 00 AM" (as Destination) and "sheet2=All Computers_AP1" (as Source) & "sheet3=All Computers_ADASIAPAC" (as Source). Problem is i'm not sure to use which Formula can sort out those result/row indicating 'Disabled' and fall that result under sheet1[Column F (for ..._AP1)] and [Column G (for ..._ADASIAPAC)]?

OR

in sheet1[Column F, showing an result say something like example:- 'Disable in 'AP1' or 'Disable in both'

Code i use to used but not able making it work

=IF(AND(ISERROR(VLOOKUP(A:A,B:B,1,0)),ISERROR(VLOOKUP(B:B,A:A,1,0))),"Disabled "&A2&" and "&B2,IF(ISERROR(VLOOKUP(A:A,B:B,1,0)),"Disabled in AP1 - "&A2,IF(ISERROR(VLOOKUP(B:B,A:A,1,0)),"Disabled in ADASIAPAC - "&B2,"Disabled in Both")))

2. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

you have
=VLOOKUP(A2,'All Computers_AP1'!A:A,2,FALSE)
the range is 1 column , but you are asking to bring back the 2nd column
so you need to extend the range
=VLOOKUP(A2,'All Computers_AP1'!A:B,2,FALSE)

same for the next vlookup
needs to be

not sure i understand how you now want to show column F criteria having pulled back the results from the two sheets in column D and E

are you looking for disabled ?
as you also have a combined
DISABLED\NO_PWDREQD

and only 1 entry in column D

3. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

Thanks etaf. My formula use to work in a single sheet but now i gonna make it work in multiple/separate sheet under one workbook. Otherwise i myself will get confuse if the 'Source' and 'Destination' all put together under one sheet, cause of the column result will keep rolling out more column showing different result.

4. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

Anyone? Forum Guru?!!

5. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

You correct etaf, i wan looking for 'Disabled' & 'Disabled\No_PWDREQD' both and show only the found result in Column D (Sheet1).

6. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

cause of the column result will keep rolling out more column showing different result.
and therefor you will need to extend the range to pull in those additional columns

I'm still not sure what you want to do now I have given the correct formula to pull back the results
what are you wanting to combine in D&E and the result needed

because you have disabled as part of another text then you would need to use

left(cell,8) to get just disabled
and can then use that to test the two columns

=IF(and (left(D2,8)="disabled" , left(E2,8)="disabled" ), "both disabled", IF(left(D2,8)="disabled", "All Computers_AP1", if (left(E2,8)="disabled","All Computers_ADASIAPAC", "none disabled")))

7. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

what are the possible codes used in the sheet for column D
just
Disabled' or 'Disabled\No_PWDREQD

=VLOOKUP(A2,'All Computers_AP1'!A:B,2,FALSE)

=IF ( OR(VLOOKUP(A2,'All Computers_AP1'!A:B,2,FALSE) = "Disabled", VLOOKUP(A2,'All Computers_AP1'!A:B,2,FALSE) = "Disabled\No_PWDREQD"), "disabled", "")

result in Column D will be disabled or blank

8. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

What if i 'combine' the two Column A and B become one Column C result? [ADHKG0_360 (Column A) NO_PWDREQD (Column B) become ADHKG0_369 NO_PWDREQD (Column C)] now the formula itself should look for the word 'Disabled' and 'Disabled\No_PWDREQD' both, how was that?

Btw, thanks for the =IF formula. I'd create additional Column, F (Disabled in AP1) and G (Disabled in ADASIAPAC); but somehow the F showing '#N/A' instead of 'Yes' ???

F (Disabled in AP1), the result show up #N/A instead of 'Yes' [=IF(OR(VLOOKUP(A2,'All Computers_AP1'!A:B,2,FALSE)="Disabled",VLOOKUP(A2,'All Computers_AP1'!A:B,2,FALSE)="Disabled\No_PWDREQD"),"Yes","")]

9. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

really sorry , dont understand the combine A and B - where would you do that ?

the #n/a is because the vlookup is not finding the computer id

add an iferror to get rid of that

=VLOOKUP(A2,'All Computers_AP1'!A:B,2,FALSE)

becomes

=iferror(VLOOKUP(A2,'All Computers_AP1'!A:B,2,FALSE),"")

that should sort out the #N/A errors from the vlookup and also the IF should now work

10. Re: Use which Formula to capture the 'result=Disable' in two seperate sheet?

This work~ thanks again etaf

=IFERROR(OR(VLOOKUP(A13,'All Computers_AP1'!A:B,2,FALSE)="Disabled",VLOOKUP(A13,'All Computers_AP1'!A:B,2,FALSE)="Disabled\No_PWDREQD"),"Disabled")

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1