+ Reply to Thread
Results 1 to 7 of 7

Pulling data from table

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    Pulling data from table

    I have a simple table, checkbook register on sheet1. However, on sheet 2, I am trying to display the records that have not been checked off.
    columns are:
    date, payee, chk#, debit, credit, balance, r

    As the records are reconciled, an x is placed in column "r".
    For the records that do not have an x in column r, I would like to have those records displayed on sheet 2.
    I'm trying to do this without writing vba.
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Pulling data from table

    Use this array formula in A2 of Sheet2 and pull it to the right to column G and then down.

    =IFERROR(INDEX(Sheet1!A$2:A$100,SMALL(IF(Sheet1!$G$2:$G$100="",ROW(Sheet1!G$2:$G$100)-1),ROWS($A$1:A1))),"")

    ....confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Please see attached file
    Attached Files Attached Files
    Last edited by AlKey; 11-16-2014 at 02:50 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Pulling data from table

    Thanks AlKey. But is there another way than to have to include sooo many cells prefilled with formula? what about something that is more dynamic or would I have to write vba?

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Pulling data from table

    Click just below left if it helps, Boo?ath?

  5. #5
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Pulling data from table

    Thank you boopathiraja, but I don't need to filter the data. I need to extract data based on if rows in the "r" column are empty. Thanks

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Pulling data from table

    Quote Originally Posted by DonW View Post
    Thanks AlKey. But is there another way than to have to include sooo many cells prefilled with formula? what about something that is more dynamic or would I have to write vba?
    Since I didn't know the size of your data set, I set the actual range A$2:A$100. You can replace 100 with a number more suitable for your needs.

  7. #7
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Pulling data from table

    Thanks AlKey.

+ 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. [SOLVED] Pulling Data from a table
    By Jryan123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-09-2014, 09:25 AM
  2. [SOLVED] Pulling Aggregate Data from Table 1 to Automate Drawing Conclusions from Table 2
    By QuantumPolagnus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-11-2013, 05:14 PM
  3. Pulling data from a table
    By pinkpeace7 in forum Excel General
    Replies: 7
    Last Post: 11-18-2009, 11:44 AM
  4. Pulling data out of a table
    By baroda11 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-17-2008, 12:40 AM
  5. Help with pulling data from a table
    By cattlegrid_79 in forum Excel General
    Replies: 3
    Last Post: 10-24-2006, 11:13 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