Forum Widgets
Latest Discussions
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.
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?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.
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. : )
Conditional formatting for row based on one cells value compared to 2 other cells
I’ve attached two pictures of a sales report. The black and white is what I’m starting with and the colored version is the goal (currently coloring it manually) I need the row to highlight a certain color if the data in column i of that row is less than the cell next to it (column J), or a different color if it’s greater than or equal to another cell (column k). Is this possible to do without having a different rule for each row? If so, how?Custom number formatting help
Hello, I need some help/advice for displaying a value. Im scaling large recipe amounts, and at some point it'll become cumbersome to do '112 tbsps' and I'd prefer it read '7cups' after a threshold. Ive got a lot happening in an office script that I've made and Ideally I'd like to avoid further complicating the core formula of these cells. So I was looking at number formatting as a possible solution. However, Im struggling to figure out if its possible. Right now I can obviously check for the thresholds, but the formatting itself doesn't seem to do any of the actual math. For example [>=32] #/16 "cup(s)";# ?/? "tbsp" just returns 1792/16 cup(s). Rather than divide the value, it scales it up by 16 which is... confusing. Could someone tell me what I'm missing? Or am I looking in the wrong direction here trying to use the number formatting and instead I should work in the CONVERT function into my cell formula? I'm hoping to avoid that, so I thought I'd ask for help. Thanks!
formula help
I have a spreadsheet where it list if a person is ordering a certain item and what size they are requesting. I need a formula that will use the total column to add together all the items that have sizes or quanities by the price of that item with a total of all the items. Champions Black Sweatshirt $52 Bella Canvas Black Sweatshirt $30 Champions White Sweatshirt $52 Bella Canvas White Sweatshirt $30 Water Jub $35 TOTAL DUE $52.00 $30.00 $52.00 $30.00 $35.00 MED 0 0 1 $ 52.00
Using filter to populate cells from the bottom up.
I am using filter to populate calendar day cells from the top down, but would like to populate from the bottom up (see below), but would rather fill the cells in from the bottom up. A calendar day cell is illustrated below. the current formula is: =FILTER(Dates!$H$4:$H$37,Dates!$G$4:$G$37=DAY(H9),"") Where: Dates is a tab with the dates and tasks to be shown G - is the day number part, I.e. 10 H - is the text to be displayed, i.e. April Fool’s Day (n this tab H9 is the date value where DAY(h9) = 10
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.
