Dynanic Worksheet functions
 

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

Dynanic Worksheet functions
 

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.


"Les G" wrote in message
...
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

Dynanic Worksheet functions
 

Did you look in the help index for IF?
in cell b2
=if(a1="x","Y","")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Les G" wrote in message
...
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

Dynanic Worksheet functions
 

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


On Sat, 10 Nov 2007 10:55:00 -0800, Les G
wrote:

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

Dynanic Worksheet functions
 

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

"Don Guillett" wrote:

Did you look in the help index for IF?
in cell b2
=if(a1="x","Y","")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Les G" wrote in message
...
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

Dynanic Worksheet functions
 

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



"Gord Dibben" wrote:

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


On Sat, 10 Nov 2007 10:55:00 -0800, Les G
wrote:

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

Dynanic Worksheet functions
 

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

"Les G" wrote in message
...
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

"Don Guillett" wrote:

Did you look in the help index for IF?
in cell b2
=if(a1="x","Y","")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Les G" wrote in message
...
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

Dynanic Worksheet functions
 

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


"Les G" wrote in message
...
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



"Gord Dibben" wrote:

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


On Sat, 10 Nov 2007 10:55:00 -0800, Les G

wrote:

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

Dynanic Worksheet functions
 

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

"Tyro" wrote:

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

"Les G" wrote in message
...
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

"Don Guillett" wrote:

Did you look in the help index for IF?
in cell b2
=if(a1="x","Y","")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Les G" wrote in message
...
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

Dynanic Worksheet functions
 

You can do just that. Put the formula in the cell where you want the answer
to be!!!!

"Les G" wrote in message
...
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..

"Tyro" wrote:

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

"Les G" wrote in message
...
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

"Don Guillett" wrote:

Did you look in the help index for IF?
in cell b2
=if(a1="x","Y","")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Les G" wrote in message
...
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

Dynanic Worksheet functions
 

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 .

"Les G" wrote in message
...
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..

"Tyro" wrote:

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

"Les G" wrote in message
...
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

"Don Guillett" wrote:

Did you look in the help index for IF?
in cell b2
=if(a1="x","Y","")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Les G" wrote in message
...
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

Dynanic Worksheet functions
 

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.

--
Biff
Microsoft Excel MVP


"Les G" wrote in message
...
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



"Gord Dibben" wrote:

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


On Sat, 10 Nov 2007 10:55:00 -0800, Les G

wrote:

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

Dynanic Worksheet functions
 

For various reasons, I want to hide the formula from the 'destination' cell
and rather 'send' the value... Thanks anyway

"Tyro" wrote:

You can do just that. Put the formula in the cell where you want the answer
to be!!!!

"Les G" wrote in message
...
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..

"Tyro" wrote:

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

"Les G" wrote in message
...
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

"Don Guillett" wrote:

Did you look in the help index for IF?
in cell b2
=if(a1="x","Y","")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Les G" wrote in message
...
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

Dynamic Worksheet functions
 

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 dont 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 cant be done€¦ Thanks for
your help anyway.

Dynanic Worksheet functions
 

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

On Sat, 10 Nov 2007 11:39:00 -0800, Les G
wrote:

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



"Gord Dibben" wrote:

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


On Sat, 10 Nov 2007 10:55:00 -0800, Les G
wrote:

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

Dynamic Worksheet functions
 

You should have stated originally what you were trying to do, rather than
wasting our time and effort.

"Les G" wrote in message
...
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.

Dynamic Worksheet functions
 

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"



"Tyro" wrote:

You should have stated originally what you were trying to do, rather than
wasting our time and effort.

"Les G" wrote in message
...
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.