Automate excel tasks to collect data from database
Budget: $30 – $250 USD
I currently have an Excel file (with VBA) that allows me to filter data.
Calculations are then made based on the remaining data after the filters is enacted.
Currently, I need to manually input the different combinations of filters I am evaluating.
I would like to automate this process maintaining the formulas and calculations.
To help understand the current process, I have included a sample worksheet that includes current calculations and filters. I have also included a series of PNG files for visuals.
Current process:
1) In the “current process.xlsm” file, enter parameters in (for example, cells I49 and J49). In this example, I entered “2.00” in cell I49 and “3.00” in cell J49.
Cells that correspond to those parameters are automatically filled accordingly. In this example, corresponding cells TNX2840 and TNY2840.
2) Click the “GO” button.
The “GO” button initiates the filter for the related items of the database (FH2663:VBF2809). Please see the filter activated on cell TLB2662 (GAP % filter) in the accompanying PNG file.
Results are calculated from the filtered database in cells EN195:FA2658.
The secondary filter generates the results on the sheet in EN5:FA179.
3) I then manually copy the cells with values from EN5:FA179 (only the cells with values) and paste them into the attached “aapl stats (example 1).xlsx” file.
4) I then click on the “Unfilter” button in the “current process.xlsm” file.
5) Erase the values from cell I49 and J49.
6) Enter new values for different parameters in cell I49 and J49, or other cells
7) Click the “GO” button..
8) Repeat the process
I would like to automate these steps to more efficiently create an “aapl stats.xlsx” file.
My idea is that I have a blank portion of the worksheet, or a different file, if necessary where I enter in the parameters I would like to check.
Each row is a different set of parameters/filters that I would like to have automatically filtered and the results pasted onto an “aapl stats.xlsx” file.
After the first row of parameters is calculated, copied and pasted into the “aapl stats.xlsx” file, then it moves onto the next row, and copies and pastes those results into the file. It will continue building onto the file until it reaches a blank row.
Please see the attached PNG files and accompanied spreadsheets to get a better visual of these steps.
Begin with viewing the step-by-step procedure in the "Automation.docx" file attached.
Calculations are then made based on the remaining data after the filters is enacted.
Currently, I need to manually input the different combinations of filters I am evaluating.
I would like to automate this process maintaining the formulas and calculations.
To help understand the current process, I have included a sample worksheet that includes current calculations and filters. I have also included a series of PNG files for visuals.
Current process:
1) In the “current process.xlsm” file, enter parameters in (for example, cells I49 and J49). In this example, I entered “2.00” in cell I49 and “3.00” in cell J49.
Cells that correspond to those parameters are automatically filled accordingly. In this example, corresponding cells TNX2840 and TNY2840.
2) Click the “GO” button.
The “GO” button initiates the filter for the related items of the database (FH2663:VBF2809). Please see the filter activated on cell TLB2662 (GAP % filter) in the accompanying PNG file.
Results are calculated from the filtered database in cells EN195:FA2658.
The secondary filter generates the results on the sheet in EN5:FA179.
3) I then manually copy the cells with values from EN5:FA179 (only the cells with values) and paste them into the attached “aapl stats (example 1).xlsx” file.
4) I then click on the “Unfilter” button in the “current process.xlsm” file.
5) Erase the values from cell I49 and J49.
6) Enter new values for different parameters in cell I49 and J49, or other cells
7) Click the “GO” button..
8) Repeat the process
I would like to automate these steps to more efficiently create an “aapl stats.xlsx” file.
My idea is that I have a blank portion of the worksheet, or a different file, if necessary where I enter in the parameters I would like to check.
Each row is a different set of parameters/filters that I would like to have automatically filtered and the results pasted onto an “aapl stats.xlsx” file.
After the first row of parameters is calculated, copied and pasted into the “aapl stats.xlsx” file, then it moves onto the next row, and copies and pastes those results into the file. It will continue building onto the file until it reaches a blank row.
Please see the attached PNG files and accompanied spreadsheets to get a better visual of these steps.
Begin with viewing the step-by-step procedure in the "Automation.docx" file attached.