Data validation listing all associations but looking at 2 separate worksheets?

Bo_Ry-Thanks for your help with this expanded issue of mine with this project. The formula you provided is giving me the results from the opposite selection. I've highlighted in yellow on the specific data sheets the rows that would match the three cell match criterion; (see new workbook attachment) Thanks again!

Try this for "List"

Formula:

Please Login or Register  to view this content.

Thanks John! If I use your formula and scroll down, it will only provide me the 1st employer and not the 2nd affiliated with the individual. Do I need to edit anything per line? Thanks again!

Thanks Bo_Ry! I'm still having issues with the correct employer names populating, even with adding the "N" for NCP. Also for the CP I get #N/A. I assume I can drag this formula down to accommodate individuals who may have 5 or more past employers? I found the drop down data validation list displays the correct employers, but I don't see one of them listed in the formula section in column Q. Why is that? I've also been trying to understand the data validation part too. I see it says "=list". Where is this list that it references? The reason I ask, is that the drop down works for NCP employers, but doesn't list the CP employers. Sorry for so many questions, just trying to figure this out. Thanks again for your patience with me.

see attached: formula entered with Ctrl+Shift+Enter

John that is fantastic! It appears to be doing exactly what I need! Thank you! Could you explain how the data validation formula "=list" works? So I can try to have the references correct on real workbook? Appreciate the help!

=CHOOSE(1+('Employer Disclosure (case)'!$N$2="CP"),OFFSET('Employer Docket'!$N$2,MATCH('Employer Disclosure (case)'!$M$2,'Employer Docket'!$A$3:$A$99,),,COUNTIFS('Employer Docket'!$A$3:$A$99,'Employer Disclosure (case)'!$M$2)),OFFSET('Employer DocketCP'!$N$2,MATCH(1,INDEX(('Employer DocketCP'!$A$3:$A$99='Employer Disclosure (case)'!$M$2)*('Employer DocketCP'!$B$3:$B$99='Employer Disclosure (case)'!$M$3)*('Employer DocketCP'!$C$3:$C$99='Employer Disclosure (case)'!$M$4),),),,COUNTIFS('Employer DocketCP'!$A$3:$A$99,'Employer Disclosure (case)'!$M$2,'Employer DocketCP'!$B$3:$B$99,'Employer Disclosure (case)'!$M$3,'Employer DocketCP'!$C$3:$C$99,'Employer Disclosure (case)'!$M$4)))

If CP is picked then in CHOOSE(1+('Employer Disclosure (case)'!$N$2="CP the RED part is TRUE which resolves to 1 so the CHOOSE value is 2 , so it selects the formula in RED i.e looks at the CP data.

The MATCH statement does the comparisons against account & REF# And ID.

If you get problems post a file of your real workbook and we should be able to get the formulae aligned.

Appreciate that John! I can't get my original workbook down to the size that it would allow me to upload it, even with all of the data removed; it has many pages and is over 1,431kb saved as a binary file; which is bare bones.

The sample data validation (drop down menu) references "list" as how it locates the employers. It doesn't seem to be referencing off of the array formulas that I can tell at least by any association of a "list" title. (hope that makes sense). That is what is puzzling me. So if I go to my original file and for cell M6, I go to data validation and I select list as my option and then in the formula bar portion I need to reference what? The sample file in the formula section displays "=list" is that the section with all of your formulas or is it referencing the N columns of both data worksheets? Thanks again for taking the time to help!

You only need to supply 2 sheets which represent the CP/NCP sheets plus the "selection" sheet i.e where you have drop down for CP/NCP.

It references N in both cases and compares (MATCH) columns A, B and C.

You could ZIP (compress) the file if required.

Ok, John I'll try the zip. I removed all of the normal data and replaced with the test data.. so hopefully this will work. Really appreciate your help! The worksheet is titled Employer Disclosure (case) as it will open by default on a different worksheet due to macros.

I created a named range called Listx using the formula in post #11.

I changed the DV in M6 to use Listx: now looks OK.

Thanks John! Look forward to seeing the revised file!

Hopefully this OK.

John, that does exactly what I need! Can't thank you enough for taking the time to help!

You're welcome. Not forgetting Bo_Ry's contribution.

You both have helped immensely for sure! Thanks again!