# Cross-Referencing Data in Different Worksheets

#### DemonCipher13

##### New Member
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.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Jerry Sullivan

##### MrExcel MVP
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.

#### DemonCipher13

##### New Member
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.

#### Jerry Sullivan

##### MrExcel MVP
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:

#### DemonCipher13

##### New Member
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

#### Jerry Sullivan

##### MrExcel MVP
Delighted to hear that worked. Cheers!

1,102,134
Messages
5,484,929
Members
407,474
Latest member
Pam Sander

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
• Please i need your help to create formula
I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
• Got error while adding column and filter
Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...