Custom Function Causes Excel to + 1 every time it saves and won't auto calculate anymore

[DDRA Steampunk]

What am I doing wrong?

I'm using a custom function

Function NxtShtNm() As String
    Application.Volatile
    NxtShtNm = ActiveWorkbook.Sheets(ActiveSheet.Index + 1).Name
End Function

That makes it so I can create the formula in Cell P1 to return the name of the event on the next page

=IFERROR(INDIRECT("'"&NxtShtNm()&"'!"&"b$1"),0)

and in Q1 return the day of event count from the next sheet (which is the previous show date).

=IFERROR(INDIRECT("'"&NxtShtNm()&"'!"&"e$1"),0)

I can then use them to pull up and compare the current and next sheet names and get

=IF(B1<>P1,1,Q1+1)

B1 is the current sheet name derived by

=(MID(CELL("Filename",A1),SEARCH("]",CELL("Filename",A1),1)+12,32))

It must be derived this way because each tab has the date plus the event name. The formula therefore automatically checks if the event names match, and if so takes Q1 and adds 1 to that number, if not it sets the current day count to 1.

The formula works... sort of. First it gave me a circular ref warning and refused to update, so I allowed iterative calculations, then it started doing this weird thing where every time I save the file it adds 1 to whatever was in Q1 previously and sets P1 to whatever is on the first sheet in the sequence. When I go through all of the pages starting at the last page and hit "calculate page" it fixes all of the numbers and page references to the correct result until I try to save the file where the loop starts over.

I think it's something wrong in the structure of the function, but I can't seem to figure it out. I tried saving the code on individual pages but that didn't seem to make any difference.

Fixes or other ways of doing this are both welcome

 

[DDRA Steampunk]

Thank goodness anyone else using my Workbook will only have to copy and paste CSV data into each page! I created a complex workaround by flipping my Index from Ascending to Descending to create the proper date order, then used Offset and Match to pull from my Sales Page (that uses the Index List and Indirect functions to pull all the relevant cells from all 100 Date pages). Since I'm the only user that ever sees the Index Page it won't matter that it is now "upside down". Some very ugly formulas now clutter up the pages and I ended up with 3 hidden cells. It's a crazy way of doing it, and I'm pretty sure it could be a lot simpler, but without being able to get a PrevSheet! to update on all 100, or the custom function to add up correctly, this works well enough. It only took me 6 hours to learn (I'm crying on the inside).

A big thank you to all the various Posters here and elsewhere for all the bits and pieces!

A1 Hidden= Page Name (for example 2018-01-01 Event Name) =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

A2 Hidden= Day Count of Next Page =IFERROR(OFFSET(INDEX('Event Sales'!$R$3:$R$102,MATCH($A$1,'Event Sales'!$R$3:$R$102,0)),-1,-14,1,1),0)

B1 = Name of Current Event =(MID(CELL("Filename",A1),SEARCH("]",CELL("Filename",A1),1)+12,32))

P2 Hidden= Event Name of Next Page =IFERROR(OFFSET(INDEX('Event Sales'!$R$3:$R$102,MATCH($A$1,'Event Sales'!$R$3:$R$102,0)),-1,-17,1,1),0)

E1 = What Day Count should this page be? =IF(B1<>P1,1,((IFERROR(OFFSET(INDEX('Event Sales'!$R$3:$R$102,MATCH($A$1,'Event Sales'!$R$3:$R$102,0)),-1,-14,1,1),0)))+1)