assigning a function to a cell remotely 3

[jinx2311]

Hi All,

I have a cell (say, A1) which receives input from user, a cell B1 which should compute/display a value based on the input in A1. If I write a condition or formula for this in B1, its gets over-written once I input a value in A1 and correspondingly B1 takes the desired value. So I cannot re-use the condition for another input in A1.

As suggested by someone previously I tried to link the function in B1 to a different cell C1, but I am not sure if I am doing it correctly. If someone could take a look below and help me out, it would be great!

C1=IF(A1=1,B1=2,B1=4)

Can I do this?If yes, how? As of now, it gives me a true or false value in C1 and nothing in B1, when I input value in A1.

Please help!Thanks in advance!

 

[electricpete]

It's not clear what you're trying to do.

Note the syntax of the IF function:
IF(logical_test,value_if_true,value_if_false)

The first argument should be boolean (such as your A1=1)

The function returns either the 2nd or 3rd arguments, based on results of evaluation of the boolean in the first position.

In your case, you have provided boolean expressions in 2nd and 3rd position, so the function returns a boolean result (True or false).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[johnwm]

Just put this into B1:
=IF(A1=1,2,4)

B1 will display 2 if A1 contains 1, else it will contain 4. It won't overwrite the formula.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[jinx2311]

Hi Electricpete,

If I write just a value in the 2nd and 3rd arguement, by default the formula will be set up for C1....which is not what I am looking to do. I want to write a formula for B1 in cell C1.

So I dont know if I am on the correct path the way I am doing it. Do you see any alternative or any way to do it at all?

Thanks for the reply!

 

[jinx2311]

Hey guys,

I am sorry, I left out a crucial part in my dilema....cell B1 is also an input cell....it displays selected list based on input in A1. The values 2 and 4 in arguements are just examples. I have drop down lists in B1 which will get displayed only if it satisfies the condition wrt value in A1.And user selects values from that particular list in B1. So once that value is selected, if there is a formula in B1 to display the lists, its getting over written when the user selects value in B1 from the list.

Am I making any sense at all?

Once again sorry for the confusion!

 

[johnwm]

You could put a Combobox control over Cell B1, then use this code, which assumes the two lists of values are in J1:J3 (option1) and K1:K3 (option2):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Cells(1, 1) Then
If Cells(1, 1).Value = 1 Then
ComboBox2.ListFillRange = "J1:J3"
Else
ComboBox2.ListFillRange = "K1:K3"
End If
End If
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[joerd]

You can also look into Data Validation (I think I have posted this before, but I'll repeat myself here).
So, for example, you can set up a "dynamic" list in column C that contains the values you want to display, based on the value in cell A1. So for example cell C1 contains the formula =IF(A1=1,1,2) and C2 contains =IF(A1=1,3,4), etc.
Then you setup data validation in cell B1 (from the menu, choose Data/Validation), and select Allow: List. Set the Source of the list to =$C$1:$C$2, and you're done!
Of course this is just an example, have fun experimenting. One more tip: it usually is a good idea to define a name for your list source, and then refer to the name in the validation definition. This will also allow you to put the list source on a different worksheet.
You can also implement lookups and dynamic ranges to make everything even more flexible, but that would be a whole different topic.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.