Avatar of Mark Wood
Mark WoodFlag for United States of America

asked on 

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

Avatar of undefined
Last Comment
Mark Wood
Avatar of byundt
byundt
Flag of United States of America image

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))

Open in new window

The above formula placed in a single cell will spill down all the IP addresses, one per row.

My-Network-Info.xlsm
Avatar of Mark Wood
Mark Wood
Flag of United States of America image

ASKER

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.
Avatar of byundt
byundt
Flag of United States of America image

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.
Avatar of Mark Wood
Mark Wood
Flag of United States of America image

ASKER

ok, i am installing offfice 365 to test
Avatar of Mark Wood
Mark Wood
Flag of United States of America image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of Mark Wood
Mark Wood
Flag of United States of America image

ASKER

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

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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo