data populate according to dropdown

Here are two options, but I would go with option #2, the pivot table.

when I select any specific address in yellow box columns then that specif address will remove from bottom list.

I don't really understand what this means. The attachment you posted does not have any yellow box cells and although your picture shows yellow, please describe to me with an appropriate attachment as to what you are seeking.

With your new attachment, please use the formula I provided as you need it to work and tell what it doesn't do!

How about this...

On the data sheet I added a helper column, column D, and on the dashboard changed the named range for address and added NA to the formula in column F (Address) with some conditional formatting.

So I'm guessing you are wanting every day of the week (C11-C20, E11-E20 etc) removed from the list in B111:B4500 ?? If 4500 is correct then all those CSE formula could drag down your processing time.

Anyway, not sure I have the right formula solution for this, but I will ask for some help

jeffery have you checked?

Yes I have checked, but it appears nobody else has any suggestions. Sometimes your data is simply laid out wrong to force an over complicated formula into the structure.

More times than not, the best way to structure your data is in a database form. When you do this it makes analyzing the data much easier.

Example: All of the dates runs down column A, Store down column B, Address down column C etc.

Maybe you should consider the structure and then re-ask the question. Unless somebody else jumps in, I simply don't have the solution. Sorry.

No ideas so far. I am not sure I understand the instructions yet.

I did notice there are records nearly duplicate in rows 1702 and 1720. There are also two fields in 'data' that are "Territory". I am confused. Is this a duplicate record that we may delete or can the matching Territory be clarified?

	A	B	C	D	E	F
1	Territory		Territory		Banner	Address1
1702	ON_St_Catharines		BC - Kelowna		OVERWAITEA SAVE ON FOODS	3175 LAKESHORE RD
1720	BC_Kelowna		BC - Kelowna		OVERWAITEA SAVE ON FOODS	3175 Lakeshore Rd

I suspect this will cause problems. May we delete one of them?