+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    Sgainpore
    MS-Off Ver
    Excel 2010
    Posts
    8

    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")))
    Attached Files Attached Files
    Last edited by DonnyLau; 06-12-2013 at 08:25 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,685

    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
    =VLOOKUP(A2,'All Computers_ADASIAPAC'!A:A,2,FALSE)
    needs to be
    =VLOOKUP(A2,'All Computers_ADASIAPAC'!A:B,2,FALSE)

    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
    Last edited by etaf; 06-11-2013 at 09:46 AM.

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    Sgainpore
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #4
    Registered User
    Join Date
    06-11-2013
    Location
    Sgainpore
    MS-Off Ver
    Excel 2010
    Posts
    8

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

    Anyone? Forum Guru?!!

  5. #5
    Registered User
    Join Date
    06-11-2013
    Location
    Sgainpore
    MS-Off Ver
    Excel 2010
    Posts
    8

    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. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,685

    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. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,685

    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)

    so instead of that

    =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. #8
    Registered User
    Join Date
    06-11-2013
    Location
    Sgainpore
    MS-Off Ver
    Excel 2010
    Posts
    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","")]
    G (Disabled in ADASIAPAC), the result show up 'Yes' [=IF(OR(VLOOKUP(A2,'All Computers_ADASIAPAC'!A:B,2,FALSE)="Disabled",VLOOKUP(A2,'All Computers_ADASIAPAC'!A:B,2,FALSE)="Disabled\No_PWDREQD"),"Yes","")]

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,685

    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)
    =VLOOKUP(A2,'All Computers_ADASIAPAC'!A:B,2,FALSE)

    becomes

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

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

  10. #10
    Registered User
    Join Date
    06-11-2013
    Location
    Sgainpore
    MS-Off Ver
    Excel 2010
    Posts
    8

    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")

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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