Prevent range reference

[Basta1980]

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

 

[Luke M]

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

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

 

[Patrick Molloy]

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]

Patrick & Luke,

Thnx

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