how do I identify specific cell of column referenced in formula?

[mnirx]

For illustration purposes, I created a super simplified version (Table 1) of what I am doing. The data set I am actually working with is much larger, thus, the need to identify which cells it is using in the formula for quality check purposes when a second or third pair of eyes verify the info. Table 2 shows the formulas within the last two columns of Table 1. 

The issue: I am trying to find a formula that will show which cells add up to 72 (G1).

To reiterate, this is a simplified version of what I am working with. Trace Precedents, under Formula Auditing, does not work as it highlights the entire B and F column. I have a way to figure this out, but it involves creating various values that Column B can total, and use that as a reference. I would rather avoid doing all of that. I do not think there is a formula, if anything, may be VBA that is required.

Thank you

Table 1

        A              B             C            D              E            F           G

1/1/2021	1		3/15/2021			72
2/1/2021	2
3/1/2021	3
4/1/2021	4				yes
5/1/2021	5				yes
6/1/2021	6				yes
7/1/2021	7				yes
8/1/2021	8				yes
9/1/2021	9				yes
10/1/2021	10				yes
11/1/2021	11				yes
12/1/2021	12				yes

Table 2

                     F                                                     G

=IF(A1>=$D$1,"yes","")	=SUMIF($F$1:$F$12,"yes",$B$1:$B$12)
=IF(A2>=$D$1,"yes","")
=IF(A3>=$D$1,"yes","")
=IF(A4>=$D$1,"yes","")
=IF(A5>=$D$1,"yes","")
=IF(A6>=$D$1,"yes","")
=IF(A7>=$D$1,"yes","")
=IF(A8>=$D$1,"yes","")
=IF(A9>=$D$1,"yes","")
=IF(A10>=$D$1,"yes","")
=IF(A11>=$D$1,"yes","")
=IF(A12>=$D$1,"yes","")

[#2]

@mnirx 

=SUMPRODUCT(($F$1:$F$12="yes")*$B$1:$B$12)

A simple solution could be with SUMPRODUCT. In the formula bar select the array within SUMPRODUCT. In this example this is ($F$1:$F$12="yes")*$B$1:$B$12 and then press F9 to show the values like in the screenshot. In order to return to the formula press ctrl+Z.
 

[#3]

Thank you so much for your response. Unfortunately, the issue is that I am dealing with much larger table that have matching figures so finding the exact cell would be ideal as those figures cross reference cells that would not match the same way. So in this example, the other person would be left to wonder which cell that 4 would be pulling from. Knowing where it is pulling from is key. I am looking for something that the Trace Precedents function would normally do, if it were not for my formulas that are referencing an array as opposing to individual cells.

Thank you again for taking the time.

[#4 · Accepted solution]

@mnirx 

Do you want to return the cells which add up to 72? In your example that would be cells B4, B5, B6, B7, B8, B9, B10, B11 and B12.

With these lines of code you can return the cells. In the attached file you can click the button in cell J2. The cells are listed in column H. The macro works for any number of rows.

Sub reference_cells()

Dim i, j, k As Long

Range("H:H").Clear
i = Range("A" & Rows.Count).End(xlUp).Row
k = 1
For j = 1 To i

If Cells(j, 1).Value > Cells(1, 4).Value Then
Cells(k, 8).Value = "B" & j
k = k + 1
Else
End If

Next j

End Sub

[#5]

Thank you so much. This helps a lot. I appreciated your patience as I provided further detail and the time you took to respond. Hope you have a good day and that the rest of your week goes well.