User Profile
mathetes
Gold Contributor
Joined Oct 14, 2019
User Widgets
Recent Discussions
Re: Help with summing totals using multiple texts
It would be a lot easier to help if you could provide us with not a set of images but the actual spreadsheet. This doesn't appear to be the kind of thing that would need to be kept secret for corporate proprietary reasons. But without that, you're in effect asking us to create anew worksheets you already have in order to show how to approach the problem. Just attach a copy of the file to your reply. You should be able to drag and drop the file. If not here, then put a copy in Google Drive or the equivalent and paste a link that allows access.Re: Excel-Formating Issue
You don't give enough information for a definitive answer. For example, on the most basic level you don't say how the cost figures are entered into that column: are they the result of a calculation? do you simply type them in? are the the result of a copy and paste from some other electronic/computer source such as an emailed invoice? something else? I could hazard the guess that what's being entered--by whatever method that's being done--is coming in as text, in which case the currency formatting would not apply. But that's a guess.Re: Excel - Conditional formatting
You realize, I trust, that you're asking us to imagine how your workbook looks and how it functions, and then (based on whatever we've concocted in our imaginations) come up with specific ways to accomplish what are in fact some pretty sophisticated "tricks." You'd make it a lot easier to for us to give you the requested assistance if you posted either a copy of your actual workbook or (if you need to protect proprietary or private information) a representative mockup of the actual. If you can, attach it to your next reply here in this forum. If you're not able to do that, post a copy in OneDrive or GoogleDrive and paste a link here that grants access. All of that having been said, I would encourage you to make use of some on-line resources and give yourself permission "to play" with the methods of conditional formatting and data validation. Work with a copy of the actual workbook, but give yourself permission to engage in trial and error--it's the best way to learn. Kidd_Ip has already given you some official Microsoft links. I personally find even more helpful the site ExcelJet.Net, where his examples help flesh things out.Re: Operations Dashboard in Excel
I'd like to ask what airline you're working for so that I can avoid ever booking a flight with that airline. I say that partly in jest, but also to make a point: it worries me that you're trying to manage something so critical by designing a spreadsheet to track it....surely there already exists software to manage this workflow and all other associated matters. If this is a serious request, you should be going to a consultant who works professionally on this type of thing and who is well compensated for that work (as contrasted with volunteers here who just enjoy helping people get over some of the hurdles of learning Excel). Definitely you shouldn't be using ChatGPT, not for something this critical.Re: Cleaning messy excel/csv form ERP system
Some interesting questions. I feel your pain. I regularly import financial data from a credit card account with American Express investment accounts with Fidelity The former, when I use their XLSX format, is utterly frustrating because whoever designed their excel version chose to make it a "pretty" spreadsheet--merged cells, colors, multiple lines of things like address. As a result, the CSV file is far more useful, but I would love to see them make the Excel useful as an Excel table. The latter does allow me to export a good Excel table of data, but at the top they have a huge merged cell, followed by a blank row, then the headers, then the data, beginning at row 4. Then at the bottom of the useful table there are two blank rows followed by "Disclosure" and a paragraph of legalese. To import only the data in the table, I've written: a formula that finds the row in which the word "Disclosure" appears in that raw export file another formula that imports only the table of data from that raw export file. My working workbook uses a dynamic array function to import the data, but knowing that I always want to ignore the first three rows, I set the import function to import $A$4 through the final column ($T) and that row determined by the prior formula. In other words, I'm able to ignore the header and footer that Fidelity is legally obligated to clutter their file with. Other than that, I often have to convert text dates to Excel dates, or text representations of dollar values into actual numeric fields.Re: File always corrupt
It's hard to diagnose with the information you've given so far. So I hope you're able to answer a few questions. Does this happen with multiple Excel files--three or four different ones--or is this experience solely with a single file? What is the nature of the file in question (assuming it is just one particular file) --- what kind of data, what kinds of formulas? Please give examples. Would you be willing to attach the file to your next posting here in this forum? Or the post the file on Google Drive or some other publicaly accessible location, with a link pasted here that grants access?Re: Time sheet for hours worked
Could I just give you a few words of encouragement, based on (believe it or not) 50 years of working with arrays of data, most of that time with spreadsheets as the primary tool. You don't ever need to "hope" that there's a formula to get from A to B. Quite seriously. A rule of thumb that I developed many years ago, first for myself, then for others who were still learning: IF you find yourself thinking along the lines "Excel should be able to ________" THEN you can be confident that the programmers have indeed made it possible. The challenge is to find the function or functions that make it possible. Related to that rule of thumb is the reality that you do need to find a comprehensive and clear way to describe the situation, thinking of these three stages: input, process, output. Rather than kicking yourself for "not sure how to word everything" think of it as describing, especially describing fully the process aspect. And focus on describing in plain English (no need to try to translate into "computerese" or "Excel functions"; that comes later). Usually, once you've described the input, process, and output--your original description here had the first and last parts of that trio; it just left vague the process itself--it becomes relatively easy to find an Excel solution because the names of the functions themselves are close enough to English descriptions of what they do that you can go down a comprehensive list of functions and start playing with them to see if they get you from A to B. Let me also underscore my use of the word "play" in that last sentence. Give yourself permission to go through trial and error. Play. You won't break anything and you will learn.Re: Time sheet for hours worked
You wrote: Sorry - wasn't sure how to word everything. <snip, snip, snip> I am hoping there is a formula for excel that will give me that exact answer. One of the things I've learned through decades of using Excel (and, going back to the 1970s, many of the other spreadsheet programs that are strewn along the roadside of the computer age) is that "If I think [Excel} should be able to do [X] , it can. I just need to figure out how the wizards behind it made it possible." In other words, you don't ever need to "hope" that there is a formula to do whatever it is that you want it to. There is such a formula; probably two or three. The challenge is to play around with the various tools until you find one of what will undoubtedly be several different ways to get from A to B. I use the word "play" very deliberately. Give yourself permission to dabble, to experience trial and error, to learn from mistakes. That is truly the best way to learn Excel (as it is many other skills). And then there's the aspect of wording--let's say "describing" instead. One key step in the process is to come up with a clear and comprehensive description in English (not computerese) of the task at hand. Generally, once we have a complete and clear description of the task (input, what to do with the input, output desired) the rest falls into place, maybe, as noted above, with a bit of playing around to find which route from A to B is the most efficient. So have at it, my friend. Here's a website I still use frequently to help me find new Excel toys to play with.Re: Time sheet for hours worked
Llamaface06 Let me add to the question Lorenzo asked: Your example gives 7.65 hours and you seem to want 7.6 ==> That could be a round Down to the 1st digit And why round down in a borderline case, as opposed to rounding up? Are there potential employee relations implications, given that pay is affected, even if minimally?Re: Time sheet for hours worked
It may be clear to you what "we do things in 6 minute increments" means in this context. I know what the phrase means--and am assuming it is convenient because it refers to tenths of an hour--but you don't make clear how you're using it (or hoping to use it) in tracking time. Here are the possibilities that occur to me. Does it mean you: round each entry to the nearest tenth of an hour round each segment of clocked in to clocked out to the nearest tenth of an hour only round the final total "Hours Worked" to the nearest tenth of an hour something else?Re: Need 1 worksheet to add totals from multiple worksheets in the same file.
In addition to the suggestions by m_tarler , there's also the possibility that you could redesign and consolidate the multiple sheets--depending on the reason for their existence--into a single sheet. It's not uncommon for people to design a workbook with a separate sheet for (say) bank statements for each month of the year, and then want a front sheet (or dashboard) to consolidate all income and expenses for the year. It's actually a better use of Excel to have a single worksheet with all transactions for the year (or even multiple years) and use Excel's marvelous abilities to pull out, when needed, the transactions for a single month, or the transactions involving a single payee (your car payments), or the transactions for a category of expenses (utilities). If something like that could make sense in your situation, why don't you share a bit more of what your workbook is about, why you have multiple sheets, etc.Re: Excel sheets that populate a drop down menu
Without seeing your workbook, it's hard to be certain about this, but the way you describe it leads me to underscore what m_tarler says in his first paragraph: you should seriously consider redesigning your workbook so that it takes the form of a single large database. Then you can take advantage of Excel's many tools to parse, filter, select from that single database. As it is, you've slightly computerized what would have been the sensible approach back in the days of each machine being tracked on its own large paper sheet. It would be better if you rethink how you approach it, looking for a way to take more full advantage of Excel.Re: excel themes
By way of adding to my suggestion that you tread carefully: I asked AI (ClaudeAI, specifically) what Excel pros have to say about the benefits and drawbacks of Excel themes. Here's the response. Notice that there are more concerns listed than positive, especially if this is a workbook that you'll be sending to other people. The Positive Case Consistency & Branding: Excel MVPs like Mynda Treacy and other professionals praise themes for maintaining corporate branding with one click. For dashboards and reports, themes can ensure uniform colors, fonts, and effects across entire workbooks, which is valuable for professional presentations. Efficiency: Professionals appreciate that themes can transform a workbook's appearance quickly rather than manually formatting elements one by one. Custom themes can be saved and reused across all Office applications. The Major Concerns Portability Problems: This is the big one. As one commenter corrected on Mynda Treacy's site: when you send a file with a custom theme to someone outside your organization, the colors will change to "something completely off" and Excel substitutes default fonts even if the recipient has the fonts installed. The theme file itself doesn't travel with the document for external recipients. Style Proliferation: Mynda Treacy warns that "Styles can breed like mice when copied from workbook to workbook and can quickly multiply to plague levels" — a colorful caution about how styles can create maintenance nightmares. Version Compatibility: Users report frustration when Excel updates change default themes unexpectedly, breaking color-dependent macros and requiring manual theme resets on each new file. Limited Adoption: Multiple sources note that few people actually use themes despite their availability. As one tutorial states, "few people use themes to enhance their worksheets" because Excel users focus on data rather than aesthetics.Re: excel themes
I've never used these, but I queried Excel Help and was pointed to the Page Layout tool bar, where I see themes in the immediate left. And when you click on it, this opens up I'm doing this on a Mac, with Excel 16.107 If your issue is specifically finding the "Basis" theme and it's not there, THAT is a different issue. Beyond that, I'm curious what you're wanting to do with a theme in the first place. I've used Excel a lot (over several decades) and have never come across these; and I'd be wary of using them anyway. If you want to create a good looking output sheet at the end of a bunch of analysis, that's one thing, but be careful about prettifying background database and processing sheets. Too much attention to looks can seriously interfere with reliable functionality.Re: Trying to fill a field in excel with 3 different wordfs based on another field result
You need to become acquainted with the IFS function. It should be very straight forward. Depending, though, on the degree of precision, you may need to do some finagling to get 32.5% (for example) to display the right words and color. Or go at it backwards: IFS stops at the first successful match, so you could write it: =IFS(G7>.75,"Good Deal",G7>.33,"Fair Deal",G7>0,"Bad Deal")Re: EOMONTH
Lomburus : you may be wondering why Lorenzo can state with such absolute certainty that you didn't do anything wrong. What he didn't explain is how dates work in Excel. Here's an excerpt from Exceljet.net, a helpful website for understanding many aspects of Excel. "In Excel's date system, dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on. More recent dates are much larger numbers. For example, January 1, 1999 is 36161, and January 1, 2010 is 40179." It's this underlying feature that makes date math possible, which is the why behind the way Excel stores dates.
