formula assistance needed

[Eric_in_EVV]

Each employee has a separate tab in the spreadsheet with their own details in
it...I'm trying to put together a separate tab that has a one line summary
for each employee. The separate tabs have the employee name as the tab
name...can I use the employee name (generically) in column A in the summary
sheet in a formula to pull info back from each employees detailed sheet ?

I want the summary sheet to look something like this:

Employee Name Annual Allotment Total Points Incurred
Smith, Joe 12 0

with the annual allotment and total points incurred being formula driven by
looking up the info from the employee name listed....how do I use "Smith,
Joe" generically (column A, row ##) in a formula to grab data from specific
cells a separate tab named "Smith, Joe" ??

Does that make any sense ?

Thanks for any advice !

 

[RagDyer]

In the Summary Sheet, enter the employees names down Column A *exactly* as
they are formatted and spelled in the sheet tabs, say A2 to A100.

Say, in the employee sheets, you're looking to return the values in E2 and
F2 to the summary sheet.

In B2 of the summary sheet enter this formula:
=INDIRECT("'"&A2&"'!E2")

And in C2 of the summary sheet enter this formula:
=INDIRECT("'"&A2&"'!F2")

Now, select both B2 and C2, and drag down that 2 cell selection to copy the
formula down as far as you have names entered in Column A.

You could also simply *double click* the "fill handle" of that 2 cell
selection,
to automatically copy the formulas down Columns B & C as far as there is
data in Column A.

 

[Sean Timmons]

=COUNTA(INDIRECT("'"&A2&"'!A:A"))

 

[Luke M]

check out the INDIRECT function. To return the sum of A1:A10 on sheet "Smith,
Joe" you can do this:

=SUM(INDIRECT("'"&A2&"'!A1:A10"))

Note that in this formula, since A1:A10 are enclosed in qutation marks, they
will not change if you copy the formula left/right.

 

[Eric_in_EVV]

Thank you all for your assistance....I have what I need now to make things
work !

 

[RagDyeR]

You're welcome, and appreciate the feed-back.