add totals via drop down menu

[excellentexcel]

hi
please excuse the crude spreadsheet attached but it gets the message across
im looking for cell j3 to add up al the things in row 3
so in the attached case it will come to £3.00
you will understand why from the quantities
it anyone could hep or has questions please ask

[excellentexcel]

possibly a vlookup function???

[NBVC]

Not sure how you get £3.00 if would expect £1.80 maybe...

[excellentexcel]

hi nbvc
hope you are well
well there are 3 (cell c3) x chocolate @ £0.50p each = £1.50
1 (cell e3) x sweet = £0.30
2 (cell g3) x drink @ £0.60p each = £1.20

so cell j3 total is £3

[NBVC]

I don't see G3 filled in....

anyways...try this formula:

=SUM(IF(ISNUMBER(SEARCH($L$3:$L$7,B3:H3)),$M$3:$M$7)*IF(ISNUMBER(SEARCH(B3:H3,$L$3:$L$7)),C3:I3))

confirmed with CTRL+SHIFT+ENTER and copied down.

[excellentexcel]

nbvc
my apologies
in the one id saved i hadnt filled in g3
the formula works well so thank you for that
can i ask why the formula was entered with ctrl+shift+enter?
whats does that do to do it that way??
regards

[NBVC]

It's a special Array formula...

See here for a good explanation...

http://www.cpearson.com/excel/ArrayFormulas.aspx

[excellentexcel]

thanks very much
time for a read

[excellentexcel]

nbvc
if we say the sheet is clean
is there any way in which you can set a default into the qty cell (cell c3) of 1 if an entry is made into cell b3 and the user can over ride if needs be?

regards

[NBVC]

nbvc
if we say the sheet is clean
is there any way in which you can set a default into the qty cell (cell c3) of 1 if an entry is made into cell b3 and the user can over ride if needs be?

regards

Once you overwrite it, it won't go back unless you are using this as a template and only want the 1's at the start and don't care if users change as long as you have the template..... Then you can use formulas....

But...I think you'll be better off with a VBA worksheet Event macro for that...

Post the question separately in the Programming forum.