Simple calc query

I am new to Excel and have Excel 2007 if that makes any difference.

I am just trying to write my own vehicle logbook for my own vehicle which is 
used personally and for work. To denote this I have done the following:

value 1  value2  Notation  work column Personal column

The above are different cells. Value 1 is the start mileage. Value2 is the 
end mileage. Notation is either P or W. Work Column has the formula 
=IF(Ex="P",Dx-Cx,) where the "x" equals the cell number (eg, E6 or whatever) 
and of course it goes for rows from A downwards. The end result, so far, is 
that if it was a personal use then the value appears in the Personal column 
and if a work use then in the work column. The column that the value does 
not go in shows a simple 0 which is fine by me. I would prefer it to be 
blank but it is OK for now.

My problem is this. I would prefer to have, below the last entry, a 1 line 
gap and then a total for each column which is easy enough to do but what I 
would like is that when I want to enter a new entry, I can make the autosum 
for each column automatically move down one line. As you may guess, the 
logbook will last for a full financial year and then when the total year is 
over, I just create a new workbook for the next year and of course keep each 
excel file for my tax records, printing them out if necessary.

Is it possible to have those total columns move down auto when I enter a new 
trip value? If so, how can I do that, please?

Apologies for my newbieness in advance. I never had a use for Excel 
knowledge before but I bought Office 2007 and have bumbled and stumbled 
around to the point that I have everything I need excepting the auto 
advancing totals. It took a good 30 minutes to get this far to my 
embarrassment!

Thanks in advance.

0
lrb1 (101)
3/23/2007 12:28:39 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
593 Views

Similar Articles

[PageSpeed] 24

To get a blank rather than a zero use =IF(Ex="P",Dx-Cx, "")

Can the Sum be at the top of the table? =SUM(A2:A2002)
I am assuming you will not have more than 2002 entries

OR in XL2007 make the table into a Table (an Excel table not just a block of 
data)
Then you can have a totals row
But you will need to right click this row and use Inset every time you want 
to add data
Or use CTRL+SHIFT+T with a Table cell selected to toggle the Total row 
on/off; but I found I had to rest the total value from COUNT back to SUM 
each time

best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Diamontina Cocktail" <lrb@australia.com> wrote in message 
news:%23Jb6LbUbHHA.1400@TK2MSFTNGP06.phx.gbl...
>I am new to Excel and have Excel 2007 if that makes any difference.
>
> I am just trying to write my own vehicle logbook for my own vehicle which 
> is used personally and for work. To denote this I have done the following:
>
> value 1  value2  Notation  work column Personal column
>
> The above are different cells. Value 1 is the start mileage. Value2 is the 
> end mileage. Notation is either P or W. Work Column has the formula 
> =IF(Ex="P",Dx-Cx,) where the "x" equals the cell number (eg, E6 or 
> whatever) and of course it goes for rows from A downwards. The end result, 
> so far, is that if it was a personal use then the value appears in the 
> Personal column and if a work use then in the work column. The column that 
> the value does not go in shows a simple 0 which is fine by me. I would 
> prefer it to be blank but it is OK for now.
>
> My problem is this. I would prefer to have, below the last entry, a 1 line 
> gap and then a total for each column which is easy enough to do but what I 
> would like is that when I want to enter a new entry, I can make the 
> autosum for each column automatically move down one line. As you may 
> guess, the logbook will last for a full financial year and then when the 
> total year is over, I just create a new workbook for the next year and of 
> course keep each excel file for my tax records, printing them out if 
> necessary.
>
> Is it possible to have those total columns move down auto when I enter a 
> new trip value? If so, how can I do that, please?
>
> Apologies for my newbieness in advance. I never had a use for Excel 
> knowledge before but I bought Office 2007 and have bumbled and stumbled 
> around to the point that I have everything I need excepting the auto 
> advancing totals. It took a good 30 minutes to get this far to my 
> embarrassment!
>
> Thanks in advance.
> 


0
bliengme5824 (3040)
3/23/2007 12:53:48 PM
Reply:

Similar Artilces:

Excel hangs with just simple typing in cells
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi there... <br><br>I have a Mac Pro with 10gb of memory. I've recently updated to Office 2008 due to having some issues with Word 2004 - I figured that it may be a conflict with snow leopard. <br><br>Anyway, I now have a 'proper' issue with Excel. All installed and updated as it should be. Here's what's happening... <br><br>1. Type anything into a cell - eg &quot;123&quot; <br> 2. Hit enter <br> 3. Excel hangs, the pinwheel rotates fo...

Can anyone help me with a simple IIf function?
On table Reser, if field equip =AMB Eld, enter 1 if not 0 Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1 Duane Hookom wrote: >Your question lacks sufficient detail to provide more than a WAG. Try >=IIf([Equip]="AMB Eld",1,0) > >> On table Reser, if field equip =AMB Eld, enter 1 if not 0 >> >> Thanks Thank you very much. That is what I needed, I was missing the quotation mark -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707...

Simple(??) lookup question
I'm sure this is simple for lots of people, but I can't seem to get it. I have a date generated by a formula. I need to see if this date is in a list of dates. I tried MATCH, but I couldn't make it work. I looked at VLOOKUP in the Help file, but couldn't see how to make that work either. I need this in an IF formula in another cell. If anyone can line me out on this, I would greatly appreciate it. Ed If you use vlookup with the false parameter at the end, that should work. Regards, Eddie http://HelpExcel.com Are the dates in the list and the dates returned fr...

How to insert and query nodes?
Hi all, I need to use XML to store some log data and then query that data. I'm struggling with the right terminology, which is hindering my ability to successfully search for a solution. Thanks in advance for your help! Two questions: 1. In VB.NET, how can I append new "Log" record to a "Site" record? 2. How can I query all of "Log" records in a single "Site" for an average "ResponseTime"? ------------------------------ <?xml version="1.0" standalone="yes" ?> <Sites> <Site Host="www.mysi...

Query with IIF Expression Need Help
Hi ALL, I have a query which has the following fields: ROOMS RESNAME ARRIVAL DEPARTURE I have added another field in my query named ROOMS IN as an expression where I want it to return TODAY by looking at another table which has only one record which is the running date table name:RUNDATE and field DATE and then looking at the arrival date and adding two days if applicable to return to the expression field LINEN "TODAY" LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS") it returns 2 days to the other records which is fin...

Query: date is null
I am trying to write a query to call up records where no date is entered (i.e. null). However, when I go to run the query it gives an error of data type mismatch. Any ideas? Thanks What have you tried? Field: SomeDateField Criteria: Is Null IF the above doesn't help, please copy and post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County ...

number query results
Hopefully I can explain this well enough, I have a database set up to track product key attributes. Works great. I have a query that will pull the last 9 records where the line and product code match. This also works great. I need to graph the last 8 hours of lets say "moisture" with the difference between each hour also graphed (i.e. 8:00 = 4.5 and 9:00 = 5.0 I need to graph a .5). I have a form to show the query results. My goal is to have an unbound field that will "number" the query results in the form so that I can return the value of "query record #8...

SQL vendor query
Hi Guys - I need to write a query that will show all new vendors added each day. This idea is to write the query and shoot it off via email every day using sql reporting services. I am having problems writing the query. Any suggestions. Michael, There is a field in Vendor Master table (PM00200) called "CREATDDT". This field will be filled with the date in which the vendors are created. Now your query can rely on this field to list out the vendors on a particular day. For instance, if I want the Vendors who are created on 21st Aug 2009, I would write my query like this: ** S...

External web query
I am not a programmer and a new excel user. I have an external web query that I am using tracking external links from a web site. I need to create an additional column with the web page info that relates to where the link is coming from. I tried but when I refresh the data it inserts cells with the any new links listed but then that throws off my additional column info. I looked in Data Range Properties and checked the middle option but that doesn't work the way I thought it would. Suggestions? -- Message posted via http://www.officekb.com ...

Web Query #4
Hi, I'm not really sure if this an Excel issue but here goes. I have a file set up that gathers data from a web page. It updates web hits and other data from the page on a daily basis, the only problem I have is that I either have to leave the file open 24/7 or manually open it each day. I've no problem using code to place the updated data into the appropriate date columns and saving, it does that already, but is there any way of getting an Excel file to open automatically at a specified time, update itself via the code and then save and close? TIA Regards, Alan. Office XP2003 Vi...

Drop downs to select queries
I have a very large database (by my standards) that holds details of 35,000 products that I need to produce price lists for. It relies on information produced by others that I have no control over and is frankly a dogs breakfast as far as standardisation goes. However I am stuck with it. Products fall into Groups and Categories within Groups then sizes and prices within Categories. I have laborishly marked each product as to the Group and Category with Yes/No fields for the Groups and a combo box drop down for the Category so I end up with each product having a tick in one of 10 Yes/ No fi...

Microsoft query criteria help
Hi I am using microsoft query to get external data into excel and would like to filter info for forecast date greater than todays date i can type in todays date but would like to use formula =now() so can save query and use whenever required is this possible? Thanks Tina ...

Query wizard problem...
I can't get the query wizard to show the columns from an Access database. I get as far as showing the tree view of all the tables, but the tables won't expand to pick columns. I've reinstalled Excel, and I've checked google and the knowledge base... Anyone have any ideas? Thanks! ...

Having Trouble With Query Criteria
I have a query where I want to select records based on a name in the "Assigned To" field. The user selects a name from a list on a criteria form and the query takes the name and selects records with that name in that field. This part is easy. However, if the user does not select a name on the criteria form, I want the query to return ALL records. I don't know how to choose between these two possibilities. I can set up the query to run one or the other, but I don't know how to conditionally choose between them. Does anybody know how to do this? David, in that case...

MRP Query question
Feel free to point me to a better forum as this is really a SQL question. When MRP is regenerated info on each regeneration is stored in MPPS0130. I'm trying to construct a query against the MRP months table (MP010330) that will return details on each row where the date value is greater than or equal to the highest run date value from MPPS0130. I'm using the MAX function to find the highest date from MPPS0130 but when I try and compare that against the dates from MP010330 the query errors out. Any guidance would be appreciated. -- Jim@TurboChef Nevermind - got it to work us...

ldap query for address list
my organization is now migrating from exchange 5.5 to exchange 2003. after migration, i just couldn't find a way to get that sorting of address book view like in exchange 5.5 that sort the gal by grouping (e.g, sort by company then department). will it is possible that the custom ldap query will do sorting like sql statement (group by) or (order by)? ...

ddv custom Query
Hi there, I try to write to write a custome validation which is quite similar to ddV_minmaxDouble(). It checks if the values grater zero: void DDV_ValidateInput(CDataExchange* dx, double value) { if (value <= 0.0) { AfxMessageBox("value should greater than 0 "); dx->Fail(); } } I place it under do_dataExcahnge. It will pop up a message box with the warning if the user enter an invalid value. However once I close the message box, the process will continue running. It is unlike with ddv_minmaxDouble(). The user is not allow to do anything other things before changi...

Grouping dates in Microsoft Query
Hello, is it possible to aggregate lots of daily billing dates to 12 months in Microsoft Query, before returning the data to Excel? Thank you in advance. Kind regards, H.G. Lamy Excel version? Can you give an example of what the output would look like? --JP On Feb 2, 7:00=A0am, "H.G. Lamy" <Enterp...@web.de> wrote: > Hello, > > is it possible to aggregate lots of daily billing dates to 12 months in > Microsoft Query, before returning the data to Excel? > Thank you in advance. > > Kind regards, > > H.G. Lamy J...

Execute query from textbox input
I have a form which has a query made up of a few other queries (this may be an incorrect thing to do) as the Record Source. It has a three textboxes on the form in which I need each one to perform a specific query associated with that textbox. In other words, the user types in a number, presses 'Enter' or 'Tab' key, then the query (maybe needs another mechanism) associated with that entered number will execute, and it will display the relevant results in the form. This is the reason I used a query containing the need queries as the form Record Source, so I could bind ...

Query Personal Address book -v- Contact list
If I have understood what others are saying about Outlook 2000, Personal Addresses are .pab files and Contacts are .pst. I was not aware of the .pab files. I have recently changed from Outlook Express to MS Outlook (2000) I am running XP Pro and Office 2000. When I upgraded from OE I seem to have the majority of my previously used contacts (some are missing for some reason - especially the group contacts). I though I should be using Personal Address so followed the following MS instructions:- .. 1.On the Tools menu, click Address Book. 2.Click New Entry . 3.Under Put this entry, click Pe...

Simple Form Entry not workin
I have been using a workbook for several years that takes advantage of the ribbon based form that uses table headings as form labels. Stupidly, I merged some cells (actually not in the data entry area but adjacent to it). Immediately I began to get the message that form entry cannot be used on merged cells. I reinstated the merged cells but I still get the same message and am unable to use the form entry method. Can't find a solution anywhere - even in this venerable forum. Can anyone help me please? -- John ...

Microsoft Query is changing a negative number into a date
I am using Microsoft Query to pull a subset of a large excel file however two of my columns which are suppose to be numbers are not pulling in the negative numbers. If I look at the query in microsoft database the number shows up as a date/time string. I've tried formatting the original number as a number but the query still won't read the negative number. How do I get the query to read a negative number as a number and not a date. Hi Highlight the columns and format as a date "mowens" wrote: > I am using Microsoft Query to pull a subset of a large ex...

Read Excel, CSV and Tab delimited text files with an SQL query for Customer and Item imports to RMS
Hi Convoluted, On May 13, 6:35 am, convoluted <convolu...@discussions.microsoft.com> wrote: > I don't know how to have your update or insert query read an excel > spreadsheet, maybe someone more experienced with SQL will reply........ How to link sql server to an excel spreadsheet 1. Create a folder that SQL server has rights to access, EG: C:\Data \Import\ 2. Place your spreadsheet in the folder EG: ConvolutedSomething.xls with a worksheet called MySheet 3. Create a linked server to tell sql server about your folder 3.a You can do this with a script but I prefer...

Update Query
I have two tables, Employee and Gaptable. In the employee table I have a SSN field (social security number) a UPI field (unique personal Id numer) I want to update the UPI field in the Gaptable where the SNN field matchs from both tablle. How would I write the query for this? I hope it is clear enough. if i am understanding you, it should be a basic Update query. Here is the SQL: UPDATE Employee INNER JOIN Gaptable ON Employee.SSN = Gaptable.SSN SET Gaptable.UPI = Employee.UPI; Note I assumed the field names were SSN and UPI in both tables. you may need to modify accordi...

Max Date Query w/ Nulls
I've got a situation where I need to run a query that pulls data from two different (but related) tables, and then lists the most recent date for each entry in the second table. The two tables and fields are tblProperty tblProperty.PropSiteName tblProperty.PropStreet tblInspection tblInspection.InspDate Each Property can have multiple inspection dates. My goal is to have a report that provides the PropSiteName, PropStreet and InspDate, with the information sorted by the InspDate that also shows the null records (properties that have never been inspected). Is there a way to do this wi...