Forum Discussion
Formula Help
In sheet Lists in the attached file you can enter your priorities as shown in range C4:C20. The formula in the attached file displays the data ordered according to the values of range C4:C20.
Thank you, you pretty much nailed it. I do not know what caused that issue of data disappearance when certain codes were selected but I deleted the formula from the sheet and re-copied and pasted it and it worked as intended.
Many thanks for your support on this. I am just checking it out now to see if placement of things all considered are where they need to be. I don't know if there is a way to Prioritize Occ Statuses but if there is, I'd like to have Occ Status "V" starting at the top followed by Occ Status "D" and then Occ Status "TU" and after that Occ Status "IB" and after that Occ Status "IP" and after that Occ Status "RTI" and after that Occ Status "RTO" and after that Occ Status "OP", "OP BAH", "OP CNA", "OP DTS", "OP EAS", "OP ETP", "OP MAR", "OP PCS", "OP E4O3".
If there is a way to dictate the order of Occ Status it would be great.
In sheet Lists in the attached file you can enter your priorities as shown in range C4:C20. The formula in the attached file displays the data ordered according to the values of range C4:C20.
I rearranged the Occ Status list as you stated and it works just great. I have a question, I added a column on my ALL! sheet as I didn't realize I was missing a date column. When I did I found nothing on the Vacant Rooms! sheet except for #SPILL! was in cell A2 and all the data was missing. I deleted the column I added and every thing came back. What adjustments to the formula are needed for me to add my missing Date Column. I am wanting the new column in column F with a header of DOR.
Please help me out with this.
Many Thanks,
Carl
If i do this in the sample file the formula dynamically updates the results in the Vacant Rooms sheet. In the ALL sheet i've added column F with some sample data.
I intentionally created a #SPILL! error in cell A2 as you can see in the screenshot below (#ÜBERLAUF! in german Excel).
The reason for the #SPILL! error is that there is another formula in cell A3 as you can see in the screenshot below.
The spilled results of these formulas overlap and therefore return a #SPILL! error. You can delete any of these formulas to remove the error.
In the attached file i've added the DOR column F in the ALL! sheet.
Hello Oliver, first and foremost I want to thank you for your help with this formula. I personally would not have ever been able to come up with the formula that made this happen. I was wondering, because I have no idea what exactly this formula is doing, if you could assist me with one more formula very similar but somewhat different. Using the same data, but different cells, I need to do the same thing but create groups based on POSITION [POS] first and then sort those results based on the DOR. What is obvious here is the DOR date needs to be sorted in descending order. The thing about POSITION [POS] (which is what the groups are based on) is that the column on the ALL sheet shows an Acronym such as V1, V2, FC and so on but the Rate of the POSITION (defined on the Lists! sheet) is matched to the Rate on the Lists! sheet alongside the POSITION, which is Data used to sort based on the Rate. The result is to have all records on the ALL! Sheet that equates to 6, Group with all other 6 Ratings and within the group sorted in descending order based on DOR (Date of Rate), then spaced out just like the other formula, the next group will now equate to the 5, Group with all 5 Ratings and sorted in descending order by DOR and so on. The POSITIONS & RATES table can be found on the Lists! Sheet in cells M23:O60. The formula I need for this is to be placed on the 1010 BAH Wait List! in cell A2. Different Sections have different Positions and, in some cases, the same, so when it comes to the RATE Group, Different Positions will fall into the same Rate Group. So, I think the Position and the Rate has to be checked against the Section. You can also see in the POSITIONS & RATES table (Not a table though), that some Positions, within the Section, carry the same Rate.
I have provided minimum sample data in the attachment.
I hope I did not confuse you with this explanation.
Thank you,
Carl