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..
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
1.JPG
2.JPG
 
Upvote 0
Nobody will be able to access images on your desktop.
Try and copy then paste text from your sheet.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top