Forum Discussion

castre1984's avatar
castre1984
Copper Contributor
Mar 19, 2021

How to repeat same number every n rows in the same column

Hi,    I need to repeat the same number every n rows within the same column, any thoughts?   Also, how can I do the same using a formula rather than a number?   Also, and I think , slightly mor...
excel
Formulas and Functions
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Mar 20, 2021

castre1984 

Your question has two part, and this one is addressing first one, Repeat number in certain Rows.

 

How it works:

  1. Create Index Column as shown in Col AC.
  2. Formula in cell AD2:

 

=IF(MOD(ROW()-1,5),"",SUM(OFFSET(AC2,-4,0,4,1)))

 

 

  • Where 5 is Row interval, and -4 & 4 are value to repeat.

N.B. Remember Value to repeat must be smaller than Row interval.

 

Solution for Part two, if the cell has color then use a formula.

 

How it works:

  1. To get VB editor either press ALT+F11 or Right Click Sheet Tab, and from menu click View Code.
  2. Copy & Paste this VBA code as Module.

Function InteriorColor(CellColor As Range)
Application.Volatile
InteriorColor = CellColor.Interior.ColorIndex
End Function

 

    3. Return to the Sheet, and Save the WB as Macro Enabled *.xlsm.

    4. Write this formula in cell AJ2:

 

=IF(AI2>0,AH2+1,"No Color")

 

N.B. For neatness you may hide column AI.

castre1984's avatar
castre1984
Copper Contributor
Mar 20, 2021
Hi, thanks for the answer!, but i think my issue is more complex, I already attached a excel doc answering mtarler, let me know if you cannot find the excel doc and I'll share a copy with you.

Ragards

Julian
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 20, 2021

    castre1984 

    If without VBA

    1) I'd create helper column (you may hide it) which fills down value in I15, other every 12 cell in column I could use reference on this helper column

    2) To define an option I'd use not the cell property such as color, but some values, perhaps from drop-down list. Based on selected value you may do this or that calculations. To color the cell(s) you may use conditional formatting on the top.

Resources