Have several sheets with data I need to match up to same row

[CDXB503]

Good morning everyone,

I have several sheets of data with a title in column A and a record count in column B. All of the sheets share some but not all of the same titles. So becasue of the difference in number of titles, the titles themself end up on different rows.

For example:

• Sheet 1 - Title A is on row 3 (record count = 25)
• Sheet 2 - Title A is on row 10 (record count = 18)
• Sheet 3 - Title A doesn't exist
• Sheet 4 - Title A is on row 5 (Record count = 5)

I need all of the titles on one page in the same column with subsequent columns populated with the record count.

For example:

• Column A - All titles
• Column B - Record count from Sheet 1
• Column C - Record count from Sheet 2
• Column D - Record count from Sheet 3
• Column E - Record count from Sheet 4

You can reference the attached excel file for a mock up of this scenario, with the data populated as I would like to have it done (I populated it manually)

Whoever can solve this will be my hero and I will owe you a very large beer!

Cody

[JohnTopley]

Try

In E8

=IF($B8="","",VLOOKUP($B8,INDIRECT("'"&E$2&"'!B2:D52"),2,0))

Copy down

NOTE: data in your sample is out of sync: colunm D should be column E etc

[Olly]

Your description and your file don't quite match.

Are you trying to consolidate the information in worksheets "Prospect Source Campaign", "Lead Source Campaign" and "MQL Last Activity Campaign", onto sheet "Attribution Model"?

[avk]

Try with in "D8" in sheet "Attribution Model"

Please Login or Register  to view this content.

In "E8"

Please Login or Register  to view this content.

In "F8"

Please Login or Register  to view this content.

Ensure SHIFT+CTRL+ENTER
copy paste down.
File attach for immediate ref.

[CDXB503]

Hey AVK, that's awesome!! I'm tryingto replicate it on my actual spreadhseet but can't figure it out. Could you help me one more time with this spreadsheet? Could you help me figure out the rest of the tabs I've created too...? For some reason I can't figure out how to use the formula or customize it myself yet.

Thanks a ton!

[CDXB503]

Using column B as the naming convension to match against

[JohnTopley]

Your file is full of VALUE errors where the match on TEXT fields is done. And your "Attrribution Model" tab has no "tab" headings as per your "Fake":

Removed ALL the value errors from "Prospect Source Campaign"

in B2 of "Attrubution Model"

=IFERROR(INDEX('Prospect Source Campaign'!$C$1:$C$52,MATCH('Attribution Model'!$A2,'Prospect Source Campaign'!$B$2:$B$52,0)),"")

Copy down

NOTE: your Data and headings are offset by one row: hence the range offset.

[CDXB503]

Ok, I see, those mistakes in formatting made it confsuing. I think this is all working just fine right now, let me keep messing with it before I mark is solved. Thanks a ton!

[CDXB503]

Hell YES! Thanks a ton AVK. Come to Portland, beers on me my friend!

[JohnTopley]

AVK ????

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[CDXB503]

Lol, my bad, both of you were a HUGE help! Thanks JohnTopley, sorry I wasn't looking at who posted that time. Will submit rep and solved. Thanks!