Name Department Experience (yrs.) Rating Ashish Logistics 4 1 Sanjeev HR 5 2 Bhaskar HR 6 4 Rohit Admin 12 5 This range has been convered to Sanjay Logistics 8 2 range and pressing Crl+L (in Exc Ravi Accounts 5 3 Excel 2007 and Excel 2003). The Ajay IT 9 5 make the range auto expanding Mohit IT 12 1 data is added in rows below row formulas used in the various "D Reeta HR 2 2 automatically include the new r Pallavi Logistics 4 5 Mahesh Accounts 3 2 Mayur IT 2 3 Preeti HR 2 1 Supriya Logistics 2 3 The question here is to create a seperate tab for each department such that the output is dynamic for the following: 1. Changes in departments i.e. if the department changes, then the information should appear in the appropriate tab; 2. New data added i.e. for new data added (below row 17), the information should appear in the appropriate tab. For instructions on how to transfer information to seperate tabs, please refer the next sheet. This range has been convered to a List by highlighting the range and pressing Crl+L (in Excel 2003) or Ctrl+T (in Excel 2007 and Excel 2003). The purpose of doing so is to make the range auto expanding i.e. as and when more data is added in rows below row 17, the range in the formulas used in the various "Department" tabs, will automatically include the new rows added. Name Experience (yrs.)Rating This is the data for the Accounts Department. Ravi 5 3 sepeprate tabs for each department, simply cr Mahesh 3 2 copy of this sheet (right click on the sheet tab Copy > Create a Copy) and name that tab to H the same process for the other departments a All formulas are array formulas Ctrl+Shift+Enter as against the conventional En a for the Accounts Department. To create s for each department, simply create a eet (right click on the sheet tab > Move or a Copy) and name that tab to HR. Follow ess for the other departments as well. re array formulas - confirmed by er as against the conventional Enter. Name Experience (yrs.)Rating This sheet is a simple copy of the previous she Sanjeev 5 2 click on the sheet tab > Move or Copy > Create Bhaskar 6 4 After making an exact copy, change the tab na Reeta 2 2 Preeti 2 1 simple copy of the previous sheet (right eet tab > Move or Copy > Create a Copy). an exact copy, change the tab name to HR.
Pages to are hidden for
"split data across sheets"Please download to view full document