Yeah I was afraid of that answer as that is not really an option for the users of the worksheets. Is there no other way to union these like unioning two and create a new data source and then using that data source to union another one etc although that would probably be even more messier?
You need to first consolidate your worksheets for sure.
then I guess using cutom SQL you can union and create separate data source/ Follow the link to know about Custom SQL.
Hope this will help.
What you can do is use the New Union -> Wildcard (automatic) option. This will point to the general folder and the Sheet "Name" in the Excel Workbook.
This will search the parent and subfolders for the indicated workbooks and even worksheets.
Hope this helps.
PS: Please mark as Answer if it solves your concern. Thanks.
Well this only works when all the sheets are in ONE workbook stored on Dropbox. With multiple workbooks that have the sheet "Data" in it I can only refer to that one workbook using this method.
However, it works when using Excel as source as here it can somehow look into the entire folder and retrieve multiple workbooks.
Here is the attempt with Dropbox (not working):
And here using Excel connection (which however is not an option for me ) though it is working easily
This works with Excel but apparently the subfolder detection does not work with Dropbox unless I am using it wrongly or have to change some settings in Dropbox?