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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Can't you convert the old values to values using PasteSpecial??

lenze
 

hhammash

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
Jun 24, 2002
Messages
156

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

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

Board Regular
Joined
Jun 24, 2002
Messages
156
Hi,

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

Thanks
 

hhammash

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,835
Messages
5,833,898
Members
430,241
Latest member
Matty Se

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
Top