Linking multiple Sheets when adding new sheets?

[paulmcp]

Hi,

I am trying to create a summary table of a few cells from each sheet I have in a single workbook. Which is simple enough, but I want to find a way if possible to have the summary table update when I add new Sheets to the workbook.

As an example in every sheet in:
- Cell A1 = person name
- Cell A2 = Date of an evaluation
- Cell A3 = Position of person
- Cell E50 = overall evaluation score


So summary sheet should in column A list all the names of people, B the date of evaluation, c position etc....

So as and when new evaluations are entered they show in summary table.

thanks for any help !

 

[mumps]

When you add a new sheet, the sheet is blank so your Summary cannot be updated until you enter the new data. In order to update the Summary, after you add a new sheet, you could add a Worksheet_Change macro to the new sheet that will automatically update the Summary. Another option would be to have a macro that you could run manually every time you want the Summary updated. Would either method work for you? Are A1, A2, A3 and E50 the only cells copied to the Summary from each sheet? Are the "Summary" and the evaluation sheets the only sheets in you workbook?

 

[paulmcp]

Mumps, thanks for the reply.

The idea would be for users to use the move/copy for new sheet so reference cells would always be int he same place.

Both options would work although this would be used by my colleagues in other locations and having everything automatically updated would be preferable

The A1, A2, A3 + E50 are theoretical cell locations.

The reality is I need to build the evaluation form as well, but until I understand if I can accomplish what I would like to do before to waste too much time on designing the form.

 

[mumps]

To clarify: users would copy an existing sheet that would have cells that would need to be copied to a summary sheet and this new sheet would serve as the evaluation form. Is this correct? If not, please clarify in as much detail as possible.