Solved

If function that enters values in two cells

Posted on 2011-02-16
13
254 Views
Last Modified: 2012-05-11
Dear all,

I wonder if this is feasible:

An "If" function, where not only the formula cell gets an entry but another cell also at the same time.

I have attached a sample file for your convenience.

Help is much apprecitated. Thank you very much in advance.

Regards, Andreas
 Formula.xls
0
Comment
Question by:AndreasHermle
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 50 total points
ID: 34906538
Hello Andreas,

It's not possible to change the contents of C2 through a formula in B2 - if you want to use formulas then you'll need one in each cell, why don't you want to do that?

regards, barry
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 34906539
It is not possible to do that from a single formula in B2

you would only be able to do this is you ran a macro.
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 34906543
or as Barry says, havign formula in both B and C!
0
 

Author Comment

by:AndreasHermle
ID: 34906913
Great, thank you very much for your quick support.

Ok, that is what I also thought. I might get back and ask for a macro solution.

Kind regards, Andreas
0
 
LVL 81

Accepted Solution

by:
byundt earned 450 total points
ID: 34906914
It is possible to enter an array formula in cells B2 and C2 that will change both of them together:
=IF(A2=1,{"OK","Please register"},{"",""})

1) Select both cells B2 and C2
2) Click in the formula bar
3) Paste the suggested formula there
4) Hold the Control and Shift keys down, then hit Enter
5) Release all three keys. Excel will respond by array-entering the formula and surrounding it with curly braces { }.

If you see the formula only in one cell, or if there are no curly braces, then repeat steps 1 through 5.

If you have a lot of these formulas, then array entering them may save some memory and improve calculation time. If you don't have a lot of them, it is less confusing for most people to use regular formulas--one in cell B2 and the other in C2, just as barryhoudini and Runrigger have been suggesting.
=IF($A2=1,"OK","")          formula for B2. The $ lets you copy the formula across and change the text in the adjacent cells.
=IF($A2=1,"Please register","")         formula for C2. The "" makes the cell look like a blank if anything other than a 1 is in cell A2.

Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 34906927
I meant to attach this sample workbook showing the array formula entered in cells B3 and C3.
FormulaQ26825283.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:AndreasHermle
ID: 34906933
Brad,

oops, I was maybe  a millisecond quicker with my feedback. Ok, I will give this a try and let you know. I will not get back to you before tomorrow.

thank you very much.

Regards, Andreas
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 34906943
Andreas, this goes back to Barry's suggestion of you entering formula in two cells!
0
 

Author Comment

by:AndreasHermle
ID: 34918434
Hi Brad,

will need the weekend to try it out. Will get back with a feedback afterwards. Thank you again to all for the overwhelming support.

Regards, Andreas
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 450 total points
ID: 34918601
Andreas,
If the desired text is taken from cells (rather than embedded in the formula), you might use an array formula like:
=IF(A2=1,$K$2:$L$2,{"",""})                       'If A2 is 1, return text from cells K2 and L2; otherwise return empty strings (look like a blank)

This formula is still array-entered (Control + Shift + Enter). And you must select both target cells (B2 and C2 in your question) before performing the array entry.

Brad
0
 

Author Closing Comment

by:AndreasHermle
ID: 34939090
Hi Brad,

this is great stuff. It is working just fine. I awarded 50 points to Barry as well because he was the quickest to answer with essentially another right answer. But I really like your approach. This forum really deserves  its name.

Regards and thank you again  for  your professional help.

Andreas.
0
 
LVL 81

Expert Comment

by:byundt
ID: 34939170
Andreas,
In the simple example you posted in the question, most people would be quite satisfied with two regular formulas.

Where the array entry and return of values comes into play is when you have a function or calculation that really needs to return multiple values, such as LINEST or a user-defined function returning an array. It is in these latter cases that there can be a considerable savings in time. To illustrate, in the snowflake counting problem described in my screen profile, I originally returned values one at a time from user-defined functions. Switching to return of an entire array cut the recalculation time from about five minutes to a couple of seconds. This was because the results needed to be calculated just once by the user-defined function, rather than once for each cell. Since I was returning results to 13 column s x 24 rows (312 cells) using a VBA user-defined function, you can see the benefits to returning all the values after performing a single calculation.

Brad
0
 

Author Comment

by:AndreasHermle
ID: 34943599
Hi Brad,

thank you very much for your insight into this professional matter. This array formula really can come in handy for me. It is incredible how this array formula can cut computing time so considerably in certain circumstances.

Regards, Andreas
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel VBA - Public declaration of variables 3 46
Vb.net dynamic formulas in runtime 11 61
How do I crate a Pivot table in Excel 2 4
excel help 4 18
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now