Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Slumbering Gorilla
 
Posts: n/a
Default intersection formula? need help expanding on it

im made a mileage chart with many values in it. so now when i type

=chatsworth mission_hills1

it gives me the intersection which is the mileage between the two cities.
works perfectly.

BUT, i want to get it so i type the "from" city in one cell, then the "to"
city in another cell then giving me the intersection value in the third cell.
i typed in the formula

=A3 B3

A3 containging the from city and b3 containing the to city. but it doesnt
work. obviously i do not know how to get this formula to work. need help if
there is an answer.
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

A3 B3 doesn't *have* an intersection.

You could use

= B:B 3:3

to get the intersection of row 3 and column B.

One way to get what you want, if, say, the From city is in A1 and the To
city in A2, and your data in Table, you could use:

=VLOOKUP(A1,Table, MATCH(A2,OFFSET(TABLE,,,1,),FALSE),FALSE)



In article ,
Slumbering Gorilla
wrote:

im made a mileage chart with many values in it. so now when i type

=chatsworth mission_hills1

it gives me the intersection which is the mileage between the two cities.
works perfectly.

BUT, i want to get it so i type the "from" city in one cell, then the "to"
city in another cell then giving me the intersection value in the third cell.
i typed in the formula

=A3 B3

A3 containging the from city and b3 containing the to city. but it doesnt
work. obviously i do not know how to get this formula to work. need help if
there is an answer.

  #3   Report Post  
tjtjjtjt
 
Posts: n/a
Default

I'm not sure I understand how your Worksheet is set up. A3 and B3 don't
intersect--they are in parallel columns. Usually there are Ranges of more
than one cell a piece when the Intersection Operator is used.
For instance,
=A4:E4 B1:B5
would return the value in B4 because B4 is contained in both ranges.

The formula you have working likely refers to named ranges. Go to Insert |
Name, Define, and see what cells the Names refer to.

tj

"Slumbering Gorilla" wrote:

im made a mileage chart with many values in it. so now when i type

=chatsworth mission_hills1

it gives me the intersection which is the mileage between the two cities.
works perfectly.

BUT, i want to get it so i type the "from" city in one cell, then the "to"
city in another cell then giving me the intersection value in the third cell.
i typed in the formula

=A3 B3

A3 containging the from city and b3 containing the to city. but it doesnt
work. obviously i do not know how to get this formula to work. need help if
there is an answer.

  #4   Report Post  
Slumbering Gorilla
 
Posts: n/a
Default

sorry i must have been a little unclear.

i already have a table set up. with the city names as row and column
headers. of course with coulmn headers followed by a 1. so it looks a lot
like a multiplication chart.

so i type

=chatsworth sylmar1

it gives me the intersection wich is 12. works fine.

but what i want to do is simplify it so other people can use it.
so the table with all the values are located at a22 and on. there are over
9000 values.

at the top of the sheet i want to be able to type in A2 the from city, type
in B2 the to city and get the intersected value shown in c2. im looking for
the formula to put in c2 so this can all work. i myself dont have a problem
typing out =chatsworth sylmar1. but other people who i want to use this will
only have to type chatsworth then tab over sylmar1, then enter for the
rusults on the next cell.
hopefully im clear this time. i have a lot of trouble trying to explain
myself.

but im also looking into what JC said. it looks like he understood my trouble.

"tjtjjtjt" wrote:

I'm not sure I understand how your Worksheet is set up. A3 and B3 don't
intersect--they are in parallel columns. Usually there are Ranges of more
than one cell a piece when the Intersection Operator is used.
For instance,
=A4:E4 B1:B5
would return the value in B4 because B4 is contained in both ranges.

The formula you have working likely refers to named ranges. Go to Insert |
Name, Define, and see what cells the Names refer to.

tj

"Slumbering Gorilla" wrote:

im made a mileage chart with many values in it. so now when i type

=chatsworth mission_hills1

it gives me the intersection which is the mileage between the two cities.
works perfectly.

BUT, i want to get it so i type the "from" city in one cell, then the "to"
city in another cell then giving me the intersection value in the third cell.
i typed in the formula

=A3 B3

A3 containging the from city and b3 containing the to city. but it doesnt
work. obviously i do not know how to get this formula to work. need help if
there is an answer.

  #5   Report Post  
Gord Dibben
 
Posts: n/a
Default

Copy/paste this UDF to a general module in your workbook.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Then with chatsworth in A3 and mission_hills1 in B3

In C3 enter =evalcell(A3&" "&B3)

Use a Data Validation drop-down list in A3 and B3 so's you just click on a
city in A3 and B3

Gord Dibben Excel MVP

"Slumbering Gorilla" wrote:

im made a mileage chart with many values in it. so now when i type

=chatsworth mission_hills1

it gives me the intersection which is the mileage between the two cities.
works perfectly.

BUT, i want to get it so i type the "from" city in one cell, then the "to"
city in another cell then giving me the intersection value in the third cell.
i typed in the formula

=A3 B3

A3 containging the from city and b3 containing the to city. but it doesnt
work. obviously i do not know how to get this formula to work. need help if
there is an answer.




  #6   Report Post  
Slumbering Gorilla
 
Posts: n/a
Default

this is off topic. but ive spent over $200 on books teaching me how to use
office. but when it comes down to actual pratical use, this forum kicks a$$.
and dibben. that helps a lot thanks dude!! i think i can handle the rest from
here.

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Then with chatsworth in A3 and mission_hills1 in B3

In C3 enter =evalcell(A3&" "&B3)

Use a Data Validation drop-down list in A3 and B3 so's you just click on a
city in A3 and B3

Gord Dibben Excel MVP

"Slumbering Gorilla" wrote:

im made a mileage chart with many values in it. so now when i type

=chatsworth mission_hills1

it gives me the intersection which is the mileage between the two cities.
works perfectly.

BUT, i want to get it so i type the "from" city in one cell, then the "to"
city in another cell then giving me the intersection value in the third cell.
i typed in the formula

=A3 B3

A3 containging the from city and b3 containing the to city. but it doesnt
work. obviously i do not know how to get this formula to work. need help if
there is an answer.



  #7   Report Post  
Slumbering Gorilla
 
Posts: n/a
Default

off topic one more time.
i just have to say one more time...thank you dibben. im still new to excel,
but you helped me out a lot. now i can concentrate on decorating the sheet.!!

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Then with chatsworth in A3 and mission_hills1 in B3

In C3 enter =evalcell(A3&" "&B3)

Use a Data Validation drop-down list in A3 and B3 so's you just click on a
city in A3 and B3

Gord Dibben Excel MVP

"Slumbering Gorilla" wrote:

im made a mileage chart with many values in it. so now when i type

=chatsworth mission_hills1

it gives me the intersection which is the mileage between the two cities.
works perfectly.

BUT, i want to get it so i type the "from" city in one cell, then the "to"
city in another cell then giving me the intersection value in the third cell.
i typed in the formula

=A3 B3

A3 containging the from city and b3 containing the to city. but it doesnt
work. obviously i do not know how to get this formula to work. need help if
there is an answer.



  #8   Report Post  
Gord Dibben
 
Posts: n/a
Default

Thanks for the feedback.

If you need help on Data Validation drop-down lists see Debra Dalgleish at

http://www.contextures.on.ca/xlDataVal01.html

Gord

On Thu, 18 Nov 2004 17:33:05 -0800, Slumbering Gorilla
wrote:

this is off topic. but ive spent over $200 on books teaching me how to use
office. but when it comes down to actual pratical use, this forum kicks a$$.
and dibben. that helps a lot thanks dude!! i think i can handle the rest from
here.

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Then with chatsworth in A3 and mission_hills1 in B3

In C3 enter =evalcell(A3&" "&B3)

Use a Data Validation drop-down list in A3 and B3 so's you just click on a
city in A3 and B3

Gord Dibben Excel MVP

"Slumbering Gorilla" wrote:

im made a mileage chart with many values in it. so now when i type

=chatsworth mission_hills1

it gives me the intersection which is the mileage between the two cities.
works perfectly.

BUT, i want to get it so i type the "from" city in one cell, then the "to"
city in another cell then giving me the intersection value in the third cell.
i typed in the formula

=A3 B3

A3 containging the from city and b3 containing the to city. but it doesnt
work. obviously i do not know how to get this formula to work. need help if
there is an answer.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 02:49 PM
Simple formula doesn't quite add up circeo Excel Discussion (Misc queries) 3 January 17th 05 10:04 PM
how do I make a formula NOT change when the data range is moved? Alida Andrews Excel Discussion (Misc queries) 2 January 6th 05 10:02 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM
Excel 2003 - Formula result shows as 0:00 Serena Excel Worksheet Functions 4 November 11th 04 11:18 PM


All times are GMT +1. The time now is 03:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"