+ Reply to Thread
Results 1 to 4 of 4

Formula Help - Display All Records with Criteria X on Sheet1 from Pivot on Sheet2

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    58

    Formula Help - Display All Records with Criteria X on Sheet1 from Pivot on Sheet2

    I have inherited a workbook that reports which in-house tax codes apply to which online orders in our database, and I have discovered a formula is broken. My attached has two tabs - in "Tax Codes to Order #s", the user pastes data in cols. A-C that sorts in the pivot in cols. E-F. Cell B6 of "Order List" copies whichever tax code number is input in cell B3. I then need all orders and invoice amounts to populate in the "Order List" tab from the "Tax Codes" tab if the associated tax code is input into B3.

    Currently, the first "Order List" order line (DE6) recognizes if the tax code has an associated order and pulls the first entry. However, below this are offset formulas that are not configured to stop pulling from the "Tax Codes" sheet when the tax code changes (for example, in the attached, the default code setting is 46464 and pulls 9 orders - referencing "Tax Codes", only the first four order rows associate to 46464).

    A macro ultimately runs against the "Order List" page and publishes it based on each code number, so the layout of this page (most of which has been removed in the attached) should not be changed (i.e. I can't simply have a pivot that the user can sort on this page instead of formulas).

    Any help would be appreciated!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-11-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Formula Help - Display All Records with Criteria X on Sheet1 from Pivot on Sheet2

    Hi Ninjan,

    Please find attached your solution.
    Attached Files Attached Files
    Ravikumar,

    * Please Add Reputation if solved.

  3. #3
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    58

    Re: Formula Help - Display All Records with Criteria X on Sheet1 from Pivot on Sheet2

    Thanks so much, fhsbet!! This works perfect! I am a little confused how the ROW parameter works you included (i.e. ROW(A1)) - can you further explain this?

  4. #4
    Forum Contributor
    Join Date
    07-11-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: Formula Help - Display All Records with Criteria X on Sheet1 from Pivot on Sheet2

    Hi,

    It will return the row number. if you drag A1 via row, next row will give you result as A2, A3 and so on. Instead of giving numbers manually we can use Row("A1") to return row number.

    Row (A1) = 1
    Row(A2) = 2
    Row(A3) = 3 .....

+ 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: 6
    Last Post: 01-28-2017, 04:04 AM
  2. Replies: 6
    Last Post: 07-25-2013, 02:58 PM
  3. How to display Sheet2 data in Sheet1 using VBA
    By jllav03 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2010, 01:09 PM
  4. Look for value in sheet1 and display in sheet2
    By fs_fra in forum Excel General
    Replies: 8
    Last Post: 03-30-2010, 12:27 PM
  5. Copying records from sheet1 to sheet2??
    By j2dizzo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-06-2005, 11:29 AM
  6. [SOLVED] Move Records from Sheet1 to Sheet2
    By Jim May in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-29-2005, 12:05 AM
  7. Replies: 3
    Last Post: 04-01-2005, 03:24 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