# Recent content by Hui

1. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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

8. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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?