a sum-problem in Excel2000...

[patej]

Hi,
I'm really a newbie with database etc. things and now I'm trying to create a kind of a database or statistics about the fish my friends and I have caught. My idea is that I have the fish on rows and then I have columns for years, months, species, weight, lures etc..

My problem is that I want to get the sums from each column onto the first row of the corresponding column. I've got this far, but when I add another fish (=row) I would like Excel to increase the area from where it calculates the sum automatically to the new row. This is because my friends are adding the info there as well, and they know even less about Excel than I..

Here are screen shots of an example statistics I created: http://koti.mbnet.fi/patej/excel_prob.jpg The first pic shows the way I've done it and which works, but which doesn't increase the area (blue rectangle) automatically when a new row is added. I have columns up to BT, so it's quite time consuming to do it by hand every time..

The second pic shows a thing I tried, but didn't work.. I tried to select the area for the sum in B2 to be the whole column B, but I couldn't exclude the cells B1, B2, B5 and B6 from the selection (tried ctrl-key), so it resulted in error message.. Any ideas on how I could solve the problem? I really don't know about all the features in Excel2000..

Wow, this became quite a mess to try to explain my problem, but I hope someone understood..

Any help appreciated,
--patrik

[merlin_2]

http://www.kayodeok.btinternet.co.uk/favorites/kbofficeexcel.htm

[patej]

http://www.kayodeok.btinternet.co.uk/favorites/kbofficeexcel.htm

Actually I got this link already from other Excel-threads I looked through before posting, but it seems that my problem isn't a normal one, because i can't find anything to help from there.. But thanks anyway for replying!

Meanwhile I'm still looking for an answer.. Is there a way to select i.e. the whole column B, but exclude one or two cells from the selection?

--patrik

[dl65]

 patej.....  Just had a look at what your trying to do......

It seems to me that you should rethink the way you want your spread sheet to be layed out .....

If I understand you .......

    col A  2002               col B 2003               col C 2004
jan
feb
mar
aprl
may        

then on the same sheet create a imput area for each month and then write a formula to transfer the totals to the appropriate spot up top .......

PM me if you need help ........with all your labeling info and I'll see what I can do for you .

dl65  
                 

[patej]

Hi again,
I haven't had much time to play with these things, but last night I changed the whole table into a more simple one.. Now I have the sum in the bottom and it works, but how can I get the source for the sum to change to include a new row I create? Now for example I have a sum of (B3:B161) in cell B163. When I add a new row (162) and info to B162, it doesn't automatically add the new row to be included in the sum. So now I have to manually set it to (B3:B162). I have columns up to BY, so it takes a lot of time to change the sources manually.. Any ideas on how to get it automated?

I hope someone understood my problem.. Any help appreciated.

Regards,
--patrik

[Michael]

You may email your worksheet to me at [email protected] and explain (in detail) what you need to do and what you normally do e.g. add a column/row after/before certain column/row, key-in a number into certain cell, etc.