Horizontal Dynamic Charts

[JanineAEllis0808]

Hi,

Hope someone can help! I'm working in a workbook with around 4 charts (mixture of line/bar/line & bar on two axis etx) each of these has more data added every week. I am trying to update these so I don't have to change the series every week I have managed to create a test workbook using the offset function and named ranges however I can only get this to work running vertically ie =OFFSET(Sheet2!$E$1,COUNTA(Sheet2!$E:$E)) however my data runs horizontallly eg name of range in column A - Number of tasks, various text held in columns B-E then weekly data running from H:BG.

Many Thanks,

 

[Yard]

Hi and welcome to the board,

If you look at the Help for the OFFSET function you'll see how to build your dynamic named range formula:

OFFSET(reference,rows,cols,height,width)


So to define a range starting in Sheet2!E1 and continuing for as many rows down and across as there is data, try:


=OFFSET(Sheet1!$E$1,0,0,COUNTA(Sheet1!$E:$E),COUNTA(Sheet1!$1:$1))

 

[JanineAEllis0808]

Right not sure if I am doing something wrong here I appear to have two issues:

1) I have created the dynamic ranges 5 in total, Inboundunit, Outboundunit, PCAunit, Answerunit then weeks for the X axis all using the formula =OFFSET(Telephony!$C$1,0,0,COUNTA(Sheet1!$C:$C),COUNTA(Sheet1!$58:$58)) with various row references however when I input the series value as ='Telephony'!Inboundunit excel dislays an error - any ideas

2) The rows I am trying to graph contain references to another workbook eg =Sheet2!C30... if I use this formula will it work as countA will count the 0 value held within the cell until that weeks data is filled?

Any help gratefully received as now on the 5th week of updating ranges for 57 graphs (I counted lol)

 

[Yard]

Hi

i) To use dynamic ranges in a chart you must add each series individually and precede the named range with the filename e.g.:

Series 1 data values = '[My file.xls]Telephony'!Inboundunit

It's clumsy, but once set up it should stay that way.

NB your syntax suggests you have worksheet-level names (the name Inboundunit belonging to the worksheet Telephony). You may find the entry of the filename + range into the chart series easier if you use workbook-level names, like

Series 1 data values = 'My file.xls'!Inboundunit


ii) Yes, so you need to find another way to identify the last cell with data in and use that in the dynamic range. I don't know your data, so is there something else anywhere in the sheet which could tell you the last valid week in the data range?

 

[JanineAEllis0808]

Hi Yard,

Got that bit working! Step by step lol.

However the graph is all wonky, I now get an error message "The reference is not valid. References for titles, values, or sizes must be a single cell, row, or column."


P.S. I removed all future date references from the X axis so only graphable weeks were shown will this resolve the second issue?

 

[Yard]

However the graph is all wonky, I now get an error message "The reference is not valid. References for titles, values, or sizes must be a single cell, row, or column."

And are yours? Don't forget you are entering the named range for each series, so each should by default be a single row or column of data.

P.S. I removed all future date references from the X axis so only graphable weeks were shown will this resolve the second issue?

Perhaps. If it looks like it works then it works!

 

[JanineAEllis0808]

Woohoo 47 graphs now working, 16 to go!

I was making it harder than it had to be I have set the Y axis as the whole range then used a named range for the X axis with your fomula just moving it to another area of the workbook as you suggested so the future references don't interfere.

Still having a problem with the 2 axis graphs though, it would appear you can't put the whole range in the Y axis on these without it trying to plot them even if there isn't a corresponding X axis which is putting the graphs out. Using the formula wouldn't work due to the future references from the pull through from the input document. Is this something I'll just have to deal with?

 

[Yard]

Still having a problem with the 2 axis graphs though, it would appear you can't put the whole range in the Y axis on these without it trying to plot them even if there isn't a corresponding X axis which is putting the graphs out. Using the formula wouldn't work due to the future references from the pull through from the input document. Is this something I'll just have to deal with?

I'm not sure - I don't really follow what you're saying, sorry!