My dropdownlist is in A2 and this is my formula:
=VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0)
I know how to make a combo box dropdown list, thanks to you guys.
Q : How can I use the combo box dropdown list and look up in sheet 2 ( as
formula)
in other words : INSTEAD of A2 dropdown I'like combo box dropdown and still
VLOOKUP for the other information.
THANKS
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.

It depends on what type of combo box you're using. The main difference
between a combo box and a data validation drop down list is that a combo box
does not occupy a cell. A combo box "floats" above the worksheet. A data
validation drop down list does occupy a cell. So, when you make a selection
from a data validation drop down list that selection is entered as the value
of the cell. When you make a selection from a combo box (there are 2 types
of combo boxes) that selected value is not entered into any cell
automatically. You have to "format" the combo box to enter the selection in
a cell and the cell can be any cell of your choice. The cell that will hold
the selection is called the linked cell. In other words, this cell is linked
to this combo box.

Now comes the fun part!

As I said, there are 2 types of combo boxes, a Forms Toolbar combo box and a
Control Toolbox combo box. Each of these handles the linked cell a different
way. A Forms combo box will return the index number of the selection to the
linked cell. A Control combo box will return the actual selection to the
linked cell. For example:

Tom
Bill
Sue
Lisa

Let's say you select Sue from the combo box. If it's a Forms cb, then the
linked cell will return the index number 3 because Sue is the 3rd item in
the cb. A Control cb will return the actual selection Sue to the linked
cell.

So, if you need a formula that uses the selection from the cb you have to
reference the linked cell. If it's a Control cb then the reference is pretty
straightforward. If it's a Forms cb then you have to "translate" the
selection index number to the actual selection. In other words, you have to
somehow make 3 = Sue.

The easiest way to do this is to use an INDEX function. You would index the
source of the cb and use the linked cell to tell INDEX which value you want.

Suppose the list of names above is in the range A1:A4 and has the defined
name of Names. The source of the cb would be Names. Let's assume the linked
cell is B1. To do your lookup:

=VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0)

If it's Control cb:

=VLOOKUP(B1,sheet2!A1:E22,COLUMNS(A:B),0)

If it's Forms cb:

=VLOOKUP(INDEX(Names,B1),sheet2!A1:E22,COLUMNS(A:B ),0)

Here's another "neat" thing about combo boxes. Since they "float" above the
worksheet you can "hide" stuff under them. This is the perfect location of
the lnked cell.


--
Biff
Microsoft Excel MVP


"Learning Excel" wrote in message
...
My dropdownlist is in A2 and this is my formula:
=VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0)
I know how to make a combo box dropdown list, thanks to you guys.
Q : How can I use the combo box dropdown list and look up in sheet 2 ( as
formula)
in other words : INSTEAD of A2 dropdown I'like combo box dropdown and
still
VLOOKUP for the other information.
THANKS
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.

a b c d d e f a c b
2 3 4 6 5 6 7 8 9 10
10 6 4 9 2 7 8 8 9 6

a 96
b 66
c
d

what formula should I give to get this answer

No idea.

Please describe the logic behind why a should = 96 and b should = 66 and c&d
should = nothing.


Gord Dibben MS Excel MVP

On Fri, 18 Jan 2008 01:46:00 -0800, Nimish Shah
wrote:

a b c d d e f a c b
2 3 4 6 5 6 7 8 9 10
10 6 4 9 2 7 8 8 9 6

a 96
b 66
c
d

what formula should I give to get this answer

I think these are the total of the two rows with each element
multiplied together, so:

a = 2*8 (first row) + 10*8 (second row) = 96
b = 3*10 (first row) + 6*6 (second row) = 66

Presumably the OP didn't work out the values for c and d.

Pete

On Jan 18, 11:28*pm, Gord Dibben <gorddibbATshawDOTca wrote:
No idea.

Please describe the logic behind why a should = 96 and b should = 66 and c&d
should = nothing.

Gord Dibben *MS Excel MVP

On Fri, 18 Jan 2008 01:46:00 -0800, Nimish Shah



wrote:
a * b c d * * * * * * *d e f a c *b
2 * 3 4 6 * * * * * * *5 6 7 8 9 10
10 6 4 9 * * * * * * * 2 7 8 8 9 6

a 96
b 66
c
d

what formula should I give to get this answer- Hide quoted text -

- Show quoted text -

Price per each Nos.
a b c d d f c b a

20 10 10 10 10 10 20 8 20
15 15 5 15 5 15 82 70 82
10 5 10 10 10 5 5 62 10
5 5 8 5 8 5 6 5 6
25 20 60 6 60 16 8 25 8
30 25 20 4 20 80 17 30 17


a 2470
b 2715
c 1528
d 755
f 0

I want a formula where it will search for "a" in table "Pirce per each" and
also in "nos" table and if it match then they will sumproduct the common
column and give result below beside"a", "b", "c" etc.


"Gord Dibben" wrote:

No idea.

Please describe the logic behind why a should = 96 and b should = 66 and c&d
should = nothing.


Gord Dibben MS Excel MVP

On Fri, 18 Jan 2008 01:46:00 -0800, Nimish Shah
wrote:

a b c d d e f a c b
2 3 4 6 5 6 7 8 9 10
10 6 4 9 2 7 8 8 9 6

a 96
b 66
c
d

what formula should I give to get this answer

That is a humungous explanation wich I thank for the time as it detailed
and give me the choices to use the more appropriate solution. Thanks a lot
T.Valko for going the extra mile
to explain this topic. ( apologies for the late reply, just came back).
Thanks.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"T. Valko" wrote:

It depends on what type of combo box you're using. The main difference
between a combo box and a data validation drop down list is that a combo box
does not occupy a cell. A combo box "floats" above the worksheet. A data
validation drop down list does occupy a cell. So, when you make a selection
from a data validation drop down list that selection is entered as the value
of the cell. When you make a selection from a combo box (there are 2 types
of combo boxes) that selected value is not entered into any cell
automatically. You have to "format" the combo box to enter the selection in
a cell and the cell can be any cell of your choice. The cell that will hold
the selection is called the linked cell. In other words, this cell is linked
to this combo box.

Now comes the fun part!

As I said, there are 2 types of combo boxes, a Forms Toolbar combo box and a
Control Toolbox combo box. Each of these handles the linked cell a different
way. A Forms combo box will return the index number of the selection to the
linked cell. A Control combo box will return the actual selection to the
linked cell. For example:

Tom
Bill
Sue
Lisa

Let's say you select Sue from the combo box. If it's a Forms cb, then the
linked cell will return the index number 3 because Sue is the 3rd item in
the cb. A Control cb will return the actual selection Sue to the linked
cell.

So, if you need a formula that uses the selection from the cb you have to
reference the linked cell. If it's a Control cb then the reference is pretty
straightforward. If it's a Forms cb then you have to "translate" the
selection index number to the actual selection. In other words, you have to
somehow make 3 = Sue.

The easiest way to do this is to use an INDEX function. You would index the
source of the cb and use the linked cell to tell INDEX which value you want.

Suppose the list of names above is in the range A1:A4 and has the defined
name of Names. The source of the cb would be Names. Let's assume the linked
cell is B1. To do your lookup:

=VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0)

If it's Control cb:

=VLOOKUP(B1,sheet2!A1:E22,COLUMNS(A:B),0)

If it's Forms cb:

=VLOOKUP(INDEX(Names,B1),sheet2!A1:E22,COLUMNS(A:B ),0)

Here's another "neat" thing about combo boxes. Since they "float" above the
worksheet you can "hide" stuff under them. This is the perfect location of
the lnked cell.


--
Biff
Microsoft Excel MVP


"Learning Excel" wrote in message
...
My dropdownlist is in A2 and this is my formula:
=VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0)
I know how to make a combo box dropdown list, thanks to you guys.
Q : How can I use the combo box dropdown list and look up in sheet 2 ( as
formula)
in other words : INSTEAD of A2 dropdown I'like combo box dropdown and
still
VLOOKUP for the other information.
THANKS
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Learning Excel" wrote in message
...
That is a humungous explanation wich I thank for the time as it detailed
and give me the choices to use the more appropriate solution. Thanks a lot
T.Valko for going the extra mile
to explain this topic. ( apologies for the late reply, just came back).
Thanks.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"T. Valko" wrote:

It depends on what type of combo box you're using. The main difference
between a combo box and a data validation drop down list is that a combo
box
does not occupy a cell. A combo box "floats" above the worksheet. A data
validation drop down list does occupy a cell. So, when you make a
selection
from a data validation drop down list that selection is entered as the
value
of the cell. When you make a selection from a combo box (there are 2
types
of combo boxes) that selected value is not entered into any cell
automatically. You have to "format" the combo box to enter the selection
in
a cell and the cell can be any cell of your choice. The cell that will
hold
the selection is called the linked cell. In other words, this cell is
linked
to this combo box.

Now comes the fun part!

As I said, there are 2 types of combo boxes, a Forms Toolbar combo box
and a
Control Toolbox combo box. Each of these handles the linked cell a
different
way. A Forms combo box will return the index number of the selection to
the
linked cell. A Control combo box will return the actual selection to the
linked cell. For example:

Tom
Bill
Sue
Lisa

Let's say you select Sue from the combo box. If it's a Forms cb, then the
linked cell will return the index number 3 because Sue is the 3rd item in
the cb. A Control cb will return the actual selection Sue to the linked
cell.

So, if you need a formula that uses the selection from the cb you have to
reference the linked cell. If it's a Control cb then the reference is
pretty
straightforward. If it's a Forms cb then you have to "translate" the
selection index number to the actual selection. In other words, you have
to
somehow make 3 = Sue.

The easiest way to do this is to use an INDEX function. You would index
the
source of the cb and use the linked cell to tell INDEX which value you
want.

Suppose the list of names above is in the range A1:A4 and has the defined
name of Names. The source of the cb would be Names. Let's assume the
linked
cell is B1. To do your lookup:

=VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0)

If it's Control cb:

=VLOOKUP(B1,sheet2!A1:E22,COLUMNS(A:B),0)

If it's Forms cb:

=VLOOKUP(INDEX(Names,B1),sheet2!A1:E22,COLUMNS(A:B ),0)

Here's another "neat" thing about combo boxes. Since they "float" above
the
worksheet you can "hide" stuff under them. This is the perfect location
of
the lnked cell.


--
Biff
Microsoft Excel MVP


"Learning Excel" wrote in
message
...
My dropdownlist is in A2 and this is my formula:
=VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0)
I know how to make a combo box dropdown list, thanks to you guys.
Q : How can I use the combo box dropdown list and look up in sheet 2
( as
formula)
in other words : INSTEAD of A2 dropdown I'like combo box dropdown and
still
VLOOKUP for the other information.
THANKS
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.