indirect - dynamic range

methody

Well-known Member
Joined
Jun 17, 2002
Messages
845
I have a base sheet. In a particular cell (B1) I have a data validaton list from which I can select the name of 4 other sheets. In each of these sheets the key range is C6 through to C26. When I select the sheet name from B1 I want the data validation cell in B4 to be populated by the appropriate cells in the range C6 to C26. I am able to do this but if only the cells C6 to C8 are filled I only want them to appear in the data validation cell.
I presume it is a combination of INDIRECT and DYNAMIC ranges using offset but I can't get it to work.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,137,330
Messages
5,680,857
Members
419,936
Latest member
rphill48

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top