Return section of range where Sumproduct criteria are true to use in Hyperlinks go to.

[Y04NN]

Hello Excel Experts,

I couldn't find my answers on the web nor on boards. I am not sure how to look for what I need or if I am even addressing the problem the right way.

Current situation:
I have 2 files located in different folders on the network.
- 1 reporting file (a lot of tabs, a lot of rows and column)
- 1 summary (1 spreadsheet)


My summary file pulls up values from the report file using SUMPRODUCT function.
example:
say I want to know for the November 9 What is the quantity that Team C reported (no matter the type of product).

=SUMPRODUCT(--(Date="9-Nov"),--(Team="C" ),--(Qte)) =>>> 20.
(Sumproduct functions in the summary file are longer due to the path they have to follow to pull up values.)

Reporting file:

​

​	B ______​	C​	D​	E​	F​	G​	H​
2​	Date​	Team​	Type​	Qte​		9-Nov​	C​	Product​
3​	9-Nov​	A​	1​	5​		1​	0​	0​
4​	9-Nov​	A​	2​	5​		1​	0​	0​
5​	9-Nov​	A​	3​	5​		1​	0​	0​
6​	9-Nov​	B​	1​	10​		1​	0​	0​
7​	9-Nov​	B​	2​	0​		1​	0​	0​
8​	9-Nov​	B​	3​	0​		1​	0​	0​
9​	9-Nov​	C​	1​	5​		1​	1​	1​
10​	9-Nov​	C​	2​	15​		1​	1​	1​		=SUMPRODUCT(--(B3:B20=G2),--(C3:C20=H2),--(E3:E20))​
11​	9-Nov​	C​	3​	0​		1​	1​	1​		20​
12​	10-Nov​	A​	1​	2​		0​	0​	0​
13​	10-Nov​	A​	2​	3​		0​	0​	0​
14​	10-Nov​	A​	3​	5​		0​	0​	0​
15​	10-Nov​	B​	1​	0​		0​	0​	0​
16​	10-Nov​	B​	2​	0​		0​	0​	0​
17​	10-Nov​	B​	3​	15​		0​	0​	0​
18​	10-Nov​	C​	1​	5​		0​	1​	0​
19​	10-Nov​	C​	2​	3​		0​	1​	0​
20​	10-Nov​	C​	3​	2​		0​	1​	0​

<tbody>
</tbody>

Question:

How can I return the range used for the calculation where Criteria1 & Criteria2 of sumproduct are true ? (In my example E9:E11).


Goal:

For each sumproduct (when needed) I want to be able to click on a hyperlink to open the report file on the right tab, with the range that match SP criteria selected.
That would allow me to have the type detail of product in one click.

I was thinking using the formula below.

=HYPERLINK("\\NetworkPath\Reportfile.xlsx#SheetNAME!"&C10),"See detailed Mix"))

C10 = Range/section of range where sumproduct criteria are True


I guess a way could be to retrieve first row & last row of this range section. Then I could used concatenate("E",Firstrow,":E",Lastrow) in C10.


Reminder: files are located in different folders on the network. I am looking for a solution that works with file path and hopefully isn't 20 foot long ^^


Thank you for your help

 

[Y04NN]

I am sure this is not the best solution but here is the way I used.

In the report file:
I inserted a column (now hidden) that concatenate column B and C.

In the summary file:
I hidden a cell that concatenate my criteria of date and team (that will match the column hidden in the report file). I call it my "concatenate_criteria_cell"

I use the function Match to find the first row that matches my "concatenate_criteria-cell" in the column

MATCH(concatenate_criteria_cell,'\\network_path_to_report_file\[file_name.xlsx]tab_name'!$range$,0)​

I use sumproduct function to find how many time the concatenate criteria appears in the column

SUMPRODUCT(--('\\network_path_to_report_file\[file_name.xlsx]tab_name'!$range$=concatenate_criteria_cell))​

I re-use the MATCH function result and add the result of the sumproduct minus one to know what is the last row that matches my concatenate_criteria_cell in the column.

Then I concatenate with letter B and E to give me the array I want to be selected when opening the file (after clicking the hyperlink)

=CONCATENATE("A",MATCH(concatenate_criteria_cell,'\\network_path_to_report_file\[file_name.xlsx]tab_name'!$range$,0)
,":E",MATCH(concatenate_criteria_cell,'\\network_path_to_report_file\[file_name.xlsx]tab_name'!$range$,0)+SUMPRODUCT--('\\network_path_to_report_file\[file_name.xlsx]tab_name'!$range$=concatenate_criteria_cell)))-1)​

I inserted a column next to sumproduct result for its hyperlink to be and use the hyperlink function

=HYPERLINK("\\network_path_to_report_file\file_name.xlsx#'tab_name'!"&hidden_array_cell_to_be_selected,"[]")​

Now in my summary file:
when I click on the hyperlink next to my sumproduct result, it opens the report file, on the right tab, at the right height in the column, selecting the array used. B9:E11. for my example.

Please let my know if you have an easier way.