Formula to return offset value based on cell location of sech for text

[shasty]

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.

	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)))),""))))