Is there a way to have copy paste skip rows when copying a formula. Here's
what I am doing
Sheet 1 has data in
A1 data
A2 blank
A3 Data

On sheet 2 I need the data from A1 to go to A1 and the data from A3 to go
to A2 etc.
In an example Sheet 2 A1 reads "=sheet1!A1" Using copy makes the next line
read "=sheet1!A2". How can I make it skip to "=sheet1!A3" in A2?

Thanks
Mike

Select a blank cell in the column and then press Ctrl + [Space], Ctrl +
Shift + \ to select only non-blank cells in the column and then copy
and paste to new sheet.


Michael E. Gibson wrote:

Is there a way to have copy paste skip rows when copying a formula. Here's
what I am doing
Sheet 1 has data in
A1 data
A2 blank
A3 Data

On sheet 2 I need the data from A1 to go to A1 and the data from A3 to go
to A2 etc.
In an example Sheet 2 A1 reads "=sheet1!A1" Using copy makes the next line
read "=sheet1!A2". How can I make it skip to "=sheet1!A3" in A2?

Thanks
Mike

Mike,

In sheet2 A1, enter

=INDEX(Sheet1!A:A,ROW()*2-1)

and copy down as far as you need.

HTH,
Bernie
MS Excel MVP


"Michael E. Gibson" wrote in message
...
Is there a way to have copy paste skip rows when copying a formula. Here's what I am doing
Sheet 1 has data in
A1 data
A2 blank
A3 Data

On sheet 2 I need the data from A1 to go to A1 and the data from A3 to go to A2 etc.
In an example Sheet 2 A1 reads "=sheet1!A1" Using copy makes the next line read "=sheet1!A2".
How can I make it skip to "=sheet1!A3" in A2?

Thanks
Mike

To paste formulas: do as above but choose Edit Paste Special Paste
Link


Lori wrote:

Select a blank cell in the column and then press Ctrl + [Space], Ctrl +
Shift + \ to select only non-blank cells in the column and then copy
and paste to new sheet.


Michael E. Gibson wrote:

Is there a way to have copy paste skip rows when copying a formula. Here's
what I am doing
Sheet 1 has data in
A1 data
A2 blank
A3 Data

On sheet 2 I need the data from A1 to go to A1 and the data from A3 to go
to A2 etc.
In an example Sheet 2 A1 reads "=sheet1!A1" Using copy makes the next line
read "=sheet1!A2". How can I make it skip to "=sheet1!A3" in A2?

Thanks
Mike

Another play is to use this in A1 (in Sheet2):
=INDIRECT("'Sheet1'!A"&ROW()*2-1)
Copy A1 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michael E. Gibson" wrote in message
...
Is there a way to have copy paste skip rows when copying a formula. Here's
what I am doing
Sheet 1 has data in
A1 data
A2 blank
A3 Data

On sheet 2 I need the data from A1 to go to A1 and the data from A3 to go
to A2 etc.
In an example Sheet 2 A1 reads "=sheet1!A1" Using copy makes the next
line read "=sheet1!A2". How can I make it skip to "=sheet1!A3" in A2?

Thanks
Mike

Bernie,
I thought I could modify the Index and Indirect to work, but I can't. They
work fine on a new work sheet.
I should have explained further.
I am trying to move data from sheet named 'payroll entry' AI335, AI338,
AI341, etc to sheet named '28day report' H41, H42, H43, etc (AI334,AI337, AI
340 etc, holds data used for another field in the '28day report', and AI333
, AI336 and so on are used as text fields for headers below them)
Any Ideas
Thanks
Mike

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mike,

In sheet2 A1, enter

=INDEX(Sheet1!A:A,ROW()*2-1)

and copy down as far as you need.

HTH,
Bernie
MS Excel MVP


"Michael E. Gibson" wrote in message
...
Is there a way to have copy paste skip rows when copying a formula.
Here's what I am doing
Sheet 1 has data in
A1 data
A2 blank
A3 Data

On sheet 2 I need the data from A1 to go to A1 and the data from A3 to
go to A2 etc.
In an example Sheet 2 A1 reads "=sheet1!A1" Using copy makes the next
line read "=sheet1!A2". How can I make it skip to "=sheet1!A3" in A2?

Thanks
Mike