Make data tables calculate automatically

creid36

New Member
Joined
Oct 7, 2009
Messages
6
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??
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
365, 2016
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

New Member
Joined
Oct 7, 2009
Messages
6
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,463
Messages
5,468,791
Members
406,609
Latest member
cocobeans

This Week's Hot Topics

Top