PDA

View Full Version : Data Range Issue



Mattster2020
01-22-2009, 09:14 AM
Afternoon All,

I am using the below formula to lookup data in another tab called 'Data'.

=SUM(IF(Data!J1:J100=1,1,0))

On the Tab 'Data' I am using some VB code to delete rows that have a value of '0' in column 'J'.

The problem I have is that if rows are deleted the cell range in the above code keeps changing, for instance:

=SUM(IF(Data!J1:J56=1,1,0))

Is there any way in which I can always keep the same cell reference even if rows in the Tab 'Data' are deleted?

Regards,

Mattster

mdmackillop
01-22-2009, 09:32 AM
Use a range name to define the area with the Offset function
=OFFSET(Data!$J$1,0,0,100,1)

You'll need to enter your formula as an array formula (Ctrl + Shift +Enter)