Results 1 to 5 of 5

Thread: Need help with input and data validation formulas

  1. #1

    Need help with input and data validation formulas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 40 5.92
    100 x 50 8.33
    125 x 65 11.9
    150 x 75 17.7
    180 x 75 20.9
    200 x 75 22.9
    230 x 75 25.1
    250 x 90 35.5
    300 x 90 40.1
    380 x 100 55.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

  2. #2
    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,

  3. #3
    Thank you very much BGOREE09, i will give this a shot

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


    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •