Vlookup Problem

  • Thread starter Thread starter Jason Lepack
  • Start date Start date
J

Jason Lepack

I'm trying to perform this VLookup

=VLOOKUP(A2,[Updated Jan12 Daily D.xls]Daily_D___Bob!$1:$65536,15,
FALSE)

Excel doesn't like the workbook having spaces in the name it keeps
trying to correct it to:

=VLOOKUP(A2,[UpdatedJan12DailyD.xls]Daily_D___Bob!1:65536,15,FALSE)

but that isn't a valid file name.

What can I do instead to make this work?

Cheers,
Jason Lepack
 
I don't like to build the formula myself. I let excel do it.

With both workbooks open, I start typing the formula:

=vlookup(a2,
Then I click on window to activate the other workbook. Then I select the
worksheet and finally the range.

Then I take back the typing and finish with
,15, false)

====
As an aside, you may want to limit your range to just what you need. You'll
find that it'll calculate quicker and less often.



Jason said:
I'm trying to perform this VLookup

=VLOOKUP(A2,[Updated Jan12 Daily D.xls]Daily_D___Bob!$1:$65536,15,
FALSE)

Excel doesn't like the workbook having spaces in the name it keeps
trying to correct it to:

=VLOOKUP(A2,[UpdatedJan12DailyD.xls]Daily_D___Bob!1:65536,15,FALSE)

but that isn't a valid file name.

What can I do instead to make this work?

Cheers,
Jason Lepack
Click to expand...
 
This user has a database that he daily downloads data from a source and
loads it into the database. He then exports 5 queries all into a
single workbook for the day. He recreates this workbook every day but
he has comments for specific parts that he needs to copy over from the
day before, but they aren't always in the same place, or even included
in the next day, thus the vlookup.

I've created macros to format his daily input and his daily output.
The macro asks him to select yesterdays file and then it creates the
vlookup and pastes it in to lookup the data.

He knows that the easy solution is to remove the spaces, but if at all
possible we'd like to workaround some other way.

Cheers,
Jason Lepack

Dave said:
I don't like to build the formula myself. I let excel do it.

With both workbooks open, I start typing the formula:

=vlookup(a2,
Then I click on window to activate the other workbook. Then I select the
worksheet and finally the range.

Then I take back the typing and finish with
,15, false)

====
As an aside, you may want to limit your range to just what you need. You'll
find that it'll calculate quicker and less often.



Jason said:
I'm trying to perform this VLookup

=VLOOKUP(A2,[Updated Jan12 Daily D.xls]Daily_D___Bob!$1:$65536,15,
FALSE)

Excel doesn't like the workbook having spaces in the name it keeps
trying to correct it to:

=VLOOKUP(A2,[UpdatedJan12DailyD.xls]Daily_D___Bob!1:65536,15,FALSE)

but that isn't a valid file name.

What can I do instead to make this work?

Cheers,
Jason Lepack
Click to expand...
Click to expand...
 
I figured it out.

=VLOOKUP(A2,'[Updated Jan12 Daily D.xls]Daily_D___Bob'!$1:$65536,15,
FALSE)

I just needed to add in the ' ' around the file name.

Cheers,
Jason Lepack

Jason said:
This user has a database that he daily downloads data from a source and
loads it into the database. He then exports 5 queries all into a
single workbook for the day. He recreates this workbook every day but
he has comments for specific parts that he needs to copy over from the
day before, but they aren't always in the same place, or even included
in the next day, thus the vlookup.

I've created macros to format his daily input and his daily output.
The macro asks him to select yesterdays file and then it creates the
vlookup and pastes it in to lookup the data.

He knows that the easy solution is to remove the spaces, but if at all
possible we'd like to workaround some other way.

Cheers,
Jason Lepack

Dave said:
I don't like to build the formula myself. I let excel do it.

With both workbooks open, I start typing the formula:

=vlookup(a2,
Then I click on window to activate the other workbook. Then I select the
worksheet and finally the range.

Then I take back the typing and finish with
,15, false)

====
As an aside, you may want to limit your range to just what you need. You'll
find that it'll calculate quicker and less often.



Jason said:
I'm trying to perform this VLookup

=VLOOKUP(A2,[Updated Jan12 Daily D.xls]Daily_D___Bob!$1:$65536,15,
FALSE)

Excel doesn't like the workbook having spaces in the name it keeps
trying to correct it to:

=VLOOKUP(A2,[UpdatedJan12DailyD.xls]Daily_D___Bob!1:65536,15,FALSE)

but that isn't a valid file name.

What can I do instead to make this work?

Cheers,
Jason Lepack
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure if you found it via trial and error--but if you had let excel build
the formula, you would have seen the correct syntax right away.

Jason said:
I figured it out.

=VLOOKUP(A2,'[Updated Jan12 Daily D.xls]Daily_D___Bob'!$1:$65536,15,
FALSE)

I just needed to add in the ' ' around the file name.

Cheers,
Jason Lepack

Jason said:
This user has a database that he daily downloads data from a source and
loads it into the database. He then exports 5 queries all into a
single workbook for the day. He recreates this workbook every day but
he has comments for specific parts that he needs to copy over from the
day before, but they aren't always in the same place, or even included
in the next day, thus the vlookup.

I've created macros to format his daily input and his daily output.
The macro asks him to select yesterdays file and then it creates the
vlookup and pastes it in to lookup the data.

He knows that the easy solution is to remove the spaces, but if at all
possible we'd like to workaround some other way.

Cheers,
Jason Lepack

Dave said:
I don't like to build the formula myself. I let excel do it.

With both workbooks open, I start typing the formula:

=vlookup(a2,
Then I click on window to activate the other workbook. Then I select the
worksheet and finally the range.

Then I take back the typing and finish with
,15, false)

====
As an aside, you may want to limit your range to just what you need. You'll
find that it'll calculate quicker and less often.



Jason Lepack wrote:

I'm trying to perform this VLookup

=VLOOKUP(A2,[Updated Jan12 Daily D.xls]Daily_D___Bob!$1:$65536,15,
FALSE)

Excel doesn't like the workbook having spaces in the name it keeps
trying to correct it to:

=VLOOKUP(A2,[UpdatedJan12DailyD.xls]Daily_D___Bob!1:65536,15,FALSE)

but that isn't a valid file name.

What can I do instead to make this work?

Cheers,
Jason Lepack
Click to expand...
Click to expand...
Click to expand...
 
Yes that's how I found it, by trying to rebuild it. When I had
originally built my code to put that fomula in the file had no spaces.

Cheers,
Jason Lepack

Dave said:
I'm not sure if you found it via trial and error--but if you had let excel build
the formula, you would have seen the correct syntax right away.

Jason said:
I figured it out.

=VLOOKUP(A2,'[Updated Jan12 Daily D.xls]Daily_D___Bob'!$1:$65536,15,
FALSE)

I just needed to add in the ' ' around the file name.

Cheers,
Jason Lepack

Jason said:
This user has a database that he daily downloads data from a source and
loads it into the database. He then exports 5 queries all into a
single workbook for the day. He recreates this workbook every day but
he has comments for specific parts that he needs to copy over from the
day before, but they aren't always in the same place, or even included
in the next day, thus the vlookup.

I've created macros to format his daily input and his daily output.
The macro asks him to select yesterdays file and then it creates the
vlookup and pastes it in to lookup the data.

He knows that the easy solution is to remove the spaces, but if at all
possible we'd like to workaround some other way.

Cheers,
Jason Lepack

Dave Peterson wrote:
I don't like to build the formula myself. I let excel do it.

With both workbooks open, I start typing the formula:

=vlookup(a2,
Then I click on window to activate the other workbook. Then I select the
worksheet and finally the range.

Then I take back the typing and finish with
,15, false)

====
As an aside, you may want to limit your range to just what you need. You'll
find that it'll calculate quicker and less often.



Jason Lepack wrote:

I'm trying to perform this VLookup

=VLOOKUP(A2,[Updated Jan12 Daily D.xls]Daily_D___Bob!$1:$65536,15,
FALSE)

Excel doesn't like the workbook having spaces in the name it keeps
trying to correct it to:

=VLOOKUP(A2,[UpdatedJan12DailyD.xls]Daily_D___Bob!1:65536,15,FALSE)

but that isn't a valid file name.

What can I do instead to make this work?

Cheers,
Jason Lepack
Click to expand...
Click to expand...
Click to expand...
 
Back
Top