Best way to create dynamic named ranges

[waimea]

I have posted a similar thread on mrexcel.com however, there were no responses. https://www.mrexcel.com/forum/excel-questions/1081986-named-ranges-same-formula-gives-different-values.html

I have created 10 dynamic named ranges but when I evaluate the ranges the evaluate to different ranges using the same formula.

This formula gives me different results and I can't figure out why?


=OFFSET(Data!$E$14; 0; 0; COUNTA(Data!$E:$E)-2; 1)

Dynamic range1 evaluates to E14:E2233 and dynamic range2 evaluates to F14:F2234???

What am I doing wrong? And what is the best way to create dynamic named ranges for use in formulas? I am trying to create dynamic named ranges for use, mostly, in COUNTIFS and SUMIFS.

[Rob342]

This should be as below


=OFFSET(Data!$E$14,0,0,COUNTA(Data!$E:$E)-1,1))
If you are trying to read data from col F then use listindex

[waimea]

Hi Rob,

thank you for your reply! How do you mean with listindex?

[Rob342]

Something like this


Test = ws.Range("User").Cells(.listIndex + 1, 2).Value


Assigns data to Test from Dynamic range "User" so if the User was 1 in the index then it would look for (Listindex +1, column 2 which would be "B"
Remember that Dynamic Arrays always start with Index of Zero .


If You look at the Previous UserForm Its all in there

Rob

[Fluff]

X posted https://www.mrexcel.com/forum/excel-questions/1081986-named-ranges-same-formula-gives-different-values.html

[waimea]

I mentioned that I posted a similar thread at the top of this page?

[Fluff]

My apologies, I didn't notice that.