| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MATCH function
Hi,
I have a worksheet function (in fact hundreds!) like this: =MATCH("Basis B", 'C:\SRCPath\20061017\[ExportFUND.xls]Export'!$E$7:$Z$7,0) This returns the column that another cell uses in an ADDRESS function with INDIRECT to return a value from another workbook. Basis B comes from the column Header C:\SRCPath\ is the same as a cell called 'MasterPath' - but is static 20061017 comes from a cell called 'ExportDate' FUND comes from column B on the same row as the function So, is there any way to derive the path dynamically, in other words, when the cell called 'ExportDate' changes, or when the cell called 'MasterPath' changes, currently I have code running in the Worksheet_Change event to update all the formulae with the new Path and the date ... I have tried to do something like this: MasterPath&"[Export"&B17&".xls]Export'!" then tack on the search range at the end so it looks like this ="'"&MasterPath&"[Export"&B17&".xls]Export'!$E$7:$Z$7" but when I put that into my final formula: =MATCH("Basis B","'"&MasterPath&"[Export"&B16&".xls]Export'!$E$7:$Z$7",0) it always fails to evaluate and I get a #VALUE error. So, that's the challenge - is there anyway to derive the oath to the MATCH function from other cells like that? I works fine using the macro in the change event of course, but if possible I'd prefer to have a worksheet function managing to workout the path itself! Thanks for any help Philip |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with MATCH function
Does the following work? Sorry, could not test.
=MATCH("Basis B", INDIRECT("'"&MasterPath&"\"&ExportDate&"\[Export"&INDIRECT(ADDRESS(SUM(ROW()),2))&".xls]Export'!$E$7:$Z$7",0) HTH Kostis Vezerides Philip wrote: Hi, I have a worksheet function (in fact hundreds!) like this: =MATCH("Basis B", 'C:\SRCPath\20061017\[ExportFUND.xls]Export'!$E$7:$Z$7,0) This returns the column that another cell uses in an ADDRESS function with INDIRECT to return a value from another workbook. Basis B comes from the column Header C:\SRCPath\ is the same as a cell called 'MasterPath' - but is static 20061017 comes from a cell called 'ExportDate' FUND comes from column B on the same row as the function So, is there any way to derive the path dynamically, in other words, when the cell called 'ExportDate' changes, or when the cell called 'MasterPath' changes, currently I have code running in the Worksheet_Change event to update all the formulae with the new Path and the date ... I have tried to do something like this: MasterPath&"[Export"&B17&".xls]Export'!" then tack on the search range at the end so it looks like this ="'"&MasterPath&"[Export"&B17&".xls]Export'!$E$7:$Z$7" but when I put that into my final formula: =MATCH("Basis B","'"&MasterPath&"[Export"&B16&".xls]Export'!$E$7:$Z$7",0) it always fails to evaluate and I get a #VALUE error. So, that's the challenge - is there anyway to derive the oath to the MATCH function from other cells like that? I works fine using the macro in the change event of course, but if possible I'd prefer to have a worksheet function managing to workout the path itself! Thanks for any help Philip |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with MATCH function
=indirect() will only work if the sending workbook is open.
vezerid wrote: Does the following work? Sorry, could not test. =MATCH("Basis B", INDIRECT("'"&MasterPath&"\"&ExportDate&"\[Export"&INDIRECT(ADDRESS(SUM(ROW()),2))&".xls]Export'!$E$7:$Z$7",0) HTH Kostis Vezerides Philip wrote: Hi, I have a worksheet function (in fact hundreds!) like this: =MATCH("Basis B", 'C:\SRCPath\20061017\[ExportFUND.xls]Export'!$E$7:$Z$7,0) This returns the column that another cell uses in an ADDRESS function with INDIRECT to return a value from another workbook. Basis B comes from the column Header C:\SRCPath\ is the same as a cell called 'MasterPath' - but is static 20061017 comes from a cell called 'ExportDate' FUND comes from column B on the same row as the function So, is there any way to derive the path dynamically, in other words, when the cell called 'ExportDate' changes, or when the cell called 'MasterPath' changes, currently I have code running in the Worksheet_Change event to update all the formulae with the new Path and the date ... I have tried to do something like this: MasterPath&"[Export"&B17&".xls]Export'!" then tack on the search range at the end so it looks like this ="'"&MasterPath&"[Export"&B17&".xls]Export'!$E$7:$Z$7" but when I put that into my final formula: =MATCH("Basis B","'"&MasterPath&"[Export"&B16&".xls]Export'!$E$7:$Z$7",0) it always fails to evaluate and I get a #VALUE error. So, that's the challenge - is there anyway to derive the oath to the MATCH function from other cells like that? I works fine using the macro in the change event of course, but if possible I'd prefer to have a worksheet function managing to workout the path itself! Thanks for any help Philip -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with MATCH function
Hi,
Yes that's correct, so we're using the INDIRECT.EXT function from Laurent Longre's Morefunc.xll it works the same but with closed workbooks... until I can persuede Microsoft to change it in Excel 2007 ! Philip "Dave Peterson" wrote: =indirect() will only work if the sending workbook is open. vezerid wrote: Does the following work? Sorry, could not test. =MATCH("Basis B", INDIRECT("'"&MasterPath&"\"&ExportDate&"\[Export"&INDIRECT(ADDRESS(SUM(ROW()),2))&".xls]Export'!$E$7:$Z$7",0) HTH Kostis Vezerides Philip wrote: Hi, I have a worksheet function (in fact hundreds!) like this: =MATCH("Basis B", 'C:\SRCPath\20061017\[ExportFUND.xls]Export'!$E$7:$Z$7,0) This returns the column that another cell uses in an ADDRESS function with INDIRECT to return a value from another workbook. Basis B comes from the column Header C:\SRCPath\ is the same as a cell called 'MasterPath' - but is static 20061017 comes from a cell called 'ExportDate' FUND comes from column B on the same row as the function So, is there any way to derive the path dynamically, in other words, when the cell called 'ExportDate' changes, or when the cell called 'MasterPath' changes, currently I have code running in the Worksheet_Change event to update all the formulae with the new Path and the date ... I have tried to do something like this: MasterPath&"[Export"&B17&".xls]Export'!" then tack on the search range at the end so it looks like this ="'"&MasterPath&"[Export"&B17&".xls]Export'!$E$7:$Z$7" but when I put that into my final formula: =MATCH("Basis B","'"&MasterPath&"[Export"&B16&".xls]Export'!$E$7:$Z$7",0) it always fails to evaluate and I get a #VALUE error. So, that's the challenge - is there anyway to derive the oath to the MATCH function from other cells like that? I works fine using the macro in the change event of course, but if possible I'd prefer to have a worksheet function managing to workout the path itself! Thanks for any help Philip -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with MATCH function
no, it seems I am stuck building the formula using code in the event...
There's always some problem with the quotes... "vezerid" wrote: Does the following work? Sorry, could not test. =MATCH("Basis B", INDIRECT("'"&MasterPath&"\"&ExportDate&"\[Export"&INDIRECT(ADDRESS(SUM(ROW()),2))&".xls]Export'!$E$7:$Z$7",0) HTH Kostis Vezerides Philip wrote: Hi, I have a worksheet function (in fact hundreds!) like this: =MATCH("Basis B", 'C:\SRCPath\20061017\[ExportFUND.xls]Export'!$E$7:$Z$7,0) This returns the column that another cell uses in an ADDRESS function with INDIRECT to return a value from another workbook. Basis B comes from the column Header C:\SRCPath\ is the same as a cell called 'MasterPath' - but is static 20061017 comes from a cell called 'ExportDate' FUND comes from column B on the same row as the function So, is there any way to derive the path dynamically, in other words, when the cell called 'ExportDate' changes, or when the cell called 'MasterPath' changes, currently I have code running in the Worksheet_Change event to update all the formulae with the new Path and the date ... I have tried to do something like this: MasterPath&"[Export"&B17&".xls]Export'!" then tack on the search range at the end so it looks like this ="'"&MasterPath&"[Export"&B17&".xls]Export'!$E$7:$Z$7" but when I put that into my final formula: =MATCH("Basis B","'"&MasterPath&"[Export"&B16&".xls]Export'!$E$7:$Z$7",0) it always fails to evaluate and I get a #VALUE error. So, that's the challenge - is there anyway to derive the oath to the MATCH function from other cells like that? I works fine using the macro in the change event of course, but if possible I'd prefer to have a worksheet function managing to workout the path itself! Thanks for any help Philip |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with MATCH function
If you want to use this formula in VBA code you can use the following:
1. Gradually build the string. 2. Use "" inside double quotes to express " HTH Kostis Vezerides Philip wrote: no, it seems I am stuck building the formula using code in the event... There's always some problem with the quotes... "vezerid" wrote: Does the following work? Sorry, could not test. =MATCH("Basis B", INDIRECT("'"&MasterPath&"\"&ExportDate&"\[Export"&INDIRECT(ADDRESS(SUM(ROW()),2))&".xls]Export'!$E$7:$Z$7",0) HTH Kostis Vezerides Philip wrote: Hi, I have a worksheet function (in fact hundreds!) like this: =MATCH("Basis B", 'C:\SRCPath\20061017\[ExportFUND.xls]Export'!$E$7:$Z$7,0) This returns the column that another cell uses in an ADDRESS function with INDIRECT to return a value from another workbook. Basis B comes from the column Header C:\SRCPath\ is the same as a cell called 'MasterPath' - but is static 20061017 comes from a cell called 'ExportDate' FUND comes from column B on the same row as the function So, is there any way to derive the path dynamically, in other words, when the cell called 'ExportDate' changes, or when the cell called 'MasterPath' changes, currently I have code running in the Worksheet_Change event to update all the formulae with the new Path and the date ... I have tried to do something like this: MasterPath&"[Export"&B17&".xls]Export'!" then tack on the search range at the end so it looks like this ="'"&MasterPath&"[Export"&B17&".xls]Export'!$E$7:$Z$7" but when I put that into my final formula: =MATCH("Basis B","'"&MasterPath&"[Export"&B16&".xls]Export'!$E$7:$Z$7",0) it always fails to evaluate and I get a #VALUE error. So, that's the challenge - is there anyway to derive the oath to the MATCH function from other cells like that? I works fine using the macro in the change event of course, but if possible I'd prefer to have a worksheet function managing to workout the path itself! Thanks for any help Philip |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
| MATCH function, exclusion question | Excel Discussion (Misc queries) | |||
| Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
| I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
Linear Mode
