0
We make use of an Excel template (not a proper template file but just a pre-laid out spreadsheet with blank cells awaiting input) to serve as a method of recording data during the build of a product. Each product build has its own unique 'card'.
We regularly update the 'template' as new measurements or whatever are added/removed.
If there are current builds in progress, there is then a rather laborious process of saving a copy of the live card, saving a version of the updated template in its place, then copy/pasting all the input data (so far) from the old version to the new version. Multiply this by number of live builds.
I am wondering if there is a more efficient way to achieve this.
We prefer to stick with Excel as it means more people can pick it up and understand how it works...moving to something like Access means less people will understand how its working and therefore unable to make easy updates to the template.
I would like a better method than the laborious task of copy and pasting multiple ranges of input cells from one to the other every time (about 8 sheet of data).
Thanks for any suggestions.
j0nr
Posted 2017-02-14T08:45:33.750
Reputation: 33
Have you ever tried using macros? Play with the macro recorder - you may find it does what you need. For us to help in any detail, we'd need to see the layout of your 'before' and 'after' templates, what macros you've already tried, and a specific question about a thing that isn't doing what you need, rather than a more general, open-ended 'please make it work'.
– Andi Mohr – 2017-02-14T10:56:00.160I know about macros. The trouble is when the template gets updated, the layout may be different, but same data, just in different positions. My question is more a request for perhaps a better way to approach the problem. A database approach I think would be better, as all inputs would have a unique identifier and be pulled out and presented to the user through one common interface, the interface which can be updated easily and in one place...I guess Excel just isn't really designed to do that...easily. – j0nr – 2017-02-15T15:26:20.073
If the column headers always use identical names, but they're just in different positions, you could add in a macro step that searched the header row for each field to identify which column it was located in. (Even if there were a few variants of the column header names you could add a lookup step to 'normalise' the data). But it's a bit beyond the scope of superuser to write all of the code you'd need without seeing the templates you're working from – Andi Mohr – 2017-02-16T10:30:02.027