Quote:
Originally Posted by makaveli80
Is there a way for me to change the drop down to be something like "Week of Mar 5" instead of "201810"?
|
Into table tWeeks you add second column p.e. WeekTxt;
Into this column you enter either week text, or the formula to calculate it. As follows from week start and end dates you used, it looks like you are using ISO week system. As much as I know, there is no official rule for deciding, into which month a week belongs, but I myself have extended the rule the week belonging to year, to where it's Thursday belongs. So the week belongs into month where it's Thursday belongs.
NB! This workbook may serve you for several years, so it is essential in all time references keep the year too - i.e. something like "Week 5 of Mar 2018" for week text - otherwise formulas don't work;
You change the formula for Name
Code:
lWeeks = tWeeks[WeekTxt]
;
In Name Manager, you create a new name RepWeekTxt, which refers to same cell as RepWeekNo;
You edit the Name RepWeekNo:
Code:
RepWeekNo = INDEX(tWeeks[WeekNo];MATCH(RepWeekTxt;tWeeks[WeekTxt];0))
(When you use comma as function parameter delimiter, correct the formula!).
I haven't tested it, but it must be enough. When not, then you at least got the idea.
Quote:
Originally Posted by makaveli80
In the meantime, I think what I will do is try to combine the various spreadsheets into 2 (combining Streams, Employees, Weeks, and Calendary) to conserve space.
|
I myself prefer to keep every table on separate sheet. You can always hide sheets, the user usually don't need - like Calendary and Weeks, which are filled at design time and maybe corrected after some years. And also you can hide sheets you are using only yourself, and not very often (p.e. Employees).
The speed of workbook is determined mostly by size of TimeTable. I think you have to check the speed, when TimeTable exceds 20000 - 30000 rows, but as the workbook don't have too much cells with formulas, then the limit for archiving older entries may be even higher.