Search code examples
excelcopycellformula

Copy cell info with variable

I need to pull data from a cell on another sheet (Sheet1), using a variable on the Current sheet (Sheet2, 3, 4 etc)

..ie I have a formula that takes the current sheet name and returns a value in a cell (A1 on current sheet),

I then need to go to a specific Column on another sheet but using the Row as derived from the sheet name something along the lines of:- 

=Sheet1!H(Sheet2!A1)

where H is the column and (Sheet2!A1) is the row, and A1 holds the value of the current sheet name

Solution

  • Take a look at the Excel INDIRECT function, it might do what you're looking for. It takes a string (which you can build) and used the value of the string to make a cell reference.

    http://office.microsoft.com/en-001/excel-help/indirect-HP005209139.aspx