Cross-Referencing Data in Different Worksheets

DemonCipher13

New Member
Joined
May 6, 2014
Messages
14
I have three worksheets I'm trying to work from. We will call them Worksheet 1, 2, and 3.

I am trying to fill a column in worksheet 1, we will call it "Column B" for accuracy purposes.

In order to fill this column, I would like to cross reference the data from Worksheets 2 and 3, and if a match is found, transpose it into "Column B" from Worksheet 1.

For example. On Worksheet 2, "Column B," I have a string of data. Let's call this E, F, G, H, and I. And on the same worksheet, in "Column A," I have a particular reference number for each individual entry from "Column B." The same is the case with Worksheet 3, "Column A" and "Column B." What I want to do is this. If the data in Worksheet 2, "Column B" matches the data in Worksheet 1, "Column B," and/or if the data in Worksheet 3, "Column B" matches the data in Worksheet 1, "Column B," I want to be able to post a formula throughout Worksheet 1, "Column B," to cross reference both the other worksheets. This can also be done with just Worksheet 1 and Worksheet 2, or Worksheet 1 and Worksheet 3, I just don't want to have to enter 10,000 cells of individual data.

I hope I made this clear.

In other words, IF W2-CB matches W1-CC, then input equivalent row data from W2-CA into W1-CB.

Maybe THAT will explain it better.
 
Last edited:
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.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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. ;)
 
Upvote 0
I have 2010.

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

Ok good! :LOL:


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:
Upvote 0
Ok good! :LOL:


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
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top