I need help with an if/then (?) formula, please!

[kittycrickett]

division.jpg

I need help with a formula. In concept, I think it sounds simple, but I am not able to put together a string to make it work. Any assistance is appreciated!

I am making a form that in the end will have locked cells and locked formulas. Users will be able to enter sums into the yellow cells (N1 and O1); the white cell (L1) will be locked (see the snippet). I need the sums in N1 and O1 to always equal L1. There will be multiple rows of this same input with totals below. What I am trying to accomplish is to cut down on user math errors and to simplify data entry.

The user will put a sum in either N1 or O1. Whichever cell they put a sum into will automatically subtract from whatever sum is in L1 (in the example it is $1,000) and display it in the cell the user did NOT put a sum into. For example, if the user puts $400 into cell N1, then $600 automatically displays in O1. The reverse must be possible; if the user decides to put $600 in O1 first instead, then $400 is reflected in N1.

The user needs to be able to change the amount they are able to put into their cell and the calculation remain. For example, if they later decide to put $300 in N1, O1 will still reflect the difference ($700). Meaning, the formula remains even though the user deleted their sum from the cell. Maybe I can put the formula for the L/N/O sequence off to the side in a cell that will be hidden?

Thanks in advance for responses!

[Richard Buttrey]

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[mehmetcik]

What you ask is not achievable using a formula.

Right Click on the sheet name at the bottom of excel and select view code.

Paste this code in the module that opens and then close the module.

Please Login or Register  to view this content.

Enter a number in N1 or O1

[kittycrickett]

WOW! Thank you so very much! When I protect my worksheet and workbook, will the code still be accessible to the user?

[mehmetcik]

If they know what to look for.

http://www.wikihow.com/Protect-VBA-Code

[kittycrickett]

Thank you again! I really appreciate your help!

[Richard Buttrey]

As mehmetcik says it will be accessible unless you also enter a password in the VBA Project properties window.

[kittycrickett]

A little follow up. I had formulas to subtotal and total at certain points in the N and O columns but now I am unable to put those subtotal formulas back in, it only looks to the code for the entire column. Is there a way to limit the code only to certain groups of L/N/O cells?

Also, if the user later decided to remove the amount from L1, is there a way for N and O to automatically zero out?

[mehmetcik]

Easily done.

Let me think of something easy for you to maintain.

The second line creates an array of valid row numbers ie: 1, 5, 7.
Any other row is ignored.

Please Login or Register  to view this content.

[kittycrickett]

Thank you for your reply. When I put the new code in, nothing happens (as if there is no code there at all).

[mehmetcik]

Apologies I got my Array Names confused.

Please Login or Register  to view this content.

[kittycrickett]

Thank you, thank you, thank you!

So for the array numbers, do I identify the specific rows that I want this calculation to work for? Do I list them individually or can I say N1:N20?

This code is way beyond my excel skillset and I can't tell you how much I appreciate your help! Just because I'm curious: from the code you provided, how does the code know it is only supposed to be looking at columns L/N/O?

[Richard Buttrey]

Thank you, thank you, thank you!

You can't use N1:N20 without changing the code syntax
Columns L,N&O are the references to column numbers 12,14 & 15 in the code.

[kittycrickett]

Easily done.

Let me think of something easy for you to maintain.

The second line creates an array of valid row numbers ie: 1, 5, 7.
Any other row is ignored.

Please Login or Register  to view this content.

So, for the line: ValidArray = Array(1, 5, 7) I need to individually list the rows that I DO want the calculation to work for in the parenthesis?

[kittycrickett]

So, its working now. I am so very excited!

So, lastly, if the user later decided to remove the amount from L1, is there a way for N and O to automatically zero out?

[mehmetcik]

Try this:-

Please Login or Register  to view this content.

[kittycrickett]

The code worked like a charm until today. Now it doesn't work on any workbook / sheet I open. Any ideas? Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
ValidArray = Array(8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 52, 53, 54, 57, 58, 59, 62, 63, 64, 69, 70, 73, 74, 77, 78, 81, 82, 87, 88, 92, 93, 97, 98, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123)
If Target.Cells.Count > 1 Or Target.Column < 14 Or Target.Column > 15 Then Exit Sub
temp = Application.Match(Target.Row, ValidArray, 0)
If Not IsNumeric(temp) Then Exit Sub
Cells(Target.Row, 29 - Target.Column).Value = Cells(Target.Row, 12).Value * 1 - Target.Value * 1
End Sub

[mehmetcik]

Please Login or Register  to view this content.

[mehmetcik]

Did you have a macro crash on you?
Sometimes Macros disable "events" this stops other macros running,
If the macro ends without re-enabling "Events" then macros like mine will stop working.
Restarting Excel will reset everything and the macro will run.

Insert this macro into your a macro module.
Select the developer tab on excel, I hope you have one,
If not you will need to enable it. Let me know.

Select visual basic and then select view project explorer

click on your project name in the window that opens, then select insert and then module,
paste this code there and close visual basic.

Run the macro Deoptimise by selecting the developer tab, select Macros

select DeOptimise and then click on run.


Running it should let my macro run again.

Please Login or Register  to view this content.

You could also post your spreadsheet for me to check.

[mehmetcik]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[kittycrickett]

I apologize. I posted a reply (or thought I did anyway) and now its not here. I did get it to work with your help. I sincerely appreciate you!