I created a planning tool for my work. It's basically showing situation on a current week, put I need more from it for future planning and also, time is a value, so I can't waste my time if there is a way to make it faster.
My table is divided into 30 sections, showing objects, men, team leaders. I use drop down lists to change workers and objects and so on.
Very important information for planning is what is the duration of works in these objects (in weeks). So for that I have a cell beneath each section. I'm changing the duration every new week manually so far (subtract 1 from the duration) .On upper left corner of my table I have a drop-down list with week numbers.
So, what I want to have is: when I change week numbers in this drop-down list, the formula should subtract one or 2 or whatever number accordingly.
It gets trickier. Sometimes client calls when there's just 1 week left till the end and wants our workers for 4 more weeks. So I should be able to enter new term without messing with formulas. For that I created a cell next to the duration cell (for entering values manually). But I just couldn't find a way to work out the solution. Can somebody help? I added pictures to explain my situation better.
Need help: formula calculating values depending on value in drop down list.