| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
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 "Bishop" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
=TEXT(MONTH(TODAY()),"mmm")&" 09 $7 Report"
-- Don Guillett Microsoft MVP Excel SalesAid Software "Bishop" wrote in message ... 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" wrote: 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 "Bishop" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
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" wrote: =TEXT(MONTH(TODAY()),"mmm")&" 09 $7 Report" -- Don Guillett Microsoft MVP Excel SalesAid Software "Bishop" wrote in message ... 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" wrote: 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 "Bishop" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Did you look in the help index for INDIRECT
-- Don Guillett Microsoft MVP Excel SalesAid Software "Bishop" wrote in message ... 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" wrote: =TEXT(MONTH(TODAY()),"mmm")&" 09 $7 Report" -- Don Guillett Microsoft MVP Excel SalesAid Software "Bishop" wrote in message ... 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" wrote: 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 "Bishop" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
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 "Bishop" wrote in message ... 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" wrote: =TEXT(MONTH(TODAY()),"mmm")&" 09 $7 Report" -- Don Guillett Microsoft MVP Excel SalesAid Software "Bishop" wrote in message ... 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" wrote: 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 "Bishop" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
