Thread
:
using more than 1 formula in the same cell in excel
View Single Post
#
5
Posted to microsoft.public.excel.worksheet.functions
Sandy Mann
external usenet poster
Posts: 2,345
using more than 1 formula in the same cell in excel
With the table of items/materials/Costs in B2:D9 as in:
A
B
C
1
Clothing Type
Material
Cost
2
Jumper
Wool
£3.00
3
Jumper
silk
£5.00
4
Jumper
cotton
£2.00
5
Shirt
Silk
£5.00
6
Shirt
Wool
£4.00
7
Shirt
velvet
£7.00
8
Shirt
cotton
£3.00
=INDEX(C2:C8,SUMPRODUCT((A2:A8=F1)*((B2:B8=G1)*(RO W(A2:A8)-1))))
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"cerobeth" wrote in message
...
Thanks for your help. You make it sound so easy, but will that work
having
so many variations? Column A can be any number of items, column B has
several items but only 1 of these items needs to be recognised in the
formula
and C will give a different price for each of items A.
"Gav123" wrote:
You could use data validation for the drop down boxes..
See Here.
http://www.contextures.com/xlDataVal01.html
Then the VLOOKUP function for the prices.
for example your information is contained Sheet2 A2:C50
A B C
1 Clothing type Material Cost
2 Jumper Wool £3
3 Shirt Silk £5
Your formula might look like this (assuming your drop down lists are in
Sheet1
cell A2 (Clothing type) Cell B2 (Material)
In Sheet1 cell C2 type...
=VLOOKUP(A2,Sheet2!A2:C50,3,0)
This will return the following...
If Jumper is selected from the first drop down list cell C2 will show £3.
Hope this helps,
Gav.
"cerobeth" wrote:
I am trying to create an order form where column A has a drop down box
to
choose an item and column B has a drop down box to choose a category.
Depending on the choices selected I need to create variable prices.
ie if A = jumper, B = wool then C = £3, but if A = shirt, B = silk then
C = £5
Can anyone help me please
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann