Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Am I trying to do something that Excel cannot handle?
Here is the challenge:
1) On row 3 there are headings: 1st qrt, 2nd qrt, 3rd qrt, 4th qrt, 1st qrt, 2nd qrt., etc. representing subdivisions of calendar years. 2) On row 4, under each quarter heading, there is a sales forecast. The basic assumption is that what is forecast for the quarter must be on hand at the beginning of that quarter. 3) In cell A2: the user enters a leadtime in weeks for obtaining the product. 4) Based on the leadtime entered in cell A2, I want to auto-populate row 5, showing when the product should be ordered. So, if I need 500k pounds at the beginning of qrt 4 and the leadtime is is 16 weeks (1.33 quarters), 500K pounds are entered under qrt 2. 5) whatever macro or VBA could make this happen, would need to handle 8 data sets (got the 8 different products in the forecast) each with its own leadtime Am I wasting my time? |
#2
|
|||
|
|||
One way:
You don't say how your data is organized, so I don't know how to deal with the other seven products (where is their lead time entered?), but you could do something like (assuming the 1st "1st qrt" heading is in column B): B5: =INDEX(C4:$IV4,CEILING($A$2/13,1)) copy to the right as far as required. In article , "KG" wrote: Here is the challenge: 1) On row 3 there are headings: 1st qrt, 2nd qrt, 3rd qrt, 4th qrt, 1st qrt, 2nd qrt., etc. representing subdivisions of calendar years. 2) On row 4, under each quarter heading, there is a sales forecast. The basic assumption is that what is forecast for the quarter must be on hand at the beginning of that quarter. 3) In cell A2: the user enters a leadtime in weeks for obtaining the product. 4) Based on the leadtime entered in cell A2, I want to auto-populate row 5, showing when the product should be ordered. So, if I need 500k pounds at the beginning of qrt 4 and the leadtime is is 16 weeks (1.33 quarters), 500K pounds are entered under qrt 2. 5) whatever macro or VBA could make this happen, would need to handle 8 data sets (got the 8 different products in the forecast) each with its own leadtime Am I wasting my time? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I isolate my Excel server (automation) from other Excel instances? | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |