if then, change value of a cell range
 

is this possible? or some other syntax to make this work

=IF(A6="WE",A11:A24=" ","")

if A6 = “WE” then a ceartain column range is cleared of its contents, if not content stays as is

help is greatly approciated.

thanks.

Hi,

if it is possible for you to use macros in your workbook, then this should
solve your broblem.

1 ) Open the workbook ( or a copy of it ) that you are intend to modify

2 ) Press ALT + F11

3 ) at left part of window, doubleclick that worksheet that contains the
cells A6 and A11:A24 that you mentioned

4 ) paste the following code to the right part of VBE-window


Private Sub Worksheet_Change(ByVal Target As Range)

Dim SomeText As String

SomeText = UCase(Range("A6").Value)

If (SomeText) = "WE" Then
Range("A11:A24").ClearContents
End If

End Sub



5) save the file

6) shut down the Microsoft Visual Basic - window

7) you dont have to do anything else to get this work


Now, always when something changes at this particular worksheet, for example
when a value of any cell changes this macro will execute. What it then does ?

It checks if the value cell A6 is WE, We, wE or we and if that is the case
then clears contents from A11:A24. Else it wont do anything.


I don't know how your goal could be possible to accomplish by ordinary worksheetformulas.
Thats because a cell can contain text, date, formula etc. and if a cell at area
A11:A24 contains for example a timevalue, it can't at same time contain some permanent formula.



This macro does the following things :

Defines a variable named SomeText, which can contain any string-value

Takes value to SomeText-variable from cell A6

Checks if the value is some of these ( We, we, wE or WE )

if it is the contents from cells A11:A24 will be cleared immediately

if the content of cell A6 is something else nothing happens, only
the execution of macro will be stopped.


It is important that you save the code to the right place. It wont work
from module part of project.

***