Need Help to create Summary sheet.

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
*********************************
 
B

Bernie Deitrick

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
 
B

Bernie Deitrick

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...
 
D

Dave Peterson

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...
 
Top