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 more complicated, How can I add a formula in a cell depending of the colour of another cell? Trying to explain myself I meant IF the colour of an specific cell is "X" then use this formula, if is "Y" use this another formula, is that possible? ,and also repeat this pattern every n rows in the same column?

 

I am not an expert in excel but I am trying to learn as much as possible to use for work as I am dealing with excel spreadsheets with a big amount of data and I think that would be useful rather than to add the numbers one per one, thanks in advance .

 

Julian 

excel
Formulas and Functions

7 Replies

Sort By
  • chahine's avatar
    chahine
    Iron Contributor
    Mar 20, 2021

    castre1984 hello, easiest is to use if function along with mod to make repeated pattern, then for colors use conditional formatting with formulas

    attached sample, hope this helps

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

      chahine 

      You shall insert the value in every 12th cell (5th in your case) without touching values in the cells in between. =$I$15 could work, but I'm not sure what are limitations.

      Colors is another question, these are using of different formulas depends on cell color.

  • 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.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Mar 19, 2021
    Maybe sharing the sheet and/or more details on what you have and are trying to do would help.
    To repeat a number every n rows just do the 1st set of n rows, copy, then select the whole range of rows you want to fill that way and paste.
    The same method works whether the value in the cells are numbers, text or formulas
    but the formula needs to be written accurately to indicate if any particular cell reference is ABSOLUTE or RELATIVE. By that I mean if a formula in cell A1 is =B1 + 1 then you copy that down to A2 then A2 will have a formula =B2 + 1 because it will adjust B1 down to B2 accordingly. If instead you wanted it to stay with B1 then add $ before each 'fixed' component. So if A1 had =$B$1 + 1 then that reference to $B$1 will not change regardless of it being copied to a new cell location. Notice there is a $ before both the B and the 1 but you can also choose to only 'fix' one or the other: $B1 or B$1 so $B1 will fix the B but not the 1 so if you copy down rows the 1 will change but if you copy to the a different column the B will not. and similarly with B$1 if you copy to C1 then the B will change to D but if you copy down rows the 1 will stay fixed.
    Finally as for detecting the color of the cell, that is only possible using VBA (macros). IF that color is being applied based on a conditional formatting rule then you can create a formula that replicates that rule to do the appropriate action.

Resources