Formula in a table : use structures reference vs pointing to a cell

Copper Contributor

hello, 

I'm struggling to find my solution :)

I'm using a table, and making calculation in it : 

A / B / C / D / E / F / G / H / 1 / 2 / 3 / 4...

 

I would like to make a formula doing : ($ means keep the same column as in $A1)

typing in column C = $A * $B * 1

and when copying or draging the formula from C to H get automatically:

in column E = $A * $B * 2

in column F = $A * $B * 3

in column G = $A * $B * 4

...

 

If I  type in column C : 

=[@A]*[@B]*[@1] then if I copy the cell C in the cell D I get : =[@A]*[@B]*[@1] so it keeps A and B as I want, but takes 1 instead of 2

 

If I  type in column C : 

=[@A]*[@B]*[@1] then if I drag the cell C in the cell D I get : =[@B]*[@C]*[@2] so it takes 2 into account but don't keep A and B

 

I hope I'm clear enough :)

 

Any idea please ? thanks for any help.

 

 

3 Replies

You may use

=[@[A]:[A]]*[@[B]:[B]]*[@1]

**bleep** soo easy :) 

I've been looking for this solution for a long time ! great thanks Sergei !!

Each of us constantly finds something new in Excel, it's infinite