Use 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, Kai
Extracting non-empty cells from a table based on a value
Hi, There is an Excel table that we use to monitor reading compliance - columns are people names, rows are document codes, and cells are the reading dates. Please find below a simplified version: John Doe Jane Doe John Smith Gandalf THIS-123-V10 02-11-17 05-10-14 IS-456-V10 01-09-17 01-09-17 A-789-V10 27-09-17 TEST-753-V10 02-11-17 05-10-14 I need a quick way to check who has read a specific code and, if possible, on which date. I want a formula where if I put a code, it will detect all cells with a value on the corresponding row and will pull the person's name from the top cell on the same column. I already have a formula where if I put a person's name and a list of documents, if confirms if they have read it or not, but this is different. I need all results, not just from a limited list. Thank you Edit: Forgot to mention - this goes on a separate sheet Edit 2: Office 2010 :)
Conditional Formatting based on the date of another column
Hi, I'm trying to conditionally format cells in column A based on their related to cells of the same row in column J. Essentially if the date in column A is before the day in column J I want the cell to go green If the date in column A is after the date in column J I want the cell to go red Is this possible? I cannot find a way to do it.
Help with replacing #DIV/0! error with text
Good morning all I am currently working on a spreadsheet to show trends in relation to various incidents I deal with at work. I am looking to track changes in the number of incidents along with the percentage increase/decrease on a weekly basis but have come across a #DIV/0! error that I cannot solve (self taught newbie to excel). The formula currently used to work out the percentage change from the previous week is =IF(H4<>"",(H4-G4)/G4,"") and I am fully aware that the error is being caused because G4=0. I would like the cell to show some text such as "N/A" instead of the #DIV/0! error but am struggling to find the solution even with the help of Google! I have attached an example of the spreadsheet if that will help. Any assistance is appreciatedView Previous Excel Spreadsheet Version in SharePoint Online without Restoring
Due to the new AutoSave option in Excel (which I've turned off by default) my colleagues are inadvertently making changes to Excel spreadsheets that they're essentially only trying to review. My guess (from speaking with them) is that they're just enabling certain table filters or slicers in the data, or perhaps resorting records as they review certain spreadsheets. However, I can't really be sure that no data has changed and that has me concerned. Ultimately, I want to compare their version with the previous version to review what has changed. While Microsoft Word for Office 365 has great mechanisms for doing this, understandably spreadsheets are a different beast and that sort of web-based comparison doesn't seem to be available yet. What I'd like to be able to do is take the previous version (download it if I have to) and compare it against the current version. And in the long-term I've reduced the permissions for the document library folder so more people have just "View" permissions but no "Edit" permissions. Here is a image of screenshots that shows what I'm running into. https://www.screencast.com/t/1bT6y33C0t Any help for a better workflow or suggestions on what we should be doing would be appreciated. This is pricing documentation and while I want people to quickly review it, I don't want any edits to be made unless they're reviewed by multiple people and approved.
Money 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 accountsI 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!
