VLOOKUP query

[Dufus]

Hi there,

I wonder if anyone is able to assist me with a problem that I am having. I am trying to assist a friend who runs a small Electrical Contracting business, he is trying to produce a Workbook which allows him to quote for jobs using Excel 2003.

I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations. I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet. I was able to do this and I thought my friend would be very happy with the result, unfortunately he then mentioned that, in addition to the common items listed in the Price Book, there were also an extensive range of additional materials, which were often needed within the quote, whose prices varied from week to week and which he would generally type in manually once he had obtained the latest price and added the labour rate manually. I have also attached my efforts in a workbook entitled Amended Pricing Sheet. The problem occurs when he wishes to manually add one of these non-standard items, since these are not included within the dropdown list of common materials, if he tries to overtype one of these standard items, Excel will not permit this. I thought it might be possible to include an item entitled BLANK with a $0.00 Unit Price and 0.00 Hours Labour rate within the Price Book worksheet, however once again Excel generates an error message if I attempt to overtype this item with an alternative name and change the pricing and labour.

The dropdown list is not of course essential and I was wondering if anyone could offer an alternative approach to solving the issue of mixing standard items obtained from the Price Book sheet and non-standard items to be manually added within the Pricing Sheet worksheet.

Thanks

[JBeaucaire]

Protected workbooks? Also, what version of Excel are you using?

[Dufus]

Dear JBeaucaire,

Thanks for your response and please accept my apologies, I had completely forgotten that I had protected the amended workbook. I have re-attached an unprotected version.

I think I mentioned in the original post that I am using Excel 2003.

[JBeaucaire]

Highlight the Validated cells that you want to allow OTHER entries than the validation list.
Open the Validation List window and click on Error Alert.
Uncheck [ ] Show error alert

Also, you'll need some smarter formulas that won't error out all over the place when the non-matching data is entered.

I've replaced your VLOOKUPS with faster INDEX/MATCH formulas. If a user enters a custom value the yellow cells will return 0 instead of errors.

=IF(ISBLANK(B4),"",IF(ISNUMBER(MATCH(B4,'Price Book'!$A$2:$A$31,0)),INDEX('Price Book'!$B$2:$B$31,MATCH(B4,'Price Book'!$A$2:$A$31,0)),0))

[Dufus]

Dear JB,

Thanks so much for your kind and extremely prompt assistance in both solving this problem and also for taking the trouble to explain your method, that is really greatly appreciated.

Actually I think you must be a Rocket Scientist

Best wishes,

Dufus

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]