+ Reply to Thread
Results 1 to 8 of 8

Applying a forumla to a column until its not needed

  1. #1
    Registered User
    Join Date
    04-16-2008
    Posts
    15

    Applying a forumla to a column until its not needed

    Hi,

    Hoping this might be a real simple one...

    I'm trying to apply formula to a column but to get it to stop when there is no value for it to apply to. So when the 23rrd cell in column E is blank to just ignore it (I don't want N/A to show)


    This is my formula:

    LOOKUP(D3,Customers!A$2:A$15,Customers!D$2:D$15)

    I tried:

    =E:E=LOOKUP(D3,Customers!A$2:A$15,Customers!D$2:D$15)

    but that just sends back "FALSE"

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676
    I expect a dynamic named range is what you want (many examples can be found here by searching), but there's no reference to a column E cell in the formula ...?

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,058
    Hi,
    try the error function
    here's one example
    http://www.freewebs.com/davesexcel/V...20Example2.xls

  4. #4
    Registered User
    Join Date
    04-16-2008
    Posts
    15
    Yeah, sorry, I've tried searching the forum but its not working at the moment.

    I just want to apply the formula

    LOOKUP(D3,Customers!A$2:A$15,Customers!D$2:D$15)

    to every cell in column E UNTIL there is no value in column D.

    I'm recording a macro but the date I'm applying this formula to will vary in number of rows and the value "N/A" messes up a sum formula in another column.

    Hope this makes some sense. Sorry If I'm not explaining myself very well.

  5. #5
    Registered User
    Join Date
    04-16-2008
    Posts
    15
    Cheers....AWESOME!

    Think I've cracked it

    Thanks guys..

  6. #6
    Registered User
    Join Date
    04-16-2008
    Posts
    15
    The information you provided was very helpful and resolved my issue with the Look Up formula error. However when I tried to use the same idea with my IF formula...it didnt like it!

    =IF(I3="In Stock",H3,(IF(I3="Out of Stock",0,"")))

    i tried

    =IF(ISERROR(I3="In Stock",H3,(IF(I3="Out of Stock",0,""))),"0",(I3="In Stock",H3,(IF(I3="Out of Stock",0,""))

    but as i say..it didn't like it. is it something to do with 2 IFs?

    any tips?

    thanks

  7. #7
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,058
    Quote Originally Posted by Russ3578
    The information you provided was very helpful and resolved my issue with the Look Up formula error. However when I tried to use the same idea with my IF formula...it didnt like it!

    =IF(I3="In Stock",H3,(IF(I3="Out of Stock",0,"")))

    i tried

    =IF(ISERROR(I3="In Stock",H3,(IF(I3="Out of Stock",0,""))),"0",(I3="In Stock",H3,(IF(I3="Out of Stock",0,""))

    but as i say..it didn't like it. is it something to do with 2 IFs?

    any tips?

    thanks
    you do not really need an If(iserror() statement for this situation,
    Maybe...untested though...
    =if (i3="","",IF(I3="In Stock",H3,IF(I3="Out of Stock",0,"")))

  8. #8
    Registered User
    Join Date
    04-16-2008
    Posts
    15
    yes u r right....

    thanks. no help needed. .. well not formulaic help anyway!

    haha

    thanks for ur previous help.

+ 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