Referencing a sheet that doesn't exist - yet

[kosciosco]

We have a tool that includes a macro that creates a sheet when you tell it what sheet you want.

so for instance if you say you want a "RED" sheet you specify that, hit a button and it creates a sheet called "RED1" based on a RED template.

There can be lots of RED sheets.

Each sheet has a total and on a front page sheet there is a total of all the RED sheets.

The problem I have is that I want to create a formula which says
+RED1!$F$10+RED2!$F$10+RED3!$F$10

Except RED1, RED2, RED3 don't exist when you start, so how can I get it to create a total?

Thanks

Simon

[EdMac]

Have you tried to have two dummy sheets, say first red and final red. create your new sheets between them then you can

=sum('first red'!:'final red'!F10)

Ed