User Profile
SnowMan55
Bronze Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: HOW TO: "If cell contains specific text display the immediate next word after it"
Regarding the thread (discussion), I would say "If in doubt, start a new thread (discussion). You can always include a link in your post that refers to the closely-related thread(s), if that is appropriate." You don't say which Excel product you are using (Excel 365, Excel 2019, etc.). If you are using Excel 365 or Excel for the web, the new TEXTBEFORE function handles this nicely: =TEXTBEFORE(" - ", A1) (Most spaces in my formulas are optional; I include them for improved readability. But in this case the spaces around the hyphen are appropriate.) Change the A1 reference if appropriate, and copy the formula down as needed. If you are using an older Excel product, this see the attached workbook.Re: Switch Panes Using F6 not working
According to the Microsoft document Keyboard shortcuts in Excel (the Windows tab): F6 F6 alone: switches between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split, F6 includes the split panes when switching between panes and the ribbon area. Shift+F6: switches between the worksheet, Zoom controls, task pane, and ribbon. Ctrl+F6: switches between two Excel windows. Ctrl+Shift+F6: switches between all Excel windows. Maybe the list of destinations has included the ribbon, etc., for some time (and you did not realize this because the sequence started with "other pane"), but the sequence of destinations has perhaps recently changed. (?)Re: COUNTIFS for multiple ranges
A simple technique is = COUNTIFS($A$2:$A$1000, "Douglas", $B$2:$B$1000, "<499", $C$2:$C$1000, "*") + COUNTIFS($A$2:$A$1000, "Douglas", $B$2:$B$1000, "<499", $D$2:$D$1000, "*") + COUNTIFS($A$2:$A$1000, "Douglas", $B$2:$B$1000, "<499", $E$2:$E$1000, "*") + COUNTIFS($A$2:$A$1000, "Douglas", $B$2:$B$1000, "<499", $F$2:$F$1000, "*") (Yes, it works without the extra spaces and even if you don't break it into multiple lines.) See the attached workbook for more information.Re: Monthy budget planner starting on 15th...?
This solution would I suppose be considered an intermediate level of complexity. The IFS function is not commonly used, and the LET function is fairly new. The important part is understanding the main cell formulas. To make it easier for you, I have added descriptive text in that regard into column C on the _Info worksheet. I added a bonus column on the Example worksheet, and have added links to information about conditional formatting and custom number formats at the bottom of the _Info sheet.Re: Excel - Add value based on colour
There are at least two possible sources of error: Data type - The code supplied by Logaraj Sekar sums into a Long variable. Long variables can only contain integer values. As your Total numbers include decimal values, maybe you have changed the data type; but I could speculate that your formulas there are calculating using the custom function SumByColor and are adding in some other values not dependent on color. Timing - A problem in calculating based on fill color (or text color or possibly other properties) that is set by a conditional formatting (CF) rule is that cell formulas (including the custom functions they use) are calculated first; then CF rule formatting is applied. So a change to a value in a cell will occur before the fill color might be changed. The latter is one reason I recommend against doing color-based selection (filtering). Instead, create some categorization values in helper cells (which can be hidden from view), and FILTER (or GROUPBY) based on those values.Re: Monthy budget planner starting on 15th...?
The conditional formatting (CF) rule should instead be the one where you always specify a formula (at least in Excel; I would guess Google Sheets supports that also). See the attached workbook for my solution, explanations, an assumption, and reference material.Re: NUMBER TO WORDS
Hans's formulas do not take advantage of the LET function. I wrote three alternative formulas that use the LET function to reduce the size of the formulas, improve calculation speed, and make the formulas easier to read. See the attached workbook. In my performance tests, each of the three alternatives had about the same performance. I like the shortest formula the best: =IFS(A3<0,"Referred cell value must not be negative!", A3>10^12-0.01,"Exceeds maximum which is 999999999999.99", TRUE, LET(cell, A3, use_proper_case, TRUE, text_int, TEXT(INT(cell),REPT(0,12)), dec_pt_posn, FIND(".",cell), hundreds, {"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "}, teens, {"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"}, units, {"","one","two","three","four","five","six","seven","eight","nine"}, hyph_units, {"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"}, words, TRIM(CONCATENATE( INDEX(hundreds, MID(text_int,1,1)+1),CHOOSE(MID(text_int,2,1)+1,"",INDEX(teens, MID(text_int,3,1)+1),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(text_int,2,1))>1,INDEX(hyph_units, MID(text_int,3,1)+1),IF(VALUE(MID(text_int,2,1))=0,INDEX(units, MID(text_int,3,1)+1),"")),IF(cell>=10^9," billion ",""), INDEX(hundreds, MID(text_int,4,1)+1),CHOOSE(MID(text_int,5,1)+1,"",INDEX(teens, MID(text_int,6,1)+1),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(text_int,5,1))>1,INDEX(hyph_units, MID(text_int,6,1)+1),IF(VALUE(MID(text_int,5,1))=0,INDEX(units, MID(text_int,6,1)+1),"")),IF(VALUE(MID(text_int,4,3))>0," million ",""), INDEX(hundreds, MID(text_int,7,1)+1),CHOOSE(MID(text_int,8,1)+1,"",INDEX(teens, MID(text_int,9,1)+1),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(text_int,8,1))>1,INDEX(hyph_units, MID(text_int,9,1)+1),IF(VALUE(MID(text_int,8,1))=0,INDEX(units, MID(text_int,9,1)+1),"")),IF(VALUE(MID(text_int,7,3))," thousand ",""), INDEX(hundreds, MID(text_int,10,1)+1),CHOOSE(MID(text_int,11,1)+1,"",INDEX(teens, MID(text_int,12,1)+1),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(text_int,11,1))>1,INDEX(hyph_units, MID(text_int,12,1)+1),IF(VALUE(MID(text_int,11,1))=0,INDEX(units, MID(text_int,12,1)+1),"")) )), IF(use_proper_case, PROPER(words), words) & IF(ISERROR(dec_pt_posn), IF(INT(cell)=0,""," and ")&"00/100", IF(INT(cell)=0,""," and ")&--LEFT(MID(cell,dec_pt_posn+1,2)&"00",2)&"/100") ) ) Note that I included an "option" (use_proper_case) for generating either proper-case text or lower-case text.Re: Order of multiple functions in a formula.
<< I am not getting UNIQUE values in the ROUTE column >> Correct. The UNIQUE function, when applied to a 2-dimensional range, returns a row for each "unique" (distinct, actually, by default) combination of values. The Microsoft documentation does a poor job of displaying this; even this Ablebits.com tutorial is weak on it. So if you do not want multiple rows, exclude the columns (via omission in the CHOOSECOLS function, most likely) that would result in multiple rows before you apply the UNIQUE function. In the sample data you show, the Departure Time is the same for Route 1DO2 in both entries,there is only one truck identifier, and it is used for both Shipping and Receiving, and at multiple Doors. OTOH, if what you want is seven lists of independent distinct values, one from each of the columns that stand next to each other, then your multi-column sort order criteria has no meaning. You could use seven formulas, but... See the changes in the attached workbook.
