Referencing Non-Existent Worksheets

This may be a bit hard to explain so please bare with me.

As you can see from below, I have a 'ROLL UP' worksheet at the end of my excel which gathers all of the data from the previous worksheets into 1 summary page

Project1
Name Time Pay
John 2h $30

Project2
Name Time Pay
Sam 3h $40

Project3
Name Time Pay
Tim 1h $25

ROLL UP
Name Time Pay
John 2h $30
Sam 3h $40
Tim 1h $25

_________________________________________________________________________________________________________________

Here is the jist of the problem. This is a working document and I do not know how many projects there will be at the end. So, I created a macro which when clicked, will generate a clean project worksheet for the individual to fill out. So when they have another project completed, they click the button, and it will automatically create Project4, Project5, ETC.... (These are all new worksheets within the same excel file)

HOWEVER, how to do I automatically have my roll up page detect new sheets as they are created?

__________________________________________________________________________________________________________________

My idea was to have write ='Project4'!A1 (which will error, because Project4 doesn't exist)

Then when the user clicks the add new worksheet button, a Project4 page will be created and the roll up page will then see it, and un-error.

This does not seem to work however. Even though the new tab created matches the name, the page will not recalculate. I added a force recalculate line into the create page button macro but it will still not work.

I hope this makes sense...



THANKS!!!!!!!!

Hi -

Thanks, but this is not quite working. I want to reference a worksheet that does not exist, so if the user enters a new tab and calls it "project4," my other sheet can reference it. It would be great if it works in an equation, rather than VBA.