Forum Discussion

Deel_Engineering's avatar
Deel_Engineering
Copper Contributor
Jan 10, 2023

Cell References: How can I use a formula to tell excel what cell to reference?

Cell References: How can I use a formula to tell excel what cell to reference?
 
I am trying to compile a list of results from repeating processes w/in a long sheet. The results I want to reference are in cells B51, B87, B123,... B(n+36). How do I do this without manually typing in =B51, =B87, +B123, etc, etc in the cells where I want the compiled results?  (Ive got about 400 results to compile in a list)
excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 10, 2023

    Deel_Engineering 

    An alternative could be these lines of code. In the attached file you can click the button in cell B5 to run the macro. The values of the referenced cells are then listed in column E.

    Sub reference_cells()

Dim i, j, k, l As Long

Range("E:E").Clear

i = Range("A" & Rows.Count).End(xlUp).Row
l = 16

For j = 51 To i Step 36
Cells(l, 5).Value = Cells(j, 1).Value
l = l + 1

Next j

End Sub
  • Mark_Dekeyser's avatar
    Mark_Dekeyser
    Copper Contributor
    Jan 10, 2023
    Let's assume you have the numbers 1 through 9 in cells A1:A9.
    So just 1, 2, 3, 4 ... 9 underneath each other.
    Then you want to add up A3+A6+A9, so every third cell needs to be added.
    The result should be 3+6+9 = 18.
    You can do that when the row number divided by 3 has a rest/remain value of 0.
    The function for that is called Mod()=0

    So in A10, this is the formula that works:
    =SUM(IF(MOD(ROW(A1:A9);3)=0;A1:A9;0))
    Result = 18 🙂

    Mind1: this is a spill function, consolidated by the sum() function
    Mind2: it's sum(if()), so not sumif()
    Mind3: You may want to change ; into , in your area of the world...

Resources