Excel Copy Paste While Holding Cell References Intact

HELLO...

I have an immediate need, for a tip to use...when copy/pasting in one row to the next.  I've just got to believe there's a way to do this.

In a given spreadsheet, I have the following formulas in COLUMN-D:

D1-->  MIN_QTY (not a formula...only a Column Heading)
D2-->  =Sheet2!D2
D3-->  =Sheet2!F2
D4-->  =Sheet2!H2
D5-->  =Sheet2!J2
D6-->  =Sheet2!L2

What *MUST* occur...is that in rows 7-thru-11, I need to end up with the following:

D7-->  =Sheet2!D3
D8-->  =Sheet2!F3
D9-->  =Sheet2!H3
D10-->  =Sheet2!J3
D11-->  =Sheet2!L3

...and so forth...repeating this paste strategy...for the next 5,000 rows!  I thought of using some sort of Transposing method, but I'm getting a bit lost with that.  This is only an example...and will apply to COLUMNS A, C, and G as well, in the current Sheet.

The problem is, that I *cannot* alter the source data (i.e. "Sheet2"), as this comes directly from a database report extraction.  I also thought of using some sort of clever use of the "$" character, to hold the relative positions in check, but that won't work...or, I'm just using it wrong. :-)

Please HELP!  I'm stuck, and I must get this project done today for an Executive.  I'd truly appreciate any input here...THANKS!...Mark
datatechcorpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Instead of pasting, use VBA and loop through and generate the formulas...

Sub GenerateFormulas()
    Dim rw As Long, i As Long
i = 2
For rw = 2 To 5000 Step 5
    Cells(rw, "D").Formula = "=Sheet2!D" & i
    Cells(rw + 1, "D").Formula = "=Sheet2!F" & i
    Cells(rw + 2, "D").Formula = "=Sheet2!H" & i
    Cells(rw + 3, "D").Formula = "=Sheet2!J" & i
    Cells(rw + 4, "D").Formula = "=Sheet2!L" & i
    i = i + 1
Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
Use this formula...In D2

=OFFSET(Sheet2!$D$1,CEILING((ROW()-1)/5,1),((ROW()-(CEILING(ROW()-1,5)-4))*2)-2)

Open in new window


and simply drag this...

Enclosed workbook for reference...it does what you are looking for...

Saurabh....
Copy.xlsx
datatechcorpAuthor Commented:
Thank you both *SOOOOOO* much for responding.  I'm going to try both suggestions, and will award the points by day's end.  It's *VERY* much appreciated!...Mark
datatechcorpAuthor Commented:
I'm awarding you both the points...as both of your solutions were fast and fabulous!  Thanks for chiming in guys...again, it's most appreciated!...Mark
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.