Spreadsheet help needed

[veremchuka]

Thank you all for your replies.

I did see that the date updated itself (it's in the header) today when I added some new things - I see this works the same as in a word document whether MSW or OO.

The totals at the top of the category is interesting, I never would have considered that because I always saw totals on reports at the end of vs the beginning of the items being totaled. I like that because it would keep the totals for that category on page 1 but then the grand totals could also be moved to the very 1st line. It's the last category that I'll be adding the most to on an ongoing basis and will grow to 2, 3, 10 pages.

This exercise was like writing a program just using a different language. I could have done this in COBOL in a heart beat even though I retired almost 6 years ago - hard to forget 35 years of that stuff. I tweaked the SS today and moved some headings and just prettied it up.

Here's a question I just realized I'll face. when I have added enough rows that the SS goes to a 2nd or 3rd page is there a way to have headings print on the new page? I wonder if it would be in the header since that should print on each new page?

 

[Texas Proud]

If you actually have it in the header part.... it will print on all pages..

If you want to print rows on all pages, there is an option in the print menu... something like 'rows to repeat at top'.... even 'columns to repeat at left'...

 

[BuysToys]

SS work IS very much like programming. It's a slightly different formulaic language, but once you catch the hang of it, you'll be able to easily convert your COBOL thinking into SS functions.

If you want to get all fancy, you could use =sumif() functions or =if() functions. Those are very helpful when you need to add a lot of data and don't want to update all your formulas.

 

[veremchuka]

I have another question.

I liked the idea of having the total for a category at the beginning of the category as the 1st line within that category, I think this is a good idea. It works.

Now I tried to put the grand total as the 1st line in the SS, preceding all the category totals and the formula will not work. I moved the grand total line back to the last line in the SS and the formula worked fine. Is it not possible to have a grand total prior to all the lines that are the category totals? It does summarize each category total correctly and they are before the detail lines within the category. The category total formulas are =SUM(B11:B22) as an example whereas the grand total formula is =SUM(B8+B13+B40) as an example. Maybe using a range of lines allows the category total to calculate correctly but using specific line numbers with the plus sign doesn't cuz these lines are after the grand total (when the grand total is at the top)?

I'd like to have the grand total as the 1st line but I can't get the formula to work but using the same formula at the bottom does work. Why?

Thanks.

 

[BuysToys]

=SUM() is for a range of cells. Try using =B8+B13+B40 and see if that works for you.

 

[utrecht]

Insert the following into a cell:

=now()

this will put the correct date into that cell automatically even if you dont update the spreadsheet.

 

[rbmrtn]

I'd like to have the grand total as the 1st line but I can't get the formula to work but using the same formula at the bottom does work. Why?

Thanks.

It should work. Try without the function, =B8+B13+B40, see what that does.

How does it not work ? Does it throw off an error ?

 

[Lsbcal]

You can do what you tried this way: =SUM(B1, B3, B40)
or even this: = SUM(B1, B3, B38:B40)

To answer these kinds of questions, click on the "?" in the upper right to get Excel Help.

 

[Texas Proud]

It does not seem like you are doing it correctly if you are still referencing individual cells down a column...


But, an option is to use the =subtotal( option...

You can have the subtotals in 8, 13, 40 and a grand subtotal at the top...

A subtotal will ignore any other subtotals in a range...


As an example... if you had =sum in 8, 13 and 40 and then did an =sum for the whole range, you would double your total as it would add all the items plus your =sums in your grand total.

If you used =subtotal, your range would be the whole range where you wanted a grand total and all the subtotals wherever you had them would not be included in your total.


As for your question... there should be NO reason you can not have a grand total at the top even the way you are doing it...

 

[scrinch]

I'd like to have the grand total as the 1st line but I can't get the formula to work but using the same formula at the bottom does work. Why?

Not sure. The cell location of a formula usually has no impact on its validity unless it gets put in a place where it creates a circular reference.

Maybe using a range of lines allows the category total to calculate correctly but using specific line numbers with the plus sign doesn't cuz these lines are after the grand total (when the grand total is at the top)?

Again, location of a formula, as long as it doesn't have a circular calculation, is immaterial. You can drag that formula anywhere on the sheet that you want with no effect on its value. The spreadsheet is not like a program that is executed top-down and where the order of the instructions matters.

When you use the "sum()" function with + in the argument, you are using valid but probably unintentional syntax. Using the "sum()" function, the cells that you are summing must be separated by commas (or the range defined like B8:B40). By using the "+" symbol and no commas, you are summing one number, and the value of that single number is B8+B13+B40. To sum those three cells, you should use either "=sum(B8, B13, B40)" or "=B8+B13+B40".

 

[scrinch]

The attached file shows how subtotals can be placed at the top of a column, and the Grand Total placed above that. Try dragging the grand total to some other location in the spreadsheet. No effect.

 

[veremchuka]

=SUM() is for a range of cells. Try using =B8+B13+B40 and see if that works for you.

This worked! Funny how this simple difference matters, with zero experience this simple difference was just beyond me figuring this out. Thanks!

I use Open Office and while there is a tutorial/help it is often difficult to find what I want to know because I don't know how to search for it using the terms it knows.

Anyway I have the GT line on the top now. This allows me to add rows to the most active category, the last one, and see all my sub totals and the GT on the 1st page of the SS. It won't be long before that last category has enough rows in it to spill onto the 2nd page. This setup allows me to see all the totals without paging through the SS.

Thanks to all of you for your helpful hints.

 

[Meadbh]

For big soreadsheets, if often use "freeze" to ensure I can see my column and row headings at all times. For example, let's say I had totals or other information in Rows 1-3 and I had entry number in Column A. I would click on Cell B4 (just outside those areas) and then "freeze". I can the scroll up, down or sideways anywhere in my spreadsheet while maintaining reference to the headings. Try it!