Need Help to create Summary sheet.

  • Thread starter Thread starter HelpmeExcel
  • Start date Start date
H

HelpmeExcel

Hello:
I'm working on Excel2000.
In one excel file i have one sheet "MasterData" as show below:
**********************************
EMP_CD WORK_DT WORK_HRS
-----------------------------------------------------
ASH 8/25/2004 0:00 8
IPH 8/25/2004 0:00 2
IPH 8/25/2004 0:00 2
MAN 8/25/2004 0:00 8
NKH 8/25/2004 0:00 1
NKH 8/25/2004 0:00 2
RXK 8/25/2004 0:00 8
YPG 8/25/2004 0:00 8
NSH 8/25/2004 0:00 8
ASH 8/26/2004 0:00 8
YPG 8/26/2004 0:00 8
IPH 8/26/2004 0:00 8
**********************************

Now, I need to create summary sheet (New Sheet) in same file , whic
will show result as follows. Also If tommarow new employee joins i
should populate in summary sheet automatically.
**********************************
EMP_CD WORK_HRS
-----------------------------------------------------
ASH 16
IPH 12
MAN 8
NKH 3
RXK 8
YPG 16
NSH 8
*********************************
 
HelpMe,

Use a pivot table, and when you add a new Employee, either redefine the
pivot table source range, or add the new emplyee by inserting a row into the
existing table rather than below it.

HTH,
Bernie
MS Excel MVP
 
Dave,

I'm guessing that you're thinking "What is it with Bernie's dislike of
dynamic named ranges?"

Well, I like them, for me, but I think that there are just too many ways for
an inexperienced user to mess them up. And I think it's more important that
they understand the basics of how references are updated so that it's less
likely that they will have trouble in general.

But that's just my $0.02, which as we all know is worth less now than ever
;-)

Bernie


Dave Peterson said:
You could also use a dynamic range name that expands/contracts when data is
added/deleted.

Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xlNames01.html#Dynamic
Click to expand...
 
I did wonder about that (especially after I saw the 2nd post) <vbg>.

But sometimes (especially the pivottables stuff), it makes life so much easier.

And I know that I always verify that I don't have empty cells in my key
columns/rows and I'll always do the Edit|goto, type in the range name and see
what's selected. (I find that I mess them up the first 8 times, too!)



Bernie said:
Dave,

I'm guessing that you're thinking "What is it with Bernie's dislike of
dynamic named ranges?"

Well, I like them, for me, but I think that there are just too many ways for
an inexperienced user to mess them up. And I think it's more important that
they understand the basics of how references are updated so that it's less
likely that they will have trouble in general.

But that's just my $0.02, which as we all know is worth less now than ever
;-)

Bernie
Click to expand...
 
Back
Top