Cross-Referencing Data in Different Worksheets
Page 3 of 3 FirstFirst 123
Results 21 to 26 of 26

Thread: Cross-Referencing Data in Different Worksheets
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    I'm effectively a novice at Excel, so please enlighten me.

    And I would want them to remain blank.

    Thank you, again, for all the help. You seem like you may do this for a living, but it is much appreciated.

  2. #22
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    You're welcome. We're all volunteers here.

    This article has some clear instructions on how to use AutoFilters.

    How to Use AutoFilter in MS Excel: 5 Steps (with Pictures)

    These instructions would apply to 2007, 2010 or 2013 versions of Excel.

    Do you know which one you have? If you have earlier than Excel 2007 the formula I suggest next will be a little different.
    Using Excel 2016

  3. #23
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Quote Originally Posted by Jerry Sullivan View Post
    You're welcome. We're all volunteers here.

    This article has some clear instructions on how to use AutoFilters.

    How to Use AutoFilter in MS Excel: 5 Steps (with Pictures)

    These instructions would apply to 2007, 2010 or 2013 versions of Excel.

    Do you know which one you have? If you have earlier than Excel 2007 the formula I suggest next will be a little different.
    I have 2010.

    I may be an Excel novice, but I'm definitely not a computer novice.

  4. #24
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Quote Originally Posted by DemonCipher13 View Post
    I have 2010.

    I may be an Excel novice, but I'm definitely not a computer novice.
    Ok good!


    Here's steps to follow. Make a back up copy of your workbook first just in case you need to go back to the starting point.

    1. Add AutoFilters to your entire data range on Sheet Title 1.

    2. Filter Column B so that only blanks are shown.

    3. Paste this formula into Cell B5 (assuming it is the first visible Cell in Column B under the header).

    =IFERROR(INDEX('Title 2'!B:B,MATCH(C5,'Title 2'!C:C,0)),"")

    If some other row is your first visible Cell in Column B under the header, change C5 to reference that row instead.

    4. Copy Cell B5, then paste the formula so that fills all the visible cells below it in Column B down to the bottom of your data range.

    5. Clear the AutoFilters by clicking the big Funnel icon in the Ribbon > Data tab > Sort & Filter group. You should see the Autofilter drop down symbols disappear from Row 1.

    6. As a last important step, you need to convert the formulas that are in Column B to constant Values. To do that Select the Entire Column B by clicking on the Column Header "B".

    7. Copy all the Cells in Column B (use Ctrl+C) or the ribbon. Then from the Ribbon > Home tab > Clipboard group > click on the down arrow under the Paste icon. Then click any one of the 3 Paste Values Icons.

    Please let me know if any of those steps are unclear.
    Last edited by Jerry Sullivan; May 8th, 2014 at 06:21 PM.
    Using Excel 2016

  5. #25
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Quote Originally Posted by Jerry Sullivan View Post
    Ok good!


    Here's steps to follow. Make a back up copy of your workbook first just in case you need to go back to the starting point.

    1. Add AutoFilters to your entire data range on Sheet Title 1.

    2. Filter Column B so that only blanks are shown.

    3. Paste this formula into Cell B5 (assuming it is the first visible Cell in Column B under the header).

    =IFERROR(INDEX('Title 2'!B:B,MATCH(C5,'Title 2'!C:C,0)),"")

    If some other row is your first visible Cell in Column B under the header, change C5 to reference that row instead.

    4. Copy Cell B5, then paste the formula so that fills all the visible cells below it in Column B down to the bottom of your data range.

    5. Clear the AutoFilters by clicking the big Funnel icon in the Ribbon > Data tab > Sort & Filter group. You should see the Autofilter drop down symbols disappear from Row 1.

    6. As a last important step, you need to convert the formulas that are in Column B to constant Values. To do that Select the Entire Column B by clicking on the Column Header "B".

    7. Copy all the Cells in Column B (use Ctrl+C) or the ribbon. Then from the Ribbon > Home tab > Clipboard group > click on the down arrow under the Paste icon. Then click any one of the 3 Paste Values Icons.

    Please let me know if any of those steps are unclear.


    Jerry, I have a gaggle of medical professionals over my desk wowing and praising me. Thank you so much for all your help! :D

  6. #26
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Delighted to hear that worked. Cheers!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •