SOLVED

Can you reference a sheet from a cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-2843517%22%20slang%3D%22en-US%22%3ECan%20you%20reference%20a%20sheet%20from%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843517%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20template%20where%20I%20can%20add%2Fchange%20names%20of%20employees%20on%20a%20summary%20projections%20sheet%20without%20having%20to%20change%20all%20of%20the%20references%20to%20their%20individual%20sheets%20(each%20employee%20has%20their%20own%20sheet%20for%20more%20detailed%20projections).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%2C%20D7%20is%20giving%20the%20ultimate%20value%20that%20I%20want%20by%20returning%20the%20data%20of%20a%20specific%20cell%20of%20a%20specific%20person's%20sheet%20(%3D'Schmo%2C%20Joe'!N11).%20However%2C%20if%20I%20add%20another%20employee%20or%20change%20the%20name%20on%20this%20sheet%20for%20the%20following%20year%2C%20the%20sheet%20name%20changes%20and%20all%20of%20the%20formulas%20have%20to%20be%20changed%20with%20the%20new%20employee's%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20have%20the%20names%20of%20employees%20in%20column%20A%20(ex.%20A7)%2C%20which%20is%20a%20reference%20to%20their%20sheet%20and%20cell%20with%20their%20name%20(A7%3D'Schmo%2C%20Joe'!B1).%20Is%20there%20a%20way%20to%20use%20the%20data%20from%20that%20cell%20(A7)%20to%20create%20an%20equation%20for%20another%20cell%20in%20that%20sheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20example%2C%20I'd%20like%20B7%3D(pull%20name%20from%20A7%20to%20get%20'Schmo%2C%20Joe'!)%20and%20cell%20N11%20from%20the%20Schmo%2C%20Joe%20sheet.%20That%20way%2C%20when%20I%20add%20an%20employee%20or%20change%20the%20name%20on%20the%20sheet%2C%20all%20I'll%20have%20to%20update%20is%20column%20A%20to%20reflect%20the%20new%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20is%20a%20whittled%20down%20version%20of%20what%20I%20need....I%20have%20almost%2030%20employees%20each%20year%2C%20so%20being%20able%20to%20make%20a%20single%20change%20on%20the%20summary%20sheet%20would%20save%20me%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%2Fadvice!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2843517%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-2843613%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20reference%20a%20sheet%20from%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1184801%22%20target%3D%22_blank%22%3E%40alveysj08%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20the%20INDIRECT%20function%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDIRECT(%22'%22%26amp%3BA7%26amp%3B%22'!N11%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I'm trying to create a template where I can add/change names of employees on a summary projections sheet without having to change all of the references to their individual sheets (each employee has their own sheet for more detailed projections).

 

In the attached, D7 is giving the ultimate value that I want by returning the data of a specific cell of a specific person's sheet (='Schmo, Joe'!N11). However, if I add another employee or change the name on this sheet for the following year, the sheet name changes and all of the formulas have to be changed with the new employee's name.

 

I currently have the names of employees in column A (ex. A7), which is a reference to their sheet and cell with their name (A7='Schmo, Joe'!B1). Is there a way to use the data from that cell (A7) to create an equation for another cell in that sheet?

 

So for example, I'd like B7=(pull name from A7 to get 'Schmo, Joe'!) and cell N11 from the Schmo, Joe sheet. That way, when I add an employee or change the name on the sheet, all I'll have to update is column A to reflect the new name.

 

The attached is a whittled down version of what I need....I have almost 30 employees each year, so being able to make a single change on the summary sheet would save me hours.

 

Thanks for any help/advice!

2 Replies
best response confirmed by alveysj08 (New Contributor)
Solution

@alveysj08 

You can use the INDIRECT function:

 

=INDIRECT("'"&A7&"'!N11")

@Hans Vogelaar 

Yes!! That's it! Thanks so much! I knew I was missing something - I tried the indirect function, but was missing the extra quotes around the entire bit. Thank you!!