User Profile
SnowMan55
Bronze Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: Few cells in Date column is no formating
The problem is the data in the Date column of Table3—some of it is stored as text data, even though to the eye, it appears to be a date value. It would be best to "clean up" that Date column data, replacing those apparent dates with actual dates. Alternatively, for the most part, you could have your lookup formula convert the XLOOKUP return value, such as this: =DATEVALUE( XLOOKUP(D17,Table3[Consignment ID],Table3[Date]) ) But some of the data will then cause a conversion error. Note that "30-Sep-202" is missing the last digit of the year.Re: Adding Cell Value to file string
I gave three suggestions. Which did you try? In other words, show your formula (exactly). And which version of Excel are you using? (The CONCAT function has been available since Excel 2016, the TEXTJOIN function since Excel 2019. If you are using one of those functions with an older version of Excel, that would explain the #NAME? error.)Re: Looking up specific period financials from multiple period worksheet
This forum makes it difficult to see the images that are included in a reply. (It works OK for images in the initial post.) So I will ignore the conversation you have started with Peter. See the attached workbook for two other possibilities. Edit: The forum software again fails to retain the attached file. So...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.
