#1
|
|||
|
|||
Using VLOOKUP to autopopulate Unit Cost without changing old entries
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! |
#2
|
|||
|
|||
Can you post your project here for review?
|
#3
|
|||
|
|||
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. Last edited by Coronos; 06-06-2017 at 05:00 PM. Reason: A: D (without the space) was interpreted as a big grin |
#4
|
|||
|
|||
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),"") |
#5
|
|||
|
|||
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) |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
alphabetize a list (manual index) with entries and sub-entries | shmu | Word | 5 | 08-01-2016 11:15 PM |
[Project 2013] More accuracy with resource, unit %, and cost? | cag8f | Project | 18 | 01-02-2016 01:51 PM |
Cost resource not taken in real cost | pascalbidouille | Project | 1 | 08-21-2015 01:25 PM |
Using Fill In Fields to Autopopulate Multiple Areas in the Document | rogelinepaula | Word | 1 | 08-13-2015 11:15 PM |
Autopopulate | schiesa | Word | 1 | 11-06-2012 02:31 AM |