Forum Discussion

Snowback's avatar
Snowback
Copper Contributor
Sep 26, 2019

using tab names to get cells copied

  • 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

Excel Desktop
Show and Tell
tips and tricks

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 27, 2019

    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))

Resources