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.
Im sorry I cantupload a file example, the best I can do is a table in this thread.
Heres what Im 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
<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)
<tbody>
</tbody>
Heres 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 )
Im 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.
Im sorry I cantupload a file example, the best I can do is a table in this thread.
Heres what Im 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 |
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 |
Heres 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 )
Im 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.