[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

RE: Excel, again :)



PureBytes Links

Trading Reference Links

> But I want to read the "values" from the other cells, not strings.
> Imagine the other cells contain 93 and 94.

You can use either INDIRECT() or OFFSET() to do that.  Use 
INDIRECT() if you want to construct the cell reference as a 
string.  INDIRECT() looks at the *value* of the cell pointed to 
by the string argument.  Let's say A1 contains 93 and A2 contains 
94:   

 ...LN(data!C3:INDIRECT("C" & A1)/data!C3:INDIRECT("C" & A2) ...

Or you could specify the whole range in the INDIRECT() call, such 
as INDIRECT("C3:C"&A1).  You could have the string "C" in another 
cell and use that to build an INDIRECT() that references other 
columns.

If you don't want to fool around with building the cell reference 
string, you can use the OFFSET() function. You'd have to use a 
smaller offset than 93 or 94, since offsetting 94 rows from A1 
puts you in A95, but hopefully you get the idea.  So e.g. if A1 
contains 3 and A2 contains 4, you can use:  

 ...LN(data!C3:OFFSET(C90,A1,0)/data!(C2:OFFSET(C90,A2,0) ...

Gary