Requests
27 TopicsUse VBA to Autofill a Row until the end of the number of data in another row
Hello, I need some help with the following problem: The Macro should select the first cell with the vlookup (AY2) and autofill the complete range in the column AY until the last row that contain data in the cell next to it (Column E). Column E is the cell that the vlookup refers to. The situation looks like this: The code that I have so far looks like this: Sheets(3).Select Range("AY2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-41],DennisAR!C[-50],1,0)" Selection.AutoFill Destination:=Range("AY2:AY1662") Range("AY2:AY1662").Select The problem with this is, that the number of rows with data always change every week. So I cannot use a static row number of 1662. I am looking for a way to make Destination:=Range("AY:AY1662) dynamic. In fact it has to refer to the number of rows with data in column E. Thank you very much in advance, KaiSolved669KViews1like83CommentsMoney in Excel - Dual accounts not allowed
New Money in Excel user. Spouse and I both have 401k accounts with the same institution. Money in Excel will not let me import both accounts. Hoping this can be a future fix. I tried to combine the accounts at the institution, but they don't allow combining 401k accounts946Views1like2CommentsI deleted merged cells - and all rows dissappeared - help!
I now only have row 2 and 3 (the headlines), and the area below is just grey (now row-numbering or anything, just grey void..) - how to get back the main part of my sheet? As far as I know, I havent hidden or deleted the rows, but they dissappeared when I tried to delete five columns with a shared merged cell on top of above them. Thankful for help!1.1KViews0likes0CommentsCopying excel worksheet saving dropdown menus
Hi, Ive created a spreadsheet. I require 4 x spreadsheets and x 5 books. each sheet representing a week of the month and a book for each month until `December at least. When I've copied the worksheet the information in the dropdown menus haven't come across, although the formulas in different columns have. In the original workbook I had a second sheet within the book with the information to go into the drop down menus. Please can someone assist so I down have to keep creating dropdown menus and everything copies accordingly? TIA (its the log register1.9KViews0likes1CommentRookie user; need help please
New user, apologies for asking for help for IF statement with several conditions. If cell value is<7, then return "purple fill" of the cell, and if cell value is 7-8:59, then return "green fill" of the cell, and if cell value is 9-12:59, then return "yellow fill" of the cell and if cell value is 13-15:59, then return "orange fill" of the cell and if cell value is >16, then return "red fill of the cell1.2KViews0likes2Commentssignificant shortcoming of Money in Excel
I noticed off to the side on the Excel community board an invitation to download a new master template called "Money in Excel" Downloaded it, got it running. Connected with one of my most active credit card accounts fine. The product looks impressive. Much much better than the typical templates that are offered for budget and personal tracking of expenses, income (not that that's saying much). But a couple of shortcomings, one of which is pretty major in my book. A minor oversight: among the standard fixed Expense categories, one that I would expect virtually every user to have, you will not find Taxes. I would also, were I designing my own (which I've done) have fewer major expense categories and allow for sub-categories. Just as a quick example, a major category for "Fixed/Basic" and another for "Discretionary" allows some useful analyses once you connect them to the sub-categories that correspond to each. "Groceries" goes to the former (Basic) while "Restaurant/Dining" clearly belongs in the latter. And "Shopping" is a category of its own?! Surely there could have been somebody on the design team with more experience actually, you know, shopping...someone who would know that shopping for clothes doesn't fit next to shopping for, oh, jewelry, or a new phone. But here's the more significant shortcoming, which for all practical purposes precludes my using it: my main account for virtually everything is a major financial institution--the institution itself is supported, and I was able to log in with user name and password, but, in 2020, this institution and its security-conscious users, among whom I count myself, believe in dual factor authentication. Not supported. So an appeal to Microsoft developers (or is it Plaid, the service through which you all make the connections to the financial institutions?): make Money in Excel useful by making it capable of handling dual factor authentication. I know it's possible (TurboTax manages)....Solved2.7KViews0likes6CommentsCONTACT TRACING
Microsoft Excel @msexcel I need help with solving a problem A company runs 6 shelters in a city. Each shelter keeps a log book of login and logout times of homeless people that come to the shelter. Attached is the log data from the shelters. On 9th May 2020, Person 1 tested positive to the corona virus after leaving Shelter 3 and was immediately isolated. The company, in a bid to curb the spread of the virus in any of their shelters, decided to perform a contact tracing exercise of those that could have been exposed to Person 1. The following assumptions are made for the purpose of this exercise. The infection is only passed from person to person and cannot be transmitted from surfaces or the air. This implies that when a person leaves a place, the people that visit the place after him are not at risk. There is a 14 day period in which the infected person could be asymptomatic. The infection rate of the virus is exponential i.e. one person can infect another person, who can then in turn infect other people he comes in contact with. You have been saddled with the task of generating a report showing a list of those that have visited any of the shelters and could have been infected through person to person contact starting with the index case. Generate a routine that accepts the following parameters, and outputs the list of probable infected persons. Person infected Last Date/time he left the Shelter just before isolation Last visited Shelter before being isolated The routine should be dynamic such that slotting in different parameters would yield an accurate contact trace list.1.7KViews0likes2CommentsPassword protection
Hello, Sadly my father has recently died and I am trying to help his accountant collect his records. He had some passwords on some excel files on his work computer that I haven't been able to guess. Any tips on how to remove these? The file type is- Microsoft Excel 97-2003 Worksheet Any help would be hugely appreciated. Thanks943Views0likes1Comment(Budget) Need ideas for an optimal way to proceed
Hey everyone, I'm working in a hospital and we're asked to keep track of every overtimes, 1:1 surveillance, surplus, etc. (It's a really huge work charge for only 2 employees, thank you pandemic) We're asked to do this in an Excel 2016 table (1 "transaction" = 1 row), by filling in every infos they ask. Just fyi, here's approximatively what the required infos are : Department - Date - Time start - Time end - Break time - Type (overtime, surplus...) - Job title, etc. Of course, I can copy/paste most of the time, but it gets quite tiring and I was simply wondering if you guys had any idea about how I could do what they ask in the most optimal way ? I was thinking about creating like a form, but it seems slower than copy/pasting... Any suggestions ? I know there's a big lack of details, but any tips will probably help me. Thanks a lot in advance !798Views0likes1Comment