I have a spreadsheet that tracks time for all employees across all projects.
And I will start a sheet to track employees that are over or under the
alotted monthly time.
So I need to have the Over/Under sheet open the Time workbook, search the
TOTAL for each employee (there's a Pivot table that tracks the Total for each
employee) and then copy any TOTAL row that has a greater than 1 total in it.
This sheet tracks each month of the year, so I need it to check those fields.
FOr the life of me, I don't know how to program this in VBA, so help would
be GREATLY appreciated!
Thanks!
Heather

Hi Heather,

If I understand what you want, I would use formulas.

You could put the Over/Under sheet in the Time workbook.

In column A:A, place the list of employees--names or ID numbers--
however you identify them in the Time workbook.

Then in column B:B, put vlookup formula that uses your pivot table as
your table array.

The formula will look something like:

=VLOOKUP(A1,Time!A1:E500,3,FALSE)

Dan

Dan-
I'll try that and let you know how it goes.
Thanks.
Heather

"dan dungan" wrote:

Hi Heather,

If I understand what you want, I would use formulas.

You could put the Over/Under sheet in the Time workbook.

In column A:A, place the list of employees--names or ID numbers--
however you identify them in the Time workbook.

Then in column B:B, put vlookup formula that uses your pivot table as
your table array.

The formula will look something like:

=VLOOKUP(A1,Time!A1:E500,3,FALSE)

Dan

Dan-
That didn't work. No idea what I did wrong.
What I need is something like this:
If total for each employee any month is greater than 1, then copy the row
and put it in Over worksheet. If row 1 in Over is full, move to row 2
.....
Then I need code for Underallocation which says basically the same thing...
If total for each employee for any given month is less than 80%, then copy
the row and put it in the Under worksheet. If row 1 in Under is full, move
to row 2.
Thanks!

"Heather" wrote:

Dan-
I'll try that and let you know how it goes.
Thanks.
Heather

"dan dungan" wrote:

Hi Heather,

If I understand what you want, I would use formulas.

You could put the Over/Under sheet in the Time workbook.

In column A:A, place the list of employees--names or ID numbers--
however you identify them in the Time workbook.

Then in column B:B, put vlookup formula that uses your pivot table as
your table array.

The formula will look something like:

=VLOOKUP(A1,Time!A1:E500,3,FALSE)

Dan

Hi Heather,

I'm not sure I understand what you need. I'm not able to visualize
your situation. My hopefully clarifying questions are below:

On Mar 24, 9:01 am, Heather wrote:
I have a spreadsheet that tracks time for all employees across all projects.

What is the name of this spreadsheet?

And I will start a sheet to track employees that are over or under the
alotted monthly time.

Is this the Over/Under sheet?

So I need to have the Over/Under sheet open the Time workbook, search the
TOTAL for each employee (there's a Pivot table that tracks the Total for each
employee) and then copy any TOTAL row that has a greater than 1 total in it.

What is the layout of the pivot table? I don't understand how you
would have a Total row. I am imagining a Total Column.

This sheet tracks each month of the year, so I need it to check those fields.

Which fields? How do these fields relate to the month of the year?

Dan

Currently, there isn't an Over/Under sheet. I need to create that.
The layout of the pivot table is this:
Resource, Role, Project as Rows. Jan 08, Feb 08, Mar 08, etc through to Dec
08 in Data.
The fields that I am referring to are the months of the year.
If the data in any month field *on the resource's Total row* (because
Resources work on more than one Project) is greater than one, I want the
entire row to be copied and dropped from the Pivot table sheet and dropped
onto the Over/Under sheet.
I also need to track Underallocation (less than .80) in the month fields and
have that information drop into a different part of the Over/Under sheet.

"dan dungan" wrote:

Hi Heather,

I'm not sure I understand what you need. I'm not able to visualize
your situation. My hopefully clarifying questions are below:

On Mar 24, 9:01 am, Heather wrote:
I have a spreadsheet that tracks time for all employees across all projects.

What is the name of this spreadsheet?

And I will start a sheet to track employees that are over or under the
alotted monthly time.

Is this the Over/Under sheet?

So I need to have the Over/Under sheet open the Time workbook, search the
TOTAL for each employee (there's a Pivot table that tracks the Total for each
employee) and then copy any TOTAL row that has a greater than 1 total in it.

What is the layout of the pivot table? I don't understand how you
would have a Total row. I am imagining a Total Column.

This sheet tracks each month of the year, so I need it to check those fields.

Which fields? How do these fields relate to the month of the year?

Dan