Log In
Register
Home
November 16, 2019
Offline
Hello,
I have an excel file where the data grows horizontally. A chart shows this data and I want the chart to update automatically when more data is added. For that, I wanted to create range in "Name Manager" but before, I tried the formula in a cell.
If I write =ADDRESS(6;COUNTA(6:6)) I get $AZ$6
So I thought that this could be used in the formula =AP6:address(6;counta(AP6:BJ6)) to find out what comes after ":", but I get the message of the image below.
Is there anything I am doing wrong? Is there any other way to create a Range?
Thanks in advance,
Cristina
Trusted Members
Moderators
November 1, 2018
Offline
ADDRESS returns text, not a range reference, so you'd need to use INDIRECT:
=AP6:INDIRECT(address(6;counta(AP6:BJ6)))
or perhaps just:
=AP6:INDEX(6:6;counta(6:6))
November 16, 2019
Offline
Hello Velouria,
The second option works well because I get the array I want when I write it in a cell. The problem is that when I write this formula in the Name Manager, it changes the row to a complete different one.
I've tried to fix the row but then I get a message telling me that there's a problem with this formula.
=$AP$6:index($6:$6:counta($6:$6)+1))
Does this mean that I can't fix cells or colums in the Name Manager?
Why, once I write and save a Name in the Name Manager, the rows change completely?
Thanks in advance,
Cristina
July 16, 2010
Offline
Hi Cristina,
Not sure why you're using ADDRESS for this. It's best to use INDEX or OFFSET to write dynamic named ranges.
Mynda
Trusted Members
Moderators
November 1, 2018
Offline
You've used a colon instead of a semicolon in the name definition - it should read:
=$AP$6:index($6:$6;counta($6:$6)+1))
1 Guest(s)
