×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

assigning a function to a cell remotely
3

assigning a function to a cell remotely

assigning a function to a cell remotely

(OP)
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!

RE: assigning a function to a cell remotely

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.

RE: assigning a function to a cell remotely

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: www.essexsteam.co.uk

RE: assigning a function to a cell remotely

(OP)
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!

RE: assigning a function to a cell remotely

(OP)
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!

RE: assigning a function to a cell remotely

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):

CODE

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: www.essexsteam.co.uk

RE: assigning a function to a cell remotely

2
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources