Hello all,
The level of knowledge on this site never ceases to amaze me, but I have a feeling this is a stumper. I feel it may just be too much for Excel to handle.
I am trying to create a formula for a calendar. I have 10 departments labeled 1 through 10. Each department must do a random audit at least twice a month,
Sunday to Saturday. I want it set up so that the same department is not audited 2 days in a row, and only once per week. I am using the Excel template,
"Any year calendar(single month per tab)".
I created a sheet called Departments. In cell A2 I entered DEPT1, in A3 I entered DEPT2 etc, and worked my way down to A32 to represent each day of a month.
I created in Column B, a heading "JAN 2020" and entered the numbers 1 to 31 to represent each day of a month, then in column C I entered a RAND function to randomly sort the numbers in Column B.
I continued this across the sheet until each month was represented.
Having trouble developing a formula that will:
A) sort each month randomly
B) have a department audited only once per week
C) have a department audited at least 2 times per month
I hope I have explained this well enough, but I fear I haven't. Thanks
The level of knowledge on this site never ceases to amaze me, but I have a feeling this is a stumper. I feel it may just be too much for Excel to handle.
I am trying to create a formula for a calendar. I have 10 departments labeled 1 through 10. Each department must do a random audit at least twice a month,
Sunday to Saturday. I want it set up so that the same department is not audited 2 days in a row, and only once per week. I am using the Excel template,
"Any year calendar(single month per tab)".
I created a sheet called Departments. In cell A2 I entered DEPT1, in A3 I entered DEPT2 etc, and worked my way down to A32 to represent each day of a month.
I created in Column B, a heading "JAN 2020" and entered the numbers 1 to 31 to represent each day of a month, then in column C I entered a RAND function to randomly sort the numbers in Column B.
I continued this across the sheet until each month was represented.
Having trouble developing a formula that will:
A) sort each month randomly
B) have a department audited only once per week
C) have a department audited at least 2 times per month
I hope I have explained this well enough, but I fear I haven't. Thanks