Automatically select range with known first cell

[drakaz]

Hi,

I'm looking for formula or a method how could calculate a product of range of cells when I have only the first cell, but I need to multiply 260 cells that are in front of it.

I now find a cell using index with double match criteria and use "Cell(info_type, Ref)" to determine the cell where the first value is. I need to take that value and multiply it with with t+1, t+2, etc. to get the final number.

Any ideas?

Regards,
drakaz

[[email protected]]

Please attache Excel sheet with Proper solution.

Regards,
Suhas

[drakaz]

Please attache Excel sheet with Proper solution.

Regards,
Suhas

Extracted few lines from document

[[email protected]]

Hi Friend,
Please find attached sheet as well as below formula for your solution.

Cell AI2=
="[123example.xlsx]Sheet2!"&ADDRESS(MATCH(AF2,Sheet2!$A$1:$A$13,0),MATCH(A2,Sheet2!$A$1:$MEC$1,0))

Regards,
Suhas

[drakaz]

Hi Friend,
Please find attached sheet as well as below formula for your solution.

Cell AI2=
="[123example.xlsx]Sheet2!"&ADDRESS(MATCH(AF2,Sheet2!$A$1:$A$13,0),MATCH(A2,Sheet2!$A$1:$MEC$1,0))

Regards,
Suhas

Sorry but what is that?

I do not want to create a similar name to the value in AH2 (Sheet1), but just to use it as a first value in a product.

Example explaining what BHAR does here: It takes the value from Sheet one that is with the SEDOL code and date, than multiplies it with the value in period t+1 from that date, than with value from t+2, etc.

For the AH2 I have found the first value that has to be taken, which is $3.300 (in Sheet2), then formula (which I am looking for) should multiply this value with t+1 (from Sheet2 again) which is $3.725 (Cell $GNE$2) then with $3.300 (Cell $GNF$2), ..., with last value of $1.95 (Cell $GXU$2), overall making multiplications of 260 cells:
$3.3 * $3.275 * $3.3 * .... * $1.95 = x?