Nested if then else statement
Hi. I need to have one cell in my summary spreadsheet that looks to
another detail spreadsheet and gives me the data if there is something in the cell. I have it working for one but when I try to nest the "if then else" statement it fails. Basically what I have is this: =IF('May 19'!K4="", "","Vac") Now I need to add to this if K5 is not null "sick", k6 is not null "personal", etc. It will work for one with the above formula but when I try adding more criteria it fails. I tried this: =IF(('May 19'!$K4=""),"",IF(('May 19'!$K5=""),"",IF(('May 19'! $K6=""),"",IF(('May 19'!$K7=""),"",CONCATENATE(K4,K5,K6,K7,K8))))) But that didn't work either. Any suggestions would be appreciated. |
Nested if then else statement
Anytime you have the same output for multiple checks, you can probably use
AND/OR functions. Also, CONCATENATE is a bulky function with a 30 piece limit, and not really necessary. You can join things things together more easily witht he ampersand. 'May 19'! Rearranged: =IF(OR('May 19'!$K4="",'May 19'!$K5="",'May 19'!$K6="",'May 19'!$K7=""),"",K4&K5&K6&K7&K8) Or, another way of interpreting your question... =IF(K4="","","Vac")&IF(K5="","","sick")&IF(K6=""," ","Personal") which has you concatenating different things, with each part being determined by an IF function. Note that following this structure, you do not have to worry about the 7 nested function limit. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "clk" wrote: Hi. I need to have one cell in my summary spreadsheet that looks to another detail spreadsheet and gives me the data if there is something in the cell. I have it working for one but when I try to nest the "if then else" statement it fails. Basically what I have is this: =IF('May 19'!K4="", "","Vac") Now I need to add to this if K5 is not null "sick", k6 is not null "personal", etc. It will work for one with the above formula but when I try adding more criteria it fails. I tried this: =IF(('May 19'!$K4=""),"",IF(('May 19'!$K5=""),"",IF(('May 19'! $K6=""),"",IF(('May 19'!$K7=""),"",CONCATENATE(K4,K5,K6,K7,K8))))) But that didn't work either. Any suggestions would be appreciated. |
Nested if then else statement
Hi
try =IF('May 19'!K4<"","Vac",IF('May 19'!K5<"","Sick",IF('May 19'!K6<"","Personal",""))) "clk" wrote: Hi. I need to have one cell in my summary spreadsheet that looks to another detail spreadsheet and gives me the data if there is something in the cell. I have it working for one but when I try to nest the "if then else" statement it fails. Basically what I have is this: =IF('May 19'!K4="", "","Vac") Now I need to add to this if K5 is not null "sick", k6 is not null "personal", etc. It will work for one with the above formula but when I try adding more criteria it fails. I tried this: =IF(('May 19'!$K4=""),"",IF(('May 19'!$K5=""),"",IF(('May 19'! $K6=""),"",IF(('May 19'!$K7=""),"",CONCATENATE(K4,K5,K6,K7,K8))))) But that didn't work either. Any suggestions would be appreciated. |
Nested if then else statement
On Jul 7, 12:47*pm, Eduardo wrote:
Hi try =IF('May 19'!K4<"","Vac",IF('May 19'!K5<"","Sick",IF('May 19'!K6<"","Personal",""))) "clk" wrote: Hi. *I need to have one cell in my summary spreadsheet that looks to another detail spreadsheet and gives me the data if there is something in the cell. *I have it working for one but when I try to nest the "if then else" statement it fails. Basically what I have is this: =IF('May 19'!K4="", "","Vac") *Now I need to add to this if K5 is not null "sick", k6 is not null "personal", etc. It will work for one with the above formula but when I try adding more criteria it fails. *I tried this: =IF(('May 19'!$K4=""),"",IF(('May 19'!$K5=""),"",IF(('May 19'! $K6=""),"",IF(('May 19'!$K7=""),"",CONCATENATE(K4,K5,K6,K7,K8))))) But that didn't work either. Any suggestions would be appreciated.- Hide quoted text - - Show quoted text - Thank you so much! It worked. Appreciate the suggestions! ~ Carrie |
All times are GMT +1. The time now is 11:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com