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

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

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

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

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

Help with Formula (data validation maybe?)
 

THANK YOU! That was perfect...It is done!