User Profile
Carl_61
Iron Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: Formula Help
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, Carl15Views0likes0CommentsRe: Formula Help
Hello Sir, Question? Yesterday my sheet worked super fine. Today, I'm getting #SPILL! in cell A2. I don't recall doing anything that would have caused this. The only thing I did was changed the Sheet name to "Occ Status By Status Code". Did this do something that needs changing somewhere else? I also added the DOR column for which I understand you accounted for this in your formula. Would you please assist me in getting this working again. Thank you, Otherwise, this solution is working great. Carl32Views0likes0CommentsRe: Formula Help
Oliver, I sent you a reply a bit ago but I'm not seeing it in these threads so I am sending it again. I was wondering if I can add a column on my ALL! sheet as I left out a Date Column. I need to insert this column in the column F position on the ALL! sheet. I noticed when I did this, all the data on the Vacant Rooms sheet disappeared and was replaced by #SPILL!. I deleted the column I inserted and every thing came back. How does the formula need to be adjusted to account for the new column on the ALL! sheet? Thank you, Carl62Views0likes0CommentsRe: Formula Help
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, Carl62Views0likes5CommentsRe: Formula Help
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.80Views0likes7CommentsRe: Formula Help
I also just noticed, when I select some of my Occ Statuses which are Data Validation picks such as OP, OP DTS, OP BAH, OP ETP, OP PCS, OP CNA & TU, all the data on the Vacant Rooms sheet disappears. Only some of the selections such as D, V, IB, IP, O, E4O3, RTI, RTO produce data on the Vacant Rooms Sheet.132Views0likes1CommentRe: Formula Help
This formula worked and its pretty much what I was trying to achieve. There are some issues though maybe you help me with. Is there a way to have the headers be a part of each group? Another words, whenever a group is separated, can the headers head each group? Can the Headers be of Bold Text? When cell on the ALL Sheet is blank, can it also be blank on the Vacant Rooms sheet? right now, all cells blank on the ALL Sheet are showing 0 in them. In my data, column J are dates of births and Vacant (V) and Down Rooms (D) don't have dates on the ALL Sheet and therefore the dates for Vacant and Down rooms are showing as 1/0/1900. Would rather just have these cells or any cells with dates, blanks or zeros, just be blank on the Vacant Rooms sheet. When there is a separation of Groups, can the Occ Status be displayed above the Headers for each group as follows: Group: VACANT. Group: Down. Group: IB and so on? Such as Column A, Group: In column B, VACANT. Then at the bottom of the Group, Column A, VACANT Total: then to the right of it, place the count #. IE: VACANT TOTAL: 5 DOWN TOTAL: 10 IB TOTAL:2 And so on. Remove the Group Count from column U Thank you for any other assistance you can provide. Carl51Views0likes9CommentsFormula Help
Hello Everyone, I have attached a sample of my workbook and here is what I am trying to accomplish. I have a Tab called ALL! (yes I know, will change it some day) that houses names and information about individuals living in dorm rooms I manage. Based on the headers on the ALL sheet I'm sure the Headers will speak for themselves to the data I have on record for individuals residing in the rooms. The main thing here however is that I am trying to Group Data based on [Occ Status] shown in the [Occ Status] column on the ALL sheet. I am wanting to have REAL TIME grouping. So if a change is made to the [Occ Status], on the ALL sheet, it will affect the group, add to or take away from, the group showing on the Vacant Rooms sheet. The Occ Statuses can be seen on the Lists sheet. As part of this grouping, I am also trying to have 2 spaces between each group with a group count of records showing within the Occ Status. In Name Manager, I have a named range called StatusList. On the Vacant Room sheet in cell A3 I have a formula that that returns #CALC! and I cannot figure out where the issue lies. =LET( headers, {"BLDG","WING","ROOM","NAME","GNDR","MAIN A/C","WINDOW A/C","PORTABLE","OCC STATUS"}, bldg, ALL!A3:A170, wing, ALL!B3:B170, room, ALL!C3:C170, name, ALL!D3:D170, gndr, ALL!H3:H170, mainAC, ALL!N3:N170, windowAC, ALL!O3:O170, portable, ALL!P3:P170, occStatus, ALL!T3:T170, data, CHOOSE({1,2,3,4,5,6,7,8,9}, bldg, wing, room, name, gndr, mainAC, windowAC, portable, occStatus ), blankRow, HSTACK("", "", "", "", "", "", "", "", ""), blankBlock, VSTACK(blankRow, blankRow), sectioned, MAP( StatusList, LAMBDA(s, LET( labelRow, HSTACK("Occ Status: " & s, "", "", "", "", "", "", "", ""), filtered, FILTER(data, occStatus = s, SEQUENCE(0)), IF(ROWS(filtered) > 0, VSTACK(labelRow, filtered, blankBlock), SEQUENCE(0) ) ) ) ), VSTACK(headers, sectioned) ) I used CHATGPT for the above formula but doing so is just not getting me the result I need. Can anyone help me out here. I am using MS 365, Version 2501, Build: 18429.20200 Thank you for all offers of support. CarlSolved466Views0likes18CommentsWord Mail Merge Filter Issue
Hello and thank you to all that can help me understand what if anything I am doing wrong here. I am trying to mail merge a document using data in my excel file for the merge. The issue is I want to filter out records based on some filtering criteria. With the document open I select "Edit Recipient List" and all the data pops into view. In the bottom section of the pop up there is the "Refine recipient list" with options of Sort, Filter, Find Duplicates, Find recipient & Validate addresses. I select "Filter" and a pop up comes into view that says "Filter and Sort". In the "Filter Records" tab I click the down arrow under "Field" and select "Date BO Letter Sent 2025" which is a column header/label in the sheet. In the "Comparison" field I select "Is not blank". I then select "And" as the operator. In the "Field" box next to And I select "Actual Response Date 2025" then under "Comparison" I select "Is Blank". Finally, I click "OK". While looking at the "mail Merge Recipients" list the data now showing appears to have filtered. The issue now is that when I click "OK", the data showing in the "Mail Merge Recipients" list is not the data showing for the document. I scroll thru the list on the screen and it not the filtered list I saw before clicking "OK". When I go back to the "Edit Recipients List" icon and click it, the "Mail Merge Recipients" pop up appears and I click on filter. When the "Filter and Sort" pop up appears another criteria has been added "OR" "Actual Response Date 2025" "Is blank". This keeps happening and I don't understand. As you can see, both Fields I am Filtering are Dates. I am just trying to Filter out Records that show a BO Letter was sent and no response to the letter has been received. The Filter and Sort pop up is adding additional criteria I am not asking for and messing up my results. Again however, this mess up appears to only be happening when I click "OK" to return back the the document that will be merged. I have selected some of the records like 1 thru 5 and documents are merging and printing but the ones I don't want are being included. I did this to test the results of my Filter request. Am I doing something wrong here??? To be clear, I am trying to Filter based on one field not being BLANK (Is not blank) "AND" the other Field being BLANK (Is Blank). Its like the Filter is being lost when I click "OK" on the "Mail Merge Recipients" pop up to get back to the document to be merged. Pulling my hair out, I need help please. Thank you, Carl478Views0likes5CommentsRe: Mail Merge to Email Addresses
I was hoping for a different result but I get it. I currently have individual records with individual email addresses for emails I stated here in. This gives me 4 separate columns plus the combined column of email addresses. I am doing a mail merge to all the personal emails, then a merge to work emails and so on. Is this what you are saying as the work around? It would be nice if this functionality was part of a future version. Carl441Views0likes1CommentMail Merge to Email Addresses
Need some help. I am using Excel as a data source and created word document templets for mail merging. With in the data source I have for columns for entering email addresses. Personal Email address Work Email Address Supervisor Email Address Section Email Address Combined Email Address When I need to send an email to an individual that also needs to go out to all the emails, I have joined the email addresses together using the following formula and have the combined email addresses in a column called Combined. =LET(email,TEXTJOIN("; ",TRUE,T3:U3,AS3:AT3),IF(email="","",HYPERLINK("mailto:"&email,email))) This works with no issues and am able to click on a combined email line and it launches the email program and drops the line of EMAIL ADDRESSES into the email TO: location. The issue I need help with, if possible, is the interaction with the combined email and word mail merge. When I try to do the mail merge and select the Combined Email Address column, word gives me an error, "Microsoft Outlook does not recognize " the list of combined email addresses". Is there a way for outlook to recognize the combined email addresses during the merge? Any help would be greatly appreciated. Carl653Views0likes5CommentsRe: Table to Range issue
Thank you Hans, That being said, I see there a variety of merge commands, one of which may help me here but I have no idea how to apply the appropriate one. I have a separate sheet with all my rooms and occupants. This sheet also includes the BO Start & End Dates. I need to run a mail merge against this sheet but only for the ROWS that have BO Begin Dates. I have like 98 BO Letters I have to merge and email. When I try to merge now, the merge wants to include all rooms and I do not need that. I need to exclude rooms that do not have a BO Start Date. Hence the need for an IF Statement of something. Can you help me out with this? Thank you, Carl52Views0likes1CommentTable to Range issue
Hello everyone, Could use some help. I have a workbook with many TABS. The main sheet in the workbook is a TABLE. This TABLE is Named "Room_Roster". When I made this sheet a Table, I did not realize it was going to mess up something being displayed on the room roster that was coming from merged cells on another sheet. Because of this I decided to convert the TABLE back to a RANGE. Upon doing this, another TAB was created called "Room_Roster" as a range. My issue is, while trying to perform a mail merge, the mail merge is looking at the Range Sheet "Room_Roster" instead of the Table defined as "Room_Roster". Thinking this was an issue, I renamed the RANGE to Room_Roster Non Table but the mail merge is still seeing the range as the data source even though I renamed it. I tried to delete the Room_Roster defined name from the Name Manager but it would not let me. I am stuck here and do not know what to do about this. Can I please get some ideas of what to do? Thank you, Carl91Views0likes3CommentsMail Merge Dates Issue
I have an Excel Sheet that includes dates. I have created a Word Mail Merge Document that includes the merging of dates and other information from the Excel Sheet. In the merge, all data other than the dates are merging correctly. The dates are merging in as numbers, regardless of switches. I do not understand why I am having so many problems with this date issue. The date clearly shows as a date in Excel yet its merging in as numbers. No matter what I try, I cannot get the Date to merge in as the Date shown in Excel. How can I make this work. Carl7.7KViews0likes4CommentsFormula Help
I manage buildings with 168 rooms. the occupants of these rooms from time to time are selected to be part of a weekly cleanup crew that are randomly selected. The formula below provides filters for this random selection. This formula works but I am having issues with analyzing if the outcome is correct. I could use some help from those that can see this more clearly than I. Here is the formula : =LET( n, FILTER(Room_Roster[NAME], (Room_Roster[NAME]>" ")*(Room_Roster[GNDR]=$E$3)*(Room_Roster[POSITION]<>Lists!L9)*(Room_Roster[Occ Status]="O")*(Room_Roster[BO Selected Date:]="")*(Room_Roster[Extra Duty Status Code]<>2)*(Room_Roster[Extra Duty Status Code]<>3)*(Room_Roster[Date BO Duty Completed]<=EDATE(TODAY(),+Lists!L7))*(Room_Roster[Room Assign/Check-in Date]<=EDATE(TODAY(), +Lists!L5))), count, ROWS(n), SORTBY(n, RANDARRAY(count))) Most of this is no problem but where I loose the logical path is where the EDATE Functions come into play. Do I have formula correct to achieve the desired outcome?. Here is the part in question: (Room_Roster[Room Assign/Check-in Date]<=EDATE(TODAY(), +Lists!L5) I am not sure if <=EDATE is correct or should it be >=EDATE and should it be +Lists!L5 or -Lists!L5. Lists!L5 is 6 months in this case. Do I have this formula correct? Do not select the individual unless TODAY() is =>Lists!L5 months from check-in date: Do not select if individual has not been checked in for at least 6 months or more. Another words, Do not select if the individual has not been checked in for more than, the number of months defined in Lists!L5. Lets say the number of months is 6. The number of months can be adjusted to less or more depending on the availability of people. This is why it is on the Tab Lists in cell L5. I am also trying to determine if another part of this formula is correct. (Room_Roster[Date BO Duty Completed]<=EDATE(TODAY(),+Lists!L7) Lists!L7 is 12 for 12 months. Trying to ensure once an individual has completed the duty they are not selected again for at least Lists!L7 (12 Months) from the date they completed the duty. Do not select again unless TODAY() - completion date is => Lists!L7. (12 Months) Do I have this part of the formula correct? The last issue I have is we don't want to select someone again if they are already selected but they have not completed the BO Duty. Do not select if "Selected Date" <> "" and Completed Date ="". Over all the objective is to randomly select someone for each Monday to be on the cleanup crew. Upon being selected, a selection date will be recorded and the individual will be provided with the Monday date they are to start on the cleanup crew. Once the assignment has been completed a completion date is recorded. Once a selection date has been recorded, the duty date will be in the future. When a random selection is being made, we don't want the same individual being selected again until after they have completed the Duty and the desired amount of time has passed, per the number in the Lists Tab has passed. I'd like to have, if possible, the formula clear the selection date and Completed date cells once the number of months defined in LISTS!L7 has passed so the individual goes back into the pool of potential selectees. Can anyone help me with whatever part of this can. Thank you, Carl66Views0likes1Comment
Recent Blog Articles
No content to show