Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-01-2024, 06:56 AM
ToLoC999 ToLoC999 is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 11 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2019
Novice
Changing the worksheet name in multiple formulas (possibly using INDIRECT)
 
Join Date: Nov 2024
Posts: 3
ToLoC999 is on a distinguished road
Default Changing the worksheet name in multiple formulas (possibly using INDIRECT)

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
Attached Images
File Type: png Screenshot 2024-10-31 173415.png (28.9 KB, 33 views)
Reply With Quote
  #2  
Old 11-02-2024, 12:33 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 10 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Hi and welcome. You are right about the INDIRECT function. Have a look at this link for more details.
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 11-02-2024, 05:45 AM
p45cal's Avatar
p45cal p45cal is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 10 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2021
Expert
 
Join Date: Apr 2014
Posts: 947
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default

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.
Reply With Quote
  #4  
Old 11-14-2024, 01:09 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 10 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

@Toloc. Has this solved your problem?
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 11-24-2024, 06:14 AM
ToLoC999 ToLoC999 is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 11 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2019
Novice
Changing the worksheet name in multiple formulas (possibly using INDIRECT)
 
Join Date: Nov 2024
Posts: 3
ToLoC999 is on a distinguished road
Default

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
Attached Images
File Type: png Screenshot 2024-11-24 131204.png (17.3 KB, 22 views)
Reply With Quote
  #6  
Old 11-24-2024, 07:25 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 10 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Glad you solved it ( attaching a sheet instead of an image would have helped).
Please mark the thread as solved '( see thread tools). Thx
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 11-28-2024, 02:30 AM
ToLoC999 ToLoC999 is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 11 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2019
Novice
Changing the worksheet name in multiple formulas (possibly using INDIRECT)
 
Join Date: Nov 2024
Posts: 3
ToLoC999 is on a distinguished road
Default

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.
Attached Files
File Type: xlsx Word Scoring 2024 Season.xlsx (106.3 KB, 6 views)
Reply With Quote
  #8  
Old 11-29-2024, 01:27 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 10 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

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
Attached Files
File Type: xlsx Word Scoring 2024 Season(1).xlsx (107.7 KB, 3 views)
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 01-05-2025, 08:42 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 10 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Hi. As the thread isn't mark as solved, did my last post help?
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #10  
Old 01-05-2025, 01:38 PM
ArviLaanemets ArviLaanemets is online now Changing the worksheet name in multiple formulas (possibly using INDIRECT) Windows 8 Changing the worksheet name in multiple formulas (possibly using INDIRECT) Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

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!).
Attached Files
File Type: xlsx GameScores.xlsx (75.5 KB, 3 views)
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to run worksheet multiple times changing a single input? jigadoo Excel 4 06-08-2022 12:37 PM
Need a formula (possibly something like VLOOKUP) that will return multiple results. SilverUnicorn79 Excel 8 08-16-2016 01:34 PM
Excel 2010 changing external link formulas automatically on Webdav mapped drive dirkswart Excel 2 10-07-2013 12:44 PM
Copy Worksheet and preserve formulas silverspr Excel 4 02-21-2013 07:51 AM
Changing the worksheet name in multiple formulas (possibly using INDIRECT) Protecting the formatting and formulas in a worksheet furface00 Excel 5 02-27-2011 02:22 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:53 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft