Hello @TlhalefoMosoeu-pt6fx, Great question! To handle leave records for different years, you need to modify the leave tracker so that it filters data based on the selected year. In the article, the tracker is designed to update dynamically when a year is selected. To ensure the leave data updates correctly: 1. Include a 'Year' column in your leave data table. 2. Use a SUMIFS formula to sum the leaves taken for the selected year. For example: =SUMIFS(Leave_Range, Year_Column, Selected_Year, Employee_Column, Selected_Employee) This ensures that only leaves from the chosen year are counted. 3. If you're using conditional formatting or formulas to display leave details, apply the same year filter to keep records distinct for each year. This way, when you switch between 2024 and 2025, you will only see the relevant leave records for each year. Hope this helps! Regards ExcelDemy

Hello @iamlili92, Yes, this will still work! You can add new joiners by inserting a new row and updating the formulas accordingly. If someone resigns, you can either clear their data or mark them as inactive. To ensure the tracker updates correctly, make sure formulas and conditional formatting cover the newly added rows. Regards ExcelDemy

Hello @CLINTONACOSTA, You can automate adding rows in Excel by using an Excel Table, which automatically expands when you enter new data in the next row. Additionally, you can use VBA to insert new rows dynamically based on conditions. If you're working with structured data, Power Query can also help update and manage rows automatically. Regards ExcelDemy

Hello @rialsnogard, Thanks for your feedback and appreciation. Indeed TEXTJOIN is a powerful and dynamic function which really helps in various situations. Keep exploring Excel with ExcelDemy! Regards ExcelDemy

Hello @HousemanLopez, It looks like you're asking about "chedu". If you're referring to how to create a schedule in Excel, feel free to check out the article, as it explains step-by-step how to set up a monthly schedule. Let me know if you need further clarification or help with anything specific! Regards ExcelDemy

Hello @jerichokatende8151, You are most welcome. Thanks for your feedback! Glad you found the tutorial helpful. Explore our other helpful tutorials. Regards ExcelDemy

Hello @Md.Ismail-v1e, Thank you for your feedback. Appreciate it! Feel free to ask if you have any more questions. Regards ExcelDemy

Hello @nagajyothireddy9336, 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. Explore the comment section of this post to get more options and answers: www.exceldemy.com/create-leave-tracker-in-excel/ Regards ExcelDemy

Hello @philsiokos, You are most welcome. Thanks for your feedback. Glad to hear that our detailed and organized tutorial was helpful! Regards ExcelDemy

Hello @LifeIsLife1978, Thanks for your feedback! We're glad you appreciated the examples. ChatGPT in Excel is a growing topic with lots of potential. If there’s a particular feature or use case you'd be interested in seeing, feel free to share your thoughts! Regards ExcelDemy

Hello @beccabonk1, In the video, we used Excel for Windows, which has different menu options than Mac. Unfortunately, Mac doesn't have the File > Options menu. Instead, go to Excel > Preferences in the top menu bar to find similar settings. Hope this helps! Regards ExcelDemy

Hello @rahieshdaya3334, You can handle unexpected leave by adding a column to track "Shift Status" or "Leave Updates." When an employee takes sudden leave, update the roster by marking their shift as "On Leave" and assigning a replacement if available. Keeping the roster dynamic with conditional formatting for leave entries can also make it easier to spot schedule gaps. Let me know if you'd like a detailed example! Regards ExcelDemy

Hello @pr.hernandez2171, Yes, it's possible! You can use a combination of Excel functions and VBA to achieve this. One simple approach without VBA is to use a helper column to store the selections and then use formulas to display them in different cells across a row. Alternatively, if you're comfortable with VBA, you can use a macro to automate this. VBA Code: Private Sub Worksheet_Change(ByVal Target As Range) ' Set the range for the drop-down checklist Dim ChecklistRange As Range Set ChecklistRange = Range("A1:A10") ' Change this range to match your checklist cells ' If the change is made within the checklist range If Not Intersect(Target, ChecklistRange) Is Nothing Then ' Set the starting row and column to place the names Dim RowNum As Long RowNum = 2 ' Change this to your desired row number where you want to add names Dim ColumnNum As Long ColumnNum = 1 ' Starting from the first column ' Loop through the checklist range and add names to the row Dim cell As Range For Each cell In ChecklistRange If cell.Value <> "" Then Cells(RowNum, ColumnNum).Value = cell.Value ColumnNum = ColumnNum + 1 ' Move to the next column End If Next cell End If End Sub Regards ExcelDemy

Hello @Coldwing06, You can extend it using nested IF statements to accommodate additional ranges like 60, 70, 80, 90, and 100. Here's an adjusted version: =IF(A1<=50,"In Progress",IF(A1<=60,"Progressing",IF(A1<=70,"On Track",IF(A1<=80,"Near Completion",IF(A1<=90,"Almost Done","Completed"))))) This formula breaks the progress into smaller steps based on the ranges you've mentioned. Adjust the values and labels to suit your needs. Regards ExcelDemy

Hello @paulmango_ke, Thanks for watching! Glad you found it helpful. Keep exploring Excel with ExcelDemy! Regards ExcelDemy

Hello @Seanvstherobots, To display images in the search results using VBA, you need to modify the code so that it can handle images. One approach is to use Shapes.AddPicture to dynamically display the image in the Paste_Cell location. Here’s an updated version of the code to support inserting images: Sub SearchMultipleSheets() Main_Sheet = "VBA" Search_Cell = "B5" SearchType_Cell = "C5" 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 Count = -1 If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then Case_Sensitive = True ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then Case_Sensitive = False Else MsgBox ("Choose a Search Type.") Exit Sub End If 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 If PartialMatch(Value1, Value2, Case_Sensitive) = True 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 ' Insert image if found in corresponding cell If IsEmpty(Rng.Cells(i, j + 1).Value) = False Then ' Assuming image path or file name is in the next cell Dim img As Picture Set img = Sheets(Main_Sheet).Pictures.Insert(Rng.Cells(i, j + 1).Value) img.Left = Paste_Range.Left img.Top = Paste_Range.Top End If End If Next j Next i Next S Application.CutCopyMode = False End Sub Regards ExcelDemy

Hello @annteather2826, The formulas or features used in the tutorial might not be fully compatible with Excel 2007, as it lacks some newer functions. To ensure proper functionality, I suggest checking if the formulas are supported in your version, especially dynamic array functions. You might need to manually adjust the matrix layout or use alternative methods like helper columns. Regards ExcelDemy

​@@exceldemy2006 thanks for your reply. I don't think the dynamic functionality is in my version and I am not really familiar with how arrays work, or helper columns. I do have a very large Excel manual for old Windows 95 Excel, so at least I now have an idea of where to start learning about how to make this work! Most grateful 🙂

Hello @annteather2826, It sounds like you're determined to make it work despite the limitations of Excel 2007-great attitude! Since dynamic functions aren't available in your version, I recommend starting with your manual to learn about array formulas and how to set up helper columns. Focus on understanding SUMPRODUCT or IF functions for conditional categorization in your matrix. To make the Eisenhower Matrix compatible with Excel 2007, use these updated formulas: Task Classification: Use IF instead of newer functions:(=IF(AND(A2="Urgent", B2="Important"), "Quadrant 1", "Other")) Conditional Summation: Use SUMPRODUCT for multi-condition summing: (=SUMPRODUCT((A2:A10="Urgent")*(B2:B10="Important"))) Data Lookup: Replace modern LOOKUP functions with INDEX-MATCH (=INDEX(B2:B10, MATCH("Important", A2:A10, 0))) Regards ExcelDemy

​@@exceldemy2006 thank you so much for this information, I am really grateful. At the last count I had 191 items in my Kanbanflow lists and am finding it rather overwhelming, so need this matrix! 😊

Hello @annteather2826, You are most welcome. It’s great that you found the information helpful! Managing 191 items can be overwhelming, but using the Eisenhower Matrix should help you prioritize tasks more effectively. Feel free to ask if you need more assistance with setting up your matrix or tweaking formulas. Best of luck organizing your tasks! 😊 Regards ExcelDemy

Hello @butterflies1337, You are most welcome. That's awesome! Wishing you the best on your journey! Let us know how your weight loss graph in Excel and win your summer goals! Regards ExcelDemy

Hello @AngelReyes-tz6lw, Could you please clarify what you mean by 'publicity'? We'd love to help you out! Regards ExcelDemy

Hello @AngelReyes-tz6lw, Excel is indeed great for both workflow diagrams and data analytics. Let us know if you're looking for a guide on how to integrate these two effectively! Regards ExcelDemy

Hello @Foodwine-ql6sj, Thank you for your feedback! The "Run-time error 9: Subscript out of range" usually occurs when the VBA code references a sheet name that doesn't exist or is misspelled. To resolve this issue: 1. Make sure all sheet names are spelled correctly in your VBA code. 2. Verify that the sheet names in the code exactly match those in your workbook, including spaces and special characters. 3. If you are dynamically adding sheet names, double-check that they are included in the search range. Updated VBA Code: Sub SearchMultipleSheets() ' Define main parameters Dim Main_Sheet As String Dim Search_Cell As String, SearchType_Cell As String, Paste_Cell As String Dim Searched_Sheets() As String, Searched_Ranges() As String Dim Copy_Format As Boolean Dim Value1 As String, Case_Sensitive As Boolean Dim Last_Row As Long, Last_Column As Long Dim Count As Long, S As Long, i As Long, j As Long Dim Rng As Range, Used_Range As Range, Paste_Range As Range Dim ws As Worksheet Main_Sheet = "VBA" Search_Cell = "B5" SearchType_Cell = "C5" Paste_Cell = "B9" Copy_Format = True ' Specify sheets and ranges Searched_Sheets = Split("Dataset 1,Dataset 2,Dataset 3", ",") ' Add more sheet names here Searched_Ranges = Split("B5:F23,B5:F23,B5:F23", ",") ' Add corresponding ranges here ' Clear previous results With Sheets(Main_Sheet) Last_Row = .Range(Paste_Cell).End(xlDown).Row Last_Column = .Range(Paste_Cell).End(xlToRight).Column Set Used_Range = .Range(.Cells(.Range(Paste_Cell).Row, .Range(Paste_Cell).Column), _ .Cells(Last_Row, Last_Column)) Used_Range.ClearContents Used_Range.ClearFormats End With ' Get search value Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value Count = -1 ' Determine search type Select Case Sheets(Main_Sheet).Range(SearchType_Cell).Value Case "Case-Sensitive" Case_Sensitive = True Case "Case-Insensitive" Case_Sensitive = False Case Else MsgBox "Choose a valid Search Type (Case-Sensitive or Case-Insensitive).", vbExclamation Exit Sub End Select ' Search across specified sheets and ranges On Error Resume Next ' Handle potential errors For S = LBound(Searched_Sheets) To UBound(Searched_Sheets) Set ws = Nothing Set ws = Sheets(Searched_Sheets(S)) If ws Is Nothing Then MsgBox "Sheet '" & Searched_Sheets(S) & "' does not exist.", vbExclamation GoTo NextSheet End If Set Rng = Nothing Set Rng = ws.Range(Searched_Ranges(S)) If Rng Is Nothing Then MsgBox "Range '" & Searched_Ranges(S) & "' on sheet '" & Searched_Sheets(S) & "' is invalid.", vbExclamation GoTo NextSheet End If For i = 1 To Rng.Rows.Count For j = 1 To Rng.Columns.Count If PartialMatch(Value1, Rng.Cells(i, j).Value, Case_Sensitive) Then Count = Count + 1 Rng.Rows(i).Copy Set Paste_Range = Sheets(Main_Sheet).Cells(Sheets(Main_Sheet).Range(Paste_Cell).Row + Count, _ Sheets(Main_Sheet).Range(Paste_Cell).Column) If Copy_Format Then Paste_Range.PasteSpecial Paste:=xlPasteAll Else Paste_Range.PasteSpecial Paste:=xlPasteValues End If End If Next j Next i NextSheet: Next S On Error GoTo 0 ' Reset error handling Application.CutCopyMode = False End Sub Function PartialMatch(Value1 As String, Value2 As String, Case_Sensitive As Boolean) As Boolean Dim Matched As Boolean, i As Long Matched = False For i = 1 To Len(Value2) If Case_Sensitive Then If Mid(Value2, i, Len(Value1)) = Value1 Then Matched = True Exit For End If Else If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then Matched = True Exit For End If End If Next i PartialMatch = Matched End Function Regards ExcelDemy

Hello @h3ll0_2, You're most welcome! I'm glad this video could help you organize your client information. Keep exploring Excel with ExcelDemy! Regards ExcelDemy

Hello @thinkingstraight2309, To hide duplicates in Google Sheets, you can use Conditional Formatting. 1. Select the range of data. 2. Go to Format > Conditional Formatting. 3. Under the 'Format rules,' choose Custom formula is. Enter the formula =COUNTIF(A:A, A1)>1 (replace A:A with your column range). 4. Set a text or background color to highlight duplicates. This highlights duplicates, and you can filter or hide them using the filter option. Let me know if you need more help! Regards ExcelDemy

Thanks @@exceldemy2006 . I have tried this already. This formula counts and highlights ALL the duplicates. I want to be able to retain one cell of the duplicates and hide the rest

Hello @thinkingstraight2309, Got it! To retain one cell of each duplicate and hide the rest in Google Sheets: 1. Add a helper column next to your data. 2. Use this formula in the helper column: =COUNTIF(A$1:A1, A1)=1 (replace A with your column range). 3. This will mark the first occurrence of each value as TRUE and duplicates as FALSE. 4. Filter the data by the helper column and show only rows with TRUE. This will retain the first instance of duplicates and hide the rest. Let me know if this helps! Regards ExcelDemy

Hello @jimh3595, You are most welcome. Thanks for the feedback! Keep exploring the world of data analysis with Excel! Regards ExcelDemy

Hello @georgetownmultimedia653, You are most welcome! Thanks for your feedback. Glad you found it helpful! Regards ExcelDemy

Hello @thandonyaba8132, You are most welcome. Glad you found the solution. Keep exploring and let me know if you have any other questions. Regards ExcelDemy

Hello @thandonyaba8132, You can achieve this by reducing the column widths to fit all the data within the visible screen. Alternatively, you can adjust the zoom level to view more columns at once. If you want to maintain a 100% zoom, you could also consider transposing the data (swapping rows and columns) or hiding less critical columns temporarily to focus on the essential data. Regards ExcelDemy

Hello @RizwanKarim-ey3dy, You can create multiple sheets with specific names like GDIN001 to GDIN150 using VBA in Excel. Here's a simple VBA code: Sub CreateSheets() Dim i As Integer For i = 1 To 150 Sheets.Add(After:=Sheets(Sheets.Count)).Name = "GDIN" & Format(i, "000") Next i End Sub This will create sheets named GDIN001, GDIN002, ..., and GDIN150. To use it, press Alt + F11, paste the code into a new module, and run it. Regards ExcelDemy

Hello @RizwanKarim-ey3dy, Thank you for your feedback. We’re sorry to hear you had trouble viewing the formulas and data. This might be due to the KZbin quality settings. Please try increasing the video resolution (e.g., to 720p or higher) for better clarity. Let us know if the issue persists, and we’ll assist further. We appreciate your support! Regards ExcelDemy

Hello @luzgomez6348, You are most welcome. Thanks for your feedback and appreciation. Glad you found it helpful! Keep exploring Excel with ExcelDemy! Regards ExcelDemy

Hello @AidenOdendaal-y7l, The '#####' error occurs when the column is too narrow to display the full value. To fix this, simply widen the column by dragging the edge of the column header or double-clicking it to auto-adjust. Let me know if you need further assistance! Regards ExcelDemy

Hello @khaingshwezinmyint5664, Thank you for the thumbs-up! 😊 If you're interested in creating a workflow diagram in Excel, please check out our tutorial or let us know if you have any questions. We're here to help! Regards ExcelDemy

Hello @jennifertaylor4300, Thank you for your question! To customize the tracker for a 6-days-on and 6-days-off schedule instead of highlighting Saturdays and Sundays, you can update the conditional formatting rules. 1. Select the range of cells you want to apply the rule to. 2. Go to the Home tab > Conditional Formatting > New Rule. 3. Choose "Use a formula to determine which cells to format". 4. Use a formula like this (assuming your dates start in cell A1): =MOD(ROW()-ROW($A$1), 12)<6 This highlights 6 consecutive rows as "on" followed by 6 "off". 5. Click Format, choose your preferred formatting, and press OK. This method adjusts the formatting to match your schedule. Regards ExcelDemy

I mean, is it possible?

Hi @mario__4366, Thanks for your feedback and appreciation. It's absolutely possible to create a bubble chart with three data ranges to represent three shops. Here's how you can do it: Organize your data in a table format with three columns for each shop: Column 1: X-axis values (e.g., Sales). Column 2: Y-axis values (e.g., Profit). Column 3: Bubble size (e.g., Number of Customers). Add a separate set of three columns for each shop. Example: Shop Sales Profit Customers Insert a Bubble Chart: 1. Select the data for the first shop (X, Y, and size values). 2. Go to the Insert tab > Click on Scatter Chart > Choose Bubble Chart. Add Data for Additional Shops: 1. Right-click on the chart and choose Select Data. 2. In the Select Data Source dialog box, click Add to add a new series. 3. Enter the following: 3.1. Series X values: Select the X-axis values for Shop 2. 3.2. Series Y values: Select the Y-axis values for Shop 2. 3.2. Series bubble size: Select the bubble size values for Shop 2. 4. Repeat the above steps for Shop 3. Customize the Chart: 1. Format the bubbles by changing colors to distinguish the shops. 2. Add a legend, axis titles, and a chart title for better readability. And that's it! You'll have a bubble chart showcasing data for three shops. Let me know if you need further clarification! Regards ExcelDemy

Hello @5pctLowBattery, Thanks for your feedback. Glad you think so! 😎 Thanks for the support! Explore the coolest article from ExceDemy! Regards ExcelDemy

Hello @Forthnigt, You are most welcome. Thanks for your subscription! Glad you found it helpful. Keep exploring Excel with ExcelDemy! Regards ExcelDemy

Hello @juanmanueljimenez2287, You're most welcome! Thanks for your feedback and appreciation. Keep exploring Excel's with ExcelDemy! Regards ExcelDemy

Hello @NicholeLow-g6q, Sorry for the inconvenience. The links will be back up shortly, thanks for letting me know! Please download it from here: www.exceldemy.com/wp-content/uploads/2022/05/Create-Leave-Tracker-1.xlsx Regards ExcelDemy

Hello @DataDuchess, I am looking into this right now. The links will be back up shortly, thanks for letting me know! Please download it from here: www.exceldemy.com/wp-content/uploads/2022/05/Create-Leave-Tracker-1.xlsx Regards ExcelDemy

@exceldemy2006 Thanks for the swift response

Hello @DataDuchess, You are most welcome. Thanks for your feedback and appreciation.

Hello @silver4731, Thank you for your feedback! I understand that some of the steps might seem a bit complicated. To simplify, you could start with just the basic structure of the timesheet, add columns for Date, Start Time, End Time, and Total Hours. You can then calculate total hours using a simple formula like =End Time - Start Time. This will give you a functional timesheet without additional details like weekends or overtime. Regards ExcelDemy