Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unconventional Table (Excel 2000)

    The unconventional 'Table', is one of those mileage information tables at the front of road atlas's. I am struggling with a Vlookup/Index/match solution to extract a particular mileage when entering 2 place names in say, A1 and B1, and have the distance between the two appear in C1. The place names are at the side and the top and that is what is confusing me; I think!

    An 'easy 'formula or a VB solution?

    Many thanks for any pointers.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unconventional Table (Excel 2000)

    Hi There

    I have attached a workbook for you to have a look at. The green boxes to the left are validation lists. I have used vlookup and hlookup and an index() to get the value for the distance. Although this does not highlight the cell it allows the value to be displayed.

    Get back to me if you want further info.
    Jerry

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unconventional Table (Excel 2000)

    Jerry
    Now that was quick! It was not how I imagined it would be, but it looks like it does the job! The index numbers are what I was not thinking of. (Along with other bits and pieces :-)

    Just for interest, I am building a 50 odd location table and wanted to do the dropdown box for the locations which would then give the distance, and then I could do a time/speed/distance calc, with timings and stuff.

    Thank you for your time and expertise.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unconventional Table (Excel 2000)

    Hi there

    I am glad it is what you want, I left the index numbers visible to help you understand my thinking but of course they can be hidden. Alternatively you could have the data table I have used in another (hidden)worksheet and one on the top of you visible worksheet for "show".


    Your time speed calculations could stay on the unhidden sheet.

    Stand by your bed though, this is the weekend and I know a few of us will have some alternative ideas with fancy formulas during the coming week but I tend to go for the KISS principle <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Keep It Simple Stupid <img src=/S/grin.gif border=0 alt=grin width=15 height=15>


    Good luck, glad I could help.
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unconventional Table (Excel 2000)

    Here is a slight variation on Jerry's solution (which works fine). It does away with the extra column and row in between the city names and the distances by using MATCH instead of VLOOKUP/HLOOKUP.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unconventional Table (Excel 2000)

    Thanks Hans

    That has helped me too <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unconventional Table (Excel 2000)

    Thanks Hans; learning all the time!

    However, I can't seem to get my SS to work (Attached). The Match works, but not the Index. I have tried only half populating the table and as you see it now. Basic error in my layout?, as your SS, Jerry, works fine.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unconventional Table (Excel 2000)

    The range named "data" is still that defined by Jezza, but you have expanded the data. You can use

    =INDEX(B3:O16,S2,S1)

    or redefine the "data" range using Insert | Name | Define...

  9. #9
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unconventional Table (Excel 2000)

    Hans / Jerry

    Despite having a large book with 700 pages of formulas and a long time in which to read it; it was down to the users of Woody's Lounge to provide the goods.

    Thank you, I can now comprehend :-)

Posting Permissions

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