Using drop down list to populate cells

[ExcelBeginner34]

A	B	C	D	E	F	G	H	I
Tenant Name	Recovery Restrictions (Drop Down List)	Amount	Total Budget	Max Recovery	Annual Charge To Tenant	Annul Charge To Landlord	Quartely Tenant	Qaurterly Landlord
Tenant A	E.g Void	100	100			£100		£25
Tenant B	E.g Rent inclusive	200	200			£200		£50
Tenant C	None	300	300		£300		£75

<tbody>
</tbody>

Hi, I have a sheet in which I have created a drop down list. This contains 4 options Void, Rent Inclusive, Cap/Lease Defect and None. How am I able to set the sheet up so that when the user selects one of these options it automatically populates a cell in the table with a figure copied from another cell? And then if they change the drop down list option it goes back to say blank again?

My table looks like the above. So what I want is to be able to select each of the drop down list options (they will be the same for all col B) and for this to have the following result:

Void - insert the Total Budget Figure (£100) into the Annual Charge to Landlord cell and then in the Quarterly cell show the amount reflecting 25% I,e 25.
Rent inclusive - the same as void above.
None - as per void above but this time populating the Annual Charge to Tenant cell and then the Quarterly figure.
Cap/Lease Defect - no action required.

I have shown as an example in the table how the above would look.

Hope you can help.

Best regards

Iain

 

[mumps]

On the Expenditure Report tab you want to hide the rows where cells in columns C, F and I are all equal to £0.00 and unhide them if the cell in any of the 3 columns does not equal £0.00. Is this correct? I'm afraid that I still don't follow what you mean by:

I would also like to populate the table shown in Columns AJ - AM with data

Can you explain in more detail using examples and referring to specific cells, rows and columns? I need to know specifically what each cell in AJ:AM should contain.

 

[ExcelBeginner34]

Hi - yes that’s right on the expenditure tab. Don’t worry about the other table - this isn’t actually that critical.

 

[mumps]

Try:

Sub HideRows()
    Application.ScreenUpdating = False
    Dim Val As String, Val2 As String, ws1 As Worksheet, i As Long, v1, item
    Val2 = "0|0|0"
    Set ws1 = Sheets("Expenditure Report")
    v1 = ws1.Range("C14:I" & ws1.Range("I" & Rows.Count).End(xlUp).Row).Resize(, 7).Value
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1) & "|" & v1(i, 3) & "|" & v1(i, 7)
        If Val = Val2 Then
            Rows(i + 13).Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Sub UnHideRows()
    Application.ScreenUpdating = False
    Cells.Rows.Hidden = False
    Application.ScreenUpdating = True
End Sub

 

[ExcelBeginner34]

Thanks - I have added this code but am getting the error message Runtime Error 1004 Unable to set the Hidden property of the range class. When I click debug the below row is highlighted.

Rows(i + 13).Hidden = True

Any thoughts?

 

[mumps]

Is your sheet protected?

 

[ExcelBeginner34]

Yes it is - that explains it. I have added some code to unprotect the sheet. Any thoughts on getting the Cap/Lease Defect functionality working? In post number 8 above?

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:E,AD:AD")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo errHandler
    If Target.Column = 5 Then
        Select Case Target.Value
            Case "Void", "Rent Inclusive"
                Cells(Target.Row, 30).Resize(1, 5).ClearContents
                Target.Offset(0, 27) = Target.Offset(0, 24)
                Target.Offset(0, 29) = Target.Offset(0, 24) * 0.25
            Case "None"
                Cells(Target.Row, 30).Resize(1, 5).ClearContents
                Target.Offset(0, 26) = Target.Offset(0, 24)
                Target.Offset(0, 28) = Target.Offset(0, 24) * 0.25
            Case "Select"
                Cells(Target.Row, 30).Resize(1, 5).ClearContents
            Case "Cap/Lease Defect"
                Target.Offset(0, 27) = Target.Offset(0, 24)
                Target.Offset(0, 29) = Target.Offset(0, 24) * 0.25
        End Select
    ElseIf Target.Column = 30 Then
        If Range("E" & Target.Row) = "Cap/Lease Defect" Then
            Target.Offset(0, 1) = Target
            Target.Offset(0, 2) = Target.Offset(0, -1) - Target
        End If
    End If
errHandler:
    Application.EnableEvents = True
End Sub

 

[ExcelBeginner34]

Great that has worked mostly. The only thing that it isn't doing is showing the quarterly amount for the tenant in AG10. This would be a quarter of the figure in AD if that helps.

 

[mumps]

How about:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:E,AD:AD")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo errHandler
    If Target.Column = 5 Then
        Select Case Target.Value
            Case "Void", "Rent Inclusive"
                Cells(Target.Row, 30).Resize(1, 5).ClearContents
                Target.Offset(0, 27) = Target.Offset(0, 24)
                Target.Offset(0, 29) = Target.Offset(0, 24) * 0.25
            Case "None"
                Cells(Target.Row, 30).Resize(1, 5).ClearContents
                Target.Offset(0, 26) = Target.Offset(0, 24)
                Target.Offset(0, 28) = Target.Offset(0, 24) * 0.25
            Case "Select"
                Cells(Target.Row, 30).Resize(1, 5).ClearContents
            Case "Cap/Lease Defect"
                Target.Offset(0, 27) = Target.Offset(0, 24)
                Target.Offset(0, 29) = Target.Offset(0, 24) * 0.25
        End Select
    ElseIf Target.Column = 30 Then
        If Range("E" & Target.Row) = "Cap/Lease Defect" Then
            Target.Offset(0, 1) = Target
            Target.Offset(0, 2) = Target.Offset(0, -1) - Target
            Target.Offset(0, 3) = Target * 0.25
        End If
    End If
errHandler:
    Application.EnableEvents = True
End Sub

 

[ExcelBeginner34]

Thanks - that works really well for splitting the costs. The only issue now is that the quarterly figure in AH is still showing as being a quarter of the amount in AC instead of being a quarter of the split amount shown in AF. For example, if AC is £743.37 and AD is £500, AE is £500, AG is 25% of that, AF is the balance of £243.37 but AH is showing as £185.84 which is a quarter of the £743.37 original figure before being apportioned between the Landlord and the Tenant. Any thought on how to solve that one? Best regards