Challenge! -- Error creating Dymnamic Named Range

[Tinhare]

The range I am trying to create will be on Sheet1 and called Rank_Range
This is the formula I am using:-
=IF(a5/10=int(a5/10),OFFSET(address(match(a5,rsosbbr_Regions,0)+4,5),0,5,vlookup(a5,Query_4,2)+vlookup(a5+5,Query_4,2),1),offset(ADDRESS( match(a5,rsosbbr_Regions,0)+4,5),0,0,vlookup(a5,Query_4,2)+vlookup(a5+5,Query_4,2),5))

Some background info to help decipher the formula:-
Named ranges used
rsosbbr_Regions, This is a dynamic range
=OFFSET(Sheet1!$A$5,0,0,COUNT(Sheet1!$A:$A),1)

Query_4, This is also a dynamic range
=OFFSET(Sheet2!$A$3,0,0,COUNT(Sheet2!$A:$A),2)
The two columns contain :-
10 and 4
15 and 5
20 and 3
25 and 5

The data is as follows:
Sheet1 Column A starting at row 5
10,10,10,10,15,15,15,15,15,20,20,20,25,25,25,25,25
Columns B and C starting at row 5
Stuff, not important
Column D starting at row 5
=Rank(E5,$E$5:$E$13)
=Rank(E6,$E$5:$E$13)
etc etc down to row 13
Column D Starting at row 14
=Rank(E14,$E$14:$E$21)
=Rank(E15,$E$15:$E$21)
etc etc down to row 21
Column E contains various numbers that are ranked in Column D

As you can see the Range used in the rank command changes based on the numbers in column A this is the Dynamic Range I am trying to create.

[Aladin Akyurek]

I don't know what to say, except: please use the method I described earlier to you:

http://www.mrexcel.com/board/viewtop...c=6608&forum=2

It's of prime importance that the OFFSET formulas compute the exact range of data at all times. The RANK function is sensitive to the range. If spurious data is included in a dynamic name range that you feed to RANK, the results will be unpredictable.

For example:

=OFFSET(Sheet1!$A$5,0,0,COUNT(Sheet1!$A:$A),1)

is incorrect. It will have 4 trailing zeroes at the end. And if you have blanks in the A-range, COUNT will misrepresent the end of the range.

Regards,

Aladin

Postscript. I just discovered your test WB in my mail. May I ask resend it in a state that it doesn't ask for external links.

[ This Message was edited by: Aladin Akyurek on 2002-04-30 09:00 ]

[Tinhare]

Thanks again for responding,
I will try it again and resend the WB tonight when I get home.

Cheers,
Alan.