Tryig to answer 3 questions about spreadsheets and how to update monthly dates

Business & Finance
Tutor: None Selected Time limit: 1 Day

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

Apr 25th, 2015

  1. 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.
  2. 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.
  3. Return to the source worksheet and press ESC to remove the animated border around the cell.
  4. 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). 
  5. 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.
  6. An easy formula that returns the quarter for a given date. There's no built-in function in Excel that can do this.
Quarter Formula in Excel

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

Did you know? You can earn $20 for every friend you invite to Studypool!
Click here to
Refer a Friend
...
Apr 25th, 2015
...
Apr 25th, 2015
Dec 7th, 2016
check_circle
Mark as Final Answer
check_circle
Unmark as Final Answer
check_circle
Final Answer

Secure Information

Content will be erased after question is completed.

check_circle
Final Answer