How would i go about combining this string?

=IF(B43=Medicaid_Medical!$A$2,COUNTA(Medicaid_Medi cal!$A$4:$A$50)/COUNTA(Medicaid_Medical!$A$4:$A$50),0)

I need all of the A's to change according to the column they are
representing. I have A - Z in a column that I would like to reference so
that when I drag down the letters change accordingly. I know the row #'s will
change without the $ signs when I drag down, but I need the column letters to
change as I go down...

When I try:

=IF(B43=Medicaid_Medical!"&I43&"$2,COUNTA(Medicaid _Medical!"&I43&"$4:"&I43&"$50)/COUNTA(Medicaid_Medical!"&I43&"$4:"&I43&"$50),0)

Replacing the A with the cell that has A in it, it does not work... Is there
another way to do this? Transpose, somehow?

Check out the indirect and offset functions in help
(I assume you meant to use count if not counta in the denominater)
if not please change as needed.

=IF(B43=indirect("Medicaid_Medical!"&I43&"$2",COUN TA(offset(indirect("Medicaid_Medical!"&I43&"$4"),0 ,0,1,47)/COUNTIF(Medicaid_Medical!"&I43&"$4:"&I43&"$50),off set(indirect("Medicaid_Medical!"&I43&"$4"),0,0,1,4 7)

"IntricateFool" wrote:

How would i go about combining this string?

=IF(B43=Medicaid_Medical!$A$2,COUNTA(Medicaid_Medi cal!$A$4:$A$50)/COUNTA(Medicaid_Medical!$A$4:$A$50),0)

I need all of the A's to change according to the column they are
representing. I have A - Z in a column that I would like to reference so
that when I drag down the letters change accordingly. I know the row #'s will
change without the $ signs when I drag down, but I need the column letters to
change as I go down...

When I try:

=IF(B43=Medicaid_Medical!"&I43&"$2,COUNTA(Medicaid _Medical!"&I43&"$4:"&I43&"$50)/COUNTA(Medicaid_Medical!"&I43&"$4:"&I43&"$50),0)

Replacing the A with the cell that has A in it, it does not work... Is there
another way to do this? Transpose, somehow?

I am trying to get a percent of the number of fields being referenced from
another worksheet, so counta is actually what I am trying to use. (
counta(columnC)/counta(totalplans) to get a percent of fields complete) I am
confused by how the ,0,0,1,47 came into the function? I really appreciate
your help with this

"bj" wrote:

Check out the indirect and offset functions in help
(I assume you meant to use count if not counta in the denominater)
if not please change as needed.

=IF(B43=indirect("Medicaid_Medical!"&I43&"$2",COUN TA(offset(indirect("Medicaid_Medical!"&I43&"$4"),0 ,0,1,47)/COUNTIF(Medicaid_Medical!"&I43&"$4:"&I43&"$50),off set(indirect("Medicaid_Medical!"&I43&"$4"),0,0,1,4 7)

"IntricateFool" wrote:

How would i go about combining this string?

=IF(B43=Medicaid_Medical!$A$2,COUNTA(Medicaid_Medi cal!$A$4:$A$50)/COUNTA(Medicaid_Medical!$A$4:$A$50),0)

I need all of the A's to change according to the column they are
representing. I have A - Z in a column that I would like to reference so
that when I drag down the letters change accordingly. I know the row #'s will
change without the $ signs when I drag down, but I need the column letters to
change as I go down...

When I try:

=IF(B43=Medicaid_Medical!"&I43&"$2,COUNTA(Medicaid _Medical!"&I43&"$4:"&I43&"$50)/COUNTA(Medicaid_Medical!"&I43&"$4:"&I43&"$50),0)

Replacing the A with the cell that has A in it, it does not work... Is there
another way to do this? Transpose, somehow?

The ,0,0,1,47 makes the offset function use the range based on the referenced
cell and 46 cells to the right

the denominator factor in your equation
COUNTA(Medicaid_Medical!$A$4:$A$50)
will be the same as the numerator
COUNTA(Medicaid_Medical!$A$4:$A$50)

I missread your original request and screwed up my response also.

I now assume that the sheet in the numerator or the denominator is
incorrectly referenced.

=IF(B43=indirect("Medicaid_Medical!"&I43&"$2"),COU NTA(offset(indirect("Sheet1!"&I43&"$4"),0,0,1,47))/COUNTA(offset(indirect("Sheet2!"&I43&"$4"),0,0,1,4 7)),0)

"IntricateFool" wrote:

I am trying to get a percent of the number of fields being referenced from
another worksheet, so counta is actually what I am trying to use. (
counta(columnC)/counta(totalplans) to get a percent of fields complete) I am
confused by how the ,0,0,1,47 came into the function? I really appreciate
your help with this

"bj" wrote:

Check out the indirect and offset functions in help
(I assume you meant to use count if not counta in the denominater)
if not please change as needed.

=IF(B43=indirect("Medicaid_Medical!"&I43&"$2",COUN TA(offset(indirect("Medicaid_Medical!"&I43&"$4"),0 ,0,1,47)/COUNTIF(Medicaid_Medical!"&I43&"$4:"&I43&"$50),off set(indirect("Medicaid_Medical!"&I43&"$4"),0,0,1,4 7)

"IntricateFool" wrote:

How would i go about combining this string?

=IF(B43=Medicaid_Medical!$A$2,COUNTA(Medicaid_Medi cal!$A$4:$A$50)/COUNTA(Medicaid_Medical!$A$4:$A$50),0)

I need all of the A's to change according to the column they are
representing. I have A - Z in a column that I would like to reference so
that when I drag down the letters change accordingly. I know the row #'s will
change without the $ signs when I drag down, but I need the column letters to
change as I go down...

When I try:

=IF(B43=Medicaid_Medical!"&I43&"$2,COUNTA(Medicaid _Medical!"&I43&"$4:"&I43&"$50)/COUNTA(Medicaid_Medical!"&I43&"$4:"&I43&"$50),0)

Replacing the A with the cell that has A in it, it does not work... Is there
another way to do this? Transpose, somehow?

After a little bit of playing around, I finally got it working....! Here is
what I did:

=IF(B44=INDIRECT("Medicaid_Medical!"&I44&"$2"),COU NTA(OFFSET(INDIRECT("Medicaid_Medical!"&I44&"$2"), 2,0,47,1))/COUNTA(States_Medical),0)

The 47 and 1 were reversed, and the "States_Medical" will always be the
denominator for the reference.

Thanks man, that was of much help!

"bj" wrote:

The ,0,0,1,47 makes the offset function use the range based on the referenced
cell and 46 cells to the right

the denominator factor in your equation
COUNTA(Medicaid_Medical!$A$4:$A$50)
will be the same as the numerator
COUNTA(Medicaid_Medical!$A$4:$A$50)

I missread your original request and screwed up my response also.

I now assume that the sheet in the numerator or the denominator is
incorrectly referenced.

=IF(B43=indirect("Medicaid_Medical!"&I43&"$2"),COU NTA(offset(indirect("Sheet1!"&I43&"$4"),0,0,1,47))/COUNTA(offset(indirect("Sheet2!"&I43&"$4"),0,0,1,4 7)),0)

"IntricateFool" wrote:

I am trying to get a percent of the number of fields being referenced from
another worksheet, so counta is actually what I am trying to use. (
counta(columnC)/counta(totalplans) to get a percent of fields complete) I am
confused by how the ,0,0,1,47 came into the function? I really appreciate
your help with this

"bj" wrote:

Check out the indirect and offset functions in help
(I assume you meant to use count if not counta in the denominater)
if not please change as needed.

=IF(B43=indirect("Medicaid_Medical!"&I43&"$2",COUN TA(offset(indirect("Medicaid_Medical!"&I43&"$4"),0 ,0,1,47)/COUNTIF(Medicaid_Medical!"&I43&"$4:"&I43&"$50),off set(indirect("Medicaid_Medical!"&I43&"$4"),0,0,1,4 7)

"IntricateFool" wrote:

How would i go about combining this string?

=IF(B43=Medicaid_Medical!$A$2,COUNTA(Medicaid_Medi cal!$A$4:$A$50)/COUNTA(Medicaid_Medical!$A$4:$A$50),0)

I need all of the A's to change according to the column they are
representing. I have A - Z in a column that I would like to reference so
that when I drag down the letters change accordingly. I know the row #'s will
change without the $ signs when I drag down, but I need the column letters to
change as I go down...

When I try:

=IF(B43=Medicaid_Medical!"&I43&"$2,COUNTA(Medicaid _Medical!"&I43&"$4:"&I43&"$50)/COUNTA(Medicaid_Medical!"&I43&"$4:"&I43&"$50),0)

Replacing the A with the cell that has A in it, it does not work... Is there
another way to do this? Transpose, somehow?