Free Microsoft Excel 2013 Quick Reference

Accumulating amounts into a balance

How can one update a balance such as:

Opening Balance Debit Credit New Balance
$200 $100 -50 $50

When I need to post my new Balance will do the calculation such as the opening balance will be added to the New Balance and then move the New Balance to the opening Balance and "$0.00" the New Balance and the Debit and Credit.

Hope this is clear?

Can it be done through a VBA if yes, can any one show me how?

Thanks for your help in advance.

Post your answer or comment

comments powered by Disqus

I am racking my brain trying to figure out how to input a dollar figure into
a cell (A1)that updates another cell (A2) with the running total (easy to do)
but the hard part is when you want to add in the next dollar figure in the
same cell (A1) but you want to increase the value in A2 by this new number.

Why do I want to do this?

I have created a spreadsheet for a game and instead of using paper money I
want to be able to increase or decrease the player's dollar amount by having
the player (knows nothing about excel) input the dollar amount they want to
either increase or decrease their running total by and I want to keep it
simple for the player so they do have to add formulas when they enter the new
dollar figure.

Hope this makes sense?

Hello All,
I am working on a project where I have to collect several reports, import those into Access as text, and then query those tables to find amounts that I then need to export into a specific cell in MS Excel. My problem is that because of how the original reports are set up I need to set up a Recordset function along with a Looping function, to find the amounts I want and then I have to put those amounts into a pre-made spreadsheet that already contains formuals. Does anyone have any ideas how i can specify the Query/cell in Access I want to export and then put it into a specific workbook/sheet/cell in Excel? Thank you.

I'm entering an amount into a cell, but the screen shows a number altogether
different from what I type.

I checked the format of the cell. I couldn't see anything wrong.

I am struggling with this worksheets are all in one workbook. There are
columns for: Name of Payor; Invoice #; Category of payment; Cheque #, Date of
Cheque; cheque amount

I need to get the "category of payment" and the Cheque Amount into a pivot
tablework that will tally them up and as I enter new info into those columns
the changes will be reflected in the table

How do I do that and what shoul it look like?

Good Afternoon!
I am having an issue with trying to figure out how to program a specific macro in VBA using Excel 2007. This macro that I want to develope will concatenate a range (single rows only (example: select C20:X20)) of X (variable) amount of Cells into 1 single cell, preferably the cell preceeding the selected range (ie B20). The issue is that not all the cells in that range would be filled and each row has a different column filled in with text. Thusly, how do I program a macro that will allow me to select a range of cells, pick out only the cells filled with text, and concatenate those cells into a single cell just preceeding the range in the same row? Then from there i would be able to copy that macro and and paste it in an infinite number of rows below each row selecting it's own row range next to it and applying the concatenation (example concatenate C20:X20, copy and paste the macro to the next cell and it will automatically select and concatenate C21:X21... etc...). Thanks for taking the time in considering my post


When pasting large amounts of text (using Paste Special - Text) into a cell,
we don't see all the text. Some text appears to run beyond the cell border.
Tried the AutoFit and that doesn't make the text fit. When I widen the
column, the text still doesn't appear properly. What more can I do? Thank

In Excel, is there a limit on the amount of text that can go into a cell? I
have to create either a spreadsheet or a database (table definitely doesn't
work for my purposes) in which one of the fields will have to be large enough
to take a significant amount of text - anywhere from one sentence to the
equivalent of half a page.

Hi, I am creating variable amount of XL files using Access VBA. Each XL file has a single sheet with
different columns. I wanted to copy the work sheet from all the XL files in the directory into a master
file with multiple sheets. How can I do that? I simply want to combine all the files to a single
one. I am using Access VBA 2007. Thanks for your comments. There is a code in the following link but
when I used that I get error 2302, subscript out of range.

Hi all,
I was looking for some help on a formula.

Im working on a balance sheet for a database.
Heres how it goes.
I have some clients/students who attend EMT/Basic Life savings courses.
I have devised a database for keeping records of them, including their balances.
When importing data to excel spread sheet, I have the amount they have paid, but Im searching for a way to Lookup the total cost - the amount they paid based upon the type of class they take.
I have 15 classes and different prices.
I was using the IF command, but after 7 if's memory runs out.
What is an alternative route for this.
Example: =IF(K2=$D$17,$E$17-E2,IF(K2=$D$18,$E$18-E2))
When I import data, I know the class they take, and the amount they paid, but the IF command is too long by this method. Whats another method?

I am creating variable amount of XL files using Access VBA (2207) and output into a directory. Each XL file has a single sheet with different columns. I wanted to copy each sheet from all the XL files in the directory into a master XL workbook with multiple sheets so there is only xl file. How can I do this in Access VBA? There is a code in the following link..but when I run that I get runtime error 2302, subscript out of range. Thanks for your comments.

I have a file that has various numbers of columns, some of which i need, some of which i do not. Under these columns, there are various rows, sometimes 50, sometimes 400. I need to write something that copies only the specific columns I need and pastes it into a new worksheet. For example, in the data file there is are columns property type, loan balance current, and amortization type. I need them to be copied with the information in the rows below them to a new worksheet, to eliminate the unnecessary information in the data file.

Hi all,

I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.

Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.

List of target columns in sheet 'Final' and source cells in sheet 'Source':

Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount

So basically I'm creating an entry for every amount in the table.

Hope to hear from someone.. and thanks for your help!

I have big list of sales details with contract #, year of sale, territory, account head and amount. As there could be more than one sale for a contract #, I converted this list into a pivot table. Please see attached sample spreadsheet.

I would like to convert this pivot table to a list, i.e. blank cells inside the pivot table should be filled with appropriate values. For example, in the pivot sheet attached, I need value "2000" in cells A6 to A8 and "MALAYSIA' in cell b7 and so on.

Is there a a way to achieve this?

The sheet has two parts (source and summary).

I have a log for my checks. For very specific reasons, it's stored in 3 columns: Month, GL Code, and Amount. In each column, the same data can be repeated several times throughout the log. For example January is mentioned 20 times and the GL Code 15100 is mentioned 6 times (but not necessarily all in January)

To the right of the log, I want to summarize the information into a matrix with the months across the top and GL Codes running vertically down the left hand side of the matrix. The totals are to automatically sum in the matrix based upon the corresponding month and GL code from the source log.

Source: Of the 20 instances in January, 5 instances have the GL Code 51000, the total amount of all 5 is $2000.

Matrix: in the January column in the row labeled 51000, $2000 is calculated.

The matrix has to base the calculations off of two criteria: month and GL code. Since there are several instances of the same information, it has to somehow sum the infomation.

What is the best function (or combination of functions) to pull this off?

Thanks in advance!

I have data columns that accumulate a balance with a 3 digit code after the last accumulated total. The last accumulated total may be 2 or 3 cells long or as many as 12 cells long.

I need a formula in a cell that returns the last accumulated total above a code.

For example, in column H starting with cell H5 the following accumulated totals exists:

05 (4,329.41)
06 (11,399.90)
07 (18,319.78)
08 (23,454.78)
09 (31,755.69)
10 (38,720.00)
11 (47,800.42)
12 (56,345.31)
13 (48,865.40)
14 418.00

I need a formula in cell H25 that returns (48,865.40). Some months the totals may go thru row 16 or 06, etc. However, the code 418 will always be in the next cell after the last accumulated total. Some columns will be negative & some will be positive.

Please help. Thank you very much. mikeburg


I need to enter information into a company's website form. The usual info., that is, date, name, arrival date, departure date, time, amount, etc. It takes a long time. Have to do that for about 10 to 20 people at a time. Is there a way to build an Excel spreadsheet, enter the info. onto the spreadsheet and upload it to the website? Am I able to do this or do I request the company to build a programme?



This is relating to my previous post. I have figured out how to return the
column letter of the column I want to SUM through in a separate cell (cell
L4). Now what I am trying to do is write a SUM formula that will sum M4 to
the column that is being returned in cell L4. The amount showing in L4 is
"t", which is correct. Now, is there a way to pull the "t" into a SUM

=SUM(m4:CELL("contents",L4)4) is what I've tried. Is this close?

What I am trying to do in this case is SUM M4 to T4, with T being a variable
that will change depending on what is returned in cell L4.


I am trying to merge from Excel to Word. When I merge a cell with a currency
amount that was formatted from the format cell to currence, it doesn't keep
the currency formatting. It drops the $ and the decimal. Is there a way to
merge and keep the formatting?

How do I get data automatically entered into a cell when I enter data into an
adjoining cell? I'm doing a payroll project, and I basically want to enter a
name in one cell and have that person's hourly wage data automatically
entered in the next cell without having to type in the dollar amount every

Over the years, this board has been a huge help to me, so I thought I'd
post this link in which I show how to bring stock quotes into a
spreadsheet in an easy and robust way.
(click on the blue W3 circle below the words that say "visit

This is a link to a a simple spreadsheet that allows you to pull stock
quotes in from Yahoo Finance, for a range named 'tickers'. If you
change the tickers, the company names and stock prices will change as
well. This spreadsheet just makes it easier to track a portfolio using
Excel. Virtually all financial quotes tell you the amount that a stock
has changed since the prior close. One benefit of this spreadsheet
approach is that by recording your observation, you can review the
change since the last time you looked at your portfolio, whether that
was an hour or a week ago. This is written in visual basic for excel,
and I've left the source code open. email:

I have a record of salaries in three columns - Empoyee, Date, Amount.

I am trying to use this to establish an annual salary for each pay day,
and Indexing is not working because the pay days are monthly and the
salaries are annual. To make looking up a salary easier, I have moved
the salaries into Year columns, as below:

Employee Date Salary Jan-04 Feb-04 Mar-04 Apr-04 May-04
1000 Jan-04 5000 5000
1000 Feb-04 5200 5200
1000 Mar-04 5400 5400
1000 Apr-04 5600 5600
1000 May-04 5800 5800
1000 Jun-04 6000
1001 Jan-04 2500 2500
1001 Feb-04 2600 2600
1001 Mar-04 2700 2700
1002 Feb-04 4000 4000
1002 Mar-04 4500 4500
1002 Apr-04 4750 4750

I now need a method to move all the salaries of each employee into a
single row.

Thanks in advance.

CharlesF's Profile:
View this thread:

How can i convert A Numerical dollar amount into words ( $1.00 into "one

Hi everyone,

Apologies that the title isn't very descriptive, there are just so many factors that my very basic skills do not even nearly extend to so I really hope someone can help please?

I often receive large spreadsheets of data and have to make summary tables on a new worksheet of this data to give to my boss (so ideally would be formatted nicely so I can give it directly but that is the last of my worries!). We receive these regularly (I have done one each day for the last week) and as my excel skills are slightly more advanced than my colleagues (in terms of I can use vlookup formulas so nothing major!) they generally all get passed on to me!

Ok so what I have to do/need:

Add a new worksheet called "50 GRPs".

As the data needed in the table can change each time, in an ideal world a userform would pop up with all of the headings found checked and the user can deselect what they do not need in the summary table.

For each Name found in the Brand Name column, I need it to go down the column named "Actual Main GRPs" and read the value closest to 50. Once it has found this I need it to read the values from that row and put the values from the columns selected in the userform (along with the column heading) into a table in the new worksheet.
I need it to do this in 50 increments and then take the final value.

I need it to have one table for each name found in the column Brand Name and for the table to be called the Brand Name and also quote the "Main Target".

The column headers won't always be in the same order or have the same name (bar the column headers "Brand Name", "Main Target" and "Actual Main GRPs").

I really hope this makes sense and someone is able to help me out as repeating this over and over is beginning to drive me a little mad! I would REALLY appreciate any help offered. An example of the finished workbook is attached - I have left in my previous workings with v-lookup but obviously the would no longer be formulas and the column headed "Base GRPs" wouldn't be needed.

Many thanks

This is relating to my previous post. I have figured out how to return the
column letter of the column I want to SUM through in a separate cell (cell
L4). Now what I am trying to do is write a SUM formula that will sum M4 to
the column that is being returned in cell L4. The amount showing in L4 is
"t", which is correct. Now, is there a way to pull the "t" into a SUM

=SUM(m4:CELL("contents",L4)4) is what I've tried. Is this close?

What I am trying to do in this case is SUM M4 to T4, with T being a variable
that will change depending on what is returned in cell L4.


No luck finding an answer? You could always try Google.