Posts by SChalaev

    Hi guys,


    What I want to do is to find the last (not max) number from a range B1:B12 and when it is found return a string 'HELLO' in column C. In the picture the correct result (HELLO) would be displayed in a cell C12. I've got a formula so far but it doesn't go to the last value (see pic).


    Info about the numbers in B1:B12 - they're produced by a formula and may contain duplicates; they're unsorted/random and the worksheet is set up to ignore zeros (checkbox is unchecked for show a zero in cells that have zero value).


    Many thanks,


    Stas


    [Blocked Image: http://i62.tinypic.com/8zn3i8.png]

    Re: Dynamic label next to a line for a line chart


    Managed to do it by creating more columns (containing formula for dynamic labels), then using original data labels (not the ones used in the line graph from "Above", "Right" and "Below") and replacing the data labels with a cell reference of the columns containing dynamic labels. Bit of a mouthful but if you check the file it should explain itself. Just wondering if there's an easier way of doing the same thing, without VBA?


    Stas

    Hi guys,


    I have a table of data from which a line chart is plotted (sample file attached) based on checkbox selection.


    The chart also contains columns titled "Above", "Right" and "Below" which are used for plotting Data Labels on the line chart so that Data Labels do not over lap.



    What I would like to do is to create a dynamic label next to each line, at the end of each line, as the data populates from week I to XII.


    I have done it in the file manually, unfortunately if I delete the last data entry from the table (week XII) then the labels will disappear.


    My guess is that you have to identify a last cell with value in the data table and then assign a label (Series name) to it, I'm just not sure how to go about it.



    Stas

    Hi guys,


    I've watched a tutorial on how to hide and unhide columns on a particular sheet with the use of FormControl checkboxes (I want to use the checkboxes to add/remove series from a column graph by hiding/unhiding columns), the following code was used and it worked in the tutorial but when I try and run it in my Excel 2013 workbook it highlights the bolded line with a run-time error '424' (Object Required), how do I fix it?


    Code
    Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    End If
    If CheckBox1.Value = False Then
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = False
    End If
    End Sub


    If CheckBox1.Value = True Then


    Many thanks,


    Stas

    Re: Checkbox VBA: Dynamic Chart Legend via hiding/unhiding columns?


    Thankyou pike, that works fantastic. I'll have a go at applying it to my data spreadsheet.


    Would you mind explaining the VBA logic in this piece of code:


    Code
    For i = 1 To 6
            If Not Sheets("Sheet1").Range("C2").Offset(0, i).Value = "True" Then
                objCht.Legend.LegendEntries(i + x).Delete
                x = x - 1


    Thanks again,


    Stas

    Hi guys,


    I've created a mock spread sheet where a car is selected by clicking a check box first, then a second check box selection is made based on Total, Main, Additional Parts.


    After the user makes the selection for Cars and Parts, a dynamic chart (data for which is used on Sheet2) is posted. Unfortunately the legend does not update itself based on user's selection.


    The data on Sheet2 is set up in such a way that when all three graphs are plotted, the Data Labels for each graph do not overlap (i.e. the biggest number stays 'Above' the line, second biggest number stays to the 'Right' and third biggest is placed 'Below').


    So in order to update the legend the idea was to hide/unhide columns on Sheet2 based on the selection of checkboxes (Toyota and Total, Main, Additional Parts) using VBA (I have little to no experience with VBA) which would add or remove series from the graph. The columns that you'd hide on Sheet2 would be Total, Main, Additional Parts columns but also Above, Right, Below which are used for Data Labels on the graph.


    How could this be done with VBA?


    Many thanks,


    Stas