Hi There,

Starting Cell = A20 (GBSUP)
Number of items = Variable but Starting at $B$21 ...B65000
Number of Periods = Variable C19 ...20 ...21 ....


DEC1403 DEC1204 DEC1105 .....
GBSUP
WHITE B.I.B1 1,9 1,8 2,1
WHITE B.I.B2 4,6 4 4,6
WHITE B.I.B3 4,1 4,4 5,3
WHITE B.I.B4 0 0 0
WHITE B.I.B5 0 0 0
.....

I would like to write via code a formula that multiplies my %shares
(the figures above) with my totals which I calculate as follows:
=INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATC H(C$19;INDEX(SOURCE;1;0);0))

In this example I would copy down and 3 cells to the right in F21:
=OFFSET($A$20;ROW()-20;COLUMN()-COUNTA($19:$19)-1;1;1)*INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0 );MATCH(C$19;INDEX(SOURCE;1;0);0))

The problem is that my starting point to plug this formula in varies:
It depends on the number of periods chosen (in row 19)
(and the number of items down).

Hope you understand,
Could you help me out please?

Best Regards Sige

Sige,

How would your formula vary with different values? Post two example cases, and the cell values on
which you want the formula to depend.

HTH,
Bernie
MS Excel MVP


"Sige" wrote in message
ups.com...
Hi There,

Starting Cell = A20 (GBSUP)
Number of items = Variable but Starting at $B$21 ...B65000
Number of Periods = Variable C19 ...20 ...21 ....


DEC1403 DEC1204 DEC1105 .....
GBSUP
WHITE B.I.B1 1,9 1,8 2,1
WHITE B.I.B2 4,6 4 4,6
WHITE B.I.B3 4,1 4,4 5,3
WHITE B.I.B4 0 0 0
WHITE B.I.B5 0 0 0
.....

I would like to write via code a formula that multiplies my %shares
(the figures above) with my totals which I calculate as follows:
=INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATC H(C$19;INDEX(SOURCE;1;0);0))

In this example I would copy down and 3 cells to the right in F21:
=OFFSET($A$20;ROW()-20;COLUMN()-COUNTA($19:$19)-1;1;1)*INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0 );MATCH(C$19;INDEX(SOURCE;1;0);0))

The problem is that my starting point to plug this formula in varies:
It depends on the number of periods chosen (in row 19)
(and the number of items down).

Hope you understand,
Could you help me out please?

Best Regards Sige

Hi Bernie,

My formula does not change with different values ...

It is just that:
If I have 3 periods (Column C, D, E -row19), as in the above example.
Then I should multiply the corresponding product
on row 21 (and down...) with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0))
respectively.

To get a result in F21, G21, H21

IF I have 4 periods: C-D-E-F
Then I should multiply the corresponding product on row 21 with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (F$19;INDEX(SOURCE;1;0);0))
To get a result in G21, H21, I21,J21

and this down for the number of items in column B.

Does this make more sense??
If not, I'll try again ;o)

Sige

If I have 3 periods (Column C, D, E -row19), as in the above example.
Then I should multiply the corresponding product
on row 21 (and down...) with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))

INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))

INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))

respectively.


To get a result in F21, G21, H21
==
F21= C21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))

G21= D21*
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))

H21= E21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))

F22 =C22
**INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MAT CH(C$19;INDEX(SOURCE;1;0);0))
.... for the nr of items in column B
<==

HTH

Sige,

Try the macro below.

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim myRow As Long
Dim myCol As Integer
Dim myOff As Integer
myRow = Range("B65536").End(xlUp).Row
myCol = Range("IV19").End(xlToLeft).Column
Range("C21", Cells(myRow, myCol)).FormulaR1C1 = _
"=INDEX(Source;MATCH(R20C1;INDEX(Source;;1);0) " & _
";MATCH(R19C;INDEX(Source;1;0);0))"
myOff = myCol - 2
Range(Cells(21, myCol + 1), Cells(myRow, (myCol - 2) * 2 + 2)).FormulaR1C1 = _
"=RC[-" & myOff & "] *INDEX(Source;MATCH(R20C1;INDEX(Source;;1);0)" & _
";MATCH(R19C[-" & myOff & "];INDEX(Source;1;0);0))"
End Sub




"Sige" wrote in message
oups.com...
If I have 3 periods (Column C, D, E -row19), as in the above example.
Then I should multiply the corresponding product
on row 21 (and down...) with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))

INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))

INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))

respectively.


To get a result in F21, G21, H21
==
F21= C21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))

G21= D21*
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))

H21= E21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))

F22 =C22
**INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MAT CH(C$19;INDEX(SOURCE;1;0);0))
.... for the nr of items in column B
<==

HTH

Hi Bernie,

It is bugging on the first Range.... :o(

XL97?
Sige