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

Referencing sheet names in a VLOOKUP

In the attached file, in cell D3 of the first tab, I have a VLOOKUP on the value in C3. The table array is on the 2nd tab, columns C and D.

However, to refer to the 2nd tab, I don't want to click on the tab. Instead I have named the 2nd tab as the same name as the value in C3, so that I can reference the 2nd tab by simply referencing C3.

So I have to reference C3 and use the TEXT function to convert the slashes to a period. This has to be surrounded by a single quote at the beginning and the end. Also at the end, an exclamation mark is needed. This will give the sheet name. Then after that, I can reference columns C:D.

But after I hit enter, for some reason, a second TEXT function automatically gets populated.

I also tried the INDIRECT function along with using the & and various combinations of double quotes surrounding a single quote at the beginning and the end of the sheet reference. Nothing is working.

Any suggestions?
 

Attachments

  • Chandoo - Reference Tab name.xlsx
    10.1 KB · Views: 3
Hi:

This is what I did , I have to use a helper column to get the desired results...
 

Attachments

  • Chandoo - Reference Tab name.xlsx
    9.2 KB · Views: 5
Hi ,

See the attached file.

The problem was using the brackets "[" and "]" ; these are used to enclose the workbook name , if you are using an external reference i.e. a reference to another workbook ; for a reference within the same workbook , you do not need to use these.

Narayan
 

Attachments

  • Chandoo - Reference Tab name.xlsx
    9.8 KB · Views: 6
Hi ,

See the attached file.

The problem was using the brackets "[" and "]" ; these are used to enclose the workbook name , if you are using an external reference i.e. a reference to another workbook ; for a reference within the same workbook , you do not need to use these.

Narayan

I think I see what I did wrong. I tried the INDIRECT function but I was doing it up to the !' part. I did not include columns C:D.

Thank you.
 
Back
Top