Log In
Register
Home
July 10, 2018
Offline
Appreciate the help 🙂 (This is my first time to ever post to a forum)
See Answer
November 8, 2013
Offline
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.
July 10, 2018
Offline
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.
July 10, 2018
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
July 10, 2018
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
July 10, 2018
Offline
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?
VIP
Trusted Members
June 25, 2016
Offline
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
July 10, 2018
Offline
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!
VIP
Trusted Members
June 25, 2016
Offline
Hi Kim
Thanks for your feedback.
Hope you will be able to dazzle your boss 
Cheers
Sunny
July 10, 2018
Offline
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!
VIP
Trusted Members
June 25, 2016
Offline
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
Answers Post
1 Guest(s)
