Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: lookup easy one

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, maybe i misunderstood you previous question but the left most column of the range "PAX" are all voyage numbers i.e. A112
    ,A113, D112 etc the lookup value range e15-e21 are the possible blank cells.

    Hope that makes sense

  2. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-25 16:28, lars wrote:
    Mark, maybe i misunderstood you previous question but the left most column of the range "PAX" are all voyage numbers i.e. A112
    ,A113, D112 etc the lookup value range e15-e21 are the possible blank cells.

    Hope that makes sense
    Great! So the value, 0, isn't a valid "voyage number". This allows you to insert a row into the 'PAX' range and setup a value that you'd like your VLOOKUP to return if E15:E21 are blank. You see... when E15:E21 are blank your VLOOKUP function will search for a 0 entry in 'PAX'... so put one there for it to find! Does this make sense? You're configuring your table for all possible lookup values.

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, that works, but the range PAX is a download and I was hoping to copy the download in without having to manipulate or change it in anyway. But it works and that is great.

    Thanks Mark
    Lars

  4. #14
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Qroonz, I tried it and it works if I put in the second position but if I add other voayges in the range e16-e21 nothing adds up. it just returns the first voyage find

  5. #15
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-25 16:43, lars wrote:
    Mark, that works, but the range PAX is a download and I was hoping to copy the download in without having to manipulate or change it in anyway. But it works and that is great.

    Thanks Mark
    Lars
    There are ways around that too. You could use the Data | Get External Data menu command to import your data and have PAX filled in just below a permanent 0 entry on your worksheet.

    The important thing (for me at least) is that you're thinking out-of-the-box when you implement a solution such as this. Most users would try to "fix it" by constructing a convoluted formula.

    [ This Message was edited by: Mark W. on 2002-04-25 16:51 ]

  6. #16
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark , I take that back it doesn't work if you add other voyages to E16-E21 it only works with E15. hmmmmm....

  7. #17
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-25 16:50, lars wrote:
    Mark , I take that back it doesn't work if you add other voyages to E16-E21 it only works with E15. hmmmmm....
    What value did you associate with 0 in PAX table?

  8. #18
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    did you change the cell references when you copied the formula?. or are you just repeating the same lookup throughtout all your cells?

  9. #19
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, I just typed in a 0...no good?


    Qrooze, the formula is in one cell only.

  10. #20
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have to go to pick up my daughter

    Thanks for the help I will check this out tomorrow morning. Maybe then I can repost and we can have some more fun with it.

    Thanks again you guys

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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