limit of 7 nested functions?

[Olympiad]

I have the following formula working well, however as there is a limit of 7
nested functions and need some more, what is my options?

=IF(B5="P-NE","Pre App Not Endorsed",IF(B5="P-PI","Pre App Pending
Info",IF(B5="P-WD","Pre App
Withdrawn",IF(B5="WFIN","Withdrawn",IF(B5="PREP","App Being
Prepared",IF(B5="PFI","App Pending Further Info",IF(B5="PDD","App Pending D&D
report",IF(B5="PID","App",))))))))

[Niek Otten]

Use the Vlookup function instead.
Look in Help for details, post back in this thread if you have problems.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Olympiad" <[email protected]> wrote in message
news:[email protected]...
>I have the following formula working well, however as there is a limit of 7
> nested functions and need some more, what is my options?
>
> =IF(B5="P-NE","Pre App Not Endorsed",IF(B5="P-PI","Pre App Pending
> Info",IF(B5="P-WD","Pre App
> Withdrawn",IF(B5="WFIN","Withdrawn",IF(B5="PREP","App Being
> Prepared",IF(B5="PFI","App Pending Further Info",IF(B5="PDD","App Pending
> D&D
> report",IF(B5="PID","App",))))))))

[spog00]

use code:

Sub test()

If Range("B5") = "P-NE" Then Range("A1") = "Pre App Not Endorsed"
If Range("B5") = "P-PI" Then Range("A1") = "Pre App Pending Info"
If Range("B5") = "P-WD" Then Range("A1") = "Pre App Withdrawn"
If Range("B5") = "WFIN" Then Range("A1") = "Withdrawn"
If Range("B5") = "PREP" Then Range("A1") = "App Being Prepared"
If Range("B5") = "PFI" Then Range("A1") = "App Pending Futher Info"
If Range("B5") = "PDD" Then Range("A1") = "App Pending D&D Report"
If Range("B5") = "PID" Then Range("A1") = "App"

End Sub

I'm fairly new to VBA, this will work (I have just tested) but there are probably much 'prettier' ways of doing it. Change "A1" to wherever you want the result to appear.

Alastair

[olympiad]

With the limit of 7 nested function on the formula below, I am still
struggling with a solution how to add several more for the purpose of my
workbook. The aim of this is that I have for example in cell B5 a variety of
abreviations (codes) that I would like to appear in another cell with as a
full description.

"Olympiad" wrote:

> I have the following formula working well, however as there is a limit of 7
> nested functions and need some more, what is my options?
>
> =IF(B5="P-NE","Pre App Not Endorsed",IF(B5="P-PI","Pre App Pending
> Info",IF(B5="P-WD","Pre App
> Withdrawn",IF(B5="WFIN","Withdrawn",IF(B5="PREP","App Being
> Prepared",IF(B5="PFI","App Pending Further Info",IF(B5="PDD","App Pending D&D
> report",IF(B5="PID","App",))))))))

[mangesh_yadav]

Hi olympiad,

As Niek Otten replied, use vlookup. Lets say you save your list of codes and their description in a table in range A1:B10. e.g.
Col A | Col B
P-NE | Pre App Not Endorsed
P-PI | Pre App Pending Info
P-WD | Pre App Withdrawn
and so on...

In your formula use:
=VLOOKUP(B5,A1:B10,2)


Mangesh