Need help: formula calculating values depending on value in drop down list.

Report
Posts
1
Registration date
Thursday April 29, 2021
Status
Member
Last seen
April 29, 2021
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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.

1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Urr100,

Not sure if I understand you correctly, but:
=WEEKNUM(TODAY(),2)
will get you today's weeknumber.
When you use that with the starting weeknumber of the project, you will get the duration.

If this isn't what you are after, then could you let us know which values you enter manually and which values you want to be calculated.

Best regards,
Trowa