How do I create a cell reference from a value within a cell?
 

I need to use a value in a cell to create a cell reference. What I'd like to
do is almost accomplished by the address function, but address returns it as
text rather than a cell reference.

Example:
I have numerous sample records in a spreadsheet that summarizes sampling
results. The number of records varies will vary from workbook to workbook,
and I'm trying to automate through use of a template without getting into
visual basic stuff (which I know very little about).

So,I use the count function in Cell A3 to count the number records on the
sheet. The result comes back a whole number...say "20" in this case. Then,
I want to refernce a range of cells that includes cell C4:C24 (i.e. the
vertical range based on the 20 records)in order to count all the blank cells
in the range.

I looked through all the available functions, and couldn't find anything
that I thought would help (except address). Anybody have suggestions?
Thanks in advance!

How do I create a cell reference from a value within a cell?
 

How about the offset function, as in =COUNTIF(OFFSET($C$4,0,0,20,1),"")
The 20,1 at the end indicates that the result should include 20 rows, 1
column. Since you've calculated the number of rows in A3,
=countif(offset($C$4,0,0,A3,1),"").

"Fishboy" wrote:

I need to use a value in a cell to create a cell reference. What I'd like to
do is almost accomplished by the address function, but address returns it as
text rather than a cell reference.

Example:
I have numerous sample records in a spreadsheet that summarizes sampling
results. The number of records varies will vary from workbook to workbook,
and I'm trying to automate through use of a template without getting into
visual basic stuff (which I know very little about).

So,I use the count function in Cell A3 to count the number records on the
sheet. The result comes back a whole number...say "20" in this case. Then,
I want to refernce a range of cells that includes cell C4:C24 (i.e. the
vertical range based on the 20 records)in order to count all the blank cells
in the range.

I looked through all the available functions, and couldn't find anything
that I thought would help (except address). Anybody have suggestions?
Thanks in advance!

How do I create a cell reference from a value within a cell?
 

Hi

P.e. you have "Sheet1" in A1 and "C2" in B1

=INDIRECT("'" & A1 & "'!" & B1)
returns a value from cell Sheet1!C2


Arvi Laanemets


"Fishboy" wrote in message
...
I need to use a value in a cell to create a cell reference. What I'd like
to
do is almost accomplished by the address function, but address returns it
as
text rather than a cell reference.

Example:
I have numerous sample records in a spreadsheet that summarizes sampling
results. The number of records varies will vary from workbook to
workbook,
and I'm trying to automate through use of a template without getting into
visual basic stuff (which I know very little about).

So,I use the count function in Cell A3 to count the number records on the
sheet. The result comes back a whole number...say "20" in this case.
Then,
I want to refernce a range of cells that includes cell C4:C24 (i.e. the
vertical range based on the 20 records)in order to count all the blank
cells
in the range.

I looked through all the available functions, and couldn't find anything
that I thought would help (except address). Anybody have suggestions?
Thanks in advance!

How do I create a cell reference from a value within a cell?
 

Awesome! Your rock, man! That did the job. Thanks for your help!

"bpeltzer" wrote:

How about the offset function, as in =COUNTIF(OFFSET($C$4,0,0,20,1),"")
The 20,1 at the end indicates that the result should include 20 rows, 1
column. Since you've calculated the number of rows in A3,
=countif(offset($C$4,0,0,A3,1),"").

"Fishboy" wrote:

I need to use a value in a cell to create a cell reference. What I'd like to
do is almost accomplished by the address function, but address returns it as
text rather than a cell reference.

Example:
I have numerous sample records in a spreadsheet that summarizes sampling
results. The number of records varies will vary from workbook to workbook,
and I'm trying to automate through use of a template without getting into
visual basic stuff (which I know very little about).

So,I use the count function in Cell A3 to count the number records on the
sheet. The result comes back a whole number...say "20" in this case. Then,
I want to refernce a range of cells that includes cell C4:C24 (i.e. the
vertical range based on the 20 records)in order to count all the blank cells
in the range.

I looked through all the available functions, and couldn't find anything
that I thought would help (except address). Anybody have suggestions?
Thanks in advance!