Formula to recognise a new sheet being added to a file, then returning a value

    On a summary page the formulas link to a cell within each of 200+ sheets. Additional sheets are being added all the time.


    Is it possible to write a formula on the summary page which will recognise when a new sheet has been added, eg sheet 205, 206, 207 etc, then return a cell refence from the new sheet.


    Thanks in advance

    Re: Formula to recognise a new sheet being added to a file, then returning a value


    How does a new sheet get added? Is it manually or is there some VBA involved? More than likely you can't do it with a formula unless you have a UDF. And even then I think you will have to catch an Event to know when a new sheet is added.

    Re: Formula to recognise a new sheet being added to a file, then returning a value


    Thanks.
    Added manually. I could set the spead sheet up with say 500 sheets in the first instance. Then I just need a formula to link the summary to each sheet individually (opposed to linking all 5oo sheets manually). Its easy to sum one cell on each sheet to one total figure on the summary, but I don't know how to list each total from each sheet separately on the summary sheet.


    Any thoughts?
    Thanks in advance.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login