Help compiling a complex formula please!

wadergirl

New Member
Joined
Jun 3, 2016
Messages
49
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
A
B
C
Date
Payments
2/3/2019
020319
2/4/2019

020419
2/5/2019

020519
2/6/2019

020619
2/7/2019

020719
2/8/2019

020819
<tbody> </tbody>

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)

A
B
C
D
E
F
G/H
I/J
AC/AD
Fiscal
Year
Start
End
52/53
Weeks
Feb
Mar
Jan
2017
01/29/17
02/03/18
53
01/29/17
02/25/17
02/26/17
04/01/17
12/31/17
02/03/18
2018
02/04/18
02/02/19
02/04/18
03/03/18
03/04/18
04/07/18
01/06/19
02/02/19
2019
02/03/19
02/01/20
02/03/19
03/02/19
03/03/19
04/06/19
01/05/20
02/01/20
2020
02/02/20
01/30/21
02/02/20
02/29/20
03/01/20
04/04/20
01/03/21
01/30/21
2021
01/31/21
01/29/22
01/31/21
02/27/21
02/28/21
04/03/21
01/02/22
01/29/22
2022
01/30/22
01/28/23
01/30/22
02/26/22
02/27/22
04/02/22
01/01/23
01/28/23
<tbody> </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.

 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top