Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Old 04-23-2013, 10:18 PM   #21
Moderator
Ronstar's Avatar
 
Join Date: Aug 2007
Location: A little ways southwest of Chicago
Posts: 9,352
Yes the date should update.


In a situation like this, I have a tab for each category with a summary tab that contains cells that sum up a thousand or so rows of each category tab. That way I don't have insert rows or copy the formula to update the sum
__________________

__________________
Ronstar is online now   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 04-24-2013, 02:46 AM   #22
Thinks s/he gets paid by the post
 
Join Date: Apr 2006
Location: North Bay
Posts: 1,026
Quote:
Originally Posted by veremchuka
I setup a header that has the date in it, not one that I manually put in but rather I clicked on the date icon. Now I'd think (assume!) that the date will be automatically updated any time I make a change to the SS with the calendar date. Is that correct? Seems logical but this is uncharted territory.
I assume this is a date within a header for printing, not within the spreadsheet itself? Then yes, it will show the date of the printing. If you want a fixed date within a spreadsheet, type the date in in whatever format you want, and it will be recognized and stored as a date. If you want a cell to show today's date updated whenever you open the spreadsheet, type =today() in the cell.

To avoid having to add cells or rows, put the Total at the top of the column and make the value of that cell the sum of the next 200 cells in the column. Then you have room to add 200 items downward without having to update your formula or add cells. If you have 4 categories of items, just use 4 columns for the different categories, and add up the grand total on the side.
__________________

__________________
scrinch is offline   Reply With Quote
Old 04-24-2013, 08:59 AM   #23
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,708
Not sure if I understand all, but the OP might be looking for a way to insert the date the file was last modified...
__________________
target2019 is offline   Reply With Quote
Old 04-24-2013, 10:11 AM   #24
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: May 2005
Posts: 13,275
Quote:
Originally Posted by RunningBum View Post
Do you have an idea of how many rows you might have? I tend not to create new rows in existing spreadsheets, but instead make enough rows initially for what I'll need. Often I put the total at the top, say in B1, and then if I think I'll have 10 entries, I'll leave room for 15 just to pad it, and in B1 I'll put =SUM(B2:B16). If I only have 4 entries now, it doesn't matter that b6 through b16 are blank, and as they get filled in I don't have to make any formula changes.
Quote:
Originally Posted by TrvlBug View Post
I use a spacer row/column to avoid the 'will it or won't it adjust my totals' question. Rows/columns are inserted above the spacer row/column. Depending on the complexity of the spreadsheet and the need for more detailed summaries, I also like putting the summary/grand total info in a new tab with lookups to the detail spreadsheet.
I have used both of these tricks...

The fist one is good in that it really does not matter how many items you have.... the totals are always at the same location... the top...

The second is for when the totals need to be at the bottom... a blank row after all the entries just sits.... you insert a row at the bottom of your numbers and this blank row moves down.... your formulas are adjusted...
__________________
Texas Proud is online now   Reply With Quote
Old 04-24-2013, 10:12 AM   #25
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: May 2005
Posts: 13,275
Quote:
Originally Posted by dfarr View Post
Just put the formula =sum(b:b) in any column other than column b. It will give you the total of column b no matter how many lines you add. Just make sure you don't put that formula in column b or you'll get a circular reference error.
For your different sections put your values in different columns. (c,d,e,f.........) Keep one column reserved for your formulas that will be written like =sum(c:c), =sum(d:d), =sum(e:e).........
Or you can create a separate worksheet with a tab named for each of your sections and have one main worksheet that contains your totals.

This is a new one for me.... I might be able to use it sometime.... thanks..
__________________
Texas Proud is online now   Reply With Quote
Old 04-24-2013, 02:50 PM   #26
Thinks s/he gets paid by the post
veremchuka's Avatar
 
Join Date: Oct 2010
Location: irradiated - too close to the nuclear furnace
Posts: 1,294
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?
__________________
veremchuka is offline   Reply With Quote
Old 04-24-2013, 04:15 PM   #27
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: May 2005
Posts: 13,275
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'...
__________________
Texas Proud is online now   Reply With Quote
Old 04-24-2013, 05:28 PM   #28
Recycles dryer sheets
 
Join Date: Dec 2012
Posts: 78
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.
__________________
BuysToys is offline   Reply With Quote
Old 04-29-2013, 11:30 PM   #29
Thinks s/he gets paid by the post
veremchuka's Avatar
 
Join Date: Oct 2010
Location: irradiated - too close to the nuclear furnace
Posts: 1,294
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.
__________________
veremchuka is offline   Reply With Quote
Old 04-30-2013, 03:52 AM   #30
Recycles dryer sheets
 
Join Date: Dec 2012
Posts: 78
=SUM() is for a range of cells. Try using =B8+B13+B40 and see if that works for you.
__________________
BuysToys is offline   Reply With Quote
Old 04-30-2013, 07:19 AM   #31
Thinks s/he gets paid by the post
 
Join Date: Nov 2006
Posts: 2,268
Insert the following into a cell:

=now()

this will put the correct date into that cell automatically even if you dont update the spreadsheet.
__________________
utrecht is offline   Reply With Quote
Old 04-30-2013, 07:45 AM   #32
Thinks s/he gets paid by the post
 
Join Date: Jul 2012
Location: Mississippi
Posts: 1,878
Quote:
Originally Posted by veremchuka View Post
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 ?
__________________
rbmrtn is online now   Reply With Quote
Old 04-30-2013, 09:18 AM   #33
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Lsbcal's Avatar
 
Join Date: May 2006
Location: west coast, hi there!
Posts: 5,686
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.
__________________
Lsbcal is online now   Reply With Quote
Old 04-30-2013, 11:24 AM   #34
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: May 2005
Posts: 13,275
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...
__________________
Texas Proud is online now   Reply With Quote
Old 04-30-2013, 07:26 PM   #35
Thinks s/he gets paid by the post
 
Join Date: Apr 2006
Location: North Bay
Posts: 1,026
Quote:
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.

Quote:
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 is offline   Reply With Quote
Old 04-30-2013, 07:32 PM   #36
Thinks s/he gets paid by the post
 
Join Date: Apr 2006
Location: North Bay
Posts: 1,026
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.
Attached Files
File Type: xls SampleSum.xls (18.5 KB, 5 views)
__________________
scrinch is offline   Reply With Quote
Old 04-30-2013, 09:25 PM   #37
Thinks s/he gets paid by the post
veremchuka's Avatar
 
Join Date: Oct 2010
Location: irradiated - too close to the nuclear furnace
Posts: 1,294
Quote:
Originally Posted by BuysToys View Post
=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.
__________________
veremchuka is offline   Reply With Quote
Old 04-30-2013, 11:45 PM   #38
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jul 2006
Posts: 11,018
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!
__________________

__________________
Meadbh is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


 

 
All times are GMT -6. The time now is 09:12 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.