How to set a formula to run a tally of multiple sheets that have not been created?

I am creating an excel sheet for my company to run a tally of client information for programs that we sell in 2017, but the catch is that we do not know how many clients we will have in 2017. My question is, if I want to run a tally of company names on sheet 1 and I use this formula in A2 (=Worksheet!$C$2) is there a way that if we add another Worksheet, that A3 will have that same formula but pull from the new sheet that was created or do I have to create all 1000 sheets right now for all anticipated business of 2017? Thanks for reading!

You can set a range of non blank rows for the new sheet. If you attach sample file perhaps someone here can help you.

Tally for 2017 will pull the information from each of the "Worksheets". It is my intention that every time we get a new lead, we will duplicate that Worksheet tab and then then Tally sheet will pull A3... from that new sheet.

I have only put in the formula for Company Name right now until I get the sheet working correctly.

Does anyone have a variation of this that may work?

=(Start:End!(Worksheet!$C$2))

If you added the worksheet name for each company in column A (or any column of your choice) of "Tally for 2017", you could use:

in B2 (now "Company Name")

=INDIRECT("'" & A2 &"'!C2")

NOTE: INDIRECT is a volatile function so this will impact performance but if you are only adding companies as needed it should be acceptable.