+ Reply to Thread
Results 1 to 2 of 2

Delivery Note Address Formula that excludes blank lines

  1. #1
    Registered User
    Join Date
    09-11-2015
    Location
    Middlesex
    MS-Off Ver
    2010
    Posts
    6

    Delivery Note Address Formula that excludes blank lines

    Hi

    I am trying to create a formula on my delivery note in relation to addresses in excel where i have five colums (C-G) in my data spreadsheet where sometimes some of these fields are blank. For example if cell d2 is blank i have put in the below formula which will enter the text of cell e2, f2 or g2 (if not blank) instead, so instead of having gaps on the delivery note between text they will be pushed up a line so all of the text is together and the blank lines are underneath. This is working but what i need to add is that if the text for d2 for example has already been used on the line above (because c2 was blank) i dont want to use this on the next row as it has already been used, instead i would want the text from e2 instead.

    =IF(AND(Data!D2="",Data!E2<>""),Data!E2,IF(AND(Data!D2="",Data!E2="",Data!F2<>""),Data!F2,IF(AND(Data!D2="",Data!E2="",Data!F2="",Data!G2<>""),Data!G2,IF(AND(Data!D2="",Data!E2="",Data!F2="",Data!G2=""),"",Data!D2))))

    Can anyone help with this as i cant work out the final part of the formula.

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Delivery Note Address Formula that excludes blank lines

    I prefer to do it in a User Function, gives you more scope to change it etc
    Please Login or Register  to view this content.
    use it as
    Please Login or Register  to view this content.
    or any other range you want
    If someone has helped you then please add to their Reputation

+ 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. Trying to create an automatic delivery note
    By DonGray in forum Excel General
    Replies: 3
    Last Post: 07-28-2015, 04:56 AM
  2. Shift address lines over to fill blank spaces
    By TheGift73 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-10-2013, 10:57 AM
  3. [SOLVED] Help with the design of my delivery note
    By Alanna R in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2013, 11:12 AM
  4. Replies: 3
    Last Post: 04-09-2012, 02:53 PM
  5. Create Address Cell - removing blank lines
    By SpeedsIE in forum Excel General
    Replies: 2
    Last Post: 06-24-2010, 08:49 AM
  6. [SOLVED] ?? Extra blank lines in 'address' cell after exporting to Excel
    By Hadyn Pkok in forum Excel General
    Replies: 4
    Last Post: 04-15-2005, 07:07 PM
  7. Delivery note or delivery order sheet
    By Roy Istanbouli in forum Excel General
    Replies: 1
    Last Post: 01-21-2005, 02:06 PM

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