Using Excel2007
Workbook contains 2 sheets,Combined and Reports
On Combined, columnB=Invoice No, C=Account No, E=Invoice Date
On Reports, columnAS=Invoice No, AT=Account No, AU=Invoice Date, AV=Currency
(the other columns on each sheet are not relevant at the moment)
Created Named Ranges as follows:
SAcc= =OFFSET(Reports!$AT$5,0,0,COUNTA(Reports!$AT:$AT),1)
SDte= =OFFSET(Reports!$AU$5,0,0,COUNT(Reports!$AU:$AU),1)
SRef= =OFFSET(Reprts!$AS$5,0,0,COUNTA(Reports!$AS:$AS),1)
SFnd= =OFFSET(Reports!$AS$5,0,0,COUNTA(Reports!$AS:$AS),3)
If I use Range("D5").FormulaArray="=INDEX(Reports!AV$5:AV$1932,MATCH(B5&C5&E5,SRef&SAcc&SDte,0))" I get the result I want, but if I change the formula to "=INDEX(Reports!AV$5:AV$1932,MATCH(B5&C5&E5,SFnd,0))" I get the A Value is no available to the formula or function error.
Have tried changing SFnd to only include 2 columns and using SFnd&SDte in the formula but I still can't get the right result.
This becomes even more important in a formula I need later where I am coming up against the 255 character limit.
Can you help me please
Workbook contains 2 sheets,Combined and Reports
On Combined, columnB=Invoice No, C=Account No, E=Invoice Date
On Reports, columnAS=Invoice No, AT=Account No, AU=Invoice Date, AV=Currency
(the other columns on each sheet are not relevant at the moment)
Created Named Ranges as follows:
SAcc= =OFFSET(Reports!$AT$5,0,0,COUNTA(Reports!$AT:$AT),1)
SDte= =OFFSET(Reports!$AU$5,0,0,COUNT(Reports!$AU:$AU),1)
SRef= =OFFSET(Reprts!$AS$5,0,0,COUNTA(Reports!$AS:$AS),1)
SFnd= =OFFSET(Reports!$AS$5,0,0,COUNTA(Reports!$AS:$AS),3)
If I use Range("D5").FormulaArray="=INDEX(Reports!AV$5:AV$1932,MATCH(B5&C5&E5,SRef&SAcc&SDte,0))" I get the result I want, but if I change the formula to "=INDEX(Reports!AV$5:AV$1932,MATCH(B5&C5&E5,SFnd,0))" I get the A Value is no available to the formula or function error.
Have tried changing SFnd to only include 2 columns and using SFnd&SDte in the formula but I still can't get the right result.
This becomes even more important in a formula I need later where I am coming up against the 255 character limit.
Can you help me please