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