Dynamic named range ?

[fredrerik84]

Hi guys I'm struggling to make my named ranges dynamic :/

I have two lists in name manager:

currency =Conversion!$B$63:$B$70
and:
mycurr =Conversion!$B$63:$D$70

However I need those list to be dynamic without putting them in a table.
I tried this for currency

=Conversion!$B$63:INDEX(Conversion!$B:$B,COUNTA(Conversion!$B:$B))
but for some reason it will only return the first 3 results .

Any help would be greatly appreciated

 

[Marcelo Branco]

Questions
Are there another data below the lists that should not be included in the named ranges?
What is the data type in column B, numbers, text or mixed?

M.

 

[fredrerik84]

Hi M, thanks for your reply ,

Are there another data below the lists that should not be included in the named ranges?

- No there is no data stored below the named range only empty cells.

What is the data type in column B, numbers, text or mixed?

Column B have different currencies: text like "USD" "EUR" "GBP"
Column C = conversion for 1 usd: Numbers with decimal like "0,764625"
Column D = inv USD

Hope this is enough to clarify

 

[Marcelo Branco]

See if these work

currency
=Conversion!$B$63:INDEX(Conversion!$B:$B,MATCH(REPT("z",255),Conversion!$B:$B))

mycur
=Conversion!$B$63:INDEX(Conversion!$D:$D,MATCH(REPT("z",255),Conversion!$B:$B))

M.

 

[fredrerik84]

Nice, looks like this work but there is a small hiccup with this one :
Conversion!$B$63:INDEX(Conversion!$B:$B,MATCH(REPT("z",255),Conversion!$B:$B))

For some reason it produces last items in the list twice So when I select this list I get "SEK" twice , which is my last currency here

 

[fredrerik84]

Sorry this mistake was mine

Thanks alot for helping me with this..

Now my conversion rate script is almost finished.