Auto Fill, Every other column

M

MABeatty

I want to grab the values from one worksheet and display them in
another, but I only want every other value. Presently I'm using a
simple auto fil and then going back and manually changeing the column
letter.

Example
Sheet 1

5 10 4 2 5
2 3 4 5 6

Sheet 2
5 4 5
2 4 6
 
K

Ken Johnson

Hi, try this formula in A1 on Sheet2 then fill it across and down...

=INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN()*2-1))

Any blank cells on Sheet 1 in this range of cells will appear on Sheet
2 as zeroes. If that is not to your liking then use this formula...

=IF(ISBLANK(INDIRECT("Sheet1!" &
ADDRESS(ROW(),COLUMN()*2-1))),"",INDIRECT("Sheet1!" &
ADDRESS(ROW(),COLUMN()*2-1)))

If Sheet 1 has a different name such as Silly Sheet Name then use...

=IF(ISBLANK(INDIRECT("'Silly Sheet Name'!" &
ADDRESS(ROW(),COLUMN()*2-1))),"",INDIRECT("'Silly Sheet Name'!" &
ADDRESS(ROW(),COLUMN()*2-1)))

OR...

=INDIRECT("'Silly Sheet Name'!"&ADDRESS(ROW(),COLUMN()*2-1))

Ken Johnson
 
C

CLR

Hold down the Ctrl key and highlight the columns you wish to copy, then do
COPY, and Paste them into the new sheet.........they will be consecutive
there.

hth
Vaya con Dios,
Chuck, CABGx3
 
P

Paul Lautman

MABeatty said:
I want to grab the values from one worksheet and display them in
another, but I only want every other value. Presently I'm using a
simple auto fil and then going back and manually changeing the column
letter.

Example
Sheet 1

5 10 4 2 5
2 3 4 5 6

Sheet 2
5 4 5
2 4 6
Click to expand...

One way is to:
1) Transpose original data
2) Use Autofilter to show every second row
3) Copy the results of the filter to the other sheet
4) Transpose the new data
 
C

CLR

The "simple" ways are the only ways I know.....everything else makes my head
hurt......<g>

Tomorrow starts Hurricane Season...(sigh)

Vaya con Dios,
Chuck, CABGx3
 
Top