Using MID function within an IF function

R

Ray Kostanty

I have a simple spreadsheet of about 240 rows with columns as follows:
Column A - name
Column B - street address
Column C - city, state, Zip
Column D - phone number
Column E - email address
Column F - "Permissions" to include data in columns A-E in a directory

Column F consists of 1 to 7 characters. The first position is always Y.
The other odd positions can be Y, N or blank. The even positions are
hyphens or blanks. Examples:

Y-Y-Y-Y means OK to include all data in columns A-E in directory
Y-N-Y-Y means OK to include cols A, D and E in directory
Y-N-Y means OK to include cols A and D in directory (If there they don't
have an email address, there are only five characters.)
Y-Y-N means okay to include cols A-C in directory
Y-Y means OK to include cols A-C; phone number and email are unknown

In column G, I'm trying to create a function based on column F that will
blank out data that's not to be published. For example, in row 1, if
column F is Y-Y-N-Y, I want the phone number in D1 to be blank. Here's
what I've entered in G1:

=IF("MID(F1,5,1)"="N",D1=" ")
and
=IF(MID(F1,5,1)="N",D1=" ")

In either case, the word FALSE appears in G1, and the phone number in D1
remains unchanged.

I've also tried these functions in G1:

=IF("MID(F1,5,1)"=N,D1=" ")
and
=IF(MID(F1,5,1=N,D1=" ")

In both these cases #NAME? appears in G1 and the phone number remains
unchanged.

I've also tried putting the permissions data in column A and shifting
all the other data one column to the right, hoping that the there's
something odd about the sequence that Excel follows in evaluating
functions. Same results.

Any other suggestions for me to try? Note also that if the third
character is N, I want to blank both columns B and C for that row.

Thanks for you help.

Ray
 
S

Scott Collier

I am sure there are many ways to skin this cat, but I would insert a column
at column D making Column E your phone number column

in D1 : =IF(G1="N","",E1)
in G1 : =MID(F1,5,1)
in E1 : your typed phone number

D1 will go blank if G1=N, otherwise it will display the phone number as
typed in E1.

You can then hide column D if required.
And access it through your other formulas,
instead of actually trying to change the typed phone numbers.

Regards
Scott
 
R

Ray K

Scott said:
I am sure there are many ways to skin this cat, but I would insert a column
at column D making Column E your phone number column

in D1 : =IF(G1="N","",E1)
in G1 : =MID(F1,5,1)
in E1 : your typed phone number

D1 will go blank if G1=N, otherwise it will display the phone number as
typed in E1.

You can then hide column D if required.
And access it through your other formulas,
instead of actually trying to change the typed phone numbers.

Regards
Scott
Scott,

There was a bit of a problem with your formula. The MID function can't
reference F1 because it became G1 when I inserted the new column D; then
it became a circular reference.

I kept the new column D and changed D1 to =IF(MID(G1,5,1)="N","",E1).
Works perfectly. I should be able to handle the other cases okay with
your approach. I'm still puzzled why my original formula didn't work.

Thanks for guidance.

Ray
 
P

Pete_UK

A formula can only return a result - it cannot affect the formatting or
content of another cell. You could have tried conditional formatting to
turn the font colour the same as the background colour if the condition
is met, though this would have to apply to the cell which you want to
change and the contents are unchanged - merely hidden.

Hope this helps.

Pete
 
R

Ray K

Pete,

I'm not trying to format a cell or change a font color. I merely wanted
the cell to be blank or contains data, depending on the whether certain
letters in one of the cells are Y or N.

I used Scott's approach and got everything to work. The trick was to
split the Permissions into four columns, with each having a single Y or
N letter. The split is done with the Data/Text to column menu option.

Ray
 
P

Pete_UK

Ray, this is what you put in your original posting:
In column G, I'm trying to create a function based on column F that
will blank out data that's not to be published. For example, in row 1,
if column F is Y-Y-N-Y, I want the phone number in D1 to be blank.
Here's what I've entered in G1:

=IF("MID(F1,5,1)"="N",D1=" ")
and
=IF(MID(F1,5,1)="N",D1=" ")

and you also added later:
I'm still puzzled why my original formula didn't work.

What you *seem* to be trying to do here is to set D1 to blank if the
5th character of F1 is "N" and your formula was in G1 - my reply says
you can't change D1 from a formula in G1.

Anyway, I'm glad you got something to work for you.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top