Auto Sum unknown number of rows

Im using odbc to import data from a database.

Imported is a list of products in column A and then various columns
containing sales figures.

Because of the import from the database, I do not know how many rows of
products will appear,  this week we sell 500 different products, next
week 550.

I need to add totals to the columns directly under the last product.

I've tried creating the totals in an empty template and then inserting
the imported data so the total figures move down and appear at the
bottom. However the imported data
moves the total cells to the right instead of down.  Tried all methods
of inserting the data range, but the totals always move sideways.

Does anyone know how to automatically create total cells at the bottom
of a column of values so they appear under the last item ?
Remember,I do not know how many rows will be imported.

Alternatively, does anyone know why my totals move sideways when I
insert data into my template.
I tried selecting rows and columns and everything else I could think of
before inserting the data range.

is VB my only option ?

Thanks
Regards
Andy



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/7/2003 1:32:36 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1084 Views

Similar Articles

[PageSpeed] 39

You can add the formula with a macro. Assuming the data starts in B2, 

  Cells(65536,2).End(xlUp).Offset(-1,0).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

As for why the data moves to the right when you insert, you have a choice of which direction to
move, down or to the right. Are you sure you are selecting the right option?


On Tue, 7 Oct 2003 09:32:36 -0400, andy_lakeland <andy_lakeland.uxlgb@excelforum-nospam.com>
wrote:

>Im using odbc to import data from a database.
>
>Imported is a list of products in column A and then various columns
>containing sales figures.
>
>Because of the import from the database, I do not know how many rows of
>products will appear,  this week we sell 500 different products, next
>week 550.
>
>I need to add totals to the columns directly under the last product.
>
>I've tried creating the totals in an empty template and then inserting
>the imported data so the total figures move down and appear at the
>bottom. However the imported data
>moves the total cells to the right instead of down.  Tried all methods
>of inserting the data range, but the totals always move sideways.
>
>Does anyone know how to automatically create total cells at the bottom
>of a column of values so they appear under the last item ?
>Remember,I do not know how many rows will be imported.
>
>Alternatively, does anyone know why my totals move sideways when I
>insert data into my template.
>I tried selecting rows and columns and everything else I could think of
>before inserting the data range.
>
>is VB my only option ?
>
>Thanks
>Regards
>Andy
>
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
myrnailarson (145)
10/7/2003 2:15:34 PM
Hi Andy.  See if this does what you want.  In the B-column, assuming the
data you are pasting in begins in cell B2, enter the following formulas:

In cell B3: =IF(B2="","",B2)
In cell B4: =IF(B3="","",IF(SUM($B$2:B2)=B3,"",SUM($B$2:B3)))
Then copy the formula in B4 as far down as you need it.

Hope this helps.

Ken

andy_lakeland <andy_lakeland.uxlgb@excelforum-nospam.com> wrote in message
news:andy_lakeland.uxlgb@excelforum-nospam.com...
> Im using odbc to import data from a database.
>
> Imported is a list of products in column A and then various columns
> containing sales figures.
>
> Because of the import from the database, I do not know how many rows of
> products will appear,  this week we sell 500 different products, next
> week 550.
>
> I need to add totals to the columns directly under the last product.
>
> I've tried creating the totals in an empty template and then inserting
> the imported data so the total figures move down and appear at the
> bottom. However the imported data
> moves the total cells to the right instead of down.  Tried all methods
> of inserting the data range, but the totals always move sideways.
>
> Does anyone know how to automatically create total cells at the bottom
> of a column of values so they appear under the last item ?
> Remember,I do not know how many rows will be imported.
>
> Alternatively, does anyone know why my totals move sideways when I
> insert data into my template.
> I tried selecting rows and columns and everything else I could think of
> before inserting the data range.
>
> is VB my only option ?
>
> Thanks
> Regards
> Andy
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
kensch (23)
10/8/2003 12:10:48 AM
Reply:

Similar Artilces:

Restrict Number of Columns
How do i restrict my worksheet to 20 rows & 20 columns. On Sat, 13 Feb 2010 01:20:01 -0800, xxx <xxx@discussions.microsoft.com> wrote: >How do i restrict my worksheet to 20 rows & 20 columns. Hide the rows and columns that you don't want to see. Hope this helps / Lars-�ke Hide those you don't want to look at. Or set the scrollarea via VBA Since the scrollarea method does not stick between sessions you will have to reset it each time you open the workbook. You may wish to place the code into a WorkBook_Open Sub in ThisWorkbook module and specify...

Please assist. Extract Number from String
How can I use a macro to extract only the first batch of numbers from the following type of info? Examples: 01458-MODE 1548-JUNE 1245-NOD 01054-MORNING 00154-JUNE 55145-55145 Result should be: 01458 1548 1245 01054 00154 55145 -------------------- I only need the first batch of numbers before the "-" sign. There will always be this sign "-" separating the two batch of data. Thanks! firstBit =3D Split(theValue,"-")(0) Tim On Dec 16, 8:39=A0am, Damil4real <damil4r...@gmail.com> wrote: > How can I use a macro to extr...

Reference every 4th row from a master list of modifiable length
I'm creating an excel database for a clinical study on patients wit migraine. Each patients will treat 4 attacks (episodes). Thi presents a bit of a problem, since there's some information I' collecting that's specific to the patient (demographic information lik sex, height, etc.), and some that is specific to each attack (like th pain level of that attack, the duration of that attack, etc.). T account for this, I decided to give each patient four rows--one fo each attack--and I put their deomographic information in the secon row, leaving the other three blank in those fiel...

LDAP port number
I have a Windows 2000 server with exchange 5.5. I would like to know is it possible to change windows 2000 server LDAP server port number 389 to some other port as I would like to leave my exchange 5.5 LDAP port number as 389? ray3d84 ...

How do I not print the column letters A-Z but keep the row #'s
I am trying to print a chart where I want to keep the row numbers but not the column headings A-Z printed. Choosing Page setup under the Sheets tab only lists them together not as single choices. Is there a way to customize the sheet to only list the row numbers? How about inserting a new A column and adding =ROW() best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "jgb" <jgb@discussions.microsoft.com> wrote in message news:E503B439-3323-4CE9-A5B8-5477CFC091C2@microsoft.com... >I am trying to print a chart where I want to keep the row ...

HOST UNKNOWN ISSUE
Hello All: I keep getting either a HOST UNKNOWN or COMUUNICATIONS error when trying to send to a specific domain. However, If I do nslookup from my mail server, it resolves to that domain name without issues. Similiarly, I can telnet from my mail server to the server in question on port 25 wiithout any problems. Why are users getting this error? Any help would be appreciated. Thanks ...

Hiding Rows Conditionally
Is it possible to hide a row only if a certain cell in the row contains a reference to another cell that has a specific value? Here's the scenario: Column A of a Summary sheet contains a list of 20 categories. Each category row contains references to data on another worksheet. Often, there is no data for a specific category, so the Summary sheet displays the category with zeroes or various errors depending on the formulas referencing the data. I would like to hide the entire row if the referenced data cell contains a certain value, for instance "0". Can it be done? Thanks for a...

excel page numbering
How do I number every page in a workbook which has several tabs [SIZE="1"]To insert page numbers on multiple worksheets at the same time, click the first worksheet tab, and then hold down CTRL while clicking the other worksheet tabs. On the View menu, click Header and Footer. Click Custom Header or Custom Footer. To specify where you want the page number to appear, click inside the Left section, Center section, or Right section box. Click the page number button , and then click OK. A preview image of your header or footer will appear in the Page Setup dialog box. -- Rebel ------...

counting phone numbers
I have a column that contains phone numbers. Some of the phone numbers are repeated in more than one row. I know there is a way, which I can't remember at the moment, to count the numbers such as this: Phone # of times it appears 555-555-1212 1 555-555-1213 1 555-555-1212 2 555-555-1214 1 Can someone remind me how to do this, please? Thanks. Glenn, You appear to be counting numbers above the one that is currently being considered (ie. 1212 counts 1 the first time then 2 the second) =COUNTIF(A$1:A1,A1) drag ...

Saving templates with sequential numbers
I have a template I use to enter information about customers, every time I save the file with a distinctive name. Here is what I want, in the Save As dialogue box should automatically be filled in a serial number based on the previous workbook, so I can track sequential numbers. Is there anyway excel lets me do this? Hi, Hershey. Search this page for the word "number" or "invoice". Look for Excel macros---there's a few entries there that offer up numbering methods. http://www.vbaexpress.com/kb/kblist.php ******************* ~Anne Troy www.OfficeArticles.com &quo...

Combining rows of data that have one cell in common
Hi everyone, let me explain... I'm working with a large excel file (13,000+ rows) of cancer cases recorded in the state of Florida. Each time a patient presents with a cancer, that "case" is recorded as a row on the spreadsheet, along with a UNIQUE patient ID assigned to that particular individual. If that same person develops another cancer sometime later, that information appears in a new case/row, but with the SAME patient ID. I need to export this file to another program (SPSS v13.0) to do some advanced statistical analysis. However, the program requires that all of t...

vb dataset row count incorrect
Hi Has anyone else had this problem. Im using a vb .net dataset to read & write XML but when I check the number of rows for a table it's incorrect. Example number of rows = 4 table row count says there are 8 rows does anyone know how to correct this problem ? Regards Neil neil rowe wrote: > Has anyone else had this problem. Im using a vb .net dataset to read & > write XML but when I check the number of rows for a table it's incorrect. > > Example number of rows = 4 > table row count says there are 8 rows That may have something to do with whitespa...

Printing the first two rows as headers.
I would like to have the first two rows print at the top of every page of my print out. I know this is a basic function, yet I can not seem to find it in the help function. Could someone please post how to do this /AND/ tell me what key words to put in the search/help function that would show the answer as well. Thanks Michael -- J. Michael Morse michael@stignatius.cc <mailto:michael@stignatius.cc> St. Ignatius <C:/Parish%20Administration/Graphics/Bulletin/Ignatius2C.jpg> "Let no one ever come to you without leaving better and happier." - Mother Teresa Hi ...

Generate multiple ACH Files (NACHA files) based on routing numbers
We are using GP 9.0 with Direct Deposit Module. We would like to find if there a way in GP to Generate multiple ACH Files (NACHA files) based on routing numbers setup for employee's in Great Plains. We are currently setup to generate 1 ACH file per company. Customer support response was that there is no solution in any version of GP. If someone can of any assistance with this request, it would be great appreciated. Thanks. Azfar Azfar, You could write a VBScript or VB program that will take the output file and split it in multiple files based on routing number. This should be...

Showing Number of Read Messages
Hi. In Outlook Today I show my Inbox, Outbox, Drafts and Junk-E-Mail folders. When I have no unread mail in my Inbox Outlook Today displays 0. But, my Junk-E-Mail folder displays the number of messages regardless of whether they have been read or not. Is this by design? If I've read the messages in my Junk-E-Mail folder I want Outlook Today to display 0. Thanks, James ...

How to show actual sum
Any help will be appreciated, I am trying to display the actua calculation along with the answer. In excel you enter a formula in cell and that gives you the answer I would also like to display th working out. Thank -- Message posted from http://www.ExcelForum.com Something like: ="B1+B2=" & B1+B2 Regards Trevor "wk495292 >" <<wk495292.15n6xv@excelforum-nospam.com> wrote in message news:wk495292.15n6xv@excelforum-nospam.com... > Any help will be appreciated, I am trying to display the actual > calculation along with the answer. In excel you en...

convert number to dozen showing remainder
Trying to convert a number to show dozen with remainder. ie: convert 29ea to 2dz 5ea =INT(A1/12)&" doz "&(MOD(A1,12))&" ea" The value in aA1 should be a number edvwvw hayescircus wrote: >Trying to convert a number to show dozen with remainder. ie: >convert 29ea to 2dz 5ea -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/201003/1 ...

How to Show/Hide Columns or Rows with no data via VB
Hi, I am need of some advice please. I have a spreedsheet (2007) which I designed as an attendence register. Currently it works well but I want to improve on a few matters. For the main page I have a column with the date, next column day, then the next 40 columns are set for the crew employees. These are broken into 4 groups of 10 for each crew (A,B,C,D, Shift workers), however there is normally only 7 people per crew, the other 3 are blank unless there are extra enployees to that crew. Currently I am hiding and showing manually, but I want to be able to do something via ...

Formula For Sorting Part Numbers
I have Excel 2000. Sheet1 of my workbook has: * Part numbers in column D * Primary bin location in column F * Secondary bin location(s), if any, in columns I, J, K, L * Each part number and corresponding bin location(s) are listed on the same row and there are no duplicate part numbers I need help with a formula (to be inserted in Sheet2) that does the following: * List all the part numbers and bin locations (primary + secondary) if there is an "X" in column AB of any part number listed in Sheet1 * All the information for a part number should be on a single row * T...

Custom Number Formatting
I'm creating a form where you can enter a credit card number. I set a custom number for the cell and described it as ####-####-####-####. You can enter a 16 digit number just fine, but when you tab away from the cell and the custom number takes place, it changes the last digit to a "0". Has anyone else encountered this? How do I fix it? I was told I could add an apostrophe before the entry, but this form is going to be used by hundreds of employees, so it needs to work automatically. Suggestions? -- CSBUG ---------------------------------------------------------------------...

auto-tabbing
Hi folks! I found the perfect code yesterday for entering survey data and my form seemed to be working was working but now it doesn't. The first field is called "Q_No". In the "On Change" event for this field I have the following: If Len(Me![Q_No].Text) > 2 Then Me![S1S1].SetFocus End If When I enter a 3-digit number into "Q_No" the cursor moves to "S1S1" but it won't let me enter any numbers and the database crashes. If I bypass "Q_No" and enter a number directly into "S1S1" the tab works fine for the rest o...

insert a row in a protected sheet
hello out there, does anyone know in excel. how do insert a row in a protected sheet without unprotecting the sheet. i have a document where i do not want the formula to be touched in no way form or fashion but i would like my users to constantly add or delete information as they occur and the only way to do this is by inserting or deleting a row to enter information. thanking you all in advance for your continued help. thank you, rennoyinc ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! Free Support at http://www.ozgrid.com/forum/ ** Which versio...

Conditional Formatting Whole Row
Morning All, Using Windows and Excel XP. I have a worksheet that has the dates of this month in A1:A31 and other data in B1:F31. I have set up a conditonal format: cell value equal to =TODAY() to have the current date (December 6) in A1:A31 to highlight in yellow. What I would like to do is not only highlight the current date but also all the data in that particular row. For example, in A1:A31, December 6, 2003 (cell A6) is highlighted in yellow. How can I get the entire row (A6:F6) to be highlighted in yellow. Is it possible to do that? Thanks in advance, Mi...

Error Number: 0x800C0131
Hi, i've been having alot of problems with outlook 5.0 for ME but now this error is repeatedly coming up. It is refusing to recieve or send emails from varying accounts. What should i do? Thanks, Andrea This newsgroup is for support of Outlook 97-2003 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.window...

y-axis auto range based on data limits
I have a spread sheet with numerous charts. I need the y axis on the charts to set the minimum appoximately 20% below the lowest data point and set the maximum appoximately 20% above the highest data point. The major unit should be set at appoximately 10% of the entire range of data. The spreadsheets are templates and will have a wide variety data placed into the cells. Calculate the appropriate values in some cells. Use this technique to apply the calculated values to the chart's axis: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html - Jon ------- Jon Peltier, Mic...