User Profile
Kendethar
Iron Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: Why is my SUMIF formula not working?
Just figured it out. It was reading the "<" as a value. Unfortunately, the VALUETOTEXT doesn't like working as the RANGE argument in SUMIF but I just changed all the <Unnamed #> to '<Unnamed #> and it works now with: SUMIF($A$5:$A$29,INDEX($A$5:$A$29,MATCH($F$5:$F$29,$A$5:$A$29,0),1),$C$5:$C$29)Why is my SUMIF formula not working?
In this sheet, there is a hidden formula that just displays "<Unnamed #"> if the user puts a number with no category name. My sumif formula works for categories that have the same name or single names but not if it's "<Unnamed #>". Each <Unnamed #>" is a unique number and I have the match function set to EXACT (0). Any thoughts as to why the sumif is summing everything (and not $15 in this case)? =IFERROR(IF($B7="","<Unnamed 3>",$B7),"<Unnamed 3>") =SUMIF($A$5:$A$29,INDEX($A$5:$A$29,MATCH($F$7,$A$5:$A$29,0),1),$C$5:$C$29)Re: Need Help - Conditional format keeps changing the number format when it should be CLEAR
I tried only circling the affected rules/info in the pictures because I have a lot of rules. Also, the affected rules are on top (the ones above it are applied to column B so are N/A here) so I don't think other rules are overwriting it. Esp. since column I is being changed to a date format due to the affected rules (as seen in the picture) when the only other conditional format rules as well as the cells themselves for I are formatted as "h:mm AM/PM" (time). All in all, the affected rules are just simply changing the number format when they should be set to Clear for everything but fill. Regardless, I'll message you the workbook as I have done before with you, and therefore, do trust you (as always though, please do not distribute and/or sell for profit). If you find the issue, please share here for everyone to see and your "best response" credit. Regarding the amount of rules. There are a lot rules and I really did try to have them as minimal as possible but there are a lot of things I want to happen.Need Help - Conditional format keeps changing the number format when it should be CLEAR
Greetings all, I have a sheet that has several columns of dates and a column for time, in a table. This workbook is for job searching. However, usually after opening the file, for some reason one of my conditional formats changes from the set number format I put. Happens regardless of whether the sheet is locked or unlocked. I very deliberately want the number format to NOT be set (or set to Clear) so it can apply to a range of various different number formats - changing the backcolor but not the number format or anything else. Why is it changing to Date (in this case, it has changed to General before too) when it should be permanently set to Clear? Notes: • The range $C$6:$Q$55 is: "Apply Deadline" through "Follow-up Action" (not shown in pictures but shouldn't be relevant) • All columns (like "Interview Time" in the picture above) are formatted with the proper number format in the cells themselves already. I also verified this - double checked. I would be very grateful for any solution to make this conditional formatting rule never change (namely Number format). Thank you!SOLVED - Split data in dynamic/spill array by delimiter
Greetings everyone, I have a calculation tool for Minecraft items that lists data from minecraft.fandom.com/wiki/Crafting and does various things with that data. I cannot get the queried data to spill properly because I don't know how. I have a query to minecraft.fandom.com/wiki/Crafting/Bulding_blocks (to name one) that excludes the "Crafting recipe" column and the "Description column" (in Power Query Editor), on a sheet called Crafting & Ingredients Query. The data's listings sometimes show a choice of materials in the game by using an " or ". The cells display this with character 10 from the website. I need a spill formula to separate and then continue the spill down for each occurrence of the " or ". I also need to show the same "Ingredient" on the right of those separations. On a test sheet in range $B$4, I have a spill formula that simply references and filters the blanks of the Name column from the query on the Crafting & Ingredients Query sheet ('Crafting & Ingredients Query'!$B$5:$B$2000). In in range $D$4 of the test sheet, I am attempting to make a spill formula that will 1), remove all instances of " or " (CHAR(160)&"or"&CHAR(10)) then 2), display each item after the cell's delimiter ("or") sequentially underneath in the spill, and then 3) display the same Ingredient next to each item of the items that are transposed down. Spill Formula so far (separates each item by the delimiter of Character 160, up to 10 delimiters for now): Current Formula (spill from range $D$4 - incomplete) =MID(SUBSTITUTE($B4:$B2000,CHAR(160),REPT(" ",LEN($B4:$B2000))),(TRANSPOSE(SEQUENCE(10,1)-1))*LEN($B4:$B2000)+1,LEN($B4:$B2000)) (Original formula credit: https://exceljet.net/formula/split-text-with-delimiter) Notes: • The =SEQUENCE() function is not dynamic (is just 10) and is temporary. Once each cell that has a delimiter is displayed underneath, the spill should be only 1 column wide. • I want the number of delimiters a cell can have to be dynamic (no limit - infinite, 1 or 7 or 100 or whatever). • Attached is the file with the relevant data. I would be very grateful for any solution to make this spill formula happen. Thank you!Re: SOLVED - Split data in dynamic/spill array by delimiter
SergeiBaklan That is exactly what I needed! Your genius formula splits text in a single cell by a delimiter ("or" in my case) and dynamically transposes it into a running spill array (even starts at the first string in the cell and inserts it where it appeared in the range - not all condensed at the top/bottom or something). Thank you so much! You're a gentleman and a scholar. Best of luck to you in all your Excel endeavors.
