Hi,
I am attempting to use some arrays to sort out the details, but something is going haywire. I have been getting some assistance, but the Summary isn't populating correctly. I have been trying to solve this rubix cube for over a week now. Any help would be greatly appreciated.
Thank you,
Last edited by Nobleks; 09-29-2019 at 09:36 PM.
Here is a formula to sort and return a list of values that match the criteria:
Formula:
Please Login or Register to view this content.
Click the * to say thanks.
For some reason it wasn't happy with that formula. This is what I am at currently, I just need that pesky ledger account in the summary EB to sort from smallest ledger number to the largest. Suggestions?
Is there something odd going on with your latest sample file. I can't see the tab markers....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I noticed that. Here let me resave and reupload. That should fix it.
F2 of data sheet:
=LEFT(D2,4)+0
sort by that column. Problem solved??
Not really but many thanks for the effort.
Would you care to explain what "Not really" means??? It's hard to provide a fix with such a vague description.
My ultimate goal is for the sort on Column B5 and beyond on Summary EB.
Did you look at column B5 and beyond on Summary EB in the sheet that I posted, They look sorted to me....
Oop, sorry didn't see the attachment. Thanks greatly!!! (need more coffee)
God, you had me worried there!!
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
OK, so given that. My only concern is that if more data is added, then the user would have to know to sort. I'm trying to make it so they just add the data and look at the outcome. (I'm having to deal with people who aren't savvy with Excel. You should have seen the master file and what they were doing before I got started on it. )
Try it now.
But do look at the sheet!!!
I could cry if I were drunk and it were later in the day here. LOL Thank you so greatly!!! I knew some how aggregate was going the right way, it was just eluding me as to how. Oh man you are a rock star.
You're welcome. I had looked at this thread early this morning and was getting horribly bogged down with a very nasty formula. Eventually, I walked away from your problem.
It was only when i saw your explanation at Post 3 that I realised that i'd misunderstood your requirement completely....
Heya Glenn, is it odd to have the attached anomaly? 5310 only has one entry in the raw data for 73000 but for some reason it is acting like there are two based on the calculation?
I'm away from PC.... check for leading/trailing spaces around 5310. Is it on the sheet you posted?
yes sir. I'm thinkin it has to do with my formula for the data because it isn't doing any checking around it, just looking for matches. "=IF($B5="","",IF($C5="",IF(D$3<=$B$1,SUMIFS(Actual!$H:$H,Actual!$E:$E,'Master File'!$B5,Actual!$R:$R,'Master File'!D$3,Actual!$M:$M,'Master File'!$B$2),SUMIFS(Plan!$C:$C,Plan!$I:$I,'Master File'!D$3,Plan!$B:$B,$B5,Plan!$F:$F,$B$2)),IF(D$3<=$B$1,SUMIFS(Actual!$H:$H,Actual!$M:$M,$B$2,Actual!$R:$R,'Master File'!D$3,Actual!$V:$V,$C5),SUMIFS(Plan!$C:$C,Plan!$I:$I,'Master File'!D$3,Plan!$F:$F,$B$2,Plan!$M:$M,$C5))))" is what I am using ...just derpy simple sumifs based on whether or not there is data in C and B columns respectively and if the dates are bueno.
can you post a sheet showing the problem? My wee brain can't visualise a formula that size!!!
You got it. I am quite sure I could clean it up but it's month end reporting time and that was a quick throw it in jobber to get it done.
Do you think if I throw them into Sumproduct formulas that would eliminate the duplication? It's month end and my brain won't leave this alone but I also have to get the other reports done as well. ARGH!!!
OK. I forgot about you 'cos I normally look at a list of threads that I have posted in where i am NOT the last commenter. I see the problem, but don't yet understand it. I'm going away now for the night. But... can you just reply to this post.... anything... to make sure you don't vanish off the radar.... again.
Can we take a few steps backwards here??
I was looking at this for a while earlier today and was getting very confused. We need to strip this back to basics.
This fixes the duplication:
Formula:Please Login or Register to view this content.
however, walk me through what is supposed to happen if the D3>B1... the bit in red.
Yeah, I was thinking the same. And I thought that because this said solved, ...but now it doesn't lol. OK, so the design is that if D3's date is less than or equal to the current date (A1), then it should proceed to do the sumifs formula for the actual financials, otherwise it would do the sumifs formula for the forecast financials.
I "unsolved" it for you...
can you confirm which COLUMNS it should sum for the actuals. same, or different (apart from the sheet, that is...)? I'm a bit boggle eyed with this one, too!!
is it working for the < dates?
Now try adding 4 different reports that all need automating before they give you MORE lol. OK, so with this one, we are Using H column for the total dollars. E is the ledger account (B) on the summary page. S is the Spend category (C) on the summary tab. R is the period column (or D3) on the summary tab. And those are just for the actuals, but let's just focus on the Actuals as I can figure out the forecast later ^_^
Aha!! A moment of enlightenment. Your date fields were a complete disaster. I have reworked B1 and D3 to J3.
I think that ACT is working. However FCST is still wonky. It's making lots of silly returns (e.g. for 5430), as there is no "Supplier as Worktag" field in FCST. so.... what do you want to happen??
I have to go away shortly and will return to it later. In the meantime:
Check over the proposed changes
Check over the ACT results
See if inspiration strikes regarding the FCST.
LOL! Well...I figured I had more significant areas that were a disaster. Good to know it was fixable lol. And yeah...With Supplier as worktag in forecast. It does have one, but the file was teetering on size limitations, so I had to sacrifice. Thank you greatly though and I figure I can manipulate the formuals for actuals to rock the forecast. Going to TP the guy who wants it this ways house.
You are so awesome! OK, now what books do you suggest I read and/or classes I could take to get up to par with your awesomeness?
Last edited by Nobleks; 10-11-2019 at 10:12 AM.
You're welcome. I have no books and have been on mo courses for Excel. I just picked it up, here, mostly, from others helping me and (more recently) me helping others.TbH, it can be a bit of a time waster, but if you cant do what you want to do xcosof the weather, or have a bit of spare time.... it's a nice challenge. Some more than others, of course!!
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
OK, it's so minor and stupid but bugging me. For some deranged reason your file shows the ledger 5600 for cost center 1000...but mine doesn't. It's making me crazy.
I manually changed ALL sorts of data entries things while testing - mostly numbers, but.... I have NO IDEA if I changed that, too....
No worries! You rock star you. It is bueno now, ^_^
RoFLAO !!!!
Just curious Glenn, in July, August and September's data, the actuals have entries that weren't forecast. I'm trying to think if there is a way to add those items so that the system balances? I can't imagine it is possible because I would think the array has to be either one tab or the other not both.
Also, if I load the helper formulas into the 9+3 data...the ledgers get temperamental (this last time instead of supplies...it got temperamental with fringe), so should I just continue to pull the data, sort it and add it to the month tab in their respective spots to keep it from being a butt? I have to think about the other potential users of this file, that's why I was seeing if I should just link it to the 9+3 forecast tab or even try loading the formulas without sorting in ...should I just sort the forecast tab and add the formulas there??? Man ignorant users can be scary (ignorance isn't bad but rampant button pushing is...eeek).
"Stairs, they are always up to something."
Houston...it hates me. I tried to load a different data set into it....it decided to randomly exclude a ledger. I thought that maybe adding the formulas to the Forecast tab and sorting the Ledger account column would help. NOPE. Still is randomly excluding at least one ledger account...dangum file is so big. dangum!!!
Hi. I have been away a lot (work, travel, family issues.....). If you still have issues can you post a file showing where the problem(s) exist(s). If there are file size issues, save as an xlsb. The forumupload limit is around 9Mb...
I thank you for your time btw. Me thinks it was being derpy but it seems to be working better now???
Last edited by Nobleks; 10-22-2019 at 04:02 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks