Hello,
Is there a way to take a group of data and automatically move it to a new sheet based on a formula? For example, we have 24 different departments and right now we are manually moving each grouping to new sheets. I posted an example file. I would like to find a quicker way of separating the data into different tabs. I want to take column A,B,C,D and I,J,K and move them to a new tab. This is just for one department, but I'd like to do this for multiple.
Sheet 1 and 2 -
I'd appreciate any suggestions!
Is there a way to take a group of data and automatically move it to a new sheet based on a formula? For example, we have 24 different departments and right now we are manually moving each grouping to new sheets. I posted an example file. I would like to find a quicker way of separating the data into different tabs. I want to take column A,B,C,D and I,J,K and move them to a new tab. This is just for one department, but I'd like to do this for multiple.
Sheet 1 and 2 -
Example.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
4 | 64100--Administration | 67950-- Float Pool | 64100--Administration | 67950--CMG Float Pool | ||||||||||||
5 | Month Ending | Month Ending | Year To Date | Year To Date | ||||||||||||
6 | Actual | Budget | Variance | Actual | Budget | Variance | Actual | Actual | Budget | Variance | Actual | Budget | Variance | |||
7 | Revenue | |||||||||||||||
8 | Capitation | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
9 | Contractual Allowances | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
10 | Bad Debt Expense | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
11 | Total Net Patient Service Revenue | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
12 | ||||||||||||||||
13 | Other Revenue | |||||||||||||||
14 | Pay for Performance FFS | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
15 | Patient Medical Records Copying Revenue | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
16 | Operating Income - Management Fee | 172,917 | 172,917 | 0 | 0 | 0 | 0 | 0 | 1,902,087 | 1,902,083 | 4 | 0 | 0 | 0 | ||
17 | Other Income and Fees | 1,000 | 0 | 1,000 | 0 | 0 | 0 | 0 | 1,498,854 | 0 | 1,498,854 | 0 | 0 | 0 | ||
18 | Rental Income | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8,827 | 0 | 8,827 | 0 | 0 | 0 | ||
19 | SJP Revenue Adjustment | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
20 | Total Revenue | 173,917 | 172,917 | 1,000 | 0 | 0 | 0 | 0 | 3,409,768 | 1,902,083 | 1,507,685 | 0 | 0 | 0 | ||
21 | ||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D8:D10,N14:N19,K14:K19,G14:G19,D14:D19,N8:N10,K8:K10,G8:G10 | D8 | =B8-C8 |
B11:N11 | B11 | =SUM(B8:B10) |
B20:N20 | B20 | =SUM(B14:B19)+B11 |
Example.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Month Ending | Year To Date | ||||||||
2 | Actual | Budget | Variance | Actual | Budget | Variance | ||||
3 | Revenue | |||||||||
4 | Fee for Service | 0 | 0 | 0 | 0 | 0 | 0 | |||
5 | Capitation | 0 | 0 | 0 | 0 | 0 | 0 | |||
6 | Contractual Allowances | 0 | 0 | 0 | 0 | 0 | 0 | |||
7 | Bad Debt Expense | 0 | 0 | 0 | 0 | 0 | 0 | |||
8 | Total Net Patient Service Revenue | 0 | 0 | 0 | 0 | 0 | 0 | |||
9 | ||||||||||
10 | Other Revenue | |||||||||
11 | Pay for Performance FFS | 0 | 0 | 0 | 0 | 0 | 0 | |||
12 | Other Income and Fees | 0 | 0 | 0 | 0 | 0 | 0 | |||
13 | Meaningful Use - EHR Revenue | 0 | 0 | 0 | 0 | 0 | 0 | |||
14 | Interest Income | 0 | 0 | 0 | 0 | 0 | 0 | |||
15 | Total Revenue | 0 | 0 | 0 | 0 | 0 | 0 | |||
64100 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D4:D7,H11:H14,D11:D14,H4:H7 | D4 | =B4-C4 |
B8:D8,F8:H8 | B8 | =SUM(B4:B7) |
B15:D15,F15:H15 | B15 | =SUM(B11:B14)+B8 |
I'd appreciate any suggestions!