LO 6, Mojave
I want to set up a formula/etc in a given cell that states that, when a number is copy/pasted in from an external source, if the number within the cell is outside of a given range of numbers, display nothing, but if within the range, display the original number. This will serve to omit all results outside of the specified range.
For the purposes of this question, let’s say the cell is A1 and the range is 1-5.
Which approach is recommended in this instance?
Thanks as always.
Many will find your question simple and will recommend the classic version with the IF() function. Something like
=IF(AND(A1>=1;A1<=5);A1;"")
But not so simple.
This formula must be located in some other cell, otherwise “when a number is copy/pasted in from an external source” the formula will simply be erased by the inserted value and will stop working.
Yes, this approach can be used in this way: create two sheets in your spreadsheet, for example, “Raw Data” and “Clear Data”. Fill in the cells of the “Clear data” sheet with the same formulas, just refer to the corresponding cells in the “Raw data” sheet. The formula will get a little longer. For example, in cell A1 there will be a formula
=IF(AND($'Raw Data'.A1>=1;$'Raw Data'.A1<=5);$'Raw Data'.A1;"")
Now paste the data copied from an external source into the Raw Data sheet and use the cleared data for further work.
This will work, but it is cumbersome and not quite what you wanted when you asked this question.
Another approach that is usually offered in similar situations is the use of Data Validation.
Set criteria and response to erroneous data
This will also work, but only for keyboard input. When you use the insert, this setting will also be destroyed.
Therefore, this method also does not answer your question.
Perhaps closest to your requirement would be Conditional Formatting. Inserting data into a cell will not destroy this condition; it will continue to work. But Conditional Formatting will not clear the erroneous data, it will simply show the user cells that do not match the condition.
The most accurate answer to your question would be “Use a macro to clear data”
This is the most flexible and at the same time the most time-consuming way. On the one hand, you can describe the reaction of the program to any situation - display a message in a pop-up window, change the inserted data, cross-check various cells and so on. On the other hand, all this needs to be written in the form of a program, debugged, configured to automatically start it at the right time or for certain events. This requires certain knowledge and skills. But it will work best. IMHO.
If you decide to choose this path, then I recommend that you have at hand the book of Andrew Pitonyak - Useful Macro Information For OpenOffice.org There are many ready-made solutions for many tasks, I highly recommend it.
