Track when new sheets are added to an Excel file

[Jyggalag]

Hi all,

2 questions:

1) I have the following setup:

I add new files quarterly to my excel file, so Q1 2021, Q2 2021, Q3 2021, Q4 2021, Q1 2022, Q2 2022 etc.

Right now I only have one for each year, but I plan to add many more going forward for 2022-

Does anybody have a way in which I can automatically track when a new sheet is added? So I can make a cell for example saying:
2022 Q2 was added on: (formula that tracks when Q2 was added)
2022 Q3 was added on: (formula that tracks when the file, after Q2 was added, was added)
2022 Q4 was added on: (formula that tracks when the file, after Q3 was added, was added)

and so on

or does anybody have a better idea?

2) I am constantly changing cells in my sheet and I am wondering. Is there a formula that can look at when the last time a cell was changed? So for example, look below:

I would like to have a formula that does what cell E2, E3, and E4 are currently stating, if this makes sense?

Thank you so much all! Please let me know if you have any questions

Kind regards,
Jyggalag

 

[Georgiboy]

Have you tried Excels built in track changes option?

It lists all of the changes along with the username, what was changed, and when it was done. I think it even colour codes between seperate users.

 

[Jyggalag]

Have you tried Excels built in track changes option?

It lists all of the changes along with the username, what was changed, and when it was done. I think it even colour codes between seperate users.

i have not! but that sounds very nice.

I am not sure that I have this available however?

a formula seems very simple and easy, if possible, but otherwise please do elaborate

 

[Georgiboy]

Do you have lot's of code in the spreadsheet that multiple users use?

The reason i ask is that for track changes to work the workbook needs to be shared, this disables macro's if i remember rightly.

If you want to give it a go then:
GOTO Review in the ribbon at the top
Select share workbook
Select the little checkbox 'Allow changes by more than one use....'
Click OK
Click on 'Track Changes' in the ribbon
Select 'Highlight changes'
Untick: When, Who & Where
Untick: 'Highlight changes on screen'
Tick: 'List changes on a new sheet'

Save the workbook
Make a change to a cell, add a sheet, change a sheet name
THEN
Save the workbook again
GOTO: track changes onece more
Untick: When, Who & Where
Untick: 'Highlight changes on screen'
Tick: 'List changes on a new sheet'

It will then generate a new sheet with all of the changes you made...

Sorry not the best at explaining things.

 

[Jyggalag]

Ah

Do you have lot's of code in the spreadsheet that multiple users use?

The reason i ask is that for track changes to work the workbook needs to be shared, this disables macro's if i remember rightly.

If you want to give it a go then:
GOTO Review in the ribbon at the top
Select share workbook
Select the little checkbox 'Allow changes by more than one use....'
Click OK
Click on 'Track Changes' in the ribbon
Select 'Highlight changes'
Untick: When, Who & Where
Untick: 'Highlight changes on screen'
Tick: 'List changes on a new sheet'

Save the workbook
Make a change to a cell, add a sheet, change a sheet name
THEN
Save the workbook again
GOTO: track changes onece more
Untick: When, Who & Where
Untick: 'Highlight changes on screen'
Tick: 'List changes on a new sheet'

It will then generate a new sheet with all of the changes you made...

Sorry not the best at explaining things.

,
I do have some macros that I use so I assume that this would mess it up?

 

[Georgiboy]

No actually, i have just had a little read on the subject and the Macro's still remain and work but you can't edit the macro until you unshare the workbook again.
I think there are a few things you can't do with VBA inside a shared workbook like change the protection of sheets etc...
So may be worth a try unless all of your sheets are protected...

 

[Jyggalag]

No actually, i have just had a little read on the subject and the Macro's still remain and work but you can't edit the macro until you unshare the workbook again.
I think there are a few things you can't do with VBA inside a shared workbook like change the protection of sheets etc...
So may be worth a try unless all of your sheets are protected...

hm i will note it for now, but would still prefer if someone has a formula or VBA code to do this.

I actually remember seeing it done before, I believe with an offset formula? However, I have unfortunately forgotten where I found the formula