Changing the worksheet name in multiple formulas (possibly using INDIRECT)

[ToLoC999]

Good afternoon everybody and many thanks in advance for any possible assistance. I have done multiple searches for a solution without success, although INDIRECT in the correct syntax could be a possibility

I only really know basic Excel so please do not laugh at this question

A friend and me play a few 'wordle type games' every day and I collate all the score onto a weekly worksheet. I currently have to change the name of the latest worksheet onto 8 formulas on the running total worksheet every week to keep the scores current.

How would I be able to just type the current playing week into a cell such as the one shown with red writing and get it to automatically go into the formula area marked?
Any help would be greatly appreciated and sorry for the stupid question, I have tried and tried many possible solutions and it always gives me an error.

I have attached a snapshot of the Running Totals worksheet for reference.

Many thanks

[Pecoflyer]

Hi and welcome. You are right about the INDIRECT function. Have a look at this link for more details.

[p45cal]

If you add a sheet, call it say, Last, and then when you add new weekly sheets make sure it's between Last and the the first sheet you're summing, your formula can then remain =SUM('01_01:Last'!I12)
Make sure you have nothing on sheet Last in cell I12



This difficulty stems from the source data for your summary sheet all over the place.
I'd suggest you put your games results in a single table and include a date column. You'll then easily be able to get a whole host of summary details from that one table, and you won't have to add new sheets.


Let's have a peep of one of your weekly sheets, or better, attach a workbook here; I'll rearrange the data and give you a new Master/Summary sheet. Do it sooner rather than later because I won't be near a machine for a couple of weeks.

[Pecoflyer]

@Toloc. Has this solved your problem?

[ToLoC999]

Hiya Pecoflyer,

Whatever I tried I could not get the syntax correct for a INDIRECT statement and therefore came up with a cunning workaround.

I created another formula in the worksheets (B13) that had the formula =IF(B3>0,1). Refer to the attached screenshot but this basically changed its value from FALSE to 1 as soon as the first score went in. From here I was able to write into the formulas to only include the worksheet if B13 = 1

[Pecoflyer]

Glad you solved it ( attaching a sheet instead of an image would have helped).
Please mark the thread as solved '( see thread tools). Thx

[ToLoC999]

Hi Pecoflyer,

I have attached the whole worksheet for you to have a look at. Please do not laugh at the way I have done some of the formulas, I am only a basic user of Excel.

You will possibly know of some improved ways of me resolving the results in the running totals worksheet. The only two I have to manually adjust each week are the lowest score results in C16 and C18.

I will mark this later as solved.

Many thanks for your help so far

Tony.

[Pecoflyer]

The trick is to add an empty sheet before all the others and call it something like Start ( or whatever) and another called Stop ( or whatever) as last of the data sheets ( before "running Total).

Whenever there is a new data sheet be sure to insert it between those two .
I have changed the formulas in the Running Total sheet ( yellow highlight) taking this into account.Those sheets must remain empty.
I leave it up to you to change the rest.
A couple of tips :
DO NOT use merged cells, they are nothing but trouble. Use " Center across selection" instead
You do not need to reference something like E13:E13. E13 on its own is sufficient

[Pecoflyer]

Hi. As the thread isn't mark as solved, did my last post help?

[ArviLaanemets]

An example how I'd do something like this!

All game results are entered on single sheet (Scores).

I used ISO week definition, and it looks like this is what you used too mostly. But it looks like you messed up this sometimes - p.e. sheets 08_01, and 09_01, where the week start is Sunday! Are tables there starting at 08.0.2024 and 15.01.2024 (both Sundays), or 09.01.2024 and 16.01.2024 (both Mondays, as the row header in table says)?

There are some registry sheets (Players, Rounds, Calendar, Lists), which are used as sources for Data Validation Lists, and are needed when creating various report tables or calculations.

On List sheet are defined the period lists for selecting periods for various reports. Based on those lists, dynamic ranges are defined, which exclude periods later then current one.

On Scores sheet, the table contains some calculated columns, which are used in report calculations. You can hide them, and better do this so you don't accidentally mess them up!

I used Defined Tables here, as I think they have some useful advantages (all formulas and formats are expanded to new rows automatically, unless you have several different formulas or formats in same column, and the formulas are easier to read). But there are also some limitations, and one of them is, that there can't be several columns with same header (this is the reason why some column headers in my report are different from ones you used in your tables).

I created a single example of report sheet, based on your design of weekly tables. You select the week number, and all game results for selected week are displayed. Based on this example, you can learn to create any other reports you need (p.e. yearly or monthly reports).


You can use this app for several years without any design changes (i.e. only registering new game results). In case the number of entries grows too much (so the workbook slows down), you can save the workbook as archive one (deleting from saved archive some of last years data), and after that deleting from working version score data for archived years (NB! Delete sheet rows, not table rows!).