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