Some people said I have to use scripts to solve this problem. I have explored the answers posted Excel Variables and Return empty cell from formula in Excel, but they don't seem to help. I have two sheets in Excel, one with formulae calculating business costs, and another where I'm trying to calculate revenue projections based on those costs. Images:
I'm trying to set it up so that the blank rows in the Projection sheet are dynamically calculated using the formulae from the Costs sheet, based on the Sales and Markup values input by the user.
I have also tried using Scenario Manager and Data Table options in Excel, but they didn't work. What is the simplest solution with / without scripts? Grateful for any advice on this.
Update: Here are further details.
The Costs sheet already has values for 'Sales' (D18) and 'Markup' (D26). What I'm trying to achieve is a formula that will automatically calculate the 'Variable Cost', 'Price', 'Revenue', and 'Profit' (E23, D27, E29, and E30, respectively, in the Costs sheet), based on values for 'Sales' and 'Markup' that a user inputs in the Projections sheet.