Tax Rate Cell

[hhammash]

Hi,

I have in cell A3 a value for Tax rate (0.037216), and in b5 I have a formula to calculate the tax for the purchase price =A5*A3/100. The tax rate might change after a month or so, how can the user, who is an old man, change the value in A3 without affecting the already calculated cells.

Example:
From A5 to A23 the tax was calculated based on 0.037216, how can the user change the tax rate in cell A3 to (0.037145) and affect only the new rows and keep the calculated values from A5 to A23 untouched (based on the old tax rate).

Thanks

 

[lenze]

Can't you convert the old values to values using PasteSpecial??

lenze

 

[hhammash]

Thank you for your reply,

I can use Copy and PasteSpecial, but he can't.

As I mentioned, the user is an old man who is not at all good at computers.

He know how and where to enter the purchase price and how to change the tax rate in the dedicated cell.

If there is no automatic solution for this, then I will have to ask him to enter the tax rate each time he enters the purchase price.

Thanks

 

[lenze]

Can you explain a little about the layout? I believe you can do the conversion in the background every time the Tax Rate (C3) is changed.

lenze

 

[hhammash]

Hi,

I have Cell A3 which contains the current tax rate. Then below it on A5 I have Purchase Price, in B5 I have a formula that calculates the tax rate =A5*A$3/100. A3 is fixed in all the cells in column B.

The tax rate will change, the user has only to take the cursor to A3 and change the tax rate, but what happens is that all the previous calculations get affected because of referencing A3.

How to change the value of A3 without affecting the previous calculations.

Thanks

 

[lenze]

So use a Change Event on A3

Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Address <> "$A$3" Then Exit Sub
With Range("$B$5:$B" & Cells(Rows.Count,"A").End(xlUp).Row)
    .Value = .Value
End With
End Sub

The user will never know when it happens

lenze

 

[scottylad2]

Ow often will the tax rate change? If its yearly or on specified dates you could buil a tax table and use a vlookup tied to dates to reference, you wont be able to change it using just one cell and one rate. VBA may work that way, doubt a single cell formula would though

 

[hhammash]

Hi,

The tax rate will not change often, might be quarterly, semi-annually or annually.

Thanks

 

[hhammash]

I tried the code but it did not solve the problem. Still when I change the value on A3 the results in B column change.

 

[lenze]

Yes the calculation is taking place befor the cells are converted. Maybe you can use selectionChange

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
With Range("$B$5:$B" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Value = .Value
End With
End Sub

Anytime A3 is selected, the cells will be converted to values!!

lenze