Using mod row to display in every nth cell with some cells skipped

[mick86]

Hi

I wish to have a formula displayed running down the sheet in a column, the catch is I only wish to display this in some cells while skipping others.

My workbook is arranged into weeks and I have 7 cells representing week days followed by 4 cells in between. The frequency of display will be specified by a number between 1 and 7 (denoted in another cell) and depending on this value my formula will display in every cell or every 2nd cell or every 3rd cell and so on. I wish to display the formula in every nth cell keeping a count but skipping over the 4 blank cells after every 7 cells. I hope that makes sense, if not I've attached a sample file. On attached file the sheet named "setup" has the numbers that denote display frequency and sheet named "daily" has my attempts to implement the code.

My current code uses MOD(ROW()-4 REFERENCE TO CELL WITH FREQUENCY NUMBER)=0

This is meant to set a value of true or false to the appropriate cells so that I can then use a logical test on the column next door and only display formula if adjacent cell is true. This however only works in some cases with some numbers, I can't work out a way to keep the count in order while skipping the blanks.

If anybody could help me with this I will greatly appreciate their assistance.

If there's another way to display my information aside from MOD ROW I'm open to any thing that works.

Regards,

Michael fro Australia.

[martindwilson]

i havent a clue from what you showed but here is how to create a dynamic series with 1,2,3,4 up to 10 gaps change g2 and h2(a direct static version of 7 numbers with 4 gaps in column a)

[mick86]

Thank you very much, pretty sure I will be able to take what I need away from that example, that's pretty much what I'm trying to accomplish.

Thanks again, would not have been able to figure that out without your assistance.

[mick86]

Update:

Code almost accomplishes what I'm after, just need to tweak it slightly so that there are 4 blank rows after every 7 rows yet the count is not affected.

I modified your code slightly like this

=IF(MOD(ROW(),3)+1>1,"",MAX($D4:D$4)+1) [i'll later use cell references similar to ur example to dynamically grab the # where 3 currently is]

Works but can't keep count over the 4 blank/skipped rows

Below I've drawn a visual representation of what I need, black #s on left are row #s, red #s are the count, bold #s on left are skipped rows

Sorry to take up yet more of your time with this


1 1
2
3
4 2
5
6
7 3
8
9
10
11
12
13
14 4
15
16
17 5
18
19
20
21
22
23
24 6

If that makes sense is there a simple way to achieve this?

[martindwilson]

count or sum?

[mick86]

I thought the term was count, in my drawn example even though I'm displaying numbres in a series of 1 with gaps of 2 I want to be able to have 4 empty cells after every 7 cells. I dont want these 4 cells included in the repetition count. If the repetition is changes i.e 1 cell display 1 cell blank i'll still have 4 blank cells after every 7 cells that i dont want affected

In such a case it would look like this

Column numbers on left in black


1 #
2
3 #
4
5 #
6
7 #
8
9
10
11
12
13
14 #
15
16
17 #
18
19
20
20
21
22
23 #
24

New example uses 1 cell gaps and has # signs instead of a running count, in reality it doesn't matter what displays, # signs, zeros or a count so long as the frequency is right

[martindwilson]

=SUMPRODUCT((MOD(ROW(A1:A100)-1,11)+1<8)*(ISNUMBER(B1:B100))) will count numbers
=SUMPRODUCT((MOD(ROW(A1:A100)-1,11)+1<8)*(B1:B100<>"")) will count anything

[mick86]

I tried incorporating that into my existing code but can't seem to get the desired outcome, I'll try to completely rephrase my goal, I want to draw a pattern running down the sheet in a column. The pattern will repeat so that there's 1 cell which contains some kind of text or symbol followed by 2 blank cells, the pattern repeats for 7 rows then has 4 empty cells under it which have nothing to do with the pattern, they are just empty space, the pattern resumes beneath this for another 7 rows, this will continue on. The pattern of 1 cell with contents followed by 2 empty cells will need to some how jump over the 4 gap cells without being affected.


1 (pattern begins)
2
3
4 pattern
5
6
7 pattern
8 not in pattern
9 not in pattern
10 not in pattern
11 not in pattern
12 (row 7 had pattern so need to have 2 empty cells)
13 (row 7 had pattern so need to have 2 empty cells)
14 (pattern resumes)
15
16
17 pattern
18

[martindwilson]

doesnt make sense you now have a movable pattern, just fill in the cells manually and delete what you dont need it would be quicker
so its now 6 gaps? i cant see the difference between not in pattern and 2 empty cells

[mick86]

the 7 days are week days in a daily planner. the gaps are the result of formatting as the weeks are spaced out with some blank rows in between. The whole thing will auto generate based on input from another page. The frequency of the pattern/count will b denoted by a value in another cell, in my example i used the number 3 but this could b anything from 1 to 7. I should b able to perform any minor changes i just seek code that performs a count just like this

=IF(MOD(ROW(),3)+1>1,"",MAX($D4:D$4)+1) [i'll later use cell references similar to ur example to dynamically grab the # where 3 currently is]

Only change needed is that this thing repeats in blocks of 7 rows

The 7 rows are separated by 4 rows which only exist for formatting reasons


The gap hasn't jumped to 6, there are the 4 blank cells which exist after every 7 plus the gap of 2 from the pattern which in some cases will line up exactly with the gap of 4 from formatting

Iv again attached an example, this time I've tried to make things clearer

please refer to the "Daily" sheet

[martindwilson]

sorry i just cant see it

[icestationzbra]

mick, take a look at the highlighted portion and see if that is what you are looking for. the scratch-work can be collapsed into fewer columns; i just wanted you to look at them and see if they follow your thought process.

[martindwilson]

hooray some help!

[mick86]

My sincerrest thanks, that's exactly what I was after, sorry if my attempts to explain the situation were not clear, I'll try to condense it all down to a few less columns and I'll change the number 3 in the MOD formula a dynamic reference.

Thank you to all that helped.