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

F

Fishboy

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!
 
B

bpeltzer

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),"").
 
A

Arvi Laanemets

Hi

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

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


Arvi Laanemets
 
Top