+ Reply to Thread
Results 1 to 10 of 10

Extracting info from a list into a table "report"

  1. #1
    Registered User
    Join Date
    10-12-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Question Extracting info from a list into a table "report"

    Hi there,

    I'm trying to create a report sheet in Excel 2003 so that it extracts data from a large list into a table specific by year (which can be changed by the user), and with different sections separated by month. Here's the sheet attached:
    yearly summary test1.xls

    The list is the first tab and the second tab is the report 'Yearly Summary'. In the second worksheet the year is contained in B1, with each month in that year being a "mini table". My goal is to eventually get it so that it extracts the data, then create a macro that hides blank rows automatically...but I'm struggling even with the first part!

    Taken from another sheet I've tried to get it so that the Z number from the first worksheet is extracted in "Yearly Summary" cell C5 to 8 if it is occuring in January, and then my intention was to create it so that it extracted it just when it was in January AND the year inputted into cell B1. However, for whatever reason I can't even get it to extract the data just based on the month yet. Can anyone help?

    Thank you!

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Extracting info from a list into a table "report"

    In C5 this ARRAY formula,gives you what you need?

    =INDEX('Stability Timelines'!A$2:A$1000,SMALL(IF(MONTH('Stability Timelines'!$H$2:$H$1000)=1,ROW('Stability Timelines'!A$2:A$1000)-1),ROW('Stability Timelines'!A1)))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    10-12-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Extracting info from a list into a table "report"

    Awesome thank you! Is there anyway to expand it so it gives results just for January of the year put in 'Yearly Summary'!B1 ? I tried this and got a bizarre result and then #NUM errors:

    =INDEX('Stability Timelines'!A$2:A$1000,SMALL(IF(AND((MONTH('Stability Timelines'!$H$2:$H$1000)=1),(('Stability Timelines'!$J$2:$J$1000)='Yearly Summary'!B1)),ROW('Stability Timelines'!A$2:A$1000)-1),ROW('Stability Timelines'!A1)))

    Is there another way to do this?

    Thanks again

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Extracting info from a list into a table "report"

    Try in this way..

    =INDEX('Stability Timelines'!A$2:A$1000,SMALL(IF((MONTH('Stability Timelines'!$H$2:$H$1000)=1)*(YEAR('Stability Timelines'!$J$2:$J$1000)=$B$1),ROW('Stability Timelines'!A$2:A$1000)-1),ROW('Stability Timelines'!A1)))

  5. #5
    Registered User
    Join Date
    10-12-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Extracting info from a list into a table "report"

    Awesome thank you!!! I've put the following around it for when it's an error:

    =IF(ISERROR(INDEX('Stability Timelines'!A$2:A$1000,SMALL(IF((MONTH('Stability Timelines'!$H$2:$H$1000)=1)*(YEAR('Stability Timelines'!$J$2:$J$1000)=$B$1),ROW('Stability Timelines'!A$2:A$1000)-1),ROW('Stability Timelines'!A1)))),"",INDEX('Stability Timelines'!A$2:A$1000,SMALL(IF((MONTH('Stability Timelines'!$H$2:$H$1000)=1)*(YEAR('Stability Timelines'!$J$2:$J$1000)=$B$1),ROW('Stability Timelines'!A$2:A$1000)-1),ROW('Stability Timelines'!A1))))

    Not sure if there's a shorter way to do this.

    For the comments field (which will be blank I tried to put this in, so that it didn't display an error AND was blank when it gave a 0.

    =IF(ISERROR(INDEX('Stability Timelines'!N$2:N$1000,SMALL(IF((MONTH('Stability Timelines'!$H$2:$H$1000)=1)*(YEAR('Stability Timelines'!$J$2:$J$1000)=$B$1),ROW('Stability Timelines'!N$2:N$1000)-1),ROW('Stability Timelines'!A1)))),"",IF(INDEX('Stability Timelines'!N$2:N$1000,SMALL(IF((MONTH('Stability Timelines'!$H$2:$H$1000)=1)*(YEAR('Stability Timelines'!$J$2:$J$1000)=$B$1),ROW('Stability Timelines'!N$2:N$1000)-1),ROW('Stability Timelines'!A1)))=0,"",INDEX('Stability Timelines'!N$2:N$1000,SMALL(IF((MONTH('Stability Timelines'!$H$2:$H$1000)=1)*(YEAR('Stability Timelines'!$J$2:$J$1000)=$B$1),ROW('Stability Timelines'!N$2:N$1000)-1),ROW('Stability Timelines'!A1)))))

    Again it's pretty long.


    Finally I was wondering if there was any way that, based on whether the cell in column M of the first worksheet says "completed" the cell could be highlighted/crossed out. Is there any way to do this you know of? I tried playing with conditional formatting but couldn't get it to work...

    Thanks so much for your help!!

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Extracting info from a list into a table "report"

    1) As you use Excel 2003, you can not use IFERROR, that is much more shorter. So IF(ISERROR...is the way to go.

    2) You have already a Conditional formatting rule that highlight the rows that in column M, the word "Complete", exists.. Do you mean something else that i can not understand?

  7. #7
    Registered User
    Join Date
    10-12-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Extracting info from a list into a table "report"

    I meant so that in the "Yearly Summary" worksheet it is also highlighted when highlighted in the "Stability Timelines" worksheet. Do you know if this is possible?

    Thanks for all your help with this!!!

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Extracting info from a list into a table "report"

    See if this can helps you..
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-12-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Extracting info from a list into a table "report"

    Yes that worked great thank you! I altered it for all of the cells. Thank you!!!

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Extracting info from a list into a table "report"

    ..........................

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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