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.

"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.

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.

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.

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.

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.

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.

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.

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

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.