I have a production board that is currently done by hand using magnets and want to automate using 2 files.
One file (SAPDATAFILE) will have data coming from SAP - 4 fields that would show updates of order.
Second file (ORIGINAL DATA FILE) would show where the job is in production using Green or Red cells (currently use magnets). The cell info would be keyed by the workers on floor and changed based on conditional formatting hopefully.
The intended process -
Once the SAPDATAFILE is downloaded to excel file to update the orders the ORIGINAL DATA FILE is merged into the SAPDATAFILE and would be the NEW Current Board.
I have run Power Query and placed the merged file into the ORIGINAL DATA FILE as a worksheet (NEW BOARD). This new board worksheet should be the one updated with Green or Red in cells with the next daily SAPDATAFILE.
Questions I am having trouble with are:
CONFUSED ON: If the query is updating each day using the Original File (INITIAL BOARD worksheet in the Merge process), how can I get the New Board worksheet data into the ORIGINAL DATA FILE (INITIAL BOARD worksheet) to be used for daily updates. The New Board worksheet would be used by both Power Query and by workers to change cells Green or Red.
I plan on doing nightly automatic updates so the board is refreshed each morning or perhaps sooner if workers refresh the NEW BOARD during the day. Do you see any problems with refreshes. In particular, losing the Conditional Formatting of Green/Red cells.
Thanks for any help you can provide.
Richard
Hi Richard,
Welcome to our forum! Thanks for sharing your files.
If your workers make changes to the query output on the 'New Board' sheet, those changes will be lost when you refresh the query. Therefore, the solution is to have the workers only enter their information into the Initial Board table. You can then get the initial board and SAPDATA and bring them together into your New Board table to be used as a reporting tool only.
Hope that helps.
Mynda
Hi Mynda,
Thanks for the quick reply.
The issue I can't figure out is the INITIAL BOARD will not be current as they are looking at it once the first merge is done. I need to automatically get the NEW BOARD info into the INITIAL BOARD each time the merge is run. How can I do that within Power Query or would it have to be some other means.
Thanks.
Richard
Hi Richard,
You can't have the workers enter data into the resultant query table. Power Query can only display data in a table that it gets from somewhere else. It can't reference itself. i.e It gets data from Table A and puts it in Table B. When you refresh the query it will overwrite anything you type in Table B.
You would have to manually, or with VBA, create a copy of the data in Table B and paste it to Table C where your workers can enter their data, then reference Table C in your next query along with the SAPDATA, thus creating a copy of the query output for your workers to enter data in and also a new query to reference this new table each time you want to refresh the report.
I think you need a database that can take data entry, like Access, where your workers can enter their notes and you can import new data from SAP to keep the database up to date.
Hope that clarifies the limitations.
Mynda
Hi Mynda,
Thanks for the help. I figured I might have to use VBA which I am unfamiliar with. I was able to get the file refreshed anytime a change was made but now have to get the copy/paste to work so query can reference the same file. I prefer using Excel and not getting into Access. I know Power BI but getting a new excel file for workers to use off BI would be too difficult perhaps. I will do more research for Excel and VBA.
Thanks again.
Richard
Hi Richard,
Power BI doesn't allow you to input data and it uses the same Power Query tool as Excel, so this won't solve the problem, unfortunately. If you get stuck with the VBA, you can post in our VBA forum.
Mynda
