Automatically typing the whole list by the list name

Shail216

New Member
Joined
Nov 6, 2019
Messages
5
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..
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
732
Office Version
2016
Platform
Windows
Please supply an example of your data.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
732
Office Version
2016
Platform
Windows
Nobody will be able to access images on your desktop.
Try and copy then paste text from your sheet.
 

Shail216

New Member
Joined
Nov 6, 2019
Messages
5
Below is the list of trainings named as April. These trainings are done to be in April month.


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



<colgroup><col></colgroup><tbody>
</tbody>

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,729
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 6, 2019
Messages
5
Thanks Peter_SSs,

You did the job man...:cool:
 

Shail216

New Member
Joined
Nov 6, 2019
Messages
5
Also I need to punch the dates in the next cell like I have the data below:

Basic electronics trouble shooting1-Apr-2019
Basic Excel11-Apr-2019
Basic Hydraulics trouble shooting13-Apr-2019
Basic Computer operation23-Apr-2019
Root Cause Analysis3-Apr-2019
Kaizens5-Apr-2019
Poka Yoke9-Apr-2019
Preventive Maintenance15-Apr-2019
Predictive Maintenance15-Apr-2019
RO Operation & Maintenance17-Apr-2019
OHSAS19-Apr-2019
5 S Training26-Apr-2019

<colgroup><col><col></colgroup><tbody>
</tbody>

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,729
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,737
Messages
5,470,444
Members
406,699
Latest member
perfectioncts

This Week's Hot Topics

Top