+ Reply to Thread
Results 1 to 9 of 9

Have Excel Form want to have data entered into 3 columns auto when data entered in cell

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2007 2010
    Posts
    5

    Have Excel Form want to have data entered into 3 columns auto when data entered in cell

    I have a form created in excel, I want to be able to enter either a code in Column A and have Column B and C auto fill or enter part of the description in Column B and have Column A and B auto fill. I have a table of the product codes, description and price on a separate sheet labelled MAIN Please Help me form.JPGMAIN SHEET.JPG

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: Have Excel Form want to have data entered into 3 columns auto when data entered in cel

    Want to get your question answered quickly?

    Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    01-23-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2007 2010
    Posts
    5

    Re: Have Excel Form want to have data entered into 3 columns auto when data entered in cel

    Sorry, thanks for letting me know I have now attached a workbook example. Thanks again
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,055

    Re: Have Excel Form want to have data entered into 3 columns auto when data entered in cel

    GV Gals, welcome to the forum

    A few points.....
    1. the table in MAIN contains no data to check against.
    2. With regular formulas, in any cell, you can either have a formula, or enter data...you cannot have both. So, you can EITHER have B and C filled based on A, OT A and C filled based on a partial entry in B. Take your pick?
    3. Using A as the criteria, ise this in B, copied down...
    =VLOOKUP(A2,Sheet1!$A$10:$E$40,2,FALSE)
    and in C, use this, copied down...
    =VLOOKUP(A2,Sheet1!$A$10:$E$40,5,FALSE)

    Hope this helps?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    01-23-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2007 2010
    Posts
    5

    Re: Have Excel Form want to have data entered into 3 columns auto when data entered in cel

    Hi FDibbins, thanks for your help, I am not sure what you mean but the MAIN doesn't have any data,it has data in Column A Product Code, Column B Description and Column C is Price. I tried your formula but because it's pointing to Sheet1 I am getting errors, I tried ti with pointing to MAIN but also get an error.

    When I put 3002 in Column A11 with the below formulas in B and C I get #N/A in B and C

    I entered this into Column B of the Form =VLOOKUP(A11,Main!$A$11:$E$40,2,FALSE)
    Column C of the Form =VLOOKUP(A11,Main!$A$11:$E$40,5,FALSE)

    I don't need to go all the way to Column E only B and C.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,055

    Re: Have Excel Form want to have data entered into 3 columns auto when data entered in cel

    ok, maybe I got the sheets mixed around lol

    in sheet1, column B, copied down, use this...
    =IFERROR(VLOOKUP(A11,Main!$A$2:$C$12,2,FALSE),"")
    and for the cost, use thios, copied down...
    =IFERROR(VLOOKUP(A11,Main!$A$2:$C$12,3,FALSE),"")

  7. #7
    Registered User
    Join Date
    01-23-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2007 2010
    Posts
    5

    Re: Have Excel Form want to have data entered into 3 columns auto when data entered in cel

    Okay, thanks got that working but now since the forumlas are in the column I am getting an #Value! error in Column E the total price of Row. So not all of rows will have data in them so I would want the #Vaule! error to not bee seen, so i only 5 rows have data the bottom can still sum E11:E40 with any vaule errors.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,055

    Re: Have Excel Form want to have data entered into 3 columns auto when data entered in cel

    change the formula in E to...=IF(C11="","",C11*D11)

    also, you may want to check the sum formula in E41, i think it should be =SUM(E11:E40) not =SUM(B8:H40)

  9. #9
    Registered User
    Join Date
    01-23-2013
    Location
    New Zealand
    MS-Off Ver
    Excel 2007 2010
    Posts
    5

    Re: Have Excel Form want to have data entered into 3 columns auto when data entered in cel

    Thanks for all of your help, I really appreciate it, I did see that sum error and fixed it and now all is going good. Now the only thing that would make this the best form, if it's possible is that if there is data in a row to have a data Validate maybe asking to enter the QTY needed. Is that possible???

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1