At 14:38 18/05/2015 +0100, Derek Day wrote:
I want to create a formula with the following criteria
If cell "A1" contains a certain word, say"water", put the value "4W"
in cell "B1"
The SEARCH() function will seek out text in a cell. The result is the
position in a string where the text starts; if it fails, the result
is error 519 (so the cell shows "#VALUE!"). The ISNUMBER() function
will return TRUE for a number and FALSE for this error, so
ISNUMBER(SEARCH("water";A1)) will be TRUE is the text is found and
FALSE otherwise. In B1, enter:
=IF(ISNUMBER(SEARCH("water";A1));"4W";"")
If cell "A1" does not contain the word "water" check if A1 contains
the word "fire" and if so put the value "4F" in cell "B1"
In B1, enter:
=IF(ISNUMBER(SEARCH("water";A1));"4W";IF(ISNUMBER(SEARCH("fire";A1));"4F";""))
Note that the SEARCH() function is not case-sensitive, so it will
also find "wAtEr" and "Fire" and so on. If you need a case-sensitive
test, use the FIND() function in place of SEARCH().
If cell "A1" does not contain any of the words above allow a manual
entry of my choice in cell "B1"
Have a human being - you'll do - look at cell B1 and type something
into it if it appears empty! You could even change the formula to:
=IF(ISNUMBER(SEARCH("water";A1));"4W";IF(ISNUMBER(SEARCH("fire";A1));"4F";"Type
here!"))
Note that this will overwrite the formula in the cell, so it will no
longer respond correctly if A1 is changed. If you want anything
cleverer than this, you want something other than a spreadsheet.
Also is there a way one can make a formula refer to a complete
spreadsheet rather than copying the formula into each cell every
time you make a new entry.
I suspect you don't mean "refer to" but rather "appear in" a number
of cells. (And a spreadsheet with the same thing in every cell would
be rather useless.) Yes: you can copy and paste a formula into
multiple cells at once or you can "fill" it down a column or
whatever. See any beginner's guide to spreadsheets or Chapter 2 of
the Calc Guide.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
