Help with tricky IF statement. Somewhat conditional.

You may need to write a macro to ensure that G14 stays blank in the cells
you specify. There is no way I know of with just formulas.


"mcr1" <mcr1.21apum_1136689800.7187@excelforum-nospam.com> wrote in message
news:mcr1.21apum_1136689800.7187@excelforum-nospam.com...
>
> You guys have been so helpful, and I sure do appreciate everything you
> all have done to each me about Excel. Thanks.
>
> The formula in G14 is =B14. There will always be a value in B14. I
> only want G14 to equal B14 is there is a specific value in F6. If
> there is not the value that I want in F6, I want G14 to remain blank
> and not copy the contents of B14 into G14.
>
> For example:
> If F6=n12345 then G14 = B14.
> If F6=n567AB then G14 = B14.
> but,
> If F6=n6609K then G14 should not copy B14 and stay blank.
> or
> If F6=n145AB then G14 should not copy B14 and stay blank.
>
> There are maybe 5 nXXXXX that I would need this to happen for, so if 1
> of these 5 values appear in F6, then I want G14 to equal the contents
> B14, all the while keeping the formula of G14 equaling B14. If there
> is is nothing in G14, I want the space to remain completely blank, no
> "0", no "#N/A", no nothing. As always, thanks in advance
> guys................
>
> mcr1
>
>
> --
> mcr1
> ------------------------------------------------------------------------
> mcr1's Profile:
> http://www.excelforum.com/member.php...o&userid=15496
> View this thread: http://www.excelforum.com/showthread...hreadid=499109
>

On Sat, 7 Jan 2006 21:00:46 -0600, mcr1
<mcr1.21apum_1136689800.7187@excelforum-nospam.com> wrote:

>
>You guys have been so helpful, and I sure do appreciate everything you
>all have done to each me about Excel. Thanks.
>
>The formula in G14 is =B14. There will always be a value in B14. I
>only want G14 to equal B14 is there is a specific value in F6. If
>there is not the value that I want in F6, I want G14 to remain blank
>and not copy the contents of B14 into G14.
>
>For example:
>If F6=n12345 then G14 = B14.
>If F6=n567AB then G14 = B14.
>but,
>If F6=n6609K then G14 should not copy B14 and stay blank.
>or
>If F6=n145AB then G14 should not copy B14 and stay blank.
>
>There are maybe 5 nXXXXX that I would need this to happen for, so if 1
>of these 5 values appear in F6, then I want G14 to equal the contents
>B14, all the while keeping the formula of G14 equaling B14. If there
>is is nothing in G14, I want the space to remain completely blank, no
>"0", no "#N/A", no nothing. As always, thanks in advance
>guys................
>
>mcr1

What you describe cannot be done. There is no way to have a formula in G14,
and also have G14 be "blank". In Excel, a "blank" cell contains nothing. So
G14 cannot be "blank" since it contains a formula.

If you want G14 to "appear" empty unless F6 has one of the requisite numbers,
then you can change the formula as noted below.

G14:

=if(or(f6="n12345",f6="n567AB",f6="nXXXXX"),b14,"")

This will put a null string in G14 if F6 does not contain one of the requisite
values.

It is also possible to use conditional formatting so that the contents of G14
cannot be seen; however, if G14 is being used in subsequent calculations, then
this may not be a good solution.


--ron

Ron,

1000 thanks. That worked really well.

On Sat, 7 Jan 2006 22:34:20 -0600, mcr1
<mcr1.21atsa_1136694900.7403@excelforum-nospam.com> wrote:

>
>Ron,
>
>1000 thanks. That worked really well.

You're welcome. Glad to help.

If you ever wind up with a long list of acceptable numbers, a separate list of
them might be more efficient; but for just a few, the formula I posted should
work fine.
--ron

hi!

One way:

A1:A5 holds your values of interest.

Formula in G14:

=IF(ISNUMBER(MATCH(F6,A1:A5,0)),B14,"")

Biff

"mcr1" <mcr1.21apum_1136689800.7187@excelforum-nospam.com> wrote in message
news:mcr1.21apum_1136689800.7187@excelforum-nospam.com...
>
> You guys have been so helpful, and I sure do appreciate everything you
> all have done to each me about Excel. Thanks.
>
> The formula in G14 is =B14. There will always be a value in B14. I
> only want G14 to equal B14 is there is a specific value in F6. If
> there is not the value that I want in F6, I want G14 to remain blank
> and not copy the contents of B14 into G14.
>
> For example:
> If F6=n12345 then G14 = B14.
> If F6=n567AB then G14 = B14.
> but,
> If F6=n6609K then G14 should not copy B14 and stay blank.
> or
> If F6=n145AB then G14 should not copy B14 and stay blank.
>
> There are maybe 5 nXXXXX that I would need this to happen for, so if 1
> of these 5 values appear in F6, then I want G14 to equal the contents
> B14, all the while keeping the formula of G14 equaling B14. If there
> is is nothing in G14, I want the space to remain completely blank, no
> "0", no "#N/A", no nothing. As always, thanks in advance
> guys................
>
> mcr1
>
>
> --
> mcr1
> ------------------------------------------------------------------------
> mcr1's Profile:
> http://www.excelforum.com/member.php...o&userid=15496
> View this thread: http://www.excelforum.com/showthread...hreadid=499109
>

If you know for certain which values for F6 trigger having G14 equal
B14, and there aren't too many of them (you said there are five), try
putting something like this in G14:

=IF(OR(F6=nXXXXX,F6=nXXXXX,F6=nXXXXX,F6=nXXXXX),B14,"")

replacing XXXXX with your actual values, of course. Extend the series
of OR tests by adding more "F6=nXXXXX" bits separated by commas.

This makes the value of G14 blank unless one of your conditions is met.

If you know for certain which values for F6 trigger having G14 equal
B14 and there aren't too many of them (you said there are five), try
putting something like this in G14:

=IF(OR(F6=nXXXXX,F6=nXXXXX,F6=nXXXXX,F6=nXXXXX),B14,"")

replacing XXXXX with your actual values, of course. Extend the series
of OR tests by adding more "F6=nXXXXX" bits separated by commas.