User Profile
Carl_61
Iron Contributor
Joined Sep 30, 2021
User Widgets
Recent Discussions
Re: Formula help
I don't know how to attach the sample data. Please assist. This formula is in cell B8 and works but only displays the description for the 1st key number found in A8. The formula gets drug down from B8:B18. =IF(A8="","", LET( keyNum, A8, cleanKey, IF(LEFT(keyNum,1)="V", MID(keyNum,2,99), keyNum), idVal, 'Key Issue Form'!$D$6, roomVal, 'Key Issue Form'!$K$14, logKeys, 'Key Log'!$K$4:$K$1000, logIDs, 'Key Log'!$A$4:$A$1000, logRooms, 'Key Log'!$C$4:$C$1000, logTypes, 'Key Log'!$F$4:$F$1000, logStatus, 'Key Log'!$E$4:$E$1000, logDates, 'Key Log'!$D$4:$D$1000, matchRowsStrict, FILTER( ROW(logKeys)-3, (logKeys=cleanKey)*(logIDs=idVal)*(logRooms=roomVal) ), matchRowsRelaxed, FILTER( ROW(logKeys)-3, (logKeys=cleanKey)*(logIDs=idVal) ), matchRow, IFERROR(INDEX(matchRowsStrict, ROW(B8)-ROW($B$8)+1), INDEX(matchRowsRelaxed, ROW(B8)-ROW($B$8)+1)), keyType, INDEX(logTypes, matchRow), status, INDEX(logStatus, matchRow), transDate, TEXT(INDEX(logDates, matchRow), "m/d/yy"), roomUsed, INDEX(logRooms, matchRow), isSuite, IF(OR(RIGHT(roomUsed,1)="A", RIGHT(roomUsed,1)="B"), TRUE, FALSE), desc, IF(keyType="Swipe Key", IF(isSuite, "Combined Main & BDRM Door Swipe Key", "Main Door Swipe Key"), IF(isSuite, "Combined Main & Room Door Hard Key", "Main Door Hard Key")), desc & " - " & status & ", " & transDate )) This formula is the one in A8:A18 and it appears to be working: =IFERROR( INDEX('Key Log'!$K$4:$K$1000, SMALL( FILTER(ROW('Key Log'!$K$4:$K$1000)-3, ('Key Log'!$A$4:$A$1000='Key Issue Form'!$D$6)* ('Key Log'!$C$4:$C$1000='Key Issue Form'!$K$14)), ROWS(A$8:A8)) ), "") It is supposed to search thru the Key Log and find all transactions that match to the ID # and room number. It should show row by row, any transactions that match. So, A8 might show a swipe key number of 1960404 and a hard key number of 60. Based on the room number an appropriate description is supposed to be displayed in B8:B18. The problem however is, I'm not getting the discerptions for the keys in A8:A18 except for the B8:B18 description. I get things like #Calc!, #Ref, #N/A. Please help me understand how to attach the sample data. There use to be a button for attaching a file but I do not see that anymore. Carl3Views0likes1CommentRe: Formula help
I do not know how to attach the file. Please assist. This formula is in cell B8 and works but only displays the description for the 1st key number found in A8. The formula gets drug down from B8:B18. =IF(A8="","", LET( keyNum, A8, cleanKey, IF(LEFT(keyNum,1)="V", MID(keyNum,2,99), keyNum), idVal, 'Key Issue Form'!$D$6, roomVal, 'Key Issue Form'!$K$14, logKeys, 'Key Log'!$K$4:$K$1000, logIDs, 'Key Log'!$A$4:$A$1000, logRooms, 'Key Log'!$C$4:$C$1000, logTypes, 'Key Log'!$F$4:$F$1000, logStatus, 'Key Log'!$E$4:$E$1000, logDates, 'Key Log'!$D$4:$D$1000, matchRowsStrict, FILTER( ROW(logKeys)-3, (logKeys=cleanKey)*(logIDs=idVal)*(logRooms=roomVal) ), matchRowsRelaxed, FILTER( ROW(logKeys)-3, (logKeys=cleanKey)*(logIDs=idVal) ), matchRow, IFERROR(INDEX(matchRowsStrict, ROW(B8)-ROW($B$8)+1), INDEX(matchRowsRelaxed, ROW(B8)-ROW($B$8)+1)), keyType, INDEX(logTypes, matchRow), status, INDEX(logStatus, matchRow), transDate, TEXT(INDEX(logDates, matchRow), "m/d/yy"), roomUsed, INDEX(logRooms, matchRow), isSuite, IF(OR(RIGHT(roomUsed,1)="A", RIGHT(roomUsed,1)="B"), TRUE, FALSE), desc, IF(keyType="Swipe Key", IF(isSuite, "Combined Main & BDRM Door Swipe Key", "Main Door Swipe Key"), IF(isSuite, "Combined Main & Room Door Hard Key", "Main Door Hard Key")), desc & " - " & status & ", " & transDate )) This formula is the one in A8:A18 and it appears to be working: =IFERROR( INDEX('Key Log'!$K$4:$K$1000, SMALL( FILTER(ROW('Key Log'!$K$4:$K$1000)-3, ('Key Log'!$A$4:$A$1000='Key Issue Form'!$D$6)* ('Key Log'!$C$4:$C$1000='Key Issue Form'!$K$14)), ROWS(A$8:A8)) ), "") It is supposed to search thru the Key Log and find all transactions that match to the ID # and room number. It should show row by row, any transactions that match. So, A8 might show a swipe key number of 1960404 and a hard key number of 60. Based on the room number an appropriate description is supposed to be displayed in B8:B18. The problem however is, I'm not getting the discerptions for the keys in A8:A18 except for the B8:B18 description. I get things like #Calc!, #Ref, #N/A. Please help me understand how to attach the sample data. There use to be a button for attaching a file but I do not see that anymore. Carl5Views0likes0CommentsFormula help
I have the following formula that partially works. It processes this formula and returns the 1st match based on the formula criteria. =IFERROR( LET( KeyTypes,FILTER('Key Log'!$F$4:$F$309,(TRIM('Key Log'!$A$4:$A$309)=TRIM($D$6))*(TRIM('Key Log'!$C$4:$C$309)=TRIM($K$15))), KeyNums,FILTER('Key Log'!$K$4:$K$309,(TRIM('Key Log'!$A$4:$A$309)=TRIM($D$6))*(TRIM('Key Log'!$C$4:$C$309)=TRIM($K$15))), IF(INDEX(KeyTypes,ROW(A1))="Hard Key","V"&INDEX(KeyNums,ROW(A1)),INDEX(KeyNums,ROW(A1))) ),"") This formula resides in cells A8:A18 which is designed to return a key number, whether the key be a swipe key or a hard key. Cells B8:B18 are for descriptions of the key identified in cells A8:A18. This formula is supposed to find the 1st match on the key log and then find any other matches and place them in cells A8:A18. There is another formula for defining the description based on criteria. I need this formula to search out all transactions on the key log that match the ID number on the Key Issue Form in cell D6 with the cells in column A on the ID number and to also match the room number from K15 on the Key Issue Form with the room number in column C on the key log. There is something missing from this formula that is not allowing the other transactions to be found and listed on the Key Issue Form. Can anyone help me complete this formula? This is the formula for B8:B18 =IF(A8="","", LET( FilteredRows,FILTER(SEQUENCE(ROWS('Key Log'!$A$4:$A$309)),('Key Log'!$A$4:$A$309=$D$6)*('Key Log'!$C$4:$C$309=$K$15)), RowNum,INDEX(FilteredRows,ROW(A1)), Room,INDEX('Key Log'!$C$4:$C$309,RowNum), Status,INDEX('Key Log'!$E$4:$E$309,RowNum), Type,INDEX('Key Log'!$F$4:$F$309,RowNum), DateVal,INDEX('Key Log'!$D$4:$D$309,RowNum), IsAB,OR(RIGHT(Room,1)="A",RIGHT(Room,1)="B"), DoorDesc,IF(IsAB,"Combined Main & Room Door","Main Door"), KeyDesc,IF(Type="Swipe Key","Swipe Key","Hard Key"), DoorDesc & " " Can someone help me figure this out? Carl289Views0likes7CommentsRe: Key Issue Form Fill In
ID Name Room Date Out, In, Lost, Found, Defective Key Type Select Swipe Key 1st 3 Digits Select Swipe Key Number Hard Key Number Key Tag Number Hard Key or Swipe Key Num 58959988 GEVIDO 210B 05/27/25 Out Swipe Key 137 1371306 0 1371306 58959988 GEVIDO 210B 07/23/25 Out Hard Key 319 42 319 58959988 GEVIDO 210B 07/23/25 In Hard Key 319 42 319 58959988 GEVIDO 210B 07/24/25 Out Hard Key 319 42 319 58959988 GEVIDO 210B 07/29/25 In Swipe Key 137 1371306 0 1371306 58959988 GEVIDO 210B 11/11/25 Lost Hard Key 180 319 42 319 58959988 GEVIDO 210B 11/12/25 Out Hard Key 181 319 42 319 58959988 GEVIDO 210B 11/13/25 Found Hard Key 182 319 42 31926Views0likes0CommentsKey Issue Form Fill In
Hello, Community, I could use some help please. I am trying to have a Form filled in based on key issues in my Key Log. I have a sheet called "Key Log" and a sheet called "Key Issue Log". When transactions are made in the KEY LOG. I'd like to have those transactions show on my Key Issue Form. There are various cells that will need to be filled along with the key transactions. I have been working on what is called a Temporary Key Issue Form that currently gets filled out by hand, but I am trying to automate it. Our rooms use either Plastic Swipe Keys or Hard Keys to gain entrance into the rooms. Once these keys are issued out to an individual, they are required to acknowledge receipt of the keys, and this form is used for this acknowledgment. In excel, I have a sheet called Key Log where the keys get recorded to account for their whereabouts. In this KEY LOG, I have the following columns: ID, Name, Room, Date, (Out, In, Lost, Found, Defective), Key Type, Select Swipe Key 1st 3 Digits, Select Swipe Key Number, Hard Key Number, Key Tag Number, Hard Key or Swipe Key Number, Notes, Key Form/Lost Chrg Completed, Column 1 (Blank Column), Status(Helper Column), Swipe Card Prefix 1(Helper Column), Swipe Card Prefixes 2(Helper Column). The columns are in this order with these Headers in row 3 starting at A3. The data starts in A4 which is the ID number. I have a few issues I am trying to overcome and hoped you could help. The Temporary Issue Receipt is on a sheet called Key Issue Form. On this form, based on the ID number of the individual selected from a drop down in K6, the ID number is placed in cell D6 on the form. Doing so, see the formulas for D4 and A6. Starting in cell A8:A18 are the key numbers pulled from the KEY Log sheet transactions that are associated with the ID number & room number. In cells D8:D18 is where specific descriptions are to be listed based on the transaction on the Key Log sheet. For instance: on the Key Log sheet if the room number selected is for example Room 126 (3 numbers only) from column C, along with the date, Column D, the action of the transaction, Column E, (Out), the Key Type, (Hard Key) will pullup the Key Number, Column I & the Tag Number. Column J. The final result of the key number shows in column K. The key number would be in column A8. The Description for this example would be: Main Door Hard Key - Out, 2/9/25 If the selected key type was Swipe Key, it would say Main Door Swipe Key - Out, 2/9/25 If the room number is, for example, 236A, the description would be Combined Main & Room Door Hard Key - Out, 2/9/25 If the Key Type selected was Swipe Key, the Description would be Combined Main & Room Door Swipe Key - Out, 2/9/25 The description is built by the selections made in the Key Log. The type of transactions that are selectable are Out, In, Lost, Found, Defective. So that means different transactions are possible based on the action selected in column F on the Key Log. Examlpe: Main Door Swipe Key - Lost, 6/10/25, Main Door Hard Key - Lost, 6/10/25 Main Door Swipe Key - Found, 10/7/25. Remember, if the room number selected in column C on the Key Log is just 3 digits, the 1st part of the description would be Main Door. If the key number has an Alpha Character, such as A or B, The Description would start as Combined Main & Room Door with the rest of the description being defined based on the selections from the Key Log. The following formula in cell B8 is what I have but it's not doing the job: This formula and many other versions have been offered to me via Chat GPT but ChatGPT has not gotten it correct thus far. =IF(A8="","", LET( KeyNum, A8, ID, 'Key Issue Form'!D6, LogKeys, 'Key Log'!$K$4:$K$309, LogIDs, 'Key Log'!$A$4:$A$309, LogTypes, 'Key Log'!$F$4:$F$309, LogStatus, 'Key Log'!$E$4:$E$309, LogDates, 'Key Log'!$D$4:$D$309, LogRooms, 'Key Log'!$C$4:$C$309, MatchRows, FILTER(ROW(LogKeys), (LogKeys=KeyNum)*(LogIDs=ID)), LastRow, IFERROR(INDEX(MatchRows, COUNT(MatchRows)), NA()), Room, IF(ISNA(LastRow), "", INDEX(LogRooms, LastRow)), Status, IF(ISNA(LastRow), "", INDEX(LogStatus, LastRow)), DateVal, IF(ISNA(LastRow), "", INDEX(LogDates, LastRow)), Type, IF(ISNA(LastRow), "", INDEX(LogTypes, LastRow)), IsAB, IF(Room="", FALSE, OR(RIGHT(Room,1)="A", RIGHT(Room,1)="B")), Description, IF(Type="Swipe Key", IF(IsAB, "Combined Main & BDRM Door Swipe Key", "Main Door Swipe Key"), IF(IsAB, "Combined Main & BDRM Door Hard Key", "Main Door Hard Key") ), IF(Description="", "Unknown -", Description & " - " & Status & ", " & TEXT(DateVal,"m/d/yy")) )) Formula in Cell A8 =LET( MatchRow, SMALL( IF( ('Key Log'!$A:$A=$D$6) * ('Key Log'!$E:$E="Out") * ('Key Log'!$N:$N=""), ROW('Key Log'!$K:$K)), ROW(A1)), KeyType, INDEX('Key Log'!$F:$F, MatchRow), KeyNum, INDEX('Key Log'!$K:$K, MatchRow), IFERROR( IF(KeyType = "Hard Key", "V" & KeyNum, KeyNum), "" ) ) A6 formula: =IF(D6="","",INDEX(ALL!$I3:$I170, MATCH(D6, ALL!$H3:$H170, 0)) & ", " & INDEX(ALL!$L3:$L170, MATCH(D6, ALL!$H3:$H170, 0))) Formula in Cell D6 =IF(K6="","",VALUE(LEFT(K6,FIND(" -", K6)-1))) Formula in Cell D4 =IF(D6="","", INDEX(ALL!J3:J170, MATCH(D6, ALL!$H3:$H170, 0))) As an example, the individual shown on the Key Issue Form attached, has an ID number of 58959988 and room number 210B. If you were to filter room 210B you will see a number of transactions that should be showing up on the Key Issue Form with the various Descriptions in B8:B18 and the Key Numbers in cells A8:A18. I hope my lengthy description provides you at least most of the information you need to help me out. If not, please let me know your questions This is the Key Log Filtered to this one individual I don't know how to publish my example data and form Car;l75Views0likes1CommentRe: 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, Carl23Views0likes0CommentsRe: 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. Carl40Views0likes0CommentsRe: 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, Carl74Views0likes0CommentsRe: 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, Carl79Views0likes5CommentsRe: 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.97Views0likes7CommentsRe: 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.143Views0likes1CommentRe: 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. Carl58Views0likes9CommentsFormula 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. CarlSolved601Views0likes18CommentsWord 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, Carl693Views0likes5Comments
Recent Blog Articles
No content to show