Hello,

I have an Excel document containing two sheets; on the first sheet, input data are located, on the second sheet, I do perform calculations with the input data.

Input data: A list of locations which several variables attached, e.g. temperature and altitude, sorted alphabetically.
Calculation: A location's variable is multiplied with a certain factor (individual per location).

Thus, what I have is basically a table which is split between page 1 and 2; some location attributes (temperature, altitude) are on page 1, others (multiplication factor and result) are on page 2.

Now to my problem: The input data table is flexible, meaning that new locations can be added; the location list however stays sorted alphabetically. How can I make sure that the calculation factors on the second page stay attached to the right locations also if a new location is added "in the middle" of the location list?

An example: On page 1, I have the locations London and Paris, and each city has got an altitude value.
Location Altitude
London 10
Paris 200

On page 2, I reference this table via "=A1", "=A2" etc, and I add an extra column for the multiplication factor and the result:

Location Altitude Factor Result
London 10 5 50
Paris 200 10 2000

If I add an extra location "Moscow", alphabetically between London and Paris on page 1, the Paris multiplication factor is wrongly attached to it on the second page. How can I make sure the Paris multiplication factor stays attached to the location Paris on page two?

Thanks already in advance

Requin