Find and Replace Reference Worksheet Name

[multifidus]

I am working on a fun project that will automatically update all of the stats for NBA players. As a part of this I have three sheets for each team (total stats, home stats only, away stats only). I built out the first one without any problem, but if took a lot of time since I am referencing a separate workbook that is running a web query to pull the stats data from basketball reference.

Instead of hardcoding this again for the next team, I am attempting to simply use find and replace to change the name of the reference sheet.

Example:

=AVERAGE([BostonCelticsPlayers.xlsx]Tatum_Jayson!$J:$J)

I want to change the "BostonCelticsPlayers.xlsx" to reference the workbook I have for the Atlanta Hawks "AtlantaHawksPlayers.xlsx". When I try to use find and replace it says "Microsoft Excel cannot find matching data to replace"

I have tried numerous iterations of typing the the text (using quotes, not using quotes, just changing BostonCeltics to AtlantaHawks, the full file name, etc.).

Any suggestions?

 

[zero269]

I want to change the "BostonCelticsPlayers.xlsx" to reference the workbook I have for the Atlanta Hawks "AtlantaHawksPlayers.xlsx". When I try to use find and replace it says "Microsoft Excel cannot find matching data to replace"

Check your Find & Replace "Look in" selection to see if its Values or Formulas.

When I tested this, I get the following error when Values is selected:

Switching to Formulas should be able to find your string within that formula you're looking for:

​

Leaving it on Formulas will still find Values, so no need to change it back:

​

 

[zero269]

Instead of changing the filenames every time you want to update your stats, you could consider keeping them in a Table or Named Range on a different Sheet and just reference them for each relevant column in your master workbook.
... or use a drop-down list...?

 

[multifidus]

Thank you for taking the time to respond! I have a web query for each team on separate spreadsheets (Atlanta Hawks as a team have their own workbook, and each player has their own sheet with their stats auto-updating).

I want to have one master workbook that has three sheets for each team (total stats, away game stats, home game stats), which will pull from the team specific workbook and the player specific sheet.

As I am building out the master workbook I have to change my formula each time to reference the specific team workbook and player sheet. This is why I am having issue with find and replace.

The only work around I have found to do is to make the change myself for the first cell of a player (change the file name and the sheet reference), then copy and paste that across all stat categories for that first player. Then I copy and paste that player's stats down the line for all other players on the team. I do this because it will then have the updated file name. Then I go row by row and find/replace the sheet reference and that seems to work. Though it has been somewhat time consuming.