• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Actual vs Budget-Depending on the Month selection, Sum Rows from different sheets.|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Actual vs Budget-Depending on the Month selection, Sum Rows from different sheets.|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Actual vs Budget-Depending on the M…
sp_PrintTopic sp_TopicIcon
Actual vs Budget-Depending on the Month selection, Sum Rows from different sheets.
Avatar
Kim Board
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 10, 2018
sp_UserOfflineSmall Offline
1
July 10, 2018 - 12:51 pm
sp_Permalink sp_Print

Appreciate the help 🙂 (This is my first time to ever post to a forum)

Actual-vs-Budget-Snip.PNGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Actual-vs-Budget-Snip.PNG (293 KB)
sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
July 10, 2018 - 1:49 pm
sp_Permalink sp_Print

Hi Kim,

An excel sample file instead of a picture would be more helpful, can't see your complete data structure.

You have worksheets named Jan, Feb, Mar...? They include the year (like Jan 18) or not?

See this article for another way of doing things: https://www.myonlinetraininghu.....ower-query

You can try a formula like this, it will add the data from the table you have:

=SUMPRODUCT((I4:T4="Actual")*(I2:T2<=X2)*(I7:T7))

It depends on the format you have for the Month-Year cells in row 2, if they are formatted dates or just text.

If you want to add data from multiple worksheets based on multiple criteria, then you should change the way you do things, you should agregate the data into a single table, it will be much easier to analyze it.

Avatar
Kim Board
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 10, 2018
sp_UserOfflineSmall Offline
3
July 10, 2018 - 2:40 pm
sp_Permalink sp_Print

I'm trying to upload the excel file.

I have 3 worksheets

1. The feeder worksheet (the picture you see).  This worksheet pulls the information by changing the drop-down in row 4 to either actual or budget.

2. Actual P&L numbers imported from Quickbooks automatically.

3. Budget numbers worksheet is in the exact same format that the controller changes often.

The Month headers are text.

Avatar
Kim Board
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 10, 2018
sp_UserOfflineSmall Offline
4
July 10, 2018 - 2:57 pm
sp_Permalink sp_Print

Thank you so much for your help.

What I want to do is select from the drop down in cell W2 and return in Column W the sum from Jan to month in W2 from the Actual worksheet. It has to be driven by the the month-year text because the words actual and budget is not on the actual and budget worksheets only the month-year text in row 2.

I'm sorry I know I don't explain things very well. I've turned my brain to mush trying to figure this out and yet it seems like it should be pretty simple.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
5
July 11, 2018 - 12:03 pm
sp_Permalink sp_Print

Hi Kim

See if this is what you wanted.

It is a bit difficult to come up with the solution as you did not give us the file with all the required sheets.

There are some merged cells and this complicate it even more. I had unmerged the cells in the header of my test "Actual" sheet.

What I am doing is to get the column number in the ACTUAL sheet that matches what you selected in cell W2.

I then convert this column number to the column alphabet and then concatenate it with the row number.

I then use the INDIRECT() function to get the result. Note that I have used the ROW() function as part of the formula so that the row number will change when you copy the formula downwards.

Hope this helps.

Sunny

Avatar
Kim Board
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 10, 2018
sp_UserOfflineSmall Offline
6
July 11, 2018 - 4:00 pm
sp_Permalink sp_Print sp_EditHistory

Thank you for your patience with me. I didn't know you could upload an entire worksheet.

I took your suggestions and removed merged cells.

My objective is still the same: I want to sum Year to date (Jan thru whatever date is selected in "Y1").  Actual year to date total in column "W" and Budget in column "Y". (Then in column "AA", I will use conditional formatting to show percentage difference with arrows :))

Again I apologize for not uploading the entire worksheet from the beginning.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
July 11, 2018 - 4:52 pm
sp_Permalink sp_Print

Hi Kim

My previous reply included an attachment with the formulas to get the result.

You will just need to modify them to suit your needs.

Sunny

Avatar
Kim Board
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 10, 2018
sp_UserOfflineSmall Offline
8
July 12, 2018 - 1:46 pm
sp_Permalink sp_Print

I ran into a snag. 

I used the index(match formula to pull the information because the row information my change on the "Actual" tab, but the columns do not. The "Actual" tab is populated from QuickBooks. (Of course that was not seen because I didn't have the entire worksheet uploaded).

Is it possible to use a formula that doesn't use a row index?

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
July 13, 2018 - 11:52 am
sp_Permalink sp_Print

Hi Kim

I am using a helper column (column W) in the ACTUAL and BUDGET sheet to extract the figures needed.

I then refer to them from the ACTUAL vs BUDGET sheet using INDEX and MATCH.

I had to convert your headers in both the ACTUAL and BUDGET sheet to date to allow the calculation.

I also converted your month selection in cell Y1 to a date in cell AD4 so that I am able to sum the required range.

Please note that I did not want to combine the formulas together in the ACTUAL vs BUDGET as it will make it very difficult to understand and amend.

Hope this helps.

Sunny

Avatar
Kim Board
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 10, 2018
sp_UserOfflineSmall Offline
10
July 13, 2018 - 3:32 pm
sp_Permalink sp_Print

Sunny you are awesome! and thank you for hanging with me and please don't give up!  Sorry my response time is not the best.  I think we are on different sides of the globe 🙂

On my way to bed but I will try what you have suggested first thing in the morning once I get to work.  I realize my demands for this spreadsheet may not seem realistic but I have my reasons: (I'll express what they are so it might help you understand why I have certain limitations.)

1.) I want to dazzle my boss, the owner of the Company, who is not excel savvy.  I have static reports in Power Query that he can update by just adding a file to the folder and he loves it, but this is a different animal.

2.) After talking to the owner he expressed to me how much appreciated a spreadsheet that he could easily add the current financials from Quickbooks and get the "actual" numbers for the month (any time during the month to see how the company is doing at that point) and compare it to a budget forecast that he can updated periodically.

The only real constraint I have is the "Actual" worksheet.  It's imported from Quickbooks with just a couple of clicks, and I don't think Quickbooks will import the month/dates and keep them formatted the way you need for this to work, but I will do all I can to make it work.  Also, please do not worry about making it too complicated for me to amend or understand.  I will learn whatever you come up with.  I love learning! 

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
11
July 14, 2018 - 9:26 am
sp_Permalink sp_Print

Hi Kim

Thanks for your feedback.

Hope you will be able to dazzle your boss Laugh

Cheers

Sunny

Avatar
Kim Board
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 10, 2018
sp_UserOfflineSmall Offline
12
July 18, 2018 - 2:00 am
sp_Permalink sp_Print

OK. I put all the help I received and this is the end product.  I think I dazzled 🙂

Need to fine tune a couple of things but other than that, I am very pleased.

I do need to figure out how to protect the "Actual vs Budget" Sheet, but still allow the user to select actual or budget from drop-down.

 

Again thank you!

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
13
July 18, 2018 - 10:14 am
sp_Permalink sp_Print

Hi Kim

Glad to know all is working fine.

To answer your question:

1) Select the range you want to unprotect (cells I3 to T3 in your case) on the Actual vs Budget sheet

2) Right-click on any of the selected cells and select Format Cells

3) Select the Protection tab and untick Locked and click OK. The selected cells are now unprotected.

4) To protect the sheet, right-click on it's tab and select Protect Sheet.

5) Enter a password (optional) that will allow you to unprotect the sheet later

6) Click OK

The sheet will now be protected but will allow you to select the drop-down from the unprotected cells.

Hope this helps.

Sunny

sp_AnswersTopicAnswer
Answers Post
Avatar
Kim Board
Member
Members
Level 0
Forum Posts: 8
Member Since:
July 10, 2018
sp_UserOfflineSmall Offline
14
July 18, 2018 - 10:57 am
sp_Permalink sp_Print

Perfect! Thank you 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: SALLY, Valentyn Kristioglo, Tiffany Kang
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27294

 

Member Stats:
Guest Posters: 49
Members: 31919
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.