Forum Discussion

mnirx's avatar
mnirx
Copper Contributor
Sep 20, 2022
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 usin...
excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Sep 20, 2022

    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

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Sep 20, 2022

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.
 

mnirx's avatar
mnirx
Copper Contributor
Sep 20, 2022
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.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 20, 2022

    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

     

    • mnirx's avatar
      mnirx
      Copper Contributor
      Sep 21, 2022
      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.

Resources