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

Closed
urr100 Posts 1 Registration date Thursday April 29, 2021 Status Member Last seen April 29, 2021 - Apr 29, 2021 at 10:32 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - May 3, 2021 at 12:13 PM
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.
Related:

1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
May 3, 2021 at 12:13 PM
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
0