Suppress cell calculations until data is entered in another cell

[Marty O'Connell]

Okay, my first question:

I have a simple 14 column, 5 row spreadsheet working with our company's monthly net income for the year.

The columns are: column 1, the row labels; columns 2-13, the months of the year (January - December); and column 14, "TOTAL."

The rows are: Budgeted NI, Actual NI, Monthly Budget Variance, Total Cumulative Variance, and NI Needed To Meet Annual Goal.

The Budgeted row contains the monthly budget figures for each month, plus the total of these numbers in the final TOTAL column. These are static numbers entered at the beginning of the year (except for the TOTAL, which is simply a sum of the 12 numbers).

The Actual NI row is an input row, into which we enter the respective months' actual NI figure. The TOTAL column has the total of the entered figures.

Okay, the last three rows are all calculated. I don't want anything to display in the cells of the months that don't have anything entered into the respective months' Actual NI cells.

So, at the beginning of the year, there will only be one row of figures displayed - the budgeted NI for the year for each month and their totals in the far right column. When we receive January's financials, we enter January's NI, and voila! January's Monthly Budget Variance, Total Cumulative Variance, and NI Needed To Meet Annual Goal magically appear!

Can this be done?

One more thing. I would like the TOTAL for Monthly Budget Variance to display the average monthly budget variance of the months that have an Actual NI entered in.

Can this also be done?


I appreciate you taking the time to read through this, and look forward to your expert help!

Thanks,

Marty

 

[Hui]

Marty

You could use a combination of Formulas and Conditional Formatting to do this

Add all the data
The Totals will need to be setup to only add up values in the budget and actuals where the Actuals exist

Then use CF to set the foreground color of the Budget cells to white where the Actual doesn't exist

that way as data is added to the Actuals the budget numbers will show and the calculations will reflect the revised Months and YTD data

If you posts a sample file we can supply a more specific solution

 

[Marty O'Connell]

Thanks for the reply, Hui! I have attached the file, with the first two month's information included. It's dummy information.
Again, I'm only wanting the info in the last three lines (for the month columns) to appear when there is an entry in the Actual row. And, I want the Monthly Budget Variance cell in the TOTAL column to display the average of the displayed monthly figures.

Thanks!

Marty

 

[NARAYANK991]

Hi Marty ,

See if this is OK.

Narayan

 

[Marty O'Connell]

Narayan and Hui,

Thank you SO much for your help. This is exactly what I was looking for. I am going to study what formulas and formatting you used and add them to my Excel arsenal.

With Much Appreciation,

Marty