Dynamic List from other Sheets - Auto adjustment if row/column inserted in others sheets

Hello Guys,

I have a challenge for you. Here is the situation:

I would like to have in let's call it Main Sheet, values that come from different Sheets. Let's call them Sheet2, Sheet3 etc.

The idea is to have a drop-down list in Main Sheet where I can select the other Sheet names and then the data appears (with some INDEX/MATCH).

I have used INDIRECT to refer to another Sheet. But the problem comes with the non dynamic part of INDIRECT namely the range.

What I would like is these ranges/cells to be dynamic so if I insert a row or column in another Sheet, it will automatically change, keeping the same data in Main Sheet.

Here is the formula:

IFERROR(INDEX(INDIRECT("'"&$B$2&"'!$B$3:$B$6"),MATCH(INDIRECT("'"&$B$2&"'!$A"&ROW(3:3)),INDIRECT("'"&$B$2&"'!$C$3:$C$6"),0)),"")

If I insert a column in Sheet 2 before where the formula looks at to extract data (so $B$3:$B$6 for example), it will not understand because range will be locked while the actual data range would have moved to column C.

I don't know if it is possible to do it via Excel without VBA as it seems quite complex. I have attached a sample.

Let me know your thoughts

Many thanks

You could use Named Ranges on Sheet1 and Sheet2 and refer to them in the INDIRECT function. The Named Ranges would be "dynamic" when a row or column is inserted.

In this example, the named ranges are called Sheet1rng and Sheet2rng and encompass A3:C6 on each sheet. Using the Sheet's name at the beginning of the named range makes it convenient to reference the B2 dropdown value and add "rng" to the name.

Formula:

=INDEX(INDIRECT($B$2&"rng"),MATCH(INDEX(INDIRECT($B$2&"rng"),ROW(1:1),1),INDEX(INDIRECT($B$2&"rng"),0,3),0),2)

To refer to cell in row 3 column B of a dynamic sheet name in B2, why not? (No helper, ofcourse):

=INDIRECT("'"&$B$2&"'!$B"&ROW(3:3))

To avoid "Zero" from blank cell, try:

=IF(INDIRECT("'"&$B$2&"'!$B"&ROW(3:3))=0,"",INDIRECT("'"&$B$2&"'!$B"&ROW(3:3)))

Drag down as far as probably need.