Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good morning

    I have been asked to compile a list of low and high temperature information for various worldwide cities so that we can choose the right type of packaging when shipping from one Country to another with different temperature ranges.

    First of all I thought that perhaps I could lay out the data with all of the cities on the left and copied along the bottom and where the information intersected I would have the answer but that has become very complicated.

    I am thinking now if there is a way to run a formula or probably it would need to be a macro driven user form that would allow me to select a From Country, a To Country and a month with the result showing as, for example

    January
    London Low -1 High 5
    Riyadh Low 15 High 22

    I would like to ask if it is possible because to achieve it I am going to need a lot of help and support from fellow loungers and I do not want to bog anybody down with the impossible. I have attached a sample starting point spreadsheet if that helps

    TIA
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your layout looks perfectly reasonable to me. INDEX/MATCH formulas should be able to get you the answers you need. See attached for example.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A bit more fancy with cascaded validation; first select country, then select the applicable city.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    HI Rory and Jan Karel

    You guys are amazing, thank you so much for the effort and the quick responses, you have saved me [s]hours days [/s] months of pulling my hair out
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    pieterse,
    I'm only seeing one city listed when picking a country with multiple cities when using your workbook.

    stevenhocking,
    The 3 of USA cells have a trailing space. It could cause problems with various functions.

    I've attached a version that uses a unique country list (hidden in the AB column on Sheet1) for the country dropdown and then uses the Offset function to populate the list of the City drop down. I also use the offset function to return the high and low temps.
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Odd, because it worked for me.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured it did. I'm using 2007.

    I wonder if it has to do with the ; separators in the Named range somehow.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't think so, Excel will adjust the delimiters according to your regional settings when you open the file.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    pieterse,
    I'm only seeing one city listed when picking a country with multiple cities when using your workbook.

    stevenhocking,
    The 3 of USA cells have a trailing space. It could cause problems with various functions.

    I've attached a version that uses a unique country list (hidden in the AB column on Sheet1) for the country dropdown and then uses the Offset function to populate the list of the City drop down. I also use the offset function to return the high and low temps.
    Hi Mike

    Thanks for doing that but I can't open or download it, it goes straight to an error page (Please see below) perhaps a MOD can pick up on this and advise

    Can't attach the picture either it says Error The server returned an error during upload

    Perhaps there is a general lounge problem so I will try later
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The powers that be are looking into this attachment issue. I can't access them either.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    pieterse,
    I'm only seeing one city listed when picking a country with multiple cities when using your workbook.

    stevenhocking,
    The 3 of USA cells have a trailing space. It could cause problems with various functions.

    I've attached a version that uses a unique country list (hidden in the AB column on Sheet1) for the country dropdown and then uses the Offset function to populate the list of the City drop down. I also use the offset function to return the high and low temps.
    Hi Mike

    Downloads are working again now

    Fantastic solution, thank you. I am working though the formulas and ranges, I can see that there is a temperature range but in the low and high result cells you have =lowtemperature and =hightemperature and I can't fathom out where to find them or where they are getting their values from (I can see the offset formula below. Sorry just a little confused
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    They are named ranges. Press Ctrl + F3 to see the Names manager. I don't remember how to get there in Ex2003 (or earlier)> I think it's Insert Names> Define but I'm not entirely sure.

Posting Permissions

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