Forum Discussion
Solved
how do I identify specific cell of column referenced in formula?
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","") |
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
=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.
- 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.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
