Copying Formulas

M

Michael E. Gibson

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
 
L

Lori

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.
 
B

Bernie Deitrick

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
 
L

Lori

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

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.

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
Click to expand...
Click to expand...
 
M

Max

Another play is to use this in A1 (in Sheet2):
=INDIRECT("'Sheet1'!A"&ROW()*2-1)
Copy A1 down
 
M

Michael E. Gibson

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
 
Top