MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 348
- Office Version
- 2013
- Platform
- Windows
Cell AA4’s formula is =IFERROR(LOOKUP(2,1/(B4:Y4<>""),B4:Y4),"xx")
Cells B2:Y2 indicate a specific month an action was performed per the result in AA4.
When the value in AA4 comes from October then the value in AD4 should read October.
When the value in AA4 comes from November then the value in AD4 should read November, this I have not been able to figure out how to do.
So basically any value in column AM needs to get the correct month for the date value in column AP based upon which month column the Action was last done.
1st, 2nd, 3rd, 4th, 5th, & 6th indicate which week of the month the indicated date occurs within. This is done because in some months no action occurs in any given week of the month as indicated by the Xl2bb Mini Sheet. And in some months any given action may occur in more than one week of the month thus the need for a separate column for each week of the month.
As you can see in the Xl2bb Mini Sheet some values in the November range, 2nd week, do not give correct results in Column AD.
I have explored multiple methods and just can’t seem to find a good method.
Perhaps I don’t have enough if statements.
Any help will be much appreciated.
Following this description you will find two Mini Sheets. The second Mini Sheet is simply the calendar.
The second Mini Sheet must appear in a follow-up to this post as I received the message "Generated content exceeds the limit..."
Cells B2:Y2 indicate a specific month an action was performed per the result in AA4.
When the value in AA4 comes from October then the value in AD4 should read October.
When the value in AA4 comes from November then the value in AD4 should read November, this I have not been able to figure out how to do.
So basically any value in column AM needs to get the correct month for the date value in column AP based upon which month column the Action was last done.
1st, 2nd, 3rd, 4th, 5th, & 6th indicate which week of the month the indicated date occurs within. This is done because in some months no action occurs in any given week of the month as indicated by the Xl2bb Mini Sheet. And in some months any given action may occur in more than one week of the month thus the need for a separate column for each week of the month.
As you can see in the Xl2bb Mini Sheet some values in the November range, 2nd week, do not give correct results in Column AD.
I have explored multiple methods and just can’t seem to find a good method.
Perhaps I don’t have enough if statements.
Any help will be much appreciated.
Following this description you will find two Mini Sheets. The second Mini Sheet is simply the calendar.
| BogusSchedule2024_Chngs.xlsx | |||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | |||
| 2 | September | October | November | December | November 06, 2024 | 11 | October | 6 | October | ||||||||||||||||||||||||||
| 3 | 1st | 2nd | 3rd | 4th | 5th | 6th | 1st | 2nd | 3rd | 4th | 5th | 6th | 1st | 2nd | 3rd | 4th | 5th | 6th | 1st | 2nd | 3rd | 4th | 5th | 6th | 2024 | November | 11/6/2024 | ||||||||
| 4 | Action 01 | 7 | 28 | 28 | October 28, 2024 | October 28, 2024 | |||||||||||||||||||||||||||||
| 5 | Action 02 | 16 | 27 | 9 | 18 | 28 | 6 | 6 | November 28, 2024 | November 28, 2024 | |||||||||||||||||||||||||
| 6 | Action 03 | 16 | 27 | 9 | 18 | 28 | 7 | 7 | October 28, 2024 | ||||||||||||||||||||||||||
| 7 | Action 04 | 16 | 27 | 9 | 18 | 28 | 6 | 6 | November 28, 2024 | ||||||||||||||||||||||||||
| 8 | Action 05 | 6 | 24 | 8 | 8 | October 28, 2024 | 24 | 6 | $K$8 | ||||||||||||||||||||||||||
| 9 | Action 06 | 16 | 8 | 18 | 28 | 5 | 5 | October 28, 2024 | |||||||||||||||||||||||||||
| 10 | Action 07 | 18 | 18 | October 28, 2024 | |||||||||||||||||||||||||||||||
| 11 | Action 08 | 16 | 9 | 9 | October 28, 2024 | ||||||||||||||||||||||||||||||
| 12 | Action 09 | 8 | 18 | 28 | 5 | 5 | October 28, 2024 | ||||||||||||||||||||||||||||
| 13 | Action 10 | 8 | 18 | 28 | 5 | 5 | October 28, 2024 | ||||||||||||||||||||||||||||
| 14 | Action 11 | xx | |||||||||||||||||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| AA2 | AA2 | =TEXT($AD$3&" "&AA5&", "&$AA$3,"mmmm dd, yyyy") |
| AB2 | AB2 | =MONTH(AE3) |
| AG2 | AG2 | =TEXT(EOMONTH(TODAY(),-1),"mmmm") |
| AE2 | AE2 | =DAY(AE3) |
| AE3 | AE3 | =TODAY() |
| AG4 | AG4 | =TEXT($AD$2&" "&$AA4&", "&$AA$3,"mmmm dd, yyyy") |
| AG5 | AG5 | =TEXT($AD$3&" "&$AA4&", "&$AA$3,"mmmm dd, yyyy") |
| AE8 | AE8 | =MAX($B$8:$Y$8) |
| AF8 | AF8 | =OFFSET($K$8,0,-2) |
| AG8 | AG8 | =CELL("address",INDEX($B$8:$Y$8,MATCH(MAX($B$8:$Y$8),$B$8:$Y$8,0))) |
| AA4:AA14 | AA4 | =IFERROR(LOOKUP(2,1/(B4:Y4<>""),B4:Y4),"xx") |
| AD2 | AD2 | =TEXT(TODAY()-DAY(TODAY()),"mmmm") |
| AD3 | AD3 | =TEXT($AB$2*29,"mmmm") |
| AD4:AD14 | AD4 | =IF($AA4="xx","",IF($AA4<>$AE$2,$AG$4,IF($AA4=$AE$2,$AG$5))) |
The second Mini Sheet must appear in a follow-up to this post as I received the message "Generated content exceeds the limit..."
