Dynamic file/tab reference in formula?

[knobcreekfan]

Is it possible to have a dynamic file and tab reference within a formula?

Here is what I am trying to do...I have a new client that has a couple dozen facilities. Each facility has 10-12 crews. Each crew has 8-10 people. All of their pay is based on their units produced. So, I have a separate file for each facility and a separate tab within those files for each crew to compute their weekly pay.

Now I need to create an excel file that can be used to import this data into an outsourced payroll service. They have a very specific format they require so I cannot just use the spreadsheets as is from above. My intent is to simply look to the appropriate cells in each file & tab.

And that is what I am trying to make easier. I do not want to go into this spreadsheet and physically point each cell to the appropriate file & tab.

If I have a pretty simple lookup that says facility A = Phoenix, B = Tempe, C = Tucson, etc..., is it possible to have the normal =[Book1]Sheet1!A1 pickup a different file & tab based on those lookups?

Thank you.

[Ken Puls]

Hi there, and welcome to the forum.

You can, but...

If the workbooks were always open, you could use the INDIRECT function to do this. The problem is that INDIRECT can't pull data from a closed workbook.

There are workarounds, but you'll need to install an add-in to do it. There's more information here, but it also warns that it is quite slow.

So question that I'd have is... can you add a worksheet to each workbook that summarizes the data in the correct format for upload? That way the data would all be local, and we could help you with a macro to consolidate all the records into a single workbook for upload.

[knobcreekfan]

Thanks. Let me play with both INDIRECT and a summary page. If I go with the summary, I will need help with the macro.