Cell References
 

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.

Cell References
 

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Toph" wrote:

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.