Do some Excel Work -- 2

  • Status Closed
  • Budget $10 - $30 USD
  • Total Bids 23

Project Description

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

Get free quotes for a project like this
Skills Required

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online