Hi everyone, in the attached file I have a simple task manager but based on audits, I am trying to find a formula that will show next audit due and then when its completed show when the next audit after that is due and so on and so forth, I just cant get my head around it, any help will be appreciated, im new so I apologise if I have posted wrong
Last edited by AliGW; 07-31-2021 at 05:45 AM. Reason: Typo in title corrected.
Welcome to the forum.
Which cells are we looking at? Some signposting, please!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Thank you for reply, sorry yes the cells in the I column show due dates, I am trying to get a formula in cell H23 ( currently shows overdue, please ignore the archive buttons as they archive completed audits and kinda messes the next audit due up anyway so may remove those, the cell F23 shows Audit reminders and H23 should show when next one is due after the initial one is completed and next after that one etc, Jeez I hope im making sense here. But if I mark the Ashgate audit as completed it still shows overdue because its a past date and I want it to show when the next one is due etc.
And where are we to find the audit dates to check against? Still not sure ...
Hi
The audit dates are entered in cells I3, I4, I5 etc. Cell 3 shows audit at ashgate due on Friday July 30, and next one is Derby UCC on Wednesday August 4, start dates are irrelevant just the due dates in Column I is what matters really
OK. Bear with me. I was born in Derby - I know the Nottingham area well.
So what happens when there are two due on the same date? I am sorry - I am still not clear what you want to see or exactly where. Is it a date? Is it a company?
Can you mock up how you would want it to look on a couple of key dates?
Last edited by AliGW; 07-31-2021 at 05:39 AM.
Hi yes of course, You perceive the next problem very well i.e. the audits on same day, but I can i guess live with that but essentially I am looking for cell H23 to show when the next audit is based on the dates in column I, Ashagte is one of our sites, so all places in Column B are different sites that need to be audited. the due date is essentially the date I have booked to carry out the audit, so cell H23 would show the date the next scheduled audit is except when I mark it as completed in column G the status column I want it to show when the next scheduled one is, which in this case would be Derby UCC on wednesday 4th August and if that gets marked as complete then the next scheduled one which is Derby Johnson Building also on 4th , initially I was hoping to get it to say for next audit the site as well as the date i.e Cell H23 would have said next audit Ashgate 30th July etc but that I think is asking a bit much, but if I could get it to show the next scheduled audit date it would be a help, thankyou for looking at it for me it must be frustrating from your point of view when people like myself ask questions thinking everybody knows instantly waht were after
Steve - this is all text. It's washing over me!
Please will you do as I asked and MOCK UP what you want to see, where you want to see it, on a couple of key dates. Do this manually and post the sample workbook again. I need a visual - you are not communicating what you want clearly in words - sorry.
I will look again once you've provided what I've asked for.
Hi I did a quick video on dropbox and put a link in but thats not allowed until I have done a few posts but that would explain clearly as I demonstrtaed it on video,
anyhow I have mocked up on the worksheet and tried again to explain, sorry im not getting this over very well I have added attachment with mark up
Thank you
Steve
I am really sorry, Steve - I would love to help, but I see no difference between the two workbooks provided. I do not see any mock-up of what you appear to be describing (and I would not have followed a link even if you'd provided one - I never do). If someone can't show me clearly in a workbook what they want to SEE, then I find it very hard to help. I'm not going to spend time on something that might be completely wrong.
I hope that someone else sees this and is able to interpret your requirements. Bear in mind that it's quiet here over the weekend, so you may have to wait until Monday. Sorry!
I have put out a call for help to the community - let's keep our fingers crossed.
Last edited by AliGW; 07-31-2021 at 06:59 AM.
Try
=INDEX($B$3:$B$16,AGGREGATE(15,6,(ROW($I$3:$I$16)-ROW($I$3)+1)/($I$3:$I$16>=TODAY())/($G$3:$G$16<>"Completed"),1)) & CHAR(10) &TEXT(INDEX($I$3:$I$16,AGGREGATE(15,6,(ROW($I$3:$I$16)-ROW($I$3)+1)/($I$3:$I$16>=TODAY())/($G$3:$GI$6<>"Completed"),1)),"dd -mmm-yyyy")
I am sure there is more "sophisticated" solution with 365 functions which will allow return of multiple audits with same date.
I suggest you consider having a separate sheet ("Dashboard" is popular for this!) for the information currently in rows 23 down.
Look at Column O
in O3
=INDEX($I$3:$I$16,MATCH(SMALL(IF($I$3:$I$16>=TODAY(),$I$3:$I$16),1),$I$3:$I$16,0))
Enter with Ctrl+Shift+Enter
in O4
=IFERROR(INDEX($B$3:$B$16,AGGREGATE(15,6,(ROW($I$3:$I$16)-ROW($I$3)+1)/($I$3:$I$16=$O$3)/($G$3:$G$16<>"Completed"),ROWS($1:1))),"")
Copy down
O3 gets the date of next audit
O4 down lists sites to be audited on date in O3.
Better than trying to "squeeze" everything into a single cell
And I would go easy on the "Technicolor" !!!!
Last edited by JohnTopley; 07-31-2021 at 11:25 AM.
John
I bow to the greater God, WOW that is exactly what I was looking for, you have made an old man a happy one. The sheet was just one sheet from a dashboard containing 30 sheets. I have all the tabs on one home page with links to the actual tab and the tabs automatically hide when unclicked so only the home page is visible using a deacticate tab macro. I am an absolute novice and researched all my formulas, I can now write basic IF formulas and IFS but thats it, yours is very complex to me and I would never have got that. I cant thank you enough John, have a great weekend
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks