Forum Discussion
OneDrive Files On-Demand For The Enterprise
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.
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.
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.
- Well, if all your files have same columns, maybe Power Query can help you get around "opening each file" issue.
You can import each file separately or you can combine them into one worksheet, if that's ok for what you need.
https://www.youtube.com/watch?v=ldoQws7Zbx8
https://www.youtube.com/watch?v=a7E29H5ZUmEThanks 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!