Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

Copy PasteSpecial Transpose

Vaya con Dios,
Chuck, CABGx3



"mkj" wrote:

Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

That's close, but it won't let me paste over the original selection to get
the end result I'm after. I was also wondering if a formula would do it so I
can insert new places as I go, up to a total of 165 places. I'm not planning
on typing that all in, in one go...


Thanks for your quick response before!


"CLR" wrote:

Copy PasteSpecial Transpose

Vaya con Dios,
Chuck, CABGx3



"mkj" wrote:

Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

If the cities start in A2 and go down, put this in B1:

=INDEX($A$2:$A$20,COLUMN(A1))

And copy to the right. Of course $A$20 is variable.

--
Kind regards,

Niek Otten

"mkj" wrote in message
...
Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the
top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

That's pretty cool. It certainly copies the place names easily enough.
I guess that for each row I would have to individually change it to
something like
(in column E)
=INDEX($B$2:$B$20,COLUMN(E2)) in row 2
=INDEX($C$2:$C$20,COLUMN(E3)) in row 3 etc etc to get the distances copied
from one side to the other. If I do it manually, that is still 165 changes to
make. Can you think of an easier way of changing the parameters, or would I
be looking at a macro? *Shudder*

Thanks very much,

MKJ

"Niek Otten" wrote:

If the cities start in A2 and go down, put this in B1:

=INDEX($A$2:$A$20,COLUMN(A1))

And copy to the right. Of course $A$20 is variable.

--
Kind regards,

Niek Otten

"mkj" wrote in message
...
Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the
top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

=INDEX($A$1:$K$11,MATCH(K$1,$A$1:$A$11,0),MATCH($A 2,$A$1:$K$1))

This formula would be put in column K (as an example) and copied to all
cells
above the diagonal 0 line. Then all you would have to do is change the

distances on the bottom half and the top half would be updated
automatically.

To clarify what this is doing:

$A$1:$K$11 is the entire range of data
K$1 the K is whatever column you are putting the formula in and 1 is
the row
where your cities are listed across the top.

all other values would have to be adjusted accordingly to reflect your
data.

Is this what you are looking for? It is hard to explain and I feel
like I may have
done a poor job. If you are familiar with excel functions then you can
probably
play around with it and get it to work.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=519326

Example only....adjust ranges and cities.

You have a table A1:J10

Leave A1 blank.

Say A2:A10 contains city and B1:J1 contains city1

e.g

A2 = Quebec
B1 = Quebec1

A3 = Ottawa
C1 = Ottawa1

A4 = Timmins
D1 = Timmins1

B2:J10 contains mileages.

One method is to select the entire table A1:J10 then choose
InsertNameCreate, and select top row and left column.

Then use the intersect functionality:

=city city1

In above example =quebec ottawa1


This will return the value of the cell at the intersection of city and city1


Gord Dibben Excel MVP




On Mon, 6 Mar 2006 06:48:34 -0800, mkj wrote:

Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

Gord Dibben MS Excel MVP

Hi,

That's excellent! One formula that can be pasted anywhere in the upper half
and be expected to work. (Obviously modifying for different sized tables.)
Exactly what I was hoping to find. Thank-you very much for your help.

MKJ

"mphell0" wrote:


=INDEX($A$1:$K$11,MATCH(K$1,$A$1:$A$11,0),MATCH($A 2,$A$1:$K$1))

This formula would be put in column K (as an example) and copied to all
cells
above the diagonal 0 line. Then all you would have to do is change the

distances on the bottom half and the top half would be updated
automatically.

To clarify what this is doing:

$A$1:$K$11 is the entire range of data
K$1 the K is whatever column you are putting the formula in and 1 is
the row
where your cities are listed across the top.

all other values would have to be adjusted accordingly to reflect your
data.

Is this what you are looking for? It is hard to explain and I feel
like I may have
done a poor job. If you are familiar with excel functions then you can
probably
play around with it and get it to work.


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=519326

Hello.

I'm afraid you lost me slightly there, though I did play with the feature to
InsertName... That could be useful later on.
mphello found a solution that I'll use, but thanks for your efforts on this
anyway! It's much appreciated.

MKJ

"Gord Dibben" wrote:

Example only....adjust ranges and cities.

You have a table A1:J10

Leave A1 blank.

Say A2:A10 contains city and B1:J1 contains city1

e.g

A2 = Quebec
B1 = Quebec1

A3 = Ottawa
C1 = Ottawa1

A4 = Timmins
D1 = Timmins1

B2:J10 contains mileages.

One method is to select the entire table A1:J10 then choose
InsertNameCreate, and select top row and left column.

Then use the intersect functionality:

=city city1

In above example =quebec ottawa1


This will return the value of the cell at the intersection of city and city1


Gord Dibben Excel MVP




On Mon, 6 Mar 2006 06:48:34 -0800, mkj wrote:

Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

Gord Dibben MS Excel MVP

Hi,

Im creating a similar table showing the distances between 112 schools in our
borough. As this is a very large table of information, I want to have a
simpler worksheet for colleagues to use without scrolling all-over a large
sheet of figures.
I want to have 2 drop-down lists to make a "from" and "to" selection, then
be presented with the distance taken from the table on the other worksheet,
any ideas how I can do this?

Thanks,
Chris


"mkj" wrote:

Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

Create a table in another worksheet.

Put the names of the school in A2:A113
and put the names of the school in B1:DH1
Put the distances between schools in that giant table.
(Kind of like the table you see in maps)

Then you can use a couple of cells with data|validation (use A2:A113) as the
source.
See Debra Dalgleish's site to see how to name that range so that it can be used
as the data|validation list:
http://contextures.com/xlDataVal01.html#Name

Now you can use a formula to return the intersection of those two choices.
Time to visit Debra's site once again:
http://contextures.com/xlFunctions03.html
especially example 2 (or 3)

You may want to put this in your formula:

=if(or(a1="",b1=""),"",index(....))

where a1 and b1 hold the two school names.



chris1978uk wrote:

Hi,

Im creating a similar table showing the distances between 112 schools in our
borough. As this is a very large table of information, I want to have a
simpler worksheet for colleagues to use without scrolling all-over a large
sheet of figures.
I want to have 2 drop-down lists to make a "from" and "to" selection, then
be presented with the distance taken from the table on the other worksheet,
any ideas how I can do this?

Thanks,
Chris

"mkj" wrote:

Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

--

Dave Peterson