Prevent range reference

  • Thread starter Thread starter Basta1980
  • Start date Start date
B

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
 
Could use an INDIRECT reference to make it completely static...

=SUMPRODUCT((INDIRECT("'Source Data'!H1:H5000")=A96)*
(INDIRECT("'Source Data'!G1:G5000")=$B$94))
 
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
 
Patrick & Luke,

Thnx

Patrick Molloy said:
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
Click to expand...
 
Back
Top