Formula reference changes depending on filename written on a cell

[MiKos23]

Good day,

I created a timekeeping and payroll in excel for our company (company cannot afford to purchase a PR software). So there are 2 files, 1 pure timekeeping (biometric records converted to number of hours) and payroll computation. The payroll computation will get the data from the timekeeping file. The problem arises when I copy and paste it for a new record (ie copy the PR file for Jan 15 and make it a PR file for Jan 30 pay period). Most of the cells i have to manually edit the formula to point in the correct timekeeping file..

Here is the formula i stated:

=IF(AN8=0,0,INDEX('D:\Payroll\2017 PAYROLL\Payroll 12.30.17\[TIMEKEEPING FOR 12.30.17.xlsx]TKS'!$E$5:$AJ$650,MATCH($B8,'D:\Payroll\2017 PAYROLL\Payroll 12.30.17\[TIMEKEEPING FOR 12.30.17.xlsx]TKS'!$C$5:$C$650,0),MATCH(AT$6,'D:\Payroll\2017 PAYROLL\Payroll 12.30.17\[TIMEKEEPING FOR 12.30.17.xlsx]TKS'!$E$3:$AJ$3,0)))

Let us say that the last pay period was 12/30/17 and that is also the file name. for the new pay period 1/15/18, the formula would be change to.

=IF(AN8=0,0,INDEX('D:\Payroll\2018 PAYROLL\Payroll 1.15.18\[TIMEKEEPING FOR 1.15.18.xlsx]TKS'!$E$5:$AJ$650,MATCH($B8,'D:\Payroll\2018 PAYROLL\Payroll 1.15.18\[TIMEKEEPING FOR 1.15.18.xlsx]TKS'!$C$5:$C$650,0),MATCH(AT$6,'D:\Payroll\2018 PAYROLL\Payroll 1.15.18\[TIMEKEEPING FOR 1.15.18.xlsx]TKS'!$E$3:$AJ$3,0)))

is there a way to just type it the file in a cell (i.e. a3 = 1.30.18) so that excel will automatically look in that file like:

=IF(AN8=0,0,INDEX('D:\Payroll\2018 PAYROLL\Payroll 1.30.18\[TIMEKEEPING FOR 1.30.18.xlsx]TKS'!$E$5:$AJ$650,MATCH($B8,'D:\Payroll\2018 PAYROLL\Payroll 1.30.18\[TIMEKEEPING FOR 1.30.18.xlsx]TKS'!$C$5:$C$650,0),MATCH(AT$6,'D:\Payroll\2018 PAYROLL\Payroll 1.30.18\[TIMEKEEPING FOR 1.30.18.xlsx]TKS'!$E$3:$AJ$3,0)))

Hope someone can help

 

[Special-K99]

Try

=IF(AN8=0,0,INDEX('D:\Payroll\2018 PAYROLL\Payroll 1.15.18\[TIMEKEEPING FOR "&TEXT(A3,"mm.dd.yy")&".xlsx]TKS'!$E$5:$AJ$650,MATCH($B8,'D:\Payroll\2018 PAYROLL\Payroll "&TEXT(A3,"mm.dd.yy")&"\[TIMEKEEPING FOR "&TEXT(A3,"mm.dd.yy")&".xlsx]TKS'!$C$5:$C$650,0),MATCH(AT$6,'D:\Payroll\2018 PAYROLL\Payroll "&TEXT(A3,"mm.dd.yy")&"\[TIMEKEEPING FOR "&TEXT(A3,"mm.dd.yy")&".xlsx]TKS'!$E$3:$AJ$3,0)))

with a date value in A3

You may need to use INDIRECT() in the above for each TEXT() in there, I'm not sure.
e.g.

...INDIRECT('D:\Payroll\2018 PAYROLL\Payroll 1.15.18\[TIMEKEEPING FOR "&TEXT(A3,"mm.dd.yy")&".xlsx]TKS'!$E$5:$AJ$650)...

 

[MiKos23]

Tried both but it doesn't work.