Need help with input and data validation formulas

kyle

New member
Joined
May 27, 2014
Messages
12
Reaction score
0
Points
0
Hi All

Found the site searching the web while trying to find help in creating a spreadsheet i am trying to create. I only have a basic knowledge of excel and would love to learn more and thought this place looked like the right place to do this.

I dont know how to explain this but i will give it a shot.

i have 2 sheets the second sheet contains the following information

75 x 405.92
100 x 508.33
125 x 6511.9
150 x 7517.7
180 x 7520.9
200 x 7522.9
230 x 7525.1
250 x 9035.5
300 x 9040.1
380 x 10055.2


first column is steel size, the second column is the weight for that steel section in calculated in meters. Ie 75x40 is 5.92kg for 1 meter

My first sheet i want to create the following

a Data validation list which i can select the steel size (i know how to create this).
When i have chosen the steel size from the list i want it to display its weight in the next column next to the list as shown above.

how do i create this?

i have a few other questions, but dont want to bomb the first thread with everything

Thanks in advance
Kyle
 
Good morning,

In the column to the right of your validation use this:

=iferror ( vlookup ( **first validation cell [i.e. a2]**, **Sheet 2 array in absolute format [i.e. Sheet2!$A$1:$B$30]**, 2 [assuming the array is just two columns -- vlookup finds the leftmost column and then returns the n-th column, in this case the 2nd], FALSE [for an exact match] ) , "" [If an error occurs such as no data found -- iferror -- the formula will return blank rather than an error message] ).

Then drag down as necessary.

Hopefully this is easy enough to follow. Best of luck,
 
Thank you very much BGOREE09, i will give this a shot
 
Hi Bgoree09

I tried doing what you have typed but it keeps displaying an error, and does not like the formula, or i am doing something wrong, which is probably more like it.

I have included the file with information under it what i am trying to achieve?

If i select a steel size from the validation list from the drop down menu in (a5), in the next cell next to it (b5) i want it to display that steel weight from the second column in sheet 2.

(c5) is where i will enter the length in meters and (d5) will multiply (c5) to display total weight.


View attachment steel.xlsx

Sorry for being a pain but would really appreciate the help, as i am only new to excel

Once again thanks in advanced

Kyle
 
Sorry i have worked it out from your above script just had to be modified slightly the formula i used was =vlookup(a5,xx:xx,2,false) no need for the =iferror..thanks again bg
 
Back
Top