Drop Down menu based on the month

JamieLee2k

New Member
Joined
May 9, 2004
Messages
38
I will try to explain this the best way I can.
So there is a game called Animal Crossing and in the game you can catch Bugs and Fish, I am looking to make an excel sheet where I can choose from a drop down menu for a certain month so only the relavent fish or bugs show up so I know what is available for my specific month.

Lets narrow this down and give you an example of how it would look

Above are examples of how it will be layed out but what I need to do is add multiple months to each bug and then I can filter them to only display based on the month I select, a common Bluebottle is available from April-August and a Paper Kite Butterfly is available from Jan-Dec and a greate purple emperor from May-August, Since there are multiple months for each bug I want a drop down so say I select April it will only select bugs for april, I can do the drop down menu what I can't do is create multiple months and only select them
 

Attachments

  • bugs 2.jpg
    bugs 2.jpg
    29.9 KB · Views: 19
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
557
Office Version
  1. 365
Platform
  1. Windows
You didn't mention what version Excel you're running. 365 has a new function that would let you enter in this one formula:
=FILTER(animals,INDEX(tableofchecks,,MATCH(dropdown,monthheaders)="✓")
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
557
Office Version
  1. 365
Platform
  1. Windows
If you format as a table, you can click the filter arrow and select the check. In attached example filtered for April.
 

Attachments

  • table filter example.png
    table filter example.png
    71 KB · Views: 3

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Greetings JamieLee2k and All

I think you Need to use "INDEX" Function, If so Please Name cells Range below Month with Name to Use "INDIRECT" function
and to be copy and Paste Function if your Table similar to Below Table then Press Ctrl +Shift + Enter or it will not works, to be Between { }
"=IFERROR(INDEX($A$2:$P$5;SMALL(IF(INDIRECT($A$7)=$B$7;ROW($A$2:$A$5)-1;"");ROW(A1));1);"")"

and this to Save Time for Name Range Titled with Months Name
VBA Code:
Sub To_Name_Ranges()

For Each Rng In ActiveSheet.Range("E1:P1")
ActiveWorkbook.Names.Add Name:=Rng.Value, RefersTo:="=" & Rng.Offset(1, 0).Resize(4, 1).Address

Next
End Sub
VBA Code:
=IFERROR(INDEX($A$2:$P$5;SMALL(IF(INDIRECT($A$7)=$B$7;ROW($A$2:$A$5)-1;"");ROW(A1));1);"")
 

Attachments

  • Bugs.gif
    Bugs.gif
    197.4 KB · Views: 2

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Here I Used XLBB the Last Right Button


match.xlsm
ABCDEFGHIJKLMNOP
1BugsJanFebMarAprMayJunJulAugSepOctNovDec
2Common bluebottle300Flying4 AM - 7 PM-------
3Paper kite butterfly1,000Flying8 AM - 7 PM
4Great purple emperor3,000Flying4 AM - 7 PM--------
5Monarch butterfly140Flying4 AM - 5 PM
6
7Jan
8Paper kite butterfly
9Monarch butterfly
10 
11 
4
Cell Formulas
RangeFormula
A8:A11A8=IFERROR(INDEX($A$2:$P$5,SMALL(IF(INDIRECT($A$7)=$B$7,ROW($A$2:$A$5)-1,""),ROW(A1)),1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Apr='4'!$H$2:$H$5A8:A11
Aug='4'!$L$2:$L$5A8:A11
Dec='4'!$P$2:$P$5A8:A11
Feb='4'!$F$2:$F$5A8:A11
Jan='4'!$E$2:$E$5A8:A11
Jul='4'!$K$2:$K$5A8:A11
Jun='4'!$J$2:$J$5A8:A11
Mar='4'!$G$2:$G$5A8:A11
May='4'!$I$2:$I$5A8:A11
Nov='4'!$O$2:$O$5A8:A11
Oct='4'!$N$2:$N$5A8:A11
Sep='4'!$M$2:$M$5A8:A11
Cells with Data Validation
CellAllowCriteria
A7List=$E$1:$P$1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
I am running 2013 version
Can you please update your account details to show this, as it affects which functions you can use. Thanks

Another option, without volatile functions, or named ranges.
+Fluff New.xlsm
ABCDEFGHIJKLMNOP
1BugsJanFebMarAprMayJunJulAugSepOctNovDec
2Common bluebottle300Flying4 AM - 7 PM------
3Paper kite butterfly1000Flying8 AM - 7 PM-
4Great purple emperor3000Flying4 AM - 7 PM--------
5Monarch butterfly140Flying4 AM - 5 PM
6
7Apr
8Common bluebottle
9Paper kite butterfly
10Monarch butterfly
Sheet1
Cell Formulas
RangeFormula
A8:A10A8=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-ROW($A$2)+1)/($E$1:$P$1=$A$7)/($E$2:$P$5=$B$7),ROWS(A$8:A8))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,604
Messages
5,625,755
Members
416,133
Latest member
ToseSenpai

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
Top