Data against a drop down in adjacent column

[bizlife]

Hi,


I have a drop down consisting of two items- Month and Quarter. When month is selected, list of months is to be displayed in the adjacent column and when quarter is selected, list of quarter should be displayed. I have defined both the lists in other columns which I will eventually hide. I have been able to achieve the results using IF and vlookup. Is there any more efficient way to do the same.


Sample file is attached.


Thanks in advance.

 

[NARAYANK991]

Hi ,

This is another method ; whether it is more efficient or not , I do not know.

1. Two named ranges called MONTH and QUARTER have been defined ; these must match the two values that can be selected using the dropdown.

2. The formula in the output range is =INDIRECT(I5) entered using CTRL SHIFT ENTER after selecting all 12 cells in the output range.

3. Conditional Formatting has been used to hide the error values that will be displayed when QUARTER is selected , since there are only 4 quarters , whereas the formula has been entered in 12 cells.

Narayan

 

[bizlife]

Hi ,

This is another method ; whether it is more efficient or not , I do not know.

1. Two named ranges called MONTH and QUARTER have been defined ; these must match the two values that can be selected using the dropdown.

2. The formula in the output range is =INDIRECT(I5) entered using CTRL SHIFT ENTER after selecting all 12 cells in the output range.

3. Conditional Formatting has been used to hide the error values that will be displayed when QUARTER is selected , since there are only 4 quarters , whereas the formula has been entered in 12 cells.

Narayan

Thanks..this was a better way.

 

[p45cal]

See attached (and you're no longer limited to having your results in the same row as the source cells).

 

[bizlife]

See attached (and you're no longer limited to having your results in the same row as the source cells).

Thank you.

 

[bosco_yip]

See attached (and you're no longer limited to having your results in the same row as the source cells).

Similar to p45cal's formula, just added "&"" " behind the formula but without helper

In K5, copy down to K16

=IF(I$5="Month",D5,D17)&""

Regards
Bosco