Formula to write a value into a different cell.

[Mr. Clean]

I need a formula in one cell that, under certain conditions (and IF
statement), writes the value of one cell into another cell. In this case, I
can't use a macro. Anyone done this sort of thing before?

[Jim Thomlinson]

=if(B2>10, C2, D2)

This formula (placed in A2 or ???) will look at the value in Cell B2. If B2
is greater than 10 then it returns the value of C2 else it returns the value
of D2...
--
HTH...

Jim Thomlinson


"Mr. Clean" wrote:

> I need a formula in one cell that, under certain conditions (and IF
> statement), writes the value of one cell into another cell. In this case, I
> can't use a macro. Anyone done this sort of thing before?

[Bob Phillips]

Something like this in say B1?

=IF(A1>10,H1,"")

which tests cell A1, and if the value is greater than 10, stores the
contents of H1 in cell B1, otherwise it stores an empty string.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mr. Clean" <MrClean@discussions.microsoft.com> wrote in message
news:39FB6AB7-50DE-4884-A5F5-C39D78CD187A@microsoft.com...
> I need a formula in one cell that, under certain conditions (and IF
> statement), writes the value of one cell into another cell. In this case,
I
> can't use a macro. Anyone done this sort of thing before?

[Mr. Clean]

Jim, thanks for the QUICK reply. What I really need is a formula in a cell,
say A1, that tests another cell, say B2, and if the test passes, copies the
contents of B2 to cell C3.

"Jim Thomlinson" wrote:

> =if(B2>10, C2, D2)
>
> This formula (placed in A2 or ???) will look at the value in Cell B2. If B2
> is greater than 10 then it returns the value of C2 else it returns the value
> of D2...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Mr. Clean" wrote:
>
> > I need a formula in one cell that, under certain conditions (and IF
> > statement), writes the value of one cell into another cell. In this case, I
> > can't use a macro. Anyone done this sort of thing before?

[Mr. Clean]

Bob, thanks for the QUICK reply. What I really need is a formula in a cell,
say A1, that tests another cell, say B2, and if the test passes, copies the
contents of B2 to cell C3.

"Bob Phillips" wrote:

> Something like this in say B1?
>
> =IF(A1>10,H1,"")
>
> which tests cell A1, and if the value is greater than 10, stores the
> contents of H1 in cell B1, otherwise it stores an empty string.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Mr. Clean" <MrClean@discussions.microsoft.com> wrote in message
> news:39FB6AB7-50DE-4884-A5F5-C39D78CD187A@microsoft.com...
> > I need a formula in one cell that, under certain conditions (and IF
> > statement), writes the value of one cell into another cell. In this case,
> I
> > can't use a macro. Anyone done this sort of thing before?
>
>
>

[Bob Phillips]

You can't do that. A formula can only return a value to the cell it is in,
it cannot update other cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mr. Clean" <MrClean@discussions.microsoft.com> wrote in message
news:74C57CD3-D6E6-4A28-BA97-B0433C88D7BF@microsoft.com...
> Bob, thanks for the QUICK reply. What I really need is a formula in a
cell,
> say A1, that tests another cell, say B2, and if the test passes, copies
the
> contents of B2 to cell C3.
>
> "Bob Phillips" wrote:
>
> > Something like this in say B1?
> >
> > =IF(A1>10,H1,"")
> >
> > which tests cell A1, and if the value is greater than 10, stores the
> > contents of H1 in cell B1, otherwise it stores an empty string.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Mr. Clean" <MrClean@discussions.microsoft.com> wrote in message
> > news:39FB6AB7-50DE-4884-A5F5-C39D78CD187A@microsoft.com...
> > > I need a formula in one cell that, under certain conditions (and IF
> > > statement), writes the value of one cell into another cell. In this
case,
> > I
> > > can't use a macro. Anyone done this sort of thing before?
> >
> >
> >

[Mr. Clean]

Thanks, Bob. It's guys like you that keep this world going.

"Bob Phillips" wrote:

> You can't do that. A formula can only return a value to the cell it is in,
> it cannot update other cells.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Mr. Clean" <MrClean@discussions.microsoft.com> wrote in message
> news:74C57CD3-D6E6-4A28-BA97-B0433C88D7BF@microsoft.com...
> > Bob, thanks for the QUICK reply. What I really need is a formula in a
> cell,
> > say A1, that tests another cell, say B2, and if the test passes, copies
> the
> > contents of B2 to cell C3.
> >
> > "Bob Phillips" wrote:
> >
> > > Something like this in say B1?
> > >
> > > =IF(A1>10,H1,"")
> > >
> > > which tests cell A1, and if the value is greater than 10, stores the
> > > contents of H1 in cell B1, otherwise it stores an empty string.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Mr. Clean" <MrClean@discussions.microsoft.com> wrote in message
> > > news:39FB6AB7-50DE-4884-A5F5-C39D78CD187A@microsoft.com...
> > > > I need a formula in one cell that, under certain conditions (and IF
> > > > statement), writes the value of one cell into another cell. In this
> case,
> > > I
> > > > can't use a macro. Anyone done this sort of thing before?
> > >
> > >
> > >
>
>
>

[RickyGregory]

Hi,

Can anyone help, i have a rest button that reset some cells to blank. What im after is writing a formula to cell A1 once i have clicked the reset button.

I have tried the following but each time it comes back failed:

Sheets("Main Sheet").Range("A1").Formula = "=LOOKUP(2,1/(DATA!L1:L20212<>""),DATA!L1:L20212)"

Sheets("Main Sheet").Range("A1").Value = "=LOOKUP(2,1/(DATA!L1:L20212<>""),DATA!L1:L20212)"

Can anyone help.

Thanks