Do some Excel Work -- 2


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;

Additional scope:

- 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).

Skills: Data Processing, Excel

See more: tab scope, microsoft excel spread sheet, logic algorithm, microsoft at work, excel scenario, fill spread sheet with data, fill in spread sheet, fill in a spread, fill in a spread sheet with, excel tools, excel project 2/2, do some excel work 2, Do some Excel Work -- 2, d printable, create excel worksheet, spread sheet fill, data fetch spreadsheet, scan excel data, additional work scope, create user forms excel, lock specific cell, excel sheet consolidate, excel macro read data files, additional work scope work, excel selection list

Project ID: #6132547

5 freelancers are bidding on average $32 for this job


Hello - Good Morning !@ I just recently completed an inventory template. I have also worked on shopping engine export template and sales template before here at freelancer. You can have a look at my portfolio sa More

$35 USD in 1 day
(11 Reviews)

A proposal has not yet been provided

$19 USD in 1 day
(1 Review)

A proposal has not yet been provided

$20 USD in 1 day
(0 Reviews)

Hello As I am new on freelancer but this task is very simple and i can do it within 1-2 days. I am a professional excel worker who works for this kind of work only, creating automation tool that can be used for ver More

$25 USD in 1 day
(0 Reviews)

Propunerea nu a fost încă furnizată

$25 USD in 3 days
(0 Reviews)

Kindly allocate this project to me and i have many ideas related to the above functionality and if i got this project i will assure you that i will complete it on time and with quality work.

$55 USD in 1 day
(0 Reviews)