Referencing named arrays across different sheets problem

[rxp409]

Hi,

I'm working in excel 2002. I have a table of data in sheet1, and am trying to perform calculations across them in sheet2. The whole table of data is named all, the first column is named dates, then in sheet2 i have 2 cells specifying dates start and end.

The calculations in the second sheet which seems to have a problem is:

=(PERCENTILE(INDIRECT(ADDRESS(MATCH(start,Sheet1!dates,1),4)):INDIRECT(ADDRESS(MATCH(end, sheet1!dates,1),4)),0.9))*100

which returns #NUM! error. In the define name dialog box all named ranges have the correct sheets referenced. So actually in the above formula when i hit enter the Sheet1! parts get replaced by the name of the workbook.xls

Also in the second sheet i have got other formulas which reference the all table without specifying the sheet at all - and that works, but makes no difference when i just use dates in the above formula. So i guess it has something to do with the INDIRECT, ADDRESS and MATCH functions.

Any ideas?

 

[rxp409]

OK, so the problem is not with referencing the named ranges across sheets, as i've tried with other functions and they all work. [I would change the name of the thread, but i can't see the edit button anywhere ].

So the problem is with the percentile function working across both sheets, or maybe the way i am specifying the inputs. I have tried a number of suggested permutations of 'sheet1'![dates] etc. which don't seem to work. Given what i said above the name of the range should work on its own - but doesn't - leading me to believe it is something to do with the other inputs.