If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
To leave a cell blank following an "IF" function
try
=if(and(a10,a14),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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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,a14),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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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,a14),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. |
#10
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|