Using Offset and CountA to create a dynamic range in a chart

[Peter Davison]

Hi,
I'm trying to set a dynamic range in a chart
My header is in cell E16 and my data can be in cells E17 to E517 or more
For this example my data is only 200 cells created from formulas in the cells so I have 300 blank cells with formulas in.
I don't know how to make the dynamic range ignore the formula blank cells?
Any help would be appreciated.
Thanks

=OFFSET('Start Data1'!$E$16,0,0,COUNTA('Start Data1'!$E$17:$E$517)-1,1)

 

[Peter Davison]

Hi
I just wondered if anyone was able to help me on the query above.
Thanks, any help would be great.

 

[Anthony47]

I guess you use suc formula to create a NamedRange and then use named ranges to define the Chart source data.

Try using the following formula:

=OFFSET('Start Data'!$E$1,0,0,SUM(--(LEN('Start Data'!$E$1:$E$500)>0)),1)

Bye

 

[Peter Davison]

Thank you I will give it a go shortly.
Regards your help is greatly appreciated.

 

[Peter Davison]

Hi,
Sorry me again
I tried the code below (had to put row 17 in as the start row because I have other data in rows 1 to 16)
Unfortunately it didn't work and returned all the blank cells that have formulas in.
Do you have any other thoughts?

As you said I am using it as a named range in the chart.

=OFFSET('Start Data1'!$E$17,0,0,SUM(--(LEN('Start Data1'!$E$17:$E$500)>0)),1)

 

[Peter Davison]

Sorry I think it has worked I had to enter the named range again.
I'll do a few more to double check.
Thanks for all your help so far.
I'm a bit of a beginner

 

[Anthony47]

So I seem that eventually it worked (for now!); good

 

[Peter Davison]

Yes and thank you for your help, you have been most helpful.
I do have another question, if I can communicate it to you.

I have a row of data cells E16 to AW16 in sheet1 and another row of data in sheet 2 same cells E16 to AW16. They are all formula cells that populate data in a variable way.
So for example in sheet 1 I may have data in E16 to H16 and blan for the rest (all cells are formularised
and data in sheet 2 in E16 to H16 and blank for the rest.
I am creating a stack chart in 2 columns (Column 1 is sheet1 and column 2 is sheet2). This is in a separate Chart File called "Graph"
Each of the entries in sheet 2 are used against each entry in sheet 1, so the data looks like the attached image (Chart Data jpg and the chart result is Chart jpg).

What I would like to do is have a formula or vba macro that goes and selects the data from sheet 1 and sheet 2 and places it in column D in the "Graph File" without leaving any gaps in rows.
I can then place formulas in the cells to get the chart numbers?
Would you have any thoughts on how best I can achieve this?
Any help would be great.

 

[Peter Davison]

Sorry forgot the attachments

 

[Anthony47]

Sorry, I didn't understand neither how the source data are organized nor how the destination table should be arranged and which are the rule behind
Try to share a sample worksbook, maybe it woud help, and anyway will be a test bed for any possible proposal.

Bye