0

Alright so I looked through for other solutions but I didn't get anything close enough with my limited knowledge to make it work so I hoping some geniuses here can help.

Basically I am using excel to autoupdate some data based on the value of another cell. A simplified version of my table looks like the below:

ID            Step       Count
526985       - Step 1      8
123569       + Step 3      3
589745       - Not in AMP  1
589465       + Step 2      5
  1. IDs are unique and always 6 digits (just fyi if that helps anything). There will never be a Step column or count column value without an ID value
  2. I would like to use the change val in vba so it changes as I go along automatically
  3. The goal is for the user to not have to update manually the value in the "Count" column
  4. When the user starts working on the sheet, the "Step" column will be blank and will be selected from a drop down menu but the "Count" and "ID" will be populated already

What I need:

  1. When a value of "+ Step 1", "+ Step 2", "+ Step 3", "+ Step 3 ext", "- Step 2", "- Step 1" is selected in the "Step" column for an ID, I need "+1" added to whatever the current value is in the "count" column
  2. When a value of "- Not in AMP" is selected from the "Step" column, I need the value to be 0 in the "Count" column
  3. There will be other values selectable from the "Step" column which I need to be ignored (Keep the same "Count" column value)
  4. After a step value has been selected in the "Step" column and the "count" column has been updated. I still need to be able to go back and change that value to any other number manually.

I think that's about it. I thought of using formulas which I could do but the issue is where I need to be able to overwrite the value with another, it will delete the formula. I'm open to anything that makes this work though. Thank you in advance!

Community
  • 1
  • 1
veneratio
  • 1
  • 1
  • 1
    If you can do it with formulas then I am wondering why you posted the question. If not, then you'll probably have to look into VBA: it seems to me that you will have to capture the `Change` event on that sheet in case "someone is overwriting" your formulas with data. For more information on that topic you might want to look at the following answer: http://stackoverflow.com/questions/30513655/using-vba-how-do-i-detect-when-any-value-in-a-worksheet-changes Let us know once you are running into a specific coding error so we can offer more specific help. – Ralph Feb 17 '16 at 00:11
  • With the number of variables and conditions, would it not be better to use a VBA script? I'm not very knowledgeable in this field and because there is a new sheet updated quarterly based on the results of the previous quarter's sheet, I didn't want to have formulas but rather actual results. That way they each stay independant of each other rather than having formula results based on a multitude of previous formula results. – veneratio Feb 17 '16 at 15:42
  • 1
    A good starting point to get acquainted with VBA might be the following site: http://www.homeandlearn.org/. Together with the above referenced solution you should have all that you need to come up with a VBA solution. Let us know if you run into a specific VBA error while writing the solution (in which case you should also post all of the VBA code you have written thus far). – Ralph Feb 17 '16 at 15:49
  • "I hoping some geniuses here can help." <-- a very nice credit indeed.. ( : – p._phidot_ Jul 31 '18 at 20:00
  • Dear OP, if you really get stuck with vba.. I'll recommend to use a 'hidden' column/sheet with formula instead. – p._phidot_ Jul 31 '18 at 20:19

1 Answers1

1

After you have a Change event you could have some logic to check: - if user is adding a new value in the correct column, you would load the previous data into a variant to perform the logic that you have given to populate the addition cells - if not, let the user update the values.

Justin
  • 297
  • 1
  • 2
  • 10