Tricky Question

[nathanpeattie]

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

 

[Barrie Davidson]

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]

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]

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
	A	B	C	D
1	1	blue	Tom	A
2	2	red	Dave	B
3	3	green	Rick	C
4	4	white	Bob	D
5	5	black	Steve	E
6
7	Choice	Color	Name	Letter
8	3	green	Rick	C
Sheet1

 

[nathanpeattie]

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]

Check your e-mail. Hope it helps! :wink:

 

[nathanpeattie]

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]

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]

Is this formula in column M what you need?

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

 

[nathanpeattie]

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