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
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))
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
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
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))
Hello Velouria,
It worked now!!!
Thanks so much for your support 
Cristina
Hi Mynda,
Velouria has helped me with this and it is solved now.
Thanks,
Cristina
