Java Junkie
New Member
- Joined
- Jan 17, 2018
- Messages
- 3
Hey, everyone. I would like to open some discussion on the best approach to a task I have been given for creating a solution to simplify a process. I am not Excel savvy. I can use it, do nifty things when I need to, but I am not a guru or fluent in VBA or formulas.
I have been asked to help with a project at work where a current manual process is considerably time consuming and leaves a lot of room for human error. The long convoluted process is outlined below. *(Note: Do not read until your caffeine levels are high enough to prevent falling asleep at your desk!)
Background information:
A standalone program is used to manage many types of details and information for ingredients used in all of our recipes. We are required to monitor the recipes for ingredients that are considered allergens in order to make sure consumers are warned in advance before consumption.
When recipes change or new ones are added to the master database, we use a built-in tool to export the entire list of ingredients being used in new recipes or new ingredients being used in existing recipes. The list exports into a new workbook (which is created as 'Book1' onto the user's local drive) and contains only the data relevant to the specified recipe retrieved in the query.
At present, we are manually copying and pasting the freshly exported data from 'Book1' into a common workbook ('Recipes') that tracks each recipe revision and ingredient property in one spreadsheet. 'Recipes' uses conditional formatting to highlight the specific allergens we wish to monitor if/when they are present.
As it is now, in order to populate the remainder of the ingredient info, we copy the new ingredient data from a third worksheet ('Ingredients') that tracks details per ingredient, rather than the entire recipe, and then paste it into the common worksheet to fill in gaps not covered by the exported info. 'Ingredients' contains static information that never changes (formal name, identification information, etc.).
My task is to figure out a method to streamline the whole process to eliminate as many manual steps as possible.
Limitations:
-have to write it to work with a common filename on the exported worksheet (exports as Book1)
-have to write it to work with a common location of the exported worksheet (exports to local computer)
-have it insert data at bottom of destination worksheet and apply the conditional formatting
-number of items in each export/import is not consistent - based on the individual recipes ("bottom" is relative)
-need to be able to select specific target columns/cells to call data from and paste data into
Summary:
There is data in a program that is exported into 'Book1". The new data is manually copied and pasted into 'Recipes' but only some of the columns are filled in. The remaining data for the individual ingredients is copied and pasted in from a third workbook, 'Ingredients'. This process needs to be simplified, either using VBA or built in functions/formulas.
I am hopeful that some chatting here can help send me in the right direction for how to approach this, a better idea for what would work best and how to set about doing it.
I appreciate everyone's input but am not just asking for blocks of code. I would actually like to discuss the pros and cons of what works best and why. I would greatly like to learn what it is that I am going to have to do here to solve this task.
Thanks in advance for all replies...I apologize for the essay post, but I want to make sure the entire scenario is clearly explained.
I have been asked to help with a project at work where a current manual process is considerably time consuming and leaves a lot of room for human error. The long convoluted process is outlined below. *(Note: Do not read until your caffeine levels are high enough to prevent falling asleep at your desk!)
Background information:
A standalone program is used to manage many types of details and information for ingredients used in all of our recipes. We are required to monitor the recipes for ingredients that are considered allergens in order to make sure consumers are warned in advance before consumption.
When recipes change or new ones are added to the master database, we use a built-in tool to export the entire list of ingredients being used in new recipes or new ingredients being used in existing recipes. The list exports into a new workbook (which is created as 'Book1' onto the user's local drive) and contains only the data relevant to the specified recipe retrieved in the query.
At present, we are manually copying and pasting the freshly exported data from 'Book1' into a common workbook ('Recipes') that tracks each recipe revision and ingredient property in one spreadsheet. 'Recipes' uses conditional formatting to highlight the specific allergens we wish to monitor if/when they are present.
As it is now, in order to populate the remainder of the ingredient info, we copy the new ingredient data from a third worksheet ('Ingredients') that tracks details per ingredient, rather than the entire recipe, and then paste it into the common worksheet to fill in gaps not covered by the exported info. 'Ingredients' contains static information that never changes (formal name, identification information, etc.).
My task is to figure out a method to streamline the whole process to eliminate as many manual steps as possible.
Limitations:
-have to write it to work with a common filename on the exported worksheet (exports as Book1)
-have to write it to work with a common location of the exported worksheet (exports to local computer)
-have it insert data at bottom of destination worksheet and apply the conditional formatting
-number of items in each export/import is not consistent - based on the individual recipes ("bottom" is relative)
-need to be able to select specific target columns/cells to call data from and paste data into
Summary:
There is data in a program that is exported into 'Book1". The new data is manually copied and pasted into 'Recipes' but only some of the columns are filled in. The remaining data for the individual ingredients is copied and pasted in from a third workbook, 'Ingredients'. This process needs to be simplified, either using VBA or built in functions/formulas.
I am hopeful that some chatting here can help send me in the right direction for how to approach this, a better idea for what would work best and how to set about doing it.
I appreciate everyone's input but am not just asking for blocks of code. I would actually like to discuss the pros and cons of what works best and why. I would greatly like to learn what it is that I am going to have to do here to solve this task.
Thanks in advance for all replies...I apologize for the essay post, but I want to make sure the entire scenario is clearly explained.
