+ Reply to Thread
Results 1 to 22 of 22

Getting data from another sheet within the same spreadseet by dropdown

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Getting data from another sheet within the same spreadseet by dropdown

    Hi,
    I have 3 sheets in the excel file.1 main sheet and 2 sheets with information.What I am trying to do is I have created a drop-down list in the main sheet to select either one of these 2 sheets and display all the info into the main sheet.I have tried one of the methods posted here> http://www.excelforum.com/excel-gene...down-list.html and I just can't seem to get it working.I have attached my excel file for reference.Help is greatly appreciated.Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Hi,

    I have made some modification which can help u better .
    find the attached sheet .

    Punnam
    Attached Files Attached Files

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Getting data from another sheet within the same spreadseet by dropdown

    Got the solution..
    check the attachment..
    no Array No Vba..



    Don't forget to click *
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Hi,
    thanks for yr fast response.Firstly because I am new to using more complicated excel functions,I would like to ask the explaination of =INDIRECT("'"&$C$7&"'"&"!"&ADDRESS((ROW()-15),(COLUMN()-1))),as in how to I interpret the command as I have been trying to search google but can't seem to grasp a understanding of it,that's why I have to post in forums for help

    Also,I would like to ask must the drop down list name correspond with the sheet name?As in drop down list "Govt employees" and sheet name must also be "Govt employees"?

  5. #5
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Getting data from another sheet within the same spreadseet by dropdown

    it looks complex but not .

    1) I used indirect to create reference to worksheet using the sheet Name, And cell address i,e row Number & Column Number .
    when we link a cell from one sheet to another sheet =Sheet6!E11 in this "Sheet6!" Sheet name E is the column & 11 is row number .i have used & to join the data i.e concatenate ,""to considered as text or special Charters
    2) Address which is created based on row number & Column
    3) Row() gives me the number of current row
    4) Column Give the column of current column
    5) Yes in my case it is necessary to maintain the sheet name & Data validation to be same .
    Punnam

  6. #6
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Quote Originally Posted by Vikas_Gautam View Post
    Got the solution..
    check the attachment..
    no Array No Vba..



    Don't forget to click *
    Hi Mr Vikas,
    Thanks for yr input.
    I would like to ask abt the following code

    =IF($C$7='A2'!$A$2,INDEX('A2'!$A$6:$B$20,ROW($A3),COLUMN(B$1)),INDEX('A1'!$A$6:$B$20,ROW($A3),COLUMN(B$1)))

    this index is array or ref?
    ROW($A3) and column (B$1):what i understand is $A3 is absolute column...why is it that it's put inside the ROW()?
    Last edited by fajl; 07-17-2014 at 05:26 AM.

  7. #7
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Hi,
    I am reading around that vlookup is possible too.When i look ard for tutorials,most of them reference within the same sheet.I would like to ask for my case how do i reference it from another sheet to the main sheet?

  8. #8
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Getting data from another sheet within the same spreadseet by dropdown

    I hope when I am explaining you this ..
    You know something about INDEX function...
    INDEX - returns the cell value at the intersection of row and column in the table array provided...

    what I did is...
    first I match the $C$7 with the 'A2'!$A$2 for your Govt. and Non Govt Clause..
    then If It matches then INDEX formula come into action..
    my INDEX function took 3 argument..
    first is the tableArray... ie the whole table in the other sheets..
    second is RowNo.. ie the row in the table array you want to refer..
    third one is ColumnNo. ie the column in the table array you want to refer..

    the reason behind I use $A1 for rows and A$1 for columns is that..
    when I drag the formula vertically, it must give value in the next row but in the same column(that is why $ is used before A) in the Index provided..
    and similiarly...
    when I drag the formula horizontally, it must give value in the next column but in the same row(that is why $ is used before 1) in the Index provided..

    I hope you have understood this..

    Don't forget to click *

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Quote Originally Posted by Vikas_Gautam View Post
    I hope when I am explaining you this ..
    You know something about INDEX function...
    INDEX - returns the cell value at the intersection of row and column in the table array provided...

    what I did is...
    first I match the $C$7 with the 'A2'!$A$2 for your Govt. and Non Govt Clause..
    then If It matches then INDEX formula come into action..
    my INDEX function took 3 argument..
    first is the tableArray... ie the whole table in the other sheets..
    second is RowNo.. ie the row in the table array you want to refer..
    third one is ColumnNo. ie the column in the table array you want to refer..

    the reason behind I use $A1 for rows and A$1 for columns is that..
    when I drag the formula vertically, it must give value in the next row but in the same column(that is why $ is used before A) in the Index provided..
    and similiarly...
    when I drag the formula horizontally, it must give value in the next column but in the same row(that is why $ is used before 1) in the Index provided..

    I hope you have understood this..

    Don't forget to click *

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hi,
    Ok,I got the INDEX('A2'!$A$6:$J$24,ROW($A4),COLUMN(A$1))
    What I am still confused is 'A2'!$A$2. and 'A1' What i understand is it would ref A2,if false it will ref A1 right?If I have 10 sheets,do i need to reference each sheet like eg A3 A4 etc...?

  10. #10
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Getting data from another sheet within the same spreadseet by dropdown

    No my friend you are taking it wrong..
    Now I will try to explain it only the row part..

    Row($A1) means row 1 in the Index provided...

    now when I drag the formula.. horizontally the row no this $A1 will not change .. (because I need not to change it as I would like to be in first row) and also Column part ie. $A1 will not change as I have used $ to fix it...

    now the second aspect
    when I drag it vertically.. the row No will change to 2 (as I would like to refer to next row when I drag the formula..) because the i haven't fixed the row part...

    I think that will help you to get rid of your confusion...


    Don't forget to click *

  11. #11
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Getting data from another sheet within the same spreadseet by dropdown

    I you wanna clarify it more then use formula auditing-----> evaluate formula....

    on each cell of the result...



    Don't forget to click *, if it helps you someway...

  12. #12
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Quote Originally Posted by Vikas_Gautam View Post
    No my friend you are taking it wrong..
    Now I will try to explain it only the row part..

    Row($A1) means row 1 in the Index provided...

    now when I drag the formula.. horizontally the row no this $A1 will not change .. (because I need not to change it as I would like to be in first row) and also Column part ie. $A1 will not change as I have used $ to fix it...

    now the second aspect
    when I drag it vertically.. the row No will change to 2 (as I would like to refer to next row when I drag the formula..) because the i haven't fixed the row part...

    I think that will help you to get rid of your confusion...


    Don't forget to click *
    Hi Sir,
    Thanks for yr reply.
    From my understanding now since $A1 means absolute column,putting it in ROW($A1) means it stays at the column but moving vertically in rows to get the values right?
    How about 10 sheets of data to index?

  13. #13
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Getting data from another sheet within the same spreadseet by dropdown

    Check the solution...
    for 10 sheets..

    no vba..
    easy and simple....

    check the attached file...



    Don't forget to click *
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Quote Originally Posted by Vikas_Gautam View Post
    Check the solution...
    for 10 sheets..

    no vba..
    easy and simple....

    check the attached file...



    Don't forget to click *
    Thanks.will try it out

  15. #15
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Getting data from another sheet within the same spreadseet by dropdown

    I use that because .... the range provided in the index function ... have index no 1 for its 1st row..
    and row(A17:A26) returns an array for 17 to 26. so I deduct Row(a16) ie 16 to make it an array of 1 to 10..

    I hope you can understand...

    Don't forget to click *

  16. #16
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Quote Originally Posted by Vikas_Gautam View Post
    I use that because .... the range provided in the index function ... have index no 1 for its 1st row..
    and row(A17:A26) returns an array for 17 to 26. so I deduct Row(a16) ie 16 to make it an array of 1 to 10..

    I hope you can understand...

    Don't forget to click *
    Hi,
    Thx I managed to get it working...+rep!!
    One problem I encountered is I use vlookup(true) to look up age.Some gave correct results,some did not.Also,isit possible to have 2 drop down lists to index?

    I have extracted the portion of my excel that I am working on into the govt non govt.xls.(Cell H6,C11).
    Can I request help on this?
    Attached Files Attached Files

  17. #17
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Getting data from another sheet within the same spreadseet by dropdown

    yes we can include Pension or Non Pension......
    but where you gonna mention pension or non pension and what effect its gonna have on master data...?

    just as you have mentioned govt or non govt in each of the sheet...

    Don't forget to click *

  18. #18
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Quote Originally Posted by Vikas_Gautam View Post
    yes we can include Pension or Non Pension......
    but where you gonna mention pension or non pension and what effect its gonna have on master data...?

    just as you have mentioned govt or non govt in each of the sheet...

    Don't forget to click *
    Hi,
    My intention was to first select either Govt or Non Govt from the 1st drop down list,then 2nd drop down list would be either pensionable or non-pensionable,and the relevant sheet would be displayed.
    Eg. Sheet A1 is Non Govt No Pension
    Sheet A2 is Non Govt Pension
    Sheet A3 is Govt No Pension
    Sheet A4 is Govt Pension
    I tried to modify the code =INDEX($K$17:$K$26,LARGE(IF($J$17:$J$26=$C$7:$C$8,ROW($K$17:$K$26)-ROW($K$16)),1)) but can't get it to work.

  19. #19
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Okay I got it....

    wait for my reply...

  20. #20
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Got the solution...

    check the attachment..
    Only one formula has been change ..
    rest all is same..



    Don't forget to click *
    Attached Files Attached Files

  21. #21
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Getting data from another sheet within the same spreadseet by dropdown

    I have used a different approach. Instead of lengthy formulae, I have used Named Ranges. I also used a separate range to get H6 to have the correct display.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  22. #22
    Registered User
    Join Date
    07-16-2014
    Location
    SG
    MS-Off Ver
    2003
    Posts
    9

    Re: Getting data from another sheet within the same spreadseet by dropdown

    Hi,
    I used the combo of vikas and ndm and it works!!
    I am now computing all my data and I think it's working perfect.
    I will mark thread as solved but if I have any problems I will start another thread.
    Kudos to this forum for all the help!!esp vikas.U guys rock!

    +rep!
    Last edited by fajl; 07-30-2014 at 04:42 AM.

+ 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] Populate data on another sheet based on dropdown criteria from the first sheet
    By ARod0726 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2013, 01:36 PM
  2. [SOLVED] Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet data
    By EMLalan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-07-2012, 09:46 AM
  3. UDF in excel to use a dropdown list in a sheet, to push data to other sheet.
    By elnicko in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2011, 08:13 PM
  4. [SOLVED] Last row in spreadseet
    By Tony Wainwright in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-16-2005, 04:05 PM
  5. [SOLVED] adding data from one sheet to another sheet as a dropdown list bo.
    By gatorguy in forum Excel General
    Replies: 1
    Last Post: 02-18-2005, 07:06 PM

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