Hi,
New member to this awesome forum.

I have a spreadsheet that tracks what supplies (Item) are delivered to different buildings on specific dates.

Sheet1 = VistaBuilding
Column A = Date
Column B = Address
Column C = City
Column D = Item
Column E = Quantity
Column F = Unit Price
Column G = Price Total

Sheet2 = CostLookUpTable
Column A = Item
Column B = UnitCost



In Column F:
=IF(D2="","",VLOOKUP(D2,CostLookupTable!$A$2:$B$51 ,2,FALSE))

I want to enter the Item name in column D and use VLOOKUP in Column F to auto-populate the Unit Price. However, the Unit Price can change periodically.

My question is, how can I have new entries grab the new Unit Price but prevent older entries from changing the Unit Price? Is this possible with a formula by looking at the entry date? Or would I need to use VBA? If VBA, can you give me an example?

I will have multiple tabs with different building names to enter the delivered supplies.

My VLOOKUP works to auto-populate, but the values change for previous entries.

Thanks to all in advance for the help!

Can you post your project here for review?

I could be wrong, but I don't think you can do this without VBA.

There's probably a better way, but this is what I ended up with for entering line items in my invoicing workbook:

Range("K" & (ActiveCell.Row)).Select
ActiveCell = "=VLOOKUP(D" & ActiveCell.Row & ",'Service Items'!A: D,4,False)"
ActiveCell.Value = ActiveCell.Value

Column K is the item price on my line items sheet. 'Service Items'!A: D is my lookup range, where D holds the unit price. After looking it up, "ActiveCell.Value = ActiveCell.Value" takes the resulting value of the lookup and pastes it in place of the lookup formula.

Or you could add a date to the CostLookupTable. Of course that would make the table much larger since it would then basically be a history of costs for each item, not just the current. Still, that could be useful information too to retain.

Also, I would change the lookup statement to use an IFERROR and a table name to be a bit more robust. For example, if you name your table CostLU, you do not need to enter the range of cells.

=IFERROR(VLOOKUP(D2,CostLU,2,FALSE),"")

Having price history table on separate sheet is probably the best solution.

Price History sheet: columns ItemID, ItemPrice, DateFrom, DateTo, PriceNo
i.e given price is valid starting from DateFrom until another entry for same item with DateFrom later as current one. Column PriceNo enumerates prices for same item - use COUNTIFS() to count records with same ItemID and with DateFrm less than current one. Column DateTo uses SUMIFS() formula to return next DateFro for same item, or TODAY() when there is not any later entry. And you have to be sure there are no rows with same IdemID and DateFrom (you can use conditional formatting for this - format the font red bold for double rows).

To get right price, use SUMIFS() function (=SUMIFS(tPriceHistory[ItemPrice],tPriceHistory[IdemID],[@ItemID],tPriceHistory[DateFrom],">=" & [@MyDate],tPriceHistory[DateTo],"<" & [@MyDate])
(I used table formula syntax here - it is easier to write formulas and to get the meaning of them. @ indicates, that the value is read from same row of table, the formula belongs to)