A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help With My Excel Database



 
 
Thread Tools Display Modes
  #1  
Old November 16th, 2009, 05:38 PM posted to microsoft.public.excel.misc
Roopesh1978
external usenet poster
 
Posts: 5
Default Help With My Excel Database

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
  #2  
Old November 17th, 2009, 06:52 AM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 2,641
Default Help With My Excel Database

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

  #3  
Old November 18th, 2009, 12:34 PM posted to microsoft.public.excel.misc
Roopesh1978
external usenet poster
 
Posts: 5
Default Help With My Excel Database

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.