Multiple level of if is not supported in excel

[jb]

I have following formula in some columns of my excel sheet. Where I am using a column value in b7, c7 and so on which contains name of a sheet. This b7, c7 and so on contains alt+enter key so i have used clean function. now to make this formula generalized, I have used address and row function. I have used 2 in column part as fixed value. I want to use formula to generate value 2. In some another sheet same formula is to be used where instead of 2, 3 will come.

To generate value 2,3 and so on there is a rule. Cell A5 of all sheets contains name of a day. e.g MONDAY, TUESDAY etc. Rule is: if A5=MONDAY then 2 will come in formula. For TUESDAY 3 will come and so on. If I am trying to replace nested if for generating value 2,3, upto 7 then it gives me an error regarding more levels of parenthesis. I am not allowed to add anything on any sheet except revising this formula. Is there any other way to do this?

=IF(INDIRECT(CLEAN(B$7)&"!"&ADDRESS(ROW(),2))="","",INDIRECT(CLEAN(B$7)&"!"&ADDRESS(ROW(),2)))

 

[Hui]

JB


In Excel Versions 95 to 2007 you can use 7 levels of nesting

On Excel post 2007 you can use as many levels of nesting as your PC's memory can support.


Generally when going to multiple levels of nesting in If's you could think of other ways to implement the same functionality including the use of Choose() or Using some of the Lookup functions V/HLookup, Index etc

 

[sgmpatnaik]

@JB


Hi


It is confusing, can you give some more details exact what do you want other wise try to upload a sample workbook


for posting a sample workbook go through this link


http://chandoo.org/forums/topic/posting-a-sample-workbook


Thanks


SP


Oops Hui sir already replied sorry

 

[NARAYANK991]

Hi ,


Why don't you put in a named range called Days_of_the_Week , and in the Refers To box , put in the following :


={"SUNDAY","MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY"}


Then , your main formula can be this :


=IF(INDIRECT(CLEAN(B$7)&"!"&ADDRESS(ROW(),MATCH(A5,Days_of_the_Week,0)))="","",INDIRECT(CLEAN(B$7)&"!"&ADDRESS(ROW(),MATCH(A5,Days_of_the_Week,0))))


Thus , if the cell A5 contains SUNDAY , the column number will be 1 , if it contains MONDAY , the column number will be 2 , and so on.


You don't need multiple levels of IF.


Narayan

 

[jb]

I tried but not able to upload excel file. So I am trying to explain my problem.

I have 6 main sheets with name mon, tue, wed, thu, fri and sat. On mon sheet, column a5 contains word MONDAY, on tue sheet, column a5 contains word TUESDAY and so on.


Also b7, c7, d7 upto q7 cells of each sheet contains names of 16 data sheets.


I have 16 additional data sheets from where I need to fetch data. All 16 additional sheets have 6 columns with title mon, tue, wed, thu, fri and sat. mon title is in 2nd column, tue in 3rd and so on.


On mon sheet, I have 16 columns.

10th row and first column of mon sheet will get data from 10th row and 2nd column of data sheet1.

1oth row and second column of mon sheet will get data from 10th row and 2nd column of data sheet2. and so on.


I have used following formula to perform this task.


=IF(INDIRECT(CLEAN(B$7)&"!"&ADDRESS(ROW(),2))="","",INDIRECT(CLEAN(B$7)&"!"&ADDRESS(ROW(),2)))


here b$7 contains name of data sheet1. row() refers current row i.e. row 10. column 2 is constant. column 2 means second column of every data sheet with title mon.


I want this 2 to be replaced with some formula. I have tried to use cell a5 in if condition.


if A5=MONDAY then 2 will come in formula. For TUESDAY 3 will come and so on. If I am trying to replace nested if for generating value 2,3, upto 7 then it gives me an error regarding more levels of parenthesis. I am not allowed to add anything on any sheet except revising this formula. Is there any other way to do this?

 

[jb]

Thanks NARAYANK991. It worked. Thanks a lot. Ignore my most recent post on same issue.