Home

using tab names to get cells copied

%3CLINGO-SUB%20id%3D%22lingo-sub-877702%22%20slang%3D%22en-US%22%3Eusing%20tab%20names%20to%20get%20cells%20copied%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877702%22%20slang%3D%22en-US%22%3E%3CUL%3E%3CLI%3EI%20have%20a%20spreadsheet%20with%20several%20tabs%20from%201-100.%3C%2FLI%3E%3CLI%3EIn%20tab%201%20I%20have%20created%20a%20JE.%3C%2FLI%3E%3CLI%3Etab%202-100%20are%20identical%20templates%20with%20different%20numbers%20in%20key%20cells.%3C%2FLI%3E%3CLI%3EI%20would%20like%20to%20put%20the%20tab%20name%20say%20'2'%20in%20%3CSTRONG%3E%3CU%3EA1%3C%2FU%3E%3C%2FSTRONG%3E%20of%20Tab%201.%3C%2FLI%3E%3CLI%3EI%20would%20then%20like%20to%20see%20certain%20cells%20being%20populated%20with%20key%20cells%20in%20tab%20'2'%3C%2FLI%3E%3CLI%3Eif%20I%20change%20the%20%3CU%3E%3CSTRONG%3EA1%3C%2FSTRONG%3E%3C%2FU%3E%20address%20in%20tab%201%20to%20'50'%20the%20Tab%20one%20will%20then%20be%20populated%20with%20the%20key%26nbsp%3Bcells%20from%20tab%2050.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EHow%20can%20I%20do%20that%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-877702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EShow%20and%20Tell%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878438%22%20slang%3D%22en-US%22%3ERe%3A%20using%20tab%20names%20to%20get%20cells%20copied%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415339%22%20target%3D%22_blank%22%3E%40Snowback%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20share%20a%20sample%20file%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879508%22%20slang%3D%22en-US%22%3ERe%3A%20using%20tab%20names%20to%20get%20cells%20copied%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415339%22%20target%3D%22_blank%22%3E%40Snowback%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20formulas%20you%20use%2C%20but%2C%20if%20without%20VBA%2C%20INDIRECT()%20will%20be%20as%20the%20basis.%20If%2C%20for%20example%2C%20you'd%20like%20to%20pick-up%20the%20value%20in%20cell%20C2%20from%20another%20tab%2C%20it%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDIRECT(%22'%22%26amp%3BA1%26amp%3B%22'!C2%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%2C%20the%20same%20but%20with%20more%20flexibility%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDIRECT(%22'%22%26amp%3BA1%26amp%3B%22'!%22%26amp%3BADDRESS(ROW(C2)%2CCOLUMN(C2)%2C4%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Regular Visitor
  • I have a spreadsheet with several tabs from 1-100.
  • In tab 1 I have created a JE.
  • tab 2-100 are identical templates with different numbers in key cells.
  • I would like to put the tab name say '2' in A1 of Tab 1.
  • I would then like to see certain cells being populated with key cells in tab '2'
  • if I change the A1 address in tab 1 to '50' the Tab one will then be populated with the key cells from tab 50.

How can I do that?

Thanks

2 Replies
Highlighted

@Snowback 

Can you please share a sample file? 

 

Highlighted

@Snowback 

It depends on formulas you use, but, if without VBA, INDIRECT() will be as the basis. If, for example, you'd like to pick-up the value in cell C2 from another tab, it could be

=INDIRECT("'"&A1&"'!C2")

or, the same but with more flexibility

=INDIRECT("'"&A1&"'!"&ADDRESS(ROW(C2),COLUMN(C2),4,1))