Returning a Defined Table in a Formula

[mistryman]

Good Afternoon

Right now when I try this formula I am able to drag down and across and return my desired table when I change cell B47.

=IFERROR(IF($B$47=2,Table2,IF(B47=3,Table3,"")),"" )


However I have 30+ tables and a nested if wont be able to handle them. Any other suggestions on how I could get this done using another formula?

I tried vlookup and index match with no avail.

Thanks

 

[prasaddn]

hi,

How about concatenate?
="Table" & b47

Regards,
Prasad DN
PS: pls share the further formula or applicability of result so that we can give better solution.

 

[mistryman]

Hi Prasad

I have 30 tables across 30 sheets. I would like to be able to pull up a table when I change cell B47.

I just type =Table2 into a cell A3 it will pull up A3 in Table2 I want to be able to dynamically change it.

 

[Mike H..]

Hi Prasad

I have 30 tables across 30 sheets. I would like to be able to pull up a table when I change cell B47.

I just type =Table2 into a cell A3 it will pull up A3 in Table2 I want to be able to dynamically change it.

Hi,

What exactly are Table2 and Table3 in this context?

The only way I can get this to work, and it may answer your question, is to select a number of cells, that number being the same size as or larger than any of your tables and then paste this into the formula bar:-


=(INDIRECT("Table" & B47)

If you now hold down the CTRL + Shift and tap enter then you get the table number that is entered into B47. i.e. if we have a 2 in B47 we get Table2. Change B47 and we get a different table.

You can wrap the formula in IFERROR but that will only trap an invalid table number it won't prevent errors if you entered the formula in a number of cells that is larger than your table.

=IFERROR(INDIRECT("Table" & B47),"")

 

[jeffreyweir]

This also sounds like a job for the CHOOSE function. I've answered something similar somewhere by using CHOOSE.

Can you upload a sample worksheet?