Dynamic range

[Lv27]

Hello people!

I can't succeed in making a dynamic range.

The name of the document is "Porteføljer.xlsm" and the name of the specific sheet is 'Porteføljer '
Under formulas I name the x axis coloumn "Years" and define is as: =OFFSET('Porteføljer '!$A$4;0;0;COUNT('Porteføljer '!$A:$A))
and I define my x axis as ='Porteføljer.xlsm'!Years

What am I doing wrong?

[Spencer101]

You seem to be missing an argument at the end of the formula.

=OFFSET(StartCell,0,0,COUNT(Range),1)

[Lv27]

Thanks! I've just tried using the function COUNTIF(Range)-1 and it seems to solve the problem as well - but I don't really understand why it is necessary?

[Spencer101]

If you post your entire formula I will explain what it's doing and that should clear up your query above.

[Lv27]

Sorry adding 1 in the end does indeed work Excel and I are not really good friends today I guess...

[Lv27]

Now I've tried adding "1" in the end but it doesn't change anything. Nevertheless using COUNTIF does work, so my formula will be: =OFFSET('Porteføljer '!$A$4;0;0;COUNTIF('Porteføljer '!$A:$A)-1)

So my only question is: Why is it necessary to use COUNTIF and minus 1 instead of just COUNT (the formula I am using when defining my y-axis)?

...I don't know if this question makes any sense at all

[Spencer101]

Are you using COUNTIF or COUNTA?

If COUNTIF then you're missing a bit to the formula.

If COUNTA then it will work and the -1 at the end discounts the header row in the count.

[Lv27]

Hmm, using COUNTIF does work, when I minus 1 after the argument Anyway, everything is running as it's supposed to now, so I'm just going to mark this thread as solved - thanks a lot!

[Lv27]

Just wanted to say that I am the one being a complete idiot. I am not using COUNTIF but another formula that I guess translates into COUNTA in English.

Thank you again (I have added credit)