This is the only way I know how to do this...without copy/pasting values or running a circular reference.
Data validation ...dropdown lists that consist of cells containing formulas, even if they change, does not invalidate the existing value in the cell with the dropdown box
For a simple illustration:
Cell A2 contains a formula. Cell A2 you want to be a locked/protected cell that another user can't double click or F2 into
Suppose in Cell A1, you want to refer to the value currently given in Cell A2. But you want Cell A1 to maintain the hardcoded value it got from current cell A2, even after the value in Cell A2 is to change.
but since A2 is protected, you can't have a person copy Cell A2/ paste values into Cell A1
Here's what I discovered
You can give Cell A2 a range name or simply
Then set Cell A1 to "data validation...list =A2
Then you have a dropdown box for Cell A1 with one number to select from. That number is of course the number in A2. However, this isn't the same as referencing cell A2. This allows you to reference the value in the other cell at the time you use the dropdown box, even if A2 is a protected cell. So A3 can be for instance, =A2-A1
Basically for whatever reason, using data validation lists based on formula cells does not mean that the value in the data validation cell becomes invalidated, even if it's no longer a valid option in the list.
What this means is I can time stamp, or create a buy/sell stock portfolio program, or a track something at a specific point in time with the NOW() formula, without having to mess with copying and pasting values.
With a lot of these, you can dodge circular references
Whete sometimes you want to use X as a predicter of Y, and other times you want to use Y as a predicter of X, and by having 2 drop-down boxes and 2 formulas....you can dodge the circular reference complication entirely
Data validation ...dropdown lists that consist of cells containing formulas, even if they change, does not invalidate the existing value in the cell with the dropdown box
For a simple illustration:
Cell A2 contains a formula. Cell A2 you want to be a locked/protected cell that another user can't double click or F2 into
Suppose in Cell A1, you want to refer to the value currently given in Cell A2. But you want Cell A1 to maintain the hardcoded value it got from current cell A2, even after the value in Cell A2 is to change.
but since A2 is protected, you can't have a person copy Cell A2/ paste values into Cell A1
Here's what I discovered
You can give Cell A2 a range name or simply
Then set Cell A1 to "data validation...list =A2
Then you have a dropdown box for Cell A1 with one number to select from. That number is of course the number in A2. However, this isn't the same as referencing cell A2. This allows you to reference the value in the other cell at the time you use the dropdown box, even if A2 is a protected cell. So A3 can be for instance, =A2-A1
Basically for whatever reason, using data validation lists based on formula cells does not mean that the value in the data validation cell becomes invalidated, even if it's no longer a valid option in the list.
What this means is I can time stamp, or create a buy/sell stock portfolio program, or a track something at a specific point in time with the NOW() formula, without having to mess with copying and pasting values.
With a lot of these, you can dodge circular references
Whete sometimes you want to use X as a predicter of Y, and other times you want to use Y as a predicter of X, and by having 2 drop-down boxes and 2 formulas....you can dodge the circular reference complication entirely
An MS Excel trick I just discovered
0 commentaires:
Enregistrer un commentaire