Supasage2003
New Member
- Joined
- Feb 6, 2020
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hi guys. I'm trying modify a large number of existing formulas such that they are "wrapped" with a new "If" formula. I was hoping to use VBA code to do this.
Maybe I should explain what I'm trying to accomplish first.... I have two nearly-identical worksheets in my Excel workbook, which contains a financial model for a company. One of the worksheets contains the forecasted financial results for the company (let's call this the forecast sheet). The other worksheet contains the company's actual financial results (let's call this the actual sheet). I would like to create a drop-down menu in row 1 of my forecast sheet that gives me two options, "Forecast" and "Actual". If I select "Actual", the values in that column will pull the data from the actual worksheet. If I select "Forecast", the figures shown will be the result of the formulas that forecast the financials.
I already know how to create the drop-down menu, but can't figure out how to use VBA code to modify my formulas. As an example, I originally had this formula in a cell AS63 in my forecast sheet "=AS20+AS27". After manually modifying the formula for the drop-down list, it now reads "=IF(AS$6="E",AS20+AS27,IF(AS$6="A",Actual!AX68,"No Data"))". I would like to "wrap" this "If" formula around the rest of the formulas in the column, but can't figure out how to do this. Any help would be really appreciated!
Maybe I should explain what I'm trying to accomplish first.... I have two nearly-identical worksheets in my Excel workbook, which contains a financial model for a company. One of the worksheets contains the forecasted financial results for the company (let's call this the forecast sheet). The other worksheet contains the company's actual financial results (let's call this the actual sheet). I would like to create a drop-down menu in row 1 of my forecast sheet that gives me two options, "Forecast" and "Actual". If I select "Actual", the values in that column will pull the data from the actual worksheet. If I select "Forecast", the figures shown will be the result of the formulas that forecast the financials.
I already know how to create the drop-down menu, but can't figure out how to use VBA code to modify my formulas. As an example, I originally had this formula in a cell AS63 in my forecast sheet "=AS20+AS27". After manually modifying the formula for the drop-down list, it now reads "=IF(AS$6="E",AS20+AS27,IF(AS$6="A",Actual!AX68,"No Data"))". I would like to "wrap" this "If" formula around the rest of the formulas in the column, but can't figure out how to do this. Any help would be really appreciated!