Excel Copy Paste While Holding Cell References Intact

datatechcorp
datatechcorp used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
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

Saurabh Singh Teotia
Top Expert 2015
Commented:
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
datatechcorp

Author

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
datatechcorp

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial