Dynanic Worksheet functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am fairly clued up with VB, but want to try cut down using VB when
designing spreadsheets... Is it possible to have a function (e.g. If
function) post a result into an alternative cell dependant an the value in
the 'input' cell?
i.e. If A1 = X value then B2 = Y value
 
Sure. In B2 put a formula like =IF(A1=5,"yes","no") if you want cell B2 to
be yes if a1 = 5, else no. Also, =IF(A1=5,10,15) if you want B2 to be 10 if
A1 = 5, else 15. Also, =IF(A1=5,C23,D14) if you want B2 to be the value in
C23 if A1 = 5, else the value in D14. Etc.
 
In B2 enter =IF(A1="x", "y")

If many of these, perhaps a VLOOKUP table and a VLOOKUP formula in B2 would
suffice?

Of simply =LOOKUP(A1,I1:I10,J1:J10) where I1:I10 hold the "x" values and
J1:J10 hold the "y" values.


Gord Dibben MS Excel MVP
 
I want to try something like IF(A1="Y",B2=C2,B2=D2), but I get FALSE in the
'input cell.. not exact formula being used, but I hope you get the gist
 
I think I confused everyone when I mentioned Y & V values... I want to try
something like IF(A1="Y",B2=C2,B2=D2), but I get FALSE in the
'input cell.. not exact formula being used, but I hope you get the gist
 
=IF(A1="Y",C2,D2). Excel evaluates this as: if a1 = "y", return the value in
cell C2 else return the value in cell D2.

If you use =IF(A1="Y",B2=C2,B2=D2) Excel evaluates this as: if a1 ="y",.
then evaluate b2=c2 (true, false?) and if a1 is not = "y", then evaluate
b2=d2 (true, false?) and returns the appropriate true or false.
 
=IF(A1="Y",C2,D2). Excel evaluates this as: if a1 = "y", return the value in
cell C2 else return the value in cell D2.

If you use =IF(A1="Y",B2=C2,B2=D2) Excel evaluates this as: if a1 ="y",.
then evaluate b2=c2 (true, false?) and if a1 is not = "y", then evaluate
b2=d2 (true, false?) and returns the appropriate true or false.
 
Thanks Tyro...
That is logical but a pity, because if you could have returned a result from
one of the cells as apposed to the 'True / False', it would have meant that I
could iterally program from within excel without the downfalls of sending
excel spreadsheets to my users with VBA modules / macros..
 
Show me exactly what you want. Show me the "input" cell and the cell where
you want the result to be and the values that you want the result to be and
where the values are .
 
It sounds like what you're wanting to do is "push" the result of the formula
to cell B2. That can't be done with formulas. A formula can only "pull" a
result to the cell that contains the formula.
 
For various reasons, I want to hide the formula from the 'destination' cell
and rather 'send' the value... Thanks anyway
 
Hi Tyro,
I am actually trying to produce a ‘program’ that my users can use to select
data from a dropdown box. The dropdown box will put a result into a cell
which will then used in a vlookup function nested within an if function to
‘push’ data into other cells. For various reasons, I don’t want the
destination cells to have formulas… I can do it using VBA, but mentioned
earlier wanted to try and do without VBA.

Biff, the microsoft Excel MVP advises that this can’t be done… Thanks for
your help anyway.
 
Entered in B2

=IF(A1="y",C2,D2)

Other than that......yes, I am confused.

Perhaps as Biff mentions, you are trying to push rather than pull.

Which cell has the formula entered?


Gord
 
You should have stated originally what you were trying to do, rather than
wasting our time and effort.
 
To be fair - his original post asked
"Is it possible to have a function (e.g. If function) post a result into an
alternative cell"
 
Back
Top