Forum Widgets
Latest Discussions
Summarize multiple columns of varying row lengths into one column.
I am copying old data into into Column B and add my own findings to Columns D and G. I'm looking to summarize the data as depicted in columns P&Q. What I've got making the M&N columns is M: =IF(NOT(ISBLANK(A4)),A4,"") N: =IF(NOT(ISBLANK(B4)),B4, IF(NOT(ISBLANK(D4)),D4, IF(NOT(ISBLANK(G4)),G4,""))) I don't know how to account for a variable number of rows between sample ID for column and multiple items from the columns B,D,G printing on different rows in the desired data set. Thanks in advance.
Excel will not let me click into cells or incorrect cell is selected randomly
Hi, This is an ongoing issue that I've been having for years across platforms, computers, versions of excel and workbooks. Basicially, one of two things happen: 1-I click on a cell but nothing happens, the cell is not selected 2-I click on a cell but the wrong cell is selected (Example: Click on cell B2 but excel will select C2) This will happen as frequently as a few times a day (across multiple workbooks) to every month (again, across multiple workbooks) In ALL cases cells are NOT protected Saving the workbook, closing then restarting excel fixes the problem. As I mentioned before, this has been ongoing for years now across multiple computers, workbooks, operating systems. All systems and versions of excel were fully up to date at the time of the error. Because this is happening over several different workbooks, I do not belive it is a workbook issue (especially when it doesn't happen 100% of the time to ANY workbook) Systems used: Macbook Pro (circa 2015), OS up to date - No longer have this computer Windows 8 (again, old computer) Windows 10 (old computer Windows 11 (2 different computers, currently owned) I have always kept excel and the OS fully up to date and I'm currently using the most recent version of Windows and Excel. In all cases, I have used built in trackpad and a 3 different wireless mice (with full batteries). Still randomly occurs. It's really just an annoyance and like I mentioned before, saving the workbook and restarting excel solves the issue... until next time (which could be later that day or a few weeks...It's very random.)
Run Multiple sets of data through set formula's
Hello, First off thank you for taking the time to look at this question though I would not be at all surprised if I am completely wrong about what I actually need. One of my colleagues and I have been working on a calculator of sorts for and we're able to get the result we wanted when you run one set of information through it at a time. However, what we want to be able to do is run multiple sets of data through it otherwise it is not the time saver we are looking for. Here are some screen shots: This is not real data as I am not sure if that would get me in trouble and I made the layout basic just to illustrate what I am trying to do. The Orange boxes are the information you would enter, so the artice, site and so on. The blue information are the result we receive back from the formula's we have already entered obtained from a data sheet we have setup. I think the tricky part is when you fill in the orange information, data is pulled from our other sheet which fills out this table: That's what make it hard to do multiple at once as the table as far as I can figure out, can only be populated one-by one. The goal would be able to upload any number of sets of those article, site, vendor and QTY's groups, and be able to simultaneously run them all to gather the needed information to vastly speed up this process. If providing more details on any of the other formula's we have will help, please let me know and again, any advice would be 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?
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.
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 Thanks
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!
