Auto-fill cells with different sheets that have unique sheet names

Hi,

We have 54 different sheets (different names) all with unique names and I am trying to create one sheet that have their aggregate scores in them.

I understand if all the sheets were named Sheet1, Sheet2,...Sheet54, I could just go into Sheet55 and in, say in cell A2, I could enter Sheet1!D7, in A3 I would enter =A2+1 and propagate that down until the data from Sheet54 is entered.

How would I do something like this if the sheets are all uniquely named.

For example, Sheet1 is named John Doe, Sheet2 is named Jane Doh, Sheet3 is named Mike Simpson, etc, etc.

I have a working basic knowledge of VBA so VBA answers are welcome, but non-VBA answers would be something I would be more comfortable with!

Thank you