The following is what I need help with:

(1) I have a main sheet where I am entering my data.

I have defined a dynamic range for this data so that if I enter a new row or
column in the sheet then this data will be included in the dynamic range.

I want to know what I need to do to copy the data from the main sheet into a
second sheet so that when I enter a new row/column or amend the existing data
in the first sheet then the second sheet will automatically update.

I presume it is something to do with referencing the dynamic range from the
first sheet into the second sheet.

Once this part has been implemented then I will lock the second sheet so
that it cannot be amended and only the first sheet can be amended.

(2) I have been using the count functions to total up data from the first
sheet into a summary sheet, for example, the total number of people from a
specific area.

I have also used SUMPRODUCT to total up dates in specific range, for
example, the total number of people born between 1.1.1970 and 31.12.1970.

I want to implement a formula in my summary sheet which will take my dynamic
range from the first sheet and search it by 2 or more conditions, for
example, I want to total up the number of people from a specific area who
were born between 1.1.1970 and 31.12.1970.

Any help would be appreciated.

Kind Regards

Roopesh

Part 1... This requires a simple macro. There is no way to lock down your
sheet 2 from updating other than making a static copy of your first sheet.

Part 2... I appreciate that you went to the effort of creating the dynamic
named range but you probably do not want to use it here. The offset function
is volatile making your sumproducts volatile and that will add a lot of
calculation overhead. You are better off to hard code a range that is
equvalent to the entire column (sumproduct will not accept entire columns
excpet 2007). From your description there is no reason that I can see that
you will have any difficulty implimenting a multi condition test with
sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Roopesh1978" wrote:

The following is what I need help with:

(1) I have a main sheet where I am entering my data.

I have defined a dynamic range for this data so that if I enter a new row or
column in the sheet then this data will be included in the dynamic range.

I want to know what I need to do to copy the data from the main sheet into a
second sheet so that when I enter a new row/column or amend the existing data
in the first sheet then the second sheet will automatically update.

I presume it is something to do with referencing the dynamic range from the
first sheet into the second sheet.

Once this part has been implemented then I will lock the second sheet so
that it cannot be amended and only the first sheet can be amended.

(2) I have been using the count functions to total up data from the first
sheet into a summary sheet, for example, the total number of people from a
specific area.

I have also used SUMPRODUCT to total up dates in specific range, for
example, the total number of people born between 1.1.1970 and 31.12.1970.

I want to implement a formula in my summary sheet which will take my dynamic
range from the first sheet and search it by 2 or more conditions, for
example, I want to total up the number of people from a specific area who
were born between 1.1.1970 and 31.12.1970.

Any help would be appreciated.

Kind Regards

Roopesh

hi Jim,

Thanks for the link in relation to Part 2

With reference to Part 1, i have a simple knowledge of implementing macros,
what code would i include in my macro?

Kind Regards,

Roopesh

"Jim Thomlinson" wrote:

Part 1... This requires a simple macro. There is no way to lock down your
sheet 2 from updating other than making a static copy of your first sheet.

Part 2... I appreciate that you went to the effort of creating the dynamic
named range but you probably do not want to use it here. The offset function
is volatile making your sumproducts volatile and that will add a lot of
calculation overhead. You are better off to hard code a range that is
equvalent to the entire column (sumproduct will not accept entire columns
excpet 2007). From your description there is no reason that I can see that
you will have any difficulty implimenting a multi condition test with
sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Roopesh1978" wrote:

The following is what I need help with:

(1) I have a main sheet where I am entering my data.

I have defined a dynamic range for this data so that if I enter a new row or
column in the sheet then this data will be included in the dynamic range.

I want to know what I need to do to copy the data from the main sheet into a
second sheet so that when I enter a new row/column or amend the existing data
in the first sheet then the second sheet will automatically update.

I presume it is something to do with referencing the dynamic range from the
first sheet into the second sheet.

Once this part has been implemented then I will lock the second sheet so
that it cannot be amended and only the first sheet can be amended.

(2) I have been using the count functions to total up data from the first
sheet into a summary sheet, for example, the total number of people from a
specific area.

I have also used SUMPRODUCT to total up dates in specific range, for
example, the total number of people born between 1.1.1970 and 31.12.1970.

I want to implement a formula in my summary sheet which will take my dynamic
range from the first sheet and search it by 2 or more conditions, for
example, I want to total up the number of people from a specific area who
were born between 1.1.1970 and 31.12.1970.

Any help would be appreciated.

Kind Regards

Roopesh