I’m at a loss as to what to call the subject of this post… I have a large financial model that is generic (which is turning out to be much harder to do than a specific one). It’s generic in that the user can enter 1-7 service names each of which has 1-5 sub-services (and there are other ‘n’ items to make it more 
). Each of these services can start in any year and take from 1-5 years to complete. My problem is how can I create some formulas that know what year they are to start in, and be 0 in other years.
For example: Service1 starts in 2006 and duration = 3 years, Service2 starts in 2007 and duration = 2 years. I have various capex, opex, arpu, etc. data that needs to be valid (non-zero) only during the years the service is “online” (2006-2009 for Service1, 2007-2009 for Service2 in my example).
I’ve attached a simplistic workbook with just one example. The start year and duration can change (blue cells) but I can’t figure out formulas that track against these years w/o losing data. I manually entered the data I want but if you change the years and/or duration, they don’t work. It seems I can’t just create a simple IF formula and drag it across since I’ll lose reference to previous cells. I’m sure this doesn’t make sense but I can’t explain it very well. If I have a formula like:
=IF(and(yr>=2006,yr<=2008),"fetch data in some cell",0)
when I drag this type of formula to the right to fill the years (7 total), the cell address in the TRUE part changes of course but, I need it to stay still (as if it was an asbolute formula like $C$4) until the condition is TRUE at which point it should change to a relative formula like C$4. Clear as mud, eh? 
I do not know in advance the year the user will start with so I need formulas that are smart enough to ‘track’ with the year chosen. I hope this doesn’t need VBA as I am too close to a deadline to add code (and this is a very large model).
Hopefully the attached workbook will do a better job of explaining my problem.
Deb 
but I think I can adapt it for the other 1000+ similar data rows I have across 15 sheets. I definitely wouldn’t of figured that one out by myself, no way. You understood my problem very well 
Deb
All too true! Where were you in Nov when I got “volunteered” to do this? So let’s have a tool that has ‘n’ legacy services, and ‘n’ new services (each with ‘n’ sub-services) where each service can start/end in any year (and all the corresponding pricing, fees, equipment, etc. have to begin in the right year). Now make one income statement that can show all these unknowns, and and and ….. 
