formula reference formula...

**swmasson** · 07-18-2008, 10:32 AM

but i jut cant figure it out... i have uploaded an example senario in case someone is able to help...

I am trying to create a time tracking spreadsheet whereby Table1 records hours daily for each job/project for each employee...

Table 2 is the weekly summary and is supposed to pull the Week (WK totals) from Table 1 for ech employee and each job/project...

When i try and drag the formula it does not work, so i'm guessing its a bit more complex than i hoped... I have alot more entries than displayed in the attachment, hence why i cant just create the formula for each wekkly total.

Any help would be much appreciated...

Thanks for your help

Regards

SWM

**Richard Buttrey** · 07-18-2008, 11:24 AM

Hi,

The trick is to use a new helper row 1 which contains the concatenation of the Job Number & Individual code in C1:N1, i.e. C1= JOB-01SM, D1= JOB-01KB, and the Week reference in Q1:AB1. You can hide this row 1 for presentation purposes.

A tip here, whenever you're in the position of wanting to analyse a database like table 1, make sure you have a unique reference in the header row and left column. By formatting cells like C2 and Q2 with an alignment across cells, you lose the ability to easily work out the column number for use in various functions.

Now enter in Q4 the formula:

Please Login or Register  to view this content.

And drag across and down.

I'm re-attaching the workbook with the above changes.

HTH

**swmasson** · 07-18-2008, 11:45 AM

Richard... your actually a genius... however, i am obviously not... i could not figure out how to transfer your forumla into my actual time tracking spreadsheet...

Thanks very much for the fast reply, would be very grateful if you could help me with the actual spreadsheet i have attached also... i made an attempt but a very poor one...

Thanks again for your help...

Find attached new spreadsheet...

Regards

SWM

**Richard Buttrey** · 07-18-2008, 01:25 PM

Hi,

See attached sheet. You'll just need to copy the formulae in column B, (which is a new helper column) on the Daily record sheet down the appropriate rows.

Regards,