How to make an If statement result searchable?

[Ed Harris]

Hi, I am trying to use an IF statement to put a 1 in every 2000th row of a column. This is a precursor to putting a graph there of the preceeding 2000 rows column G and H. I have never managed to make any macro type approach do anything with graphs but it is very useful to use control and up or down to quickly move between the ones in the column to review the graphs. I autofil the cell macro then copy the column and paste "values" and hope to be left with the "1"s. But my control + up or down keyboard shortcut strategy doesnt recognise them. I noticed it was putting the ones to the left of the column when result "if true" has hyphons implying a string so removed the hyphons and got the ones on the right of the cells. Still after pasting the values "control + up or down" does not recognise them.
Is there anything else I can do to fix this. It would be nice to put the graphs in automatically also but I will still need the "1"s.

Streaming Dec 24-12-24.xlsx
	B	C	D	E	F	G	H	I
161996	16199400	69.56416		14:16.9	16199400	71.09377	69.56416
161997	16199500	69.56421		14:17.0	16199500	71.09373	69.56421
161998	16199600	69.56416		14:17.1	16199600	71.09378	69.56416
161999	16199700	69.56419		14:17.2	16199700	71.0938	69.56419
162000	16199800	69.56421		14:17.3	16199801	71.09387	69.56421	1
162001	16199900	69.56422		14:17.4	16199900	71.09384	69.56422
162002	16200001	69.56419		14:17.5	16200000	71.09386	69.56419
162003	16200100	69.56417		14:17.6	16200100	71.09388	69.56417
162004	16200200	69.56425		14:17.7	16200200	71.09386	69.56425
162005	16200300	69.56425		14:17.8	16200301	71.09386	69.56425
162006	16200400	69.56433		14:17.9	16200400	71.09386	69.56433
162007	16200500	69.56437		14:18.0	16200501	71.09387	69.56437
162008	16200600	69.56435		14:18.1	16200601	71.09389	69.56435
162009	16200700	69.56439		14:18.2	16200700	71.09389	69.56439
162010	16200800	69.56439		14:18.3	16200800	71.09389	69.56439
Streaming Bx data 24-12-24

Cell Formulas
Range	Formula
I162000	I162000	=IF(MOD(ROW(A162000),2000),"",1)
I162001:I162010	I162001	=IF(MOD(ROW(A162001),2000),"","1")

 

[Peter_SSs]

then copy the column and paste "values" and hope to be left with the "1"s. But my control + up or down keyboard shortcut strategy doesnt recognise them.

So after you have pasted the values ..

1. Select the entire column by clicking its heading label
2. Press F5 and choose Special ... -> Mark 'Constants' -> Remove the tick from Numbers a little below that-> OK
3. Press the Delete key

Now try navigating up/down the column to the 1s

 

[Ed Harris]

Thanks, works perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.