+ Reply to Thread
Results 1 to 6 of 6

Auto –populate data from one sheet to another based on month

  1. #1
    Registered User
    Join Date
    08-09-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Auto –populate data from one sheet to another based on month

    Hi - I'm close but not there.. please help
    The workbook contains a summary sheet with different criteria’s listed in a column.
    A different sheet where the data entry should be have a set of criteria’s listed in column A with 12 Months in the other columns
    User enters data per month. Only data for the current month should show up on the summary sheet.
    How can I get my formula straight so I dont get the #N/A. All month work but April. Also is there a formula combination between the index/match and vlookup? as I have 200 metrics in data entry sheets and only selected in summary - would cut down on formula writing - any help is super appreciated - thanks in advance - attached sample of concept
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Auto –populate data from one sheet to another based on month

    In B1 of your Summary sheet, change Apr to April to match what you have in E1 of your Data Entry sheet, or alternatively change E1 of your Data Entry sheet to Apr instead of April.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-09-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Auto –populate data from one sheet to another based on month

    Thank you. I caught that. Any advise on a vlookup / match formula for this case?

  4. #4
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Auto –populate data from one sheet to another based on month

    In Sub-Summary,
    Put this in B2:
    =INDEX('Data Entry'!$B$2:$E$7,MATCH($A2,'Data Entry'!$A$2:$A$7,0),MATCH(B$1,'Data Entry'!$B$1:$E$1,0))
    Copy down
    Last edited by Max, Singapore; 08-10-2012 at 11:00 AM.
    Max
    Singapore

  5. #5
    Registered User
    Join Date
    08-09-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Auto –populate data from one sheet to another based on month

    Max - this is great and just solved one of my problem - thank you. Now what if the criterias in Sub or Summary are not in order or not in the same cell alignment (say in B7) than in Data Entry and a vlookup is required. Can I Mix and MATCH? :-)

  6. #6
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Auto –populate data from one sheet to another based on month

    The expression will take care of that, via the 2 MATCHes. The order of the lookup data, eg Cats is immaterial. Same goes for the month col header eg April, ie you could easily copy the formula to return more than a single month's worth of results

+ 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