# Nesting in excel exceeding limits

#### crachic

##### New Member
I have a spreadsheet where I have around 90 selections to choose from depending on what number is selected in the cell. I have one tab labeled "Ministers" where i keep the data. Here is a quick example of what it looks like.

Column A Column B
1 John Doe
2 Jane Doe
3 Suzie Doe

All the way up to 90 or so, all with different names.

My other tab is labeled Schedule which has columns were these name will be entered.

Instead of typing these names over and over again on the schedule tab, I would like to setup a separate tab (currently it is on the same tab called Schedule to make it easier) where I input a number and the name populates in the cell. For example, if I type the number 1, then it should input John Doe, if I type in number 2, then it would input Jane Doe.

I have this currently setup to work with values up to 9. After I try to enter the 10th one, it gives me a message where the nesting has exceeded its limit for the format type.

On the tab called "Schedule" I have three columns where I need these name to populate and the numbers I am inputting is just out to the right.

Once I get the nesting limit figured out, I will move the numbers to a separate tab.

One other thing to mention. Each cell needs to have the ability to take a name that corresponds with a number of 1-90. I will just copy them out to the other cells.

How can I get around the nesting limit.

Below is what I have working currently up to 9 nestings.

=IF(K3=1,Ministers!\$B\$1,IF(K3=2,Ministers!\$B\$2,IF(K3=3,Ministers!\$B\$3,IF(K3=4,Ministers!\$B\$4,IF(K3=5,Ministers!\$B\$5,IF(K3=6,Ministers!\$B\$6,IF(K3=7,Ministers!\$B\$7,IF(K3=8,

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Michael M

##### Well-known Member
Have a look at using a VLOOKUP formula instead.
Taking a rough stab at what you have provided, it would be something like
Code:
``=VLOOKUP(K3,Ministers!\$A\$1:\$B\$1000,2,0)``

Change the ranges to suit, but maintain the \$ signs
Copy down as far as required

#### crachic

##### New Member
I am trying this and I am having some issues with format types. Is there a specific format type that is needed with the VLOOKUP formula. General? Text? etc?

Have a look at using a VLOOKUP formula instead.
Taking a rough stab at what you have provided, it would be something like
Code:
``=VLOOKUP(K3,Ministers!\$A\$1:\$B\$1000,2,0)``

Change the ranges to suit, but maintain the \$ signs
Copy down as far as required

#### Oeldere

##### Well-known Member
You get a failure or what?

If so, please tell us what the failure is.

The syntax of the formula is OKE.

So what is in cell K3 and is that same name (as you have in cell K3) also availabe in column A in sheet Ministers?

OK now i see:

K3=1 (value)

is column A in sheet Ministers the same value (or is it stored as text)?

Last edited:

#### crachic

##### New Member
I set all cells to text. This includes the cell that has the formula that gets filled out, the cell that gets the number entered (K3) and both cells on the Ministers list (which has one column with the number and the other column that has the name).

Overall it works but when I got and "edit" the formula again, it does not save the formula as a formula, it saves the fomula as text.

As I am writing this, i believe the right formats are the following:
Ministers list (both Columns) - text
The K3 cell - text
The cell that has teh formula - formula format.

Does this sound right?

You get a failure or what?

If so, please tell us what the failure is.

The syntax of the formula is OKE.

So what is in cell K3 and is that same name (as you have in cell K3) also availabe in column A in sheet Ministers?

OK now i see:

K3=1 (value)

is column A in sheet Ministers the same value (or is it stored as text)?

#### Oeldere

##### Well-known Member
Code:
``Overall it works but when I got and "edit" the formula again, it does not save the formula as a formula, it saves the fomula as text.``

a guess => did you forget the = sign before the formula?

#### crachic

##### New Member
The + is there. I think what happened is when I was playing with the formats and changed it to text, then went to edit the formula, it kept it as text. I will play with it some more and see if I can get it to work the way I want. I believe I am on the right direction with the VLOOKUP. I will be looking at it again over the next few days. Thanks for your time so far.

Code:
``Overall it works but when I got and "edit" the formula again, it does not save the formula as a formula, it saves the fomula as text.``

a guess => did you forget the = sign before the formula?

#### crachic

##### New Member
Sorry. I meant the equals sign...not the plus sign.

The + is there. I think what happened is when I was playing with the formats and changed it to text, then went to edit the formula, it kept it as text. I will play with it some more and see if I can get it to work the way I want. I believe I am on the right direction with the VLOOKUP. I will be looking at it again over the next few days. Thanks for your time so far.

Replies
3
Views
2K
Replies
3
Views
290
Replies
1
Views
192
Replies
6
Views
374
Replies
4
Views
196

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back