• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. Monty

    Non edible sheet

  2. Monty

    Non edible sheet

    You lock the selected cells which you do not wanted them to edit.looks professional...
  3. Monty

    Answering questions with chatGPT, GPT4 and BARD

    I will make sure Marc!
  4. Monty

    Answering questions with chatGPT, GPT4 and BARD

    Marc, I've been a member since 2014 and consistently seek help from this group while also trying to assist those who struggle with Excel, just like I do. I strive to be honest in my learning approach. Regarding trying before posting, I actually don't, as I've had bad experiences with viruses on...
  5. Monty

    Defining Range to use based on a matching value

    @p45cal I should admitt it...Your meticulous analysis of problem statements has inspired me to be more deliberate in my thinking and avoid jumping to conclusions too quickly. Your dedication and expertise are truly admirable.Hat's off to you!
  6. Monty

    Defining Range to use based on a matching value

    Absolutely misunderstood, Am hear to help and learn...No shortcuts..
  7. Monty

    Defining Range to use based on a matching value

    Modified in case! =INDEX($A$2:$A$41471,MATCH(MIN(IF(($B$2:$B$41471=B2)*($C$2:$C$41471>=D2), $C$2:$C$41471-D2, 10^10)), IF(($B$2:$B$41471=B2)*($C$2:$C$41471>=D2), $C$2:$C$41471-D2, 10^10), 0))
  8. Monty

    Defining Range to use based on a matching value

    Okay... I Think there was a misunderstanding in how the IFERROR function was used. Let's adjust the formula to avoid the reference back to cell A2 when encountering an error. We can achieve this by using a large number as the default value instead of IFERROR. Here's the revised formula...
  9. Monty

    Defining Range to use based on a matching value

    Did you try my previous formula provide with the change
  10. Monty

    Defining Range to use based on a matching value

    Hello Inhave changed by removing the extra , 0 at the end of the formula. This was causing the "too few arguments" error. Now the formula should work =INDEX($A$2:$A$41471,MATCH(MIN(IF(($B$2:$B$41471=B2)*($C$2:$C$41471>=F2), $C$2:$C$41471-F2, IFERROR(1/0))))...
  11. Monty

    Defining Range to use based on a matching value

    The above formula should find the closest matching product code within each Generic ID class to the 22nd percentile price. Adjust the range if your data extends beyond row 41471. Let me know if this resolves the issue.
  12. Monty

    Defining Range to use based on a matching value

    Hello Let's try refining the formula. It seems the issue might be related to how the conditions are applied. Let's adjust the formula to ensure it's correctly filtering based on the Generic ID and the price being above the 22nd percentile. Here's the revised formula for column G...
  13. Monty

    Defining Range to use based on a matching value

    Hello We can use the PERCENTILE function to find the 22nd percentile price for each Generic ID class. You can use the following formula in an empty column (let's say column F): =PERCENTILE(IF($B$2:$B$41471=B2,$C$2:$C$41471),0.22) Now, in another column (let's say column G), use the following...
  14. Monty

    Compile error as variable has not been created yet.

    Hello I think want to use codenames of sheets from another workbook in your VBA code. However, VBA won't recognize those codenames if the sheets are not present in the current workbook. Combining the workbooks is a valid workaround if keeping them separate isn't crucial for your workflow...
  15. Monty

    Compile error as variable has not been created yet.

    Hello Debsar I agree if the code is not working or have created discrepancy but it is written code....
  16. Monty

    Pivot Table Field and Slicers disappearing

    Hello Based on your description, it seems like the issue might be related to how the slicers are updating when new data is added to the "Shipment Table" and the pivot table is refreshed....Am currently workingbon code separately...will share shortly
  17. Monty

    VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section

    Try this ! Sub DataSplitWithHeader() Dim asheet As Worksheet Dim lastrow As Long Dim myarray As Variant Dim i As Long Dim nextRow As Long Dim targetSheet As Worksheet Set asheet = ActiveSheet Set targetSheet = Worksheets("DataTarget") lastrow =...
  18. Monty

    VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section

    Hello After looking at code i think you have to replace the line Sheets.Add.Name = myarray(i) with Worksheets("DataTarget").Activate. Here is the modified version of your cod3. Sub DataSplitWithHeader() Dim asheet As Worksheet Dim lastrow As Long Dim myarray As Variant Dim i...
  19. Monty

    Compile error as variable has not been created yet.

    Yes Marc....You're absolutely correct. If the worksheet is located in the same workbook as the VBA procedure, you can directly reference its codename without the need to declare a variable. Here's an example: Sheet1_CodeName.Activate Sheet2_CodeName.Activate In this case, Sheet1_CodeName and...
  20. Monty

    Compile error as variable has not been created yet.

    Hello mate If you want to refer to sheet codenames from nother workbook, you need to declare variables for those sheets correctly. Since the sheets might not exist at compile time, you can use the Object data type, which is generic and can refer to any object, including worksheets. Here's an...
  21. Monty

    Saving as cell values

    Glad! it helped.
  22. Monty

    Saving as cell values

    Good suggestion by Marc Using the Text property instead of the Value property can be beneficial in certain cases, especially when dealing with formatted cells. If you're interested in using the Text property, you can modify the code like this: Sub SaveWorkbookWithCellValues() Dim ws As...
  23. Monty

    Saving as cell values

    As per my understanding based on your discussions! Sub SaveWorkbookWithCellValues() Dim ws As Worksheet Dim fileName As String Dim path As String Dim value1 As String Dim value2 As String Dim value3 As String Set ws = ThisWorkbook.Sheets("Vessel Schedule")...
  24. Monty

    Using VBA to restrict pivot table changes - reordering all columns and rows

    In the updated code, I added `.Orientation = xlHidden` inside the loop that iterates through each pivot field. This line hides each pivot field from the pivot table layout, effectively preventing users from dragging and reordering them.
  25. Monty

    Using VBA to restrict pivot table changes - reordering all columns and rows

    This should help Sub RestrictPivotTable() Dim pf As PivotField On Error Resume Next With ActiveSheet.PivotTables(1) .EnableDrilldown = True .EnableFieldList = False .EnableFieldDialog = False .PivotCache.EnableRefresh = True For Each pf In...
Back
Top