Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
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: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
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



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 04:40 AM.


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