Make merged cells absolute (2003 SP3)

[steveh]

Good morning

I have this formual to merge B26 and C 26 =B26& " - "&C26 and have then dragged it down a 500 row column, and that is fine.

The problem that I have is that B26 and C26 contain data that is obtained from a data query but the in the cells that I have merged it into I would like to put it in alphabetical order because it will later be accessed from a combo box if I make the formula =$B$26& " - "&$C$26 and drag it down it (obviousely) gives me the same result all the way down the column, any ideas please?

[HansV]

Why don't you sort the data query the way you want?

[steveh]

Why don't you sort the data query the way you want?

Hi Hans

I have a sheet that contains the average weather conditions for several thousands of cities which contain historical data by month and shows the average highest and the lowest temperatures recorded over a 30 year period.

One of my Directors has decided that it would be good to have a 'live' daily version and I have been searching for a couple of months for a site that I could get the information from in one giant alphabetical list but I can't find one, I have emailed some of them and offered to pay but I have not got any answers.

As a next best thing I have founf a site that has Capital city information that I can use on one page but there is a page for each area, for example, Europe, N. America, C. America, S. America so I have made a WB which automatically does about 20 web queries at the same time but these are in 20 regional blocks, for the purpose of mu ultimate goal I need to have them in order for ease of searching from such a huge list.

Hope that explains

[HansV]

A combo box with thousands of entries doesn't sound attractive. I'd let the user select a region first, then select a city within that region. That way, you can use the existing setup.

[HansV]

Another option would be not to use formulas, but to use a macro to populate the single list, then sort it. You'd have to run this macro after running the web queries.

[steveh]

A combo box with thousands of entries doesn't sound attractive. I'd let the user select a region first, then select a city within that region. That way, you can use the existing setup.

Hi Hans

Thanks for the advice, I think this option sounds better and also something I should be able to do.