![]() |
Intersect Formula???
Lets say in sheet 1 the user will type a "From Location"
(i.e. New York) into Cell A1, and a "To Location" (i.e. Boston) into Cell B1. I want cell C1 to equal the milage. In sheet 2 there is a distance chart with from location arranged in Cells A1:Z1. The To Locations are in cells A2:A25. The row/column intersect contains the miles. How can I get cell C1 in sheet one to equal the corresponding intersect value based on what they typed in Sheet 1 A1 and B1? SDC |
Intersect Formula???
You have created a conflicting situation where you placed your inputs and
results in the from locations. Let's say that the from is in A1, the to is in B1 The from locations are in A2:Z2 the to locationsin A3:A25 =OFFSET($A$2,MATCH(B1,$A$2:$A$25,0)-1,MATCH(A1,$A$2:$Z$2,0)-1) -- Regards, Tom Ogilvy "scrabtree" wrote in message ... Lets say in sheet 1 the user will type a "From Location" (i.e. New York) into Cell A1, and a "To Location" (i.e. Boston) into Cell B1. I want cell C1 to equal the milage. In sheet 2 there is a distance chart with from location arranged in Cells A1:Z1. The To Locations are in cells A2:A25. The row/column intersect contains the miles. You How can I get cell C1 in sheet one to equal the corresponding intersect value based on what they typed in Sheet 1 A1 and B1? SDC |
Intersect Formula???
In A2 use =MATCH(A1,Sheet2!A1:Z1,0) this tells the column position of the
From City In B2 use =MATCH(B1,Sheet2!A2:A25,0) this tells the row position of the To City In C1 use =INDEX(B2:Z25, B2,A2) this locates the intersection (as you call it) In C2 let's combine the formulas =INDEX(B2:Z25,MATCH(B1,Sheet2!A2:A25,0),MATCH(A1,S heet2!A1:Z1,0)) Best wishes Bernard Liengme NORTHca "scrabtree" wrote in message ... Lets say in sheet 1 the user will type a "From Location" (i.e. New York) into Cell A1, and a "To Location" (i.e. Boston) into Cell B1. I want cell C1 to equal the milage. In sheet 2 there is a distance chart with from location arranged in Cells A1:Z1. The To Locations are in cells A2:A25. The row/column intersect contains the miles. How can I get cell C1 in sheet one to equal the corresponding intersect value based on what they typed in Sheet 1 A1 and B1? SDC |
Intersect Formula???
Missed you were on different sheets, but you should be able to adjust the
formula. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... You have created a conflicting situation where you placed your inputs and results in the from locations. Let's say that the from is in A1, the to is in B1 The from locations are in A2:Z2 the to locationsin A3:A25 =OFFSET($A$2,MATCH(B1,$A$2:$A$25,0)-1,MATCH(A1,$A$2:$Z$2,0)-1) -- Regards, Tom Ogilvy "scrabtree" wrote in message ... Lets say in sheet 1 the user will type a "From Location" (i.e. New York) into Cell A1, and a "To Location" (i.e. Boston) into Cell B1. I want cell C1 to equal the milage. In sheet 2 there is a distance chart with from location arranged in Cells A1:Z1. The To Locations are in cells A2:A25. The row/column intersect contains the miles. You How can I get cell C1 in sheet one to equal the corresponding intersect value based on what they typed in Sheet 1 A1 and B1? SDC |
All times are GMT +1. The time now is 11:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com