I apologise in advance but I have a problemthat I'm really struggling to be able to put into words.
I have a workbook with multiple worksheets in it.
Worksheet 1 (S1) is a 'setup' sheet and contains multiple columns includingCompany, Location, Account Manager, and Regional Manager - then a number of columscontaining tick boxes which put a true or false against particular products wesupply each company.
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o
referrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_1" style="width: 340.8pt; height: 67.8pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1025"> <v:imagedata o:title="Image1" src="file:///C:\Users\Michael\AppData\Local\Temp\msohtmlclip1\01\clip_image001.jpg"></v:imagedata></v:shape>
Worksheet 2 (Input) has the number of each product supplied to each companyeach month.
Where I'm really struggling is;
I can Filter worksheet 2 by Company, Location, Account Manager, or RegionalManager
When for example I filter by Regional Manager I need a count in worksheet 2 ofhow many companies in this Region we provide a particular product to. Ie. How many ticks are in column K between K5and K104 on Sheet "S1" but only where the Regional Manager is the name chosen in the filter in Sheet "Input".
I can get the total number of companies across All Regions by using theformula: =(COUNTIF('S1'!$K5:$K104,TRUE))-G$114
When the filter is applied I only need to count the number of companies basedon the Regional Manager<o></o>
<o> </o>
Any help or guidance would be much appreciated.
I have a workbook with multiple worksheets in it.
Worksheet 1 (S1) is a 'setup' sheet and contains multiple columns includingCompany, Location, Account Manager, and Regional Manager - then a number of columscontaining tick boxes which put a true or false against particular products wesupply each company.
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o
referrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_1" style="width: 340.8pt; height: 67.8pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1025"> <v:imagedata o:title="Image1" src="file:///C:\Users\Michael\AppData\Local\Temp\msohtmlclip1\01\clip_image001.jpg"></v:imagedata></v:shape>Worksheet 2 (Input) has the number of each product supplied to each companyeach month.
Where I'm really struggling is;
I can Filter worksheet 2 by Company, Location, Account Manager, or RegionalManager
When for example I filter by Regional Manager I need a count in worksheet 2 ofhow many companies in this Region we provide a particular product to. Ie. How many ticks are in column K between K5and K104 on Sheet "S1" but only where the Regional Manager is the name chosen in the filter in Sheet "Input".
I can get the total number of companies across All Regions by using theformula: =(COUNTIF('S1'!$K5:$K104,TRUE))-G$114
When the filter is applied I only need to count the number of companies basedon the Regional Manager<o
></o><o
> </o>Any help or guidance would be much appreciated.
