Dynamic Lookup

[glaupie]

Hi,

I have a sheet which shows responses from stores (just a "YES" if they responded). The fields are:
Store Number, Store Name, Date, Response.

Stores are supposed to respond every day but they do not all do so.
So I will for instance have 100 stores, on the 25th 20 will respond, on the 26th 15 will, and so forth.

So there are tons of duplicates:

5554	Store A	2012/06/25	YES
2234	Store B	2012/06/25	YES
1236	Store C	2012/06/25	YES
3939	Store D	2012/06/26	YES
1236	Store C	2012/06/26	YES
7788	Store E	2012/06/26	YES
5545	Store A	2012/06/27	YES
1236	Store C	2012/06/27	YES
7788	Store E	2012/06/27	YES
1896	Store F	2012/06/27	YES

<TBODY>
</TBODY>

So you can see store C responded on all 3 days, but stoe A only on the 25th and 27th.

Now on another master store sheet I want to see, per store, who responded one ach day:

Store #	Name	2012/06/25	2012/06/26	2012/06/27	2012/06/28
5554	Store A	YES		YES
1236	Store C	YES	YES	YES

<TBODY>
</TBODY>

Daily I will get new stores added to teh responded sheet (this is sorted by date).

On my master sheet I have a formula which looks to see if the store number is in the table and if the response is yes and the date is the same as in the formula. So in the column above for the 26th I have:

=IF(AND(VLOOKUP(B266,'Master Store Feedback'!B$50:E$68,4,FALSE)="Yes",VLOOKUP(B266,'Master Store Feedback'!B$50:E$68,3,FALSE)=DATEVALUE("2012/06/25"))=TRUE,"YES","#N/A")

And for the 27th I just change the date.

Problem is I always only pick up the first value in the lookup. So if a store responded on more than one day, then it will match on the first day, but then in the next column it will again find that store number at the top and thed ate won't match (even though it will if it keeps looking down the line).

No idea what to do - this is pre-exisiting files that I am trying to help someone with and not at all teh way I would have done it.

Basically I want that formula to only "do the lookup" on the range where the date is equal to the one in the formula. Without having to change the formula daily.

Any ideas?

G

 

[TheCman81]

You could just use a pivot table if that's an option? Then if you insert new data you can just refresh the pivot table.

 

[glaupie]

Yes I have a pivot in there - but the way they want to see it is on the master file with all the stores (including the ones with no responses).

Think I will redo the whole way it is set up - thank you anyway for the quick response.

G

 

[FC OLEPUT]

Would the index and match functions work here?

 

[glaupie]

I saw posts of them when I was googling around earlier - will read up a bit because I've nevr used them before