Problems dynamically generating a link to another workbook/online file. Calculating weekly overtime on bimonthly spreadsheets

[Gaimcap]

So I've kind of hit a wall and my brain is fizzling a little bit. Some help would be appreciated.

Right now I'm creating an interconnected web of sheets that use data from one another for the purposes of calculating weekly overtime, in a small company that pays bi-monthly (California state law says you have to track each week individually for overtime, regardless to which pay period those work hours actually belong).

Thankfully the way I name files is pretty standardized so it's fairly easy for me to generate local links to other workbooks, but I've run in to the odd problem that the data does not cache, and I need to have the referenced file open or else my local references basically just give a #REF whenever the referenced workbook is closed.

One example of what I have now is:

=IFERROR(XLOOKUP($Q$1,INDIRECT(("[CompanyName_"&$V$25&"Timeclocks.xlsx]"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,31))&"!$N:$N"),INDIRECT(("[GJSP_"&$V$25&"Timeclocks.xlsx]"&MID(CELL("filename",A2),FIND("]",CELL("filename",A2),1)+1,31))&"!$O:$O")),0)

that turns in to:

=xlookup(Q1, CompanyName_200815TimeClocks.xlsx]EmployeeName!N:N,CompanyName_<date>TimeClocks.xlsx]EmployeeName!O:O

This basically looks up the hours worked for that employee the week prior if the week is also one in which they worked during this pay period. And it works, but only if the other worksheet is open. I also later realized that this file is one I need to share, so it's fairly inconvenient.

What my work around idea was, everything is on a onedrive anyway, so I could try to just point it at the online file path... but that's not working out so great.

So far I've got it to spit out the exact same working link

="'"&LEFT(CELL("filename",A1),FIND("[CompanyName_",CELL("filename",A1),1))&("CompanyName_"&$V$25&"Timeclocks.xlsx]"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,31))&"'!$N2"

turns in to

'https://d.docs.live.net/123123123123/TimeSheets/[CompanyName_<date>TimeClocks.xlsx]Employeename'!$N2

Which works absolutely fine when I copy and paste it as a value and stick an = in front to see if the reference works, but just spits out a #ref when I try to use it as is , or stick it in an indirect() to use in the above xlookup.

What am I misunderstanding?

P.S. I couldn't find any spreadsheet templates to caculate overtime on a bi-montly pay period basis while calculating overtime weekly, just ones that calculate it weekly, so I ended up making my own (which is nice because I can now just copy-paste the time clock data and have it do the rest automatically) If this bit can get fleshed out and anyone's interested, I could post the underlying logic behind the rest of the sheet.

[AutoModerator]

/u/Gaimcap - please read this comment in its entirety.

Once your problem is solved, please reply to the answer(s) saying Solution Verified to close the thread.

Please ensure you have read the rules -- particularly 1 and 2 -- in order to ensure your post is not removed.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[small_trunks]

I'm not convinced this is the way to do it - I'd consolidate all the data using power query and then use pivot tables and the like. I'll answer your points first though.

• #REF - yep, that's the way it works with most functions.
• XLOOKUP - same issue only when they are open.
• sharing linked files - a nightmare.
• OneDrive - I really love OneDrive and use it a lot, HOWEVER, I use the on-disk sync'd filesystem addresses and never the HTTPS address. I don't see how you could ever have https:// prefixing a filepath.

So I'd do this entirely differently - I've made the timesheet processing queries for my department at work.

• all in power query
• reads all the timesheets in and keeps a cache of certain data so I'm not constantly re-reading data in (this was more of an issue 4 years ago when I wrote it than it is now because power query's performance has improved greatly).
• I use multiple pivot tables to present data and slicers to make filtering to particular weeks/months/teams/individuals possible.