perform multiple actions in an IF

hi all

is there a way to perform multiple actions in an IF statement

ie
=IF(ISBLANK(A1),<set current cell value = "">;<set Cell B1 value = 0>;<set
Cell C1 value = 0>,<set Current cell value = A1+1>;<set cell b1 value =
1>;<set cell c1 value = 1>)

if A1 is blank, then the current cell is 'blank', B1 contains 0, C1 contains 0
else current cell =A1+1, B1 contains 1, C1 contains 1

if that is not possible, is it possible to set the value of another cell to
a certain value (other than the one the formula is in).

tia

J

You would have to link the formulas, so B1, C1 etc. would have formulas that
test A1 or the cell with that formula in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gixxer_J_97" <GixxerJ97@discussions.microsoft.com> wrote in message
news:A3A10EB2-371F-4ED7-9E57-878E0313BDC8@microsoft.com...
> hi all
>
> is there a way to perform multiple actions in an IF statement
>
> ie
> =IF(ISBLANK(A1),<set current cell value = "">;<set Cell B1 value = 0>;<set
> Cell C1 value = 0>,<set Current cell value = A1+1>;<set cell b1 value =
> 1>;<set cell c1 value = 1>)
>
> if A1 is blank, then the current cell is 'blank', B1 contains 0, C1
contains 0
> else current cell =A1+1, B1 contains 1, C1 contains 1
>
> if that is not possible, is it possible to set the value of another cell
to
> a certain value (other than the one the formula is in).
>
> tia
>
> J

To further Bob's expanation, a formula within a cell can only affect the cell
that it is in. So a fromula in A1 can not directly change the value in B1. B1
can however use the value in A1 as an input to detrmine its own value. When
you think about it this makes a lot of sense. Imagine trying to figure out
why the contents of B1 is what it is if any other cell in the spread sheet
could change it.

HTH

"Gixxer_J_97" wrote:

> hi all
>
> is there a way to perform multiple actions in an IF statement
>
> ie
> =IF(ISBLANK(A1),<set current cell value = "">;<set Cell B1 value = 0>;<set
> Cell C1 value = 0>,<set Current cell value = A1+1>;<set cell b1 value =
> 1>;<set cell c1 value = 1>)
>
> if A1 is blank, then the current cell is 'blank', B1 contains 0, C1 contains 0
> else current cell =A1+1, B1 contains 1, C1 contains 1
>
> if that is not possible, is it possible to set the value of another cell to
> a certain value (other than the one the formula is in).
>
> tia
>
> J

true - but in my case this one cell affects the values of 3 cells in total,
the cell it's in, and two other separate cells.

unfortunately the two other cells i have cannot have formulas in them - only
values
maybe if i explain what i'm doing you might have an idea of a direction to
point me

i have three cells
A1, D1 and E1
A1 will either be blank, or contain the line item number (for A1, line item
1, A2, line item 2, etc (but just the number 1,2,etc))
B1 and C1 both are 'occupied' by comboboxes - and linked to D1 and E1
respectively
what i was trying to do was set the values of A1, B1 and C1 based on A1
meaning if A1 is 'blank', then i would set A1 to "" (which is easy) D1 and
E1 both to have a value of 0 - thus 'hiding' the comboboxes in B1 and C1

any way to do this other than vba?



"Jim Thomlinson" wrote:

> To further Bob's expanation, a formula within a cell can only affect the cell
> that it is in. So a fromula in A1 can not directly change the value in B1. B1
> can however use the value in A1 as an input to detrmine its own value. When
> you think about it this makes a lot of sense. Imagine trying to figure out
> why the contents of B1 is what it is if any other cell in the spread sheet
> could change it.
>
> HTH
>
> "Gixxer_J_97" wrote:
>
> > hi all
> >
> > is there a way to perform multiple actions in an IF statement
> >
> > ie
> > =IF(ISBLANK(A1),<set current cell value = "">;<set Cell B1 value = 0>;<set
> > Cell C1 value = 0>,<set Current cell value = A1+1>;<set cell b1 value =
> > 1>;<set cell c1 value = 1>)
> >
> > if A1 is blank, then the current cell is 'blank', B1 contains 0, C1 contains 0
> > else current cell =A1+1, B1 contains 1, C1 contains 1
> >
> > if that is not possible, is it possible to set the value of another cell to
> > a certain value (other than the one the formula is in).
> >
> > tia
> >
> > J

Hi

I am not clear on what you want. Just like you explained that column A has
Part Numbers, can you explain what Columns B, C, D and E have. What is in
combo boxes B and C (specially since I think do not have any data in Columns
B and C)
Note however, some basic facts about formulas in any cell.

1. Cell can have a formula such that it can be dependent on the values of 1
or hundreds of other cells.

2. Other cells value can depend on its own value. Howver, it cannot force
another cell to take a specific value
For instance a nor formula in Cell A1 can force a value of 10 in Cell B1.
Formula in Cell B1 can be =5*A1 and when A1 is 2, Cell B1 will automatically
become 10.

3. In a similar vein to 2 above, no cell formula can directly change the
environment. For instance it cannot hide a column, change the column width,
change the color of another cell etc.

Alok


"Gixxer_J_97" wrote:

> true - but in my case this one cell affects the values of 3 cells in total,
> the cell it's in, and two other separate cells.
>
> unfortunately the two other cells i have cannot have formulas in them - only
> values
> maybe if i explain what i'm doing you might have an idea of a direction to
> point me
>
> i have three cells
> A1, D1 and E1
> A1 will either be blank, or contain the line item number (for A1, line item
> 1, A2, line item 2, etc (but just the number 1,2,etc))
> B1 and C1 both are 'occupied' by comboboxes - and linked to D1 and E1
> respectively
> what i was trying to do was set the values of A1, B1 and C1 based on A1
> meaning if A1 is 'blank', then i would set A1 to "" (which is easy) D1 and
> E1 both to have a value of 0 - thus 'hiding' the comboboxes in B1 and C1
>
> any way to do this other than vba?
>
>
>
> "Jim Thomlinson" wrote:
>
> > To further Bob's expanation, a formula within a cell can only affect the cell
> > that it is in. So a fromula in A1 can not directly change the value in B1. B1
> > can however use the value in A1 as an input to detrmine its own value. When
> > you think about it this makes a lot of sense. Imagine trying to figure out
> > why the contents of B1 is what it is if any other cell in the spread sheet
> > could change it.
> >
> > HTH
> >
> > "Gixxer_J_97" wrote:
> >
> > > hi all
> > >
> > > is there a way to perform multiple actions in an IF statement
> > >
> > > ie
> > > =IF(ISBLANK(A1),<set current cell value = "">;<set Cell B1 value = 0>;<set
> > > Cell C1 value = 0>,<set Current cell value = A1+1>;<set cell b1 value =
> > > 1>;<set cell c1 value = 1>)
> > >
> > > if A1 is blank, then the current cell is 'blank', B1 contains 0, C1 contains 0
> > > else current cell =A1+1, B1 contains 1, C1 contains 1
> > >
> > > if that is not possible, is it possible to set the value of another cell to
> > > a certain value (other than the one the formula is in).
> > >
> > > tia
> > >
> > > J

by "current cell" I assume you mean the currently selected cell.
Try the following code:

Option Explicit

Sub Test()

SetCells Range("A1")

End Sub
Sub SetCells(source As Range)
Select Case True
Case source.Value = ""
source.Offset(0, 1) = 0
source.Offset(0, 2) = 0
Selection = ""
Case IsNumeric(source.Value)
source.Offset(0, 1) = 1
source.Offset(0, 2) = 1
Selection = source.Value + 1
Case Else
End Select
End Sub




"Gixxer_J_97" wrote:

> hi all
>
> is there a way to perform multiple actions in an IF statement
>
> ie
> =IF(ISBLANK(A1),<set current cell value = "">;<set Cell B1 value = 0>;<set
> Cell C1 value = 0>,<set Current cell value = A1+1>;<set cell b1 value =
> 1>;<set cell c1 value = 1>)
>
> if A1 is blank, then the current cell is 'blank', B1 contains 0, C1 contains 0
> else current cell =A1+1, B1 contains 1, C1 contains 1
>
> if that is not possible, is it possible to set the value of another cell to
> a certain value (other than the one the formula is in).
>
> tia
>
> J

Hi
The data i gave was for simplicity - my actual implementation is a little
more complex
Ok, specifically -
A1 contains the formula
=IF(ISBLANK(B1),"",1)
B1 will contain an item code - chosen from a data validation box (text only)
C1 and D1 will physically contain no data, but will 'host' a form control
combo box
E1 will be the link cell for the combo box that 'exists' in C1
F1 will be the link cell for the combo box that 'exists' in D1

the combo boxes that are 'in' C1 and D1 contain text that the user will
chose - and then E1 and F1 will be updated with the selected indecies of the
corresponding combo box.

i was hoping that i could 'hide' (set the index to 0) the combo boxes in C1
and D1 based on the value of A1 (or B1 being blank)

if i put a formula in E1 or F1, as soon as i change the value of the
corresponding combo box, the formula gets overwritten by the selected index.
this is why i was hoping to have another cell 'force' a value

i'm thinking that VBA may be the only option here - and it's something i've
implemented before - i was just hoping to stay away from vba as much as
possible and only use it where needed. apparently it's needed here if i want
to do it this way =)

thanks!

J

"Alok" wrote:

> Hi
>
> I am not clear on what you want. Just like you explained that column A has
> Part Numbers, can you explain what Columns B, C, D and E have. What is in
> combo boxes B and C (specially since I think do not have any data in Columns
> B and C)
> Note however, some basic facts about formulas in any cell.
>
> 1. Cell can have a formula such that it can be dependent on the values of 1
> or hundreds of other cells.
>
> 2. Other cells value can depend on its own value. Howver, it cannot force
> another cell to take a specific value
> For instance a nor formula in Cell A1 can force a value of 10 in Cell B1.
> Formula in Cell B1 can be =5*A1 and when A1 is 2, Cell B1 will automatically
> become 10.
>
> 3. In a similar vein to 2 above, no cell formula can directly change the
> environment. For instance it cannot hide a column, change the column width,
> change the color of another cell etc.
>
> Alok
>
>
> "Gixxer_J_97" wrote:
>
> > true - but in my case this one cell affects the values of 3 cells in total,
> > the cell it's in, and two other separate cells.
> >
> > unfortunately the two other cells i have cannot have formulas in them - only
> > values
> > maybe if i explain what i'm doing you might have an idea of a direction to
> > point me
> >
> > i have three cells
> > A1, D1 and E1
> > A1 will either be blank, or contain the line item number (for A1, line item
> > 1, A2, line item 2, etc (but just the number 1,2,etc))
> > B1 and C1 both are 'occupied' by comboboxes - and linked to D1 and E1
> > respectively
> > what i was trying to do was set the values of A1, B1 and C1 based on A1
> > meaning if A1 is 'blank', then i would set A1 to "" (which is easy) D1 and
> > E1 both to have a value of 0 - thus 'hiding' the comboboxes in B1 and C1
> >
> > any way to do this other than vba?
> >
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > To further Bob's expanation, a formula within a cell can only affect the cell
> > > that it is in. So a fromula in A1 can not directly change the value in B1. B1
> > > can however use the value in A1 as an input to detrmine its own value. When
> > > you think about it this makes a lot of sense. Imagine trying to figure out
> > > why the contents of B1 is what it is if any other cell in the spread sheet
> > > could change it.
> > >
> > > HTH
> > >
> > > "Gixxer_J_97" wrote:
> > >
> > > > hi all
> > > >
> > > > is there a way to perform multiple actions in an IF statement
> > > >
> > > > ie
> > > > =IF(ISBLANK(A1),<set current cell value = "">;<set Cell B1 value = 0>;<set
> > > > Cell C1 value = 0>,<set Current cell value = A1+1>;<set cell b1 value =
> > > > 1>;<set cell c1 value = 1>)
> > > >
> > > > if A1 is blank, then the current cell is 'blank', B1 contains 0, C1 contains 0
> > > > else current cell =A1+1, B1 contains 1, C1 contains 1
> > > >
> > > > if that is not possible, is it possible to set the value of another cell to
> > > > a certain value (other than the one the formula is in).
> > > >
> > > > tia
> > > >
> > > > J

current cell was the cell that the 'if' statement was in

i think your option may be the best way to go

last time i used the Worksheet_Change(ByVal Target as Range)
and checking for target being in column 1, and from rows 1 to 20
and then setting the values of the cells i wanted based on that




"Patrick Molloy" wrote:

> by "current cell" I assume you mean the currently selected cell.
> Try the following code:
>
> Option Explicit
>
> Sub Test()
>
> SetCells Range("A1")
>
> End Sub
> Sub SetCells(source As Range)
> Select Case True
> Case source.Value = ""
> source.Offset(0, 1) = 0
> source.Offset(0, 2) = 0
> Selection = ""
> Case IsNumeric(source.Value)
> source.Offset(0, 1) = 1
> source.Offset(0, 2) = 1
> Selection = source.Value + 1
> Case Else
> End Select
> End Sub
>
>
>
>
> "Gixxer_J_97" wrote:
>
> > hi all
> >
> > is there a way to perform multiple actions in an IF statement
> >
> > ie
> > =IF(ISBLANK(A1),<set current cell value = "">;<set Cell B1 value = 0>;<set
> > Cell C1 value = 0>,<set Current cell value = A1+1>;<set cell b1 value =
> > 1>;<set cell c1 value = 1>)
> >
> > if A1 is blank, then the current cell is 'blank', B1 contains 0, C1 contains 0
> > else current cell =A1+1, B1 contains 1, C1 contains 1
> >
> > if that is not possible, is it possible to set the value of another cell to
> > a certain value (other than the one the formula is in).
> >
> > tia
> >
> > J