Dynamic Formula Based on latest column added (Budget Vs actual)

akash121

Board Regular
Joined
Apr 21, 2013
Messages
58
Hello experts,

I need your help to know what formula should I apply to get dynamic results.
In the attached spreadsheet I have sample data of my monthly report, where we have monthly actual column and remaining month forecast (M05 to M12).
I also have monthly budget and monthly forecast formula to show the variance from current month M04 actual cost. every month I have to manually move the cell reference of actual column to show the variance when compared to latest month, cell reference for Budget variance and Forecast variance column are fixed,
For example for next month I have to move the formula manually from M04 to M05 to show the comparison against latest actual for all the rows manually.

please advise is there any formula which can do this movement every month once M05 gets updated with actual next month? to put formula in column O and P?

Thank you
Akash

Book1
ABCDEFGHIJKLMNOP
1ActualActualActualActualForecastForecastForecastForecastForecastForecastForecastForecastCurrent BudgetCurrent ForecastVariance vs BudgetVariance Vs Forecast
2M01M02M03M04M05M06M07M08M09M10M11M12M04M04M04M04
310010010010110010010010010010010010010090-1-11
42001802001992002002002002002002002002001901-9
5250220250275250250250250250250250250250270-25-5
Sheet1
 

Attachments

  • Untitled.png
    Untitled.png
    22.8 KB · Views: 1

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,541
Office Version
  1. 2016
Platform
  1. Windows
Hi akash121,

Is this what you want?

Akash121.xlsx
ABCDEFGHIJKLMNOP
1ActualActualActualActualForecastForecastForecastForecastForecastForecastForecastForecastCurrent BudgetCurrent ForecastVariance vs BudgetVariance Vs Forecast
2M01M02M03M04M05M06M07M08M09M10M11M12M04M04M04M04
310010010010110010010010010010010010010090-1-11
42001802001992002002002002002002002002001901-9
5250220250275250250250250250250250250250270-25-5
Sheet2
Cell Formulas
RangeFormula
O3:P5O3=M3-INDEX($A3:$L3,AGGREGATE(14,6,COLUMN($A$3:$L$3)/($A$1:$L$1="Actual"),1))
 
Solution

akash121

Board Regular
Joined
Apr 21, 2013
Messages
58
Hi akash121,

Is this what you want?

Akash121.xlsx
ABCDEFGHIJKLMNOP
1ActualActualActualActualForecastForecastForecastForecastForecastForecastForecastForecastCurrent BudgetCurrent ForecastVariance vs BudgetVariance Vs Forecast
2M01M02M03M04M05M06M07M08M09M10M11M12M04M04M04M04
310010010010110010010010010010010010010090-1-11
42001802001992002002002002002002002002001901-9
5250220250275250250250250250250250250250270-25-5
Sheet2
Cell Formulas
RangeFormula
O3:P5O3=M3-INDEX($A3:$L3,AGGREGATE(14,6,COLUMN($A$3:$L$3)/($A$1:$L$1="Actual"),1))

Thank you so much sir, you are awesome :)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,541
Office Version
  1. 2016
Platform
  1. Windows
You're welcome!
Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,708
Messages
5,654,854
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top