Forum Widgets
Latest Discussions
Spilled formula calculating revenue by quater or year
Hi, I am trying to create a formula takes all project from a table and spills those projects in revenues per quarter or year (both would be handy to have) and chops the revenues in those quarters or years accordingly. See the attached simple example. I know that I can use sequence in order to spill a number of months from s startindate and calculate the revenue per month, but not sure how to group that the right way. Somebody any advice how to approach? Many thanks!MichielS340May 23, 2025Brass Contributor46Views0likes1CommentIs there a way to make a Form Control Checkbox make an embedded checkbox =True
In Column B I have embedded checkboxes, while in column C, I have multiple Form Control Checkboxes in a cell. The idea is once all the Form Control Boxes are checked in a cell from column C, (lets say C2), the corresponding embedded checkbox in B2 will auto check itself, signifying all is completed in C2. I am happy to have one of the Form Control boxes activate the embedded box. Meaning. If there are 5 boxes in C2, when the 5th box is clicked (last box in the series) the embedded box in B2 gets ticked as well. Or it could be arranged where all 5 Form Control boxes have to be clicked to auto-tick the embedded box. I would be grateful either way. Below, if it helps is a screenshot.SolveditsMontyMay 23, 2025Copper Contributor38Views0likes2CommentsHow To Create a Gauge-Style Doughnut Chart that Includes Negative Numbers & Greater than 100% Values
Hello, I need to create a gauge-style doughnut Chart that not only shows the 0% - 100% range, but that will also show ranges that fall below 0%, or that go over 100%. By following tutorials on YT, I've made gauge-style doughnut charts that look like this... ...and different charts that combine 2 doughnuts to look like this... (combining 2 tutorials) As much as I like the chart above with the 100 slices, the solid-fill chart works best for the spreadsheet I'm working on since I have to reduce the size down to very small (and the slices don't show up very clear when zoomed down so small), since I need to visually see up to 23 charts (one for each day of the month) on a single sheet, like this... These doughnut charts are rotated to a 270-degree angle and the 0% - 100% values work fine, but when given a value of minus 0%, the gauge doesn't work properly (rather than the gauge going the opposite direction, it fills part of the gauge...in the same direction as it would when a positive number is given. And when a value of greater than 100% is given, it doesn't go beyond the 100% level. In order to visually describe what I'm trying to achieve, I've created in Photoshop what I need the chart to do, I hope that's OK. Here is a picture of the chart (no Photoshop) that works perfectly fine when no value less than 0% or greater than 100% is given. (The inner (purple) doughnut represents the "daily target" which is always going to occupy the 0%-100% range, since that never changes. The outer (blue) doughnut represents the "actual" figure...and this figure will very often be either a negative value, or a value greater than 100%)... Here is a Photoshop'ed picture...showcasing what I would like the outer doughnut (the "actual" value) to look like when a negative value is given to it... And here is another Photoshop'ed picture...showcasing what I would like the outer doughnut (the "actual" value) to look like when a value greater than 100% is given to it... Thank you so much for your time! I would appreciate any help in being able to create this gauge chart that allows for negative values and values over 100%. Kind Regards, NathanSolvedntlknightMay 23, 2025Copper Contributor6.6KViews0likes14CommentsPrevent Text Box from being copied to a new sheet .
Is it possible to add a text box to a sheet that cannot be copied when that sheet is copied to a new tab? In other words - I have a workbook with several tabs. Tab 7 is intended to be a change order template that gets copied to a new tab for each subsequent change order. I have a Text Box note on Tab 7 that contains step-by-step instructions to other users (some users are very computer illiterate) regarding how to copy the sheet and rename the copied tab (by right clicking tab 7, selecting "Move or Copy...", selecting "move to end" and checking the "Create a copy" box. I'd like the new tab to NOT contain the instructional text box. I've read several AI generated responses and tried all of them but none of them seem to work. One of them was recreating the text box as an "ActiveX Control Text Box". That was no help at all. I got the same result. I'm using Excel 365. Thanks in advance for any help.erictengoMay 23, 2025Occasional Reader25Views0likes2CommentsHighlight Duplicates but only from one column
Hello, I have two columns of values in my screenshot. Column D and Column E. Column D has some duplicates and Column E does not. How do look at both columns but only highlight the duplicates in column D, only IF they appear in column E? Thanks!1162646322May 23, 2025Copper Contributor68Views0likes2CommentsMove up to next blank row after copy/paste from previous sheet.
Snowman got me rolling with code that does almost exactly what Im trying to do. After I hit end of day button Im trying to get the copied data to move up to the next blank cell in column B within a range. If I have any data in B2:B9,B11:B21,B23:B29 marked with a t next to it in column A and hit the end of day button only data in cells marked with that t are moved to the next sheet in the same cells they came from. What Ive noticed is only data in B2:B9 go to the exact same cell. B11:B21 are offset by 1, and B23:B29 are offset by 2. This is fine as the data is still moving. What I want it to do is for those ranges move up to the next blank cell in column B. So if I have data only in B4, and B6 I want that to transfer to next sheet in B2,B3. The same for the other 2 ranges. I also dont want it to overwrite anything that may already be in a cell on the next sheet. If I have "Tree" in B2 of the next sheet I want the data to go to B3,B4. Same for the other two ranges. I have tried xlUp, xlDn, and played with the code that was given to me by Snowman to try and make it work. Im not getting anywhere with this. I thought maybe if I create another macro that after I transfer the data would move everything up into blank cells then Id be okay with that also. Im not having any luck with that either. I even recorded a macro for copy/paste but that wont work if there is data in a cell already that I need to keep, and not be overwritten. I have attached a copy of the workbook. Hopefully this time it will allow it to be published with this query.BrianP475May 23, 2025Copper Contributor26Views0likes3CommentsFormatting a table based on todays date
I created a table that contains information the next time employees are eligible to order uniforms based on their start date. I want the spreadsheet to highlight the row for each employee based on "today's" date. I add funds to their accounts, which allows the employee to order their uniforms when they are ready for new ones. I created a table that includes conditional formatting. Now I use "Format only cells that contain". I am using TODAY() date as the criteria. I want the formula to select the entire row. Right now, it only selects the cell. I would also like for the table to email or notify me daily who is eligible. Is this question more of a VBA than conditional formatting?DnGrMay 22, 2025Brass Contributor20Views0likes3CommentsIFTRUE() formula that does nothing if False
I want a built-in Excel worksheet function named IFTRUE() { IFTRUE(logical_test, value_if_true) } that is similar to the IF() function, except that it performs an action only if the test condition is True. However, it should do absolutely nothing (takes no action) if the condition is not True and should not contain a value_if_false parameter - not even an optional one. Using the formula IF() formula, such as IF(1+2=3, "True", ""), does not work for my purposes because it a writes an empty string to the target cell when the condition evaluates to False. Using the slight alternate of IF(1+2=3, "True") without the value_if_False parameter also does not work because this one writes a blank value, a zero (0), or "FALSE" to the cell if the condition evaluates to False. The reason this does not work is because assigning any value at all is not the same as doing absolutely nothing to change the value of the target cell. This is the whole point for this function because performing any action at all upon the taget cell when the condition evaluates to False imposes negative consequences for numerous other things I am trying to do within the worksheet. Even if it merely gives the cell a blank value or a value of 0/False/"", the IF() function still results in some action on the target cell, and I want it to do nothing at all if the condition parameter evaluates to False. If the condition parameter of the IFTRUE() function equates to 'False' I do not want: - A blank value written to the target/currently selected cell. - A value of zero (0) written to the target/currently selected cell. - An empty string value ("") written to the target/currently selected cell. - A triggered Worksheet.Change() for the target cell. If the conditional parameter evaluates to a value of False, simply exit the IFTRUE() function immediately and without doing ANYTHING else!! The current IF(logical_test, value_if_true, value_if_false) function equates to the following Visual Basic conditional structure: Using IF(1+2=3, "Evaluates to True", "")... If 1+2=3 Then Range("A1").value = "Evaluates to True" 'Or some other command. Else Range("A1").value = "" 'Or 0, or "FALSE"... depending on the cell's formatting. End If I want my IFTRUE() formula to do absolutely nothing at all if the condition parameter evaluates to False. So, my IFTRUE(logical_test, value_if_true) formula would, instead, equate to the following Visual Basic conditional structure: If 1+2=3 Then Range("A1").value = "Evaluates to True" 'Or some other command. Else Exit Sub End If Or... If 1+2=3 Then Range("A1").value = "Evaluates to True" 'Or some other command. End If This is somewhat similar to a Trilean custom data type I created to replace the limited Boolean data type: Rather than always being limited to the following built-in Boolean data type: Public Enum Boolean 'Indicates whether a data member, routine, property, event, event handler, or conditional statement evaluates to a Boolean state of True or False. True = (-1) 'Equates to True, yes, on, or positive. False = 0 'Equates to False, no, off, or negative. End Enum I created and often use the following custom data type: Public Enum Trilean 'Indicates whether a data member, routine, property, event, event handler, or conditional statement evaluates to an extended Boolean state of True, False, or neither. Aye = 1 'Equates to True, yes, on, or positive. Nay = (-1) 'Equates to False, no, off, or negative. Nil = 0 'Equates to Neutral, nothing, null, undefined, uninitialized, or undetermined. Neither True nor False. Neither On nor Off. End Enum Very often I need to distinguish whether a property, a function, or a variable was referenced or initialized and it actually returned a value of False, or if it has not yet actually been referenced or initialized at all. So, using a 'False' value to represent both an actual returned value of False and to indicate that the member has not yet been referenced or initialized just doesn't cut it for me - because they are 2 different things and I need to know specifically which occurred. Likewise, just as a Boolean data type sometime needs a third "neither" or "neutral" value, so too, does an IF() function sometimes needs a companion IFTRUE() function that does nothing when the condition evaluates to False, or rather, it performs an action only if the condition evaluates to True. An IFTRUE() function is easy enough to achieve using VBA. However, the IT departments at many, many companies (like my employer) block all macros and VBA within Microsoft documents. Also, allowing and using circular references in Excel at work is not an option. So, an IFTRUE() formula would very easily solve this issue. And since I've seen numerous others ask about accomplishing the same thing in other forums, I know others have a need for this feature, as well. EDIT: This formula can also evaluate to True for intentionally false (inverse) condition values, thereby eliminating the need for an additional IFFALSE() function: IFTRUE(1+2<>5, "This also evaluates to True") or IFTRUE(NOT(13+2=7), "This also evaluates to True")SolvedGypsyPrinceMay 22, 2025Copper Contributor68Views0likes5CommentsSum from cell across multiple sheet, depending on hire date
I can not figure this out, I am trying to make a summary sheet that will count the vacation days across multiple sheets from "Start" to "All" (these are timesheets between those named sheets) for employees. But I need the count to reset on their hire anniversary month and day. Employee 1 - Hired on 1/2/2001 (found in cell B5 on the sheet named "Summary") Employee 2 - Hired on 4/9/2020 (found in cell B6 on sheet named "Summary") In this example, any vacation days in cell F5 (employee 1) across multiple sheets between sheets named "Start" to "All" will count until the anniversary month and day arrives, then it will reset and start counting forward again until that date arrives again. Any help will be much appreciated!SolvedLisaB1009May 22, 2025Copper Contributor276Views0likes13Comments
Resources
Tags
- excel42,727 Topics
- Formulas and Functions24,785 Topics
- Macros and VBA6,424 Topics
- office 3656,055 Topics
- Excel on Mac2,659 Topics
- BI & Data Analysis2,382 Topics
- Excel for web1,930 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,647 Topics