VLOOKUP: Referencing worksheets whose names contain commas

[David Aukerman]

Hello,

How do I make a VLOOKUP formula reference a different worksheet that has a
comma in its name? For instance, in Sheet1 I want to refer to a sheet named
"Last, First" in this way:

=VLOOKUP(A1, 'Last, First'!$A$1:$M$100, 13, TRUE)

But the comma in sheet "Last, First" seems to make the VLOOKUP function
advance the argument too early. Is there an easy fix for this?

Thanks,
--David Aukerman

[David Aukerman]

Update:

It seems that the function as I wrote it *evaluates* correctly, but while
I'm typing it in, the tooltip (that helps me determine which argument I'm
entering) advances too soon when the comma in the sheet name is entered.
Maybe this is a bug?

--David Aukerman

[T. Valko]

About the only thing you can do to stop that from happening is to not use
commas in sheet names.

--
Biff
Microsoft Excel MVP

"David Aukerman" <DavidA...@discussions.microsoft.com> wrote in message
news:3141AA81-D6A3-4439...@microsoft.com...

[L. Howard Kittle]

Does this work for you?

=VLOOKUP(F1,'[Book10]Last, First'!$A$1:$B$5,2,0)

The third and fourth argument are not consistent with your lookup but I
believe you can change to 13 and 1 (or TRUE). Not tested

HTH
Regards,
Howard

"David Aukerman" <DavidA...@discussions.microsoft.com> wrote in message

news:65708493-2605-452B...@microsoft.com...

[FatBytestard]

Would it not be much more simple to NOT ever use a comma in a sheet
name as a POLICY!?

[L. Howard Kittle]

Sorry, I gave a solution that had a workSHEET named "Last, First" and was in
workBOOK 10.

I saved Book 10 as Last, First, with the worksheet named Last, First in it
and is refrenced in the formula. Did not have a problem with a comma in the
name of the workbook. Using Excel 2002 SP3.

Am I missing something here?

Perhaps this will work as it did in my test where the Workbook is named
"Last, First". Again change the third and fourth arguments to suit.

=VLOOKUP(F1,'[Last, First.xls]Last, First'!$A$1:$B$5,2,0)

HTH
Regards,
Howard

"David Aukerman" <DavidA...@discussions.microsoft.com> wrote in message
news:65708493-2605-452B...@microsoft.com...

[T. Valko]

>Am I missing something here?

I think so...

You need Excel 2002 or higher with Function ToolTips enabled to see what the
OP is talking about.

Start typing this formula:

=VLOOKUP(10,

As soon as you type the comma the tooltip argument advances to the next
argument which would be the table_array argument.

Since their table array is on a sheet named First, Last we continue typing:

=VLOOKUP(10,'First,

As soon as you tpe the comma in the sheet name the tooltip argument advances
to the next argument which would be the col_index_num. However, you haven't
finshed typing in the full sheet name for the table_argument yet. So, you
might get confused about which argument you need to type in.

The only solutions I can think of are:

1. don't use sheet names that contain commas

--
Biff
Microsoft Excel MVP

"L. Howard Kittle" <lhki...@comcast.net> wrote in message
news:OfbN$U$lKHA...@TK2MSFTNGP05.phx.gbl...

[L. Howard Kittle]

Hmmm, I see what you mean when you type in the formula. Does just what you
indicate.

However, if I type in =VLOOKUP(F1, and from here click on the workbook
named First, Last and select A1:B5 on worksheet named First, Last and then
add the comma it accepts that and then type ,2,0) it accepts the formula
and returns the proper value in my test.

=VLOOKUP(F1,'[Last, First.xls]Last, First'!$A$1:$B$5,2,0)

Then if I click in the formula bar with the above formula and arrow across
the formula the tool tip indeed seems to get out of sequence and is confused
by the commas.

Selecting instead of typing works in my test.

Howard

"T. Valko" <biffi...@comcast.net> wrote in message
news:%23zkS%23p$lKHA...@TK2MSFTNGP02.phx.gbl...