Click on the destination cell when copying multiple cells of data, click on the cell in the top left corner of the. A moving black border will surround the selected cell (s). Take a look at the video and see the details of how the formula works. Click on a cell or multiple cells to highlight them. Right-click the cell or area where youd like to. My formula will automatically adjust the area where the formula is copied: Once youve opened the correct spreadsheet, paste the data into Excel. As the formula is continued to be copied down, it will then give the next cell, and the next cell and so on. So, the formula will copy what is in the cells on the "Cities" tab until it hits a blank, then it will run the second INDIRECT formula which will go to the top of the list of the "Cities" tab and return that result. If it is not blank, then just give me what's in that cell (Cities!A2). IF the cell referenced with the INDIRECT function (INDIRECT("Cities!A"&ROW(A2)) ISBLANK, then run this formula: INDIRECT("Repeat!A"&(ROWS($A$2:A2)-(COUNTA(Cities!A:A)-2))). Here I have a list of cities on a tab called "Cities":Īnd on a tab called "Repeat" I've created a formula to copy that list of cities and keep repeating that list for as many rows as I copy the formula down:
If you get a preview, look for the download arrow in the upper right hand corner. You can download the file here and follow along.
Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.In this tutorial I've created a formula that can retrieve a list of items, repeat that list, and automatically adjust if items are added to or deleted from that list.
Select the Create A Copy check box ( Figure C) and click OK.Ĭheck out the Microsoft Excel archive and catch up on other Excel tips.
Finally, you can use the 'Ctrl' plus 'C' keyboard shortcut to copy the data. Alternatively, select the 'Copy' option from the 'Home' tab in the toolbar. First, right-click and choose 'Copy' on the drop-down menu. From the Before Sheet list box, select (move to end). After you've highlighted the information, copy the data.Click the To Book drop-down list and select Mailing Lists.Click Move Or Copy from the shortcut menu.Open the second workbook containing the customer address listing for Cherry Hill on Sheet 1.Select the Values clipboard icon from the paste options. Click on the lower part of the Paste button in the clipboard section. Select the cell you want to copy the values into. To add another list of clients from another workbook - say, one containing records on customers from Cherry Hill, NJ - follow these steps: Select and copy the data you want to paste into your clipboard. Select the Create A Copy check box ( Figure B) and click OK.Įxcel creates a new workbook (Book1) and copies the data from the first worksheet to Sheet 1 of the new book.Click the To Book drop-down list and select (new book).Select Move Or Copy from the shortcut menu ( Figure A).Open the workbook containing the customer data.Using Excel’s worksheet copy/move feature, you can quickly create a workbook that contains only the information needed to address your weekly promotional mailings. However, you don’t want the marketing staff to work with the same workbook as the sales staff. You would like to use the information in the Excel workbook to send weekly reminders of weekly promotions to your clients. The second worksheet keeps a record of the clients’ sales transactions. The first worksheet lists the company name, address, and contact name and title. Let’s say you keep all records for your clients for the Philadelphia region in one Excel workbook. Quickly move data to another workbook without copy and pasteĮxcel offers a fast, convenient way to transport data from one workbook to another.