Using drop down list to populate cells

[ExcelBeginner34]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D

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

[/TD]
[TD]None
[/TD]
[TD]300
[/TD]
[TD]300
[/TD]
[TD][/TD]
[TD]£300
[/TD]
[TD][/TD]
[TD]£75

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column B.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Void", "Rent inclusive"
            Target.Offset(0, 5) = Target.Offset(0, 2)
            Target.Offset(0, 7) = Target.Offset(0, 2) * 0.25
        Case "None"
            Target.Offset(0, 4) = Target.Offset(0, 2)
            Target.Offset(0, 6) = Target.Offset(0, 2) * 0.25
    End Select
End Sub

 

[ExcelBeginner34]

Hi thanks to this. That has worked great. The only question I have is if the void option was used by mistake and then the user selected none then both the landlord and tenant cells would be filled. Is there anyway if another drop down option is chosen then the cells would be cleared again before the correct option is chosen?

Thanks

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo errHandler
    Select Case Target.Value
        Case "Void", "Rent inclusive"
            Cells(Target.Row, 6).Resize(1, 4).ClearContents
            Target.Offset(0, 5) = Target.Offset(0, 2)
            Target.Offset(0, 7) = Target.Offset(0, 2) * 0.25
        Case "None"
            Cells(Target.Row, 6).Resize(1, 4).ClearContents
            Target.Offset(0, 4) = Target.Offset(0, 2)
            Target.Offset(0, 6) = Target.Offset(0, 2) * 0.25
    End Select
errHandler:
    Application.EnableEvents = True
End Sub

 

[ExcelBeginner34]

Thanks again for your help.

Could you explain what this code - Cells(Target.Row, 6).Resize(1, 4).ClearContents - is asking Excel to do? My table isn't exactly the same as the one above. It was too large to copy in. I managed to edit the original code given and it works perfectly. So does the above code when I use it in a blank sheet and copy and paste in the table exactly as shown above. I think it is this line of code which is the issue. My interpretation of this is that the target row is row 6 I,e two rows below the end of the data in the table and then the Resize command is asking for rows 1 through to 4 to be cleared i.e the rows in the table containing the figures. I may be completely wrong !!!

Any further help would be greatly appreciated.
Thanks

 

[mumps]

To address the problem you described in Post# 3, the macro now clears columns F to I before it fills the cells with values. This way any old data is deleted. Target.row refers to the row in which you make the selection in the drop down list and the six refers to column F. Resize(1,4) just resizes the range to be cleared to 4 cells starting in column F (F:I). If you are still having problems, upload a copy of your file that is representative of your actual data. What works on sample data most often will not work with the actual data.

 

[ExcelBeginner34]

Perfect, that makes sense now and it is now working. Thanks. If I wanted to have another cell populated, when the void option is chosen, would I just add another line of code like this under the relevant case heading? Target.Offset(0, 27) = Target.Offset(0, 24)

Below is the coding I now have. If I wanted to populate another cell which is

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

If we are taking 0 as being column E - where the drop down list is - then the cell I want to populate is AK3 - so 7 rows above the first row with the drop down and then 33 along to the right. How would I enter this?

Thanks

 

[ExcelBeginner34]

Sorry another question - for the Cap/Lease Defect option on the drop down I want to be able to carry out the following:

In column AD enter an amount in £ - this reflects the maximum amount which can be recovered from a tenant.
Then have the amount in Column AC Proportion of Total Budget split between column AE annual charge to tenant and AF annual payable by landlord. So for example if the amount in AD was £25 and in AC was £100 then the £25 would go into AE and the remaining amount of £75 would go into AF.

Is that possible? Again the drop down list is in Column E.

Thanks

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[ExcelBeginner34]

Hi, have uploaded the file to the below link.

On the Expenditure Report tab - I want to be able to run a macro which will hide any rows where the cells in all of Columns C, F and I are £0.00. I have tried creating a Macro for this and it shows the various rows in each of these columns which can potentially have a value in them. However, when I run this, it hides everything except the headings. If there is a value in only column I for example, it would still need to show once you run the macro. Effectively, what I am trying to do is to create one view Print View - which removes any rows which have no value (to tidy the report up for printing) and a Data Input View where the user can input data. I want them to be able to switch between the two views in case they make a mistake and need to go back to the full Data Input View and add or remove something.

On the Apportionment tab - the code is now in place to work the drop down list. However, as per my earlier post I would like to add some functionality to the Cap/Lease Defect option as described previously. I would also like to populate the table shown in Columns AJ - AM with data. So whenever someone uses the void option it will populate the value in cell AL3, but if for example all three of the tenants were void the figure would need to be the total of all three added together. I would then want to repeat the same for None, Rent Inclusive. These would be split so that Void and Rent Inclusive would populate the Landlord costs in AL3 and None would be the tenants in AK3. The cap/lease defect would be split between the two depending on what amount was entered into col AD.

Hope that makes sense.

Thanks

[url]https://www.dropbox.com/s/r0e6wbdycg8x4lw/TEST%20Service%20Charge%20Template.xlsm?dl=0
[/URL]