Recent Discussions
Move 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.33Views0likes4CommentsSum 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!Solved296Views0likes15CommentsHow can I adjust this VBA code to effect the sheet not the workbook
I have this code for clearing checkboxes from an inserted object, but it clears the entire workbook. Does anyone know how to adjust it to clear the sheet it is placed in. Please note I chose this code because the checkboxes are in groups and it was still able to clear the boxes. Thanks Sub Oval1719_Click() Dim sheet As Worksheet For Each sheet In Sheets On Error Resume Next sheet.CheckBoxes.Value = False On Error GoTo 0 Next sheet End Sub34Views0likes3CommentsCharts not displaying
Office 365 recently updated. None of the charts on separate worksheets from earlier recent Excel spreadsheets will display. They will display on the same worksheet as the data, but cannot be moved to a separate chart worksheet. Tried everything to fix this. Help.27Views0likes2Commentsformule Excel : combien de fois personne X est de "garde" un samedi ou dimanche
Bonjour à la communauté je cherche une formule dans tableau Excel pour dénombrer le nombre de fois où un médecin est de "garde" le samedi et le dimanche selon schéma suivant : j'essaye avec "Nb.Si.Ens" mais cela ne fonctionne pas merci de votre aide26Views0likes1CommentHow do I return multiple continents for multiple countries?
I have a list of soft drinks being sold in different countries. I want to return the continents for them. i.e. Soft drink | Countries | Continents Coca cola | China, Denmark, Italy, Australia | Asia, Europe, Australia Fanta | Korea, Japan, China | Asia I have the corresponding countries to continents ready. But it seems like no formula works for this23Views0likes1CommentFind out & highlight duplicate data(number)within same excel sheet in diff column or in diff sheet
I have number in excel column A like "5434567880765" and so on & in column B a JSON Data is present where the no "5434567880765" may be present? So how to found the duplicate & highlight it in the same or different row in that excel sheet ? PFB the Excel column A & Column B example, It's may be present in same sheet or different sheet. A B 4356778 "team":"In","test":"4356778","IsPresent":"True" 9876546 "team":"US","test":"9876546","IsPresent":"True" 5435657 "team":"US","test":"2874541","IsPresent":"True"5Views0likes0CommentsHow to avoid calculating the same value over and over again - performance best practice
Suppose you want to compare each cell in a range with the total average. What is the best solution in terms of performance? Place the cell with the Average formula to the left or top Place the cell with the Average formula to the right or below No need to load the Average to a dedicated cell. A Lambda wrapper for the Average does the trick. Other Many thanks.29Views0likes1CommentExcel add same formula easy way
Hello, I have numbers in C1 till Z1 cells. I would like to multiply these cells value with A1 value. Example: +A1*C1, +A1*B1......+A1*Z1 I know I could write one by one. But is there a simple way to add all cells to this formula? So if I change A1 cell value all the values have to change. C1-Z1 is not fixed. It have to change when A1 changing.747Views0likes5CommentsIFTRUE() 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")Solved85Views0likes7CommentsTengo una base de datos e Access que contiene una serie de documentos Word y PDF.
Tengo una base de datos e Access que contiene una serie de documentos Word y PDF. Me ha funcionado perfectamente hasta el día 14 del 05 de 2025 que ha habido una actualización, desde entonces no puedo abrir ninguno de los documentos Word, me aparece el siguiente mensaje: Microsoft Word Word detectó un error al intentar abrir el archivo, Pruebe Io siguiente: * Compruebe los permisos del documento o la unidad. *Asegúrese de que hay suficiente memoria y espacio de disco. Abra el archivo con el convertidor de recuperación de texto. y cortar un Jamon.docx) Sin embargo, los documentos PDF los sigue abriendo perfectamente. Pero me sorprende que tengo instalada una copia de esta misma base de datos en otro portátil que no ha sido actualizado y me abre estos mismos documentos Word perfectamente, sin ningún problema. ¿Qué puedo hacer para solucionar esta situación?24Views0likes1CommentColumns in Footnotes
Hello, I have a document full of footnotes and oftentimes the footnotes are lengthy and I would like to make the footnotes appear in columns. How do I access the column format settings for the footnotes? Can't find any help online anywhere!! Any ideas?? Thanks4.3KViews0likes5CommentsExcel Add Text after first character _
Hi I was asking people to help me with adding text and although I hoped they may have answer none of 30 excel day to day excel people could help me and I am hoping that there is bigger expert here so they may give me some hints I have test like this DVS.101_SERVICE-LAN-IN_172.16.25.1 DVS.2047_INTERMEDIA-WEB_test DVS.2070_cCFS_DMZExt_192.168 Now I only need to add work SYN after first red character. I have used Kutools and although good it adds this word after every _ character which is not what I want I would need to add only word SYN after first red character. It doesnt work by position because some have 8 or 9 position so that wouldnt be the patern NOt sure if excel can look and only add that word after first _ character and not the rest of the cell20Views0likes4CommentsLoop workstation read only access still on the roadmap?
There is an article on the Microsoft 365 Blog "Loop governance, lifecycle, manageability for IT Admins - Nov 2024" by dancontoso that indicates read only access for workspaces are coming H1 CY2025. Is that still the case? I have a user asking. I looked on the Roadmap and didn't see anything specifically relating to this, so I am concerned it was canceled or pushed back.5Views0likes0CommentsPower-Query Manually Replacing Missing Data within Queried Column
Hello! I want to start off by saying I've never worked with Excel professionally before, I've only used it for very specific purposes that fit my Interest. So this might have a easy solution or plainly be impossible. My current interest is Tracking the meta-data of Articles I read online. Through Power-Query I've found a way to automate this to a point where I can just add the link of the article to a list of URLs and power-query fills out all the meta data, because I've trained it to pull the correct data through the "Table from Examples" function of Power-Query. Following the instructions of the following Article, I've Changed the code of the Power-Query to accept multiple links instead of one singular one with all the Data.: https://www.howtoexcel.org/how-to-extract-data-from-multiple-webpages/ The code looks something like this: let MetaData=(URL) => let Quelle = Web.BrowserContents(URL), #"Aus HTML-Code extrahierte Tabelle" = Html.Table(Quelle, {{"Title", "H4:nth-last-child(4) > :nth-child(1)"}, {"Author", ".heading:nth-child(1) A + *"}, {"Topic", ".landmark + .tag"}, {"Published", ".Date*"}, {"Publisher", ".category-slash *"}, {"DateAccessed", ".time*"}}, [RowSelector=".blurb"]), #"Geänderter Typ" = Table.TransformColumnTypes(#"Aus HTML-Code extrahierte Tabelle",{{"Title", type text}, {"Author", type text}, {"Topic", type text}, {"Published", type text}, {"Publisher", type text}, {"DateAcessed", type date}}) in #"Geänderter Typ1" in MetaData (It's in German, but Quelle=Source, "Geänderter Typ"="changed Type". I've modified it slightly to hopefully reflect the simulated Data later in this post.) This Connection is then used to insert a Custom Column and expand it, so the Data appears in the new table. The Problem I'm now having is that some of the Articles are only accessible with an Account on the page I'm using to read the Articles. This means that Power-Query Returns null for those articles across all columns. Original Table: Extracted Data: (This is manually simulated Data, not actual links to the articles) What I now want to do, is manually insert the Data for the Article in Row 4. I've coloured them red in this screenshot to show what I mean. However, if I refresh the table (For example because I've added more links to the original Table, because I've read more Articles), the red data disappears. My browsing of Excel Forums has taught me that it's possible to create a self-referencing-query to manually add data to a table, if that data is in it's own, new Column. However, since my manual data is in the same columns that the power-query fills out, and I lack the fundamental understanding how Power-Query or the programming language of it works, I don't see how this solution can help my case. I'd love to be proven wrong. I hope somebody can help. Thank you in Advance :) - Yorin6Views0likes0CommentsFormatting 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?Solved25Views0likes4CommentsDownload and Save completed forms
Hi there, does anyone know a way I can download completed forms in the form format? I am trying to set up a form to use as research consent and it would be helpful if I could save each response separately and view it as a document. The only way I can seem to download anything is in an excel and it saves all the responses together. Thanks!12KViews0likes2CommentsTo-Do Overdue tasks
Tasks in folders that are not completed lose focus. All my tasks are in folders to categorize them. Whether I flag the task as "My Day" or not, the next day the task is not in "My Day" or in "Planned" views. I suggest that the tasks stay in these views showing as "Overdue". Or is there something I should be doing differently?20KViews14likes21CommentsButton for reset all filters
Hi, First of all I am not at good at this but I looking for a way to use a button to reset all the filters I have. To use one button instead of clear all the filters separately would be great as it is more effcient. But as I wrote, I am not very good at this I was wondering if someone could show/teach me how to do it.84KViews1like3CommentsSpilled 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!Solved85Views0likes5Comments
Events
Recent Blogs
- We are excited to announce that the highly anticipated keynote sessions from Jeff Teper, Vasu Jakkal, Jared Spataro, and Brenna Robinson are now available for all to experience. Visit our conference ...May 22, 2025167Views0likes0Comments
- 2 MIN READWe've eliminated the unused space between the ribbon and your canvas, giving you more room for what matters most.May 08, 20251.1KViews1like5Comments