Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup function or table from dates that the overhead changed
I need to add an overhead rate (OHR) column to my purchases table.
The purchases table contains: item cost date The OHR will come from the OH table, which lists the new OHR every time it is changed The OH table looks like this 1 Jan 09 - 50% 7 Feb 09 - 51% 2 Mar 09 - 53% etc. So for example, for a purchase made on 10 Feb 09, the OHR would have been 51% since it changed to this value 3-days earlier on 7 Feb 09. Everyday I pull the data for these two tables from our Data Warehouse, and use macros to format final results - so I need a macro to generate either a lookup function or table to give me the OHR for any date based on the data in the OH table? Note: My purchase data spans ~15-years, and the OHR changes about 3-times per year. So a lookup table would have something like 15*365=5,474 rows. A lookup function, maybe a if-then construct, would have around 15*3=45 lines of code. -- Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup function or table from dates that the overhead changed
I find when writting VBA code and I need to perform a lookup that is not
exact I usually write a formula to the worksheet or use the evealuate function 1) formula method 'get last row of OHR table with sheets("OHR") LastOHR = Range("A" & rows.count).end(xlup).row end with MyDate = datevalue("10 Feb 09") 'get last row of purchase table with sheets("Purchase Table") Lastrow = .Range("A" & rows.count).end(xlup).row .Range("D2").formula = " "=Vlookup(OHR!$A1:$B" & LastOSH & "," & MyDate & ",2)" 'copy the formula down th eworksheet .Range("D2").copy _ Destination:=.Range("D2:D" & LastRow) end with If you don't want to use a formula then use evaluate ReturnValue = evaluate("Vlookup(OHR!$A1:$B" & LastOSH & "," & MyDate & ",2)") "Richard" wrote: I need to add an overhead rate (OHR) column to my purchases table. The purchases table contains: item cost date The OHR will come from the OH table, which lists the new OHR every time it is changed The OH table looks like this 1 Jan 09 - 50% 7 Feb 09 - 51% 2 Mar 09 - 53% etc. So for example, for a purchase made on 10 Feb 09, the OHR would have been 51% since it changed to this value 3-days earlier on 7 Feb 09. Everyday I pull the data for these two tables from our Data Warehouse, and use macros to format final results - so I need a macro to generate either a lookup function or table to give me the OHR for any date based on the data in the OH table? Note: My purchase data spans ~15-years, and the OHR changes about 3-times per year. So a lookup table would have something like 15*365=5,474 rows. A lookup function, maybe a if-then construct, would have around 15*3=45 lines of code. -- Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace Function that uses lookup table? | Excel Worksheet Functions | |||
LOOKUP function between two dates in Excel 2007 | Excel Discussion (Misc queries) | |||
Lookup dates, fiscal period table | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Between dates table lookup. | Excel Programming |