spreadsheet function

[Guest]

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.

 

[Guest]

Hi. Excel GuRu;
Not sure if this answers your question, because it seems more elementary
than pivot tables:
You can write formulas that take data fom other sheets and even other
workbooks. From another sheet it would look something like this: =Sheet1!A1
And from another workbook: =[Book1]Sheet1!$A$1 (When you open a workbook
linked in this way to another workbook, it will ask if you want to update
links. If you click yes, it will get the latest data from the other book.)
The quickest way to create the latter formulas for a whole column is to copy
the column containing the data, switch to the other workbook, and then it's
Paste Special> Paste Link.
So your departmental sheets could include such cells for comparison, and
your future company-wide budget could be a sum of department budgets.
Regards,
IanRoy

 

[Guest]

I wasn't familiar with the paste link function, but don't know that that
answers the question.


In sheet 1 we have all our data. Column A includes the dept, where as,
columns, b,c,d,etc. include the numerical data.

In sheet 2, could you use a there a lookup function that would return all
rows(including data in cells in various columns (b,c,d,e,f,g,etc.) where the
dept(in column a) = 4000.

In sheet 2, you might gather all data for dept 5000. It would take some
time to set up, but would be easier than cutting and pasting.

Let me know what you think!

Hi. Excel GuRu;
Not sure if this answers your question, because it seems more elementary
than pivot tables:
You can write formulas that take data fom other sheets and even other
workbooks. From another sheet it would look something like this: =Sheet1!A1
And from another workbook: =[Book1]Sheet1!$A$1 (When you open a workbook
linked in this way to another workbook, it will ask if you want to update
links. If you click yes, it will get the latest data from the other book.)
The quickest way to create the latter formulas for a whole column is to copy
the column containing the data, switch to the other workbook, and then it's
Paste Special> Paste Link.
So your departmental sheets could include such cells for comparison, and
your future company-wide budget could be a sum of department budgets.
Regards,
IanRoy

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.