Modify formula to return null/blanks instead of zeroes ...

[jgreer7322]

Hi

The shot below is from a home made dictionary in MS365 Excel. Fundamentally, this sheet is to show every 'x'th row, starting from row 'y' - intended to generate a random (ish) selection of Welsh for revision purposes.

When the formula gets to a row in the 'raw data' that is blank - currently c. 5.6k 'real value' rows, it returns zeroes as below; which is fine, but ... how would I/do I modify the formula to return null/blanks instead of zeroes - trying to avoid printing sheet after sheet of zeroes!

Obviously, if I do a print preview before printing, and only print pages with 'interesting' values, that's fine - but if I forget ...

Column H here in the formula contains (essentially) the sum of D1 (start from this row) and B1 (the increment); an extra (in this case) 100 on each row; H3 = 5000, H4 = 5100, H5 = 5200, and so on.

Thanks.

Jim

 

[Fluff]

As you have 365 why not use the filter function.
Clear out all your formulae & put this in A3 only

=LET(d,'Raw Data'!A1:D10000,s,SEQUENCE(ROWS(d)),FILTER(d,(INDEX(d,,1)<>"")*(s>=D1)*(MOD(s-D1,B1)=0)))

 

[jgreer7322]

Hi Fluff

Brilliant, Thanks. I put it in as above, but nothing happened; I changedA1:D10000 to A1:C10000 (the three columns that have data in them) and it leapt into life!

Thanks again.

Jim

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jgreer7322]

I can probably tell you now that I'm ... Welsh! ;-0)