Vlookup Problem

[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

 

[Dave Peterson]

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.

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

 

[Jason Lepack]

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

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.

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

 

[Jason Lepack]

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

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

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.

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

 

[Dave Peterson]

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.

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

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

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

 

[Jason Lepack]

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

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.

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

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