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

 

[Ashish Mathur]

Hi,

To answer your first question, go to sheet2 and just link to the cell in
sheet1. They copy and paste down and across. Any change made the existing
data (edits/new rows/new columns) will appear in sheet2 .

Have I misunderstood your question

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Roopesh1978]

Hi Ashish,

I am sheet2 and have referenced sheet1,
for example, i have typed =sheet1!A1 in cell A1, then =sheet1!A2 in cell A2,
etc,
is there a way of referencing the whole of sheet1 into sheet2 in one go
without having to reference each individual cell?

also once i have done the referencing in sheet2, i have setup an autofilter
list for each column, i then go and filter say column D to show only certain
rows, when i update sheet1 with a new row, sheet2 doesnot update
automatically even though the data entered in sheet1 is similar to the
filtered data in sheet2, i have to click on ALL from the dropdown list for
column D in sheet2 to show all the data again and then filter it again. Is
there anyway round this so that the filtered list in sheet2 will
automatically update accordingly to the data entered in sheet1?

Kind Regards,

Roopesh

 

[Ashish Mathur]

Hi,

After linking cell A1 of sheet 2 to cell A1 of sheet1, simply copy and paste
down and right - you do not need to link every cell. Also, if you have 545
rows in sheet1, then in sheet 2 copy down till row 1000 so that rows added
beyong 545 also get reflected in sheet2

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Roopesh1978]

thanks Ashish i will give that a try later


Roopesh