Have a spreadsheet showing actual financial data for a number of periods by
department and account for the entire company.

Want to divide the data by data to department and distribute to managers to
complete the forecast for the current period.

Is there functional way(other than cutting and pasting) to take a sheet and
divide the data in multiple sheets? We want them to be able to enter data
for the budget period and have the prior periods as comparison. Want to be
able to calculate the total variance between their current budget and the
original budget.

The pivot table function "show pages" is similar to what I want. I tried
that, thinking that they could enter their current forecast along side the
pivot table. This works with two exceptions, 1)they would be unable to add
any rows since the data is in the pivot table, it would be cumbersome to
calculate the variance compared to original budget since the current budget
is off of the pivot table and the original budget is included on the pivot
table.

Is there a function that keys off one column and every time it changes will
push the data to a new spreadsheet? Kind of link how you key on a change in
data when using the subtotal function.

Maybe I'm extremely wishful. Looking to streamline a cumbersome process.
Let me know your thoughts.