Validation List

[junoon]

Hi All,

I have a column consisting of Weeks. like this:

A | B
===============
Week 1 5646.00
Week 1 4542.00
Week 1 3563.00
Week 1 3235.00
Week 1 4511.00
Week 1 1543.00
Week 1 3454.00
Week 2 5454.00
Week 2 3464.00
Week 2 8784.00
Week 2 6132.00
Week 2 3123.00
Week 2 2454.00
Week 2 6345.00
Week 3 1445.00 etc.


I have taken the column A in a Data Validation list in Cell C1. Now,
whenever i select the Week, say Week1, i would get the average sale of
the week in Cell D1.

But my problem is that the list shows :

Week 1
Week 1
Week 1 etc....

I just want only one Week1, Week 2, Week 3...to reflect in the list.

Is there a formula to do the same, which i can input for the validation
list???

PLEASE Help ASAP.

 

[Biff]

Hi!

Use a different source for the drop list that contains only the unique items
from column A.

How many weeks do you have?

An easy way to do this is to enter this formula in some cell, say, J1:

="Week "&ROWS($1:1)

Now just copy down to get:

Week 1
Week 2
Week 3
etc
etc

Then use this as the source for the drop list.

Biff

 

[junoon]

Thanks Biff,

It works!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff