Make data tables calculate automatically

[creid36]

Hi,

This is my first post so apologies if I don't describe the problem accurately enough!!

I am using data tables to perform price sensitivity. E.g if production=70%,80%,90% of actual production what would the cost per unit be.I have the tables working however, I need do do this for 30-40 products over the full year.Just now I have each month as a new tab(worksheet) on the same spreadsheet. Therefore, what I have done is copy and paste the data tables from April for May,June,July etc.

However, when I have done this the tables don't automatically calculate even though the production figures are different each month.

Is there a way of making each table calculate automatically for each month?Currently I am selecting the area, going to data then tables and selecting the cell.

Any help??

 

[dave3009]

Hi and welcome to MrExcel

I think I understand this do you perhaps want a pivot table?

You would select the whole dataset and go to Data>>Pivot Tables..>>Finish

You would then drag your date/month into report filter at the top of the pivot then your categories into row data the columns you want to total in column data and the costs into the field data.

From here you can then select the month you want to analyse from the report filter dropdown and each month will adjust accordingly. It also means you only have two pages, one for raw data and one for your report.

HTH


Dave

 

[creid36]

I'm not sure I am looking for a pivot table?

My layout just now is:

 

[creid36]

Product 1 Product 2 Product 3 etc...

70% production 3000/ £2.10 CPU
80% 4000/£2
90% 5000/£1.80


I basically want this layout for product 1-30 without having to click data-table-cell. Does that make sense?

Maybe you are right that i need a pivot table but I when I don't think I have my data set up correctly for the pivot table to work.

Thanks.

 

[Andrew Poulsom]

When you copy a data table the formulas that contain the TABLE function are converted to values. So you need to recreate the data table for each copy.

 

[creid36]

ok thanks guys.