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
- 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
- I would like to use the change val in vba so it changes as I go along automatically
- The goal is for the user to not have to update manually the value in the "Count" column
- 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:
- 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
- When a value of "- Not in AMP" is selected from the "Step" column, I need the value to be 0 in the "Count" column
- There will be other values selectable from the "Step" column which I need to be ignored (Keep the same "Count" column value)
- 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!
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.