Very new excel user....summary question

Hello All,

I'm hoping this question has an easy answer. I maintain a spreadsheet for my company's sales force. it's about 140 sheets and on each individual sheet is a person specific weekly sales call report. On the last page I need to create a summary that includes the entire sales force and I have manually entered the formula for the first 2 people. I'm wondering if there is a way to automatically import the rest of the data without manually entering. The only variable is the person (name of each sheet) every other piece of data is in the same sell on each page.

Example:

The summary below is pulled from a sheet named Ingersoll (sales reps last name). I need 140 of these to run down a summary page where in each of these the only variable is the name of the worksheet that the data is pulling from. Is there a way to type a formula so the data can be duplicated 140 times where all I have to do is input the sheet's name? like a insert formula if sheet's name is ingersoll...input formula if sheet's name is johnson...etc.



Any help would be tremendously appreciated so I don't spend my entire weekend.

Thank you

Jim

Input the sheet name in A2 and use

=INDIRECT("'"&A2&"'!M17")

and replace M17 with the cell on the rep's sheet that you actually want to reference

This may be a stupid question but How do I type that out if I want to use multiple cels. For exacmple on each sheet I need the data from Cells A1:G20.

So for example on my summary sheet I need to show the data from a sheet named Casey Cells A1 Through G20 and then below that data on the summary sheet I need to show the same data from a sheet named Johnson Cells A1 through G20. I know I come off as an idiot, just not a ton of excel experience here. Your help is very much appreciated.

You want to show those 140 cells in say B:EK?

Sort of. What I have is 140 sheets that are all identical in format (see example A below). What I need to have happen is to have the summary page replicate the data running down a summary page (SEE EXAMPLE B BELOW) it's basically the same exact data but a copy past isn't efficient as I would like it to tall be live so I don't have to run through formatting and extra work each week. Please let me know your thoughts.

EXAMPLE A: INDIVIDUAL PAGE


EXAMPLE B: (SUMMARY)

Lost me completely now, that is really not an enlightening visual.

Post an example workbook, with a couple of examples of what you want on the summary sheet.