+ Reply to Thread
Results 1 to 5 of 5

How to populate a table using the intersections based on a criteria

  1. #1
    Registered User
    Join Date
    12-12-2009
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    21

    How to populate a table using the intersections based on a criteria

    Hi,

    What I am trying to achieve is, my data is in a sheet, there is data for different cities. In another sheet, I have a same format blank table, and a list of cities. I want to be able to select the city and automatically show the data from the previous sheet.

    Please see attached. Thanks in advance.
    Attached Files Attached Files
    Last edited by shanipk82; 09-07-2011 at 05:27 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: How to populate a table using the intersections based on a criteria

    One way:

    Cell E7: =OFFSET(Kaynak!$A$1,MATCH($C$6,Kaynak!$C:$C,0)+ROW($E1)-1,COLUMN(E$1)-1,1,1)

    Copy down and across.


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-12-2009
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: How to populate a table using the intersections based on a criteria

    Quote Originally Posted by TMShucks View Post
    One way:

    Cell E7: =OFFSET(Kaynak!$A$1,MATCH($C$6,Kaynak!$C:$C,0)+ROW($E1)-1,COLUMN(E$1)-1,1,1)

    Copy down and across.


    Regards
    That worked perfectly, thanks a lot. If you have time, please explain the formula briefly for my knowledge.

    Thanks again.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: How to populate a table using the intersections based on a criteria

    You're welcome. Thanks for the rep.

    MATCH($C$6,Kaynak!$C:$C,0) finds the relevant row in the Kaynak sheet using the result from the data validation drop down box.

    ROW($E1)-1 and COLUMN(E$1) just provide suitable offsets so that the formula can be dragged across and down ... rather than hard coding 1, 2, 3, ... etc

    Try putting "=ROW(E1)" in a cell ... it will return 1; "=COLUMN(E1)" will return 5.

    I usually have to experiment a little to get the right reference ... but I'm getting better ;-)

    Regards

  5. #5
    Registered User
    Join Date
    12-12-2009
    Location
    Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: How to populate a table using the intersections based on a criteria

    Quote Originally Posted by TMShucks View Post
    You're welcome. Thanks for the rep.

    MATCH($C$6,Kaynak!$C:$C,0) finds the relevant row in the Kaynak sheet using the result from the data validation drop down box.

    ROW($E1)-1 and COLUMN(E$1) just provide suitable offsets so that the formula can be dragged across and down ... rather than hard coding 1, 2, 3, ... etc

    Try putting "=ROW(E1)" in a cell ... it will return 1; "=COLUMN(E1)" will return 5.

    I usually have to experiment a little to get the right reference ... but I'm getting better ;-)

    Regards


    Thanks, that was informative as well as helpful. Wish I could rep again

+ 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