Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
perform multiple actions in an IF
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
perform multiple actions in an IF
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
perform multiple actions in an IF
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
perform multiple actions in an IF
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
perform multiple actions in an IF
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
perform multiple actions in an IF
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
perform multiple actions in an IF
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macros To Perform Multiple Averages | Excel Worksheet Functions | |||
Code to Look for Text and then Perform Specified Actions | Excel Discussion (Misc queries) | |||
Undo multiple actions | Excel Discussion (Misc queries) | |||
How to perform two actions ? | Excel Worksheet Functions | |||
Performing actions on multiple worksheets | Excel Programming |