Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-25-2017, 11:02 AM
robot robot is offline Using VLOOKUP to autopopulate Unit Cost without changing old entries Windows 7 64bit Using VLOOKUP to autopopulate Unit Cost without changing old entries Office 2007
Novice
Using VLOOKUP to autopopulate Unit Cost without changing old entries
 
Join Date: May 2017
Posts: 1
robot is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 05-25-2017, 05:50 PM
Logit Logit is offline Using VLOOKUP to autopopulate Unit Cost without changing old entries Windows 10 Using VLOOKUP to autopopulate Unit Cost without changing old entries Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Can you post your project here for review?
Reply With Quote
  #3  
Old 06-06-2017, 04:57 PM
Coronos Coronos is offline Using VLOOKUP to autopopulate Unit Cost without changing old entries Windows 10 Using VLOOKUP to autopopulate Unit Cost without changing old entries Office 2010 64bit
Novice
 
Join Date: Oct 2016
Posts: 4
Coronos is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 06-07-2017, 05:52 AM
gebobs gebobs is offline Using VLOOKUP to autopopulate Unit Cost without changing old entries Windows 7 64bit Using VLOOKUP to autopopulate Unit Cost without changing old entries Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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),"")
Reply With Quote
  #5  
Old 06-07-2017, 07:00 AM
ArviLaanemets ArviLaanemets is offline Using VLOOKUP to autopopulate Unit Cost without changing old entries Windows 8 Using VLOOKUP to autopopulate Unit Cost without changing old entries Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using VLOOKUP to autopopulate Unit Cost without changing old entries alphabetize a list (manual index) with entries and sub-entries shmu Word 5 08-01-2016 11:15 PM
Using VLOOKUP to autopopulate Unit Cost without changing old entries [Project 2013] More accuracy with resource, unit %, and cost? cag8f Project 18 01-02-2016 01:51 PM
Using VLOOKUP to autopopulate Unit Cost without changing old entries 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft