Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Edinburgh, Scotland
    Thanked 0 Times in 0 Posts

    Problem with SumProduct - can I protect a row ?

    I'm a basic spreadsheet user so please go kindly and simply on me ! What I've got works (sort of!). It may well be that there are other ways of doing this but can we avoid going down that road.

    We have an approximately 50 strong group that goes away for a week each New Year and take it in turns to organise the finances, etc. It was my turn last year and I wrote a basic spreadsheet to track the bed-nights, incomes and out-goings.

    This year's organiser is using the same spreadsheet, but has found a flaw.

    I've used SumProduct on the column of Balance Due from each family - in conjunction with the next column showing the date paid, the sheet can show total monies in hand and total due, so I have 3 cells with the general form "=SUMPRODUCT(($L$3:$L$32=""),$K$3:$K$32)". The problem arises with the row 32 in this formula. Last year I had 31 family entries; and then a family child arrived during the week and I just quickly wrote them into row 32 which was empty. That was a mistake as you will see.

    I cleared the sheet this year of all but the names in Column A (up to row 32!) before passing it to this year's organiser, who found it all worked fine until he did Insert Rows after row 32 to add some new names and the SumProduct formula of course didn't track these new rows.

    Easy enough to sort, but the query I have is can I protect this now mythical Row 32 so an operator is forced to do an Insert Row before this Row 32, rather than overwriting it?


  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Pa, USA
    Thanked 652 Times in 594 Posts

    Add the following code to the Worksheet module of the sheet you want to restrict inserting rows. If the user makes a selection greater then row 32, the insert row function will be deactivated and the user cannot insert any rows. If the user selects row 32 or less, the insert row function will be activated and rows can be inserted. If you need instructions on how to add the code to the worksheet module, please let me know. I am guessing you want this solution to create a dynamic range and extend your sumproduct formula to the inserted row. Named ranges may also be a solution you might want to explore.


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 32 Then
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
            False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, AllowInsertingColumns:=True, _
            AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
            AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
    Else: ActiveSheet.Unprotect
    End If
    End Sub
    Last edited by Maudibe; 2015-12-14 at 08:12.

Posting Permissions

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