• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use sheet's name in a cell and on a formula

P

Pampos

Guest
Hello.

I have 2 sheets: First Last

In sheet First I insert in A1 the word Last which is the name of the other sheet.

In A2 I can have ='Last'!A2 and then I can have the A2 cell of sheet Last. But I want to use the word Last I have written.

='Last'!A2 The word Last must be replaced by the value of A1 (sheet First) which, in that case, is the name of second sheet.

Is that possible? Thanks.
 
You can use INDIRECT function as follows..

  • I have created a worksheet named Last and in cell A1 of this worksheet.. I have a value named ABC in cell A1
  • In second worksheet of the same file, I would like to fetch the value of the worksheet based on the worksheet name entered in cell G1..
  • The formula I have entered is as follows:
  • Code: 
    =INDIRECT(G1&"!$A$1",TRUE)
  • In the above formula, the worksheet name and the cell address are concatinated using & symbol and the 2nd paramter for Indirect function is whether the cell reference is specified as R1C1 method or convention cell addressing where column name and row number are available in a cell address.
I hope this helps!!
 
You must log in or register to reply here.
Back
Top