[darwin99]

I have a complex database file that the users require to be in excel. I have created a master part list and the attached sheets in the workbook are for loading Bill of Materials (BOMs) into. The attached BOM sheets have formulas which reference the Master Part List via VLOOKUP formulas and there are also complex macros. I would appreciate help with some or all of the following:

1)find the lowest price on the parts master
2)create an order of operations with which to populate one column on the BOM sheet (for example: first search for most recent price, if not then lowest price, if not then a particular vendor, etc...
3)create buttons to execute 2)

I know I am asking for a lot and if anyone can help with a small fragment of the above, I would be so eternally grateful!!!

 

[Jimmy the Hand]

Hi Darwin, welcome to TSG.

I'm sorry to say but I think this is insufficient information. More details are needed. It's difficult to say anything without understanding the project you work on. For example, what is "most recent price"? I came up with the following possible definitions:

1) You keep records of prices for each item in the Master Part List, and want the most recent price of a particular item.
2) You want the price that was most recently entered into the database.
3) You want the price of a particular part that the most recent puchase was made at.
4) You want the full price that the most recent puchase was made at.

You see? Any of the above, and possibly more, could be the one you want, but they need different approach, each. The best I can do, without trying to cover more possibilities than I see point in at the moment, is this:

The lowest price on the master sheet can be found by using the MIN function.
Search for most recent price is done with the MAX function, using dates related to prices.
Search for a particular vendor, item, price, etc. is done with the MATCH function.
All 3 functions can be used in VBA macro with the following syntax:

Result = Application.WorksheetFunction.FunctionName(parameter list)

e.g.

Result = Application.WorksheetFunction.Match(what, where, how)

Creating a button in advance is a piece of cake. Creating it runtime is a bit more difficult. Creating it runtime and assigning a macro to it, also in runtime, so that it would work, well, I haven't been able to do that so far.

Jimmy