You are the cost accountant for an injected molding company that manufactures and labels plastic water bottles. You have 3 locations in 3 different countries with spreadsheets that are incompatible and can't use copy and paste 1) Link the three divisions into one workbook 2) relate the data from each division in a summary sheet 3) update the monthly dates for each quarter (=YEAR(A2)&"-Q"&INT((MONTH(A2)+2)/3) where A2 is the cell containing the date
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.
May 9th, 2015
Studypool's Notebank makes it easy to buy and sell old notes, study guides, reviews, etc.