none
Use a column as key between sheets. RRS feed

  • Question

  • Hi,

    i have an Excel 2013 file with many sheets; i need to extract data from them based on the value of a column, repeated on each sheet (the idea is that this colums works like a key).

    What is the best way to do this ?

    Thank you. 

    Monday, October 9, 2017 12:26 PM

All replies

  • linuxmc,
    re:  extract data from worksheets

    The best way for me would be to use VBA.
    But I've been doing that for 20 years.
    The code modules and the language comes with each Excel version.
    Finding and extracting data using VBA is a common task.

    Using a MAC/Linux OS probably should be avoided and windows10 / XL2016 is still having problems.
    The answer is what's best way for you?

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Tuesday, October 10, 2017 3:16 AM
  • Hi,

    Based on your description, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, October 10, 2017 8:27 AM
  • Hi linuxmc,

    I try to check the description.

    you had mentioned that," i need to extract data from them based on the value of a column, repeated on each sheet (the idea is that this colums works like a key)."

    so I try to create one example may help you.

    I create a workbook with 4 sheets.

    first sheet will contain sheet names in column A.

    other sheets contains the values which we will try to access based on values stored in sheet 1 column A.

    Code:

    Sub demo()
    Dim i As Long
    Dim numrows As Long
    Dim sht As String
    numrows = Range("A1", Range("A1").End(xlDown)).Rows.Count
    For i = 1 To numrows
    sht = Cells(i, 1).Value
    Debug.Print (ThisWorkbook.Sheets(sht).Range("A1"))
    Next i
    End Sub

    Output:

    if I misunderstand something from your above description then try to correct me, I will try to provide further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, October 12, 2017 2:55 AM
    Moderator
  • Hi linuxmc,

    is your issue is solved now?

    I find that after creating this thread, you did not done any follow up.

    if your issue is solved then I suggest you to post your solution here and mark it as an answer.

    if your issue is still exist then I suggest you to refer the suggestion given by myself.

    it may solve your issue.

    if then also you have any further question then let me know about it.

    I will try to provide you further suggestion to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 17, 2017 8:47 AM
    Moderator