Help compiling a complex formula please!

[wadergirl]

If I could do this with VBA, I think I could figure itall out... but it's a shared work workbook and would really prefer to have itjust as a formula. Maybe I'm just over-thinking.

I’m sorry I can’tupload a file example, the best I can do is a table in this thread.

Here’s what I’m working with…

Sheet1:
Cells A2:A365contain a date in mm/dd/yyyy format.
Cells C2:C365contain formula =TEXT(A2,"mmddyy") which obviously updates for eachrow number
[TABLE="width: 329"]
<tbody>[TR]
[TD="width: 127, bgcolor: transparent"]

A​

[/TD]
[TD="width: 204, bgcolor: transparent"]

B​

[/TD]
[TD="width: 108, bgcolor: transparent"]

C​

[/TD]
[/TR]
[TR]
[TD="width: 127, bgcolor: transparent"]

Date​

[/TD]
[TD="width: 204, bgcolor: transparent"] Payments
[/TD]
[TD="width: 108, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 127, bgcolor: transparent"]

2/3/2019​

[/TD]
[TD="width: 204, bgcolor: transparent"][/TD]
[TD="width: 108, bgcolor: transparent"] 020319
[/TD]
[/TR]
[TR]
[TD="width: 127, bgcolor: transparent"]

2/4/2019​

[/TD]
[TD="width: 204, bgcolor: transparent"]
[/TD]
[TD="width: 108, bgcolor: transparent"] 020419
[/TD]
[/TR]
[TR]
[TD="width: 127, bgcolor: transparent"]

2/5/2019​

[/TD]
[TD="width: 204, bgcolor: transparent"]
[/TD]
[TD="width: 108, bgcolor: transparent"] 020519
[/TD]
[/TR]
[TR]
[TD="width: 127, bgcolor: transparent"]

2/6/2019​

[/TD]
[TD="width: 204, bgcolor: transparent"]
[/TD]
[TD="width: 108, bgcolor: transparent"] 020619
[/TD]
[/TR]
[TR]
[TD="width: 127, bgcolor: transparent"]

2/7/2019​

[/TD]
[TD="width: 204, bgcolor: transparent"]
[/TD]
[TD="width: 108, bgcolor: transparent"] 020719
[/TD]
[/TR]
[TR]
[TD="width: 127, bgcolor: transparent"]

2/8/2019​

[/TD]
[TD="width: 204, bgcolor: transparent"]
[/TD]
[TD="width: 108, bgcolor: transparent"] 020819
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:
Cells A2:A40contain a year in “yyyy” format (the formula bar shows 1/1/2019, but the cellonly displays the actual year)

Cells B2:B40contain the mm/dd/yy start of that year
Cells C2:C40contain the mm/dd/yy end of that year
Cells G1:AD7 isa table of the start/end dates of each month (below, only showing first 2 andlast month to save space, but all columns through AD have data/dates)

<tbody> [TD="width: 42, bgcolor: transparent"]

A​

[/TD]
[TD="width: 56, bgcolor: transparent"]

B​

[/TD]
[TD="width: 56, bgcolor: transparent"]

C​

[/TD]
[TD="width: 47, bgcolor: transparent"]

D​

[/TD]
[TD="width: 63, bgcolor: transparent"]

E​

[/TD]
[TD="width: 58, bgcolor: transparent"]

F​

[/TD]
[TD="width: 123, bgcolor: transparent, colspan: 2"]

G/H​

[/TD]
[TD="width: 123, bgcolor: transparent, colspan: 2"]

I/J​

[/TD]
[TD="width: 112, bgcolor: transparent, colspan: 2"]

AC/AD​

[/TD]
[TD="width: 42, bgcolor: transparent"]

Fiscal
Year​

[/TD]
[TD="width: 56, bgcolor: transparent"]

Start​

[/TD]
[TD="width: 56, bgcolor: transparent"]

End​

[/TD]
[TD="width: 47, bgcolor: transparent"]

52/53
Weeks​

[/TD]
[TD="width: 63, bgcolor: transparent"]

​

[/TD]
[TD="width: 58, bgcolor: transparent"]

​

[/TD]
[TD="width: 123, bgcolor: transparent, colspan: 2"]

Feb​

[/TD]
[TD="width: 123, bgcolor: transparent, colspan: 2"]

Mar​

[/TD]
[TD="width: 112, bgcolor: transparent, colspan: 2"]

Jan​

[/TD]
[TD="width: 42, bgcolor: transparent"]

2017​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/29/17​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/03/18​

[/TD]
[TD="width: 47, bgcolor: transparent"]

53​

[/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"]

01/29/17​

[/TD]
[TD="width: 62, bgcolor: transparent"]

02/25/17​

[/TD]
[TD="width: 62, bgcolor: transparent"]

02/26/17​

[/TD]
[TD="width: 62, bgcolor: transparent"]

04/01/17​

[/TD]
[TD="width: 56, bgcolor: transparent"]

12/31/17​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/03/18​

[/TD]
[TD="width: 42, bgcolor: transparent"]

2018​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/04/18​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/02/19​

[/TD]
[TD="width: 47, bgcolor: transparent"][/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"]

02/04/18​

[/TD]
[TD="width: 62, bgcolor: transparent"]

03/03/18​

[/TD]
[TD="width: 62, bgcolor: transparent"]

03/04/18​

[/TD]
[TD="width: 62, bgcolor: transparent"]

04/07/18​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/06/19​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/02/19​

[/TD]
[TD="width: 42, bgcolor: transparent"]

2019​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/03/19​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/01/20​

[/TD]
[TD="width: 47, bgcolor: transparent"][/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"]

02/03/19​

[/TD]
[TD="width: 62, bgcolor: transparent"]

03/02/19​

[/TD]
[TD="width: 62, bgcolor: transparent"]

03/03/19​

[/TD]
[TD="width: 62, bgcolor: transparent"]

04/06/19​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/05/20​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/01/20​

[/TD]
[TD="width: 42, bgcolor: transparent"]

2020​

[/TD]
[TD="width: 56, bgcolor: transparent"]

02/02/20​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/30/21​

[/TD]
[TD="width: 47, bgcolor: transparent"][/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"]

02/02/20​

[/TD]
[TD="width: 62, bgcolor: transparent"]

02/29/20​

[/TD]
[TD="width: 62, bgcolor: transparent"]

03/01/20​

[/TD]
[TD="width: 62, bgcolor: transparent"]

04/04/20​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/03/21​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/30/21​

[/TD]
[TD="width: 42, bgcolor: transparent"]

2021​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/31/21​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/29/22​

[/TD]
[TD="width: 47, bgcolor: transparent"][/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"]

01/31/21​

[/TD]
[TD="width: 62, bgcolor: transparent"]

02/27/21​

[/TD]
[TD="width: 62, bgcolor: transparent"]

02/28/21​

[/TD]
[TD="width: 62, bgcolor: transparent"]

04/03/21​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/02/22​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/29/22​

[/TD]
[TD="width: 42, bgcolor: transparent"]

2022​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/30/22​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/28/23​

[/TD]
[TD="width: 47, bgcolor: transparent"][/TD]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"]

01/30/22​

[/TD]
[TD="width: 62, bgcolor: transparent"]

02/26/22​

[/TD]
[TD="width: 62, bgcolor: transparent"]

02/27/22​

[/TD]
[TD="width: 62, bgcolor: transparent"]

04/02/22​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/01/23​

[/TD]
[TD="width: 56, bgcolor: transparent"]

01/28/23​

[/TD]
</tbody>

Here’s what I need:
Cell B2:B365 on Sheet1 need to link to a cell on a tab ina spreadsheet in a file path based on the date and fiscal month.
='\\shared\YYYY Payments\[MM YYYY Payments.xls]mmddyy'!$N$8

(i.e. cell B2 formula for 2/3/19 is ='\\shared\2019Payments\[02 2019 Payments.xls]020319'!$N$8 )

I’m thinking I need some kind of lookup on Sheet1 incolumn D to determine the month/year from Sheet2, and maybe a lookup in column E just forthe year? That way the actual formula incolumn B just has to “compile” the values in columns C-D into the formula?

Bonus if a certain sheet does not exist (either because there was just no file for that day or because it doesn't exist yet, i.e. 12/1/19 because it is in the future), the cell shows as blank instead of giving an error.