User Profile
mathetes
Silver Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: "Getting Name" bug on my worksheet
From ExcelJet, here are some possible explanations for the #NAME? error. Your image doesn't show the formula in that cell, so you're kinda making it hard for anybody to actually diagnose. I trust looking at the possibilities under that link will give you something to move forward with. If you still need help, though, you can help us help you by giving more than just the error message and an incomplete image. "What's the formula as entered?" for example. Depending on how long, how many embedded functions there are, and so forth, it could simply be a misspelled function name.Re: Complex Cell Format, Maybe!!!
You misunderstand the Mail Merge process. I never had in mind exporting or removing the data from Excel. That's not how mail merge works. On the contrary, this sounds like one of the times when you take advantage of Excel for managing the data, and Word for formatting. In fact, Word gives a lot of flexibility for formatting a nice report that isn't bound to rows and columns. That doesn't mean you should do it, but if you're going to reject it, reject it for a valid reason.Re: SUM WITH INDEX MATCH ERROR
Could you try again to explain what you're trying to do? And better than an image, if you could attach the actual file (or provide a link to OneDrive or some other repository), we could provide a working example of whatever solution. But it's not clear from your description what you want the sum to be in cell D19, and what the criteria are, or how you want to be able to change things dynamically. So give a few examples of what you mean.Re: Excel Paste as Image Issue
It's not altogether clear to me, from your description, exactly what the issue is. Nevertheless, let me offer the following as an addition to the suggestions from NikolinoDE : There are some key combinations that work to get sections or whole screen images. For example, when I need to get an image of a section of an Excel spreadsheet, I use a key-combination to clip the image of the portion of the full screen that I want. That key combination is (on my Mac) Shift+Command+4. For example, I used it on a section of a spreadsheet that's been shared on this forum to get this image, a small corner of the overall spreadsheet. A Google search for key combinations to do various screen grabs yields different results for Windows vs Mac. Take your pick for what works.Re: Bank Reconciliations
My response is still much the same as before. All you are using Excel for is the simple stuff. The user--those novices you're ostensibly creating this for--will need to do the more complicated (still fairly simple, I recognize) bit of adding all individual expenses in each month for, say, :Food"; same for "Entertainment" or any other expense categories like clothing, laundry, gas -- i.e., MOST daily expenses. It would be fairly easy to get from a credit card or check register the once/per/month rent check, or electric bill or water bill. But the reality is that there are a lot of daily transactions that also need to be tracked, and your approach is asking the user to do the hard work of summarizing in those more daily expense categories into a monthly total. The Pivot Table (as in the still very simple example I gave you) will take a detailed database of daily transactions, and summarize them by month by category. That is to say, it does all the hard work of summarizing. Use Excel to do the heavy lifting. For what it's worth, to fill in the detailed transactions in my personal workbook that I alluded to in my first message, I just download each credit card's monthly statement (which is detailed) and my bank account's monthly statement. I do need to add the budget categories to each line, and sometimes the credit card statement helps by separating out "Gas" or "Restaurant" ...but they always include payee--which is the main thing I rely on to categorize each line. So, yes, it still involves work, but it's work in categorizing; NOT work (summing and tabulating) that Excel can do far more quickly and reliably. Your approach is asking novices to do almost all of both types of work. Study the Pivot Table. Play with it on your own. It will change your life.Re: Best way to get rolling 12-month totals
I agree with you in general. You can take a look at a simplified example of his pivot table using the link he finally provided down below. My reaction there, which you can also see, is that he's made it far more complicated than it needs to be. I love Pivot Tables, for the most part, but haven't myself used them with slicers and the like; only with some high level filters.Re: Looking up specific period financials from multiple period worksheet
I would second the request from peiyezhu that you provide a working copy of your workbook. In addition, I'm speculating here, but this appears to be a situation where you are wanting to pull data (for reporting purposes) from data that has already been summarized elsewhere in the workbook. This leads me to wonder whether it mightn't be more effective to produce your higher level, more focused summary, directly from the raw data. What led me to speculate along those lines was your suggestion that you might try concatenating headers into "JulyADRUSD" in order to extract the desire set of numbers; that sounded like making something that should be simple -- extracting data from a database according to certain identifiers --and making it overly complicated.Re: V Look Up - not for an exact match
You'll have to start here with a much clearer explanation -- perhaps attaching either your actual workbook (use Disney character names rather than the real ones) or an image of it. Your description doesn't make things very clear and clarity (aka precision) is helpful for Excel formulas and functions to work.Re: Best way to get rolling 12-month totals
Yes, thank you. We can access it, which will go a long way, as I said, toward getting your answer. I'll look at it further, but my first impression is that you're making this far more complicated than it needs to be. My guess is that a couple of the others who've already offered more sophisticated suggestions than mine will now be able to see this example and jump in.Re: Best way to get rolling 12-month totals
You would go a long way toward helping us help you by posting a copy of an anonymized version of your actual workbook, containing several years of data. Verbal descriptions are good to a point, pictures are better, but in the case of Excel workbooks, the actual (or a mockup) is far and away the best.Re: Bank Reconciliations
I appreciate what my on-line friend< Riny_van_Eekelen , has done to help with your approach. My own suggestion would be to radically change your approach, or at least to consider changing. Excel can do virtually all of the summarizing for you, of income in various categories and expenses in various categories, by means of the Pivot Table. All you (or your novice users) would need to do is enter each transaction, by date, showing whether income or expense, and budget-category...i.e., a transactional history in the form of a database. (I will point out that your approach is already asking the user to do the monthly summary "off-line", introducing the possibility of careless mistakes or overly casual "eye-ball" summaries..) I'm attaching a very simple example for your consideration. This highly simplified example is based on a spreadsheet I use myself for tracking our own income and expenses. I have a more complicated (two to three levels of sub-categories) set of budget categories. The main point is that Excel's Pivot Table does all the summarizing. No formulas are needed. The Pivot Table, once discovered, becomes a favorite of many Excel users. In addition to that link I've already given, you'll find a wealth of "how-to" videos on YouTube.Re: Best way to get rolling 12-month totals
At the simplest level, this has worked for me for years. =SUM(C207:C218) I'm at row 218 right now in a spreadsheet containing a multi-year monthly summary of increases/decreases in my IRA. There's a column for changes in market value, a column for dividends and interest, for withdrawals. Then, off to the side, I calculate the rolling 12 month totals in each of those categories. So, to spell this out one step further, C207 contains the monthly market increase for October, 2024; C218 contains September 2025 data. The intervening rows contain the other ten months. Since each row is a single monthly total, copying the formula down changes the relative references so they always are the rolling 12 months including the last or current. As noted, this is the ultimate in simplicity and assumes that each row we're working with already contains one month, and that monthly rows are contiguous with no blanks or other extraneous rows.
