Relative cell reference in Name Manager

**Petijandro** · 02-06-2013, 10:37 AM

I want to set up a reference (i.e Table 1) for the A2:Ax array
and I always need to know how many items are in the column and change the reference's range according to that.

I do a COUNTA(A:A) and I get a number and I want this number to be the end of the range.
(i.e COUNTA(A:A) is in B2 and the value is 5461. So the reference changes to $A$2:$A$5461)
How can I change the reference's range in the Name Manager so it always updates itself when the A column changes?

**zbor** · 02-06-2013, 10:44 AM

here, try this:

Formula:

Please Login or Register  to view this content.

(;2 is number of columns you want to return. If it's only 1 then remove that part):

Formula:

Please Login or Register  to view this content.

Also change ; to , if you use , in Regional settings

**Petijandro** · 02-07-2013, 07:03 AM

It doesn't work for me (maybe im doing it wrong).
I uploaded the xls.
There is the A column A2:A5461, it is often updated so the range can change to other then 5461 but I want to keep it updated automatically.
So if there is more data added to the A column, I want the reference in the Name Manager to update itself so the formulas which use the List1/2/3 references could use the updated data.
Book2.xlsx