Adding an option to an existing formula

[Jeff07]

=IF(B5=0,0,VLOOKUP(B5, INDIRECT("P"&CEILING(MONTH(G3)/3,1)&"!A2:Z10000"),5,0))

I use the above formula in a sales receipt form. The formula looks at the date in cell G3 and then looks up the price of an item from 4 other worksheets depending on what the date is. Each of the lookup worksheets are labled P1,P2, P3, P4 and each worksheet represents a three month quarter of the year so P1 will contain prices that are current from January 1st to March 31st or the first quarter of the present year, P2 has pricing for April 1st - June 30th the second quarter of the year and so on. Manufacturers will change prices for products during the coarse of the year due to price increases in raw material pricing. This is usually done annually but sometimes they are forced to change prices in an off month. Right now for example there has been a price increase scheduled to take effect March 1st (30 days before the end of the first quarter) which will fall under P1 How would you suggest adding a new price worksheet and changing the formula above to access the changed prices in the new worksheet for the month of March only? Thank you for your expert advice.

 

[Gerald Higgins]

Hi.

If I were you, I would give serious consideration to doing away with separate sheets for each quarter / month / whatever, and just have a single sheet for ALL your prices, together with the date that each price became live.

Then it would be relatively straightforward to lookup the most recent price for any item, and you could change prices as often as you liked - even several times a day if you logged the time !

 

[Jeff07]

Hi Gerald, Of course your suggestion makes 100% sense, problem is I am not sure how to write a formula that will accomplish this. Any suggestions?

 

[Jeff07]

For example I have a sales receipt form that has in cell G3 the date of the receipt. In cell H5 of the same sheet I need the price of the item that occurs in cell A5.

I have a separate worksheet labled P1 which contains the prices of all items with the following column headers

Model# , Cost, Price, Effective Price Date
Blue Widget 24.00 36.00 1/15/11
Blue Widget 26.00 40.00 3/1/11


Lets say todays date is 3/5/11 How do I get correct price to show up in cell H5 of the sales receipt sheet.
In other words I need the formula to read the model # in A5 and also read the date in cell G3 and select the correct price from the price sheet, in this case
lets say A5 = Blue Widget and G3 = 3/5/11 so I want the result in H5 to be $40.00. How would this formula be written?

 

[Gerald Higgins]

There are several ways to do this, here's one.

Use excel's Database functions, in this case DMAX.

Let's say your price list is in the range A2:D100, with headers on row 2.
You'll need to set up a criteria range somewhere, which duplicates the headers.
Let's say you do this in the range F2:I3.

In F3, put "Blue Widget".
You can do this with a formula linked to the invoice if you want.

In I3, put this formula

="<"&Sheet1!G3

This basically says "less than the date you specified in cell G3. It will probably appear as a number, not a date, but don't worry about that.

Then, use this formula, to show the most recent date for that product

=DMAX(A2:D5,"Effective Price Date",I2:I3)

Let's say you put this formula in cell I5.

Then, on your invoice sheet, put this formula where the price should be, in H5.

=SUMPRODUCT(--('P1'!A3:A17="Blue Widget"),--('P1'!D3:D17='P1'!I5),'P1'!C3:C17)

 

[Jeff07]

I must be dense, I tried doing what you said but nothing shows up in the H3 cell. Thanks for trying anyway.
Best Regards,
Jeff McDonald

 

[Gerald Higgins]

Did you get something in I5 ?