excel table help

[geo]

Name Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7

Bill Buffalo Pittsburgh Utica Jacksonville Buffalo Houston Dallas
George Pittsburgh Buffalo Dallas Seattle Indy Carolina Miami
Lisa
Mark
Cherie
Nicole
Sue

What I want is this in the main spreadsheet. The users from week to
week pick
a city. (preferably picking cities from some sort of table) So that
the spelling
etc. is consistent. THE CATCH is no user can pick the same city
twice.
So if you look at user BILL they picked BUFFALO in Week 1 and BUFFALO
again in Week 5. I can't have that. When it happens I'd like to be
notified
that it happened and what week it was previously picked.



I'm just not sure how to set this up. So that I can set up like a lookup
table of valid
cities. Then in the main spreadsheet allow users to select cities from the
lookup table
from week to week. Then in also have a rule of some sort flagging me if a
user has
picked the same city previously?

 

[Pete]

The first bit is quite easy - use Data Validation. Compile your list of
cities, let's say in cells Z1 to Z50. The highlight all the cells under
Week1 - Week7 for as many user names as you have and select Data |
Validation with List pointing to $Z$1:$Z$50. Each of these cells will
now have a pull-down scrollable list from which to select a city.

I'll have to think about the second part ...

Pete

 

[Dave Peterson]

How about using Data|Validation to display the list, but then using a formula
that shows if the choices are ok.

I put the person's name in column A, the warning message in column B, and the
choices in column C:M.

Then in B1, I put this formula:
=IF(COUNTA(C1:M1)<>SUMPRODUCT((C1:M1<>"")/COUNTIF(C1:M1,C1:M1&"")),
"Error!!","Ok")
(one cell)

Debra Dalgleish has some very nice instructions (including pictures) for working
with data|validation at:
http://www.contextures.com/xlDataVal01.html

 

[geo]

COOL that worked. Learned something new today

Thanks,

 

[geo]

COOL that worked. Learned something new today

Thanks,

 

[geo]

Just trying to understand the formula now so I can get a better
understanding of how it works.

CountA is just counting all the data in the particular row right? So if I
have 7 non-blank entries it returns a 7.

SUMPRODUCT if my entries are text (Cities) what is this returning?

COUNTIF is that doing the compare each cell? If equal it prints error else
it prints ok?

Thanks just trying to fully understand.

 

[Dave Peterson]

=counta() does count the cells that have something in it (a formula or a value)

SUMPRODUCT((C1:M1<>"")/COUNTIF(C1:M1,C1:M1&""))
is a neat way to count unique items in a range.

You can see how it works with a little experiment.

Put
a
b
a
a
b
c

In A1:A6.

Put this formula in B1 and drag down to B6
=1/COUNTIF($A$1:$A$6,A1)

You'll see:
0.333333333
0.5
0.333333333
0.333333333
0.5
1

The a's all have .3333 next to them and there are 3 a's.
The b's have .5 next to them and there are 2 b's.
and c has a 1 and there's only 1 of them.

So the formula is counting the number of times a value is in that list and
dividing it into 1.

Add them all up and you get 3--and there are 3 unique entries.

Same with your formula.