+ Reply to Thread
Results 1 to 7 of 7

Nested If revolving around IP addresses & subnet masks

  1. #1
    Registered User
    Join Date
    11-18-2013
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    2

    Nested If revolving around IP addresses & subnet masks

    Hi everyone,

    I'm trying to accomplish the following:

    Have a some logic around checking an IP address against say 30 subnet ranges. We'll say Cell F2 has 10.1.1.1, I need to build something to check if that cell contains something in this range:

    > 10.1.0.0 AND <10.1.255.255, "Insert Subnet Name", if not
    is it > 10.2.0.0 AND <10.2.255.255, "Insert Subnet Name", if not
    is it > 10.3.0.0 AND <10.3.255.255, "Insert Subnet Name", if not
    Etc for another 30 subnets.

    I've made it work for one (obviously pretty easy), but when I had to nest them together it got complex and out of my depth. I have roughly 7000 cells I have to apply this to, and will just be filling the formal down the entire sheet for F3, F4, F5 etc.

    Any help would be very appreciated.

    TJ

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Nested If revolving around IP addresses & subnet masks

    You could create a table of IP Address ranges. In reality, you only need the first two octets so you can extract them and use a VLOOKUP against a table.

    Have a look at the example workbook to see how this might work.


    Regards, TMS
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Nested If revolving around IP addresses & subnet masks

    I would use an index/match against a table, and as only the first 2 parts are actually needed, it makes it pretty simple
    see attached

    Hope this helps

    EDIT-
    Darn, beat again...lol
    Attached Files Attached Files
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Nested If revolving around IP addresses & subnet masks

    Darn, beat again...lol
    Way beat ...

    But very similar solutions ... and same comment about the structure.

    Regards, TMS

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Nested If revolving around IP addresses & subnet masks

    Thanks TM, got caught on the phone and forgot to refresh before posting...lol

  6. #6
    Registered User
    Join Date
    11-18-2013
    Location
    Edmonton, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Nested If revolving around IP addresses & subnet masks

    This is exactly what I was looking for. I've adapted it and it works like a charm. Thanks for taking the time to help, it's appreciated.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Nested If revolving around IP addresses & subnet masks

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need to take an IP Subnet Range and Output Subnet Mask
    By Markh7999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2013, 08:53 PM
  2. Input Masks // Display Masks
    By anonymust in forum Excel General
    Replies: 0
    Last Post: 02-13-2012, 02:47 PM
  3. Ranking a revolving average?
    By Jeremiahm in forum Excel General
    Replies: 1
    Last Post: 12-01-2009, 03:36 PM
  4. Masks
    By John Lee in forum Excel General
    Replies: 2
    Last Post: 08-12-2008, 08:27 AM
  5. Nested IF to evaluate street addresses
    By ManosS in forum Excel General
    Replies: 6
    Last Post: 03-16-2006, 11:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1