Vlookup

B

Bishop

Here is my vlookup formula:

=VLOOKUP($A6,'[Feb 09 $7 Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

Which works. However, the spreadsheet "Feb 09 $7 Report" changes every
month. So next month it will be "Mar 09 $7 Report". I want to use a wild
card in the table_array so that no matter which months report I have open it
will know to look at the $7 Report. I tried this:

=VLOOKUP($A6,'[????? $7 Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

and this:

=VLOOKUP($A6,'[* $7 Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

but neither of those works. How do I accomplish this?
 
D

Don Guillett

One way is to put a formula in a cell referencing the month and then
INDIRECT in your VLookup formula.
 
B

Bishop

But the month name changes every month. I need to be able to refer to the
spreadsheet no matter what the month says. "$7 Report" is the only part of
the name that remains constant from month to month. If I'm not understanding
you then maybe an example would help. Thanks for the prompt reply.

Bishop

Don Guillett said:
One way is to put a formula in a cell referencing the month and then
INDIRECT in your VLookup formula.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bishop said:
Here is my vlookup formula:

=VLOOKUP($A6,'[Feb 09 $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

Which works. However, the spreadsheet "Feb 09 $7 Report" changes every
month. So next month it will be "Mar 09 $7 Report". I want to use a wild
card in the table_array so that no matter which months report I have open
it
will know to look at the $7 Report. I tried this:

=VLOOKUP($A6,'[????? $7 Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

and this:

=VLOOKUP($A6,'[* $7 Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

but neither of those works. How do I accomplish this?
Click to expand...
Click to expand...
 
D

Don Guillett

=TEXT(MONTH(TODAY()),"mmm")&" 09 $7 Report"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bishop said:
But the month name changes every month. I need to be able to refer to the
spreadsheet no matter what the month says. "$7 Report" is the only part
of
the name that remains constant from month to month. If I'm not
understanding
you then maybe an example would help. Thanks for the prompt reply.

Bishop

Don Guillett said:
One way is to put a formula in a cell referencing the month and then
INDIRECT in your VLookup formula.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bishop said:
Here is my vlookup formula:

=VLOOKUP($A6,'[Feb 09 $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

Which works. However, the spreadsheet "Feb 09 $7 Report" changes every
month. So next month it will be "Mar 09 $7 Report". I want to use a
wild
card in the table_array so that no matter which months report I have
open
it
will know to look at the $7 Report. I tried this:

=VLOOKUP($A6,'[????? $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

and this:

=VLOOKUP($A6,'[* $7 Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

but neither of those works. How do I accomplish this?
Click to expand...
Click to expand...
Click to expand...
 
B

Bishop

Forgive my ignorance but I'm not following you. I put this formula in my
spreadsheet and the cell displays Jan 09 $7 Report, which is the format of
the spreadsheet name but I still don't see how I can use this in my vlookup
formula as a wildcard...

Don Guillett said:
=TEXT(MONTH(TODAY()),"mmm")&" 09 $7 Report"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bishop said:
But the month name changes every month. I need to be able to refer to the
spreadsheet no matter what the month says. "$7 Report" is the only part
of
the name that remains constant from month to month. If I'm not
understanding
you then maybe an example would help. Thanks for the prompt reply.

Bishop

Don Guillett said:
One way is to put a formula in a cell referencing the month and then
INDIRECT in your VLookup formula.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Here is my vlookup formula:

=VLOOKUP($A6,'[Feb 09 $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

Which works. However, the spreadsheet "Feb 09 $7 Report" changes every
month. So next month it will be "Mar 09 $7 Report". I want to use a
wild
card in the table_array so that no matter which months report I have
open
it
will know to look at the $7 Report. I tried this:

=VLOOKUP($A6,'[????? $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

and this:

=VLOOKUP($A6,'[* $7 Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

but neither of those works. How do I accomplish this?
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

Did you look in the help index for INDIRECT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bishop said:
Forgive my ignorance but I'm not following you. I put this formula in my
spreadsheet and the cell displays Jan 09 $7 Report, which is the format of
the spreadsheet name but I still don't see how I can use this in my
vlookup
formula as a wildcard...

Don Guillett said:
=TEXT(MONTH(TODAY()),"mmm")&" 09 $7 Report"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bishop said:
But the month name changes every month. I need to be able to refer to
the
spreadsheet no matter what the month says. "$7 Report" is the only
part
of
the name that remains constant from month to month. If I'm not
understanding
you then maybe an example would help. Thanks for the prompt reply.

Bishop

:

One way is to put a formula in a cell referencing the month and then
INDIRECT in your VLookup formula.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Here is my vlookup formula:

=VLOOKUP($A6,'[Feb 09 $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

Which works. However, the spreadsheet "Feb 09 $7 Report" changes
every
month. So next month it will be "Mar 09 $7 Report". I want to use
a
wild
card in the table_array so that no matter which months report I have
open
it
will know to look at the $7 Report. I tried this:

=VLOOKUP($A6,'[????? $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

and this:

=VLOOKUP($A6,'[* $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

but neither of those works. How do I accomplish this?
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

example where g3 is the lookup value and h2 has the workbook name as
described earlier.

=VLOOKUP(G3,INDIRECT("["&H2&".xls]Sheet3!$A$1:$C$7"),3,0)
or use the formula instead of h2
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bishop said:
Forgive my ignorance but I'm not following you. I put this formula in my
spreadsheet and the cell displays Jan 09 $7 Report, which is the format of
the spreadsheet name but I still don't see how I can use this in my
vlookup
formula as a wildcard...

Don Guillett said:
=TEXT(MONTH(TODAY()),"mmm")&" 09 $7 Report"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Bishop said:
But the month name changes every month. I need to be able to refer to
the
spreadsheet no matter what the month says. "$7 Report" is the only
part
of
the name that remains constant from month to month. If I'm not
understanding
you then maybe an example would help. Thanks for the prompt reply.

Bishop

:

One way is to put a formula in a cell referencing the month and then
INDIRECT in your VLookup formula.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Here is my vlookup formula:

=VLOOKUP($A6,'[Feb 09 $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

Which works. However, the spreadsheet "Feb 09 $7 Report" changes
every
month. So next month it will be "Mar 09 $7 Report". I want to use
a
wild
card in the table_array so that no matter which months report I have
open
it
will know to look at the $7 Report. I tried this:

=VLOOKUP($A6,'[????? $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

and this:

=VLOOKUP($A6,'[* $7
Report.xls]By_Rep_by_Filter'!$F$1:$P$5000,8,FALSE)

but neither of those works. How do I accomplish this?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup and using single quote 0
Reference Cell in VLOOKUP 5
Calculating(Processors(2)): % 1
Calculating(Processor(2)): % 8
VLOOKUP & Data Validation 0
Vlookup and if formula 2
Parsing Month WorkSheet (one row/Project) into Folder & SubFolders by Project 0
If Function 1

Top