Search

[BadBoy]

I have a workbook that has the first sheet named ‘totals’ and 20 other sheets
which are named as employee names.

The totals page sums all the numbers of activities and time associated with
each activity for all 20 employees and sums it on the totals page which has
the same headings as each employee’s page.

The numbers on the totals page is the sum of all 20 employee pages.

Question;

Is there a way that I can type the name of any one of the 20 employees in,
for example cell A1 on the totals page and only the numbers from that
employee will show on the totals page?

The formulas on the totals page are like this;

=SUM(SP1!D14,SP2!D14,SP3!D14,SP4!D14,SP5!D14,SP6!D14,SP7!D14,SP8!D14,SP9!D14,SP10!D14,SP11!D14,SP12!D14,SP13!D14,SP14!D14,SP15!D14,SP16!D14,SP17!D14,SP18!D14,SP19!D14,SP20!D14,SP21!D14,SP22!D14,SP23!D14,SP24!D14,SP25!D14)

=SUM(SP1!D15,SP2!D15,SP3!D15,SP4!D15,SP5!D15,SP6!D15,SP7!D15,SP8!D15,SP9!D15,SP10!D15,SP11!D15,SP12!D15,SP13!D15,SP14!D15,SP15!D15,SP16!D15,SP17!D15,SP18!D15,SP19!D15,SP20!D15,SP21!D15,SP22!D15,SP23!D15,SP24!D15,SP25!D15)

I only want the information for what ever name I type in the search window
ie: Cell A1 on the totals page.

For individual employee statistics;
Employee Name Search> Type Name Here

Thank you very much in advance.

-Bad

 

[Luke M]

First, to tidy up your formula. Since your referring to same cell in all
sheets, and I'm assuming all your employee sheets are next to each other, use
a 3d reference like so:

=SUM('SP1:SP25'!D14)
&
=SUM('SP1:SP25'!D15)

Second, to answer your question. You can use the INDIRECT function to
combine a cell's contents into a actual cell reference, like this:
=INDIRECT("'"A1&"'!D15")

Note that the use of single quotation marks is used in case any of your
sheet names ever have a space in their name. Thinking ahead a bit, you could
combine 2 formulas into this:
=IF(ISBLANK(A1),SUM('SP1:SP25'!D14),INDIRECT("'"A1&"'!D14"))
which display complete total, unless a name is specified.

 

[BadBoy]

Thank you very very much!

Not only the answer but some house keeping ideas also.

You my friend,.. are da man!

-D