User Profile
mathetes
Gold Contributor
Joined 7 years ago
User Widgets
Recent Discussions
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.Re: Name Order in Excel
From your brief description, I, for one, was not able to get an image of the full workbook/spreadsheet you're working with. Perhaps some of the other folks around here can infer more of the database itself, from which your formula generates that reverse order. That said, I think you would benefit from learning the SORT function. And, indeed, some other(s) of these Dynamic Array functions may be more what you need. FILTER is often very useful, for example -- it could easily screen out the "/" placeholder until such time as there's a name.Re: i need the data from all the sheets in the workbook to link to one data sheet.
For now, it's not Excel ability that you need to bring to bear. It's plain old descriptive English. As you acknowledge, you've not been very effective in your explanation, and that is where we need to focus first. The two images you did include--they're only images, not attached files--don't totally make sense or correspond to your attempted description. For example, you refer to wanting to get "only certain parts of the data [from Sheet 2], in this case B and F. There is no column F (assuming you're referring to columns) in the first image. And although it's reasonable to assume that if we could see all of column A in that first image, we'd see "160 - T" in some of those cells, that and nothing more. and frankly, saying there should be a "7 because there is 7 1's below for that Org" doesn't make sense if you're trying to copy the data over. That's a summary of something or other.... Unless the data in this workbook is confidential, you could help us by dragging and dropping the actual workbook into your next reply. If that's not possible, then see if you can provide a more complete description.Re: How can I use Pivot Tables to keep two worksheets of user information and add rows for new users?
Unless I'm missing something here, I think you'd be making a mistake. I say that because what I'm hearing is taking a single database and (essentially) duplicating it.....making for unnecessary redundancy, a need to maintain two duplicate (or highly overlapping) sets of data, increased opportunity for inconsistency.... So unless there's some compelling reason why Auditor A and Auditor B can't be working from the same, single database, where any change is made once for both users, and so on, you and your organization would be better served by keeping it all as a single database. Excel is very adept at extracting data (FILTER, etc) for different users, including what A needs to see in one "dashboard." what B needs to see in another.Re: Issues attaching files to posts
I made repeated attempts to attach a file with a name that had two spaces in it. They failed. When I removed the spaces (making it a slightly less "friendly" file name) it worked. If THAT technicality is a requirement for attaching files, then this pinned post seems like the place where it should be mentioned.Re: Cleaning up data with Macros
NikolinoDE gave you the macro you requested. I want to question your original request. To me--the creator and owner of a spreadsheet that I too use to track income and expenses--your request doesn't make complete sense. If you really are interested in tracking such things, your interest should extend (I would think) to comparing expenses on X in this year with expenses on X in the past year; or quarter to quarter, or ...whatever. The point being, Excel has plenty of capacity to hold data on transactions for many years. I can't understand--which is why I'm asking--why you would want to limit your tracking to only the last three months. Are you just wanting to make sure each check or credit card charge is accounted for? That could be a legitimate goal, though it wouldn't really qualify as "tracking" in my book. "Tracking," to me, implies an interest in longer term attention to trends in spending in various categories. So my purpose in asking this is to invite you to a deeper possible goal in your use of Excel.
