Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent List Box Reference from Changing when workbook closes? | Excel Discussion (Misc queries) | |||
Prevent cut and paste over a range | Excel Worksheet Functions | |||
Prevent formula reference from changing | Excel Discussion (Misc queries) | |||
Excel - how to prevent new office version from upgrading word object reference | Excel Programming | |||
prevent row insert in a range of rows | Excel Programming |