Asking for the Moon - Pivot Table not Adding Row to Whole Sheet 1

[akrshaw]

Good Monday Morning!~

Getting an early start this morning!~

I have a Pivot Table that looks like this:

Bid Manager
Doe, John
Pursuit One
Pusuit Twelve
Pursuit Gator
Smith, Jane
Pursuit Fly By
Pursuit Forty-Three

To the right of the Pivot Table I have:

2/6 2/13 2/20 2/27 3/5 3/12 3/19

So it looks like this:

Bid Manager 2/6 2/13 2/20 2/27 3/5
Doe, John
Pursuit One
Pusuit Twelve
Pursuit Gator
Smith, Jane
Pursuit Fly By
Pursuit Forty-Three

They can input high, medium and low to show their availability for each week for each pursuit.

My problem is when you refresh to add a new pursuit the rest of the sheet does not add a row, so all the other rows get thrown off.

I know this is less than ideal, I am just trying to get something to work until we get the database built. AND something that doesn't require having 20 spreadsheets and multiple data entry!~

If there is another way to accomplish, please point me in the right direction!~

Thanks so very much!~

 

[SkipVought]

hi,

A PivotTable is a reporting tool. It is not designed for data entry. An Excel PivotTable points to a Source Data Table, that is the BIBLE. THAT Source Data Table is where your new data needs to be added, it seems.

Maybe you need to explain WHAT it is that you are trying to accomplish, rather then HOW you think it ought to be done on a spreadsheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[akrshaw]

Will do!~

I have a data sheet with the following information (there is other data, but shortned):

Pursuit Name Pursuit ID Bid Mgr Bid Crd Date Assigned
Pursuit One GFGDFGD43 Smith 1/1/2011
Pursuit Fly By LDSOEMG76 Doe Sparrow 3/4/2010
Pursuit Gater LCPWLGR87 Barney Wiggles 5/8/2009

What I need to do is have something that shows me each Bid Mgr or Bid Crd and the pursuits they are working on, because you may have some that have both assigned and some pursuits that just have one or the other.

They need to be able to show their bandwidth for each week for each pursuit.

 

[SkipVought]

You are talkin' yer own jargon! I can only GUESS what that means.

What is Bid Mgr, Bid Crd, band width, pursuit.

What are you trying to accomplish?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[akrshaw]

Sorry, hard to turn off the jargon!~

OK, see if this is better.

There may be 1 or 2 people working on each project (Bid Mgr and Bid Crd)

The way the data sheet is setup it shows both people on one row to consolidate the notes and keep down multiple data entry.

I need to split that out on another sheet and list the projects each person is working on. This way each week they can update their availability on each project.

I hope this helps....not sure how else to describe it.

 

[SkipVought]

The way the data sheet is setup it shows both people on one row to consolidate the notes and keep down multiple data entry.

Is multiple data entry REALLY that much of a problem?

Trouble is, by "saving" on data entry, you increase the effort of things that you have to do to accomplosh what you just asked for: "I need to split that out on another sheet and list the projects each person is working on." The way your data is structured, not normalized (ie 2 workers on one row), you must hold your breath, close your eyes, stand on one hand and do a back flip. Had your data been normalized, it could simply be done via a PivotTable.

You might consider such a structure...
[tt]
Pursuit Name
Pursuit ID
EmplID
EmplTitle
Date Assigned
Date Completed
[/tt]
where EmplID is the employee assigned, EmplTitle could be Bid Mgr or Bid Crd, and Date Completed if needed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[akrshaw]

I would LOVE to normalize the data!~

My skills in Excel are keeping me from accomplishing that, looks like I need to learn how to make a database in Excel while I work on the Access Database.

 

[SkipVought]

How many rows do you currently have in your table?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[akrshaw]

Right now I have data in 60 rows.

 

[SkipVought]

Here's how you can quickly normalize your data.

1) Save your original.

2) make 2 copies of the table

3) in the FIRST copy
a) delete the DATA in column Bid Crd
b) rename the Bid Mgr column Employee or whatever you want
c) rename the Bid Crd column Emp Title or whatever you want
d) Put Bid Mgr in the Emp Title column for each row

4) in the SECOND copy
a) delete the DATA in column Bid Mgr
b) SORT the data on Bid Crd and then DELETE the row with no Bid Crd
c) Drag the Bid Crd DATA to the Bid Mgr column
d) Put Bid Crd in the adjacent column for eac row
e) COPY the DATA and then PASTE a the bottom of the FIRST copy.

Now you have normalized data: one row for each employee.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]