Page 1 of 3 123 LastLast
Results 1 to 15 of 34

Thread: Excel tables

  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel tables

    Hello,

    I washoping you might be able to assist me.

    I haveattached a very simple excel file containing 2 tables. I would like to beable to tick the boxes in table on sheet one and for table in sheet 2 toautomatically update based on what was ticked in table on sheet one.

    This wouldbe a repetitive action within the exercise, supposing 100 times, by the end ofwhich I would like to see the to the results in table on sheet 2. Basically how many of those 100 answered yes or no on the different criteriaBook1.xlsx

    Is thispossible and if so where should I begin, I thought of feeding into a pivottable range but am not sure how tom go about this either.

    Anyassistance would be greatly appreciate. I am using Excel 2010.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Pat,

    Welcome to the Lounge as a new poster!

    A couple of questions.

    1. Do you plan to have other rows on Sheet 1 or are you going to clear the entries each time it is used and just accumulate the results on sheet 2? For example, I check the appropriate boxes on Sheet 1 then push a button to add the results to sheet 2.

    2. The other option I can see is that this is just a template and cols A & B will have other info like Item and Date and there will be a long list down the sheet?

    The reason I ask is the formula/code used to accomplish each of the options above is very different. Please advise.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Pat,

    Welcome to the Lounge as a new poster!

    A couple of questions.

    1. Do you plan to have other rows on Sheet 1 or are you going to clear the entries each time it is used and just accumulate the results on sheet 2? For example, I check the appropriate boxes on Sheet 1 then push a button to add the results to sheet 2.

    2. The other option I can see is that this is just a template and cols A & B will have other info like Item and Date and there will be a long list down the sheet?

    The reason I ask is the formula/code used to accomplish each of the options above is very different. Please advise.

    Hi Retiredgeek, Thanks so much for your response.

    Yes, Initially I would just like to have a button that would add the results to sheet 2, after which I can remove the infos from sheet 1 but have sheet 2 accumulate the results after multiple new entries on sheet one. so option 1 you mention above.

    Ideally if there is a way of adding a reference number for each entry in sheet one that can then be associated with the results and the results be retrieved using that reference number that would be fantastic, but initially just a button to update sheet 2 would be great. Your time and assistance is much appreciated!
    Cheers!!

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    PatKav,

    Here is your revised workbook. Clicking Y/N in your grid places checkmarks for your questions. Clicking the Submit updates the running totals for each question. There is a log sheet that maintains a record of the entries and assigns an index.

    HTH,
    Maud

    Sheet 1
    PatKav1.png

    Sheet2
    PatKav2.png

    Log sheet
    PatKav4.png
    Attached Files Attached Files
    Last edited by Maudibe; 2015-05-22 at 09:32.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Nice work...you're faster on the draw once again!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Thanks RG. Certainly not a race! The thought just popped when I was reading the post and your clarification made it clear. Please add your thoughts to the project.

    Maud

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Since you asked.

    Designed so the user need only double click one of the cells to set it.
    See Notes sheet in the attached workbook.

    Sheet1 Code:
    Code:
    Option Explicit
    
    '                   +-------------------------------+             +----------+
    '-------------------| Worksheet_BeforeDoubleClick() |-------------| 05/22/15 |
    '                   +-------------------------------+             +----------+
    'Toggles cells in DefinedRng between blank and check mark when double clicked
    'For flexibility create DefinedRng as a DynamicNamedRange!
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("AnswerGrid"), Target)
       If Not isect Is Nothing Then
       
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
    
         With Target
             .Font.Name = "Marlett"
             .Value = IIf(.Value = vbNullString, "a", "")
         End With  'Target
         
         Application.EnableEvents = True '*** Reset Events ***
         
       End If
    
       Cancel = True
       
    End Sub          'Worksheet_BeforeDoubleClick()
    Module 1 Code (Submit Button Processing)
    Code:
    Option Explicit
    
    Public Sub EnterData()
    
      Dim shtTally As Worksheet
      Dim celCur   As Range
        
      Set shtTally = Worksheets("Sheet2")
      
      For Each celCur In Range("AnswerGrid")
      
         shtTally.Range(celCur.Address()).Value = _
            shtTally.Range(celCur.Address()).Value + IIf(celCur.Value = "a", 1, 0)
         celCur.Value = ""  'Blank out
         
      Next celCur
      
    End Sub
    Test File: Copy of PatKav_Revised RG1.xlsm

    Just a different way to do things...choices so many choices!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Pat,

    Here's a revised version that Hides the Submit button until at least one item is checked and will rehide it if all checks are removed.

    Also note I named the sheets to make the code more understandable.

    Test File: Copy of PatKav_Revised RG2.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    RG,

    Very Nice. Any reason for a double click instead of a single click? You can have the user just toggle back and forth between Y/N with a single click where a click on the Yes will erase the No and a click on the No will erase the Yes. Also, the user can select both a Yes and a No for the same question. This will incorrectly tally both responses for the question on the Tally sheet and the log sheet does not seem to be updating. Lastly, the user is locked out of selecting any other cell on the sheet. I suspect this was out of design, however, when the user double clicks on any cell outside the grid, it will affect the active cell on the inside of the grid.

    HTH,
    Maud

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    RG got me thinking about validation. What if the user left a question blank. If all questions must be answered, one way to handle this would be a pop-up indicating that a question was left blank. The user would not be able to submit until all questions were completed.

    HTH,
    Maud

    PatKov_validate.png
    Attached Files Attached Files

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Very nice trick leaving A1 unprotected and returning there for the firing of SelectionChange! While we're at it I just thought I'd toss in a general design consideration...why even have a NO column? If Yes is unchecked it is NO it's not only redundant to have a no column but requires more code to handle. Of course if the choice "not made" information is critical to the application then both Yes & No are required (requiring different code that if not) but in my experience this is not often required. I'm stating all this because Less code = Less maintenance = $ Savings.

    This in no way takes away from the excellent code you wrote!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    RG

    You offer an excellent point with eliminating the No column. Perhaps the OP would consider the design change in his project. A method of determination may have to be made to distinguish between a question that the user wants to skip and a answer that will default to No.

    Maud

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Pat & Maud,

    Seems I just can't seem to stop playing with this one.

    Here's a version that, I hope, gets the best of what we've been discussing.

    1. Single Click to change value.
    2. Single Input Column.
    3. Still maintains dual column totals.
    4. Submit button hidden or re-hidded if no checks.


    Test File: Copy of PatKav_Revised RG3.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Pat & Maud,

    Seems I just can't seem to stop playing with this one.

    Here's a version that, I hope, gets the best of what we've been discussing.

    1. Single Click to change value.
    2. Single Input Column.
    3. Still maintains dual column totals.
    4. Submit button hidden or re-hidded if no checks.


    Test File: Copy of PatKav_Revised RG3.xlsm

    HTH

    Geek and maud,

    I cannot thank you enough! hopefully soon I will be able to contribute to other peoples posts and help them the way thay you have helped me.

    Cheers!!

Page 1 of 3 123 LastLast

Posting Permissions

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