Multi-Source Dropdown Update from Filtered Data?

Rob W.

Hi all,

Got a fairly complex application and trying to figure out whether an optimization is feasible and which tools to employ in a solution. The objective was to create a daily time-card interface for salary employees to capture hours worked against a given project and task. Here's where I'm at:

Project Schedule Blueprint - Check, includes sumif to central tracking sheet for hours

Central task tracking sheet - Check, captures employee, task, project, and hours worked daily

User interface - Check, dynamic view of central task tracking sheet w/ current user filter

Current state - Users update daily through dynamic view, copy in project, task, and hours

Optimization - I currently have the project number as a dropdown option, request was to expand this to be a dropdown of active tasks by user to streamline data entry / reduce clicks and error-proof.

In theory, I would just add a concatenated column in the schedule to grab the project and task detail, then offload this to an excel attachment at the sheet via data shuttle (filtering out completed tasks), and set an upload automation to drive the dropdown menu in the central task tracking sheet.

Challenges:

1. The source data is coming from 100+ sheets (and n- future sheets) generated by a control center blueprint. I don't know of a clean function to append excel tables from multiple sources except through a copy row automation or Excel Power Query. At one point there looked to be a pseudo- data model function under sheet 'connections', but I was never able to get this to do what I wanted, and this looks to have been replaced with a link to data shuttle.
2. This doesn't solve for the current user filter, but that's not as critical. A report would get this done, but I don't know if it's possible to turn a report into an excel export (automatically) to be used as a source in data shuttle.

Options: We have access to Gold applications: Bridge, Data Shuttle, etc. and I could see offloading sheet data daily to be transformed in Excel or Power BI, but don't want to set up a data shuttle function for every existing and future sheet.

Any suggestions appreciated.

Paul Newcome

My suggestion would be the API. It may be possible through Bridge as it is supposed to be a more user friendly version of the API, but I don't have as much experience Bridge as I would like and as such can't say for sure.

Either way, you are going to want to create that report and get it in excel format for your Data Shuttle.