Forum Discussion
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, just changing the file/tab. The problem I am running into is that I cannot find a way to 'embed' this logic in a formula to replace a direct link approach.
eg. ='file location[file name.xlsx]tab name'!m25
I have set up separate lists to manage the input of the file location/name & tab combo. I just cannot seem to find a way to be able to reference the cell that those drop downs are in to modify the above. I will be linking to separate month files all located in a common SharePoint folder. Thoughts?
6 Replies
- Hey Ted, can you share a file with some sample data? It's not quite clear what you're trying to do/reference.
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.
