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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,692
Messages
5,512,907
Members
408,920
Latest member
KLH81

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top