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

1. ## 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. ## Re: Cross-Referencing Data in Different Worksheets

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

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.

3. ## Re: Cross-Referencing Data in Different Worksheets

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

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. ## Re: Cross-Referencing Data in Different Worksheets

Originally Posted by DemonCipher13
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.

5. ## Re: Cross-Referencing Data in Different Worksheets

Originally Posted by Jerry Sullivan
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. ## Re: Cross-Referencing Data in Different Worksheets

Delighted to hear that worked. Cheers!