• 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.

Tab Names as a dynamic varible (which are not links)

C

Collock

New Member
I am trying to make the tab name a variable for formals and am having an issue. Excel reads it as a external link but I am referring to tabs in the same worksheet. Example


=IF(ISNA(HLOOKUP(M$2,Freeport4!$C$2:$BB$12,7,FALSE)),0,HLOOKUP(M$2,Freeport4!$C$2:$BB$12,7,FALSE))


I want something like this.

=IF(ISNA(HLOOKUP(M$2,[$C1]!$C$2:$BB$12,7,FALSE)),0,HLOOKUP(M$2,,[$C1]!$C$2:$BB$12,7,FALSE))


Were [$c1] is the reference to a cell on the current tab that has the name of the tab I want the Hlookup refer to. I tried this and excel wanted to create a link variable I have to set every single time I open the file. It does not refer to the cell. Each tab is the same set-up so the cells I refer to would be the same in each tab. I really don't want to use an array.
 
Maybe


=IF(ISNA(HLOOKUP(M$2,INDIRECT("'"&C1&"!$C$2:$BB$12"),7,FALSE)),0,HLOOKUP(M$2,,INDIRECT("'"&C1&"!$C$2:$BB$12"),7,FALSE))
 
That did not work, but this did. Indirect was the sytex I was looking for thanks.


=IF(ISNA(HLOOKUP(G$2,INDIRECT(CONCATENATE(C1,"!","$C$2:$BB$12")),7,FALSE)),0,HLOOKUP(G$2,INDIRECT(CONCATENATE(c1,"!","$C$2:$BB$12")),7,FALSE))
 
You must log in or register to reply here.
Back
Top