Creating Spreadsheet Calculations
 

Hello,

I know that I can add a series of columns or calculate the value of a row's
cell by
using simple formulas such as =SUM(A1:A50) or =(A1*A3).

I also know that expandable and collapsable categories can be used in Excel.

My question is the following. If I have the following spreadsheet and I
find myself continuing to add new rows of data within a category, I then need
to keep changing my category formula to include the extra row. Can this be
setup to be done automatically ?

A B
1 Category 1 $100
2 lights $60
3 camera $40
4 Category 2

Now I find myself needing to add another field as follows where cell B1 needs
to have its formula changed from =SUM(B2:B3) to =SUM(B2:B4)

A B
1 Category 1 $170
2 lights $60
3 camera $40
4 action $70
5 Category 2

Thanks in advance for any insight...

Hi
it would be easier if you use 3 columns:
1. Category
2. Item
3. Value

and then use SUMIF or a pivot table

--
Regards
Frank Kabel
Frankfurt, Germany
"Glenn" schrieb im Newsbeitrag
...
Hello,

I know that I can add a series of columns or calculate the value of a
row's
cell by
using simple formulas such as =SUM(A1:A50) or =(A1*A3).

I also know that expandable and collapsable categories can be used in
Excel.

My question is the following. If I have the following spreadsheet and I
find myself continuing to add new rows of data within a category, I then
need
to keep changing my category formula to include the extra row. Can this
be
setup to be done automatically ?

A B
1 Category 1 $100
2 lights $60
3 camera $40
4 Category 2

Now I find myself needing to add another field as follows where cell B1
needs
to have its formula changed from =SUM(B2:B3) to =SUM(B2:B4)

A B
1 Category 1 $170
2 lights $60
3 camera $40
4 action $70
5 Category 2

Thanks in advance for any insight...

Another possibility (although it may be impractical for your application) is
to add the new values by inserting rows into the middle of the category,
rather than at the end. This should have the effect of expanding the formula.

Art