I am fairly ignorant of the more complicated aspects of Excel so I hope I can
get help with this problem.
I have a sheet with column C for quantity , column D for items and column E
for the cost. In column D I defined a drop down list with various materials
that we sell. I made each item in the list a Name which referred to a
specific value (e.g. safety glasses refers to $3). I want to create a formula
for each cell in column E which would be the product of column C times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also I need to
formula to work on every cell in column E referring to the cells C and D in
the same row.

Thanks,

Nick

Make it easy!

Create a 2 column table with the product in the left column and the
corresponding price in the right column:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

Then:

C2 = quantity = 5
D2 = drop down list with prod names

Formula entered in E2:

=C2*VLOOKUP(D2,A2:B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
I am fairly ignorant of the more complicated aspects of Excel so I hope I
can
get help with this problem.
I have a sheet with column C for quantity , column D for items and column
E
for the cost. In column D I defined a drop down list with various
materials
that we sell. I made each item in the list a Name which referred to a
specific value (e.g. safety glasses refers to $3). I want to create a
formula
for each cell in column E which would be the product of column C times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also I need to
formula to work on every cell in column E referring to the cells C and D
in
the same row.

Thanks,

Nick

Biff,
Thanks, that helps, but I don't know how to make that formula automatically
work for E3, E4, etc. I need to generalize the formula to look at the
corresponding C and D cells on any given row.

thanks,

Nick



"T. Valko" wrote:

Make it easy!

Create a 2 column table with the product in the left column and the
corresponding price in the right column:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

Then:

C2 = quantity = 5
D2 = drop down list with prod names

Formula entered in E2:

=C2*VLOOKUP(D2,A2:B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
I am fairly ignorant of the more complicated aspects of Excel so I hope I
can
get help with this problem.
I have a sheet with column C for quantity , column D for items and column
E
for the cost. In column D I defined a drop down list with various
materials
that we sell. I made each item in the list a Name which referred to a
specific value (e.g. safety glasses refers to $3). I want to create a
formula
for each cell in column E which would be the product of column C times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also I need to
formula to work on every cell in column E referring to the cells C and D
in
the same row.

Thanks,

Nick

Make the references to the lookup table absolute then just drag copy down
the column as needed.

=C2*VLOOKUP(D2,A$2:B$4,2,0)

As you copy down, the cell references will increment accordingly.


--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Biff,
Thanks, that helps, but I don't know how to make that formula
automatically
work for E3, E4, etc. I need to generalize the formula to look at the
corresponding C and D cells on any given row.

thanks,

Nick



"T. Valko" wrote:

Make it easy!

Create a 2 column table with the product in the left column and the
corresponding price in the right column:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

Then:

C2 = quantity = 5
D2 = drop down list with prod names

Formula entered in E2:

=C2*VLOOKUP(D2,A2:B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
I am fairly ignorant of the more complicated aspects of Excel so I hope
I
can
get help with this problem.
I have a sheet with column C for quantity , column D for items and
column
E
for the cost. In column D I defined a drop down list with various
materials
that we sell. I made each item in the list a Name which referred to a
specific value (e.g. safety glasses refers to $3). I want to create a
formula
for each cell in column E which would be the product of column C times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also I need
to
formula to work on every cell in column E referring to the cells C and
D
in
the same row.

Thanks,

Nick

Biff,
That worked perfectly, many thanks. The copy drag worked like magic.
Now for my education, what does the 2,0 at the end of the formula do?
I take it that the $ sign means that those cells should interact with
whatever cells are chosen for the rest of the formula?

Nick

"T. Valko" wrote:

Make the references to the lookup table absolute then just drag copy down
the column as needed.

=C2*VLOOKUP(D2,A$2:B$4,2,0)

As you copy down, the cell references will increment accordingly.


--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Biff,
Thanks, that helps, but I don't know how to make that formula
automatically
work for E3, E4, etc. I need to generalize the formula to look at the
corresponding C and D cells on any given row.

thanks,

Nick



"T. Valko" wrote:

Make it easy!

Create a 2 column table with the product in the left column and the
corresponding price in the right column:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

Then:

C2 = quantity = 5
D2 = drop down list with prod names

Formula entered in E2:

=C2*VLOOKUP(D2,A2:B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
I am fairly ignorant of the more complicated aspects of Excel so I hope
I
can
get help with this problem.
I have a sheet with column C for quantity , column D for items and
column
E
for the cost. In column D I defined a drop down list with various
materials
that we sell. I made each item in the list a Name which referred to a
specific value (e.g. safety glasses refers to $3). I want to create a
formula
for each cell in column E which would be the product of column C times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also I need
to
formula to work on every cell in column E referring to the cells C and
D
in
the same row.

Thanks,

Nick

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

=C2*VLOOKUP(D2,A$2:B$4,2,0)
=C2*VLOOKUP(D2,A$2:B$4,2,FALSE)

You have a 2 column lookup table. The 2 in the formula is the column number
of the table from which to get the result. VLOOKUP searches the leftmost
column of table for the lookup value D2. You have an option of searching for
an exact match or an approximate match. The 0 (or, you can use FALSE) in the
formula means you want an exact match (case not a factor). I always use 0 to
save a few keystrokes.

The $ signs make the row references to the lookup table absolute. That means
the row references will not change when you copy the formula. Since you're
copying the formula *down* a column you want the row references to the
lookup table to remain constant. If you were copying *across* a row then
you'd want the column references to the lookup table to remain constant:

VLOOKUP(D2,$A2:$B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Biff,
That worked perfectly, many thanks. The copy drag worked like magic.
Now for my education, what does the 2,0 at the end of the formula do?
I take it that the $ sign means that those cells should interact with
whatever cells are chosen for the rest of the formula?

Nick

"T. Valko" wrote:

Make the references to the lookup table absolute then just drag copy down
the column as needed.

=C2*VLOOKUP(D2,A$2:B$4,2,0)

As you copy down, the cell references will increment accordingly.


--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Biff,
Thanks, that helps, but I don't know how to make that formula
automatically
work for E3, E4, etc. I need to generalize the formula to look at the
corresponding C and D cells on any given row.

thanks,

Nick



"T. Valko" wrote:

Make it easy!

Create a 2 column table with the product in the left column and the
corresponding price in the right column:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

Then:

C2 = quantity = 5
D2 = drop down list with prod names

Formula entered in E2:

=C2*VLOOKUP(D2,A2:B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
I am fairly ignorant of the more complicated aspects of Excel so I
hope
I
can
get help with this problem.
I have a sheet with column C for quantity , column D for items and
column
E
for the cost. In column D I defined a drop down list with various
materials
that we sell. I made each item in the list a Name which referred to
a
specific value (e.g. safety glasses refers to $3). I want to create
a
formula
for each cell in column E which would be the product of column C
times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also I
need
to
formula to work on every cell in column E referring to the cells C
and
D
in
the same row.

Thanks,

Nick

Excellent, thanks

"T. Valko" wrote:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

=C2*VLOOKUP(D2,A$2:B$4,2,0)
=C2*VLOOKUP(D2,A$2:B$4,2,FALSE)

You have a 2 column lookup table. The 2 in the formula is the column number
of the table from which to get the result. VLOOKUP searches the leftmost
column of table for the lookup value D2. You have an option of searching for
an exact match or an approximate match. The 0 (or, you can use FALSE) in the
formula means you want an exact match (case not a factor). I always use 0 to
save a few keystrokes.

The $ signs make the row references to the lookup table absolute. That means
the row references will not change when you copy the formula. Since you're
copying the formula *down* a column you want the row references to the
lookup table to remain constant. If you were copying *across* a row then
you'd want the column references to the lookup table to remain constant:

VLOOKUP(D2,$A2:$B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Biff,
That worked perfectly, many thanks. The copy drag worked like magic.
Now for my education, what does the 2,0 at the end of the formula do?
I take it that the $ sign means that those cells should interact with
whatever cells are chosen for the rest of the formula?

Nick

"T. Valko" wrote:

Make the references to the lookup table absolute then just drag copy down
the column as needed.

=C2*VLOOKUP(D2,A$2:B$4,2,0)

As you copy down, the cell references will increment accordingly.


--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Biff,
Thanks, that helps, but I don't know how to make that formula
automatically
work for E3, E4, etc. I need to generalize the formula to look at the
corresponding C and D cells on any given row.

thanks,

Nick



"T. Valko" wrote:

Make it easy!

Create a 2 column table with the product in the left column and the
corresponding price in the right column:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

Then:

C2 = quantity = 5
D2 = drop down list with prod names

Formula entered in E2:

=C2*VLOOKUP(D2,A2:B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
I am fairly ignorant of the more complicated aspects of Excel so I
hope
I
can
get help with this problem.
I have a sheet with column C for quantity , column D for items and
column
E
for the cost. In column D I defined a drop down list with various
materials
that we sell. I made each item in the list a Name which referred to
a
specific value (e.g. safety glasses refers to $3). I want to create
a
formula
for each cell in column E which would be the product of column C
times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also I
need
to
formula to work on every cell in column E referring to the cells C
and
D
in
the same row.

Thanks,

Nick

You're welcome!

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Excellent, thanks

"T. Valko" wrote:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

=C2*VLOOKUP(D2,A$2:B$4,2,0)
=C2*VLOOKUP(D2,A$2:B$4,2,FALSE)

You have a 2 column lookup table. The 2 in the formula is the column
number
of the table from which to get the result. VLOOKUP searches the leftmost
column of table for the lookup value D2. You have an option of searching
for
an exact match or an approximate match. The 0 (or, you can use FALSE) in
the
formula means you want an exact match (case not a factor). I always use 0
to
save a few keystrokes.

The $ signs make the row references to the lookup table absolute. That
means
the row references will not change when you copy the formula. Since
you're
copying the formula *down* a column you want the row references to the
lookup table to remain constant. If you were copying *across* a row then
you'd want the column references to the lookup table to remain constant:

VLOOKUP(D2,$A2:$B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Biff,
That worked perfectly, many thanks. The copy drag worked like magic.
Now for my education, what does the 2,0 at the end of the formula do?
I take it that the $ sign means that those cells should interact with
whatever cells are chosen for the rest of the formula?

Nick

"T. Valko" wrote:

Make the references to the lookup table absolute then just drag copy
down
the column as needed.

=C2*VLOOKUP(D2,A$2:B$4,2,0)

As you copy down, the cell references will increment accordingly.


--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
Biff,
Thanks, that helps, but I don't know how to make that formula
automatically
work for E3, E4, etc. I need to generalize the formula to look at
the
corresponding C and D cells on any given row.

thanks,

Nick



"T. Valko" wrote:

Make it easy!

Create a 2 column table with the product in the left column and the
corresponding price in the right column:

...........A............B
1....Prod........Price
2....item1.......3.50
3....item2.......6.25
4....item3.......1.99

Then:

C2 = quantity = 5
D2 = drop down list with prod names

Formula entered in E2:

=C2*VLOOKUP(D2,A2:B4,2,0)

--
Biff
Microsoft Excel MVP


"Nick" wrote in message
...
I am fairly ignorant of the more complicated aspects of Excel so I
hope
I
can
get help with this problem.
I have a sheet with column C for quantity , column D for items
and
column
E
for the cost. In column D I defined a drop down list with
various
materials
that we sell. I made each item in the list a Name which referred
to
a
specific value (e.g. safety glasses refers to $3). I want to
create
a
formula
for each cell in column E which would be the product of column C
times
whatever the value represented by the name chosen in column D.
Seems like this should be possible, but I have no idea how. Also
I
need
to
formula to work on every cell in column E referring to the cells
C
and
D
in
the same row.

Thanks,

Nick