Spreadsheet help needed

[veremchuka]

I know next to nothing about spreadsheets. I looked at a few in work but never really had any experience using them and I never even attempted to make one.

I have a hobby for which I am tracking my costs and the volume of "things" I'm buying and using. I thought maybe a spreadsheet would be a good way to do this so last night I made a spreadsheet. I use Open Office (OO) and it has a spreadsheet (SS) option. So I took an Excel SS I had one from someone and saved it as an OO SS. I saw how to calculate totals from it and lo and behold I was able to change headings, delete columns and rows, add a column and many rows, wow I'm starting to think I may actually have a workable document.

Here's the problem. I have 3 or 4 sections, each has a total and at the end a grand total. If I add any new rows, like when I buy more stuff which is ongoing, it requires me to adjust the formula for the total in that section and the grand total.

Is it possible to have a formula that automatically adjusts for the new rows I add? If not this is too cumbersome to adjust each time I buy stuff and I am probably better just keeping track on paper or in an OO document where I will have to manually add the new purchases to the total and grand total.

I'd appreciate any help!

 

[imoldernu]

I've been using Google Drive to create and save spreadsheets, but am not really savvy in creating, except that whenever I add a "row" into the spreadsheet, it automatically recalculates the totals.
I suppose there's an easier way, but whenever I use subtotals, instead of creating a new formula, I just put the formula into another column. That also automatically accepts new calculations and adjusts the totals without needing to re-do the formulas.

Will follow along here to see an easier way. Am getting a little to old to start learning new stuff. Instructions that used to seem easy, now, not so much.

 

[Texas Proud]

I do not use OO, but yes, most formulas are dynamic and not static...

Look to see if you have a dollar sign in your formula... as an example..

Cell a1 + a2

static is =sum($a$1+$a$2)

dynamic =sum(a1+a2)

 

[veremchuka]

Here's how I write the formula I use for the totals with the categories, obviously this is for just 1 of the categories

=SUM(B11:B15)

if I add another row then I have to change the formula from B15 to B16



for the grand total line i use this formula

=SUM(B8+B16+B35)

going back to the 1st formula example, adding another row now effects this gt formula because B16 (the total line or row in that category) is now B17 not B16

It would be great to be able to just add rows in these categories and have the formulas adjust themselves for the totals and the grand total, is that possible?

 

[gauss]

Try selecting an entire row between 11 and 15 on the Left hand side.

Then with right mouse button click insert row.

A new row should appear and the sum() formula should be updated accordingly.

-gauss

 

[dfarr]

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.

 

[veremchuka]

This is interesting. I tried what you said and the formulas in that category and the grand total were adjusted! Maybe it was how I was adding a new row last night? Nope I added a row like last night (clicked the drop down menu, click insert and click row) and the formulas are adjusting for it this way too.

I'm confused, last night I thought I had a problem and now it appears to be adjusting correctly. SS are a mystery to me and this is a "see dick run. run dick run" SS - super simple.

Maybe the problem last night was I did not have any extra rows in each category and upon adding extra rows it threw things off? I did add extra rows into each category last night before finishing and maybe those unused rows are allowing the formulas to adjust properly?

Like I said, this is a mystery to me! It's a toss up between a challenge to learn this and who needs this crap!

 

[veremchuka]

I'm going to check back later, I have some stuff to do now and I have another question. I'll wait on that for replies to this 1st issue to clear, though it seems to be doing what I want without me having to adjust formulas which was a no go situation for sure. This is clear in my head now, wait 7 days! If I had to figure out how to adjust formulas I'd be wondering how to do stuff, was it done correctly....

 

[RunningBum]

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.

 

[TrvlBug]

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.

 

[Original Wally]

Here's how I write the formula I use for the totals with the categories, obviously this is for just 1 of the categories

=SUM(B11:B15)

if I add another row then I have to change the formula from B15 to B16



for the grand total line i use this formula

=SUM(B8+B16+B35)

going back to the 1st formula example, adding another row now effects this gt formula because B16 (the total line or row in that category) is now B17 not B16

It would be great to be able to just add rows in these categories and have the formulas adjust themselves for the totals and the grand total, is that possible?

There is a real easy "trick" to make it AUTOMATIC. In your first formula..
=SUM(B11:B15) be sure to have the ENDING row ("B15) be a BLANK row just below the bottom of your list. THAN if you add a row ABOVE row #15, the sum will automatically change to =sum(b11:b16) and the second formula will also automatically adjust to something like =sum(b8+b17+b36)which accounts for the row inserted. if this doesn't make sense, PM me and I will shoot you a quick example. It really is simple.

 

[scrinch]

V, I'm not sure why you had the initial problem with the formulas not updating, but I can assure you that spreadsheets are built to automatically update simple formulas like you want. Open Office must have sensed you were a newbie, and it was just messing with you.

 

[kaneohe]

My experience is similar to AW's. I'm not sure the last line has to be blank but I would insert the new rows somewhere between the 1st and last entry
"in the middle" and not below the last entry.

 

[BTravlin]

Inserting a new row just above the first row of your data set or above the row with the formula will cause the error you were experiencing. Insert a new row anywhere between the first row and the last row and the formula automatically adjusts.

I've been using Excel for 24 years and still learn new things about it all the time.

Open up the online help and browse through the @ functions. Amazing what all you can do with these. You don't need to know how to use them all but just skim through them so you know what's available and possible.

The trick to many things in Excel is realizing it operates a lot like Algebra II. Formulas follow basic orders of operation and your syntax must be perfect.

 

[walkinwood]

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.

In my experience, this is the easiest way to do it.

 

[Original Wally]

My experience is similar to AW's. I'm not sure the last line has to be blank but I would insert the new rows somewhere between the 1st and last entry
"in the middle" and not below the last entry.

Doesn't NEED a blank row, it is a "reminder" to ME. lol

 

[Brett_Cameron]

For my spreadsheets that I manually data in new rows and that I need to sum, I put the sum formula in the top row and define as many rows as I need. I also use extra worksheets for several of these lists and sum grand totals elsewhere. Example: one tab contains medical milage, dates, reasons. Another tab contains list medications, another tab a list of doctor visits and so forth. Totals are on another sheet with sums from each category..

There are lots of ways to do what you want. As you use the spreadsheets more you will probably find the way that is least work for you.

 

[rbmrtn]

Inserting a new row just above the first row of your data set or above the row with the formula will cause the error you were experiencing. Insert a new row anywhere between the first row and the last row and the formula automatically adjusts.

Yes, this will cause it, also inserting the new row after the existing data rows. Insert the new row between the existing data rows and it adjusts itself.

 

[target2019]

Most expect the sum row to be below the data rows. In that case, if you select the sum row and "insert", your total is off if you put values in the inserted row. If you select the last row of data and "insert", then the formula will adjust.

For the reason above, I usually insert a blank row and include that in the data rows. I select that row and insert, and all is well.

You have to be careful when sorting these areas in the spreadsheet.

 

[veremchuka]

Thanks everyone. I don't know how many blank rows to pad a category with as I'll always be buying stuff in at least 2 of the 4 categories, one category will eventually need another 100 rows in time. I have 3-4 blank rows in each category now so I can add something and as I said before I'm sure last night I did not have any blank rows and that's when adding a new row caused the formula to not adjust to pick up the new row or rows. But it is working now and it's only logical that a formula should automatically adjust for new rows added otherwise you'd be constantly tweaking the formulas and that'd make a SS worthless.

So here's my other question. 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.

TIA.

 

[Ronstar]

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

 

[scrinch]

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.

 

[target2019]

Not sure if I understand all, but the OP might be looking for a way to insert the date the file was last modified...

 

[Texas Proud]

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.

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]

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