Dynamic Chart with Horizontal Table

Hi All,

I've found great explanations regarding Dynamic Charts (e.g. http://www.contextures.com/xlNames02.html or http://peltiertech.com/Excel/Charts/...hartLinks.html) however all examples refer to data within a table than extends vertically.

I however would like to creat a dynamic chart on a table with data going horizontally. I assume you have to alter the OFFSET formula to Count for the data going horizontally, but not sure how to do this?? For example, please refer to attached Chart sample in 'Sheet 1'.

Does anyone know how to kick start me on this??

Is it also possible to automatically collapse the x range on graph (e.g. data in attached only appears till July, so would like to only show chart till July, rather than Dec. However august data is entered, the chart would extend to show aug)

Regards
Tim

Hi Andy,

Thanks for your response, it works great, when I have no content in the data table.

However, I've attached a different version, where you'll notice the data table has a Vlookup function in the empty cells. For some reason it doesn't work with the Vlookup.

Do you know a way around this?

Thanks
Tim

Thanks ptm0412, the COUNTIF formula did the trick.

However I have another issue.... I also have "#N/A" that also appears in the data occasionally and this seems to exclude that month from the Count.

I was thinking I would need to add something like "<>N/A", however I don't know how to add it to the formula:
=OFFSET(Sheet1!$B$22,0,0,1,COUNTIF(Sheet1!$B$23:$M$23,">=0")

Do you know how I could fix this one?

Thanks
Timarcarze!

I've had another attempt, but still not working....

=OFFSET(Sheet1!$B$64,0,0,1,SUMPRODUCT(--(Sheet1!$B$65:$M$65>=0),--(Sheet1!$B$65:$M$65=#N/A)))

can anyone help on this?
Thanks

Hi Andy,

I'm not sure I entirely understand your comments.

Basically, I've changed some of the formulas to display NA(), so the graph skips the months without data, rather than plotting as 0. However, now that I've done this, I think the Countif function is not counting that month and shortening the month range.

E.g. If I have actual data in months Jan, Feb, Mar, 0 data for Apr, data in May, Jun & July. The countif counts data in 6 mths and then only displays months Jan - Jun (missing the data for July).

I'd like both series data to be recognised and the series that has most months would be represented on graph. E.g. Series 1 has data plots data Jan-Jul, however Series 2 has 0 data in Apr but data in Jan - Aug. I would like the graph to plot to Aug for Series 2 (missing April) and Series 1 to plot to July (missing Aug).

Hope you can help. Thanks

Hi Andy,

Thanks, that seems to do the trick with #N/A, however it hasn't recognised that the chart only plots the graph with either #N/A or >0. E.g. I think the countif (">0"....but I want it to recognise a second criteria of "=#N/A" as well) function took care of that last time.

Note in attached updated spreadsheet, the chart (for precipation) does not stop plotting months at Aug.

Do you know how to fix?

Cheers
Tim

Ahhh fantastic Andy, you're a legend at this stuff!

Cheers
Timarcarze

Here is updated file with Andy's help.

Cheers
Timarcarze

HI
I am trying to do the similar thing but I dont' have vlookup function nor NA issues. So I used the offset with countif to define the range.
It works but it also includes additional 4 cells. The additonal cells are adjacent and are all empty. Any suggestions on how to rectify it.
I know its an old post. Still appreciate your help greatly.

Thanks,
Appz

Originally Posted by timarcarze

Ahhh fantastic Andy, you're a legend at this stuff!

Cheers
Timarcarze

Thats very great, But may Mr.Andy can help me too, i just need dynamic horizontal chart with double rows of axis , can u help me please

thanks before

Thanks Arlette,

I opened a new thread.

This was not obvious as normally the spanish fórums i use ask alwasy to search for threads and write in them :-)

Have a nice day.

Sky