Nesting in excel exceeding limits

[crachic]

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,

 

[Michael M]

Have a look at using a VLOOKUP formula instead.
Taking a rough stab at what you have provided, it would be something like

=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]

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

=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]

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)?

 

[crachic]

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]

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]

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.

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]

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.