Auto-change worksheet names within formulas

[jeroenft]

I have a workbook where I have a button to automatically create a new worksheet. (ex. 1, 2, 3, etc...). I would like to have a Summary worksheet that automatically grabs the data from the existing and newly created worksheets (1, 2, 3...etc) On the Summary worksheet (LOV).

However, I am unable to increment the values of the referenced worksheet on my Summary Worksheet. I'm not sure how to change the numerical value inside the single-quotes which refer to the worksheet 1, 2, etc. I would like to increment the 1 to a 2 and then to a 3 as each new worksheet is created and the Summary puts the data in the next available line, resulting in a Summary Report of Report 1 to Report N.

Hopefully that makes sense. If anyone has an idea, I would appreciate some guidance. In the attachment you can find the excel file, so that it will make more sense.

Thanks In Advance.

[jeroenft]

This was the solution:
=INDIRECT("'"&A1&"'!$A$1")
By placing the names of the sheets in cell A1 you can change the names of your worksheets in a formula.

[olebucky]

This solution solved my problem too. A small amount of research can work wonders. BTW, I was troubled "formulazing" changing named ranges. It's all good now!