Automating a Price Book

Need your help & ideas!!.

I?m trying to automate somewhat our pricing system. It presently consists of a book with about 65 pages of prices (Retail & Wholesale). I can combine those 65 pages down to about 12 pages, which includes both the Retail & Wholesale price.

In the past when we priced a home, each homes different line items was retyped onto a Schedule A for the customer to look at, review and then sign. Many errors where made and it was labor intensive. I have currently combined most of the common items into about 12 spread sheets, the total of the 11 are linked to a summary Schedule A. The problem is that every pricing request has 12 pages and when we give it to the customer we are basically passing out our price book, the competition I?m sure will be pleased when they eventually get their hands on one.

We thought about only handing out the summary page but then the customer doesn?t have the detail describing their home and they are paying for (what?s in & what?s could be problem down the road). I can just hear the complaining ---?I thought that was included?..You cheated me!?

What I?m trying to do is select only those items in the 11 spreadsheets that have something other 0 (zero) in a specific column (column B) and move it to the Schedule A for both Retail & Wholesale. I?ve attached a small example for you to see.

In column A would be the internal item #, in column B would be the number of items selected (standard interior doors for example), column C would contain the info on how each is priced, (ea. sf. lf. Pr. Un. ls, etc.), column D would be the description of the item, column M is the retail price of each unit, column N (formula) is the retail extension of column b * column M. Column O is the wholesale price and column P (formula) is the wholesale extension of column b * column 0. Setting this up so far is the easy part!!

Now for the hard part, I want to select only those items that have a value greater then or less then 0 in column B. I want to move columns A, B, C, D, M & N into a orderly list on Schedule A spreadsheet that is totaled, it will detail the retail items that the customer will be getting in their new home & will be the handout the customer takes home with them.

At the same time I want to select those items that have a value greater then or less then 0 in column B. I want to move columns A, B, C, D, O & P into a list on Schedule A that is totaled, it will detail the items that will form the Wholesale pricing of the customers new home.

Both of these lists (both Schedule A?s) will be variable in length, some customers could have 15 items and some could have 50+ items!

I?m not sure how to tackle it, with VLOOKUP & HLOOKUP, Macro?s, some kind of Report Manager or plug-ins, other items have crossed my mind, but nothing jumps out as the solution.

I guess the 1st and most basic question is -- is Excel capable of handling such a problem or should I be trying Access?

Thanks for your thoughts and any help you can cast on this problem.

If anyone has any idea's email me and I'll send a sample collection of spread sheets with some real live data for you to work with.

Neil