Sums using ranges.

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hello,

I am using a List in a data Valadation style drop down
menu which gives the user a choice between 14 titles in
the list. What I would like to do, is in each row, give a
value to each of the titles. so for example:

row a3 has the data drop menu with 14 choices.
lets say a user picks "Menu Item 3"
I would like to have row a4 give a value or a cost for
that item selected.

so for column 4 each time a menu item is chosen in column
3 I want to fill the cell next to it with a cost per item
chosen.

IS there an easy way to do this?

The menu items and the cost I would like to give them are
as follows btw these are cell ranges n4:o17

Column N Titles Column O Prices

C Shell - Clear $0.25
C Shell - Green $0.25
C Shell - Orange $0.25
C Shell - Pink $0.25
C Shell - Purple $0.25
C Shell - Yellow $0.25
Slim Line W/ Black Tray $0.25
Jewel Case W/ Black Tray $0.25
Trim Pack CD Holder $0.25
Clear Slim Pack Holder - Round $0.20
CD Paper Sleeve - Window $0.12
Tyvack with Window $0.15
6X6 White Mailer $0.21
Tin CD Holder $1.80

but then I want to have this formula in 200 rows as
well...

I hope you may have some thoughts on this issue for me
because all of my formulas so far return errors!

thanks.
 
Tom said:
Hello,

I am using a List in a data Valadation style drop down
menu which gives the user a choice between 14 titles in
the list. What I would like to do, is in each row, give a
value to each of the titles. so for example:

row a3 has the data drop menu with 14 choices.
lets say a user picks "Menu Item 3"
I would like to have row a4 give a value or a cost for
that item selected.

so for column 4 each time a menu item is chosen in column
3 I want to fill the cell next to it with a cost per item
chosen.

IS there an easy way to do this?

The menu items and the cost I would like to give them are
as follows btw these are cell ranges n4:o17

Column N Titles Column O Prices

C Shell - Clear $0.25
C Shell - Green $0.25
C Shell - Orange $0.25
C Shell - Pink $0.25
C Shell - Purple $0.25
C Shell - Yellow $0.25
Slim Line W/ Black Tray $0.25
Jewel Case W/ Black Tray $0.25
Trim Pack CD Holder $0.25
Clear Slim Pack Holder - Round $0.20
CD Paper Sleeve - Window $0.12
Tyvack with Window $0.15
6X6 White Mailer $0.21
Tin CD Holder $1.80

but then I want to have this formula in 200 rows as
well...

I hope you may have some thoughts on this issue for me
because all of my formulas so far return errors!

thanks.
Click to expand...

There seems to be some confusion between rows and columns in your post. I
will assume you are selecting in column C and you want the result returned
in column D, and that you are starting in row1. (If not, adjust the
references accordingly). The formula for D1 is
=VLOOKUP(C1,$N$4:$O$17,2,0)
Then drag this down down column D for 200 rows.
 
A simple Vlookup should do what you need:
=VLOOKUP($A$3,$N$4:$O$17,2,0)

You can copy this down as far as you need.
If the lookup cell will change from A3, remove the absolutes ($), from A3 in
the formula.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hello,

I am using a List in a data Valadation style drop down
menu which gives the user a choice between 14 titles in
the list. What I would like to do, is in each row, give a
value to each of the titles. so for example:

row a3 has the data drop menu with 14 choices.
lets say a user picks "Menu Item 3"
I would like to have row a4 give a value or a cost for
that item selected.

so for column 4 each time a menu item is chosen in column
3 I want to fill the cell next to it with a cost per item
chosen.

IS there an easy way to do this?

The menu items and the cost I would like to give them are
as follows btw these are cell ranges n4:o17

Column N Titles Column O Prices

C Shell - Clear $0.25
C Shell - Green $0.25
C Shell - Orange $0.25
C Shell - Pink $0.25
C Shell - Purple $0.25
C Shell - Yellow $0.25
Slim Line W/ Black Tray $0.25
Jewel Case W/ Black Tray $0.25
Trim Pack CD Holder $0.25
Clear Slim Pack Holder - Round $0.20
CD Paper Sleeve - Window $0.12
Tyvack with Window $0.15
6X6 White Mailer $0.21
Tin CD Holder $1.80

but then I want to have this formula in 200 rows as
well...

I hope you may have some thoughts on this issue for me
because all of my formulas so far return errors!

thanks.
 
Back
Top