SOLVED

Extracting a text from one cell to reference another sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3112519%22%20slang%3D%22en-US%22%3EExtracting%20a%20text%20from%20one%20cell%20to%20reference%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3112519%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20I'm%20trying%20to%20extract%20information%20from%20a%20different%20sheet%20within%20the%20same%20workbook.%20When%20looking%20at%20the%20attached%20example%20in%20the%20images%20below%2C%20the%20simple%20equation%20I%20would%20use%20is...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCleveland!A1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bwhich%20spits%20out%20%22Browns%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mrensel27_0-1643911508452.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345062iF865495BFADEACCE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mrensel27_0-1643911508452.png%22%20alt%3D%22mrensel27_0-1643911508452.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20with%20what%20I'm%20doing%20in%20a%20personal%20work%20related%20excel%20sheet%20(I'd%20share%20it%20but%20not%20allowed%20to)%2C%20I%20need%20to%20extract%20information%20in%20a%20different%20sheet%20(like%20the%20example%20above)%20but%20want%20to%20specify%20the%20sheet%20name%20in%20a%20cell%20itself.%20Wording%20if%20funky%20but%20for%20example%2C%20instead%20of%20manually%20typing%20%22Cleveland%22%20into%20the%20equation%2C%20%3DCleveland!A1%2C%20I%20want%20to%20reference%20the%20cell%20that%20says%20the%20sheet%20name%20of%20%22Cleveland%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20E3%20%3D%20Cleveland%3C%2FP%3E%3CP%3ECell%20E4%20%3D%20'E3'!A1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20does%20not%20work.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mrensel27_1-1643911540755.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345063i71F0DF8A5330FA3E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mrensel27_1-1643911540755.png%22%20alt%3D%22mrensel27_1-1643911540755.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20way%20to%20modify%20that%20equation%20a%20bit%20to%20make%20it%20work%3F%20Would%20save%20a%20lot%20of%20time%20in%20the%20sheet%20I'm%20currently%20working%20on%20instead%20of%20manually%20typing%20each%20equation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3112519%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-3112628%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20a%20text%20from%20one%20cell%20to%20reference%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3112628%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296379%22%20target%3D%22_blank%22%3E%40mrensel27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDIRECT(%22'%22%26amp%3BE3%26amp%3B%22'!%22%26amp%3BE2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20with%20this%20formula.%20You%20can%20dynamically%20enter%20the%20sheetname%20in%20E3%20and%20the%20cell%20in%20E2.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello! I'm trying to extract information from a different sheet within the same workbook. When looking at the attached example in the images below, the simple equation I would use is...

 

=Cleveland!A1         which spits out "Browns"

mrensel27_0-1643911508452.png

 

But with what I'm doing in a personal work related excel sheet (I'd share it but not allowed to), I need to extract information in a different sheet (like the example above) but want to specify the sheet name in a cell itself. Wording if funky but for example, instead of manually typing "Cleveland" into the equation, =Cleveland!A1, I want to reference the cell that says the sheet name of "Cleveland". 

 

Cell E3 = Cleveland

Cell E4 = 'E3'!A1        does not work.

mrensel27_1-1643911540755.png

 

Any way to modify that equation a bit to make it work? Would save a lot of time in the sheet I'm currently working on instead of manually typing each equation.

 

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

@mrensel27 

=INDIRECT("'"&E3&"'!"&E2)

 

Maybe with this formula. You can dynamically enter the sheetname in E3 and the cell in E2.

This worked perfectly for what I needed to do! Thank you so much for the help!