Forum Widgets
Latest Discussions
sum by color when colors are set by conditional formatting
i have a column of numbers that are color coded (to represent a specific mfg department) and I need to total the values by color. Meaning I need to total all the values that have the same background color. I have done an exhaustive search online and was able to quickly find a way to sum by color, unfortunately it only worked for cells whose color was set manually (NOT using conditional formatting). I have found a few references to VBcode that should provide the functionality but I can't get any of them to run (except for the one that works for manually set colors). I have control of the data that I'm trying to sum. is there another method to "tag" values? I thought of adding a letter prefix, but coulnd't find any way to sum a column of numbers that are contained in text strings. Any help would be greatly appreciated!! I'm using Office 365 (excel 2016) on a windows 10 machineSolvedmatt nipperDec 20, 2016Copper Contributor252KViews0likes304Comments
macro VBA excel nommer une cellule
Dans une macro, je veux donner un nom à une cellule donnée, à savoir celle où je suis au lancement de la macro. si je laisse "range", avec des références absolues, la macro fonctionne, mais une seule fois. Le nom choisi disparait de la liste de la feuille, mais est toujours présent dans les noms de "formule-gestionnaire de noms". voici ce que j'ai écrit, qui ne fonctionne pas: ActiveWorkbook.Names.Add Name:="horo4", RefersToR1C1:=ActiveCell.Address Merci de m'aider!Solvedbernard_daniel_1950Oct 13, 2022Iron Contributor26KViews0likes204Comments
Stock Data Types Wont Refresh - Provides Erroneous Error Message
I have an Excel workbook I created a few months ago and this afternoon while I was away, the "stocks" data type refresh feature stopped refreshing. The error message I receive instructs me to sign in to an account associated with this product (Excel), but I am clearly signed in. To be sure, I sign out and sign back in. Hit 'refresh' and receive the same error message. MS tech support tried to make a manual repair during a chat session, but it still will not refresh. Any thoughts? Thanks in advance.forbisherJul 10, 2023Brass Contributor31KViews18likes195Comments
How to count and sum "Condtional formatting" cells by color in Excel 2010?
I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command. Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ ) My question is: Is there any functional code (or other aproach) which can count and sum all cells by "Conditional formatted" colors, on one sheet, and that the generated result is linked through common formulas? That can be updated/refreshed/code run during the work process as a complete overview of colors of a matrix (and not by picking every color every time as I mentioned). Thanks in advance!Solved280KViews1like181Comments
Conditional formatting for entire row based on data in one cell
I need all cells in a row to highlight a certain color if the data in one cell contains a specific word. What I specifically want is for an entire row to turn grey if the status cell contains the word "SHIPPED." I know how to make that specific cell highlight the color I want, but not the entire row of the sheet. Thank you for your help!SolvedAUSTXCHICKJul 30, 2019Copper Contributor3.4MViews4likes131CommentsPopulating cells with a Date across 12 TABS
Hello Community, If you are up to a challenge and if it possible to accomplish with formulas or VBA I've got a challenge for you. I have 12 sheets in a workbook that look exactly the same. Each worksheet represents a month. So Jan, Feb, Mar and so on up thru Dec. Each sheet consists of 317 ROWS. Each ROW represents an individual building number. ie, 200, 210, 215, 218 and so on. Each month I have to physically go to 26 or 27 buildings and physically read the utility meters, Water, Gas & Electric and jot down the readings. I then enter these numbers (Readings) into the workbook and on the sheet for the month which the readings were obtained for. The task here is to obtain 100% of the meter reads over the course of 12 months by dividing the number of buildings by 12 to determine the number of reads necessary per month to obtain 100% by the end of the year. So, what I am trying to accomplish is the ability to enter a date against a building number, for example JAN, and have that date populate all the other cells for that building number across the months of FEB, MAR, APR and so on right on up into DEC. In essence showing that the meters for this building have already been read and thus locking those cells from having any additional entries being made into them. The only way to change the date would be to go back into the month the meters were originally read. Any change, if it were necessary would reflect/update all those other cells. Now lets say dates were entered into the FEB worksheet for which no dates were entered in the JAN Worksheet, the same thing would happen but the date entered in FEB would also reflect into JAN as well as MAR, APR, MAY and so on right on up into DEC. This process would continue all the way up into DEC. If a date was entered into DEC for a building that did not reflect as having its meters read, the DEC date would reflect all the way back into the JAN worksheet and populate all cells for that building number up thru Nov as DEC is the originating date entry. The idea here is to be in a current month and be able to look at the current month worksheet and be able to determine what buildings still need to have its meters read. So come DEC for instance, you should only see what buildings still remain to have its meters read. Again, the idea is to read a number of meters each month that would as evenly possible to obtain 100% reads by the end of the year. More or Less could be read each month but this method will always show which ones still need to be read. So what do you think about this challenge??? This will probably keep you on your toes. Keep in mind, I have 12 worksheets, each having its own TAB within a workbook. And on each TAB I have a cell, within a column, for entering a date for Electric Read, one for Gas Read and one for Water Read. Anyone up to the challenge? More details can and will be provided upon request.SolvedCarl_61Dec 15, 2022Iron Contributor24KViews0likes121CommentsGetting this msg "Excel ran out of resources while attempting to calculate one or more formulas."
I am having issue with excel sheet which keeps showing me this issue "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas can not be evaluated." Never had seen this before- pls guide.SolvedGaneshIyerDec 19, 2020Copper Contributor260KViews2likes114CommentsExcel not working properly since updating to Windows 11
Hi, Since I have updated my PC to windows 11 Excel has not been working properly. Every 5 min the sheets seem to be unresponsive when I click on a tab or anywhere on the excel page. To make it work again I have to constantly reduce the window and reopen it. This is very frustrating as it happens very often. I have tried to start excel in safe mode or disable add-ins but the bug continues. Thank youSantero10Jan 31, 2022Copper Contributor75KViews5likes113CommentsLoad Image from path into Image ActiveX Control in worksheet
I have the following code: Private Sub img_Browse_Click()On Error Resume Next Dim img As String Dim xCmpPath As String img = Application.GetOpenFilename If img <> False Then Me.img_Photo.Picture = LoadPicture(img) xCmpPath = img Sheet1.Range("AE1").FormulaR1C1 = xCmpPath End If End Sub It worked perfectly with my userform but sadly I have to change all my coding to work on a Worksheet (Sheet3) I tried to change the code accordingly, but nothing seems to work: Private Sub img_Browse() On Error Resume Next Dim img As String Dim xCmpPath As String img = Application.GetOpenFilename If img <> False Then Sheet3.Shapes.Range(Array("img_Photo")).Picture = LoadPicture(img) xCmpPath = img Sheet1.Range("AE1").FormulaR1C1 = xCmpPath End If End Sub Please can someone tell me what is wrong with the above code? And how can I set it back to "" again when I use a "clear" command button. I struggle greatly to change all the userform coding to work with the combo boxes, combo list, image etc. on/in my sheet.... And this is only the start of all the changes I have to make 😞Solved7.6KViews1like108Comments
Copy/paste no longer working in Excel
Sometime in the last few months the copy/paste function within Excel (Office 365 version) stopped working for me. I have updated Windows and all Office apps to the latest version but that hasn't fixed the problem. I've also run the Office 365 The symptom is very strange. When I hit Ctrl-C (or use the drop-down menus) on a cell in Excel the dashed lines that surround a cell selected for copying show up very briefly but immediately disappear. If I go to another cell and paste it just puts a open parenthesis "(" in the cell. Using Ctrl-X to cut exhibits similar behavior. If I repeatedly hit Ctrl-C it will occasionally work (the dashed lines remain and I can paste) but it typically takes 15-20 tries before it will work and if I try again it will fail. This only seems to affect me when I try to copy cells. If I select a cell and then edit the cell and manually select the content of the cell I can copy and paste with no issue. It also appears to only be an issue when a cell is not empty. If I select an empty cell I can copy/paste just fine. If I select a cell that has been highlighted but has no content that also works properly. It's only when there's text or a formula in the cell that this is an issue. This only affects Excel, I haven't observed the issue in any other app. Any ideas?SolvedJon FiroozOct 03, 2017Copper Contributor894KViews0likes102Comments
Resources
Tags
- excel41,701 Topics
- Formulas and Functions24,153 Topics
- Macros and VBA6,278 Topics
- office 3655,822 Topics
- Excel on Mac2,581 Topics
- BI & Data Analysis2,283 Topics
- Excel for web1,849 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,583 Topics
