To leave a cell blank following an "IF" function

A

abie26

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

BoniM

=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.
 
P

Peo Sjoblom

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))
 
D

Duke Carey

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

Dave Peterson

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

Toppers

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
 
A

abie26

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

abie26

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

abie26

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

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

Similar Threads

Excel - Wich Function should I use? 1
VBA for Dependents shortcut menu 0
If Not blank - Formula for two cells 0
Function for summing or counting cells based on a specified fill color 3
Code to cut a paragraph that's followed by a table 2
SUMIF with criteria "<>" & "=" 4
Index Match to ignore blank cells 0
VB Function for Copy & Paste 0

Top