split data across sheets by HS611j

VIEWS: 16 PAGES: 6

									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.

								
To top