Can I make this table have a slicer?

Outdoorsman80

Outdoorsman80

Board Regular
Joined
Oct 4, 2014
Messages
62
Office Version
  1. 365
Platform
  1. Windows
I have a "Dashboard" worksheet for displaying totals for Harvest, eggs, meat, etc. This table querys other tables in other worksheets and puts it into a nice viewable table. It works, but every year I need to make a new table and update the formulas - Not a big deal, but I'd like to be able to create something along the lines of a slicer or a button for each year and that table updates.

Here's the table:
2023
-SumTotal CostSide Money?
Total Eggs Harvested1423$ -$ -
Egg Sales Total86,tblOsOrganicsSales[Produce], {"Eggs"}, tblOsOrganicsSales[Date],">="&DATE(2023,1,1), tblOsOrganicsSales[Date], "<="&DATE(2023,12,31)))|nf:_($* #,##0.00_);_($* (#,##0.00);_($* \0022-\0022??_);_(@_)]$ 252.25$ -
Meaty Food Total9,tblItems[Work Done / Item], {"Grain - Broiler Crumbles"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))|nf:_($* #,##0.00_);_($* (#,##0.00);_($* \0022-\0022??_);_(@_)]$ 232.44$ 232.44
Egg Layer Grain Total10,tblItems[Work Done / Item], {"Grain"}, tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31)))|nf:_($* #,##0.00_);_($* (#,##0.00);_($* \0022-\0022??_);_(@_)]$ -$ -
Broilers Total50 (4 died, 46 Processed),tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))|nf:_($* #,##0.00_);_($* (#,##0.00);_($* \0022-\0022??_);_(@_)]$ 149.50, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31),tblItems[Side $?],"Yes",tblItems[Description], {"Broiler"}))|nf:_($* #,##0.00_);_($* (#,##0.00);_($* \0022-\0022??_);_(@_)]$ 149.50
Broiler Breast/Tender Total59.31875$ 6.44$ -
Broiler Legs Total53.34375$ 7.16$ -
Broiler Meat Total112.6625$ 3.39$ -
Seed Totals3, tblOOSeedsPurches[Date],">="&DATE(2023,1,1), tblOOSeedsPurches[Date], "<="&DATE(2023,12,31)))|nf:_($* #,##0.00_);_($* (#,##0.00);_($* \0022-\0022??_);_(@_)]$ 18.80, tblOOSeedsPurches[Date],">="&DATE(2023,1,1), tblOOSeedsPurches[Date], "<="&DATE(2023,12,31),tblOOSeedsPurches[Sales Fund],"Yes"))|nf:_($* #,##0.00_);_($* (#,##0.00);_($* \0022-\0022??_);_(@_)]$ 10.50
Pigs1$ 125.00$ -
Pig Butcher Cost1$ 321.93$ -
Pig Feed22,tblItems[Work Done / Item], {"Grain - Pig Pellets"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))|nf:_($* #,##0.00_);_($* (#,##0.00);_($* \0022-\0022??_);_(@_)]$ 242.19$ -
Pig Meat Total150.0845$ 4.59
Seed Totals



Fin.xlsx
ABCD
382023
39-SumTotal CostSide Money?
40Total Eggs Harvested1423$ -$ -
41Egg Sales Total86$ 252.25$ -
42Meaty Food Total9$ 232.44$ 232.44
43Egg Layer Grain Total10$ -$ -
44Broilers Total50 (4 died, 46 Processed)$ 149.50$ 149.50
45Broiler Breast/Tender Total59.31875$ 6.44$ -
46Broiler Legs Total53.34375$ 7.16$ -
47Broiler Meat Total112.6625$ 3.39$ -
48Seed Totals3$ 18.80$ 10.50
49Pigs1$ 125.00$ -
50Pig Butcher Cost1$ 321.93$ -
51Pig Feed22$ 242.19$ -
52Pig Meat Total150.0845$ 4.59
53Seed Totals
OO Dash
Cell Formulas
RangeFormula
B40B40=+SUM(SUMIFS(tblHarvestTotals[Amount],tblHarvestTotals[Produce], {"Eggs"}, tblHarvestTotals[Year],{"2023"}))
B41B41=+SUM(SUMIFS(tblOsOrganicsSales[Amount],tblOsOrganicsSales[Produce], {"Eggs"}, tblOsOrganicsSales[Date],">="&DATE(2023,1,1), tblOsOrganicsSales[Date], "<="&DATE(2023,12,31)))
C41C41=SUM(SUMIFS(tblOsOrganicsSales[Total],tblOsOrganicsSales[Produce], {"Eggs"}, tblOsOrganicsSales[Date],">="&DATE(2023,1,1), tblOsOrganicsSales[Date], "<="&DATE(2023,12,31)))
B42B42=+SUM(SUMIFS(tblItems[Amount],tblItems[Work Done / Item], {"Grain - Broiler Crumbles","Grain - Starter Mash","Grain - Poutry Grower"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
C42C42=SUM(SUMIFS(tblItems[Price],tblItems[Work Done / Item], {"Grain - Broiler Crumbles"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
D42D42=SUM(SUMIFS(Items!$J$2:$J$5098, Items!$D$2:$D$5098,">="&DATE(2023,1,1), Items!$D$2:$D$5098, "<="&DATE(2023,12,31),Items!$M$2:$M$5098,"Yes",Items!$E$2:$E$5098, {"Grain - Broiler Crumbles"}))
B43B43=+SUM(SUMIFS(tblItems[Amount],tblItems[Work Done / Item], {"Grain - Egg Layer"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
C43C43=SUM(SUMIFS(tblItems[Price],tblItems[Work Done / Item], {"Grain"}, tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31)))
D43D43=SUM(SUMIFS(Items!$J$2:$J$5098, Items!$D$2:$D$5098,">="&DATE(2021,1,1), Items!$D$2:$D$5098, "<="&DATE(2021,12,31),Items!$M$2:$M$5098,"Yes",Items!$E$2:$E$5098, {"Grain"}))
B44B44=SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)) &" ("&SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31), tblItems[In Place], "Died")&" died, " &SUM(SUMIFS(tblItems[Amount],tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31), tblItems[In Place], {"Processed"})) &" Processed)"
C44C44=SUM(SUMIFS(tblItems[Price],tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
D44D44=SUM(SUMIFS(tblItems[Price], tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31),tblItems[Side $?],"Yes",tblItems[Description], {"Broiler"}))
B45B45=SUMIFS(tblBroilerTotalPerBird[Bird Total],tblBroilerTotalPerBird[Part], "*Breast", tblBroilerTotalPerBird[Year], "2023") + SUMIFS(tblBroilerTotalPerBird[Bird Total],tblBroilerTotalPerBird[Part], "*Tender", tblBroilerTotalPerBird[Year], "2023")
C45C45=(C44+C42)/B45
B46B46=+SUM(SUMIFS(tblBroilerTotalPerBird[Bird Total],tblBroilerTotalPerBird[Part], {"*Legs"}, tblBroilerTotalPerBird[Year],{"2023"}))
C46C46=(C44+C42)/B46
B47B47=+SUM(SUMIFS(tblBroilerTotalPerBird[Bird Total],tblBroilerTotalPerBird[Year],{"2023"}))
C47C47=(C44+C42)/B47
B48B48=COUNTIFS(tblOOSeedsPurches[Date],">="&DATE(2023,1,1),tblOOSeedsPurches[Date],"<="&DATE(2023,12,31))
C48C48=SUM(SUMIFS(tblOOSeedsPurches[Price], tblOOSeedsPurches[Date],">="&DATE(2023,1,1), tblOOSeedsPurches[Date], "<="&DATE(2023,12,31)))
D48D48=SUM(SUMIFS(tblOOSeedsPurches[Price], tblOOSeedsPurches[Date],">="&DATE(2023,1,1), tblOOSeedsPurches[Date], "<="&DATE(2023,12,31),tblOOSeedsPurches[Sales Fund],"Yes"))
B51B51=+SUM(SUMIFS(tblItems[Amount],tblItems[Work Done / Item], {"Grain - Pig Pellets"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
C51C51=SUM(SUMIFS(tblItems[Price],tblItems[Work Done / Item], {"Grain - Pig Pellets"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
B52B52=+SUM(SUMIFS(tblPigTotalPerPig[Pig Total],tblPigTotalPerPig[Year],{"2023"}))
C52C52=(C49+C50+C51)/B52
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Since I can't edit, here's an updated table. I just combined some of the data tables into one.
Fin.xlsx
ABCD
32023
4-SumTotal CostSide Money?
5Total Eggs Harvested1423$ -$ -
6Egg Sales Total91$ 266.25$ -
7Meaty Food Total9$ 232.44$ 232.44
8Egg Layer Grain Total12$ -$ -
9Broilers Total50 (4 died, 46 Processed)$ 149.50$ 149.50
10Broiler Breast/Tender Total59.31875$ 6.44$ -
11Broiler Legs Total53.34375$ 7.16$ -
12Broiler Meat Total112.6625$ 3.39$ -
13Seed Totals3$ 18.80$ 10.50
14Pigs1$ 125.00$ -
15Pig Butcher Cost1$ 321.93$ -
16Pig Feed22$ 242.19$ -
17Pig Meat Total150.0845$ 4.59
18Seed Totals
OO Dash
Cell Formulas
RangeFormula
B5B5=+SUM(SUMIFS(tblGardenHarvestTotals[Amount],tblGardenHarvestTotals[Produce], {"Eggs"}, tblGardenHarvestTotals[Year],{"2023"}))
B6B6=+SUM(SUMIFS(tblOgsOrganicsSales[Amount],tblOgsOrganicsSales[Produce], {"Eggs"}, tblOgsOrganicsSales[Date],">="&DATE(2023,1,1), tblOgsOrganicsSales[Date], "<="&DATE(2023,12,31)))
C6C6=SUM(SUMIFS(tblOgsOrganicsSales[Total],tblOgsOrganicsSales[Produce], {"Eggs"}, tblOgsOrganicsSales[Date],">="&DATE(2023,1,1), tblOgsOrganicsSales[Date], "<="&DATE(2023,12,31)))
B7B7=+SUM(SUMIFS(tblItems[Amount],tblItems[Work Done / Item], {"Grain - Broiler Crumbles","Grain - Starter Mash","Grain - Poutry Grower"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
C7C7=SUM(SUMIFS(tblItems[Price],tblItems[Work Done / Item], {"Grain - Broiler Crumbles"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
D7D7=SUM(SUMIFS(Items!$J$2:$J$5098, Items!$D$2:$D$5098,">="&DATE(2023,1,1), Items!$D$2:$D$5098, "<="&DATE(2023,12,31),Items!$M$2:$M$5098,"Yes",Items!$E$2:$E$5098, {"Grain - Broiler Crumbles"}))
B8B8=+SUM(SUMIFS(tblItems[Amount],tblItems[Work Done / Item], {"Grain - Egg Layer"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
C8C8=SUM(SUMIFS(tblItems[Price],tblItems[Work Done / Item], {"Grain"}, tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31)))
D8D8=SUM(SUMIFS(Items!$J$2:$J$5098, Items!$D$2:$D$5098,">="&DATE(2021,1,1), Items!$D$2:$D$5098, "<="&DATE(2021,12,31),Items!$M$2:$M$5098,"Yes",Items!$E$2:$E$5098, {"Grain"}))
B9B9=SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)) &" ("&SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31), tblItems[In Place], "Died")&" died, " &SUM(SUMIFS(tblItems[Amount],tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31), tblItems[In Place], {"Processed"})) &" Processed)"
C9C9=SUM(SUMIFS(tblItems[Price],tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
D9D9=SUM(SUMIFS(tblItems[Price], tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31),tblItems[Side $?],"Yes",tblItems[Description], {"Broiler"}))
B10B10=SUMIFS(tblMeatHarvestTotals[Total],tblMeatHarvestTotals[Animal], "Broiler",tblMeatHarvestTotals[Part], "*Breast", tblMeatHarvestTotals[Year], "2023") + SUMIFS(tblMeatHarvestTotals[Total],tblMeatHarvestTotals[Animal], "Broiler",tblMeatHarvestTotals[Part], "*Tender", tblMeatHarvestTotals[Year], "2023")
C10C10=(C9+C7)/B10
B11B11=+SUM(SUMIFS(tblMeatHarvestTotals[Total],tblMeatHarvestTotals[Animal], "Broiler",tblMeatHarvestTotals[Part], {"*Legs"}, tblMeatHarvestTotals[Year],{"2023"}))
C11C11=(C9+C7)/B11
B12B12=+SUM(SUMIFS(tblMeatHarvestTotals[Total],tblMeatHarvestTotals[Animal], "Broiler",tblMeatHarvestTotals[Year],{"2023"}))
C12C12=(C9+C7)/B12
B13B13=COUNTIFS(tblOOSeedsPurches[Date],">="&DATE(2023,1,1),tblOOSeedsPurches[Date],"<="&DATE(2023,12,31))
C13C13=SUM(SUMIFS(tblOOSeedsPurches[Price], tblOOSeedsPurches[Date],">="&DATE(2023,1,1), tblOOSeedsPurches[Date], "<="&DATE(2023,12,31)))
D13D13=SUM(SUMIFS(tblOOSeedsPurches[Price], tblOOSeedsPurches[Date],">="&DATE(2023,1,1), tblOOSeedsPurches[Date], "<="&DATE(2023,12,31),tblOOSeedsPurches[Sales Fund],"Yes"))
B16B16=+SUM(SUMIFS(tblItems[Amount],tblItems[Work Done / Item], {"Grain - Pig Pellets"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
C16C16=SUM(SUMIFS(tblItems[Price],tblItems[Work Done / Item], {"Grain - Pig Pellets"}, tblItems[Date],">="&DATE(2023,1,1), tblItems[Date], "<="&DATE(2023,12,31)))
B17B17=+SUM(SUMIFS(tblMeatHarvestTotals[Total],tblMeatHarvestTotals[Animal], "Pig",tblMeatHarvestTotals[Year],{"2023"}))
C17C17=(C14+C15+C16)/B17
 
Upvote 0
It sounds like you know what a table slicer is and is effectively just a filter on the table.
If you want to pull a differenct column depending on year that can be done using Index/Match if you are wanting to pull data from different tables then you either need to have if statements directing the formula or use INDIRECT which I generally try to avoid.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
New formula for cumulative values
Replies
6
Views
196
djclements
djclements
D
  • Question Question
Having trouble getting Pivot Table data to pull from pivot table to regular table using =GetPivotData()
Replies
4
Views
1K
smozgur
smozgur
D
  • Question Question
List Generator with index match within date range
Replies
2
Views
666
Peter_SSs
Peter_SSs
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
934
paulkc
P
M
  • Question Question
SUMIFS
Replies
8
Views
365
awoohaw
awoohaw

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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