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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Toadstool

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
600
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,404
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,404
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,095,231
Messages
5,443,262
Members
405,220
Latest member
gtgaabaron

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top