I've been working on this for a while unsuccessfully. Wondering if anyone knows a way I can create a simple report that is autopopulated with the data from a schedule. The report worksheet would need to be organized by division and AM/PM. Please see attached for further explanation. I've tried things like "Index(Match)", but it's too cumbersome and not automated. Hoping for a more sophisticated solution.
Try this Array Formula in D5
and copy down.
=IFERROR(INDEX(Schedule!$A$2:$A$39,SMALL(IF(Schedule!$B$2:$B$39="AM",IF(INDEX(Schedule!$C$2:$L$39,0,MATCH(D$2,Schedule!$C$1:$L$1,0))=D$4,ROW(Schedule!$A$2:$A$39)-ROW(Schedule!$A$2)+1)),ROWS(D$5:D5))),"")
Is this what you are trying to achieve?
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
That works brilliantly for the references in Row 4, which is very helpful. However, if I copy down to cell D9, do I have to manually change the reference to pull in "Pre-Op"? Or any other automated process?
OK now I understand your requirement. You mean when all women's are listed, next you would want to extract records for Pre-Op and then for Home/Onc and so on.
If you are trying to achieve this with the help of a single formula with your current sheet layout, the formula will be really lengthy one. Moreover for each dates there are different criteria set to extract the records. So you will need one very big formula for each column. Do you really want to proceed with this?
Try this Array Formula in D5
and drag down until you get blank cells.=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX(Schedule!$A$2:$A$39,SMALL(IF(Schedule!$B$2:$B$39="AM",IF(Schedule!$C$2:$C$39="Women's",ROW(Schedule!$A$2:$A$39)-ROW(Schedule!$A$2)+1)),ROWS(D$5:D5))),INDEX(Schedule!$A$2:$A$39,SMALL(IF(Schedule!$B$2:$B$39="AM",IF(Schedule!$C$2:$C$39="Pre-Op",ROW(Schedule!$A$2:$A$39)-ROW(Schedule!$A$2)+1)),ROWS(D$5:D5)-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Women's"))))),INDEX(Schedule!$A$2:$A$39,SMALL(IF(Schedule!$B$2:$B$39="AM",IF(Schedule!$C$2:$C$39="Heme/Onc",ROW(Schedule!$A$2:$A$39)-ROW(Schedule!$A$2)+1)),ROWS(D$5:D5)-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Women's"))-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Pre-Op"))))),INDEX(Schedule!$A$2:$A$39,SMALL(IF(Schedule!$B$2:$B$39="AM",IF(Schedule!$C$2:$C$39="UCC",ROW(Schedule!$A$2:$A$39)-ROW(Schedule!$A$2)+1)),ROWS(D$5:D5)-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Women's"))-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Pre-Op"))-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Heme/Onc"))))),INDEX(Schedule!$A$2:$A$39,SMALL(IF(Schedule!$B$2:$B$39="PM",IF(Schedule!$C$2:$C$39="UCC",ROW(Schedule!$A$2:$A$39)-ROW(Schedule!$A$2)+1)),ROWS(D$5:D5)-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Women's"))-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Pre-Op"))-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="Heme/Onc"))-SUMPRODUCT(--(Schedule!$B$2:$B$39="AM"),--(Schedule!$C$2:$C$39="UCC"))))),"")
Does this work for col. D?
Sorry for the delay, stepped out to lunch. That formula allows me to copy down the names in order, but I'm thinking it may be too cumbersome as the formula requires to be coded with the names of each department.
I certainly don't want you to have to spend any more time on this. As a last possible suggestion, does it make a difference if I don't care what order the divisions are listed in? That is, "Women's" doesn't have to come first. Is it possible to create some instruction along the lines of: For each unique Division in Column C, find all the Clients who are assigned to that division?
Also, attaching workbook containing two sheets of failed efforts where I took a solution to a different problem someone posted and tried to make it work for my data. You'll note that the client names are different, as I thought it'd be less confusing. May be easier to carry one of those two across the finish line.
Thanks so much for all your help. Really appreciate it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks