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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|