Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Michigan City, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    I have a spreadsheet that contains mostly Vlookup formulas which gather data from other worksheets to put on the main worksheet. On some cells I want to use the result of the vlookup in a formula, but it doesn't seem to be working. For example, I have the following formula:

    =IF(AND((ISblank(AC3)),(ISblank(W3))),"88/88/8888",(IF(NOT(ISBLANK(AC3)),AC3,(VLOOKUP(G3,'SA Info'!$A$1:$J$991,10,FALSE)))))

    AC3 and W3 both contain vlookup formulas, so it is not reading the cell as blank. The result of the vlookup in these formulas is blank. The formula in AC3 is =IF(ISNA(VLOOKUP(G3,Graduate!$A$1:$I$22,8,FALSE)), "",VLOOKUP(G3,Graduate!$A$1:$I$22,8,FALSE)) and in W3 is =IF(ISNA(VLOOKUP(G3,'SA Info'!$A$1:$H$991,8,FALSE)),"",(IF(OR(LEFT(VLOOKUP (G3,'SA Info'!$A$1:$H$991,8,FALSE),5)="trans",LEFT(VLOOKUP (G3,'SA Info'!$A$1:$H$991,8,FALSE),4)="acad",LEFT(VLOOKUP( G3,'SA Info'!$A$1:$H$991,8,FALSE),4)="with"),VLOOKUP(G3,' SA Info'!$A$1:$H$991,8,FALSE),"")))

    I tried replacing ISBLANK(AC3) and ISBLANK(W3) in the first formula above with ISNA, but that didn't work either. It doesn't read it as blank because there is a formula in the cell (I think). Do I have to replace ISBLANK(AC3) with the vlookup formula that is in AC3? I was hoping to avoid that since it gets very confusing.

    Thanks for any help you can provide.

    Jodi

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jojames' post='784920' date='16-Jul-2009 10:06']I have a spreadsheet that contains mostly Vlookup formulas which gather data from other worksheets to put on the main worksheet. On some cells I want to use the result of the vlookup in a formula, but it doesn't seem to be working. For example, I have the following formula:

    =IF(AND((ISblank(AC3)),(ISblank(W3))),"88/88/8888",(IF(NOT(ISBLANK(AC3)),AC3,(VLOOKUP(G3,'SA Info'!$A$1:$J$991,10,FALSE)))))

    AC3 and W3 both contain vlookup formulas, so it is not reading the cell as blank. The result of the vlookup in these formulas is blank. The formula in AC3 is =IF(ISNA(VLOOKUP(G3,Graduate!$A$1:$I$22,8,FALSE)), "",VLOOKUP(G3,Graduate!$A$1:$I$22,8,FALSE)) and in W3 is =IF(ISNA(VLOOKUP(G3,'SA Info'!$A$1:$H$991,8,FALSE)),"",(IF(OR(LEFT(VLOOKUP (G3,'SA Info'!$A$1:$H$991,8,FALSE),5)="trans",LEFT(VLOOKUP (G3,'SA Info'!$A$1:$H$991,8,FALSE),4)="acad",LEFT(VLOOKUP( G3,'SA Info'!$A$1:$H$991,8,FALSE),4)="with"),VLOOKUP(G3,' SA Info'!$A$1:$H$991,8,FALSE),"")))

    I tried replacing ISBLANK(AC3) and ISBLANK(W3) in the first formula above with ISNA, but that didn't work either. It doesn't read it as blank because there is a formula in the cell (I think). Do I have to replace ISBLANK(AC3) with the vlookup formula that is in AC3? I was hoping to avoid that since it gets very confusing.

    Thanks for any help you can provide.

    Jodi[/quote]
    Try replacing ISBLANK(Ref) with Ref = ""
    =IF(AND((AC3=""),(W3="")),"88/88/8888",(IF(NOT(AC3=""),AC3,(VLOOKUP(G3,'SA Info'!$A$1:$J$991,10,FALSE)))))
    Regards
    Don

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Michigan City, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='784937' date='16-Jul-2009 16:05']Try replacing ISBLANK(Ref) with Ref = ""
    =IF(AND((AC3=""),(W3="")),"88/88/8888",(IF(NOT(AC3=""),AC3,(VLOOKUP(G3,'SA Info'!$A$1:$J$991,10,FALSE)))))[/quote]


    That worked! Thanks!

    Jodi

Posting Permissions

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