Dynamic chart range not displaying all Horizontal Axis Labels

[Peter Davison]

I have created a chart from data in Cells DR5 to DY7 - Although the DY7 could be more columns which is why I am trying to make it dynamic (see below)
I am using this Named Range called - Numberofstores
=IFERROR(OFFSET(Assortment!$DR$5:$EZ$7,0,0,3,COUNT(Assortment!$DR$5:$EZ$5)*1),"")
but as you can see it is still only displaying the one line (Row 5).
I don't think I have the named range formula correct.
Any help would be appreciated.

 

[RoryA]

Try removing the IFERROR function.

 

[Peter Davison]

When I remove the iferror I get the result but not horizontally, any thoughts? (See below)

 

[RoryA]

I am struggling to replicate that. Any chance you can post a workbook somewhere (with suitably anonymised data) that I can have a look at? I can only assume it's something particular to the chart you have set up.

 

[Peter Davison]

It's really strange because when I create a new chart with values and not formals driving Rows 5 to 7 it works perfectly with -=OFFSET(Sheet1!$DR$5:$EZ$7,0,0,3,COUNT(Sheet1!$DR$5:$EZ$5))
and the chart data looks like this -

But when I use same named range formula on my formularised rows 5-7 it looks like this -

which is why the figures are transposed on the chart.
I think its because Rows 5 to 7 are formulas but I cannot understand why it is transposing the figures.
Any thoughts why this should happen?

 

[RoryA]

Not offhand. What are the formulas? Are they dynamic arrays, or regular formulas in each cell?

 

[Peter Davison]

Rows 5 and 6 are regular formulas and Row 7 is a transpose array.
However, probably thinking like you may be, I tried to ignore row 7 and test just with standard formulas and it did the same thing.
The only time it worked was if I used values on the three rows.
Really strange never seen this before.
I appreciate your time with me on this.

 

[RoryA]

Works fine here with some simple formulas, so I think I'd have to see the workbook.

 

[Peter Davison]

Really odd.
It's a massive workbook with macros and links and client data, so difficult to send to you.
I'll have another look to see if I can spot what could be a problem.
Really do thank you for your time.

 

[RoryA]

Is it possible to cut it down to just the chart and its source data?