Chart Question

D

David Hopper

is it possible to create a chart, skipping columns or rows

I need to creat a chart using data from every other column and another
using data from about every 5th row

Any help appreciated

David Hopper
 
G

glenton

I'm open to correction on this, but I believe the best way to do this
(assuming you don't want to delete the in between data, and resort the
columns somehow) would be to create a macro that copies the data you want
into the right format (probably on another sheet) and make the chart from
there.

Something along the lines of:

sub MyCopy()
dim NewSheet as worksheet
dim OldSheet as worksheet

set NewSheet = Sheets("name of you new sheet for copying to")
set OldSheet = Sheets("name of sheet you're copying from")

for i = 1 to rows
for j = 1 to cols

NewSheet.cells(i+3,j) = OldSheet.cells(5*(i-1) +r, 2*(j-1) +c)

next j
next i

end sub

(r and c are the row and column numbers of the first data you want copied
across, and NewSheet and OldSheet are the names of the sheets that you're
copying to and from respec.)

Hope this helps. If you've never used macros before you may need additional
help, and please save before running (as always with macros).

Regards
 
H

Herbert Seidenberg

This will create another array that shows only
every 2nd row and 5th column. Create a chart from that.
Assume your data looks like this:
13 14 15 16 17 18 19 20 21 22 23
14 15 16 17 18 19 20 21 22 23 24
15 16 17 18 19 20 21 22 23 24 25
16 17 18 19 20 21 22 23 24 25 26
17 18 19 20 21 22 23 24 25 26 27

Name it ArrayA.
Define two more names, like this:
Insert > Name > Define > Names in workbook: Rown
Refers to: =ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(ArrayA)))
Insert > Name > Define > Names in workbook: Coln
Refers to: =COLUMN(INDEX($1:$1,1):INDEX($1:$1,COLUMNS(ArrayA)))
Create another 5x11 array with this array formula
=ArrayA*(MOD(Rown,2)=1)*(MOD(Coln,5)=1)
It should look like this:
13 0 0 0 0 18 0 0 0 0 23
0 0 0 0 0 0 0 0 0 0 0
15 0 0 0 0 20 0 0 0 0 25
0 0 0 0 0 0 0 0 0 0 0
17 0 0 0 0 22 0 0 0 0 27

Now remove all the zeros.
Copy > Paste Special > Values
Edit > Replace > 0 > Replace All
Edit > GoTo > Special > Blanks
Edit > Delete > Shift cells left
Edit > GoTo > Special > Blanks
Edit > Delete > Shift cells up
The final array will look like this:
13 18 23
15 2 25
17 22 27
 
Top