+ Reply to Thread
Results 1 to 7 of 7

Reference Data but Skip Blank Fields?

  1. #1
    Registered User
    Join Date
    11-28-2017
    Location
    Nashville, TN
    MS-Off Ver
    MS Professional Plus 2013
    Posts
    3

    Question Reference Data but Skip Blank Fields?

    Hi there! I'm new, but I'll try my best at explaining what I'm looking for.

    I use a spreadsheet to populate 40+ 25-page contracts via Word mail merge. The contracts require a table with a list of items and their cost. However, each contract has different combinations of a set of 12 or so items with different costs for each item.

    I have the Excel document set up with two sheets; one (Sheet1) is the more user-friendly version in which the basic information can be entered, and the other sheet (Sheet2) references the data from Sheet1 but with all the necessary formatting for the merge. I currently have all of the cost data in Sheet 1 by columns, essentially something like this:

    Item1 Item2 Item3
    ContractA ACost1 ACost3
    ContractB BCost1 BCost2
    ContractC CCost2 CCost3
    ContractD DCost1 DCost3

    I would like to be able to reference this data in Sheet2, skipping over any blank fields like so:

    ContractA Item1 ACost1 Item3 ACost3
    ContractB Item1 BCost1 Item2 BCost2
    ContractC Item2 CCost2 Item3 CCost3
    ContractD Item1 DCost1 Item3 DCost3


    Does anyone know how I can do this?

    EDIT: An example workbook is attached.
    Attached Files Attached Files
    Last edited by KayEli; 11-29-2017 at 10:22 AM.

  2. #2
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    MS365 (PC) v.2108
    Posts
    257

    Re: Reference Data but Skip Blank Fields?

    Hello!

    Can you upload a example workbook? (One without any personal/privileged info, of course.) It should also include a 'mock up' of what you'd like the end result to be; this is the best way to ensure one of the gurus in here can help you out.

  3. #3
    Registered User
    Join Date
    11-28-2017
    Location
    Nashville, TN
    MS-Off Ver
    MS Professional Plus 2013
    Posts
    3

    Re: Reference Data but Skip Blank Fields?

    Sorry! I didn't realize that the spacing would not maintain. I'm not sure where to upload, but here's a link to an example workbook:

    https://drive.google.com/file/d/1zqB...ew?usp=sharing

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Reference Data but Skip Blank Fields?

    To attach an Excel file to your post,
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  5. #5
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    MS365 (PC) v.2108
    Posts
    257

    Re: Reference Data but Skip Blank Fields?

    I don't think people like following links to workbooks away from this site. Used the "Go Advanced" button next to "Post Quick Reply," then you'll scroll down to an option to "Manage Attachments;" it's pretty self explanatory after that.

  6. #6
    Registered User
    Join Date
    11-28-2017
    Location
    Nashville, TN
    MS-Off Ver
    MS Professional Plus 2013
    Posts
    3

    Re: Reference Data but Skip Blank Fields?

    I totally understand! I followed Sandy666's instructions and the workbook is now attached to the original post.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Reference Data but Skip Blank Fields?

    This proposed solution employs two formulas and a conditional formatting rule.
    The first formula* which populates the columns that display the 'Items' is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The second formula which populates the columns that display the cost is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The conditional formatting rule is applied to the cost columns and hides zero values.
    *The first formula must be array entered meaning that you must simultaneously press the Ctrl, Shift and Enter keys to activate.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 3
    Last Post: 04-27-2017, 02:13 PM
  2. Tell macro to skip blank cells in a range and only select those with data?
    By Belinea2010 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-15-2016, 08:33 PM
  3. Indicrect reference formula but data needs to skip rows.
    By mjrtoo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-21-2015, 01:00 PM
  4. [SOLVED] Skip blank cells in chart data
    By mehulkar in forum Excel General
    Replies: 8
    Last Post: 07-26-2015, 03:57 PM
  5. [SOLVED] Skip blank spaces in data validation list
    By skip2mylew in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-04-2013, 09:20 PM
  6. [SOLVED] If B2 = 1 then pull data into sheet 2, if B2 is blank skip to next 1
    By djauncey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2013, 10:22 AM
  7. Replies: 1
    Last Post: 09-04-2012, 05:40 PM

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