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 meform.JPGMAIN SHEET.JPG
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
Sorry, thanks for letting me know I have now attached a workbook example. Thanks again
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
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.
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),"")
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.
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)
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???
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks