+ Reply to Thread
Results 1 to 3 of 3

Dynamic Range From Another Sheet Excluding Blanks

  1. #1
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Dynamic Range From Another Sheet Excluding Blanks

    Hello,

    What formulas could fill a range from another sheet that excludes the blanks if there aren't any parts (they may have been deleted).

    Please see attached file the Sheet1 has the source data and Sheet2 has the place for dynamic formulas.

    Thank you very much,
    Fred

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Dynamic Range From Another Sheet Excluding Blanks

    Please try at C21:S35

    =IFERROR(INDEX(Sheet1!$O:$Q,AGGREGATE(15,6,ROW(Sheet1!$O$3:$O$99)/(Sheet1!$O$3:$O$99>0),ROWS(C$21:C21)+INT(COLUMNS($C21:C21)/10)*15),MATCH(C$20,Sheet1!$O$2:$Q$2,)),"")

    Custom Fomat # to hide Zero
    or

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Dynamic Range From Another Sheet Excluding Blanks

    Thank you Bo_Ry it works great!

    I have thought of some other way and want to let you know.

    The reason I wanted this is because on the sources sheet some copy paste was happening which caused the formulas to have reference errors.

    On the destination sheet I converted all formulas to absolute reference using this code below:

    Please Login or Register  to view this content.
    This got rid of the errors so it seems much simpler to keep my original formulas which reference each cell in the source sheet.


+ 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. COUNTIFS excluding blanks cells in range
    By mcvideo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2018, 11:45 AM
  2. [SOLVED] Formula to count blanks and non-blanks with a dynamic range
    By brittdyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-12-2018, 07:45 AM
  3. database: Copy data from range excluding blanks plus one cell on another column
    By danwoltrs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2016, 10:24 AM
  4. Dynamic bar Chart - Largest to smallest - Excluding zeros and blanks
    By deanusa in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-15-2015, 01:31 AM
  5. [SOLVED] Create a list of uppercase data from a range excluding blanks and errors
    By PAexcel in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-17-2014, 01:32 PM
  6. Transposing column range to row while excluding blanks
    By jkhereford in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-15-2012, 12:33 PM
  7. [SOLVED] Define a range while excluding blanks
    By cowboy713 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-08-2012, 05:47 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