Posts by Cheeky Charlie

  • Sumproduct Function To Pull Data From Raw Data Import

    Re: Sumproduct Function To Pull Data From Raw Data Import


    It would really help if the original input data didn't have so many totals and subtotals dotted through it. Before writing something to work around that - how much control do you have over the inputs?


    I don't like the table at the top and summary at the bottom (but these can be worked around fairly easily) but the per-branch subtotals throughout the data are a pain to work around (it can be done and not too slowly, but developing good design is much better than working around bad design IMHO)


    FYI, there are definitely general imporvements we can make to some of the formulae to make them less complex and implicitly easier to read.

  • Paying To Start Threads

    First ever post, and it's not a terribly happy one :(


    I'm all for robust admin and banning timewasters and ingrates (anybody whose post count equals their thread count would be a good start) but I'm not entirely comfortable with the concept of OzGrid charging people for volunteers' help (not that mine has much value, but others' does).


    I'd be interested to know if this is a permanent move, and if OzGrid is going to refund people who post relevant and worthwhile questions.


    More generally, I'd like to know if I'm the only one who feels this concern?




    ADDED BY ADMIN
    FACTS BEHIND THE COST

  • Find Common Numbers Between 2 Columns

    Re: Find Common Numbers Between 2 Columns


    Urk.


    There must be a better way... hmmm too... and also - how do you choose those colours?!


    ---


    Actually, more seriously, this might not work with 30 columns each splitting out into several columns with text to columns.

  • Static Date & Time Corresponding To Cell Change

    Re: Static Date & Time Corresponding To Cell Change


    Hey, let's not get grumpy with each other. In principle we agree; you are 100% right that offset is not the same. I think you understand that the point of my post was to explain to people who couldn't "decipher" the code how what is effectively an offset is being achieved. (That is why bluecondor could not easily tweak the code to his own spec).


    Anyone that gets this far will hopefully be able to understand the difference between "something" and "effectively achieving something". I thought I covered in my original post on this concept, but to be fair, I had left it a little ambiguous (even with the italics!)


    Ultimately, nobody will be any the worse off for reading your thorough dissection and I wouldn't remove it if it were up to me.


    As for all the examples proving the same point... I guess it makes it inescapably clear! :wink:

  • Auto-Extending Graph/Chart Range

    Re: Auto-Extending Graph/Chart Range


    Thanks for following up ManU - this really helps the forum to work best. As a general tip, you can really help yourself for the future by regularly returning to the "logic" of what you're trying to do.
    I'll work through your 'labels' column to explain:
    =IF(NOT(B2=0),IF(B3=0,D2,NA()),NA())


    1. IF(NOT(x=y)) means the same as IF(x<>y) (<> means not equal to). Obviously, the second is easier to read. So we could write:
    =IF(B2<>0,IF(B3=0,D2,NA()),NA())


    2. Using an if with parameters for "if true" and "if false" means logical statements can be inverted providing the "results" are swapped, like this:
    =IF(B2=0,NA(),IF(B3=0,D2,NA()))


    3. Nested ifs can sometimes be "compounded" with AND and OR statements (which work like NOT, i.e. applied 'around' other logical statement(s)). So let's look at the logic; you are testing two criteria:
    "Don't make a label if B shows 0 (i.e. beyond end of data range)" then -
    "If the next row value in B is 0, show the cumulative value (D) for this row, i.e. show the label if it is the end of the data"
    Or, getting nearer to the "boolean" form: show the data if it is not beyond the end of the range but the next value is:
    =IF(AND(B2<>0,B3=0),D2,NA())


    Which I think is easier to read and understand later on.


    HTH

  • Static Date & Time Corresponding To Cell Change

    Re: Static Date &amp; Time Corresponding To Cell Change


    General postscript:


    For those reading this thread, the target(y,x) which confused bluecondor temporarily is effectively an abbreviated form of the offset property which can be very easily understood from the VBA help files.


    It has slightly different syntax in that target(y,x) has the target at 1,1 whereas offset starts from target = 0,0. This is probably easier to understand like this:

    Code
    Target(4, 5) = Target.Offset(3, 4)
Target(0, 0) = Target.Offset(-1, -1) '!


    I could understand a justification for using the longer wording, it makes it slightly easier to "read" as you have the offset written explicitly, so helps you understand what the numbers are for. The difference in speed is minimal, but that would depend on what you're doing...


    HTH

  • Copying Specific Columns Into A New Worksheet

    Re: Copying Specific Columns Into A New Worksheet


    uh, no... oh dear.


    I mean, this part of the code:

    Code
    Dim myReplaceWith$ 
    myReplaceWith = "" ' jiuk - edit as needs be


    Defines a constant to replace awkward ASCII characters with; this part of the code:

    Code
    vResult = Application.WorksheetFunction.Substitute(vResult, Chr(10), myReplaceWith) 
    vResult = Application.WorksheetFunction.Substitute(vResult, Chr(13), myReplaceWith)


    Uses this value.


    So if you change the line in question to:

    Code
    myReplaceWith = " " ' jiuk - edit as needs be


    Then the function will replace characters with space
    This:

    Code
    myReplaceWith = " Cheeky Charlie is a wonderful man " ' jiuk - edit as needs be


    Would make the code functionally superb in every way.


    HTH

  • Assign Variable To Worksheet Formulas

    Re: Assign Variable To Worksheet Formulas


    Hi Kindlin,


    I feel inclined to wade in here:


    To answer your original question: No, Excel does not have a "k" function that can be applied to cells. As has been alluded to, you can build an equivalent in VBA, though this would require more manual reworking than would make it worthwhile.
    Here is an idea of how that would look:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Empty Then Exit Sub
Application.EnableEvents = False
If Target.Column = 1 Then Target.Formula = "=" & Target.Value & "*0.9685"
Application.EnableEvents = True
End Sub


    This would do what you're asking, put into the worksheet.


    You've been getting "unhelpful" answers because:
    This is hardcoded to apply only to changes in column A - if you have a more complicated range you'd have to hard code all that in too. It is not tremendously robust, and if it crashes, it will leave events disabled... which could be remedied.
    It took a couple of minutes to write, far less time than simply writing the formula =A1*0.9685 in cell B1 and copying down would take.


    There are plenty of extra columns and it isn't bad practice to use them to "stretch" formulae. Also, if you use an extra column for what you're doing, you can see the original numbers at a glance and you can quickly edit them (i.e. select cell, type, instead of select cell, edit within formula, delete old number, type new number).


    I appreciate you're better than most you know; on this forum, sadly, you ain't got nothin'. If it helps soothe, consider yourself above average in the real world, but here... embrace the geek, there are plenty of people here with frightening knowledge.


    HTH

  • Search Range For Part Strings

    Re: Search Range For Part Strings


    I think instr might work - have a look at the help file, the syntax is pretty straightforward and you seem to know what you're doing. Get back to us if that doesn't help.


    PS, sometimes I too find the great DH can OVERUSE the bold and italics to make you feel a bit WORTHLESS - remember he moderates about a thousand moron posts a day (I don't mean to include you with that) and has helped thousands of people via this forum

  • Highlight Formula Reference On Another Sheet

    Re: Highlight Formula Reference On Another Sheet


    Quote

    ... I've seen code that Dave (I think) has written that follows off- sheet references. I think the lack of response is that (a) whatever highlighting is done will hose the formatting already applied to the precedents, make it necessary to capture and restore it (including conditional formats), (b) people (including me) don't perceive much value over the existing capability.


    Quote

    ok...will do...can you please let me know if it is possible to automatically revert the formatting changes made by this macro?


    The thin line between tenacity and taking it too far...

  • Auto-Extending Graph/Chart Range

    Re: Extending A Graph Range


    So,


    If I understand you correctly, you are trying to get a chart to automatically update itself according to how much information has been entered in the columns storing its data.


    For this I would recommend not a macro, but using dynamic named ranges for your series, as per: http://www.ozgrid.com/Excel/DynamicRanges.htm


    Which is shown in my attachment. See how adding and deleting from the bottoms of the columns automatically adjusts the graph to fit.


    DNRs are trickier in charts than in pivot tables but basically, the key is to define the series by dynamic named range, not the chart overall source data by the collection of the series. If someone wants to make that intelligible please go ahead, I'm not precious.


    Finally, if you post an example we might be able to help a little better.


    HTH

  • Copy Cells Into Workbooks Names Mapped From Copy Cell

    Re: Copy Cells Into Corresponding Workbooks Names


    Quote

    Maybe posting the form, some sample data, a filled-out form, and an explanation in context would help.


    I'm confident this is the best way of helping us to help you. Your task seems like a series of fairly simple operations, but we don't like to waste time solving the wrong puzzle, if you see what I mean.