To leave a cell blank following an "IF" function
 

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.

To leave a cell blank following an "IF" function
 

=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" wrote:

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.

To leave a cell blank following an "IF" function
 

try

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

"abie26" wrote:

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.

To leave a cell blank following an "IF" function
 

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

To leave a cell blank following an "IF" function
 

oops, try


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


"Duke Carey" wrote:

try

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

"abie26" wrote:

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.

To leave a cell blank following an "IF" function
 

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




abie26 wrote:

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.

--

Dave Peterson

To leave a cell blank following an "IF" function
 

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

"abie26" wrote:

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.

To leave a cell blank following an "IF" function
 

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" wrote:

=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" wrote:

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.

To leave a cell blank following an "IF" function
 

That works too Duke, thanks!

Abie26

"Duke Carey" wrote:

oops, try


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


"Duke Carey" wrote:

try

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

"abie26" wrote:

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.

To leave a cell blank following an "IF" function
 

Thanks Peo, both work and the second one gives more possibilities, that's
great!

Abie26

"Peo Sjoblom" wrote:

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