Welcome to Exceldemy, the RU-vid channel that is dedicated to helping you master Microsoft Excel!
Our channel offers a wide range of Excel tutorials, tips, and tricks that are perfect for beginners and advanced users alike. Whether you're looking to improve your Excel skills for work, school, or personal projects, our videos can help you achieve your goals.
We cover everything from basic Excel functions to advanced formulas, data analysis techniques, and automation tricks. Our tutorials are easy to follow, and we use real-world examples to show you how to use Excel in practical situations.
So, if you want to take your Excel skills to the next level, make sure to subscribe to our channel and hit the notification bell icon to stay updated on our latest uploads. Thank you for watching, and we look forward to helping you become an Excel master!
Hi, I have added status on column A (ongoing, closed, not started,Waiting to receive the product ) and different dates on column B (received date). How can i use conditional formating on column A to change the status from ongoing or not started to "Alert" if the received date on column B is older than 30 days? But not change the status if the status is close (even though the date is older then 30 days).
Hello @EniCipa, To set up Conditional Formatting for your scenario in Excel, follow these steps: 1. Select Column A where your statuses are listed. 2. Go to the Home tab >> from Conditional Formatting >> select New Rule. 3. Choose Use a formula to determine which cells to format. 4. Enter the following formula: =AND(A1<>"Closed", OR(A1="Ongoing", A1="Not Started"), B1<TODAY()-30) Adjust A1 and B1 to match the first cells of your selected range. 5. Click on Format, choose your desired format (like changing the font color to red), and click OK. 6. Press OK again to apply the rule. Now this will change the status to "Alert" for ongoing or not started items if the received date is older than 30 days, while statuses marked as "Closed" will remain unchanged. Regards ExcelDemy
I have tried this many times I created my own XML Source File, named the Columns the same as the Source Fields, when I attempt to Map the Sources to the Header (A1), it only selects A1 and not the data in A2, A3, etc... Same for all other columns. So when I export, it's only exporting out only the Row A data, none of the additional rows that have data in them. I can't figure out what I'm doing wrong. Thank you
Hello @mirghtaed, You are most welcome. It seems like the issue is related to how you're mapping the XML fields. Make sure you're selecting the entire range of cells (not just A1) for the mapping. XML mapping links the data structure to Excel, so if it's only selecting the header, it won’t map the entire range. Double-check that the XML schema matches your data structure and that your range selection is correct for the rows. Try remapping by dragging the field onto the entire column, not just the header cell. Regards ExcelDemy
Hello @muhammedalibuke4966, You might be referring to a missing line on a distribution chart in Excel. This issue could be due to the chart type selected or data not being plotted correctly. To fix this: Ensure you’ve selected the correct chart type (like a line or scatter plot). Check if the data range includes both the X and Y values. Right-click on the chart and select "Select Data" to verify if the data series is added properly. Regards ExcelDemy
Hello @sp1375, Thanks for your appreciation. You can post your templates requirements in our ExcelDemy Forum: exceldemy.com/forum/ exceldemy.com/forum/members/shamimarita.2/ Keep learning Excel with ExcelDemy! Regards ExcelDemy
Hello @valjosol12, You are most welcome. Glad to hear that our tutorial helped you to solve you problem. Our aim is to make the Excel easy to use. Keep learning Excel with ExcelDemy! Regards ExcelDemy
'In this example I am Copying the Data from Sheet1 (Source) to Sheet2 (Destination) Sub sbCopyRangeToAnotherSheet() 'Method 1 Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E1") 'Method 2 'Copy the data Sheets("Sheet1").Range("A1:B10").Copy 'Activate the destination worksheet Sheets("Sheet2").Activate 'Select the target range Range("E1").Select 'Paste in the target destination ActiveSheet.Paste Application.CutCopyMode = False End Sub
Hello @kounnaris, You’re demonstrating two methods for copying data from "Sheet1" to "Sheet2" using VBA. Method 1 is a straightforward one-liner, while Method 2 involves multiple steps to achieve the same result. Both methods are valid; it just depends on your coding style. If you have any specific questions about either method, feel free to ask! You can find more details in the original post. Regards ExcelDemy
Hello @ph.mekawey, To add Power Pivot to Excel on a Mac, follow these steps: 1. Ensure you have the latest version of Excel for Mac, as Power Pivot is included in Office 365 subscriptions. 2. Go to the Data tab and look for the Manage Data Model option. You might need to install the latest updates via the Help menu if not visible. 3. For additional functionality, consider using Power BI, which integrates with Excel. Regards ExcelDemy
Hello @SyedRehanAli-u6w, The "Compile error: ByRef argument type mismatch" typically occurs when a procedure is called with an argument that doesn't match the expected data type. In the case of Private Sub UserForm_Activate(), ensure that any parameters being passed to functions or subs are of the correct type. Check your variable declarations and any function calls related to the UserForm. Regards ExcelDemy
Hello @MrDeloitte , The "system" in a swimlane flowchart refers to how the process is organized into different lanes, each representing a person, department, or system responsible for specific tasks. It visually distinguishes responsibilities and interactions between various entities in a process, making it easier to understand workflow and accountability across teams or systems. Regards ExcelDemy
Is it possible to change the checklist output (in the VBA code) to show them items as a visual list within the cell... separated by alt+enter (carriage return list) rather than a semicolon? Is it also possible to copy/paste the checklist that I have created into other cells? I am creating a list of schools and my checklist shows the services that each school provides. I hope this makes sense! Thank you for the amazing tutorial!
Hello @amylawson404, You are most welcome. Thanks for your kind words. Yes, it is possible to change the checklist output. You can modify the VBA code to display the selected items as a list separated by Alt+Enter (carriage return) instead of a semicolon. For copying/pasting the checklist to other cells, you will need to adjust the code to handle the pasting functionality properly, ensuring the checklist works for each cell. Updated VBA Code: Sub Button_Click() Dim buttonShape As Shape, listOption As Variant, M As Integer, N As Integer Dim xP As String, resultStr As String Set buttonShape = ActiveSheet.Shapes(Application.Caller) Set checkListBox = ActiveSheet.checkList If checkListBox.Visible = False Then checkListBox.Visible = True buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students" resultStr = Range("CheckListOutput").Value If resultStr <> "" Then resultArr = Split(resultStr, Chr(10)) ' Split using carriage return For M = checkListBox.ListCount - 1 To 0 Step -1 xP = checkListBox.List(M) For N = 0 To UBound(resultArr) If resultArr(N) = xP Then checkListBox.Selected(M) = True Exit For End If Next N Next M End If Else checkListBox.Visible = False buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here" For M = checkListBox.ListCount - 1 To 0 Step -1 If checkListBox.Selected(M) = True Then listOption = checkListBox.List(M) & Chr(10) & listOption ' Use carriage return instead of semicolon End If Next M If listOption <> "" Then Range("CheckListOutput") = Left(listOption, Len(listOption) - 1) Else Range("CheckListOutput") = "" End If End If End Sub The items are now split and joined using Chr(10) (carriage return) instead of a semicolon (;), so they'll appear as a list when you press Alt+Enter. The code still allows the checklist output to be copied and pasted into other cells, and the checklist will function in those cells. Regards ExcelDemy
@@exceldemy2006 thank you very much for taking the time to provide this information. So helpful and kind! I have subscribed. I cannot figure out how to copy/paste the checklist for each school/row. Is there another video that shows how to do this? Thank you again!
Hello @amylawson404, You're most welcome, and thank you for subscribing! Unfortunately, we don't have a specific video on that yet. To copy and paste the checklist for each school or row, you must ensure that each row's checklist functions independently. Use ActiveCell in the code to tie the checklist output to the selected cell. Sub Button_Click() Dim buttonShape As Shape, listOption As Variant, M As Integer, N As Integer Dim xP As String, resultStr As String Set buttonShape = ActiveSheet.Shapes(Application.Caller) Set checkListBox = ActiveSheet.checkList If checkListBox.Visible = False Then checkListBox.Visible = True buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students" resultStr = ActiveCell.Value ' Use active cell instead of a fixed range If resultStr <> "" Then resultArr = Split(resultStr, Chr(10)) ' Split using carriage return For M = checkListBox.ListCount - 1 To 0 Step -1 xP = checkListBox.List(M) For N = 0 To UBound(resultArr) If resultArr(N) = xP Then checkListBox.Selected(M) = True Exit For End If Next N Next M End If Else checkListBox.Visible = False buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here" For M = checkListBox.ListCount - 1 To 0 Step -1 If checkListBox.Selected(M) = True Then listOption = checkListBox.List(M) & Chr(10) & listOption ' Use carriage return instead of semicolon End If Next M If listOption <> "" Then ActiveCell.Value = Left(listOption, Len(listOption) - 1) ' Store result in the active cell Else ActiveCell.Value = "" End If End If End Sub Once the checklist works for one row, simply copy the cell with the button and the checklist, then paste it into other rows. The VBA will handle the dynamic references for each cell. Regards ExcelDemy
Hello @BillyGray-f2f , Thanks for your insight! While it's true that Excel tables aren't relational databases in the traditional sense, they still serve as effective tools for basic data management. Creating a structured table like this helps many users organize and analyze client data efficiently within Excel's capabilities. We appreciate your feedback and are open to discussing deeper database concepts! Regards ExcelDemy
Hello @billy007ization, To fix Excel auto-correcting + to *, try the following: To disable AutoCorrect: 1. Go to File > Options. 2. Select Proofing, then click on AutoCorrect Options. 3. Uncheck any rule converting + to * under the "Replace text as you type" section. Ensure that the cells are formatted as General or Text, not formulas. Regards ExcelDemy
Hello @deegee7750, You are most welcome. Thanks for your appreciation. Our aim is to make the learning clear and easy. Keep learning Excel with ExcelDemy! Regards ExcelDemy
Hello @mahmoudkhan9058, Thanks for your appreciation. Glad to hear that you found it well explained Keep learning Excel with ExcelDemy! Regards ExcelDemy
Hello @heatherjohnson6506, If the "sequence" option isn't available in your version of Excel, you can manually create a sequence of numbers. Enter the first number in one cell, the second in the next, select both cells, and drag the fill handle down to automatically fill the series. This method mimics the functionality of the SEQUENCE function and works for creating payment periods or other sequences. Regards ExcelDemy
Hello! Great video and super helpful, if you were to add in half sick days and half vacation days what would you need to change in the formulas that count the totals to accommodate this? For example, "V" would be a full vacation day but "V1" would be just the morning off and "V2" would be just the afternoon off. However I would like the number in the total leaves table to be all the vacation time off. If someone had one "V" and one V1" in their row I would like the "V" total to be 1.5. Is this possible or too complicated?
I have used =COUNTIF(D9:AH9,"V")+COUNTIF(D9:AH9,"V1")/2+COUNTIF(D9:AH9,"V2")/2 but the total count value is not displaying properly. If I have one "V1" the count will be 0, but if I have two "V1" in the row than the count will be 1. The count is not updating the halves but is recognizing that two halves make 1.
Hello @taylorbrule5371, You are most welcome. glad to hear that you found the tutorial helpful. If you want to input 0.5 as V and 1 for V1. You can use the following formula : =SUMPRODUCT(-($D9:$AH9=”HD”)*0.5) It will look for all occurrences of “HD” within the range $D9:$AH9. Each “HD” will be counted as 0.5. You can modify the formula. Download the Excel file to understand it properly: www.exceldemy.com/wp-content/uploads/2024/09/Create-Leave-Tracker-ExcelDemy.xlsx Regards ExcelDemy
Hello @dwightmichael9581, You will need to use a combined formula in the summary sheet to generate all final leave. Formula is: =IFERROR(INDEX(Jan!AJ$9:AJ$13,MATCH($B10,Jan!$C$9:$C$13,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$13,MATCH($B10,Feb!$C$9:$C$13,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$13,MATCH($B10,Mar!$C$9:$C$13,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$13,MATCH($B10,Apr!$C$9:$C$13,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$13,MATCH($B10,May!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$13,MATCH($B10,Jun!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$13,MATCH($B10,Jul!$C$9:$C$13,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$13,MATCH($B10,Aug!$C$9:$C$13,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$13,MATCH($B10,Sep!$C$9:$C$13,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$13,MATCH($B10,Oct!$C$9:$C$13,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$13,MATCH($B10,Nov!$C$9:$C$13,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$13,MATCH($B10,Dec!$C$9:$C$13,0)),0) Regards ExcelDemy
Hello @sylwiakrajewska7969, The VBA code is given in the article. It's mentioned in the youtube tutorial. You will get the article link in the description box. Open the article and copy the VBA code: www.exceldemy.com/create-multiple-sheets-in-excel-with-different-names/ Regards ExcelDemy
Hello @jeannedarc1278, If you close the referenced Excel spreadsheet, the formulas that link to it will still work, but the data will not update until you open the source file again. Excel stores the last retrieved values from the linked spreadsheet, so while the data might appear static when the file is closed, it updates the next time the file is opened. For continuous updates, the source spreadsheet must remain open or be refreshed upon reopening the file. Regards ExcelDemy
Exactly what I needed. In my personal (crude) budget sheet, I just wanted to copy over my carry-over from the previous month into the current month sheet. Your =SheetName!CellNumber did the trick! (in my case =Jul!N18) Thank you!
Hello @rokhalelalit69, To link a third-party EXE with Excel you can use Power Query or CSV files. 1. The EXE can write data into a CSV or Excel file. 2. You can use Power Query to automatically fetch and update the data from the CSV/Excel file. 3. Modify the data as needed. The EXE reads back the updated data from the same file. Regards ExcelDemy
Hello @rokhalelalit69, To update data in Excel and sync it with a linked XML file, you can follow these steps: First, create an XML mapping in Excel by importing the XML schema and assigning it to the cells. Next, Modify the data in the mapped cells as needed. Once the changes are made, export the updated data back to the XML file using the "Export" option in the Developer tab. This ensures that the Excel data is reflected in the linked XML file. Regards ExcelDemy
Hello @DonValentine, Thank you for your feedback! Excel sometimes flags files as a security risk if they contain macros or VBA, even if they are safe. This is a standard warning, not an indication of any issues with the file. We assure you that our workbook is secure and only includes the necessary functionalities to enhance your experience. If you prefer, you can adjust your Excel settings to avoid seeing these notifications for trusted files. Or you can copy the code from our article in your own workbook. The article link is given in the description box. Regards ExcelDemy
Hello @annemccarty7361, You are most welcome. We are glad you found it helpful. If you have any more questions or need further assistance, feel free to ask! Keep learning Excel with ExcelDemy! Regards ExcelDemy
Hello @anniemikhaeil7583, If you want to input 0.5 as half-day leave. You can use the following formula for Half Day (HD) column: =SUMPRODUCT(-($D9:$AH9=”HD”)*0.5) It will look for all occurrences of “HD” within the range $D9:$AH9. Each “HD” will be counted as 0.5. You will need to format the cells with 2 decimal places to properly show the decimal number otherwise it will show you rounded 1. Regards ExcelDemy
Hello @blenkomulti-services8790, You are most welcome. Glad to hear that the first method worked perfectly for you. Your appreciation means a lot to us. Keep learning Excel with ExcelDemy! Regards ExcelDemy
it's not a bad video, but I have some advice. You need to move more slowly through the steps, its hard to keep up with you and the way in which you build this is too complicated especially for a beginner.
Hello @andrewfelipe6070, Thanks for the feedback! We will look into this issue and definitely our instructor will work on slowing down the pace and simplifying the steps in future videos to make them easier to follow, especially for beginners. Regards ExcelDemy
hello, fab video. i have copied this video but none of the total leave seems to be adding to the counter. its not working on the indvidual count sheet or the summary sheet. i have triple checked the formulas. any help??
Hello @JohnstonsBakery, Thank you for the compliments! We are glad you liked the video. It sounds like there might be a small issue with the formulas or how they're referencing the data. Could you check if: the ranges in the formulas are correct and cover all relevant cells based on you existing sheet. check there are no hidden rows or columns that could affect the calculation and the cell formats are set correctly (e.g., numbers instead of text) If everything seems right, feel free to share your problem in the ExcelDemy Forum with images and Excel file. Regards ExcelDemy
hello good sir. I am wondering is there a way to change the format (like colour or font) of a cell and then u can see that to the linked cell? or the paste link command is only to update the value ? Thank you !
Hello @314Tesla, You are most welcome. The 'paste link' command in Excel only updates values between linked cells and doesn’t transfer formatting such as font, color, or borders. If you want to reflect formatting changes in linked cells, you'll need to apply the same formatting manually or use conditional formatting to automate some styling based on the values. Unfortunately, Excel doesn't natively support automatic formatting updates via links. You can use this VBA code to copy the formatting from one cell to another whenever the source cell changes. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1")) Is Nothing Then Dim wsSource As Worksheet Dim wsTarget As Worksheet Set wsSource = ThisWorkbook.Sheets("Sheet1") 'Source sheet Set wsTarget = ThisWorkbook.Sheets("Sheet2") 'Target sheet wsSource.Range("A1").Copy wsTarget.Range("A1").PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False End If End Sub Regards ExcelDemy
Hello @aliciaamistoso1801, You are most welcome. Now you will need to use formula in the summary sheet to get the summary of all leaves from all the months sheet. =IFERROR(INDEX(Jan!AJ$9:AJ$13,MATCH($B11,Jan!$C$9:$C$13,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$13,MATCH($B11,Feb!$C$9:$C$13,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$13,MATCH($B11,Mar!$C$9:$C$13,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$13,MATCH($B11,Apr!$C$9:$C$13,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$13,MATCH($B11,May!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$13,MATCH($B11,Jun!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$13,MATCH($B11,Jul!$C$9:$C$13,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$13,MATCH($B11,Aug!$C$9:$C$13,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$13,MATCH($B11,Sep!$C$9:$C$13,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$13,MATCH($B11,Oct!$C$9:$C$13,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$13,MATCH($B11,Nov!$C$9:$C$13,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$13,MATCH($B11,Dec!$C$9:$C$13,0)),0) You will get the formula in our Excel workbook: www.exceldemy.com/wp-content/uploads/2024/09/Create-Leave-Tracker.xlsx Please adjust the cell references based on your month's sheet. Regards ExcelDemy
Hello @aliciaamistoso1801, Of course, the formula will work if you have more employees in the list. You will need to update the cell range in the Summary sheet formula. Change the reference of months sheet: Jan!$C$9:$C$13 Regards ExcelDemy
Hello @arskhan-qh5ws, Thank you for the feedback! We will try to adjust the formatting to make the data and formulas more visible. To see the data contents and formulas and etc please check out the article: www.exceldemy.com/create-a-bell-curve-in-excel/ Regards ExcelDemy
apple apple pear apricot apple apricot apricot let's say I have a table like this. I want it to be blue and unfilled as the value changes. how do I do this? in other words, the colors that will appear after entering the formula will be like this. blue blue unfilled blue unfilled blue blue
Hello @ahmet9719, You can use the conditional formatting to change color based on the value changes. 1. Select the range of cells you want to apply the formatting. 2. On the Home tab, click on Conditional Formatting in the ribbon. 3. In the drop-down menu, click New Rule. 4. Use a Formula to Determine Which Cells to Format: 5. In the New Formatting Rule window, select Use a formula to determine which cells to format. Enter the Formula: =OR(A1=A2, A2=A3) Fruits apple TRUE apple TRUE pear FALSE apricot FALSE apple FALSE apricot TRUE apricot TRUE Regards ExcelDemy
Confusing, I want the data from sheet one cell to be added to a cell in another sheet cell automatically with out any additional steps later other than adding the data to the cell of the first sheet
Hello @user-ps8bf5nv9c, To automatically add data from one sheet to another in Excel, you can follow Method 1. Insert a formula like =Sheet1!A1 in the target cell. This will link the data, so when you update the first sheet, the second sheet updates automatically without any extra steps later. Make sure that the cell in the second sheet references the correct cell from the first sheet. No additional actions are required beyond updating the original cell. Regards ExcelDemy
Hello @alpaynamazi, You are most welcome. We updated the VBA code: 1. To remove case sensitive option from the search result. 2. To Highlight the searched word. 3. To add headers from different sheets if match data is found. VBA Code: Sub SearchMultipleSheets_Updated() Main_Sheet = "VBA" Search_Cell = "B5" Paste_Cell = "B9" Searched_Sheets = Array("Dataset 1", "Dataset 2") Searched_Ranges = Array("B5:F23", "B5:F23") Copy_Format = True Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column)) Used_Range.ClearContents Used_Range.ClearFormats Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value ' No lowercase conversion Count = -1 For S = LBound(Searched_Sheets) To UBound(Searched_Sheets) Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S)) Dim headerCopied As Boolean headerCopied = False ' To keep track if the header has been copied ' Start searching the range For i = 2 To Rng.Rows.Count ' Start from 2 to skip the header row For j = 1 To Rng.Columns.Count Value2 = Rng.Cells(i, j).Value If InStr(1, Value2, Value1) > 0 Then ' Check for match If Not headerCopied Then ' Copy the header row if not already copied Count = Count + 1 Rng.Rows(1).Copy Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column).Resize(1, Rng.Columns.Count) Paste_Range.PasteSpecial Paste:=xlPasteAll headerCopied = True ' Mark that the header has been copied End If Count = Count + 1 Rng.Rows(i).Copy Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column).Resize(1, Rng.Columns.Count) If Copy_Format = True Then Paste_Range.PasteSpecial Paste:=xlPasteAll Else Paste_Range.PasteSpecial Paste:=xlPasteValues End If ' Highlight the matched word Call HighlightMatch(Paste_Range, Value1) End If Next j Next i Next S Application.CutCopyMode = False End Sub ' Function to highlight the searched word in the results Sub HighlightMatch(ByVal TargetRange As Range, ByVal SearchValue As String) Dim Cell As Range Dim StartPos As Integer Dim CellValue As String For Each Cell In TargetRange CellValue = Cell.Value ' No lowercase conversion StartPos = InStr(1, CellValue, SearchValue) If StartPos > 0 Then Cell.Characters(StartPos, Len(SearchValue)).Font.Bold = True Cell.Characters(StartPos, Len(SearchValue)).Font.Color = vbRed ' Highlight with red font End If Next Cell End Sub Download the Updated Excel File: www.exceldemy.com/wp-content/uploads/2024/09/Search-Box-for-Multiple-Sheets-and-Highlight-Matched-Words.xlsm Regards ExcelDemy
Thanks for your great tutorial. How can I remove Case Sensitive option and just type words and search? (Capitalization is not used in my language writing). Also how can I add the desired heading of various sheets to the results? (Headings vary in my worksheets)
Hello @alpaynamazi, You are most welcome. We updated the VBA code: 1. To remove case sensitive option from the search result. 2. To Highlight the searched word. 3. To add headers from different sheets if match data is found. VBA Code: Sub SearchMultipleSheets_Updated() Main_Sheet = "VBA" Search_Cell = "B5" Paste_Cell = "B9" Searched_Sheets = Array("Dataset 1", "Dataset 2") Searched_Ranges = Array("B5:F23", "B5:F23") Copy_Format = True Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column)) Used_Range.ClearContents Used_Range.ClearFormats Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value ' No lowercase conversion Count = -1 For S = LBound(Searched_Sheets) To UBound(Searched_Sheets) Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S)) Dim headerCopied As Boolean headerCopied = False ' To keep track if the header has been copied ' Start searching the range For i = 2 To Rng.Rows.Count ' Start from 2 to skip the header row For j = 1 To Rng.Columns.Count Value2 = Rng.Cells(i, j).Value If InStr(1, Value2, Value1) > 0 Then ' Check for match If Not headerCopied Then ' Copy the header row if not already copied Count = Count + 1 Rng.Rows(1).Copy Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column).Resize(1, Rng.Columns.Count) Paste_Range.PasteSpecial Paste:=xlPasteAll headerCopied = True ' Mark that the header has been copied End If Count = Count + 1 Rng.Rows(i).Copy Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column).Resize(1, Rng.Columns.Count) If Copy_Format = True Then Paste_Range.PasteSpecial Paste:=xlPasteAll Else Paste_Range.PasteSpecial Paste:=xlPasteValues End If ' Highlight the matched word Call HighlightMatch(Paste_Range, Value1) End If Next j Next i Next S Application.CutCopyMode = False End Sub ' Function to highlight the searched word in the results Sub HighlightMatch(ByVal TargetRange As Range, ByVal SearchValue As String) Dim Cell As Range Dim StartPos As Integer Dim CellValue As String For Each Cell In TargetRange CellValue = Cell.Value ' No lowercase conversion StartPos = InStr(1, CellValue, SearchValue) If StartPos > 0 Then Cell.Characters(StartPos, Len(SearchValue)).Font.Bold = True Cell.Characters(StartPos, Len(SearchValue)).Font.Color = vbRed ' Highlight with red font End If Next Cell End Sub Download the Updated Excel File: www.exceldemy.com/wp-content/uploads/2024/09/Search-Box-for-Multiple-Sheets-and-Highlight-Matched-Words.xlsm Regards ExcelDemy
@@exceldemy2006 I noticed that you provided updated to correct if the cell was blank; however, I want to use the code you created without the Case Sensitive option and I'm having trouble correcting this code with the correction. Can you please assist on what would be different?
Hello @rachale1992 , To modify the code to remove case sensitivity while handling blank cells, you can adjust the logic as follows: Sub SearchMultipleSheets_NoCase() Main_Sheet = "VBA" Search_Cell = "B5" Paste_Cell = "B9" Searched_Sheets = Array("Dataset 1", "Dataset 2") Searched_Ranges = Array("B5:F23", "B5:F23") Copy_Format = True Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column)) Used_Range.ClearContents Used_Range.ClearFormats Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value ' Check for blank cell If Value1 = "" Then MsgBox "Search cell is blank." Exit Sub End If Count = -1 For S = LBound(Searched_Sheets) To UBound(Searched_Sheets) Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S)) For i = 1 To Rng.Rows.Count For j = 1 To Rng.Columns.Count Value2 = Rng.Cells(i, j).Value ' Check for match without case sensitivity If InStr(1, LCase(Value2), LCase(Value1)) > 0 Then Count = Count + 1 Rng.Rows(i).Copy Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column) If Copy_Format = True Then Paste_Range.PasteSpecial Paste:=xlPasteAll Else Paste_Range.PasteSpecial Paste:=xlPasteValues End If End If Next j Next i Next S Application.CutCopyMode = False End Sub Regards ExcelDemy
Hello @mazharalam8589, You can't use formula to create multiple sheets at once. To create multiple sheets through A to Z use the following VBA code. Sub Create_Sheets_fro_AtoZ() Dim ws As Worksheet Dim i As Integer For i = 65 To 90 ' ASCII values for A to Z Set ws = ThisWorkbook.Sheets.Add ws.Name = Chr(i) Next i End Sub This code will create a total of 26 sheets in your workbook. Regards ExcelDemy
Hello @mazharalam8589, To create multiple sheets through A to Z use the following VBA code. Sub Create_Sheets_fro_AtoZ() Dim ws As Worksheet Dim i As Integer For i = 65 To 90 ' ASCII values for A to Z Set ws = ThisWorkbook.Sheets.Add ws.Name = Chr(i) Next i End Sub This code will create a total of 26 sheets in your workbook. Regards ExcelDemy
Hello @hugomascena, By using our existing template create the roaster then adjust the start and end time in the sheet based on your requirements. To add start and end time you will need to add two columns next to each date. Format the cells under these columns to accept time input. Then, insert the respective start and end times for each shift. Example: 03-Aug Start Time End Time D1 09:00 21:00 N1 21:00 09:00 D3 09:00 21:00 Regards ExcelDemy
If I change the year, it saves the previous data. I want to change the year; it will show the previous, current, or next year data automatically. How is it possible?
Hello @asishkarmakar974, It will be complex to show data from the previous, current, or next year. Retrieving data from 36 months will make the formulas complex and will cause errors and performance issues. You can create separate sheets for each year (e.g., 2023, 2024, 2025). Instead of retrieving data from all 36 months, it will retrieve data from 12 months of each year then you can create a summary from each year in a new sheet. Regards ExcelDemy
i work with libre office calc.we use a filter for quick search in column A. the filter has option for sort a z, z a .sometimes by accident you may press the buttons. how can i remove them from filter? i can send the file somehow for a better look to understand what i mean .thank you
Hello @user-fg6pf2ox1i, LibreOffice Calc doesn’t provide a direct way to disable these sort options in the filter dropdown. But you can avoid accidental sorting, by using a custom filter. Lock the specific column then use a custom filter setup that doesn't include sorting. If you want to share the file for a better understanding, you could share it via ExcelDemy Forum:exceldemy.com/forum/ Regards ExcelDemy
