Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bmclean
 
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?



  #2   Report Post  
ScottO
 
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
...
| 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   Report Post  
Biff
 
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
...
| 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   Report Post  
bmclean
 
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
...
| 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?
|
|
|





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 09:45 PM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 11:43 AM


All times are GMT +1. The time now is 09:50 AM.

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

About Us

"It's about Microsoft Excel"