• 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


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

Recent content by Hui

  1. Hui

    Distribution of Values

    You do realise that this can't be solved using a linear distribution? Firstly there are 35 records with a value less than 123.9, that is records whose value is less than a value to get 150 points That uses up 5,250 points leaving 10,000-5,250=4,750 to be distributed to the remaining 13 values...
  2. Hui

    INDIRECT in dynamic arrays

    You may want to try =Sum(INDIRECT(H2#&"!$A$1") ) Ctrl+Shift+Enter or =Sumproduct(INDIRECT(H2#&"!$A$1") ) Also supplying a sample file is the fastest way to get an accurate solution
  3. Hui

    Working with pie charts

    Select your chart then select the Chart design Tab and try the various options Also select the chart and the press Ctrl+1 Explore the various options
  4. Hui

    How to draw an approximation graph in excel

    I assume that A & B are 2 sets of Y values What is the X Axis values for the data?
  5. Hui

    Concatenate Cells According a condition

    Have a look at teh following code Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Intersect(Range("B3:B35000"), Target) Is Nothing Then Exit Sub Dim break As Boolean break = False Dim myString As String myString = "" Dim i As Integer i = 1 Do If...
  6. Hui

    3D Map Hourly Timeline don't come correctly

    Add a field to your data called Date2 or anything else Make that field =A2+B2 Then in the 3D map Refresh data Then drag the new field name from the Field Picker into the Time dropdown Enjoy Time without a date assumes that the year is 0
  7. Hui

    A Difficult But Probably Easy Problem

    Have a read about the Evaluate function at https://chandoo.org/wp/lost-excel-functions/
  8. Hui

    cycle though formulas to show best result?

    I am traveling for next few days Can’t look at it til mid next week Sorry
  9. Hui

    Help Required for Charting

    Why not use a Water Fall Chart By doing that you can have an opening balance, a number of categories for Purchases and Sales and a Closing balance
  10. Hui

    Application.WorksheetFunction.VLookup code wrong

    CStr() is Convert String so CStr(lRow1) converts the number lRow1 froma Number to a Text value for us in the remainder of the formula In VBA click on CStr and press F1 for an explanation of the function
  11. Hui

    Application.WorksheetFunction.VLookup code wrong

    You are trying to execute the VLookup function in VBA by using Application.Worksheetfunction What you need to do is save the Formula as a text string representing the formula into the cells Change the line to read as below Range("C2:C" & lRow).Formula = "=VLookup($B2, $K$2:$L$" + CStr(lRow1)...
  12. Hui

    VBA to write a formula in column if cell not empty

    I have changed the Worksheet event that is used to trigger the code and added a few tweaks Replace the existing code with the code below: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(ActiveCell, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False On...
  13. Hui

    cycle though formulas to show best result?

    Thanx Harry Can you please explain what A1:A4 do and what range of values for each cell are allowable?
  14. Hui

    VBA to write a formula in column if cell not empty

    That code has to be in the Marciela Worksheet Object You also have to save the file a s a *.xlsm or *.xlsb file type
  15. Hui

    cycle though formulas to show best result?

    Harry There are a few ways to tackle this style problem Can you post a sample file as an example of what you want?