Prevent range reference
 

Hi,

I'm using below code in regular excel (not vba). When I run my macro the
cell reference jumps automatically to the adjacent column (H becomes I and G
becomes H). Is there any way to prevent this?! Either by code or by options?!

=SUMPRODUCT(('Source Data'!$H$1:$H$5000=A96)*('Source
Data'!$G$1:$G$5000=$B$94))

Regards,

Basta

Prevent range reference
 

Could use an INDIRECT reference to make it completely static...

=SUMPRODUCT((INDIRECT("'Source Data'!H1:H5000")=A96)*
(INDIRECT("'Source Data'!G1:G5000")=$B$94))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Basta1980" wrote:

Hi,

I'm using below code in regular excel (not vba). When I run my macro the
cell reference jumps automatically to the adjacent column (H becomes I and G
becomes H). Is there any way to prevent this?! Either by code or by options?!

=SUMPRODUCT(('Source Data'!$H$1:$H$5000=A96)*('Source
Data'!$G$1:$G$5000=$B$94))

Regards,

Basta

Prevent range reference
 

so your code inserts a colum?
Use OFFSET

so something like

=SUMPRODUCT( (OFFSET('Source data'!A1:A5000,0,7)=A96) ....
the idea is set the ref to a column at teh same row, then offset it by 7
columns
it will always be 7 columns, irrespective of what you insert to that sheet,
so long as you don't insert a column 1




"Basta1980" wrote:

Hi,

I'm using below code in regular excel (not vba). When I run my macro the
cell reference jumps automatically to the adjacent column (H becomes I and G
becomes H). Is there any way to prevent this?! Either by code or by options?!

=SUMPRODUCT(('Source Data'!$H$1:$H$5000=A96)*('Source
Data'!$G$1:$G$5000=$B$94))

Regards,

Basta

Prevent range reference
 

Patrick & Luke,

Thnx

"Patrick Molloy" wrote:

so your code inserts a colum?
Use OFFSET

so something like

=SUMPRODUCT( (OFFSET('Source data'!A1:A5000,0,7)=A96) ....
the idea is set the ref to a column at teh same row, then offset it by 7
columns
it will always be 7 columns, irrespective of what you insert to that sheet,
so long as you don't insert a column 1




"Basta1980" wrote:

Hi,

I'm using below code in regular excel (not vba). When I run my macro the
cell reference jumps automatically to the adjacent column (H becomes I and G
becomes H). Is there any way to prevent this?! Either by code or by options?!

=SUMPRODUCT(('Source Data'!$H$1:$H$5000=A96)*('Source
Data'!$G$1:$G$5000=$B$94))

Regards,

Basta