Importing/Exporting data using Excel and some specific rules
I have two workbooks.
The one that I shall name from now on "Source" is a multiple sheet workbook that is free to be edited by a number of people and is stored on Google Drive. It can be downloaded so it's not that important where is stored, actually.
It contains data as it follows:
On the first column (1) the name of the employer. On the second one (2) it's the name of the brand. (3) contains the project he's working on and (4) the task. So imagine that the employer is working on brands X, Y, Z. For X he's managing the website and for that he has to upload images and to correct some text. For Y he has to do some graphic design, meaning leaflets and posters. For Z he has to brand some cars and to paint a few walls. Branding the cars he has to print the decals and for painting the walls, well, nothing more.
The next columns (5 - 9) are formatted as numbers and contains a "X" if he decided to work on that task but haven't be able to do so, or a number which means the number of hours he worked on that task.
The first row contains info about the the columns (Name/Brand/Projects/Task/Date)
The second one contains info only about the day, right under the date (1/2/3/4/5).
After every group of brands worked on by an employer, there's a row which sums the number of hours. While the number it's not important, it can be useful as a rule to divide between the employer.
That's one of the sheets. The sheets are completed during every week so they are named like (1.01 - 5.01). The next one would be named (8.01 - 12.01) and so on. So the workbook is updated continously.
And there's the second workbook.
Every brand wants a monthly report of the number of hours each employer worked on their projects and tasks. So there would be a one sheet workbook fetching data from the sheets that contains info only about one month. This may be a subject to change, if it's a must. It can be a workbook that contains multiple sheets, every one containing monthly data instead a workbook with just one sheet.
The first column (1)displays the day (4, 10, 13 etc), the second one (2) the name of the employer, the (3) the name of the project (like 3 from the Source), the (4) the internal code of the project (Car branding may be named XXX and Painting the walls AAA), the (5) the specific task (like 4 from Source), (6) contains a tag that's specific for the data imported from Source (there are 2 more tags, one for data imported from other workbooks specific to some employers, but this can be done manually and another one for a client service that can be added at the end of the month, duplicating every row and replacing the other tags with CS and modifying some other values), (7) is the number of hours he worked on one day.
So, the second workbook would try to fetch data for brand X something like this:
1. Starts searching the first day of the month, going through every employer.
2. If it finds the brand X, it gets the project name, the task he'd done and the number of hours he worked on that day.
3. It keeps adding one row for every task he'd done (because the number of hours are calculated based upon the task, not project)
Keep in mind that when the monthly reports are send to the brand they should be "unlinked" but with the final data saved there. The brand won't have acces to the Source but only to the second workbook, the report one. I can also use Acces if it must, but I don't know, as for now, how to use it, but with a little help won't be a big problem.
I attached a little workbook to use as an example for what I want to do.
Lots of thanks!
|