Cell References

[Toph]

I have one sheet with raw data (survey results) in a table, and a second with
some analysis. In the second, the first column has the title of a table
column (e.g. candidate), the second column contains a possible value for that
column (e.g. barack obama, john mccain, other, or not voting), and in the
third column I want to count how many times that value appears in that column.

So the analysis sheet looks something like this:

A, B, C
1 candidate, barack obama, formula
2 candidate, john mccain, formula
3 candidate, not voting, formula
4 gender, male, formula
5 gender, female, formula
6 ....

(note that A1:A3 are merged, as are A4:A5.)

For the formula, I could do:

=COUNTIF(SurveyResults[candidate],"barack obama")

But I want to use relative references so I can fill cells without changing
"candidate" to "gender" or whatever. So I want to do something like this to
replace the above formula:

=COUNTIF(SurveyResults[A1],B1)

The B1 reference is allowed, but the A1 reference isn't. Any ideas?

I know there are other ways of doing this, but it'd be very valuable to me
in the long run to figure this out.

 

[Barb Reinhardt]

I'd unmerge those cells and put the values in each cell. I'd then put a
conditional format on the first column like this

=COUNTIF(A$1:A1,A1)>2 (for cell A1). And change the font color to white.

The data is still there, but it's not visible to the user.