+ Reply to Thread
Results 1 to 2 of 2

Advanced Excel- Array formula issue

  1. #1
    Registered User
    Join Date
    04-15-2014
    Location
    hell
    MS-Off Ver
    Excel 2007
    Posts
    16

    Advanced Excel- Array formula issue

    Hey EXcel Geniuses,

    NOTE: All comments below refer to the Dropbox file (too large for the uploader) here is a link to download:https://www.dropbox.com/s/e1sguzb5kq...ject.xlsx?dl=0 . I have been working on an inventory purchase order creation form for a while and it used to work until I started creating larger lists. For some reason, with the attached PO creator - when I place a Qty anywhere below 1980 on the drum title sheet, (which feeds the Order Form sheet) The Array formula used to populate the order page is
    {=IFERROR(INDEX(complete!$A$2:$A$2024, SMALL(IF(complete!$G$2:$G$2024>0, ROW($H$5:$H$1982)-ROW($H$5)+1),ROWS($A$3:$A3))),"")}
    The attached document opens to the order form. If you go to the Drum Titles tab, you can see the quantities and prices of the items on the Order Form tab. Now place a qty anywhere below row 1980 and voila! the Order form is empty! Can anyone help??

    Thanks

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Advanced Excel- Array formula issue

    I didn't download your file.

    {=IFERROR(INDEX(complete!$A$2:$A$2024, SMALL(IF(complete!$G$2:$G$2024>0, ROW($H$5:$H$1982)-ROW($H$5)+1),ROWS($A$3:$A3))),"")}
    Try the formula like this...

    =IFERROR(INDEX(complete!$A:$A,SMALL(IF(complete!$G$2:$G$2024>0,ROW(complete!$G$2:$G$2024)),ROWS($A$3:$A3))),"")

    Still array entered.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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: 1
    Last Post: 01-29-2015, 02:26 PM
  2. Excel 2007 : Advanced filter issue
    By Nikeyg in forum Excel General
    Replies: 2
    Last Post: 04-06-2013, 05:53 AM
  3. Replies: 12
    Last Post: 01-18-2013, 05:23 AM
  4. Advanced issue; second instance of Excel will not go away!
    By Vacation's Over in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2005, 02:05 PM
  5. [SOLVED] Tricky array formula issue - Using array formula on one cell, then autofilling down a range
    By aspenbordr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2005, 11:05 AM

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