User Profile
mathetes
Gold Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: Conditional Formatting
Let me first say that Conditional Formatting is one of Excel's features where I often find myself doing trial and error through several iterations to get the result I'm looking for. (I just went through it about 45 minutes ago on a spreadsheet of my own.) Conditional Formatting is, I think it's safe to say, one of the less intuitive of Excel's features. That said, I find it hard to even understand what result you're looking for. Your description itself is confusing. It would help a LOT to be able to see the spreadsheet itself, but also to have a clearer description of the desired result. Are you able to attach that file or include a link to it. If the actual contains confidential info, then create a mockup that can be shared.Re: My Percentage of total sales is not 100 - Why?
Metts518 : there could be several different causes. You give no indication of what kind of data you're dealing with, whether one of more of the columns involves some rounding to nearest whole digit or two decimal places or whatever. Rounding, which most of us think of as "rounding up" could be the cause of your issue. I just recently (in another thread in this forum) learned of what's called "Bankers' Rounding," a method to minimize what you may be experiencing.Re: Having Trouble With Macros
It sounds like you're using macros for cosmetic purposes ("changing font colors, adding border, etc"). lf so, may I ask if they're really necessary? It's fun to use macros, fun to have that as part of your Excel toolkit--I'll grant you that--but there are lots of us who avoid them unless totally necessary, believing that such things as colors and borders can be done with "Conditional Formatting" where it helps in highlighting spots that warrant it. And, for that matter, even most advanced calculations and data parsing tasks can be accomplished with Excel functions....Re: Excel formula help... again!
TOTALLY concur with m_tarler : it's generally a design mistake (in the sense that it makes things more complicated and is more error prone) to have a separate sheet for each month. This often happens because people carry-over the ledger sheet mentality to Excel, rather than taking advantage of Excel's superior ability to work from a single comprehensive datatable, producing monthly reports as needed, but also readily showing current status--whatever you want. Are you open to reconsidering your design? If so, please give us a more complete picture of the application, ideally including an anonymized copy of the actual workbook.Re: How should I write this Process?
I notice that the original poster, dwarseck , has yet to respond. I can appreciate some of the attempts at answering the original question; one of my goals in giving the response I did was to provide help in articulating the situation. That's a skill in itself, vital in Excel as in many other situations; in Excel, I have found that once I can do that, the solution becomes a lot easier.Re: How should I write this Process?
I'm quite confident that whatever it is that you're trying to do IS possible. The problem so far is that you haven't really done a very good job of describing in plain old English just what that task is. You're trying to use Excel-ese to describe, but as you noted, you're a beginner with Excel. It would actually be better if you left the translation to us, and simply used your best English. A few questions might help, beginning with: WHAT is the start point? It appears as if you're starting with a list of nine people, although obviously that could just be a false inference. You've not revealed any names, just used generic "Person #" so it's also possible that the real task doesn't involve people at all......So please, what's the starting point, or raw Input? What are we beginning with? WHAT is your desired end result? I can assume, staying with the inference that it's a group of individuals, that the goal is the selection of one (or more?) of the starters, but you need to articulate clearly what the desired end result is. As it stands, you've assumed that, and just attempted to describe a "process," a description that gets muddled in part because you're trying to say it in Excel-ese. HOW, in English, as you would describe it to an intelligent middle-school student, are you thinking you'd get from the starting point to the finish point? Along the way, perhaps you could explain to that intelligent young person how Red and Blue comes into the scene, why they're associated with something called a "cutoff"...and so forth. Again, don't try to put your descriptions in Excel- or Excel-related-terms; that's the task of people here in the forum. Your job is to describe what you'd be doing if you didn't have access to Excel. Maybe that's a good way for you to think about it: what would you do if you were forced to do this all on paper? If you'd rather work on extending your Excel capabilities--though it may take longer, it's always a great way to really learn--you would do well to look over this page of Excel functions, grouped in to an intelligent set of categories, and accompanied by clear definitions and examples. But even to use it, I'd seriously recommend, for your own sake, working to describe the process in English.Re: Consolidate by category
One of the unwritten rules here in the forum is that we don't do homework for people taking courses. That doesn't mean we won't help where help is due, but we're generally far more willing to help someone who gives an indication of what they've tried. All you say is that "you can't for the life of you figure out the solution" but you give no indication at all of what you've tried. So if you truly need help, first give some indication of serious effort--describe what you've done, where it's run aground, describing more that one half-hearted effort. Then maybe somebody will help you with the next step. But don't expect people to start the process.Re: If, Then formula from multiple columns
It's not altogether clear what your various sheets contain, where that one formula resides (on the "merge" sheet?)....but IF what seems to be the case actually is, perhaps you need to familiarize yourself with the IFS function, and employ it rather than IF. So, putting it all in one column on the merge sheet, the formula could read (and I'm making some assumptions here) =IFS(BG3="Yes","Location A",BH3="Yes","Location B",BI3="Yes","Location C.....) That would run through those several columns, the majority of which are blank, pick up the one that has "Yes" and enter the corresponding location name.Re: How can I display negative values for time in calculation results
Wouldn't the only negative values in time be those before the Big Bang? You don't display what you're talking about, but picking any old arbitrary time as the "predetermined point," say 4:57, the time displaying on my screen as I type this, wouldn't 15 minutes ago be 4:42, before that 4:27, and so forth. I.e., they're not "negative time values," they're just earlier time values.Re: VBA code to allow dropdown box multiple selection
I'm quite sure your real world situation doesn't involve various combinations of fruit. But even if it did, having a cell with the contents "Apple, Pear, Banana" could potentially be problematic in itself, even if it's just part of a shopping list; what do you do if the first market you go to has the Apples and the Bananas, but no Pears? And so on. And would your desire be to always have the final selections be in the same category--in this case "Fruits"--as opposed to other occasions when the multiple selections might end up "Apples, Nails, 8x10 plywood" There's a reason why upfront conceptual design is important when it comes to having a useful workbook. It is possible to have drop downs that cascade, say three different drop downs each getting more specific. I have such a set in my budget tracking spreadsheet. Here's an image showing the concept. (There are quite a few more rows in reality). And I've attached an sample workbook that illustrates how to create what I've called a "Cascading set of Drop Downs"==if that would work in your real world situation.Re: Excel Table formatting
I think, Riny_van_Eekelen , that the original poster didn't mean to add rows, just to add the values in multiple lines of a given column. That said, the question was entirely ambiguous, so your request for clarification is still on point. Lata1976 If you're not able to post your file, you do need to ask your question with a bit more clarity of intention. For example, if you just want to add contiguous rows in a column, then the generic SUM function is what you need. If, on the other hand, you want to add the figures that correspond to a label or text in a different column.--eg, add all expenses that are "Food" in an adjacent column--then the SUMIF function would be useful. There are several other variations on these....I give those two possibles just to get you started.
