View Single Post
  #1  
Old August 5th, 2010, 05:43 PM
jrsauer jrsauer is offline
Member
 
First recorded activity by OfficeFrustration: Aug 2010
Posts: 1
Default Cutting ans Pasting cell reference problem

Hey...I am working with two worksheets in excel 2007. Sheet one named Data has all of my data ... each data row is like;
A2-first name / B2-last name / C2-address / D2-city / E2-state / F2-etc

The second worksheet is a form I must populate with this data...it splits the information into two rows like
A2-first name / B2-address / C2-state
A3-last name / B3-city / C3-zip

When I put references to the data worksheet in the form worksheet, and try to copy and paste the formulas to successive rows in the form, naturally excel adjusts the pasted reference cells by two rows instead of one as desired. My not so glamorous solution is to put a blank row between each row of data on my data worksheet. Is there a better way to control the references of my pasted formulas on the form?

Ex. Form cells before copy and paste
='Data'!A2 / ='Data'!C2 / ='Data'!E2
='Data'!B2 / ='Data'!D2 / ='Data'!F2

After copy paste
='Data'!A2 / ='Data'!C2 / ='Data'!E2
='Data'!B2 / ='Data'!D2 / ='Data'!F2
='Data'!A4 / ='Data'!C4 / ='Data'!E4
='Data'!B4 / ='Data'!D4 / ='Data'!F4

Desired Result after copy paste
='Data'!A2 / ='Data'!C2 / ='Data'!E2
='Data'!B2 / ='Data'!D2 / ='Data'!F2
='Data'!A3 / ='Data'!C3 / ='Data'!E3
='Data'!B3 / ='Data'!D3 / ='Data'!F3

Last edited by jrsauer : August 6th, 2010 at 04:44 PM.