To leave a cell blank following an "IF" function

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

Guest

How can I leave a cell blank following a response to an "IF" function?
Because in Excel 2007, the default response in a cell is "FALSE" when I leave
a cell in question to the "IF" function blank. This is because I would like
to be able to leave those cells in question to the function as blank or not
depending on the data that I would like to show.

Example: [in cell B1: IF(A1="a",1,IF(A1="b",2,IF(A1="c",3)))] in this
example, B1 returns "FALSE" when I leave A1 blank but I would like it to be
able to remain blank and not show "FALSE". What I was thinking is that the
function for a dropdown menu would do the job but I cannot get it to work the
way I would like it to so I am using the "IF" function instead.

What I have now is a dropdown menu in A1 (from data in another Worksheet)
and when I select a certain data from this menu, I would like Excel to
automatically put corresponding data in cells B1 and B2. And the only way I
know how it can do what I want it to do is to use the "IF" function in B1, B2
etc... but there is a bit of data in criteria to the "IF" function that this
function returns an error because there are too many functions within this
function.

Hope this makes sense.
 
=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,"")))

Excel will return the word False if you don't tell it what to do under those
circumstances.
 
Try



=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,"")))

if you have many values you might want to consider

=IF(A1="","",VLOOKUP(A1,{"a",1;"b",2;"c",3;"d",4;"e",5;"f",6;"g",7;"h",8;"i",9;"j",10;"k",11;"l",12},2,0))
 
oops, try


=IF(AND(A1<"d",A1>="a"),SEARCH(A1,"abc"),"")


Duke Carey said:
try

=if(and(a1>0,a1<4),choose(a1,"a","b","c"),"")

abie26 said:
How can I leave a cell blank following a response to an "IF" function?
Because in Excel 2007, the default response in a cell is "FALSE" when I leave
a cell in question to the "IF" function blank. This is because I would like
to be able to leave those cells in question to the function as blank or not
depending on the data that I would like to show.

Example: [in cell B1: IF(A1="a",1,IF(A1="b",2,IF(A1="c",3)))] in this
example, B1 returns "FALSE" when I leave A1 blank but I would like it to be
able to remain blank and not show "FALSE". What I was thinking is that the
function for a dropdown menu would do the job but I cannot get it to work the
way I would like it to so I am using the "IF" function instead.

What I have now is a dropdown menu in A1 (from data in another Worksheet)
and when I select a certain data from this menu, I would like Excel to
automatically put corresponding data in cells B1 and B2. And the only way I
know how it can do what I want it to do is to use the "IF" function in B1, B2
etc... but there is a bit of data in criteria to the "IF" function that this
function returns an error because there are too many functions within this
function.

Hope this makes sense.
Click to expand...
Click to expand...
 
How about:
=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,"")))

But I think I'd use:
=IF(a1="","",if(A1="a",1,IF(A1="b",2,IF(A1="c",3,"No match"))))

=====
Actually, I'd use data|validation for A1 that uses column A of that other sheet
as its list.

Debra Dalgleish explains how:
http://contextures.com/xlDataVal01.html

Then I'd put that other information in columns B and C of that other sheet, too:

Then I could use formulas like:

=if(a1="","",vlookup(a1,sheet2!a:c,2,false))
(in B1)

And
=if(a1="","",vlookup(a1,sheet2!a:c,3,false))
(in C1)

Debra also has notes for =vlookup():
http://contextures.com/xlFunctions02.html



How can I leave a cell blank following a response to an "IF" function?
Because in Excel 2007, the default response in a cell is "FALSE" when I leave
a cell in question to the "IF" function blank. This is because I would like
to be able to leave those cells in question to the function as blank or not
depending on the data that I would like to show.

Example: [in cell B1: IF(A1="a",1,IF(A1="b",2,IF(A1="c",3)))] in this
example, B1 returns "FALSE" when I leave A1 blank but I would like it to be
able to remain blank and not show "FALSE". What I was thinking is that the
function for a dropdown menu would do the job but I cannot get it to work the
way I would like it to so I am using the "IF" function instead.

What I have now is a dropdown menu in A1 (from data in another Worksheet)
and when I select a certain data from this menu, I would like Excel to
automatically put corresponding data in cells B1 and B2. And the only way I
know how it can do what I want it to do is to use the "IF" function in B1, B2
etc... but there is a bit of data in criteria to the "IF" function that this
function returns an error because there are too many functions within this
function.

Hope this makes sense.
Click to expand...
 
Use VLOOKUP:

Set table of values (say in Sheet2 column A & B)

A B
a 1
b 2
c 3
etc

in B1:

=VLOOKUP(A1,Sheet2!A:B,2,0)

and a similar formula for B2

HTH
 
BoniM, thanks for the tip and it works that way. I thought that I had to put
"" after each and every IF function but it works putting it only in the last
function. But it would be nice if I could find out how to automatically
change B1, B2 etc... according to what I select in a dropdown menu from A1.

Abie26

BoniM said:
=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,"")))

Excel will return the word False if you don't tell it what to do under those
circumstances.

abie26 said:
How can I leave a cell blank following a response to an "IF" function?
Because in Excel 2007, the default response in a cell is "FALSE" when I leave
a cell in question to the "IF" function blank. This is because I would like
to be able to leave those cells in question to the function as blank or not
depending on the data that I would like to show.

Example: [in cell B1: IF(A1="a",1,IF(A1="b",2,IF(A1="c",3)))] in this
example, B1 returns "FALSE" when I leave A1 blank but I would like it to be
able to remain blank and not show "FALSE". What I was thinking is that the
function for a dropdown menu would do the job but I cannot get it to work the
way I would like it to so I am using the "IF" function instead.

What I have now is a dropdown menu in A1 (from data in another Worksheet)
and when I select a certain data from this menu, I would like Excel to
automatically put corresponding data in cells B1 and B2. And the only way I
know how it can do what I want it to do is to use the "IF" function in B1, B2
etc... but there is a bit of data in criteria to the "IF" function that this
function returns an error because there are too many functions within this
function.

Hope this makes sense.
Click to expand...
Click to expand...
 
That works too Duke, thanks!

Abie26

Duke Carey said:
oops, try


=IF(AND(A1<"d",A1>="a"),SEARCH(A1,"abc"),"")


Duke Carey said:
try

=if(and(a1>0,a1<4),choose(a1,"a","b","c"),"")

abie26 said:
How can I leave a cell blank following a response to an "IF" function?
Because in Excel 2007, the default response in a cell is "FALSE" when I leave
a cell in question to the "IF" function blank. This is because I would like
to be able to leave those cells in question to the function as blank or not
depending on the data that I would like to show.

Example: [in cell B1: IF(A1="a",1,IF(A1="b",2,IF(A1="c",3)))] in this
example, B1 returns "FALSE" when I leave A1 blank but I would like it to be
able to remain blank and not show "FALSE". What I was thinking is that the
function for a dropdown menu would do the job but I cannot get it to work the
way I would like it to so I am using the "IF" function instead.

What I have now is a dropdown menu in A1 (from data in another Worksheet)
and when I select a certain data from this menu, I would like Excel to
automatically put corresponding data in cells B1 and B2. And the only way I
know how it can do what I want it to do is to use the "IF" function in B1, B2
etc... but there is a bit of data in criteria to the "IF" function that this
function returns an error because there are too many functions within this
function.

Hope this makes sense.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Peo, both work and the second one gives more possibilities, that's
great!

Abie26

Peo Sjoblom said:
Try



=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,"")))

if you have many values you might want to consider

=IF(A1="","",VLOOKUP(A1,{"a",1;"b",2;"c",3;"d",4;"e",5;"f",6;"g",7;"h",8;"i",9;"j",10;"k",11;"l",12},2,0))


--
Regards,

Peo Sjoblom



abie26 said:
How can I leave a cell blank following a response to an "IF" function?
Because in Excel 2007, the default response in a cell is "FALSE" when I
leave
a cell in question to the "IF" function blank. This is because I would
like
to be able to leave those cells in question to the function as blank or
not
depending on the data that I would like to show.

Example: [in cell B1: IF(A1="a",1,IF(A1="b",2,IF(A1="c",3)))] in this
example, B1 returns "FALSE" when I leave A1 blank but I would like it to
be
able to remain blank and not show "FALSE". What I was thinking is that the
function for a dropdown menu would do the job but I cannot get it to work
the
way I would like it to so I am using the "IF" function instead.

What I have now is a dropdown menu in A1 (from data in another Worksheet)
and when I select a certain data from this menu, I would like Excel to
automatically put corresponding data in cells B1 and B2. And the only way
I
know how it can do what I want it to do is to use the "IF" function in B1,
B2
etc... but there is a bit of data in criteria to the "IF" function that
this
function returns an error because there are too many functions within this
function.

Hope this makes sense.
Click to expand...
Click to expand...
 
Back
Top