User Profile
mathetes
Gold Contributor
Joined Oct 14, 2019
User Widgets
Recent Discussions
Re: Paste values-only Excel
"Paste Values Only" doesn't convert text to numeric values; it pastes content (aka value) "as is" But assuming that source is in fact text, and therefore what's in your column C is also text, you can convert to value by entering in column D, =VALUE(C1) and copying that down. No need to search and replace the dollar sign and space.Re: Excel message: There's a problem with this formula
Well, one possibility is that, just as you've done with posting your question here, you've given the normally very cooperative VLOOKUP function utterly insufficient information to proceed. That's my smart alec way of saying, "You need to give us a lot more information if you expect a meaningful answer." For example, and just for starters, what's the VLOOKUP formula in this case? Second: is the error message, "There's a problem with this formula"? or is that your interpretation of the situation? Do you see what I mean? In the ideal world--although many of us have had problems doing this--you could help even more by attaching a copy of the workbook(s) in question. That can be a challenge, of course, if they are--or contain materials that are--confidential or proprietary. The point, though, is that you will need to give more information if you want meaningful help.Re: Excel
djclements agree with your disappointment/frustration with this forum as well as with it having been a favorite but one I now regularly am considering dropping. The frustrations with including a file are one thing, but it also continues to seem that there are FAR fewer users coming here with questions. Has it somehow become harder to find?Re: Multiple Inventory entries on the same item
I find myself wondering how the whole spreadsheet is arranged, how you're walking around checking inventory and making entries on how many different items... It sounds as if you've got one row per item, and are entering numbers for Item A each time you see ItemA on a shelf, then going to the row with ItemB and entering numbers for it, then back to ItemA and now needing to jump ahead to column whatever.... All of which might make sense if you've only got three or four items and no more than three or four of each in stock. But it only kinda makes sense. Another approach would be something along the lines of this hastily created workbook. One sheet (Catalog) contains a list of current products, used to provide the drop down for column A of the inventory sheet itself. You just walk around creating a new row for each new entry. If there are more entries needed than the current table provides, copy the last empty row before filling it, adding enough to meet the need. The Pivot Table takes care of totalling up the numbers of each. This could be made more sophisticated, as needed. For example, if you want to track what room or shelf where each item is located, that could be another column, also managed by drop down so spelling is always consistent, etc. See the attached spreadsheet.Re: Variable "Connecting to Datasource" Load times
Could I (as someone who used to teach technical problem solving--AKA finding of cause) ask a few more defining questions that might help you or others zero in on (or eliminate) some of the possible causes. First, your description of the problem for which cause needs to be found is this: "Some users can load and run all Macros in < 15 seconds, while the others take > 10 minutes." That's a good start, seriously. But there still are questions, the answers to which should make it even clearer. Is it always the same users who load and run in <15 seconds? Is it always the same users who load and run in >10 minutes? (Or, just be sure we're clear, are there some people who have both experiences, just on different occasions?) Are there no (zero) experiences in the sizable time gap between those two extremes? If there are other experiences, let's include them in the list of symptoms seeking a cause. Are these different users all in the same geographic location (you don't mention location in your description of all the "Sames" that they do share in common. When are these different extremes experienced (time of day, time of week)? And so as to eliminate people coming up with possible causes you've already eliminated, what have been your hypotheses?Re: Formula Help for Sorting
You are looking for a single formula that will do all that--both sort and then assign? You don't really give enough info for a full understanding here, with just three examples. Are you creating (not just assigning) those departments, such that what you're ending up with is a 1:1 ratio between sorted cells and departments? That would simply be a sort. Anyway, my own approach would be to create "a helper column" which would extract the value of those last two digits from the texts ("00-14" is in fact a text value), doing so with this formula: =VALUE(RIGHT(A4,2)), and then sort on the basis of those extracted values. See the attached. I chose to sort in descending order just to demonstrate that it changed it. If you have something more extensive--e.g., multiple and random lines assigned to individual apartments--then come back with a more complete description.Re: FIXED FUNTION
First, please stop shouting (i.e., don't use ALL CAPS, which is widely regarded as the visual equivalent of shouting); it doesn't make things any more urgent, Second, you are making a very sweeping claim: "All my formulas", although you only show one formula, and your title refers very specifically to the FIXED function, which is pretty non-universal. So maybe slow down, lower your voice just a bit, and be more precise in describing what you're experiencing, as well as providing more examples if indeed it is "all" of your formulas. Third: here's a link to a resource that may point you in the right direction for resolving the error on your own. Fourth: if it's only formulas that involve the FIXED function, it may be that you're using it thinking it returns a numeric value, but in fact it returns a number as text.Re: Question with this forum itself
I could only make the "Start a Discussion" button disappear from its prominent spot toward the top of the screen by shrinking the window of my browser quite significantly. Otherwise, as noted, it is prominently displayed above all the current and recent postings. And you'll notice that the various "Share" buttons obscure nothing when the browser window is larger...... So does that explanation sufficiently plausible for your circumstances?Re: Unable to use group the pivot table with date
You leave so VERY much unstated here that it's unlikely you'll get much in the way of an explanation. In fact, you don't even ask for an explanation; just offer a vague sentence describing the inability to accomplish a task. Could you help us help you by describing more fully the nature of the grouping you desire. It's clear you expect some kind of Pivot Table grouping around a date value, but not precisely what: group by month, by quarter, by year? How extensive is the table of data being summarized in the Pivot Table? I've never encountered difficulty in grouping by month, but I'm always dealing with many transactions (usually financial in nature) in each month. What's the nature of your data table in the first place? Perhaps most helpful to those who might wish to help you: make available a copy of your workbook itself (use OneDrive or GoogleDrive if unable to attach a file here). If the actual file is confidential, then create a meaningful mockup that conceals private/confidential info.Re: Calculating hours using pivot table
Your inquiry has been up for several days without a reply. I can't speak for others with certainty, but from my perspective I suspect that the reason for the lack of replies is that the sample data you do give is far too simple. Far too simple to create a Pivot Table from; so in effect you're asking anybody who might want to offer assistance to create their own more complete set of data. You would help us help you by posting a more representative set of data (still no real names) with multiple teachers, multiple classes, etc. If you're not able to attach an actual Excel workbook to your reply in this forum, put it onto Google Sheets or the equivalent and paste a link here.Re: Simplifying cost calculation using array instead of IF statement
Unless I'm mistaken, after spending a bit of time looking at your formulae, I think you could take good advantage of INDEX and MATCH to zero in on the relevant cells in this table These hyperlinks will point you to a good reference site to learn how INDEX and MATCH work. And again, unless I'm mistaken, you would be able to eliminate the many IFS conditions comparing J4 with the top row to directly zero in on the appropriate column, and then again to identify the appropriate row based on the items down the left of this table. Given how well you've mastered IFS and XLOOKUP in combination I have no doubt that you'll be able to use INDEX and MATCH equally well. One other recommendation, assuming you are working in Excel rather than Google Sheets, you could use (on a Mac) a combination of the Option key and "Return" to format your extensive formulas as I've done with the formula in Q12, below; they're much more readable this way.
