Formula for combining distinct values from multiple sheets named ranges

I would like to retrieve distinct names from multiple sheets named ranges and show them contiguously in another sheet. IE :

Sheet 1, TEST_Assignations named range
-Bob
-Ann
-Mario
-Charlie

Sheet 2, FORMATION_Assignations named range
-Ann
-Bob
-Jack

Sheet 3, MEP_Assignations named range
-Dany
-Paul
-Ann
-Bob

On my summary sheet I'd then get :
-Bob
-Ann
-Mario
-Charlie
-Jack
-Dany
-Paul

I did it successfully for one sheet but I don't get the logic quite fully yet in order to combine with other sheets named ranges (and I suspect my formula isn't even right from the beginning) :
{=IFERROR(IFERROR(INDEX(TEST_Assignations, MATCH(0, COUNTIF($I$1:I1, TEST_Assignations), 0)), INDEX(MEP_Assignations, MATCH(0, COUNTIF($I$1:I1, MEP_Assignations), 0))), "")}

Open in new window

So I'm using dynamic named ranges, these are the ranges from which I want to combine distinct values and they are named :
TEST_Assignations
FORMATION_Assignations
DICTIONNAIRE_Assignations
PRÉLIMMEP_Assignations
MEP_Assignations

Does these ranged names have to be further available to all workbook (ie scope of named range)?

Can someone help?
STIWasabiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Herbds7Commented:
Excel 2010, Tables, PivotTable
Unique names.
No formulas, no code, no Defined Names used.
Tables are dynamic.
http://www.mediafire.com/download/b5iv45uvrnm32fu/10_19_13.xlsx
0
STIWasabiAuthor Commented:
The Pivot Table doesn't get refreshed automatically when I add a new name...I need the list to be updated automatically.

Also where is the Multiple Consolidation Ranges option in Excel 2010/2013? In other words how can I achieve the same thing with more recent versions of excel?

Thanks
0
Herbds7Commented:
You can add an event macro that refreshes the PivotTable
whenever you add Table data.
The PivotTable Wizard is hidden in the Quick Access Toolbar options list.
I added a picture of it in my link.
My example is written in Excel 2010. Compatible with 2013.
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

STIWasabiAuthor Commented:
What would be the macro code ?
0
Herbds7Commented:
Excel 2010, Tables
With Event macro.
If you decide to go with macros,
then you can dispense with PivotTables
and do all the consolidation and filtering in the macro.
The macro will do much more complex Tables than shown,
if you expand the summary Table commensurately.
http://www.mediafire.com/download/90b49q2qdj988gl/10_19_13.xlsm
0
STIWasabiAuthor Commented:
While I play with the code, any ways to have the results displayed horizontally instead of vertically?

Thanks
0
Herbds7Commented:
VBA can do just about anything.
We would get to a satisfactory answer faster
if you gave us a full picture of your project.
0
STIWasabiAuthor Commented:
I have problem implementing your code. Will attach demo file soon. Thanks.
0
STIWasabiAuthor Commented:
OK file attached with fictitious data.

There are 4 source sheets (TEST, FORMATION, PRÉLIM-MEP, MEP) and the result sheet (RESSOURCES).
In each source sheet, the tables are in the Responsable column (D). The table must stay as is (D13-D18) because I need it to be dynamic if lines are inserted between the dark gray lines.
Select some ressources with the drop-down in column C (data validation based on Ressources sheet) and depending on the selection you made, you'll see the name replicated in column D or not based on a formula (only if ressources belongs to Rofni company - again see Ressources sheet).
Only the names appearing in the table (column D) of each source sheet must be reported back to the Ressources sheet table (column J).
Code triggering is in a special tab in the ribbon called Rofni.

The code executes fine without any error but the table is always empty. I need to add a twist for the table array since it depends on a user selection in Général sheet (but it's lopping fine).

Thanks
DEMO.xlsm
0
Herbds7Commented:
Excel 2010, Tables
I suggest you redo the entire workbook,
using Tables and Structured References as shown.
http://www.mediafire.com/download/z2n9bj7x3td58dz/10_22_13.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
STIWasabiAuthor Commented:
Cliking the Update  button multiples times yields different # of results half the time. I think something's still wrong.

I'd also like to avoid starting a new with a blank workbook. That would be a whole project in itself...

Thanks
0
Herbds7Commented:
My fictitious inputs are random number generators.
You want to avoid work? Understandable.
May my works serve the community.
0
STIWasabiAuthor Commented:
Working as provided, impossible to implement in my own file and also not a formula.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.