Problem in refering to a specified cell in Excel 1

[cashmire]

Hi!
A part of my formula is as follows:
(MMULT(MMULT(TRANSPOSE($C$25:$C$29); VarCovar(INDIRECT("returns!b4:f" & H30)));$C$25:$C$29))*100))

The problem comes in this part:
VarCovar(INDIRECT("returns!b4:f" & H30)) where VarCovar is a VBA function that creates a variance-covariance matrix.

Instead of refering to the cell B4 I would like to refer to a cell in column B and specify the row number in a different cell (for example G30, that would contain a number). Is this possible?

I've been beating my head against the wall for a couple of days now and would appreciate any help on this problem.

Thank You
-Cashmire

 

[BobStubbs]

Can you combine the CONCATENATE and INDIRECT functions to achieve this? I set up a simple example in a worksheet to test this:

Cell A1 contains 123 (the test value I want to return)
A6 contains the text A
A7 contains the number 1
A9 contains the formula =CONCATENATE(A6,A7)
A12 contains the formula =INDIRECT(A9)

The CONCATENATE formula joins the contents of cells A6 and A7 together, to create the string A1.
This is passed to the INDIRECT formula in cell A9.
The result is that cell A12 displays 123, read from cell A1.

I hope this idea will help with your problem.

Bob Stubbs

 

[cashmire]

Thank you very much Bob!
I'll try your suggestion!
Cashmire

 

[xlbo]

Certainly can:

VarCovar(INDIRECT("returns!b" & G30 & ":f" & H30))

INDIRECT requires a string to reference so all you need to do is add a variable into the string as above.

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[cashmire]

Thanks super much Bob and Geoff!

Now my formula works like a charm.
I'll have use for the CONCATENATE function in another thing I'm working on...

Best Regards
Cashmire

 

[xlbo]

No need to use CONCATENATE.........ever.

It can be replaced by an &

so:

Concatenate(A1,B1)

is identical to

=A1 & B1

and I know which I'd rather type !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ChrisBurch]

Gave you a star for that Geoff, I'll never use concatenate again.

Chris

IT would be the perfect job......if it didn't have users!!!