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
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
Last Comment
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.
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.
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.
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.
ASKER
ok, i am installing offfice 365 to test
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
also trying to figure out why array won't activate on my sheet when pressing CTRL+SHIFT+ENTER
ASKER CERTIFIED SOLUTION
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.
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.
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
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