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

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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)
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Hi
I just wondered if anyone was able to help me on the query above.
Thanks, any help would be great.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,941
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:
VBA Code:
=OFFSET('Start Data'!$E$1,0,0,SUM(--(LEN('Start Data'!$E$1:$E$500)>0)),1)

Bye
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Thank you I will give it a go shortly.
Regards your help is greatly appreciated.
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 29, 2006
Messages
1,941

ADVERTISEMENT

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

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Sorry forgot the attachments
 

Attachments

  • Chart Data.JPG
    Chart Data.JPG
    215.5 KB · Views: 1
  • Chart.JPG
    Chart.JPG
    100.9 KB · Views: 1

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,941
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,613
Messages
5,549,006
Members
410,888
Latest member
leap out
Top