I have a distances grid. I need the data from the grid to enter into my sheet automatically when I type the two towns into columns 1 and 2. SO when I type tow towns into the sheet it reads the distance from the corresponding box in the grid. Vlookup hasn't worked for me so far.
Thanks
Nel post news:[email protected]
*phil2006* ha scritto:
> I have a distances grid. I need the data from the grid to enter into
> my sheet automatically when I type the two towns into columns 1 and
> 2. SO when I type tow towns into the sheet it reads the distance from
> the corresponding box in the grid. Vlookup hasn't worked for me so
> far.
>
> Thanks
How are your data: row, columns, and so on.
If you don't tell us ho your data are on the worksheet, it'quite impossible
to help you...
Help us to help you... :-)
--
Thanks in advance for your feedback.
Ciao
Franz Verga from Italy
if you are mimicking a "normal" distance table you will need to have a cell
for the vlookup value,then a match function in your lookup to find the
appropriate column
something like =
vlookup(city1,distance_table,match(city2,city2namerow,0),false)
I suggest your two lookup values are chosen from dv list or combo box so
that you always get the spelling right and you choose the right cities from
the column or row
paul
[email protected]
remove nospam for email addy!
"Franz Verga" wrote:
> Nel post news:[email protected]
> *phil2006* ha scritto:
>
> > I have a distances grid. I need the data from the grid to enter into
> > my sheet automatically when I type the two towns into columns 1 and
> > 2. SO when I type tow towns into the sheet it reads the distance from
> > the corresponding box in the grid. Vlookup hasn't worked for me so
> > far.
> >
> > Thanks
>
> How are your data: row, columns, and so on.
>
> If you don't tell us ho your data are on the worksheet, it'quite impossible
> to help you...
>
> Help us to help you... :-)
>
> --
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>
My grid is on sheet2, the city names are in row 1 and column A. I am typing the names on sheet 1 in columns 1 and 2 hopin to achieve a distance in column 3.
Thanks!!
Nel post news:[email protected]
*phil2006* ha scritto:
> My grid is on sheet2, the city names are in row 1 and column A. I am
> typing the names on sheet 1 in columns 1 and 2 hopin to achieve a
> distance in column 3.
>
> Thanks!!
I think Paul gave you the right answer. I would suggested the same too.
--
Hope I helped you.
Thanks in advance for your feedback.
Ciao
Franz Verga from Italy
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
Insert>Name>Create, 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 Sat, 24 Jun 2006 04:03:39 -0500, phil2006
<[email protected]> wrote:
>
>I have a distances grid. I need the data from the grid to enter into my
>sheet automatically when I type the two towns into columns 1 and 2. SO
>when I type tow towns into the sheet it reads the distance from the
>corresponding box in the grid. Vlookup hasn't worked for me so far.
>
>Thanks
Gord Dibben MS Excel MVP
Actually Gord, as long as:
<Tools> <Options> <Calculation> tab,
"Accept Labels In Formulas" *IS* checked.
No additional names need to be created.
A formula containing the existing cities from the grid will return the
values.
=row_city col_city
will work, with the exception of both names being the same.
Also, has to be on the same sheet as the grid.
Your suggestion enables the formulas to work on *other* sheets, but problem
comes in when the grid has duplicate names in the columns and rows.
Makes it necessary (as I see you described ... Quebec, Quebec1), to assign
"false" names to the row or the column cities.
However, what I described, and what you described, does not do what the OP
has requested, namely, returning grid values in a column adjoining 2 columns
containing 2 city names.
It's doubtful that he wants the users to actually enter formulas, no matter
how concise they may be (though I could be wrong).
So, using your 'name create' procedure for the grid on Sheet2, with "false"
names in the duplicate column cities (say "1" appended),
AND ... the user being told to enter on Sheet1,
in A1 and B1:
City1 City
Then in C1 we can try this:
=INDIRECT(A1&":"&A1) INDIRECT(B1&":"&B1)
Note the <space> between the Indirects.
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
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
Insert>Name>Create, 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 Sat, 24 Jun 2006 04:03:39 -0500, phil2006
<[email protected]> wrote:
>
>I have a distances grid. I need the data from the grid to enter into my
>sheet automatically when I type the two towns into columns 1 and 2. SO
>when I type tow towns into the sheet it reads the distance from the
>corresponding box in the grid. Vlookup hasn't worked for me so far.
>
>Thanks
Gord Dibben MS Excel MVP
Thanks RD
We'll get that cat skinned yet<g>
Gord
On Sat, 24 Jun 2006 10:03:05 -0700, "RagDyeR" <[email protected]> wrote:
>Actually Gord, as long as:
>
><Tools> <Options> <Calculation> tab,
>"Accept Labels In Formulas" *IS* checked.
>
>No additional names need to be created.
>
>A formula containing the existing cities from the grid will return the
>values.
>
>=row_city col_city
>
>will work, with the exception of both names being the same.
>
>Also, has to be on the same sheet as the grid.
>
>Your suggestion enables the formulas to work on *other* sheets, but problem
>comes in when the grid has duplicate names in the columns and rows.
>Makes it necessary (as I see you described ... Quebec, Quebec1), to assign
>"false" names to the row or the column cities.
>
>However, what I described, and what you described, does not do what the OP
>has requested, namely, returning grid values in a column adjoining 2 columns
>containing 2 city names.
>It's doubtful that he wants the users to actually enter formulas, no matter
>how concise they may be (though I could be wrong).
>
>So, using your 'name create' procedure for the grid on Sheet2, with "false"
>names in the duplicate column cities (say "1" appended),
>AND ... the user being told to enter on Sheet1,
>in A1 and B1:
>
>City1 City
>
>Then in C1 we can try this:
>
>=INDIRECT(A1&":"&A1) INDIRECT(B1&":"&B1)
>
>Note the <space> between the Indirects.
Gord Dibben MS Excel MVP
= vlookup(A1,sheet2!distance_table,match(B2,sheet2!city2namerow,0),false)
--
paul
[email protected]
remove nospam for email addy!
"phil2006" wrote:
>
> My grid is on sheet2, the city names are in row 1 and column A. I am
> typing the names on sheet 1 in columns 1 and 2 hopin to achieve a
> distance in column 3.
>
> Thanks!!
>
>
> --
> phil2006
> ------------------------------------------------------------------------
> phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> View this thread: http://www.excelforum.com/showthread...hreadid=555195
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks