I currently have 5 Spreadsheets, one for each team. Each of these Workbooks
includes a page for each person in that team, with the addition of
"Consolidated" , "Temps" and "Unallocated". These sheets are used to monitor
every piece of work in and completed by everyone.

All the sheets in all 5 books look similar, with dates along row 1, and jobs
down column A, with totals at the bottom. Column B is filled with "Start" and
"Finish" so if someone is given 10 inputting to do and they do 4 they find
the correct date along the top, the correct job down the side and in the
start/finish boxes put 10 and 6.
The only major difference between Workbooks is that the job list is tailor
made for each team.

Several problems have arisen with these sheets since I set them up. Apart
from the obvious size issue of the files, there are only 265 columns, which
is less than a year of weekdays. Adding new staff and new jobs for a team is
a time consuming process. Plus, everytime something is moved any sheets
referencing at these archive files need to be updated (mostly due to the
totals being at the top) and a major issue is mistakes while being completed.

To solve as many of these problems as I can, I'm setting up new sheets that
have the dates down the left hand side. Using another Sheet to complete that
day's productivity (either on a team or individual level) and activating a
macro copies the information to these new sheets, and on a job by job level
calculates where the information should be stored, adding any new jobs and
dates as needed. Since only jobs and dates used are there, it saves largely
on space, these sheets have gone from 20meg to 10meg just like that.

The trouble I am having is getting totals. Other workbooks will want to
automatically pull this information to use to provide Productivity / Work /
Time levels over teams or the company over differing times.
To find productivity for a team, I need to be able to look at X amount of
sheets (all sheets apart from "Calc") find the appropriate date in Column A
for each sheet then add everything for that row, Column D, then compair that
to similar for Column E (the totals are fixed in those two columns regardless
of how many jobs are answered, learnt from that mistake)

First off I tried to have somewhere in the "Calc" sheet that used Indirects
to look at every sheet (the macro used to add a new sheet for a new person
also adds that name into row 144) but since I still don't know what dates
these other sheets will need I had to include them all in these totals. All
these indirect formulas slowed the sheet down when the "updating" process was
taking place and I'm hoping to avoid that.
The only other thing I can think of is having on every sheet that calculates
productivity a series of macros that pull the information as required, but
that seems unwieldy everytime someone wants to know what the productivity was
on a certain day. And on more complex sheets I currently have that look at
how much work was done, compairing against hours worked, and looks at a
supplied figure and gives the dates closest to that much "Work Done" with how
many people were working and predicts how many people need to work. A sheet
like that calculates it all pretty much in the blink of an eye with formulas,
but with macros I'd imagine it'd take alot longer.

So, can anyone help me with any suggestions? I'm sticking with Excel,
dispite the fact i'm sure something else would be more appropriate. If only
the Indirect formula worked on a closed workbook, then I think I could easily
pull off the information.

Cheers if you've read all this, I wanted to make sure I was being clear on
the layout. I hope I haven't made these sheets too complex, its the easiest
way to save and store the information I feel.

Hi, Paul-
I think I understand enough of your process; would it be possible to
set up a single tab in a single workbook to handle every person from
every team? This would effectively create a database-like table of
information that you could use to summarize data. For instance: you
could have dropdown columns for Team number, a dropdown column for Team
member name, a dropdown for project name, a start date column, an end
date column, etc.

This would solve you the aggro you're currently getting from changes to
cell references and definitely save space, for instance. It would
allow you to total and summarize data efficiently and effectively, and
the use of dropdowns would prevent data entry problems. You could sort
rows to group data by team and team member, if that would help.

That would be something of a sea-change in terms of the way you
currently maintain the data, but in the end it would be much more
efficient because you could run pivot tables to summarize the data.

Is this do-able?
Dave O

If I understand correctly, you are basically creating a formula that sums
from each workbook. Perhaps if, instead, you were to create a worksheet on
your master workbook that pulls the column D data only, then run your
formulas off the master spreadsheet data. (This way, your formula itself is
running off of local data). Hope that makes sense. It sounded good in my head.

By the way, Access is definitely much better for this, what with forms and
links available and such, but old habits....

"PaulW" wrote:

I currently have 5 Spreadsheets, one for each team. Each of these Workbooks
includes a page for each person in that team, with the addition of
"Consolidated" , "Temps" and "Unallocated". These sheets are used to monitor
every piece of work in and completed by everyone.

All the sheets in all 5 books look similar, with dates along row 1, and jobs
down column A, with totals at the bottom. Column B is filled with "Start" and
"Finish" so if someone is given 10 inputting to do and they do 4 they find
the correct date along the top, the correct job down the side and in the
start/finish boxes put 10 and 6.
The only major difference between Workbooks is that the job list is tailor
made for each team.

Several problems have arisen with these sheets since I set them up. Apart
from the obvious size issue of the files, there are only 265 columns, which
is less than a year of weekdays. Adding new staff and new jobs for a team is
a time consuming process. Plus, everytime something is moved any sheets
referencing at these archive files need to be updated (mostly due to the
totals being at the top) and a major issue is mistakes while being completed.

To solve as many of these problems as I can, I'm setting up new sheets that
have the dates down the left hand side. Using another Sheet to complete that
day's productivity (either on a team or individual level) and activating a
macro copies the information to these new sheets, and on a job by job level
calculates where the information should be stored, adding any new jobs and
dates as needed. Since only jobs and dates used are there, it saves largely
on space, these sheets have gone from 20meg to 10meg just like that.

The trouble I am having is getting totals. Other workbooks will want to
automatically pull this information to use to provide Productivity / Work /
Time levels over teams or the company over differing times.
To find productivity for a team, I need to be able to look at X amount of
sheets (all sheets apart from "Calc") find the appropriate date in Column A
for each sheet then add everything for that row, Column D, then compair that
to similar for Column E (the totals are fixed in those two columns regardless
of how many jobs are answered, learnt from that mistake)

First off I tried to have somewhere in the "Calc" sheet that used Indirects
to look at every sheet (the macro used to add a new sheet for a new person
also adds that name into row 144) but since I still don't know what dates
these other sheets will need I had to include them all in these totals. All
these indirect formulas slowed the sheet down when the "updating" process was
taking place and I'm hoping to avoid that.
The only other thing I can think of is having on every sheet that calculates
productivity a series of macros that pull the information as required, but
that seems unwieldy everytime someone wants to know what the productivity was
on a certain day. And on more complex sheets I currently have that look at
how much work was done, compairing against hours worked, and looks at a
supplied figure and gives the dates closest to that much "Work Done" with how
many people were working and predicts how many people need to work. A sheet
like that calculates it all pretty much in the blink of an eye with formulas,
but with macros I'd imagine it'd take alot longer.

So, can anyone help me with any suggestions? I'm sticking with Excel,
dispite the fact i'm sure something else would be more appropriate. If only
the Indirect formula worked on a closed workbook, then I think I could easily
pull off the information.

Cheers if you've read all this, I wanted to make sure I was being clear on
the layout. I hope I haven't made these sheets too complex, its the easiest
way to save and store the information I feel.

I don't know why I hadn't thought of something like this, since all the
reports pulled off the system are in this format, so its a format I use every
day, I guess I was too busy thinking in 3 dimensions that I never realised
listing it all in 1 dimension would be suitable.

I can see 2 main problems with this though.
1) Productivity is completed by different people, only 1 of the 5 teams
completes it themselves, but it still means 10/12 can be trying to do it at
the same time which can produce read/write problems.
2) If 300 different jobs are done in a day that gives me less dates to work
with than what i've currently got running horizontally.

I don't think I need to have all teams working on the same sheet, 5 seperate
workbooks should be fine, especially since people will always only be working
on 1 of them, and there's little need for an overall figure where there isn't
a need for a team figure, so I can get the overall by adding the individuals
together.
If I have a seperate worksheet for each year, then I can happily set up the
sheets to go upto say 2020 by just copying the empty sheet with headers, and
on the totals page I can happily have all the dates in a long column one by
one for those 24 years with an indirect formula, or simply a normal formula
shouldn't be difficult.

I can also keep the "tools" I've set up on an individual basis for each
teams requirements just have the information copied differently. And it
should run quicker to copy a selection over to the next free space on the
sheet rather than having the looping macro's copy the information to specific
cells on an individual basis.

This only leaves me with the slight worry of how slow the sheet might be
once theres 20 years of formulas in place, but then again, I can use
Sumproducts on closed workbooks, so I might not need any formulas in the
archive workbooks at all.

Cheers for the suggestion. Like you said, it makes getting the results so
much easier, and effectivily, and it seems simpler to store the information.
I'll have a look at it.


"Dave O" wrote:

Hi, Paul-
I think I understand enough of your process; would it be possible to
set up a single tab in a single workbook to handle every person from
every team? This would effectively create a database-like table of
information that you could use to summarize data. For instance: you
could have dropdown columns for Team number, a dropdown column for Team
member name, a dropdown for project name, a start date column, an end
date column, etc.

This would solve you the aggro you're currently getting from changes to
cell references and definitely save space, for instance. It would
allow you to total and summarize data efficiently and effectively, and
the use of dropdowns would prevent data entry problems. You could sort
rows to group data by team and team member, if that would help.

That would be something of a sea-change in terms of the way you
currently maintain the data, but in the end it would be much more
efficient because you could run pivot tables to summarize the data.

Is this do-able?
Dave O

This is what I tried initially. I need to use Indirects because the formulas
need to include sheets that don't yet exist so they're up to date in 6/12
months. And because on each individual sheet only includes the dates used (so
if Jake were off for 2 weeks there isn't a 2 week gap, it goes straight from
01/12/06 to 18/12/06 on the next line) then I need to do a match for every
date with these indirect formulas. Once i've found the line on each sheet
that corresponds to that date (if its there) then I need another indirect to
look at that row, column 4.

=MATCH($C15,INDIRECT(CONCATENATE(ADDRESS(1,1,,,D$1 4),":A6000")),0)

40 times (to allow for more people to be added to row 14) followed by

=IF(ISERROR(D15),0,INDIRECT(ADDRESS(D15,4,,,D$14)) )

Then add those 40 together, do it again for column 5.

so thats 120 formulas (to allow for 40 people in total to work in that team
forever) for each day. Once I pull those formulas down 600/700 rows to allow
for a couple of years its 84000 formulas. And at this point the sheet is
getting slow to use, which is damned annoying when you're using a macro to
copy information about on a regular basis.

Cheers for the suggestion, as I said, its what I thought of first.

"Sean Timmons" wrote:

If I understand correctly, you are basically creating a formula that sums
from each workbook. Perhaps if, instead, you were to create a worksheet on
your master workbook that pulls the column D data only, then run your
formulas off the master spreadsheet data. (This way, your formula itself is
running off of local data). Hope that makes sense. It sounded good in my head.

OK, how about using Data-Get External Data
New Database Query

you may have to create your control sources first by selecting options, and
creating sources for each worksheet

Pull in only the columns you need, and this should avoid your blank cell
issue as well.

It can be a slight bit complex, and kind of hard to provide much detail
without he actual data in hand, but I hope this helps in some small way. :-)


"PaulW" wrote:

This is what I tried initially. I need to use Indirects because the formulas
need to include sheets that don't yet exist so they're up to date in 6/12
months. And because on each individual sheet only includes the dates used (so
if Jake were off for 2 weeks there isn't a 2 week gap, it goes straight from
01/12/06 to 18/12/06 on the next line) then I need to do a match for every
date with these indirect formulas. Once i've found the line on each sheet
that corresponds to that date (if its there) then I need another indirect to
look at that row, column 4.

=MATCH($C15,INDIRECT(CONCATENATE(ADDRESS(1,1,,,D$1 4),":A6000")),0)

40 times (to allow for more people to be added to row 14) followed by

=IF(ISERROR(D15),0,INDIRECT(ADDRESS(D15,4,,,D$14)) )

Then add those 40 together, do it again for column 5.

so thats 120 formulas (to allow for 40 people in total to work in that team
forever) for each day. Once I pull those formulas down 600/700 rows to allow
for a couple of years its 84000 formulas. And at this point the sheet is
getting slow to use, which is damned annoying when you're using a macro to
copy information about on a regular basis.

Cheers for the suggestion, as I said, its what I thought of first.

"Sean Timmons" wrote:

If I understand correctly, you are basically creating a formula that sums
from each workbook. Perhaps if, instead, you were to create a worksheet on
your master workbook that pulls the column D data only, then run your
formulas off the master spreadsheet data. (This way, your formula itself is
running off of local data). Hope that makes sense. It sounded good in my head.