Solved Excel Formula Depending on Data Entered

Microsoft Office excel 2003
September 30, 2016 at 11:08:44
Specs: Windows 7 Pro 6.1 SP1, Intel Core i5-3470 3.20 GHz; 8GB RAM
Good afternoon.

I have searched the internet and Computing.net for an answer to this and cannot locate one. Any help would be really great!

I have a spreadsheet with expenses as part of a person's personal finances. The person can enter either a monthly expense or yearly expense for each item. What I want is regardless of which one they choose to enter, the other period will calculate accordingly.

For example,

...............................A..........................B....................
1........................Monthly.................Yearly................
2...Rent.................??.........................??...................
3...............................................................................

If the person enters $800 under monthly I want the Yearly cell to automatically calculate the annual amount. If the person chooses to enter a Yearly amount I want the Monthly cell to automatically calculate the monthly amount.

What formulas would I place in the Monthly (A2) and Yearly (B2) cells so this can be done?

Thanks again.


See More: Excel Formula Depending on Data Entered

Reply ↓  Report •


#1
September 30, 2016 at 11:54:25
✔ Best Answer
What Column is the word Rent in? ;-)

I'll assume A2 with your Monthly/Yearly values in B2 and C2.

You can not put a formula in the same cell as you want to allow user entry. Think about what would happen to the formula when the user manually enters $800. The user would over-write the formula.

In order to accomplish your goal, you will need a macro that monitors both cells and then calculates the values that goes in the other:

1 - Right click the sheet tab for the sheet you want this to happen in and choose View Code.
2 - Paste the following macro into the pane that opens.
3 - Back in the spreadsheet enter a value in B2 or C2. (not A2 or B2)

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
  If Target.Address = "$B$2" Then
   Range("$C$2") = Target * 12
  ElseIf Target.Address = "$C$2" Then
   Range("$B$2") = Target / 12
  End If
Application.EnableEvents = True
End Sub


Keep in mind that macros need to be enabled on any machine that uses this spreadsheet. Macros are something that only the user can enable, they cannot be enabled by a macro. There are ways to "force" a user to enable macros by presenting a "page" in the workbook informing them that macros must be enabled in order for the workbook to be used properly, but we don't have to go into that until you tell me that you need to do that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
October 12, 2016 at 13:38:01
DerbyDad03, thanks very much for this. This seems like it would work great. As for a page informing the user about enabling macros how would I go about that if it's not too much trouble?

Thanks again.


Reply ↓  Report •

#3
October 12, 2016 at 19:08:51
The process and code is available here:

http://www.xl-central.com/force-use...

Note: I have not tested the specific code at that site, but it looks like the standard code available at many other sites. If you Google "force users to enable macros" you'll find other sites that explain the process and provide code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions


Ask Question