Concatenate in a string
 

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?

Concatenate in a string
 

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?

Concatenate in a string
 

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?

Concatenate in a string
 

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?

Concatenate in a string
 

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?