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
VitalieCiobanu's avatar
VitalieCiobanu
MCT
Oct 31, 2018
Hey Ted, can you share a file with some sample data? It's not quite clear what you're trying to do/reference.
Ted Wozniak's avatar
Ted Wozniak
Copper Contributor
Oct 31, 2018

Hi,

 

Please see the attached sample data that roughly replicates what I'm trying to do in a set formula.  My ultimate goal will be get all this data into a table format, however, this attempt is a short term fix to try to 'pull' data into a template.  Maybe I'm overthinking it.....

 

Also, I changed the subject above....somehow that got selected when I posted the message.

  • VitalieCiobanu's avatar
    VitalieCiobanu
    MCT
    Oct 31, 2018

    Ok, don't know if I got it right...

    So you want D30 to have file name and selected tab changed dynamically when you select different values in your C4 and C5 list values?

    What is $C$6 in D30?

     

    Anyway, if this is close to what you want, here's what I did.

    Because I don't know if your file names on SharePoint will be of same length, I'm getting file name only in column E using flash fill. So I ended up having File1 to File12 in E14 to E25.

     

    Next, changed D5 vlookup to =IFERROR(VLOOKUP(C5,C14:E25,3,FALSE)," ") to include E column with file names and display file name in D5.

    Now, in D30 put =CONCAT("[",D5,"]",C4,"!",$C$6). Again, don't know what that C6 is, because that cell is blank in your file.

    • 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.  

Resources