A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Using Concatenate inside a vlookup



 
 
Thread Tools Display Modes
  #1  
Old July 5th 05, 04:02 AM
bmclean
external usenet poster
 
Posts: n/a
Default Using Concatenate inside a vlookup

Hi, I'm putting together a spreadsheet to automate some summarizing from
other spraedsheets I receive every day. The main roadblock I've run into is
that the name of the source sheet changes every day, i.e., "june 24th.xls" &
"june 25th.xls"
So what I thought I'd to is create some lists with the part of the name that
changes and then have a cell concatenate the varialble part of the file name,
as below:

$A$1(list data): june 24 through june 30

Then a formula to incorporate this into a vlookup

=VLOOKUP(D22, CONCATENATE("'", $A$1, "th.xls", "Sheet1'!$A$8:$E$1000"), 5,
FALSE)

The trouble I'm running into is that the concatenate function sticks
quotation marks around what it sticks together, and the vlookup doesn't
understand the table_array reference with quotation marks around it.

the formula created ends up being:
=VLOOKUP(D22, "'C:\data\[june 24th.xls]Sheet1'!$A$8:$E$1000", 5, FALSE)

Is there any way to pull a string from cells and use it in a vlookup? Or a
way of getting rid of the quotation marks around the resulting array_table
value?



Ads
  #2  
Old July 5th 05, 04:07 AM
ScottO
external usenet poster
 
Posts: n/a
Default

If you take a look at the INDIRECT function, I think it will solve your problem.
Rgds,
ScottO

"bmclean" > wrote in message
news | Hi, I'm putting together a spreadsheet to automate some summarizing from
| other spraedsheets I receive every day. The main roadblock I've run into is
| that the name of the source sheet changes every day, i.e., "june 24th.xls" &
| "june 25th.xls"
| So what I thought I'd to is create some lists with the part of the name that
| changes and then have a cell concatenate the varialble part of the file name,
| as below:
|
| $A$1(list data): june 24 through june 30
|
| Then a formula to incorporate this into a vlookup
|
| =VLOOKUP(D22, CONCATENATE("'", $A$1, "th.xls", "Sheet1'!$A$8:$E$1000"), 5,
| FALSE)
|
| The trouble I'm running into is that the concatenate function sticks
| quotation marks around what it sticks together, and the vlookup doesn't
| understand the table_array reference with quotation marks around it.
|
| the formula created ends up being:
| =VLOOKUP(D22, "'C:\data\[june 24th.xls]Sheet1'!$A$8:$E$1000", 5, FALSE)
|
| Is there any way to pull a string from cells and use it in a vlookup? Or a
| way of getting rid of the quotation marks around the resulting array_table
| value?
|
|
|


  #3  
Old July 5th 05, 04:34 AM
Biff
external usenet poster
 
Posts: n/a
Default

Note: Indirect will not work unless the other workbook is open. If the other
workbook is open, it will work temporarily. As soon as the other workbook is
closed and a calulation takes place the formula will once again return #REF!

Biff

"ScottO" > wrote in message
...
> If you take a look at the INDIRECT function, I think it will solve your
> problem.
> Rgds,
> ScottO
>
> "bmclean" > wrote in message
> news > | Hi, I'm putting together a spreadsheet to automate some summarizing from
> | other spraedsheets I receive every day. The main roadblock I've run into
> is
> | that the name of the source sheet changes every day, i.e., "june
> 24th.xls" &
> | "june 25th.xls"
> | So what I thought I'd to is create some lists with the part of the name
> that
> | changes and then have a cell concatenate the varialble part of the file
> name,
> | as below:
> |
> | $A$1(list data): june 24 through june 30
> |
> | Then a formula to incorporate this into a vlookup
> |
> | =VLOOKUP(D22, CONCATENATE("'", $A$1, "th.xls", "Sheet1'!$A$8:$E$1000"),
> 5,
> | FALSE)
> |
> | The trouble I'm running into is that the concatenate function sticks
> | quotation marks around what it sticks together, and the vlookup doesn't
> | understand the table_array reference with quotation marks around it.
> |
> | the formula created ends up being:
> | =VLOOKUP(D22, "'C:\data\[june 24th.xls]Sheet1'!$A$8:$E$1000", 5, FALSE)
> |
> | Is there any way to pull a string from cells and use it in a vlookup? Or
> a
> | way of getting rid of the quotation marks around the resulting
> array_table
> | value?
> |
> |
> |
>
>



  #4  
Old July 5th 05, 09:29 PM
bmclean
external usenet poster
 
Posts: n/a
Default

I found that as well. I'll have to work around the trouble somehow, but I Was
really hoping to be able to do it without opening the workbook.

Thanks to both of you who replied.

"Biff" wrote:

> Note: Indirect will not work unless the other workbook is open. If the other
> workbook is open, it will work temporarily. As soon as the other workbook is
> closed and a calulation takes place the formula will once again return #REF!
>
> Biff
>
> "ScottO" > wrote in message
> ...
> > If you take a look at the INDIRECT function, I think it will solve your
> > problem.
> > Rgds,
> > ScottO
> >
> > "bmclean" > wrote in message
> > news > > | Hi, I'm putting together a spreadsheet to automate some summarizing from
> > | other spraedsheets I receive every day. The main roadblock I've run into
> > is
> > | that the name of the source sheet changes every day, i.e., "june
> > 24th.xls" &
> > | "june 25th.xls"
> > | So what I thought I'd to is create some lists with the part of the name
> > that
> > | changes and then have a cell concatenate the varialble part of the file
> > name,
> > | as below:
> > |
> > | $A$1(list data): june 24 through june 30
> > |
> > | Then a formula to incorporate this into a vlookup
> > |
> > | =VLOOKUP(D22, CONCATENATE("'", $A$1, "th.xls", "Sheet1'!$A$8:$E$1000"),
> > 5,
> > | FALSE)
> > |
> > | The trouble I'm running into is that the concatenate function sticks
> > | quotation marks around what it sticks together, and the vlookup doesn't
> > | understand the table_array reference with quotation marks around it.
> > |
> > | the formula created ends up being:
> > | =VLOOKUP(D22, "'C:\data\[june 24th.xls]Sheet1'!$A$8:$E$1000", 5, FALSE)
> > |
> > | Is there any way to pull a string from cells and use it in a vlookup? Or
> > a
> > | way of getting rid of the quotation marks around the resulting
> > array_table
> > | value?
> > |
> > |
> > |
> >
> >

>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Have Vlookup return a Value of 0 instead of #N/A Mr Mike Excel Worksheet Functions 4 May 25th 05 04:51 PM
Vlookup Using a string within a Vlookup function Excel Worksheet Functions 1 May 17th 05 03:40 AM
Can you use Concatenate with the If function with vlookup in the i simoneaux Excel Worksheet Functions 2 February 7th 05 08:45 PM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 12:09 PM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 10:43 AM


All times are GMT +1. The time now is 08:05 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.