Forum Widgets
Latest Discussions
Macro Formula Relative Referencing
Hi there I am trying to create a macro to use at the end of every month to convert a report into a format the client requires. The amount of rows in the report will differ every month so using relative referencing. I cannot figure out how to get the formulas in Cells G2 & H2 (which I have added while creating the macro) to reproduce regardless of the amount of rows in the report? They always stop at row 14. Thanks for any help. Sheet attached. Quit new to this. Microsoft® Excel® for Microsoft 365 MSO (Version 2505 Build 16.0.18827.20102) 64-bit PC Windows 11 Business
Using a checkbox to toggle the Char(10) command inside a textbox now working
Hi All, I am attempting to build a crude text editor by using 4 checkboxes in a row, where the first 3 checkboxes toggle on and off individual text strings in other cells, and the 4th checkbox is linked to a cell that contains the =char(10) function, to add line breaks when I choose to add them. I have 20 lines of these checkboxes in a vertical column, and corresponding cells that contain text that display if it's checkbox is checked ("TRUE"). I then have another very basic equation just using ampersands "&'s" that join each horizontal line of texts toegther on the right hand side into 1 result cell, and a final equation (again using ampersands) that vertically gathers up these result cells into one big final result cell. Lastly, I made an Activ-X text box, and linked that to that final result cell to display these results more visually as sort of interactive essay. I am using the ampersand characters to keep everything very simple, since what I wanted to do didnt work with the TEXTJOIN command, and I thought that was the problem. Each horizontal line (using the first three checkboxes) succesfully joins the text strings together, and the result boxes work perfectly, and put everything together into one result, and I can see that in the text box. As one big long string of text. No problem there. What I want to do, however, is to be able to use the 4th checkbox on each horizontal line to add a line break if I want (toggle on and off the char(10) function to add in a line break). And that's where I'm running into a problem. I have the properties of the Activ-X text box set to "Wrap Text". But Excel is treating my "=char(1)" as text, not as a command. The 4th checkbox is correctly linked to the cell with the "=char(10)" command in it, and that "=char(10)" cell is formatted to "general". So I'm not sure how I can get this to work. = / Any ideas? A large thank you in advance to all you brilliant folks who take these questions on. You're life-savers. : )
Help with with Lookups etc.
Hi I’m looking for help as I’ve exhausted my knowledge and cannot sort this out. I have a spreadsheet with columns A-L. The sheet goes from A2:L90 (see attachment) Columns A and B are surname and first name. Columns C and G:H contain the roles held by those people. They may have several roles. A particular role may be in any of those six columns. All the different roles have their own individual empty worksheet, with a tab naming the role, that will hold the data found in the LookUp exercise. I want to have a macro that enables me to click on a role name (e.g. “Band Member” or “Role A”) from a dropdown list containing all the different roles. Then I need a function (I have tried HLookup, VLookup and Xlookup with no success, but I’m obviously doing something wrong) that can look across the master spreadsheet, and identify, say, all band members (or whatever role I click on from the dropdown list) that have that role shown in any of the columns C or G-K. It then needs to copy those names onto (in this case) the “Band Leader” worksheet (see attachment). I’m totally flummoxed on how to do this after hours of trying to the best of my limited ability. I would be most grateful to be pointed in the right direction.
Delete / Disable Quick Access
How do I delete entries or disable entirely this "Quick access" view in Excel/Word when clicking File -> Open: Recently migrated a user to Windows 11, now when they open Excel/Word, it hangs and then crashes while trying to enumerate this Quick access list. We're running Office 365 / Click-to-run Excel version 18730.20220.
Power Query is Missing Some Values on Import, Causing IDs to Map to the Wrong Records
Hi. I have a Power Query / Excel issue that I just cannot find a solution to, or a similar problem online. I have a third party cloud based database which I am querying into Excel via an OData connection. The source database contains some duplicate names. As this is real company data, I've created a little demo (screenshot below) to explain the problem. So say I had two companies called Bob's Buns in my original data set. Power Query does not pull the second instance of the name "Bob's Buns", which causes all the names below to line up with the wrong IDs. So now "12" is linked to "Clare's Cakes". You'd think I could just make sure that no two companies in my database have exactly the same name and this would fix the issue, but it doesn't. When I pull the data again after fixing the duplicates, it STILL results in this misalignment error. In fact if I renamed Bob's Buns (ID 11) to "Bob's Buns Bristol", and Bob's Buns (12) to "Bob's Buns Birmingham", Power Query still returns exactly the same result you can see under "What Power Query Returns" below. It's definitely the duplication causing the error, as there were a few instances of this within the database, and the same thing happened. I have done a static export to Excel from the source database, and the results line up correctly with their IDs. However, when I used OData & Power Query, I get this infuriating error. I hope I have explained myself OK! Help very much appreciated!
Toggling autosave on saves despite saving has been disabled in VBA
I have an Excel spreadsheet that is used to calculate certain data and give a printable report. For the report patient information must be given. That information must not be saved. To accomplish that I've put following code into ThisWorkbook, and it works mostly as needed: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True 'Cancels any request to save the file End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ThisWorkbook.Saved = True 'Tells Excel that the file has already been saved (this prevents Excel from requesting that you save the file when you close it) End Sub And autosave has been disabled with code: Private Sub Workbook_Open() If Val(Application.Version) > 15 Then If ActiveWorkbook.AutoSaveOn Then ActiveWorkbook.AutoSaveOn = False End If Yet, when autosave toggle is clicked on, Excel saves the file. If the autosave is left turned on, after few seconds it displays the message, and doesn't allow saving any more, but at the moment the autosave is turned on, the file is saved. I've found a workaround. If I mark the file as final version, it can't be saved, but then it also needs additional click of the "Edit anyway" button before any data can be inserted. The file is available to other users via SharePoint, I can't manage file permissions. Is there any other way of dealing with this?
Excel search box not returning hyperlink
Hi All, Need help to show the hyperlinks when using the search function on my spreadsheet. I followed a tutorial to create a search box on excel using the 'Filter' Function. using this formula: =FILTER(Table1,ISNUMBER(SEARCH(B4,Table1[Supplier]))+ISNUMBER(SEARCH(B4,Table1[Range]))+ISNUMBER(SEARCH(B4,Table1[Supplier Colour])),"No Match") Column B shows uses the Hyperlink function so products can be quickly viewed. However when using the search box on the search on the other sheet the results the hyperlinks are not preserved Is there a way to preserve the hyperlinks when using the search box any help will be greatly appreciated ThanksMove cursor randomly appears on cell corner hover instead of fill cursor
Hi, I'm on OSX running the latest 365 suite. It's fresh install with no configuration changes, add-ons, etc. The behavior I expect is when the mouse cursor is hovered over the corner of a cell the "fill cursor" that is draggable as shown: Note that the default option "Allow fill handle and drag-and-drop cells" is enabled. But inexplicably, some cells will show a move cursor instead when hovering over the corner. When you click in that position, an empty hovering box pops up: If you hit DELETE while the box is selected, it becomes deselected but is not removed from the sheet. I uploaded the file to Excel for Web and it did not have this same behavior. So it appears to be an issue specifically with Excel for Mac. To be clear, this started occurring after some formulas were being worked on. What could cause this? Thanks for your help!
