Formula to select all from drop down list and display in table

[excelnewbielearning]

Hello,

Does anyone know how I can select all my items from a drop-down list and have them displayed along a column with a repetition of the time next to them?
So far I have created a drop-down list where I can only select 1 product from the list, I wanted to be able to have an option to select 'All', but not sure how to go about approaching this problem.

I have provided an example file to explain better. Any help would be appreciated.

Thank you

Best wishes,

 

[Chihiro]

My approach will depend on Excel version you have access to.

1. If Excel 2016, Office 365 or 2010. Instead of using formula I'd recommend using PowerQuery.

2. I'd combine dropdown with small vba if you don't have access to PowerQuery. Though it would require you to add additional dropdown item (say "All").

Do you have PQ?

 

[chirayu]

I've attached a sample file. Created as future proof so that if more products or more hours added then should still work

 

[excelnewbielearning]

My approach will depend on Excel version you have access to.

1. If Excel 2016, Office 365 or 2010. Instead of using formula I'd recommend using PowerQuery.

2. I'd combine dropdown with small vba if you don't have access to PowerQuery. Though it would require you to add additional dropdown item (say "All").

Do you have PQ?

I'm using Excel 2016 and I don't have PowerQuery.

I was hoping to use a formula as I have no knowledge of VBA.

 

[Chihiro]

If you are using Excel 2016. Power Query comes standard. It's called Get & Transform and found in data tab.

See sample attached. Only draw back is that you need to right click and refresh after changing dropdown (you could add small code like below, it's added in the sample).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B1]) Is Nothing Then
    ActiveSheet.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
End If
End Sub

 

[excelnewbielearning]

If you are using Excel 2016. Power Query comes standard. It's called Get & Transform and found in data tab.

See sample attached. Only draw back is that you need to right click and refresh after changing dropdown (you could add small code like below, it's added in the sample).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B1]) Is Nothing Then
    ActiveSheet.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
End If
End Sub

Thanks for that,

but for some reason, my avails are not showing in the tables when I select all. But does show when I select an individual item from the drop-down.

 

[Chihiro]

Hmm working fine on my end. Is the issue on my sample file? Or on your actual?

For reference, here's entire M code used for this. You can see where custom column is added (#"Added Custom1") and vFilter variable is used in TRUE/FALSE flag (for filtering purpose).

let
    vFilter = Excel.CurrentWorkbook(){[Name="lstID"]}[Content]{0}[Column1],
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product list", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Hours", each Table3),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Hours", "Custom"}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns", "Custom", {"Hours"}, {"Hours"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Hours", type time}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Product list"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Errors", "Custom", each if vFilter = "All" then vFilter = "All" else [Product list] = vFilter),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [Custom] = true),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

 

[excelnewbielearning]

I'm having the issue on the sample file, when I select all, the different availability numbers do not seem to show in the table display space.

I'm going to see if I can follow the steps you have taken on my actual file. Is it important to note that the hours and product ids and placed in two different worksheets on my actual?

 

[Chihiro]

Not really. PowerQuery/Get&Transform handles Excel table import using table name space, and sheet name is irrelevant, as long as table is in the same workbook.

Excel.CurrentWorkbook(){[Name="Table2"]}[Content]

 

[excelnewbielearning]

Is there any other work around this problem? I haven't yet managed to get this to work.

 

[NARAYANK991]

Hi ,

Is this what you wanted ?

Narayan

 

[excelnewbielearning]

Hi ,

Is this what you wanted ?

Narayan

Hello,

Yes, Thank you.

My option 'All' is showing up on multiple rows first and then all my options from the drop-down straight underneath.

And for some reason when i select an individual option, the hours don't show from 00:00 - 23:00, but are appearing from 02:00 - 23:00. It misses out the first 2 hours. Not too sure how i managed that.

 

[NARAYANK991]

Hello,

Yes, Thank you.

My option 'All' is showing up on multiple rows first and then all my options from the drop-down straight underneath.

And for some reason when i select an individual option, the hours don't show from 00:00 - 23:00, but are appearing from 02:00 - 23:00. It misses out the first 2 hours. Not too sure how i managed that.

Hi ,

Can you upload a workbook which has these problems ?

Narayan