Posts by Pesky Weasel

    Re: Negative number return in a formula


    Not really I'm afraid.


    This is what your formula does:


    The formula compares numbers to a base value ($I$5) with a tolerance of +/- 0.5%.
    If the value lies within that margin the formula returns 1, otherwise it returns ""


    Your sum formula then counts the number of values that meet the above conditions.


    Did you need it to do something else?


    Regards
    Weasel

    Re: Button to increment cell number


    dj,


    Place a button on the worksheet - makesure it is from the Controls toolbox not the Forms collection.


    Double click the button to activate the code pane, in between the buttons click event (this should appear by default) place the following:


    Code
    Sheets("sheet2").Range("a6").Value = Sheets("sheet2").Range("a6").Value + 1



    The end result looks like:


    Code
    Private Sub CommandButton1_Click()
    Sheets("sheet2").Range("a6").Value = Sheets("sheet2").Range("a6").Value + 1
    End Sub


    Regards
    Weasel

    Re: Displaying Sheet Names within one sheet


    ethc,


    There is no formula that refernces a sheet name. If you really want one paste this code into a standard module:


    Code
    Private Function SheetName(Sheet_Index As Integer)
    
    
    SheetName = Sheets(Sheet_Index).Name
    
    
    End Function


    Use it like this:


    =sheetname(1)


    where 1 is the order of the sheet in the workbook. Be warned if you change the order of the sheets in the book the column title will change but the references will still point to the original sheet.


    Regards
    Weasel

    Re: Date range inability


    kearnan,


    There are two ways to approach this but your information was a little vague.


    Method 1:
    In this example I have assumed that the first period spans 17 Oct 2003 to 15 April 2004 - any date within these parameters returns 15 April 2004.
    All other dates outside of this regardless of the year are retuned as 15 April 2004 +180 days (12 October 2004)


    =IF(AND(L4>=DATEVALUE("10/17/03"),L4<=DATEVALUE("04/15/04")),DATEVALUE("04/15/04"),DATEVALUE("04/15/04")+180)


    Method 2:
    This uses a nested if to restrict the +180 to a definite date range, you will have to supply the final argument of what the formula should return if neither criteria is met.


    =IF(AND(L4>=DATEVALUE("17/10/03"),L4<=DATEVALUE("04/15/04")),DATEVALUE("04/15/04"),IF(AND(L4>=DATEVALUE("04/16/04"),L4<=DATEVALUE("10/16/04")),DATEVALUE("04/15/04")+180,"Invalid Date")


    Regards
    Weasel

    Re: Purdy Spreadsheet examples?


    Amadeus,


    Welcome in from the cold.


    Making Excel look pretty is just a matter of playing around with the formatting tools however see here for some tips on good spreadsheet design.


    I cannot emphasise enough that a good spreadsheet has its foundations in a clear and logical data layout, not how many fonts, colours and shades you can squeeze onto one page. Have a look at the AutoFormat under the Format menu - but unless your data is laid out in database table style this will unpredictable results.


    Hope That Helps
    Weasel


    edit: I changed the name of the post to more accurately reflect the question. This assists with referencing the question with similar posts and the search function. Pleasae see the sticky note - http://http://www.ozgrid.com/forum/showthread.php?t=20947 for further details.

    Re: make a formula change another cells value


    No - a formula cant influence any cell other than the one it is entered into. Having said that, there is always a workaround but the success of this will depend on how intelligent you want the workaround to be.


    Will the positive integer always be 1 digit?
    Will it always be at the end of the text?


    Regards
    Weasel

    Re: Autofill two rows using r1c1 notation.


    Lera,


    It is a little hard to suggest a more efficient way of performing the task without seeing the rest of the Sub, could you possible post the complete code (so long as it isnt 100s of lines :) )


    Regards
    Weasel

    Re: Evaluating numbers less than x


    Max,


    see commented code below:



    Regards
    Weasel

    Re: Evaluating numbers less than x


    Max0d


    There were a few logical errors in your code. Please see sample below and compare the differences, post back if you don't understand why it now works (hopefully :) ).


    I presume that the cell J1 contains a count of the number of records.



    Regards
    Weasel

    Re: Setting Location of Programmatically added Controls by Cell


    dopplex,


    Welcome to Ozgrid - Glad you like it!


    The example below uses the active cells left, top, width & height properties to add a command button to each cell in a selection of cells. To test highlight a range of cells and then run the macro.



    Hope that helps
    Weasel

    Re: Speeding up VBA Coding


    Andrew,


    Assuming:


    1) Your table contains only the data you wish to effect
    2) There are no gaps in the data
    3) Row one had the column headers


    The following should do as you wish -



    Regards
    Weasel

    Re: assign value to list from VBA


    itsmaheshp,


    You will have to provide a little more information here:


    Does the cell contain a validation list? or
    Do you want to add a value to the cell at the end of a list?


    How do you propose to get the value that will be added:
    User input?
    Calculation?
    Variable?


    When is the value to be added:
    Button click?
    Conditions met?


    Regards
    Weasel

    Re: formulas don't resolve


    ymoriarty,


    Welcome to OZGrid!


    Could the formatting of cells be changed to text?
    Try changing the format of the cell to General and then select the cell in Edit Mode (ie make the cursor flash in the formula bar), then hit enter.


    Regards
    Weasel

    Re: Select In Select


    Thanks Will,


    I will try that out asap. My introduction to SQL so far has not covered the CASE keyword. Thats 6 keywords now - 994 to go :)


    Weasel