Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Sum button event - based on validation

    Hi,

    I am working on the attached template, wherein I have several fields like Date, Name, State, Type and Count.

    I have a “Submit” button in the end as well.

    Now I want that, If I select date as “1/1/2012”, Name “Alan”, State “SC”, Type as “Type 2” and Count “5” and click submit, that count would sum into the tab of that date under Alan’s name of SC, Type 2.
    Next time if I select the same value and click submit, it must show the value 10 under Alan, SC, Type 2 of 1/1/2012.

    Similarly I have 100 names and approximately 20 states with multiple types.

    I want a code for that event so that I can modify as per my requirements.

    Thanks,
    Ankit
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    See attached file.

    In order to do what you want, you need to find the particular row that needs to be updated for the person selected, on the sheet given by the Date choice.

    It is easier to find the required row if you add a new column on your sheets.
    This new column would contain a 'cross-reference' which simple combines the Type and State.
    You could hide this extra column.

    I have added some named ranges on the Input sheet to make things a little easier.

    Also, you would need to check that all 5 choices have been made before you can 'post' an updated value. (and do other checks)

    I have added a 'Clear' button as well.

    I have left in some formulas and comments alongside the data entry cells for your info.
    These could be hidden.
    Note that once you have made your Input selections, any existing current value will be shown, together with the new value that will be entered when you click the Submit button.

    I'm sure others could help you but hope this will start you off.

    zeddy
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for your help on this.

    I was trying to make something out of it. Most of it was done...but while UAT, I found the attached error message.


    Also, I want that instead of Clear , there must be a "Subtract" button that subtracts from the original count...

    Please advise.

    Attached are the error messages.

    Thanks,
    AnkitError 2.jpgError 1.jpg

  4. #4
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Oops I missed the updated xls file.

    Here is the attached file...
    Attached Files Attached Files

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    The reason why you can't post the number is because it couldn't find the cross-ref row for state CO in the Cert section.
    This is why you see a #N/A on the front screen.
    The #N/A tells you something isn't quite right.

    In this case, you can trace the error by moving to sheet [3-1-2012] and unhiding column [B] which is used for the cross-ref.
    As you will see, the formulas used to create the cross-ref have not been copied all the way down in the Cert section.
    To copy the formulas all the way down in this section:
    put the cellpointer in cell [B62] on sheet [3-1-2012], and then double-click the mouse on the tiny little black square in the bottom-right corner of this selected cell.

    Do the same for the section 'Reg':
    put cellpointer in cell [B118], and then then double-click the mouse on the tiny little black square in the bottom-right corner of this selected cell.

    You will need to do the same on ALL the other sheets.

    zeddy

  6. #6
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Zeddy for the reply....It's working great...

    The only challenge I am facing at the moment is that is there any way to enable the mouse scroll in the drop down list and also I want that If I press letter "S" or any other letter in validation cell, it must go to the name beginning with that letter.

    This is the same thing which we normally see in web based applications. I have looked through multiple links on google and internet but unable to find any.

    Please help.

    Thanks,
    Ankit

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi
    I'm not sure I understand the 'enable mouse scroll' in the drop down list.
    If I click on the dropdown arrow to display the list, I can certainly use the mouse to scroll.

    Regarding pressing the letter "S": The behaviour you are looking for is achieved by using a 'combo box' (inserted from the Control Toolbox menu) rather than a data validation dropdown.

    I have given an example in the attached file. My example replicates the State dropdown. You can look at the 'properties' of the example combo box to see I have named the source data range for the combo box as listState and specified a named cell inputState for the link.

    Personally, I prefer to avoid using Control Toolbox controls on a worksheet - they are perhaps better used on a Form instead.

    zeddy
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •