# Tricky Question

#### nathanpeattie

##### New Member
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

Nathan

### 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
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
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
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

I am still not able to do what I want probally I am doing it wrong

here is the file:
http://ncpcomp.dyndns.org/excel/quote.xls

I have all the steels on page two and I need them to showup in the dropdown in page 1 with the other data for the other columns

After the editing in done plz send it to ncampanella@cogeco.ca

Thanks
Nathan

#### Nogslaw

##### Well-known Member
Check your e-mail. Hope it helps! :wink:

#### nathanpeattie

##### New Member

THANKS ALL

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
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

#### Barrie Davidson

##### MrExcel MVP
Is this formula in column M what you need?

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

#### nathanpeattie

##### New Member
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

Replies
4
Views
269
Replies
4
Views
95
Replies
14
Views
956
Replies
13
Views
619
Replies
0
Views
125