Consolidate data from several sheets to a summary sheet

[racdac]

Hi All,

I have a workbook with several sheets containing results. I want to copy some of the data from each sheet to a summary sheet.

I will be adding more result sheets to the workbook each week. Ideally i would like to have the summary sheet update automatically, but running a macro is ok.

Becuase only some of the data is needed in the summary sheet, I thought rather than posting images I would post a link to the file.

The summary sheet just has links to the data that is needed to show any potential helpers where the data needs to come from. I hope this is Ok.

Any help would be appreciated.

Test results

 

[navic]

Any help would be appreciated.

You can use the INDIRECT function
In cell A8, A9, A10 put the name of the worksheet: Sheet1, Sheet2, Sheet3, etc.

In cell B8 put formula. Copy down

=INDIRECT($A8&"!C3")

In cell C8 put formula. Copy down

=INDIRECT($A8&"!C4")

etc ...

 

[racdac]

Thanks for your prompt reply navic and i apologise for my tardy reply, flat out o a different project yesterday.

I have tried your solution and it works a treat thank you very much for your help. I did modify the formula just a little to get rid of the #REF! errors to:

=IF(ISREF(INDIRECT($C8&"!A1")),(INDIRECT($C8&"!B4")),"")

Once again thank you

 

[navic]

I did modify the formula just a little to get rid of the #REF! errors to

I do not understand what you want.
Can you put a link to the example file here with expected results.
See my attached file

 

[racdac]

I will try to explain. The summary sheet is set up to accept 100 sheets. When measurements are completed another sheet is added with the results and in turn that updates the summary sheet. So if a sheet is not present i get this.

Becuase Sheet4 does not exist yet. I modified the formula to test if the sheet existed and to return nothing if it did not exist, and the data if it did.

There maybe an easier way around this but it is working perfectly.

 

[navic]

edit
please Moderator delete this double post

 

[navic]

Don't display or Remove error from cell

Becuase Sheet4 does not exist yet.

If you want remove the error as a result of the formula, try

=IFERROR(yourformula;"")

btw: You can automatically have a list of all worksheets in a workbook. See here.

 

[racdac]

Re: Don't display or Remove error from cell

Thanks navic, that works perfectly and is more elegant than my solution. Thank you for all your help.

 

[navic]

Re: Don't display or Remove error from cell

You're welcome

 

[Oeldere]

Re: Don't display or Remove error from cell

Why not work with 1 sheet for all data, that makes it a lot easier to handle?

You can analyse the data then e.g. with a pivot table.