User Profile
SnowMan55
Bronze Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: Welcome to the Excel Community
Yes, that might be handled with a single formula on your Single worksheet. It might be handled like this (depending on your version of Excel, which you should mention when posting): =SORT( VSTACK( 'Company Type 1'!A2:.A999, 'Company Type 2'!A2:.A999, 'Company Type 3'!A2:.A999, 'Company Type 4'!A2:.A999 ) ) Naturally, you would use your worksheet names, and change the column letter from A to whichever column contains the company names (it can be different for each worksheet). And by using a "higher" letter right after the colons, you can retrieve multiple columns at once. VSTACK info The periods immediately after the colons reduce the ranges to however many rows contain data, as described here; just remove them if your Excel version does not support that. In the future, please do not add your question/issue/problem to the end of an unrelated post such as this. Instead, click the big blue "Start a Discussion" button near the top of the main page to start your own message thread.27Views0likes0CommentsRe: Formula to retrieve data from several sheets and return values from given criteria.
I agree that your formula structure works if the formula is entered correctly. But do you actually need/have the staff member's name entered twice on the source worksheets (both columns C and D)? See the attached workbook for more information. Edit: The forum software is "losing" attachments frequently. Trying again... Edit: The attachment was "lost" again. Including a link to OneDrive instead: 2025-12-11 SWEX staff location by day.xlsx91Views0likes1CommentRe: Drop-Down List + IF function
I do not know the answer to that. I re-built the formula (based on mathetes' revised workbook), and it works as intended. For the benefit of those who may refer to this thread, I decided to post a more complete design, with alternatives. See the attached workbook. Edit: Once again, the forum software loses, the attached file. Trying again...33Views0likes0CommentsRe: Need help with conditional fomatting formula based in text on cells
While others have posted basic formulas, it occurs to me that they may result in "false positives". I immediately thought of the INC in LINCOLN (whether a given name such as Lincoln Chafee or a surname). Other people who would be incorrectly highlighted: Prince R. Nelson (yes, "the" Prince) Linda Prince (film producer and director in independent cinema) anyone with a surname of Finch I don't know if you are attempting to select all businesses (and even all organizations) in your data. These would be highlighted: Incarnate Word Church (not their exact title, but you get the idea) Incredible Weddings Agriculture & Clean Energy Incubator (Texas) Inca Design (UK) And other hypothetical businesses also (I made up these; no guarantee that they do not exist): Principal Realtors It's A Cinch (rope store) Cincinnati Grocery Provincial Apartments Carpet Cleaners of Distinction Invincible Law Office Ye Olde Incense Shoppe Fragrances, Charms & Incantations Springfield Incineration Facility The Income Builders (financial advisors) Inchworm Babysitters Incomparable Cleaners Incalculable Wealth Advisors "Inconceivable!" Assassins of Sicily (Wallace Shawn, Proprietor) And "LLC" has a similar, but smaller problem: Hillcrest Manor (Texas) Hellcat Firefighters Ecclesiastical Skullcaps of New York You can exclude many of those names (if desired) by adding a leading space before INC and LLC. (A period after INC would be good, but I don't know how "clean" your data is.)3Views1like0CommentsRe: Excel cell calculation
Excel evaluates mathematical expressions according to the PEMDAS rule, which you may have heard of: (first) evaluate what is inside Parentheses; next evaluate Exponentiation; next evaluate Multiplication and Division (left-to-right); next evaluate Addition and Subtraction (also left-to-right). But Excel has a few more operators, so see this Microsoft documentation, specifically, the Operator Precedence section.31Views2likes0CommentsRe: Excel Formula Help!!
See the attached workbook (assuming the forum software attaches it; lately, it has increasingly failed) for my ideas. Oh, and in the future, please identify which version of Excel you are/will be using (Excel 365, Excel 2019, etc.) on which platform (Windows, Mac, etc.). Edit: Yes, the attach failed; trying again. Edit #2: That failed also. The third time's the charm? Edit #3: Nope. Here's the link to a shared copy on One Drive: 2025-11-14 PLUM budget with overspending.xlsx51Views0likes0CommentsRe: 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.51Views0likes0CommentsRe: 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.)22Views0likes0CommentsRe: Delete cells with exactly three syllables
If you had been intending to identify word length in syllables, you would need a pronunciation source, such as most dictionaries. The Gutenberg Project offers (for free) this old-but-undated Webster's Unabridged Dictionary that could be used for that; the extraction of words and their syllable counts would include significant VBA coding. But the derived word forms (noun plurals, verb tenses, etc.) would be incomplete, and as this is an old work, some word spellings have changed, some pronunciations have changed, and other words were then not yet in use. Identifying the number of consonants is more straightforward, but the sometimes-vowels Y and W cause a problem. See the attached workbook for more information and a partial solution. The count of consonants can mostly be done with formulas (I used helper columns). But to "delete" words requires a script; I include VBA code for that in the workbook. (The workbook is not macro-enabled; I will assume that you know how to create and edit macros.) Edit: The forum software again loses a file that I attached (but it was not the one I intended anyway). Trying again... Edit #2: Well that failed also. So access the workbook on OneDrive: 2025-10-02 RAHI words containing 3 consonants.xlsx153Views0likes0CommentsRe: 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...43Views0likes0CommentsRe: 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.27Views0likes0CommentsRe: 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. (?)64Views0likes0CommentsRe: Populating a Matrix from a Table
Re: Re 1 — Yes, and the volume of information gets "better", as you will see in my responses to your notes. Re: Re 2 — You will see the problems in the second attached workbook. result2 is the (intententional) cause of the duplication, not involved in prevention of such. The LENgth functions are just another way of checking for empty strings. If your Flight + Match combinations are unique, you won't get "contradictory results" by changing boat/team/skipper names. (But rematches late in the regatta would be a problem, which we've not discussed.) Re: Smokey and the Bandit — No, I never knew that, as I did not watch those movies.228Views0likes0Comments- 62Views0likes0Comments
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.47Views0likes0Comments
Recent Blog Articles
No content to show