Tax Rate Cell

hhammash

Board Regular
Joined
Jun 24, 2002
Messages
156
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.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi,

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

Thanks
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top