Need help with Offset formula

[pazuuu]

Hi all,

I am trying to create a dynamic Excel line chart.

All is well, except for my x-axis (which is a text axis). It currently looks like this: Dynamic axis.jpg

I am using the Offset formula. The formula for my x-axis is =OFFSET(Worksheet1!$D$4,0,-3,COUNT(Worksheet1!$D$4:$D$36)). This is to show the axis if cells in Column D are filled up appropriately.

However, in the cells containing the axis name itself, this formula is used =IF(D9=0,NA(),'Worksheet2'!A6). I've tried removing the formula from these cell, but that didn't solve the problem.

I appreciate any help provided.

[adyteo]

First, I believe you need to use COUNTA instead of COUNT.
COUNT is for numbers, COUNTA is for text.

It would really help if you upload the file (remove any confidential info).

[pazuuu]

I tried using Counta, but then the text didn't come up at all on the axis.

Attached is the file. Thank you. ^^

[adyteo]

In the graph, select data and for the median correct the formula: you need to add the name Median (the one you defined with Offset): =Sample.xls!Median

then it will work (it worked for me)

[adyteo]

and replace COUNTA with COUNT for both names (Median and Percentage)

[pazuuu]

Oh yes! It worked!

Thank you so much!