Worksheet formula - auto populate

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

I have a workbook containing (2) worksheets.

Worksheet 'A' contains various calendaring info. I use this to keep track
of what schools I visit on what date, and how many miles it is from my
office. I populate Column A with the names of the schools I visit. Column L
needs to contain the mileage info.

Worksheet 'B' contains a list of the schools, along with the mileage
information (no. of miles from my office). Column A of this worksheet
contains the schools listed in alphabetical order by row. Column B of this
worksheet contains the respective mileage info.

What I am trying to accomplish is a formula that does the following:

As I type the name of the school in cell A1 (worksheet A), cell L1
(worksheet A)populates with the mileage info from worksheet B.

Any ideas?
I am thinking that the name I type worksheet A) must match the name(s)
listed in Column A (worksheet B) ?

As I type the school name into Column A (worksheet A), can Excel use
predictive feature to populate as I type? (i.e. - type 'Wash' , Excel
responds by predicting 'Washington') ?

Thanks in advance!

Brent
 
Why not use Data Validation to select the school name, and then in L1 a
loolkup formula?

To name a range, select, or block it, then in the address bar, enter the
name you want, and press <Enter>. If you block A1:B12, the address bar will
indicate A1.

First, block your list of schools, but exclude the distances, and name it
Schools. Secondly, block the list of schools with mileages, and name it
Distances.

In A1, Click on Data, Validation, replace any value with list, and in the
formula box type in =Schools

In L1, enter the following formula
=IF(A1="","",VLOOKUP(A1,Distances,2,0))

If you now click on the down arrow in A1, you will get a list of schools.
As you type, it will start selecting a school, until you have the school you
require. When you press <Enter> the distance will appear in L1

--

HTH

Kassie

Replace xxx with hotmail
 
Brent,
you will have to move the data from "Sheet B" to an unused area on "Sheet
A". Once that is done, try this:
In Cell A2, select Data, then select Validation.
The "Settings" tab:
the "Allow: " drop down, select list. "Source:" select the list of schools
(it will look like "=$N$2:$N$15", if the list is in Column N, Cell N2 down to
Cell N15). Select "OK".

In Cell L2 type "=LOOKUP(A2,N2:O15,O2:O15)". You can copy Cell A2 down as
far as you like. This tells excel to lookup the value that is represented my
your selection in A2.
If you are going to copy L2, you will have to change it to
"=LOOKUP(A2,$N$2:$O$15,$O$2:$O$15)" The "$" lock in the position so it will
not change.
hth
Dennis
 
This option seemed to work best for me. Thanks!

FloMM2 said:
Brent,
you will have to move the data from "Sheet B" to an unused area on "Sheet
A". Once that is done, try this:
In Cell A2, select Data, then select Validation.
The "Settings" tab:
the "Allow: " drop down, select list. "Source:" select the list of schools
(it will look like "=$N$2:$N$15", if the list is in Column N, Cell N2 down to
Cell N15). Select "OK".

In Cell L2 type "=LOOKUP(A2,N2:O15,O2:O15)". You can copy Cell A2 down as
far as you like. This tells excel to lookup the value that is represented my
your selection in A2.
If you are going to copy L2, you will have to change it to
"=LOOKUP(A2,$N$2:$O$15,$O$2:$O$15)" The "$" lock in the position so it will
not change.
hth
Dennis
Click to expand...
 
Thanks, Kassie. I'll give it a try.

Kassie said:
Why not use Data Validation to select the school name, and then in L1 a
loolkup formula?

To name a range, select, or block it, then in the address bar, enter the
name you want, and press <Enter>. If you block A1:B12, the address bar will
indicate A1.

First, block your list of schools, but exclude the distances, and name it
Schools. Secondly, block the list of schools with mileages, and name it
Distances.

In A1, Click on Data, Validation, replace any value with list, and in the
formula box type in =Schools

In L1, enter the following formula
=IF(A1="","",VLOOKUP(A1,Distances,2,0))

If you now click on the down arrow in A1, you will get a list of schools.
As you type, it will start selecting a school, until you have the school you
require. When you press <Enter> the distance will appear in L1

--

HTH

Kassie

Replace xxx with hotmail
Click to expand...
 
Back
Top