Help!!! Nested IF Statement limitation

[trenier]

Greetings,

I have a nested IF statement that I am using in an application I am building in Excel. I am using logic to get the current month and then evaluate whether the value is a number. I would like to know if there is a better way to perform what I am trying to accomplish using another function. I am trying to cocantonate the the statements together which has worked in the past but not for this particular function. Your help is greatly appreciated. See Below... The function works if i use 7 IF statements.


=IF(I3="Jan",IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355),IF(I3="Feb",IF(ISNUMBER(SEARCH("NA",Data!$C$355)),"NA",Data!$C$355),IF(I3="Mar",IF(ISNUMBER(SEARCH("NA",Data!$D$355)),"NA",Data!$D$355),IF(I3="Apr",IF(ISNUMBER(SEARCH("NA",Data!$E$355)),"NA",Data!$E$355))))) + IF(I3="May",IF(ISNUMBER(SEARCH("NA",Data!$F$355)),"NA",Data!$F$355),IF(I3="Jun",IF(ISNUMBER(SEARCH("NA",Data!$G$355)),"NA",Data!$G$355),IF(I3="Jul",IF(ISNUMBER(SEARCH("NA",Data!$H$355)),"NA",Data!$H$355),IF(I3="Aug",IF(ISNUMBER(SEARCH("NA",Data!$I$355)),"NA",Data!$I$355),IF(I3="Sep",IF(ISNUMBER(SEARCH("NA",Data!$J$355)),"NA",Data!$J$355)))))) + IF(I3="Oct",IF(ISNUMBER(SEARCH("NA",Data!$K$355)),"NA",Data!$K$355),IF(I3="Nov",IF(ISNUMBER(SEARCH("NA",Data!$L$355)),"NA",Data!$L$355),IF(I3="Dec",IF(ISNUMBER(SEARCH("NA",Data!$M$355)),"NA",Data!$M$355))))

Cheers,

Todd

 

[Aladin Akyurek]

I3 contains just the current Month such as "Jan" "Feb". I need to extract the data from the source worksheet. Jan, Feb are headings in the source worksheet. Users enter NA in the fields when they do not have a value. I3 contains the current month. I need to retrive values for a particular month that are arranged in columns for a particular business metric.

Sorry my skills in Excel in this area are not strong.


Thanks for everyones help on this!

Todd

Care to post a mall sample along with the desired results?

 

[trenier]

Vlady,

This function, =LOOKUP(9.99999999999999E+307,SEARCH(MonthList,I3),Data!$B$341:$M$341) is working great But it does not seem to work with Excel Services. This has been an issue when trying to work with Excel Services. Any ideas anyone?

Todd

 

[Aladin Akyurek]

Vlady,

This function, =LOOKUP(9.99999999999999E+307,SEARCH(MonthList,I3),Data!$B$341:$M$341) is working great But it does not seem to work with Excel Services. This has been an issue when trying to work with Excel Services. Any ideas anyone?

Todd

Great to hear that the formula works. What happens exactly with Excel Services?

 

[trenier]

I am getting a #Error in any of the cells that I have used the formula. As far as I know the Lookup and Search function are compatable with Excel Services.

Any ideas?

 

[Aladin Akyurek]

I am getting a #Error in any of the cells that I have used the formula. As far as I know the Lookup and Search function are compatable with Excel Services.

Any ideas?

I'm not familiar with Excel Services. What kind of error are you getting?

 

[trenier]

I am getting a #Value error in those cells. What do I need to specify in the value parameter for this function?

 

[trenier]

It may be that the following formula will not work in Excel Services is because of the range that is referenced. I found this article on compatablity with Excel Services and it referencing defining a range selection as not be compatiable with Excel Services.

Change workbook parameters in Excel Services - SharePoint Server - Office.com

=LOOKUP(9.99999999999999E+307,SEARCH(MonthList,I3),Data!$B$284:$M$284)

If anyone can offer help on resolving this issue, I would appreciate it.

Todd

 

[trenier]

A mall sample?

 

[Aladin Akyurek]

A mall sample?

I meant to write small not mall. But that's irrelevant now.

I am getting a #Value error in those cells. What do I need to specify in the value parameter for this function?

We really need someone with experience invoving Excel Services. Let's wait for one to turn up....

 

[trenier]

Hey Mark,

I just wanted to let you know that your function works well. I attempted another solution but this formula works with SharePoint Excel Services which is where this is going to reside. Aladin's solution worked but SharePoint Excel Services does not work with formulas with adjacent referenced cells as what was used in the Month range.

Thank you! for your help with this solution.

Regards,

Todd