Posts by .broken.

    Re: Determine Chosen Option Button On Worksheet


    That's how i thought i should change the name of controls... it worked before for the other two buttons but isn't any more!! It keeps jumping to Label3 after i hit return... (I've also alternated between return and enter to make sure... still no luck). Even new ones refuse to be renamed.


    With regard to:

    Code
    sOptBut.ControlFormat.Value & " of Month DD Donors"

    Should it not be changed to .Name as .Value returns true/false?

    Re: Determine Chosen Option Button On Worksheet


    Thank so you much!


    They were indeed from the Forms Toolbar. As you can see from the code i previously posted, I was unable to rename the initial OptionButton. This means that your suggestion of

    Code
    sOptBut.ControlFormat.Value & " of Month DD Donors"

    will only work for my "15th" and "22nd" OptionButtons.


    Could you help me to rename the first OptionButton? And could you please explain the use of

    Code
    sOptBut.ControlFormat.Value

    to determine whether the OptionButton is checked and the use of it to determine the name of the OptionButton?

    Good day all,


    I am having trouble writing a macro that checks the value of an option button to change a string accordingly. The ActiveMonth variable is used to copy information across from one work sheet to another, with the worksheet depending on which of the option buttons is selected.



    'Object does not support this property or method' is the error i get. VB highlights the first If line.


    All corrections will be welcomed, this is driving me up the wall!!
    Thank you in advance.

    Everytime that I insert a row into a worksheet which a number of formulae refer to, those formulae get updated automatically in such a way that it is not in my favour.


    The formulae refer to rows 2:2000 on a number of different columns. Adding a new row shifts the references to 3:2001. I presume that I can stop this from happening - please help.


    Example:


    =SUMPRODUCT(('List of DD Donors'!E2:E2000)*('List of DD Donors'!F2:F2000='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")))


    changes to...


    =SUMPRODUCT(('List of DD Donors'!E3:E2001)*('List of DD Donors'!F3:F2001='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")))

    Re: #NUM! error due to changing cells a SUMPRODUCT refers to


    the formula is:

    Code
    =SUMPRODUCT(('List of DD Donors'!$D2:$D5024)*('List of DD Donors'!$E2:$E5024='DD Tally'!C5)*IF(DATEDIF(('List of DD Donors'!$F2:$F5024)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!$F2:$F5024)-1,DATE(2006,3,31),"m")))


    Column D contains the Quantity donated, E contains the Frequency (in this case, monthly) and F contains the date of the first payment.

    Can't seem to figure out why the following error occurs:


    I asked you kind chaps for some help a while back, see http://www.ozgrid.com/forum/showthread.php?t=50041


    The formula works wonderfully. Adding new donors is no problem whatsoever. However, if I change the 1st Month of a payment (eg, because someone has increased their donation, which'll kick in at a later date) then i get a #NUM! error in the formula.


    :confused:


    Any ideas?

    Had a quick look around too see if i could find what i was looking for, but i couldn't so here i am!


    First of all, thanks for you guys existing, because without you lot I'd be lost forever ;)


    I have a column (column D) that either has Yes or No written in it. Column C has a quantity in it. If D is Yes, i would like the quantity in C added... I'm guessing that this is probably a SUMPRODUCT equation?! relatively simple formula i'm guessing, but i'm useless!


    Cheers

    Re: Searching columns for entries & performing sums to the relevent row(s)


    Just had a thought that maybe the SUMPRODUCT could refer to a cell that has the month and year of the end of the financial year in it... would this be easy to implement?


    The work you've done so far is great!


    Just tried the D:D idea... doesnt work, so as a short term solution i added $D2:$D5000.


    Also, my idea of dividing by 4, 6 and 12 only works for the Semi-annually one! prob coz there's only one of them.

    Re: Searching columns for entries & performing sums to the relevent row(s)


    Code
    =SUMPRODUCT(('List of DD Donors'!$D$2:$D$21)*('List of DD Donors'!$E$2:$E$21='DD Tally'!A20)*IF(DATEDIF(('List of DD Donors'!$F$2:$F$21)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!$F$2:$F$21)-1,DATE(2006,3,31),"m")))


    Seems to do the trick!


    could u maybe help me a little? some questions follow:


    can the

    Code
    $D$2:$D$21

    be changed to just

    Code
    D:D

    as the spreadsheet is likely to grow?


    i presume that it refers to A20 so it knows what to search for? (in this case "Monthly")


    how can i change the date when a new financial year starts?


    and is it easy to set up similar things for the Quarterly, Semi-Annually and Annually? (i presume i could just divide the calculated monthly sum by 4, 6 and 12 respectively...)

    Not quite sure how to start this, but i'll give it a go anyway!


    I'm (and you are too!) still helping to streamline a charity's spreadsheet.
    There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit).


    In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors.


    We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors.


    The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.


    A long winded challenge to explain - please ask if anything is unclear!!


    Any help here would be highly appreciated by all of us at the Fundraising Deptartment