• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Display Data as per the TWO drop down Choices

D

Deepak Sharma

Member
Hello All,

I have two drop-down lists and as per the selection, I need to display the data in Sheet 1 from Sheet 2.
Apart from PIVOTS, Is there any formulae to do this? Kindly help.

Sheet attached!

Regards,
Deepak
 

Attachments

  • Display1.xlsm
    15.8 KB · Views: 3
Try,

In G2, copied across and down :

=IFERROR(INDEX(Sheet2!B$1:B$53,AGGREGATE(15,6,ROW(Sheet2!$B$1:$B$53)/(Sheet2!$C$1:$C$53=$C$1)/(TEXT(Sheet2!$B$1:$B$53,"mmm")=$E$1),ROWS($1:1))),"")

Regards
Bosco
 

Attachments

  • Display1(1).xlsm
    16.2 KB · Views: 5
Last edited:
Typically this is best done using Advanced Filter Copy.

See link for detailed tutorial.
http://www.contextures.com/xladvfilter01.html

I'd also combine it with small VBA to transfer dropdown selection to criteria range etc.

If you need to do it with formula... you need to set up enough ranges with formula in advance, in order for it to pull all matching data. Depending on your data size, this may hamper workbook performance.

Formula would be... something like.
=IFERROR(INDEX(Sheet2!$B$2:$B$53,SMALL(IF((Sheet2!$B$2:$B$53>=($E$15&" 1")*1)*(Sheet2!$B$2:$B$53<=EOMONTH(($E$15&" 1")*1,0))*(Sheet2!$C$2:$C$53=$C$15),Sheet2!$A$2:$A$53),ROWS($1:1))),"")

However, if you want to pull "All" as indicated in C2... then you need to nest it in IF statement (checking for C2="All") and requires formula to be set up for range matching table size.

I wouldn't recommend this route.
 
Hi Guys! Thank you so much, Bosco and Chihiro for your valuable times! It was wonderful!

@Bosco - I have edited some cell references in your formula as per this sheet but don't know why not showing the accurate data however it was showing absolutely fine on the previously attached sheet! If you could please correct it on this sheet also.
I also need to search for All containers & All Months, what to do in that condition in same formula? Please advice.

@Chihiro - That tutorial was very good. Can you please suggest how to do this with VBA coding?

Thanks and Regards,
Deepak Sharma
 

Attachments

  • Track_OrderIntent_vs_OrderEntry -- Deepak.xlsm
    22 KB · Views: 1
Deepak Sharma said:
Hi Guys! Thank you so much, Bosco and Chihiro for your valuable times! It was wonderful!

@Bosco - I have edited some cell references in your formula as per this sheet but don't know why not showing the accurate data however it was showing absolutely fine on the previously attached sheet! If you could please correct it on this sheet also.
I also need to search for All containers & All Months, what to do in that condition in same formula? Please advice.

@Chihiro - That tutorial was very good. Can you please suggest how to do this with VBA coding?

Thanks and Regards,
Deepak Sharma
Click to expand...

1] Your "Data" sheet layout is different from post #.1

2] The revised formula in "Calcs" sheet I2, copied across and down :

=IFERROR(INDEX(Data!$B$1:$H$53,AGGREGATE(15,6,ROW(Data!$B$1:$B$53)/(Data!$D$1:$D$53=$C$1)/(TEXT(Data!$B$1:$B$53,"mmm")=$E$1),ROWS($1:1)),MATCH(I$1,Data!$B$1:$H$1,0)),"")

3] See revised attachment

Regards
Bosco
 

Attachments

  • Track_OrderIntent_vs_OrderEntry -- Deepak(1).xlsm
    21.7 KB · Views: 3
Thanks, Bosco, Yes that's work now.

Can we display data for "ALL" & "All" Also? If it could be possible. :)

Thanks & Regards,
Deepak Sharma
 
Amazing Boss !!!!! Seriously awesome work :) .....I couldn't even think that everything I asked is possible without VBA.
But you were like anything.
Can you please advise from where to and how to achieve this level of excellence in excel ??

Thanks & Regards,
Deepak Sharma
 
As for VBA for Advanced Filter.

First use macro recorder to record advanced filter operation. It will generate something like below.
Code: 
Sub Macro1()
    Sheets("Data").Range("A1:J53").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("S1:U2"), CopyToRange:=Range("I1:L1"), Unique:=False
End Sub

Now, to make it more dynamic. Use sheet codename and last row calculation and update like below.
Code: 
Sub AdvFilter()
Dim lRow As Long
With Sheet2
    lRow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("A1:J" & lRow).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("S1:U2"), CopyToRange:=Range("I1:L1"), Unique:=False
End With
End Sub

Now to set up event trigger code in "Calcs" sheet... I've added hard coded month name array and month start date conversion array.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mArr, fArr
Dim mNum As Long
Application.EnableEvents = False
Range("I2:L50000").Clear

mArr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
fArr = Array("1/1/2018", "2/1/2018", "3/1/2018", "4/1/2017", "5/1/2017", "6/1/2017", "7/1/2017", _
            "8/1/2017", "9/1/2017", "10/1/2017", "11/1/2017", "12/1/2017")
If Not Intersect(Target, Union(Range("C1:C3"), Range("E1:E3"))) Is Nothing Then
    If [C1].Value = "All" Then
        [S2] = ""
    Else
        [S2] = [C1]
    End If
    If [E1].Value = "All" Then
        Range("T2:U2") = Array("", "")
    Else
        mNum = Application.Match([E1].Value, mArr, 0) - 1
        [T2].Value = ">=" & fArr(mNum)
        [U2].Value = "<=" & DateSerial(Year(fArr(mNum)), Month(fArr(mNum)) + 1, Day(fArr(mNum)) - 1)
    End If
    Call AdvFilter
End If
Application.EnableEvents = True
End Sub

See attached.

Edit: I assumed you wanted fiscal year (i.e. Apr 2017 to Mar 2018).
 

Attachments

  • Track_OrderIntent_vs_OrderEntry -- VBA.xlsm
    727.1 KB · Views: 3
Thank you so much, Chihiro...Awesome briefing !!

Just a little issue...It's working fine for C2, however when selecting months in E2...It's not showing data month wise. Else it's working fine.
If you could please check it again.


Thanks & Regards,
Deepak Sharma
 
Then just change dates inside Array to dd/mm/yyyy format instead of mm/dd/yyyy format.
Or use DateSerial function.
Ex: DateSerial(2018,1,1), DateSerial(2017,12,1)...
 
I have changed dates to dd/mm/yyyy but still is not showing anything...Do I need to alter anywhere else?
Please see attached file.

Regards,
Deepak
 

Attachments

  • A-- VBA.xlsm
    731.3 KB · Views: 2
Just use DateSerial, it's more flexible method and will accommodate for any date formats used by the system.

Tested on my end without any issue.
 

Attachments

  • A-- VBA.xlsm
    726.2 KB · Views: 3
Thanks !! But I don't know what the issue is with my system. only when I am selecting months, then nothing is showing. Otherwise, it's working fine for All.

Thanks for your efforts dear!

Regards,
Deepak Sharma
 
What does criteria field show when you select month?
You should see, >=1/4/2017, <=30/4/2017...
If you select Apr.

upload_2017-12-11_8-41-29.png
 
Hi Chihiro,
So sorry for the late reply, I was on leave actually.

Yes Exactly this is displaying in criteria >=1/4/2017, <=30/4/2017

But Data is not displaying in any column. Sheet attached again after selecting Apr.

Thanks and Regards,
Deepak Sharma
 

Attachments

  • A-- VBA (1).xlsm
    729.5 KB · Views: 2
Hmm, issue isn't with code or workbook. Check your Excel and System's regional setting.

When I download your file and just change dropdown. I get data for each month correctly.
 
You must log in or register to reply here.
Back
Top