Help with my Excel Database

[Roopesh1978]

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
asecond 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

 

[jason]

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
asecond 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

hey roopesh

use this ideology:

macro 1:
dim i
i=1

do until worksheets("Data").cells(i,1)=""

worksheets("DataDump").cells(i,1)=worksheets("Data").cells(i,1)

i=i+1
loop

worksheets("DataDump").cells(i,1).formula= "=sum(A1:A" & i ")"



this will move your data, and plant a formula at the base of your
datadump sheet, with some formulas (you can make sum whatever you
want).

this should help.

 

[marcus]

Roopesh

Regulars will see your question where ever you put it, so if you could
restrict your posts to the singular that would be fantastic.

The answer to the second part of your question is below.

Change the region to reference a cell.

Dates A1 = 31-12-1970 and B1 = 1-1-1971

=SUMPRODUCT((A2:$A$18="Sydney")*($B$2:$B$18>A1)*($B$2:$B$18<B1))

Marcus

 

[Roopesh1978]

Hi Jason

Thanks for the Macro

How would I go about implenting this, would i type it into the Visual Basic
editor and save it??


Roopesh

 

[Roopesh1978]

Hi Marcus

Thanks for the formula, it worked a treat


Roopesh