I have limited skills with excel and currently I am copy pasting data for each month manually.
In 3rd Table "2021 Forecasting" I need dynamic table.
If we are in Feb 2022.
Then In 3rd table, I need data as below:
for Jan, I need actuals from "2021 Actuals" table
& from Feb to Dec I need data from "2021 Budget" table.
thus as you can see that both on "on "SUMMARY" sheet and on last table on "Units" the 2021 Forecasting table name is also dynamic.
as I just plugged in Jan 2021 Actual Unit values from "2021 Actual" table and remaining Units values from Feb to Dec is from Budget table, the dynamic name changes to 1 (Jan) to 11 (Feb to Dec)
I need your help with making this last table in range AJ to AV dynamic in a way that it fulfills below condition.
1 If Table name shows 1+11 then It should pull actual values for Jan from "2021 Actuals" and remaning values of 11 months for Feb to Dec from "2021 Budget" table.
2 If I change cell value on B6 "Summary" table then it should pull the 2021 Forecast table data accordingly.
i.e. If I put 2 in Cell B6 then 2021 Forecasting table should pull actual values for both Jan and Feb from 2021 Actuals and remaining 10 months (March to Dec) from 2021 Budget.
i.e. If I put 4 in Cell B6 then 2021 Forecasting table should pull actual values from Jan to April from 2021 Actuals and remaining 8 months (May to Dec) from 2021 Budget.
I saw some of the tutorials online and realized that, SUMPRODUCTS, OFFSET, INDEX and MATCH could be useful but as I mentioned earlier, I got lilmired skill set working with those formulas and was getting error.
Can you please help me with building formula so that my 3rd table in units tab becomes dynamics?
Thanks a lot in advance.
Bookmarks