creating a function referencing multiple worksheets

[CENorth7272]

I have a workbook that tracks the time that customer service reps are o
the phone. I have seperate sheets for each day, with summary sheets a
the end of the wkbk.

The daily sheets are set up as follows:

the left most column displays the time range (ex: 8:00, 9:00, 10:0
etc.
the next column displays the reps name (there are 7)
there are several info columns and some total columns
the rightmost column is a total talk time for the hour.

On one of the summary pages I want to be able to add the total for eac
hour per rep.

Besides just summing the info I want to be able to manipulate it i
other ways (subtracting, averages, etc)

I'm sure there has to be a function or way of doing things that I don'
know abou

 

[Frank Kabel]

Hi
you may have a look at pivot tables. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
for more info

Frank

 

[CENorth7272]

I will look more into pivot tables. I've worked with them before
however, I have a feeling (and I could be wrong) that pivot table
aren't what I'm looking for.

Are there any functions that I can utilize. Or..

Is there a way to formulate a function, such as SUM so that instea
of:

=sum(Sheet1!A1,Sheet1!A9,Sheet1!A17, etc,

I could avoid either selecting each cell individually or manuall
entering the sheet name or cell ref. individually.

In effect, I would like to be able to tell Excel which page I'm workin
off of and perform the function based on that.

maybe I'm overthinking this...please help with any other idea

 

[RagDyer]

Don't know if I completely understand what you're looking for, but is this
going in the right direction ?

If your daily pages are an *exact* duplicate, design wise, of each other
(i.e. sum of 8:00 o'clock hour is J7 on *all* pages), then you might perhaps
try a formula similar to this on your summary (Sheet8) page:

=SUM(Sheet1:Sheet7!J7)
And, of course, you could drag down to copy and increment the row.

This type of formula will work with *any* named sheets, not just what I used
here.
Just remember, the formula works with the order in which the sheets are
stored in the WB, *not* in numerical or chronological order.

For instance, if *only* Sheet9 is between Sheet1 and 7, and the others are
following Sheet7, then *only* Sheet1,9 and 7 will be summed.

The same applies if your sheets are named Mon, Tue, Wed, etc.

For the formula:
=SUM(MON:FRI!J7)
to add all the 5 days, all the days must physically reside between MON and
FRI in the WB.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I will look more into pivot tables. I've worked with them before,
however, I have a feeling (and I could be wrong) that pivot tables
aren't what I'm looking for.

Are there any functions that I can utilize. Or..

Is there a way to formulate a function, such as SUM so that instead
of:

=sum(Sheet1!A1,Sheet1!A9,Sheet1!A17, etc,

I could avoid either selecting each cell individually or manually
entering the sheet name or cell ref. individually.

In effect, I would like to be able to tell Excel which page I'm working
off of and perform the function based on that.

maybe I'm overthinking this...please help with any other ideas

 

[CENorth7272]

Thanks,

That is much closer, actually that will be very useful in anothe
application in the same workbook.

Let my try to word my question this way...Is it possible to add ever
Nth cell on one page and show the total on another page.

Right now I have it set up like this on a summary page:

REP 1
=SUM(MONDAY!N4,MONDAY!N12,MONDAY!N20,MONDAY!N28,MONDAY!N36,MONDAY!N44,MONDAY!N52,MONDAY!N60,MONDAY!N68)

REP 2
=SUM(MONDAY!N5,MONDAY!N13,MONDAY!N21,MONDAY!N29,MONDAY!N37,MONDAY!N45,MONDAY!N53,MONDAY!N61,MONDAY!N69)

I would like an easier way to formulate this so it is easily change
per day and per rep.

Thanks - Cla

 

[CENorth7272]

In my last reply I gave an example of the format I'm using to calculat
the totals I need. Is that the only/best way to do it. It seems lik
there should be an easier way, or should I be looking into reformattin
some of my pages to get the totals I need

 

[RagDyer]

Why not Sum your daily totals on the *individual* daily sheets (instead of
the summary sheet), in *exactly* the same cell on each sheet, and then use
the formula I gave you on the summary sheet to total each daily sheet ?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

In my last reply I gave an example of the format I'm using to calculate
the totals I need. Is that the only/best way to do it. It seems like
there should be an easier way, or should I be looking into reformatting
some of my pages to get the totals I need ?

 

[CENorth7272]

Thanks RD

Thats exactly what I 'm going to do. good call. I had originally se
up the sheet to make it easy for input without really thinking muc
about calculations on the sheet. I'm just going to do a littl
reformatting and reorganization of the sheet.

BTW. I've been driving myself nuts trying to recall the formula tha
you gave me earlier. I had attended some classes a while back, sa
that function and thought about how useful it would be but I can't fin
where I wrote it down. So thanks for that also

-Cla

 

[RagDyer]

You're welcome !

I might also mention that one other benefit of constructing your WS to use
that formula, is that you can click and drag a WS tab *out* of the range of
the formula (left of MON, or right of FRI), and the totals on the summary
page will *exclude* that day (sheet tab) from the calculations, giving you a
"what if" scenario.
Conversly, you could also *add* a sheet tab and change the summary sheet
totals.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------



Thanks RD

Thats exactly what I 'm going to do. good call. I had originally set
up the sheet to make it easy for input without really thinking much
about calculations on the sheet. I'm just going to do a little
reformatting and reorganization of the sheet.

BTW. I've been driving myself nuts trying to recall the formula that
you gave me earlier. I had attended some classes a while back, saw
that function and thought about how useful it would be but I can't find
where I wrote it down. So thanks for that also

-Clay

 

[CENorth7272]

Good point, thanks again RD

-Cla