Sunday, June 20, 2010

Excel: Pasting from one excel sheet to another; sequence pasting?

Ok, I basically have a sets of information in Sheet1 (5 rows, 2 columns in this example; but in my information I have 3 columns and 27 rows).



Name Sam



Hair Brown



Height Tall



Job Teacher



Kids None



Name Kim



etc



etc



etc



etc



In Sheet2 I want to pull the information out from Sheet1 in a different setup; I will have a few spreadsheets pulling only a few rows of information.



I know how to have one cell in Sheet2 equal info from Sheet 1, but I don't want to keep typing it in manually. For instance I want to have "A1" in "Sheet2" equal a person's name in "Sheet1" "A1". Next, "A2" in "Sheet2" will be the next name from Sheet1 A6 (the sixth row down from example above). I want to keep pasting in that sequence, 5 rows down at a time.



I have a ton of individual sets and it would take too long to do it manually, so how can I copy and paste them over in that fashion?



Excel: Pasting from one excel sheet to another; sequence pasting?

It's sort of hard to tell from the way you described it what you are really wanting or are trying to do. I sort of get what you are trying to do, but not totally. So I'll give you a couple different possible solutions to give a try. I'm not sure which would work best since they way you describe what you are trying to do is a little confusing.



To reference cells on another sheet just type in and equals sign followed by the sheet name then by an exclamation point followed by the cell address. So to reference cell A1 on Sheet1 from Sheet2 you'd type in:



=Sheet1!A1



If the sheet name has a space in the name. Then you'll need to suround the name in single quotes like:



='Sheet 1'!A1



You should be able to copy and paste the formula down the row and it will copy all of the data over.



If you are wanting to copy every fifth one, you might try using an AutoFilter to filter the list to just the data you want then copy it over. So select all of your data then go to Data %26gt; Filter %26gt; AutoFitler. Then on the drop down list for the column select (Custom...). Then either select Begins with or Contains and type in Name in the box next to it to filter the list to only the names. Then use Edit %26gt; Go To... and click the Special button so you can select only the Visible cells. Then copy those and paste those over to the other sheet. You can then return Sheet1 to normal by either selecting all from the drop down list for the column or by turning off the AutoFilter by going to Data %26gt; Filter %26gt; AutoFilter.



The last method you might try is the VLOOKUP function of Excel. It will look up the values on Sheet1 for you based on the values in Sheet2. I won't explain that in detail. For that you can go to Excel's help section. Two other Excel functions that work pretty similar are HLOOKUP and MATCH.



Excel: Pasting from one excel sheet to another; sequence pasting?

Copy the source cells



Paste as link

No comments:

Post a Comment

 
auto loan