Excel Linking

Originally Posted by mgkmn

sure.

A1- Column1 (Countries)

A2- USA

A3- USA

A4- RUSSIA

A5- MEXICO

A6- Mexico
A7- AUSTRALIA

B1 Column2 (Cities)

B2- Los Angeles

B3- New York

B4- Moscow

B5- Mexico City

B6- Cuernavaca

B7- Sydney

C1- Column3- (Week Number)

C2- 1

C3-1

C4- 2

C5-3

C6- 4

C7- 4

--1. The list is ordered by country (unlike the example).

--2. I would like to do two operations with this data

--a--. COUNT the number of different countries visited in a certain week. For example, week 1 would be 1 since I visited New York and Los Angeles which are both in one country.

--b-- COUNT the number of cities visited in a certain week. For example, week 1 would be 2 since (see above, I visited 2 cities in the US.) However, if I visit the same city twice in the same week, I want to count BOTH times

--3. I would like to display both results (in this case 1 and 2) in the same cell (I know how to do this already). However, I would like each result to be highlighted in a different color. SO for example, cell D1 would read "1 - 2" with "1" being highlighted one color and "2" another.

--4. I would like to have a dependant worksheet which would display the results also. However, contrary to the first worksheet, where the results are displayed in the same cell (so that I wont have to make another column), I want them to display in two different cells in the dependant worksheet. For example, in the dependant worksheet, B1 would be "1" and C1 would be "2".

--5. Finally, I am sure that if I email ONLY the dependant worksheet, all values would be lost since the source is gone. I would like to know how to SAVE the values in the dependant worksheet so that if I only email it (without the source), it will still display the values.

THX IN ADVANCE

Starting with the bad news:
--3 is not possible you can't have different colours in the same cell ( as far as I know)

--2use follwing formula

=sumproduct(--(a2:a10="country"),--(c2:c10=week))
=sumproduct(--(b2:b10="city"),--(c2:c10=week))

--4put the above forulae were you want, so in deifferent cells will be OK

--5You can use Copy - Paste Special - Values to copy values only to a sheet