Tricky Question

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43
I am making a manufacturing quote sheet with steels specs, weights, lengths, bought sizes, cost and final cost. My problem, I have created a dropdown list of all the steels

eg:
2 X 1 X 1/8W
2" SQ X 1/8W
2" SQ X 8 GA
1/2 SCH 40
76A X 5 1/2
3/8 X 1 1/4
1/4 X 6
I.D Plate
1/8 X 6
Kennedy
LVL
1738
TNT 40197
TNT 44298
1/4 X 20

and if I select fr4om the dropdown 2 X 1 X 1/8W I want 3 other fields to show default values.

I have got the list setup in Column D and I have Weight in Column G, Bought Size in H and Cost in L

I want to select a value in D and then vales G,H,L show up after secting value from column D

Thanks in advance!
Nathan
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Welcome to MrExcel!

The VLOOKUP function would work perfectly here. Syntax would be something like

=VLOOKUP(D1,{LOOKUP TABLE ADDRESS},{COLUMN NUMBER TO RETURN},0)

Is that enough information for you to go on?
 

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43
I don't think that will work D1 is a drop down list that I created on a second page that is defined through a Validate > List

I have multiple rows I have to work with each row will have a different value for column D hence

D1 dropdown to 2 X 1 X 1/8W
D2 dropdown to 2" SQ X 1/8W and so on

so the dropdown contains about 10 - 15 different options to choose from rate now so when I click in D1 and choose from the dropdown 2 X 1 X 1/8W I want Column G1 to say 2.19, Column H1 to say 80X24 and Column L1 to say 58.10

All from just clicking in the dropdown

Thanks
Nathan
 

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
That's what a VLOOKUP formula does. Example:

Your dropdown box would be in cell A8
in cell B8, you would have the following formula =VLOOKUP(A8,A1:D5,2,FALSE)

Here's a visual:
Book3
ABCD
11blueTomA
22redDaveB
33greenRickC
44whiteBobD
55blackSteveE
6
7ChoiceColorNameLetter
83greenRickC
Sheet1
 

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43

ADVERTISEMENT

THANKS ALL

you have been must helpfull!

Thanks for your time and hard work!

I am sure I am going to have more problems in the short time future I will be sure to come and ask for your very helpfull assistance.

Thanks Again
Nathan
 

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43
OK Next thing I have to do is if there is a zero value for columns G,H,I then in stead of column J doing a =product(G??,H??,I??) then I want the column M to take Column L and multiply L & C to give value in Column M.

But if there is values in G,H,I I want J to do =product(G?,H?,I?) which then column M takes =product(J?,L?/100)

I hope you understand what I meen.

Thanks
Nathan

I have uploaded the newly edited file just download it again from the above address.
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Is this formula in column M what you need?

=IF(J21=0,PRODUCT(J21,L21/100),PRODUCT(C21,L21))
 

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43
Thanks That worked great but I just had to change the code around

=IF(J21=0,PRODUCT(C21,L21),PRODUCT(J21,L21/100)) for Column M

=PRODUCT(G21,H21,I21) for column J

and it works great!

Thanks A Bunch!

Nathan
 

Watch MrExcel Video

Forum statistics

Threads
1,118,914
Messages
5,575,011
Members
412,634
Latest member
Sumanmathew
Top