The project goal is to consolidate different files of the same microsoft spreadsheet into a single consolidated spreadsheet.
The scenario will be two tools and two kind of target users:
- XLS Form to be filled by approx 100 users twice a year;
- Microsoft XLS with a macro that would fetch all the filled forms at a specific moment and consolidate data into a single new tab/spreadsheet; this xls will be used by no more than 4 users;
For mainteinability, the consolidation logic must be located at the spreadsheet and not a various XLS forms (spread for more than 100 people). It must comprises of:
1. Provide a button/trigger to start the consolidation;
2. when clicked, asked user to inform a directory to scan ms xls forms documents;
3. when submited, create a new work-sheet at the same spreadsheet having the current date (DDMMYYY_HHMM) into its name;
3. the algorithm must scan this directory and all subdirectories and, for each XLS found it must bring data to the target worksheet following certain rules:
a. data are those located in green or yellow cells; all of them must be consolidated at the target consolidation spreadsheet;
a. each form will fill in a new line at the worksheet;
b. each field (only form fields) will be a column at the spreadsheet;
c. column name must be consistent with form information.
d. form fields:
- text type: text must be filled at corresponding target worksheet cell;
- selection list: the corresponding value (not label) must be filled at corresponding target worksheet cell;
- selection list (Yes/No): 0 or 1 must be filled at the corresponding target worksheet cell;
- lock the forms cell that aren´t destinated for input (all, except yellow);
- lock the entire form spreadsheet and allow input only in yellow fields (no changes in layout as well);
- make the spreadsheet printable in A4 format (all content must be able to be read).
22 freelancers are bidding on average $31 for this job
Hi: Please check my profile page to know more about my experience in Excel macros. I have 100% completion rate and 5-star feedback on projects. Please connect with me to discuss more.
i am a partly qualified Charted institute of management accountant and i am able to do accounting works until report writing and decision making through costing calculation.