Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-25-2014, 03:22 AM
stagg201 stagg201 is offline Importing/Exporting data using Excel and some specific rules Windows 8 Importing/Exporting data using Excel and some specific rules Office 2013
Novice
Importing/Exporting data using Excel and some specific rules
 
Join Date: Mar 2014
Posts: 1
stagg201 is on a distinguished road
Default 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!
Attached Files
File Type: xlsx example.xlsx (11.0 KB, 7 views)
Reply With Quote
  #2  
Old 03-27-2014, 08:58 PM
macropod's Avatar
macropod macropod is offline Importing/Exporting data using Excel and some specific rules Windows 7 32bit Importing/Exporting data using Excel and some specific rules Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The workflow, with a separate sheet for each week, makes this unnecessarily difficult. Given that a single Excel worksheet can handle 1,048,576 rows, there is no good reason all the data can't go onto one sheet from where extraction would be a simple task. On top of that, you're trying to use Excel as both a database and a database report generator, a task for which Access is much better suited. With all the data on a single Excel sheet, though, report generation via mailmerge would be a reasonable alternative.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing/Exporting data using Excel and some specific rules Exporting Resource Usage data to Excel samg54 Project 1 08-02-2013 05:28 AM
Importing/Exporting data using Excel and some specific rules Macro: Exporting Data to a LEGIBLE Excel Spreadsheet jeffcoleky Word VBA 6 05-08-2012 08:24 AM
Importing/Exporting data using Excel and some specific rules * Exporting Access Data to Excel djreyrey Excel Programming 1 03-23-2012 10:03 PM
Importing/Exporting data using Excel and some specific rules Importing data from excel using a macro soma104 Word 1 04-14-2011 05:10 PM
Importing Rules - Folder broken? jkies00 Outlook 0 08-28-2006 08:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft