IP address increment

[deanw55]

Hi to all,
I can't seem to find any good answer which would help me out.
1. I need Excel to set the right starting IP address when user enters the third octet (B1) -> first IP always being 192.168.X.1
2. Then to increment the IP address for Y times (Y = B2)

Hope this makes sense and you can help me out. Thank you in advance!
BR, Dean

 

[StephenCrump]

Welcome to the Forum!

Do you mean like this:

	A	B
1		8
2		3
3		192.168.8.1
4		192.168.8.2
5		192.168.8.3
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	="192.168."&B1&"."&SEQUENCE(B2)
Dynamic array formulas.

 

[deanw55]

Thank you. Somehow the sequence part is what I'm looking for - I assume. But but obviously I need some more digging into this because my first IP should be 192.168.X.201 not 1 and did something worng, getting the Spill error...

 

[StephenCrump]

It's a single cell formula, and you'll need to allow room to let it spill, i.e. don't have any other values or formulae in the way.

	A	B
1		8
2		201
3		3
4		192.168.8.201
5		192.168.8.202
6		192.168.8.203
Sheet1

Cell Formulas
Range	Formula
B4:B6	B4	="192.168."&B1&"."&SEQUENCE(B3,,B2)
Dynamic array formulas.

 

[deanw55]

Hm. Not quite sure how to solve this now. When I put this formula into my table it spills and not working

If I move the same formula out of my table in an empty cell in the same worksheet it's working as supposed.

What am I missing? Thank you in advance.

 

[deanw55]

Hm. Not quite sure how to solve this now. When I put this formula into my table it spills and not working
View attachment 72777

If I move the same formula out of my table in an empty cell in the same worksheet it's working as supposed.
View attachment 72778

What am I missing? Thank you in advance.

Ok, now I converted the table to Range and it works fine. Will see if that's acceptable for all other functions I'm using.

 

[StephenCrump]

Ahh! It's in a table.

Perhaps:

	A	B	C	D
1		8
2		201
3		3
4
5
6	Add	IP	Lokacija
7	1	192.168.8.201
8	2	192.168.8.202
9	3	192.168.8.203
10	4
11	5
12
Sheet1

Cell Formulas
Range	Formula
B7:B11	B7	=IF([@Add]<=B$3,"192.168."&B$1&"."&B$2+[@Add]-1,"")

 

[deanw55]

Thank you so much for helping me out!

BR, Dean

 

[StephenCrump]

No problem. I'm glad we could help.