Layout / Feasability advice please (2003 SP3)

Topic

New Reply

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

Reply | Quote

Replies

Your layout looks perfectly reasonable to me. INDEX/MATCH formulas should be able to get you the answers you need. See attached for example.

Reply | Quote

A bit more fancy with cascaded validation; first select country, then select the applicable city.

Reply | Quote

HI Rory and Jan Karel

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Odd, because it worked for me.

Reply | Quote

I figured it did. I’m using 2007.

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

Reply | Quote

I don’t think so, Excel will adjust the delimiters according to your regional settings when you open the file.

Reply | Quote

The powers that be are looking into this attachment issue. I can’t access them either.

Reply | Quote

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.

Reply | Quote