If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Worksheet formula - auto populate
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 |
#2
|
|||
|
|||
Worksheet formula - auto populate
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" wrote: 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 |
#3
|
|||
|
|||
Worksheet formula - auto populate
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 "Brent" wrote: 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 |
#4
|
|||
|
|||
Worksheet formula - auto populate
This option seemed to work best for me. Thanks!
"FloMM2" wrote: 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 "Brent" wrote: 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 |
#5
|
|||
|
|||
Worksheet formula - auto populate
Thanks, Kassie. I'll give it a try.
"Kassie" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|