Open the ‘Find and Replace’ window in Excel (shortcut’Ctrl + F’), search for a string (close the window), and then search for the same string again (‘Find Next’) is ‘Shift + F4’.
Double Data Validation created only with the Excel function
When I looked on the Internet for a way to conditionally change the “data validation-list” where only predetermined values can be selected in a cell, there were posts called “double validation” with images and formulas. Thank you.
Looking at these posts, I created the formula below to define only the names (myreg1, myreg2, myreg3) and just copy the formula so that you can use it immediately.
- Enter value
- Enter values for ‘condition’ in the first row
- Input values for each condition from the second row
- Define the required name
- Define a name (‘myreg1’ in this case) in the first row of values
- Put the following formula in ‘Refers to’ and define ‘myreg2’
- =OFFSET(OFFSET(myreg1, 0, -1), 1, MATCH(INDIRECT(“RC[-1]”, 0), myreg1, 0), 100, 1)
- In the above formula, bolded ‘100’ is the maximum number of rows(You can change).
- Put the following formula in ‘Refer to’ and define ‘myreg3’
- myreg1, 0, -1), 1, MATCH(INDIRECT(“RC[-1]“, 0), myreg1, 0), COUNTA(myreg2), 1)
- Apply data validation
- Put “=myreg1” in the ‘Data Validation-List’ source in any cell and confirm
- Put “=myreg3” in the source of ‘Data Validation-List’ in the right cell and confirm(You can apply)