Incrementing IP Addresses with hidden rows

[sledparty]

Hello all,

I am trying to increment IP addresses in a single column but multiple rows are hidden with filters. Out of over 5k rows I only want to populate about 570 with an IP range that begins with 192.168.1.10 up to 192.168.2.255 for example. Again, MOST of the rows are hidden by filtering. I only want the rows that are visible to populate with IPs.

My current formula works but it populates all rows even hidden ones.

="192.168.1."&Rows(A$6:A6)

Thank you

 

[Joe4]

Welcome to the Board!

You can use this method here to count the visible rows: Count visible rows in a filtered list

So your formula would look something like:

="192.168.1."&SUBTOTAL(3,A$6:A6)

 

[Tetra201]

@ Joe4: Unfortunately, the suggested SUBTOTAL approach would not work -- the formula would keep returning "192.168.1.1" because the nested subtotals are ignored.

@ sledparty: See if the following formula works for you

=TEXTJOIN(".",,HEX2DEC(MID(RIGHT("0000000"&DEC2HEX(SUM(INDEX(TEXTSPLIT(A$6,"."),{1,2,3,4})*256^{3,2,1,0})+AGGREGATE(3,5,A$6:A6)),8),{1,3,5,7},2)))