How to use a value in cell to refer to a certain data file sheet in a formula?

[Kees_W]

I wont to us the value of a cell to point to a particular sheet in a data file. How can I do that?

Example:

In cell A2 I specify the name "Sheet2" as the sheet to be used. How can the formula  "=SUM(xxxxxx!$A$1:$A$10)" arranged such that the content of cell A2 is placed on "xxxxx". I have to do this 500 times for all the data available, so a simple replace operation is too borring.

[#2 · Accepted solution]

@Kees_W Try this:

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

[#3]

@Riny_van_Eekelen 

This is the way to do it it works.

Many thanks for your help.

[#4]

@Kees_W Graag gedaan!