SOLVED

Indirect reference

%3CLINGO-SUB%20id%3D%22lingo-sub-2381373%22%20slang%3D%22en-US%22%3EIndirect%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381373%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20file%20.xlxs%20with%20several%20sheets%20-%20each%20sheet%20with%20its%20own%20name%20(of%20course)%20-%20and%20I%20need%20to%20reffer%20to%20these%20sheets%20through%20a%20formula%2C%20but%20I%20need%20to%20use%20the%20content%20of%20a%20cell%20to%20compose%20the%20formula%20and%20access%20data%20on%20these%20sheets.%3CBR%20%2F%3E%3CBR%20%2F%3EIt's%20not%20so%20clear%2C%20so%20I'll%20give%20an%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Main%20sheet%20(called%20%22Panel%22)%20with%20column%20F%20containing%20the%20tickers%20of%20actives%20(stock%20market)%3C%2FP%3E%3CP%3E-%20Several%20other%20sheets%20(one%20for%20each%20ticker%20with%20data%20I%20need%20to%20access)%20named%20after%20the%20ticker%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20have%20in%20sheet%20Panel%20cell%20F10%20the%20ticker%20PETR4%20and%2C%20also%2C%20a%20sheet%20named%20PETR4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20access%20data%20on%20sheet%20%22PETR4%22%20by%20using%20the%20content%20of%20the%20cell%20F10%3F%3CBR%20%2F%3E%3CBR%20%2F%3EToday%20I%20do%20it%20%22manually%22%20by%20typing%20%3DPETR4!C3.%20I'd%20like%20to%20replace%20the%20text%20PETR4%20with%20a%20formula%20to%20get%20the%20value%20PETR4%20from%20the%20cell%20F10.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20and%20I%20hope%20I%20was%20clear%20enough%20(considering%20english%20is%20not%20my%20native%20language...)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2381373%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381406%22%20slang%3D%22en-US%22%3ERe%3A%20Indirect%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381406%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061834%22%20target%3D%22_blank%22%3E%40GiancarloGentile%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20the%20INDIRECT%20function%20for%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDIRECT(F10%26amp%3B%22!C3%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381553%22%20slang%3D%22en-US%22%3ERe%3A%20Indirect%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381553%22%20slang%3D%22en-US%22%3EThank%20you%20Hans.%20I've%20tried%20before%2C%20but%20something%20went%20wrong.%20Now%20it's%20working%20just%20fine!!!%3C%2FLINGO-BODY%3E
New Contributor

I have a file .xlxs with several sheets - each sheet with its own name (of course) - and I need to reffer to these sheets through a formula, but I need to use the content of a cell to compose the formula and access data on these sheets.

It's not so clear, so I'll give an example:

 

- Main sheet (called "Panel") with column F containing the tickers of actives (stock market)

- Several other sheets (one for each ticker with data I need to access) named after the ticker

 

So, I have in sheet Panel cell F10 the ticker PETR4 and, also, a sheet named PETR4.

 

How can I access data on sheet "PETR4" by using the content of the cell F10?

Today I do it "manually" by typing =PETR4!C3. I'd like to replace the text PETR4 with a formula to get the value PETR4 from the cell F10.

 

Thanks, and I hope I was clear enough (considering english is not my native language...)

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

@GiancarloGentile 

You can use the INDIRECT function for this:

 

=INDIRECT(F10&"!C3")

Thank you Hans. I've tried before, but something went wrong. Now it's working just fine!!!