# Tax Rate Cell

#### hhammash

##### Board Regular
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Can't you convert the old values to values using PasteSpecial??

lenze

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

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

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

So use a Change Event on A3
Code:
``````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

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

Hi,

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

Thanks

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.

Yes the calculation is taking place befor the cells are converted. Maybe you can use selectionChange
Code:
``````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

Replies
0
Views
379
Replies
4
Views
105
Replies
3
Views
387
Replies
3
Views
40
Replies
4
Views
307

1,214,766
Messages
6,121,420
Members
449,032
Latest member
egspen2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back