formula gives references error

[martijn1988]

I'm currently trying to program several graphs with dynamic axes. Now i know i need to use OFFSET for this but for some reason i'm not getting anywhere. I made the following formula:

=OFFSET(INDIRECT(Berekeningen!C266),0,0,0,COUNTIF(F75:LK75,">1"))

I'm using the indirect function to allow users to have a different starting cell. Changing the function to a normal cellreference has no effect. If i remove the last 2 arguments the formula returns the value of the cell the indirect function is referring to. The COUNTIF function on its own works perfectly. If i look at the formula from the formula box, theres no reference error from one of the individual arguments. So basically all the separate parts work but combined into the OFFSET formula it gives an #REF! error. Can somebody tell me, based on this information, why the formula isn't not working?

 

[Andrew Poulsom]

What's in C266 on sheet Berekeningen?

 

[martijn1988]

F75

coming from =ADDRESS(CELL("row", Overzicht!F75),COLUMN(Overzicht!F75)+D264+D265,4)

 

[Andrew Poulsom]

Why have you got relative references and no sheet name in COUNTIF(F75:LK75?

 

[T. Valko]

I'm currently trying to program several graphs with dynamic axes. Now i know i need to use OFFSET for this but for some reason i'm not getting anywhere. I made the following formula:

=OFFSET(INDIRECT(Berekeningen!C266),0,0,0,COUNTIF(F75:LK75,">1"))

I'm using the indirect function to allow users to have a different starting cell. Changing the function to a normal cellreference has no effect. If i remove the last 2 arguments the formula returns the value of the cell the indirect function is referring to. The COUNTIF function on its own works perfectly. If i look at the formula from the formula box, theres no reference error from one of the individual arguments. So basically all the separate parts work but combined into the OFFSET formula it gives an #REF! error. Can somebody tell me, based on this information, why the formula isn't not working?

You have the height argument set to 0.

 

[martijn1988]

Thank you both for the quick replies. I changed the formula to this, without result:

=OFFSET(INDIRECT(Berekeningen!C267);0;0;1;COUNTIF(Overzicht!F76:Overzicht!LK76;">1"))

If it clarifies things, berekeningen is the dutch word for calculations (the sheet i use to process all the data) and overzicht is overview.

I didn't have the sheet name added because the references are located in the same sheet, which was a mistake i realise.

My assigment (internship) is to create a tool that shows differences between budgetted and actual results. I want to create a graph that shows the data, but starts when the user wants it. This because its a daily report (for an entire year), and i don't want to overwhelm the user with to much data. The indirect function is used to calculate it. The countif is to calculate which what data has been alrdy collected (i tried counta but it sees that a formula is present and counts each cell, even if there is no data present yet)

Because it might have influence how the indirect function is calculated:

(user date - starting date of the tool) = A
ROUNDDOWN(A) = B
B-A=C

B*6 = D (5 days, and 1 column for a weektotal)
C*7 = E

=ADRESS(CELL("row"; Overzicht!F76);COLUMN(Overzicht!F76)+D+E;4)

Im sorry if this is a bit vague, but the workbook contains information im not allowed to post. Any help however would be greatly appreciated.

 

[martijn1988]

I am not sure why but the formula is working now. My second formula that i posted accidently used a , instead of a ; for the SUMIF function. After correcting it, the new formula (with sheet references and a height of 1) give a value error (which is basically good). Quickly trying it in out on an graph by using a name sady give an error. Seeing as it is late where i am i will try to fix that tommorow.

Thank you for the help!

 

[Andrew Poulsom]

If you must use INDIRECT:

=OFFSET(INDIRECT("Overzicht!"&Berekeningen!$C$267);0;0;1;COUNTIF(Overzicht!$F$76:$LK$76;">1"))

but I don't really see the point when the range to be counted doesn't use INDIRECT so:

=OFFSET(Overzicht!$F$76;0;0;1;COUNTIF(Overzicht!$F$76:$LK$76;">1"))