Referencing sheet names

%3CLINGO-SUB%20id%3D%22lingo-sub-2786680%22%20slang%3D%22en-US%22%3EReferencing%20sheet%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2786680%22%20slang%3D%22en-US%22%3EI'm%20creating%20a%20workbook%20to%20keep%20track%20of%20football%20statistics.%20I%20have%20a%20sheet%20named%20for%20each%20team%20with%20their%20schedule%20at%20game%20stats%20on%20each%20one.%20Is%20there%20a%20way%20to%20use%20a%20cell's%20text%2Fcontents%20to%20refer%20to%20different%20pages%3F%20For%20example%2C%20if%20a%20cell%20says%20%22Miami%22%2C%20it%20would%20automatically%20search%20the%20page%20which%20is%20also%20named%20%22Miami%22.%20However%2C%20the%20next%20season%20they%20might%20be%20playing%20New%20York%20in%20week%201%2C%20in%20which%20case%20I%20would%20want%20it%20to%20search%20%22New%20York%22%20instead%20of%20%22Miami%22.%20I%20know%20how%20to%20do%20this%20the%20long%20way%20by%20changing%20the%20formula%2C%20but%20since%20schedules%20change%20every%20season%2C%20I'm%20looking%20for%20a%20%22one%20size%20fits%20all%22%20solution.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2786680%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2786690%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20sheet%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2786690%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1167058%22%20target%3D%22_blank%22%3E%40WiiLoveZelda1987%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20the%20INDIRECT%20function%20for%20this.%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20enter%20the%20name%20of%20a%20team%20in%20cell%20B3%2C%20and%20you%20want%20to%20return%20the%20value%20of%20cell%20D20%20on%20the%20sheet%20whose%20name%20is%20in%20B3%2C%20you%20can%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDIRECT(%22'%22%26amp%3BB3%26amp%3B%22'!D20%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWhen%20you%20change%20the%20team%20name%20in%20B3%2C%20the%20formula%20will%20automatically%20look%20at%20the%20sheet%20for%20the%20new%20team.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2786712%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20sheet%20names%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2786712%22%20slang%3D%22en-US%22%3EIt%20worked!%20Thanks%20a%20bunch!%20You%20have%20no%20idea%20how%20much%20time%20this%20was%20save%20me%20in%20the%20future.%20I%20really%20appreciate%20it!%3C%2FLINGO-BODY%3E
New Contributor
I'm creating a workbook to keep track of football statistics. I have a sheet named for each team with their schedule at game stats on each one. Is there a way to use a cell's text/contents to refer to different pages? For example, if a cell says "Miami", it would automatically search the page which is also named "Miami". However, the next season they might be playing New York in week 1, in which case I would want it to search "New York" instead of "Miami". I know how to do this the long way by changing the formula, but since schedules change every season, I'm looking for a "one size fits all" solution.
2 Replies

@WiiLoveZelda1987 

You can use the INDIRECT function for this.

Let's say you enter the name of a team in cell B3, and you want to return the value of cell D20 on the sheet whose name is in B3, you can use

=INDIRECT("'"&B3&"'!D20")

When you change the team name in B3, the formula will automatically look at the sheet for the new team.

It worked! Thanks a bunch! You have no idea how much time this was save me in the future. I really appreciate it!