Results 1 to 3 of 3

Thread: Dynamic file/tab reference in formula?

  1. #1
    Neophyte knobcreekfan's Avatar
    Join Date
    Oct 2011
    Location
    North Carolina
    Posts
    2
    Articles
    0

    Dynamic file/tab reference in formula?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte knobcreekfan's Avatar
    Join Date
    Oct 2011
    Location
    North Carolina
    Posts
    2
    Articles
    0
    Thanks. Let me play with both INDIRECT and a summary page. If I go with the summary, I will need help with the macro.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •