- In the source worksheet, select the cell you want to link to and click the Copy button on the Home tab. Or press Ctrl+C, or right-click and select Copy.
- Switch to the destination spreadsheet and click the cell where you want the link. Then, depending on your version of Excel:
- Excel 2007, 2010, and 2013: On the Home tab, click the down arrow below Paste and click Paste Link. In newer versions you may also right-click and select the Paste Link from the Paste menu.
- Excel 2003 and older versions: On the Edit menu, click Paste Special, and then click Paste Link.
- Return to the source worksheet and press ESC to remove the animated border around the cell.
- Given several Excel files for different divisions and within each division different kind of representativeIt would be problematic to have different files with different layouts. To avoid mistakes and corruption of the files by the sales representative I protected the worksheets and created validation list (when possible) or user form to let people type in correctly the information. Once customers were selected I asked to the users to post the file on a collaboration portal (it can also be fine to have all the files in the same directory).
- To collect and consolidate all the files I used VBA and Access. In particular I wasn’t interested in all the available informations in the Excel Files but just some of them, therefore I just focused on exporting cells containing typed-in values and unique customer number to match this information with master data.
- An easy formula that returns the quarter for a given date. There's no built-in function in Excel that can do this.
Explanation: ROUNDUP(x,0) always rounds x up to the nearest integer. The MONTH function returns the month number of a date. In this example, the formula reduces to =ROUNDUP(5/3,0), =ROUNDUP(1.666667,0), 2. May is in Quarter 2.
Content will be erased after question is completed.