VLOOKUP: Referencing worksheets whose names contain commas

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
0
Utf
1/17/2010 9:37:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
1489 Views

Similar Articles

[PageSpeed] 50

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


"David Aukerman" wrote:

> 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
0
Utf
1/17/2010 9:54:01 PM
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" <DavidAukerman@discussions.microsoft.com> wrote in message 
news:3141AA81-D6A3-4439-9628-1CEFD575A345@microsoft.com...
> 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
>
>
> "David Aukerman" wrote:
>
>> 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 


0
T
1/17/2010 11:35:30 PM
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" <DavidAukerman@discussions.microsoft.com> wrote in message 
news:65708493-2605-452B-A7B0-5AA61254FF04@microsoft.com...
> 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 


0
L
1/18/2010 2:38:46 AM
  Would it not be much more simple to NOT ever use a comma in a sheet
name as a POLICY!?


On Sun, 17 Jan 2010 18:38:46 -0800, "L. Howard Kittle"
<lhkittle@comcast.net> wrote:

>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" <DavidAukerman@discussions.microsoft.com> wrote in message 
>news:65708493-2605-452B-A7B0-5AA61254FF04@microsoft.com...
>> 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 
>
0
FatBytestard
1/18/2010 3:40:53 AM
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" <DavidAukerman@discussions.microsoft.com> wrote in message 
news:65708493-2605-452B-A7B0-5AA61254FF04@microsoft.com...
> 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 


0
L
1/18/2010 4:18:18 AM
>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" <lhkittle@comcast.net> wrote in message 
news:OfbN$U$lKHA.4148@TK2MSFTNGP05.phx.gbl...
> 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" <DavidAukerman@discussions.microsoft.com> wrote in 
> message news:65708493-2605-452B-A7B0-5AA61254FF04@microsoft.com...
>> 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
>
> 


0
T
1/18/2010 4:55:44 AM
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" <biffinpitt@comcast.net> wrote in message 
news:%23zkS%23p$lKHA.3792@TK2MSFTNGP02.phx.gbl...
> >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" <lhkittle@comcast.net> wrote in message 
> news:OfbN$U$lKHA.4148@TK2MSFTNGP05.phx.gbl...
>> 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" <DavidAukerman@discussions.microsoft.com> wrote in 
>> message news:65708493-2605-452B-A7B0-5AA61254FF04@microsoft.com...
>>> 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
>>
>>
>
> 


0
L
1/18/2010 7:45:26 AM
Reply:

Similar Artilces:

Edit Licensed to Name
I need to edit my last name in the Office 2004 registration (Splash screen). How can I do this? Thanks In article <news-AC4BCA.12232514102004@msnews.microsoft.com>, news@biggerhammer.org wrote: > I need to edit my last name in the Office 2004 registration (Splash > screen). How can I do this? > > Thanks Take a look at http://www.mcgimpsey.com/macoffice/office/pid.html ...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Reference another workbook (variable name) inside a formula cell
I use a formula to reference a another workbook cell. The other workbook name can changes after sent to another party and returned ammended. Rather than change the name of the spreadsheet, I would prefer to change the reference in a cell of the first spreadsheet. The formula below activates the lookup if cell c75 is Y. I would like to replace the text [Midwest G1 5000.xls] with a cell containing the name. =IF($C75="Y",SUMIF('[Midwest G1 5000.xls]Summary'!$B:$B,F$4,'[Midwest G1 5000.xls]Summary'!$J:$J),0) I have tried using "&" and direct cell references...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Indirect Row Referencing
Hello All, If I have an excel spreadsheet open with two sheets. In Sheet1 in cell D2 is "Hello". In Sheet2 in cell F4 is the formula "=Sheet1!D2", so it also says "Hello". What I want is to have next to F4 in Sheet2 (in cell E4) a reference to which row in Sheet1 that the formula in F4 is referring to? Ie. Cell E4 should have the number 2 in it. Is this possible using the =INDIRECT function and the =ROW formaula in combination? With thanks Bruce =MATCH(F4,Sheet1!D:D,0) HTH Jason Atlanta, GA >-----Original Message----- >Hello All, > >If I...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

how do I reveerse name order last name, first name to first name, last name
I have a long database, 27k with names that I want to mail to. The list is last name, first name. I want to do a global change to first name, last name. I also want to add "Mr" to it. For example: the list now is Smith, John. I want to change it to Mr. John Smith for the entire list. Assuming there is only one comma in each: ="Mr. "&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))&" "&LEFT(A1,FIND(",",A1)-1) Note that some of the women might be offended.... 27k names, all male? Scott Don Smith wrote: > I hav...

copying from worksheets
I have a file containing a number of different worksheets. The first worksheet is a summary. The other worksheets are named individuals onto a calender. The summary shows what they did each week. I need to copy each individuals detail from the cells in the summary onto their own page, as it is entered. Saves me time copying. Is it possible. For example i dont need Mr Smiths details on Mr Jomnes individual sheet. Mal You might be interested in what the Data > Filter > AutoFilter feature can do for you when applied to the Summary sheet............you can filter so that you see on...

How do I sort worksheet without affecting formula values?
I have two worksheets, one of which has formulas which reference the other. The problem is, I cannot sort the first worksheet and have the formula values in the second worksheet follow it so the values stay the same. I have tried making them absolute references ($A$1) but it doesn't help. I feel your pain. I've ran into the same issue before. The problem is the use of worksheet functions. The solution is DO NOT USE worksheet functions. Use a VBA subroutine instead. Here is an example: With worksheet functions.... A B ...

Change default "Show names from the:" option
Clicking the "To:" button in a new Outlook email message brings up the "Select Names" dialog box. This dialog displays the contents of contact databases such as My Outlook Address Book and Global Address List. Is there a way to change the default database selected in the "Show names from the:" line from the current Global Address List to the contacts in my Outlook Address Book? My version of Outlook connects with an Exchange server to acces mail, contacts, etc. Thanks for any suggestions, Kerry You probably meant to post your Outlook version so we coul...

Recipient's name from contacts
Using gmail.com How can I have the recipient's first and/or last name show on the email they receive? When I send out the same email to 5 or more recipient's, how can I ad their name to the email and subject line in a field like "name", [name] or $name$ and have their first name from my contact list show for each individual contact. Enrique ...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

Help merging worksheets with more than 255 characters... #2
Hi, I am working on a project which requires mail merging an Excel spreadsheet with several fields that contain more than 255 characters. I am using Word 2003 on an XP machine. The results from these merge fields are truncated. I found a workaround for this problem in the online Microsoft Word Support file, WD2000: Field Text Truncated When Merging or Inserting Database. It recommended I use Dynamic Data Exchange (DDE) instead of Open Database Connectivity (ODBC). However, this workaround doesn=92t solve my problem, the fields remain truncated. Are there any other alternatives to resolve thi...

decimal point in worksheet
Y'all: I have a spreadsheet where I want to display a time as "min:sec". My worksheet formula for this is =INT(C2) & ":" & INT((C2-INT(C2))*60) No problem. Except that I want it to display like this: "30:03", not "30:3". How can I do this? e.g. Here is what I get: Time (minutes) Time (minutes:seconds) 30.050 30:3 Here is what I want: Time (minutes) Time (minutes:seconds) 30.050 30:03 I put the spreadsheet out on my website at http://webpages.charter.net/rwpatterson357/temp/test.xls thanks, russ Russ o...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Combine fields containing quotes
I am trying to combine several fields into one. Currently my formula looks something like this =D3&"Some text "&E3&" <br><b>more info: </b>"&F3 This all works just fine, except sometimes I'll need to insert html more advanced than a simple bold or break. How do I escape the quotes in my html so they don't affect the formula? =D3&"Some text "&E3&" <br><b>more info: </b>"&F3&Q3 For now I have just simply added my html in another field and appending it this wa...

File name error ine SMTP message
Hello, I have a pb with the file attach in the mail in CRM 3.0 The file is attach correctly in the pop-up webmail in crm. I send the mail and when the customer or me received the mail (pdf document), ne name of file is modified with this example: Pièce jointe : =_utf-_B_U3dpdGNoIERFTEwgVjQgLSBEZXZpcyAnMDctMDAyNTAnLnBkZgA=__= de Boîte de réception - Microsoft Outlook this pb is one mail of x mail send. ...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

Referencing relative cell
I'm am trying to reference a cell one row up and three to the right to check if it is zero and I get a formula error. Can someone help with this? Thanks. Michael =IF(R[-1]C[+3]>0,AG5*E4,0) Michael You can't mix A1 and R1C1 reference styles. You have to use whichever one is active in the Tools>Options>General tab If you are using A1 style you will need to enter the actual cell reference. If you're not familiar with Absolute and Relative reference styles you can read up on them in the Help files Good Luck Mark Graesse mark_graesser@yahoo.co ----- Michael wr...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...

VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...

Refresh data in Protected worksheet
Does the work sheet protection work properly when refreshing data from access. I have the range I want to refresh Unlocked and unhidden and the rest of the worksheet protected, but when i refresh it tells me the worksheet is protected and I cant refresh. Is there something I'm missing or is this a shortcoming of worksheet protection? KT just hazarding two guesses. are you talking about a macro that does it? if so... are you providing a password to unprotect before you refresh the data? ActiveSheet.Unprotect "password" 'your refresh code would go after the above sta...

Suggest name feature
The feature works while outlook is open, once i close outlook the cached names go away. Create a new profile. -- Russ Valentine [MVP-Outlook] "danny tew" <dannytew@hotmail.com> wrote in message news:61e701c3e0af$630c6080$7d02280a@phx.gbl... > The feature works while outlook is open, once i close > outlook the cached names go away. ...