Drop Down range to adjust automatically

[Legacy 287389]

Hi good people!,

I have searched but really cannot seem to find. I have numbers 1-100 ranging A1-A100. Then numbers 1-70 ranging B1-B70. In cell D1 I enter values 1 or 2. So, in Cell C1 I have a formula: If(D$1=1,A1,If(D$1=2,B1,"")). This formula is copied down to C100. C1-C100 is the actual drop down list values. The drop down list box is in E1. If I select "2" in D1, the values in column C obviously change to those in column B. If I look at the dropdown now, the list displays the blanks after value 70. How can I have the range adjust to ignore the blanks?. All and any help will be accepted with great humbleness and appreciation. Thank you all..

 

[excelmane]

juriemagic,

here is what will help you:
1) Use this formula in cell G1 : =IF(D1=1;"Sheet1!A1:A"&COUNTA(A:A);"Sheet1!B1:B"&COUNTA(B:B))
2) Now select cell G1 and the Go to Formulas ->Name Manager ,name the range as TEST and "Refers To..." should be =INDIRECT(Sheet1!$G$1).
3) Then Go to cell E1. Atl D L (data validation) then List and in Source just type =TEST. You will notice that the drop down is dynamic when cell D1 is changed from 1 to 2.

If cell D1 is 1 then the G1 will display "Sheet1!A1:A100" Else Sheet1!B1:B58 (whatever entries in column B).
Then the INDIRECT function will change this text to a cell reference. So when you change the cell D1 to 2 the named range TEST will be Sheet1!B1:B58, the named range TEST will be adjusted accordingly and the same will reflect in the drop down.

Cheers,,,

 

[Legacy 287389]

I have done it all and all works great!!!!..Thank you very kindly. I do appreciate all your efforts..