Forum Discussion

Dharmendra_Bharwad's avatar
Dharmendra_Bharwad
Brass Contributor
Jun 10, 2020
Thanks for reaching out. As you can see there're separate Budgets and Actuals tab in sheet & they're coming from Financials tab only. Budgets and Actuals sheets are created separately to work out the difference between Actuals and Budgets as evident from formulas in "Variance" tab. Now, I don't need to have two separate tabs of Actuals and Budgets & I want to have figures coming directly from Financials tab to Variance tab. Does this makes sense?
peteryac60's avatar
peteryac60
Iron Contributor
Jun 10, 2020

Dharmendra_Bharwad 

 

Ok - I think I understand what you want and I think you nearly had the solution yourself!

 

I have added a second sheet (Variance Two) and in the lower half (shaded in yellow) I have extracted the data from the Financials sheet directly.  I have only done the FEB data and not the YTD data - but i think the same principles will apply.

 

hope this helps you !

 

good luck.

 

Peter

  • Dharmendra_Bharwad's avatar
    Dharmendra_Bharwad
    Brass Contributor
    Jun 11, 2020

    peteryac60 Thank you for working out on this and yes, you've understood what I need to achieve.

     

    However, I would appreciate any help extended for the second part as well. I've tried to work out for YTD figures but the formula is not working. 

      • Dharmendra_Bharwad's avatar
        Dharmendra_Bharwad
        Brass Contributor
        Jun 11, 2020

        peteryac60 I would've surely marked it as a best solution but it's not working as earlier.

         

        As you can see in earlier sheet, YTD Forecast column should update values depending on date entered into cell C4. Actual values upto the date entered into cell C4 should be taken (+) budget and forecast values for rest of the year. Suppose if date is 30/04/2020, then it should calculate actual values upto 30/04/2020 and Budget and forecast values from 31/05/2020 to 31/12/2020.

         

        Column L i.e. 2020 budget should always be constant and it should contain sum of figures from budget and forecast values i.e. except actuals for the whole year. That is to say it'll be constant irrespective of date entered into cell C4.

         

        Hope this is clear and let me know if anything is not clear from my side.

Resources