Copy One Spreadsheet to another with cells in a different format

plato

New Member
Joined
Jul 14, 2014
Messages
8
I want to know how to copy cells from one spreadsheet to another in a workbook where the second worksheet will put the data into different cells but on the same line. I tried to do this using the 'link' paste option but found I couldn't copy the 'formula to the next line of data and if I deleted the info on sheet 1 it stayed on sheet 2.
Example: On sheet 1 I have (A)Name: (B) Handicap (C) Index) but then on sheet 2 I want these to be (E)(H) (I). I want to be able to keep this as a template so I can delete and place information on sheet 1 (same info different details) and have it appear on Sheet 2. I may have 20/30 or 40 lines of data so need to know how to extend the line formulas down the page as needed
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

darkyxtreme

New Member
Joined
Oct 27, 2021
Messages
9
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi plato,

I think that would be as simple as going to the 2nd sheet and on cell E1 you input "=A1", then you can format cell E1 as you would like..
To avoid any headaches with broken formulas (#REF!) as you paste data all the time, i'd suggest when it's time to delete values from sheet 1 to do Clear Content.

Hope that helps
 

plato

New Member
Joined
Jul 14, 2014
Messages
8
All putting =A1 into cell E1 on sheet 2 is copy cell A1 (Sheet 2) into cell E 1 (sheet 2). I need to copy from 1 sheet to another.
 

darkyxtreme

New Member
Joined
Oct 27, 2021
Messages
9
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Yes I am sorry, did not write the correct one. it should be "=Sheet1!A1" inside the cell of sheet2.

Unless you want a VBA script to do this
 

plato

New Member
Joined
Jul 14, 2014
Messages
8

ADVERTISEMENT

Doing this I got the #REF error you mentioned. What I did try was to go to the Cell on Sheet 1 (which is incidentally called "Imported Spreadsheet" e.g. I went to A2 on this sheet and 'copy' then on the second sheet called 'Team Form' I pasted the link into D3 so in this the formula looks like: ='Imported Spreadsheet'!$A$30. Then I tried to copy the formula down the row by clicking 'copy' on d3 (sheet 2) and pasting below that cell and trying 'paste formula' but it did'nt work. I had hoped that once I got the first row done I could simply extend the formula down the sheet - but no go.
 

darkyxtreme

New Member
Joined
Oct 27, 2021
Messages
9
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Doing this I got the #REF error you mentioned. What I did try was to go to the Cell on Sheet 1 (which is incidentally called "Imported Spreadsheet" e.g. I went to A2 on this sheet and 'copy' then on the second sheet called 'Team Form' I pasted the link into D3 so in this the formula looks like: ='Imported Spreadsheet'!$A$30. Then I tried to copy the formula down the row by clicking 'copy' on d3 (sheet 2) and pasting below that cell and trying 'paste formula' but it did'nt work. I had hoped that once I got the first row done I could simply extend the formula down the sheet - but no go.
you could not copy down the formula because of the $ sign. Use formula "='Imported Spreadsheet'!A30" (without $sign, $ sign makes formulas static when you copy them. You can have for example $A2 so when you copy the formula right or left in columns it will always be static.)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,001
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top