Good morning,
I have a calendar in Excel where each month is on a new tab and am trying to populate text based on multiple conditions:
The location of the value that equals 7 days before the date in the referenced cell (EX D18- 7 days = D9)
Then using that location display the value that is offset from that location -7 rows.
**Basic idea = Look specific text in the cell 7 rows above the cell that contains a date 7 days before displayed date.
Part of the problem is that a few days of each month are on that spreadsheet, so if I am trying to reference a cell 7 days before 8/1 I need to look at the previous worksheet.
<colgroup><col><col><col span="2"><col span="3"><col></colgroup><tbody>
</tbody>
This is the current formula I am using but it is specific cells (there are other conditionals) and I would like to not have to adjust every year as the dates move.
=IF(OR(WEEKDAY(D18)=1,WEEKDAY(D18)=7),"",IF(AND(WEEKDAY(D18)=2,ISNUMBER(SEARCH("Load",JUL!I38))),CONCATENATE("Calculate ",(MID(JUL!I38,FIND("Load",JUL!I38)+5,LEN(JUL!I38)))),IF(AND(WEEKDAY(D18)=2,ISNUMBER(SEARCH("Load",C2))),CONCATENATE("Calculate ",(MID(C2,FIND("Load",C2)+5,LEN(C2)))),IF(ISNUMBER(SEARCH("Load",D2)),CONCATENATE("Calculate ",(MID(D2,FIND("Load",D2)+5,LEN(D2)))),""))))
I have a calendar in Excel where each month is on a new tab and am trying to populate text based on multiple conditions:
The location of the value that equals 7 days before the date in the referenced cell (EX D18- 7 days = D9)
Then using that location display the value that is offset from that location -7 rows.
**Basic idea = Look specific text in the cell 7 rows above the cell that contains a date 7 days before displayed date.
Part of the problem is that a few days of each month are on that spreadsheet, so if I am trying to reference a cell 7 days before 8/1 I need to look at the previous worksheet.
C | D | E | F | G | H | I | |
1 | Sun | Mon | Tue | Wed | Thur | Fri | Sat |
2 | Load 2 | ||||||
3 | |||||||
4 | |||||||
5 | |||||||
6 | |||||||
7 | |||||||
8 | |||||||
9 | 07/30 | 07/31 | 08/01 | 08/02 | 08/03 | 08/04 | 08/05 |
10 | Load 3 | ||||||
11 | |||||||
12 | |||||||
13 | |||||||
14 | |||||||
15 | |||||||
16 | |||||||
17 | |||||||
18 | 08/06 | 08/07 | 08/08 | 08/09 | 08/10 | 08/11 | 08/12 |
19 | Load 4 | ||||||
20 | |||||||
21 | |||||||
22 | |||||||
23 | |||||||
24 | |||||||
25 | |||||||
26 | |||||||
27 | 08/13 | 08/14 | 08/15 | 08/16 | 08/17 | 08/18 | 08/19 |
<colgroup><col><col><col span="2"><col span="3"><col></colgroup><tbody>
</tbody>
This is the current formula I am using but it is specific cells (there are other conditionals) and I would like to not have to adjust every year as the dates move.
=IF(OR(WEEKDAY(D18)=1,WEEKDAY(D18)=7),"",IF(AND(WEEKDAY(D18)=2,ISNUMBER(SEARCH("Load",JUL!I38))),CONCATENATE("Calculate ",(MID(JUL!I38,FIND("Load",JUL!I38)+5,LEN(JUL!I38)))),IF(AND(WEEKDAY(D18)=2,ISNUMBER(SEARCH("Load",C2))),CONCATENATE("Calculate ",(MID(C2,FIND("Load",C2)+5,LEN(C2)))),IF(ISNUMBER(SEARCH("Load",D2)),CONCATENATE("Calculate ",(MID(D2,FIND("Load",D2)+5,LEN(D2)))),""))))