up vote 1 down vote favorite
share [g+] share [fb]

I'm trying to create a "Dynamic Named Range" in Excel so I can populate an X/Y values table inside of it in Java. I've seen tutorials, but I still don't get it. I know I have to use the =OFFSET() function, but where? Within the chart? Somewhere else?

I'm sorry, it's because my Excel level sucks... I haven't used it for a looong time.

link|improve this question
I've tried a lot of things!!! :( Because this function has to be inside of a chart (after further investigation)... so i've tried: =OFFSET(Sheet1!$I$29,0,0,COUNT($I$29:$I$999),1) =OFFSET(Sheet1!$I$29,0,0,COUNT(Sheet1!$I$29:Sheet1!$I$9999),1) And nothing seems to work, Excel doesn't like it. Can anyone help me please??? :( – msqar Sep 19 '11 at 14:16
Put that formula in a cell, and click the fx button to get the formula wizard. That may help you to track what's wrong. – iDevlop Sep 19 '11 at 14:25
Try replacing Sheet1!$I$29:Sheet1!$I$9999 by Sheet1!$I$29:$I$9999 to simplify – iDevlop Sep 19 '11 at 14:28
Yea, tried that, but it didn't work either :( – msqar Sep 19 '11 at 16:17
feedback

migrated from stackoverflow.com Sep 19 '11 at 16:41

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote

The easiest way is to create a named range (Insert, Name, Define in xl2003). Enter myRange (or any other name) as the name to define, and the formula in the "refers to" box.
To help you build the formula, you cand read this, this and this.
You can test if your named range works by using entering in a cell =count(myRange) or a similar formula.

Finally, you build your chart using the range name as usual, but you replace the =Sheet1!$C$4:$C$10 in the data range by =myRange.

link|improve this answer
Ok, let me check that =) thanks iDevlop! – msqar Sep 19 '11 at 16:18
One more thing, whenever i try to write on an excel cell in java, i get a NullPointerException when i get the cell, and since its empty, it gets a null, and when i do: x.setCellValue being x = null -> exception!!! :) is there any way to avoid this? is there any null value in Excel? – msqar Sep 19 '11 at 16:19
feedback

Your Answer

 
or
required, but never shown
discard