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

[castre1984]

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 

[#2]

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.

[#3]

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

[#4]

@mtarler 

Thanks in advance for you answer, I am adding an excel doc to be more precise, you can find my questions in the question sheet.

[#5]

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

[#6]

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

[#7]

@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

[#8]

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