Forum Discussion

Ted Wozniak's avatar
Ted Wozniak
Copper Contributor
Oct 30, 2018

OneDrive Files On-Demand For The Enterprise

Hello,   I am attempting to set-up a summary template with set formulas that would change the file name and tab selection through drop-down selection lists.  The cell references would the same, jus...
excel
Formulas and Functions
office 365
Ted Wozniak's avatar
Ted Wozniak
Copper Contributor
Nov 04, 2018

Thanks for your help...gave me some good ideas.  I ended up finding the exact direction I was trying to go on youtube with ExcellsFun using the INDIRECT function.  It looks little trial/error on the syntax, but was able to create a template that pulls data in from multiple files.  All files are located in a SP folder, but, the only downside is that all files do need to be open to pull the data.  It is a tremendous time saver for some financial analysis directly from some static files.  

  • Ted Wozniak's avatar
    Ted Wozniak
    Copper Contributor
    Nov 05, 2018

    Thanks for the additional thoughts - I will look through those...I have minimally dabbled with PQ so far, but, looks like something that could take this to another level.  I was trying to avoid bringing all the tabs from multiple files (it could be as many as 12 with 15 tabs each) into one file as it could become a bit unwieldy.  As an FYI, following was the formula that I was able to create for my use:

     

    =INDIRECT("'"&FilenameTrend2&"]"&SiteTrend2&"'!"&HLOOKUP(B$2,Settings!$D$27:$I$61,$I6)&"")

     

    I named a few cells to help ease the cell references and the HLOOKUP helps define what columns to look in the source file(s) for.  The columns differentiate Actual/Plan/Prior Year results.  It might not be the most efficient formula for now, but it works :) 

     

    Thanks again for your thoughts!

     

     

Resources