I have 2 columns (i.e. origin & destination) & I want it to compare to data
in other part of spreadsheet to fill in miles between origin & destination.

For example: Chicago to Des Moines. I have Chicago in the origin column
and Des Moines in the destination column. I have the miles already
calculated for that run in another part of the spreadsheet, so every time I
fill in the orgin as Chicago & Destination as Des Moines, I want the correct
miles to fill in the mileage column.

how is your mileage calculation sheet set up?
--
JNW


"quinn111" wrote:

I have 2 columns (i.e. origin & destination) & I want it to compare to data
in other part of spreadsheet to fill in miles between origin & destination.

For example: Chicago to Des Moines. I have Chicago in the origin column
and Des Moines in the destination column. I have the miles already
calculated for that run in another part of the spreadsheet, so every time I
fill in the orgin as Chicago & Destination as Des Moines, I want the correct
miles to fill in the mileage column.

The mileage is just entered into cells, no calculation.
In this example: I have orgin, destination & mileage columns in the same
spreadsheet as the main, just off to the right.

"JNW" wrote:

how is your mileage calculation sheet set up?
--
JNW


"quinn111" wrote:

I have 2 columns (i.e. origin & destination) & I want it to compare to data
in other part of spreadsheet to fill in miles between origin & destination.

For example: Chicago to Des Moines. I have Chicago in the origin column
and Des Moines in the destination column. I have the miles already
calculated for that run in another part of the spreadsheet, so every time I
fill in the orgin as Chicago & Destination as Des Moines, I want the correct
miles to fill in the mileage column.

Try this,
copy this matrix starting in cell c34.

1 2 3 4 5
a 5 6 11 16 21
b 6 7 12 17 22
c 7 8 13 18 23
d 8 9 14 19 24
e 9 10 15 20 25


c 4

18
=OFFSET(C34,MATCH(C42,C35:C39,0),MATCH(D42,D34:H34 ,0))

peter


"quinn111" wrote:

I have 2 columns (i.e. origin & destination) & I want it to compare to data
in other part of spreadsheet to fill in miles between origin & destination.

For example: Chicago to Des Moines. I have Chicago in the origin column
and Des Moines in the destination column. I have the miles already
calculated for that run in another part of the spreadsheet, so every time I
fill in the orgin as Chicago & Destination as Des Moines, I want the correct
miles to fill in the mileage column.

Try
=sumproduct(--(sheet2!A1:A50=A1),--(sheet2!B1:B50=B1),--(sheet2!C1:C50))

Sheet2 is where the stored distances are.
--
JNW


"quinn111" wrote:

The mileage is just entered into cells, no calculation.
In this example: I have orgin, destination & mileage columns in the same
spreadsheet as the main, just off to the right.

"JNW" wrote:

how is your mileage calculation sheet set up?
--
JNW


"quinn111" wrote:

I have 2 columns (i.e. origin & destination) & I want it to compare to data
in other part of spreadsheet to fill in miles between origin & destination.

For example: Chicago to Des Moines. I have Chicago in the origin column
and Des Moines in the destination column. I have the miles already
calculated for that run in another part of the spreadsheet, so every time I
fill in the orgin as Chicago & Destination as Des Moines, I want the correct
miles to fill in the mileage column.

You can shorten that to

=SUMPRODUCT(--(Sheet2!A1:A50=A1),--(Sheet2!B1:B50=B1),Sheet2!C1:C50)


--


Regards,


Peo Sjoblom


"JNW" wrote in message
...
Try
=sumproduct(--(sheet2!A1:A50=A1),--(sheet2!B1:B50=B1),--(sheet2!C1:C50))

Sheet2 is where the stored distances are.
--
JNW


"quinn111" wrote:

The mileage is just entered into cells, no calculation.
In this example: I have orgin, destination & mileage columns in the same
spreadsheet as the main, just off to the right.

"JNW" wrote:

how is your mileage calculation sheet set up?
--
JNW


"quinn111" wrote:

I have 2 columns (i.e. origin & destination) & I want it to compare
to data
in other part of spreadsheet to fill in miles between origin &
destination.

For example: Chicago to Des Moines. I have Chicago in the origin
column
and Des Moines in the destination column. I have the miles already
calculated for that run in another part of the spreadsheet, so every
time I
fill in the orgin as Chicago & Destination as Des Moines, I want the
correct
miles to fill in the mileage column.

Thanks Peter.
That works, except I changed the values on the formula to absolutes ($) (see
below), but when I copy it, it doesn't work.
Please help.

"peter" wrote:

Try this,
copy this matrix starting in cell c34.

1 2 3 4 5
a 5 6 11 16 21
b 6 7 12 17 22
c 7 8 13 18 23
d 8 9 14 19 24
e 9 10 15 20 25


c 4

18
=OFFSET($C$34,MATCH(C42,$C$35:$C$39,0),MATCH(D42,$ D$34:$H$34,0))

peter


"quinn111" wrote:

I have 2 columns (i.e. origin & destination) & I want it to compare to data
in other part of spreadsheet to fill in miles between origin & destination.

For example: Chicago to Des Moines. I have Chicago in the origin column
and Des Moines in the destination column. I have the miles already
calculated for that run in another part of the spreadsheet, so every time I
fill in the orgin as Chicago & Destination as Des Moines, I want the correct
miles to fill in the mileage column.