0

I use Google Sheets for my time sheets, using the following folder structure:

Timesheets
 Corporate
  CorpClient1
   Timesheet (Drive Sheet)
  CorpClient2
   Timesheet (Drive Sheet)  
 Freelance
  FreelanceClient1
   Timesheet (Drive Sheet)  
  FreelanceClient2
   Timesheet (Drive Sheet)
 Projects
  ProjectName1
   Timesheet (Drive Sheet)  
  ProjectName2
   Timesheet (Drive Sheet)  
 Volunteer
  VolunteerOrg1
   Timesheet (Drive Sheet)  
  VolunteerOrg2
   Timesheet (Drive Sheet)

In each Timesheet, I have data in the following columns: Date, Start Time, End Time, Duration, Task

I'd like to create a sheet under Timesheets (call it Timesheet Tracking) with the following columns: Date, Hours, Client, Category

Where Category is one of: Corporate, Freelance, Projects, Volunteer.

Somehow, I need the Timesheet Tracking sheet to do something like:

  • take the date in cell A2 of Timesheet Tracking, search for this date in each time sheet in turn, and for each time sheet it finds a matching date, total the duration entries that match that date, then enter this total in cell B2, enter the Client name in cell C2, and the category in cell D2.
  • it needs to add a separate line for each date/duration/client/category result

I don't know where to start.

  • 1
    Start by using IMPORTRANGE to get the external data into your "Timesheet Tracking sheet", then use arrays to put all imported data together in a single array. – Rubén Jun 27 '16 at 16:39
1

I'll outline the strategy in three steps. Some of them may be combined, but doing things step by step makes the process more manageable.

Import ranges

Start by pulling the data from assorted spreadsheets into one summary spreadsheet. For example, create a separate sheet/tab for each source, enter =importrange(..., "A:E") in A1, and something like

=arrayformula(if(A:A <> "", {"This_Client", "This_Category"}, ))

to mark the client and category for the source.

Combine data

In a summary sheet, enter

={filter(Sheet1!A:G, len(Sheet1!A:A)); filter(Sheet2!A:G, len(Sheet2!A:A)) } 

to combine all the sources, excluding empty rows.

Query data

Use a query to summarize data and present selected columns.

=query(Summary!A:G, "select A, sum(B), F, G group by A, F, G") 
  • Thanks Bookend. Having a problem with importrange if you're online. – Steve Jun 25 '16 at 4:16
  • Hmmm, it didn't prompt me for anything, it immediately presented #REF! as the cell value after confirming the formula. – Steve Jun 25 '16 at 8:48
  • Great. Thanks. Not very user friendly, Google. – Steve Jun 25 '16 at 9:48
  • I've reached up to Query data okay. When I enter that last query in a cell in the Summary sheet below the rest of the data rows, I receive Circular dependency detected. – Steve Jun 25 '16 at 9:58
  • Let us continue this discussion in chat. – Steve Jun 25 '16 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.