Automatically typing the whole list by the list name

[Shail216]

Hi there,

I have stucked in the above said. I have named the lists. Like 12 different training given in April month, I named the training list by "April". Now I want to show it like if I select "April" in a drop down list, the names of all training should come in next or desired cells by list view.

Can you help me please with any formula in excel. ( I don't know anything about VBA.)

Thanks in advance..

 

[Toadstool]

Please supply an example of your data.

 

[Shail216]

 

[Toadstool]

Nobody will be able to access images on your desktop.
Try and copy then paste text from your sheet.

 

[Shail216]

Below is the list of trainings named as April. These trainings are done to be in April month.
[TABLE="width: 251"]
<colgroup><col></colgroup><tbody>[TR]
[TD]

Basic electronics trouble shooting[/TD]
[/TR]
[TR]
[TD]Basic Excel[/TD]
[/TR]
[TR]
[TD]Basic Hydraulics trouble shooting[/TD]
[/TR]
[TR]
[TD]Basic Computer operation[/TD]
[/TR]
[TR]
[TD]Root Cause Analysis[/TD]
[/TR]
[TR]
[TD]Kaizens[/TD]
[/TR]
[TR]
[TD]Poka Yoke[/TD]
[/TR]
[TR]
[TD]Preventive Maintenance[/TD]
[/TR]
[TR]
[TD]Predictive Maintenance[/TD]
[/TR]
[TR]
[TD]RO Operation & Maintenance[/TD]
[/TR]
[TR]
[TD]OHSAS[/TD]
[/TR]
[TR]
[TD]5 S Training


[/TD]
[/TR]
</tbody>[/TABLE]

I have listed the months in "Data validation", and I need to select one of the months from list. I need the above trainings to come row wise in next cell where I select the month.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try

=IFERROR(INDEX(INDIRECT(A$1),ROWS(B$1:B1)),"")

Before copying down at least as far as your longest list:

- Replace A$1 with the cell with the Data validation
- Replace B$1 and B1 with the first cell that this formula is placed in.

 

[Shail216]

Thanks Peter_SSs,

You did the job man...

 

[Peter_SSs]

Thanks Peter_SSs,

You did the job man...

You're welcome. Thanks for the follow-up.

 

[Shail216]

Also I need to punch the dates in the next cell like I have the data below:

[TABLE="width: 325"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Basic electronics trouble shooting[/TD]
[TD="align: right"]1-Apr-2019[/TD]
[/TR]
[TR]
[TD]Basic Excel[/TD]
[TD="align: right"]11-Apr-2019[/TD]
[/TR]
[TR]
[TD]Basic Hydraulics trouble shooting[/TD]
[TD="align: right"]13-Apr-2019[/TD]
[/TR]
[TR]
[TD]Basic Computer operation[/TD]
[TD="align: right"]23-Apr-2019[/TD]
[/TR]
[TR]
[TD]Root Cause Analysis[/TD]
[TD="align: right"]3-Apr-2019[/TD]
[/TR]
[TR]
[TD]Kaizens[/TD]
[TD="align: right"]5-Apr-2019[/TD]
[/TR]
[TR]
[TD]Poka Yoke[/TD]
[TD="align: right"]9-Apr-2019[/TD]
[/TR]
[TR]
[TD]Preventive Maintenance[/TD]
[TD="align: right"]15-Apr-2019[/TD]
[/TR]
[TR]
[TD]Predictive Maintenance[/TD]
[TD="align: right"]15-Apr-2019[/TD]
[/TR]
[TR]
[TD]RO Operation & Maintenance[/TD]
[TD="align: right"]17-Apr-2019[/TD]
[/TR]
[TR]
[TD]OHSAS[/TD]
[TD="align: right"]19-Apr-2019[/TD]
[/TR]
[TR]
[TD]5 S Training[/TD]
[TD="align: right"]26-Apr-2019[/TD]
[/TR]
</tbody>[/TABLE]

The trainings are in one column(A1:A12) named as "April" and dates are in (B1:B12) without any name.

 

[Peter_SSs]

Unless it would cause a problem elsewhere, it might be better to have "April" refer to both columns (& similar for the other months). If you want to pursue that then post back about it. Otherwise, try

=IF(B1="","",OFFSET(INDEX(INDIRECT(A$1),ROWS(C$1:C1)),0,1,1,1))

Where
- B1 is the cell with the first formula I suggested before
- A$a is the DV cell
- C$1 & C1 are the first cell with this formula.