Referencing Issue

[pratiksuhasaria]

Hi

I have two values in 2 different cells say

​

	A	B	Forumla Used	Result	Excepted
1	10	20	=A1*B1	200	200
2	10	20	=ADDRESS(2,1,4)*ADDRESS(2,2,4)	#VALUE!	200
3

<tbody>
</tbody>

As u all can see that when using second formula there is some error which need to b rectified in order to get expected result

can anyone help me out with it
its really important.

Thanks

 

[FormR]

Hi, why not just use =A2*B2 ?

Assuming you have a good reason not to, here is an equivalent alternative to your attempt.

=INDEX(A:B,2,1)*INDEX(A:B,2,2)

 

[pratiksuhasaria]

Thanks for your help
It works Perfectly

 

[NimishK]

Hi, why not just use =A2*B2 ?


Assuming you have a good reason not to, here is an equivalent alternative to your attempt.


=INDEX(A:B,2,1)*INDEX(A:B,2,2)

The above gives result for Cols A and B of Row 2 only =Index(A:B, Row2, Col1 * Index(A:B, Row2, col2) even if i drag the formula till row 20. Any particular type of syntax that i need to incorporate to change its respective Row

NimishK

 

[FormR]

The above gives result for Cols A and B of Row 2 only =Index(A:B, Row2, Col1 * Index(A:B, Row2, col2) even if i drag the formula till row 20.

Hi, that was the intention - maybe you need to start your own thread with a clear description of what it is you are trying to do.

 

[NimishK]

Hello

Hi, that was the intention - maybe you need to start your own thread with a clear description of what it is you are trying to do.

Though the threader's intention and you answered perfectly Sir. Went slight deeply to check whether formula could be dragged/ copied in the above example and when implemented the same on the worksheet what was seen the Formula could not give correct result when dragged/copied and that's where i post my query. so in other words wanted to know if syntax requires changes if i want to copy/drag the same formula in range of respective rows. Sir, as this was observed therfore my clear cut question related to this thread

BTW i came across the following for the 1st time

=INDEX(A:B,2,1)*INDEX(A:B,2,2)

 

[FormR]

so in other words wanted to know if syntax requires changes if i want to copy/drag the same formula in range of respective rows.

In place of the static number for the row argument of the index() function you could use the rows() function to increment as you copied down. But if you are doing that then it kind of negates the whole point and there are almost certainly simpler ways.

 

[NimishK]

But if you are doing that then it kind of negates the whole point and there are almost certainly simpler ways.

Yes sir, Got it