Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Help with Formula (data validation maybe?)
I have a sheet that looks like this with phone #s filled in
a b c d e home home2 car office office2 1 Tom 2 Bill 3 Steve 4 Harry 5 John I have created a drop down menu for the names, and a dropdown menu for the locations. What I want to do, is create a formula or something to put in a box next to these menus so that when you select, for example, Tom + home2 it will give you THAT #, and if you select Steve + car it will give you THAT #, and so on Any help would be GREATLY appreciated! Scott |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Help with Formula (data validation maybe?)
Your table:
A B C D E F 1 home home2 car office office2 2 Tom 3 Bill 4 Steve 5 Harry 6 John Assuming name is in A8 and location in B8 enter formula in C8: =INDEX(B2:F6,MATCH(A8,A2:A6,0),MATCH(B8,B1:F1,0)) HTH -- AP a écrit dans le message de ups.com... I have a sheet that looks like this with phone #s filled in a b c d e home home2 car office office2 1 Tom 2 Bill 3 Steve 4 Harry 5 John I have created a drop down menu for the names, and a dropdown menu for the locations. What I want to do, is create a formula or something to put in a box next to these menus so that when you select, for example, Tom + home2 it will give you THAT #, and if you select Steve + car it will give you THAT #, and so on Any help would be GREATLY appreciated! Scott |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Help with Formula (data validation maybe?)
Hi Scott,
1. Select A1:E5, and then Choose Insert | Name | Create, and then click OK. 2. Assume that: a. you have made linked cells in H1, I1 to each of your dropdown menu respectively, b. In H2, enter "=INDEX(ListRange1, 1, H1)" In I2, enter "=INDEX(ListRange2, ,I1, 1)" where ListRange1 = {home, home2, ...} and ListRange2 = {Tom, Bill, ..... } c. Enter the following formula in a cell =ADDRESS(ROW(INDIRECT(H2) INDIRECT(H2)), COLUMN(INDIRECT(H2), INDIRECT(H2)) Hope this helps Frederick Chow wrote in message ups.com... I have a sheet that looks like this with phone #s filled in a b c d e home home2 car office office2 1 Tom 2 Bill 3 Steve 4 Harry 5 John I have created a drop down menu for the names, and a dropdown menu for the locations. What I want to do, is create a formula or something to put in a box next to these menus so that when you select, for example, Tom + home2 it will give you THAT #, and if you select Steve + car it will give you THAT #, and so on Any help would be GREATLY appreciated! Scott |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Help with Formula (data validation maybe?)
thank you for the help. it does help, for a second sheet I am
doing...however I made a mistake when posting, and I gave some wrong info. Here is the actual question that I meant to write. Here is my sheet A B C D E F G 1 x xx xxx xxxx xxxxx 2 Tom =b2 =B2*2.2 =b2*3.1 =b2*9 =b2*2 3 Bill =b3 =B3*2.4 =b3*3.2 =b3*8 =b3*4 4 Steve =b4 =B4*2.12 =b4*6.4 =b4*1 =b4*5 5 Harry =b5 =B5*1.94 =b5*5 =b5*1 =b5*2 6 John =b6 =B6*2.6 =b6*2 =b6*2 =b6*6 In the above example, I put a # in b and it multiplies it by the # in the formula (obviously) I have a drop down menu of the 5 names, and a drop down menu of the x, xx, xxx, xxxx, xxxxx. I basically want to put a box in between these cells where I can type in a # and it would display the result in the 4th box..like below (the NAME, #, X?, result are the column labels) NAME # X? Result (drop down ) (enter manually) (drop down) *** ***would take the formula from the sheet above, and put the end result here. IF you can help I would greatly appreciate it, or if you know of a better way to do this, it would be great as well. Thank you so much in advance, Scott |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Help with Formula (data validation maybe?)
You will have to consider another approach.
The following example should fit your needs: http://cjoint.com/?dwtb7HTh8d HTH -- AP a écrit dans le message de oups.com... thank you for the help. it does help, for a second sheet I am doing...however I made a mistake when posting, and I gave some wrong info. Here is the actual question that I meant to write. Here is my sheet A B C D E F G 1 x xx xxx xxxx xxxxx 2 Tom =b2 =B2*2.2 =b2*3.1 =b2*9 =b2*2 3 Bill =b3 =B3*2.4 =b3*3.2 =b3*8 =b3*4 4 Steve =b4 =B4*2.12 =b4*6.4 =b4*1 =b4*5 5 Harry =b5 =B5*1.94 =b5*5 =b5*1 =b5*2 6 John =b6 =B6*2.6 =b6*2 =b6*2 =b6*6 In the above example, I put a # in b and it multiplies it by the # in the formula (obviously) I have a drop down menu of the 5 names, and a drop down menu of the x, xx, xxx, xxxx, xxxxx. I basically want to put a box in between these cells where I can type in a # and it would display the result in the 4th box..like below (the NAME, #, X?, result are the column labels) NAME # X? Result (drop down ) (enter manually) (drop down) *** ***would take the formula from the sheet above, and put the end result here. IF you can help I would greatly appreciate it, or if you know of a better way to do this, it would be great as well. Thank you so much in advance, Scott |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Help with Formula (data validation maybe?)
THANK YOU! That was perfect...It is done!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Formula Q | Excel Worksheet Functions | |||
Data validation formula | Excel Worksheet Functions | |||
data validation formula | Excel Discussion (Misc queries) | |||
Data Validation with Formula | Excel Discussion (Misc queries) | |||
Data Validation Formula Help | Excel Worksheet Functions |