# Excel Formula To populate numbers

i have a spreadsheet and on sheet a there is a subnet calculator. when i input the data into the calculator it gives me a range of usable ip addresses.
so on sheet Network0 i get the starting ip by using this =('Subnet Calculators'!C18&"."&'Subnet Calculators'!E18&"."&'Subnet Calculators'!G18&"."&'Subnet Calculators'!I18) which works

on sheet "Subnet Calculators the ending ip is here =('Subnet Calculators'!L18&"."&'Subnet Calculators'!N18&"."&'Subnet Calculators'!P18&"."&'Subnet Calculators'!R18)

is there a way to auto populate the numbers between the starting and ending ip's on sheet "Network 0" in row b?
i have attached a sample

My Network Info.xlsm
Microsoft ExcelMicrosoft Office

Last Comment
Mark Wood
byundt

If you have Microsoft 365 and only need to vary the last octet, you could use:
``````=('Subnet Calculators'!C18&"."&'Subnet Calculators'!E18&"."&'Subnet Calculators'!G18&"."&SEQUENCE('Subnet Calculators'!R18-'Subnet Calculators'!I18+1,1,'Subnet Calculators'!I18,1))
``````
The above formula placed in a single cell will spill down all the IP addresses, one per row.

My-Network-Info.xlsm
Mark Wood

That works if you only add it to the correct number of cells below the first one. So if it were a /24 range it wouldn't work unless you copied it to enough cells.

i would like to be able to add a script or something in 255 cells let's say for a full /24 subnet but if was a /28 for example it would only fill the first 14 cells.
byundt

Au contraire!

One formula in one cell spills as many results as are needed down into the cells below. The spill range expands and contracts according to the number of IP addresses you need.

This is the so-called dynamic array feature of Excel. You need a Microsoft 365 subscription to get it (or the upcoming Excel 2021 or Excel LTSC perpetual versions).

If you have the right version of Excel, try changing the values in worksheet Subnet Calculators cells I18 and R18. The values on worksheet Network 0 will calculate automatically with no copying down needed.
Mark Wood

ok, i am installing offfice 365 to test
Mark Wood

got office 365 installed and copied the formula to my actual sheet and all it shows is #NAME?. Thought i did something wrong so i opened the sheet you sent and it worked until i changed the ips on the subnet Calculator and then it did the same thing.

also trying to figure out why array won't activate on my sheet when pressing CTRL+SHIFT+ENTER
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Mark Wood

i see you have the office 365 aps for enterprise. i only have the family version and that may be the difference. when i click on update now it tells me i am up to date.
the second option you gave works perfectly. thanks so much for all your help.

once again you saved the day for me.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY