Free Microsoft Excel 2013 Quick Reference

Download bank statement to a template

Hi,
I would like to download my bank statement directly to a template. I can very easily download from the website to excel, but then I am stuck with a lot of time consuming formatting, sorting, summing, etc.

I notice there are a lot of options from the Data>Get External Data tab. I am thinking this may be where my answer is but I'm not very familiar with these options.

Thanks


Does anyone know how to remove a link to a template ?? I have a script which copies a sheet into a new workbook, unfortunately there is a link to the old workbook. (The old workbook is a template)

As you can see in the script below you have to specify the name of the old workbook to change from ("01.01.04 Week Itineraries v6.xlt"), and then i am linking it to itself (itinerary.xls) to force excel to delete any links...


	VB:
	
ActiveWorkbook.ChangeLink Name:="01.01.04 Week Itineraries v6.xlt", NewName:= _ 
"Itinerary.xls", Type:=xlExcelLinks 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do i get this to work, when the original workbooks name is always different ?

Cheers for your help,

Chuck.

I have looked but cannot see if this has been asked before...

What I need to do is:

If Sheet1 C9 = 0 then all of the cells in Sheet2 B20:B30 = 0 otherwise use the indivdual cell formula

All of the individual cells in the range of B2:B30 have various formulas that look at different values within the sheet, so to get round this for the time being, I have put the forumla below into each cell.

=IF(Parameters!C9=0,0,B21*3)

Is there a way to apply an IF statement to a range a of cells, and if so, what is it.

Thanks in advance!

How do i return to results of an Excel IF statement to a different worksheet

I've downloade my bank statement to excel and I want to sort my purchases etc..

One column is the amount debited, the next is where the purchase was made. I want to sort all purchases made at grocery stores, or gas statiosn and sum the purchases for each. I can use sort and subtotal, but that doesnt work if I filled up at 4 different gas stations unless I add another function to sum all the totals of each. Im sure theres an easier way.

Im thinking something along the lines of conditional formatting, if cell contains "gas" then sum the cells of the associated column. But im not sure how to combine the conditional formatting with the formula.

Idealy, each month I would like to download my statement directly to an excel template that would easily/instantly sort all my purchase and sum them.

I've attached an example of purchases, perhaps someone could edit it and reattach it to the thread.

Thanks!

Hello,

I am trying to write VBA code or formulas that look at column A from a downloaded bank statement, and determine if there is either an invoice or customer number within it. The customer number and invoice number are always 8 digits. Invoice numbers always start with 11. Customer numbers 99% of the time start with 15 or 16. and if it find any of this data, to put it into column B... Customers sometimes include both invoice and customer number...

of course, there could be other date or bank data that has 11 or 15 or 16 like 20111510 (October 15, 2011) or a bank reference number like 15111B35492300 which means nothing to me except that it was sent on 15 11 11 (15 Nov 2011).

Bank details do not always have the customer number or invoice number. We are dealing with international wires, or other deposits.

There is no set way the data is received... sometimes the wire only has the number, sometimes the bank tells us their fees, sometimes the customer includes address, etc...

I have tried to search to see whether or not there is another post I could manipulate, but VBA is not my best suite, and I couldn't figure anything out in excel. Seems to be tooo complicated.

I've currently got a personal finances spreadsheet setup that downloads bank statements and then automatically categorises the balance to e.g. food, drink, travel etc.

The bank statement churns out descriptions in the following form:

'1234 23JAN08 3579 , TESCO METRO 2468

At the moment I have a matrix setup with each category assigned a column, which returns the balance to that category's column if Excel recognises any one of a number of buzzwords.

The below formula is in the supermarkets column. If the text description in D348 contains "Tesco" or "Somerfield" then it will return the balance from E348 otherwise it will return zero.

=-IF(ISNUMBER(SEARCH("SOMERFIELD",D348)),E348,IF(ISNUMBER(SEARCH("TESCO",D348)),E348,0))

It's a very cumbersome way of doing it and I would like to be able to have a much larger list of buzzwords for each category, which is not practical messing around with so many nested IF statements.

Would really appreciate any suggestions as to how I could incorporate a list of buzzwords for all my favourite bars, restaurants, shops etc so that I can automatically analyse my bank statements.

Thanks and apologies for the long post.

I am working in Excel 2003 (for compatibility purposes when switching between 2003 and 2007).

The idea to create a banking report in excel for upload into Sage 50 and post transactions based on the narratives in an excel bank statement.

My initial ideas:

Step one:

Bank statement is pasted into sheet one of the excel workbook (we’ll call ‘the importer’ for the purposes of this example). Using VBA I would like the importer to read each transaction line by line and prompt me to confirm the nature of each transaction (using userbox, combobox, or similar).

The combo box should display the transaction and prompt the user for the following: transaction type (bank payment/supplier payment,), VAT Rate (code), VAT amount, nominal account to post the transaction to (if transaction type is a bank payment), or supplier account (if transaction type is a supplier payment), and whether or not to save these selections for next time the same bank narrative is on the statement.

If the option is chosen to save the selections for next time, I want the importer to save the narrative and the chosen criteria to the questions on a separate tab, “Datatab”. This tab will be used to build a database of bank narratives and the corresponding criteria. The idea being that next time this transaction narrative appears on the bank statement, the importer recognises that this transaction type has previously been processed and uses the previous selections to allocate the transaction to the output sheet.

Which brings me to the 3 desired outputs of the programme:

Output 1 – Excel file containing transactions as confirmed by the user in step one for upload into Sage compatible format. This should also contain the transactions allocated automatically using the historic information on the data tab.

Output 2 – Excel file containing unallocated transactions for manual entry into Sage.

Output 3 – Excel file containing a list of all transactions allocated as supplier payments this month.

What I need to know, initially is this possible and what is the difficulty in creating this workbook. What kinds of functions would it entail from a technical perspective? Is there anything I should look out for, and any other advice would be brilliant!

Thank you.

Hello,

When I download my bank statement as a CSV file it doesn't download in an organized manner. Each cell in a particular column (the "Description" column) is full of information that I would like to organize but it would take forever to do it manually.

Here's a few examples of what I see in the "Description" column:

AMERICAN HONDA FINANCE Bill Payment
CHECKCARD 0302 CHICK-FIL-A #01293 HANOVER MD 10013823794
COSTCO WHSE #0 03/19 #000231162 PURCHASE COSTCO WHSE #0032

As you can see, this info is a bit garbled up. I want to make a program that will clean up unnecessary information and can split the contents into separate columns that show the type of transaction and the store/location of the payment like so:

Bill Payment American Honda Finance
Checkcard Chick-Fil-A
Debit Card Costco

Please help! Thank you.

I've hit a wall in trying to deconstruct this unwieldly Excel statement into a simple VBA function call. However, I keep getting an error whenever I try converting it. It keeps giving me a #NAME error.

The cell in question is a cell that looks to a previous sheet and grabs the date value from the first non-blank cell on the condition that a cell on that sheet is non-blank. The cell has validation on it to look for a date format.

Here's the statement:
{=IF(ISNUMBER(J11),OFFSET('Sheet 1'!A11,MAX(IF(NOT(ISBLANK('Sheet 1'!A11:A45)), ROW('Sheet 1'!A11:A45),0))-ROW('Sheet 1'!A11),0),"")}

Basically, what the statement does is look for the last date in the previous sheet and uses that as the first entry in the current sheet. The trigger is whether or not the cell in J11 has a number in it currently.

Here is the contents of cell J11 on sheet 2
=IF(ISNUMBER('Sheet 1'!K45),'Sheet 1'!K45,"")

I didn't like the way I had to explicity name the sheets for that function and was hoping I could use the parent object to grab the name of the previous sheet with VBA.

If you need me to post the code that I had been using (yech) please say so and I'll post it, but I was hoping there'd be a more elegant way than I had been using. I'm just learning VBA and I would like to see more than one way of solving this problem.

Here's more info:
- The range of A11:A45 are all dates
- The range of J11:J45 are all whole numbers >= 0
- Sheets 1 - 3 are all of the same format but more sheets may be needed later on
- All sheets after the first worksheet should have that overflow function in them in both A11 and J11
- Sheet names should not be explicitly stated (I just want to learn how to use the parent object effectively)

Again, the excel statements work for my purposes. They may not be pretty and I'd like to see another, simpler way of doing it but I can not get it to translate correctly into a simple VBA function.

I need help converting this if statement I created to a "FORMULA" in vba.

This formula is used to subtract unpaid breaks from total hours worked in a day.

For Example:

J5 = 5:00:00am
H5 = 8:30:00am

Formula will conver J5 to pm, then subtrack J5 from H5 = 8.5 hours and then based on the hours worked of 8.5 subtracts 1 hour for an unpaid lunch.

=IF(J5=0, 0, IF((((J5+0.5)-H5)*24)8.5,"OVR","")))))

My VBA code using FORMULA(START,END) results in nothing but errors.

Help!

Hi, I need to point Excel to a templates folder other than the default.
Could someone tell me where I would find this option? Thanks!

Kevin

I am saving a banking statement to a file in xls format and want to be able
ot open the spreadsheet and have the data already loaded. Also, I would like
to keep appending the file that the banking statement is saved to if you have
any ideas on that.

I have an Excel template that I have created and I want to market it. I want to make it available only by download and I want it to only be used on the computer that downloads it. I know this is possible with applications but I want to do this with the template. How can this copy protection be applied to a template?

Hi everyone,

Just joined this forum. Wanting to say hello, and see if someone can direct me to a template the allows add on interest. (What I am calling it)

Now what I am referring to is I have financed something for a company and they have been slow pay. I need a template that will allow me to adjust the interest each time the party is late with a payment. I may not be referring to the sheet with the proper term. (I don't know the question to ask the FAQ)

Need payment due date, payment date, that will adjust the days and charge interest from the last principle amount. This will add the interest to the principle, then correct the interest charge if still late (more days) then add this to the principle. When a payment is made adjust the amount paid by the interest due and subtract this, and if any left over, reduce the principle.

I hope I have explained this well enough to have it understood.

Thanks in advance for any help on this.

:D

Hi,

I have a formula with two IF statements and would like to add a third and fourth i.e.

=IF(F10="","",IF(J10<=F10*0.1,"Warning 10% or less Budget Remains!",

IF(J10<=F10*0.2," Warning 20% or less Budget Remains!","")))

The first IF in the formula checks the value in cell J10 and if it is less or equal to10% than the value F10 a message will be displayed i.e. ‘Warning 10% or less Budget Remains!’.

The second IF in the formula checks the value in cell J10 and if it is less or equal to 20% than the value F10 a message will be displayed i.e. ‘Warning 20% or less Budget Remains!’.

What I would like for the third IF statement to check if the value in cell J10 and if it is equal to F10 a message will be displayed i.e. ‘No Budget Remains!’.

What I would like for the fourth IF statement to check if the value in cell J10 and if it is greater than F10 a message will be displayed i.e. ‘You have Exceeded Your Budget!’.

Any help would be great appreciated as I’ve tried and cannot get the syntax correct

Many thanks in advance

Rob

NB Excel version 2003

Hi,

I want to extract specific entries from the bank statement to copy them and paste on a separate excel sheet. How can i do this? This I need to do for a specific bill payments has been made. Thanks for your kindly effort.

Hi All,
I have just tested a hyperlink to an Excel template and instead of opening
the file as a normal spreadsheet it opens as a template (xlt). The idea
behind the hyperlink was so that if you hit the hyperlink you will get a
fresh copy of the spreadsheet, not the template itself. Has anyone
experienced this problem and knows of a fix. Running XP Professional and
office 2003.

Thanks
Murray

In Excel 2003, the documentation for Worksheets.Add suggests that I can use
the Type parameter to specify a path to a template for an added worksheet.
This works with Sheets.Add, but produces a 1004 run-time error with
Worksheets.Add
Although the workaround is simple, the on-line help ought to be changed.

The following code will demonstrate the error in Excel 97, 2000, 2002 and
2003:

Sub NewSheet()
'This statement produces Run-time error 1004, Method 'Add' of object
'Sheets' failed
Worksheets.Add Type:="C:VBATempSheet1.xlt"

Sheets.Add Type:="C:VBATempSheet1.xlt" 'This statement works
End Sub

Hi everyone

I have an excel issue that I really hope you can help me with. I have 2 kinds of templates that I need to combine in some way. I have a lot of different files in excel, all made out of the same template. I then need to be able to choose one of them and insert them in a new template only using some of the cells from the original file.

So:

I have 100 files of one kind. They are all similar but with different values.

I have a template where I need some of the cells from one of the 100 files to be put into.

I hope it makes sense

Best regards

Mads

Hi,
I am reading data from a row in a excel and need to write it to a preexisting template file at specified points/lines. Would this be possible as I am not very familiar with VB.

Simplistically, let's say i read data from the columns (A-F) of row 1:

A B C D E F
x y z 1 2 3

and need to write x,y,z,1,2,3 to specified lines of a text file after doing some formatting to it (preceding x,y,z with certain characters)

File:
[__Header__]
ProductName = "lala "
ProductVersion = 1.1
Version = 120
Type = "type"

Path="C:Pathnamexy.txt"

data
data

%NAME = "x"

data
data

Duration=2

etc.

Many Thanks

I am having trouble creating a template that includes a BeforeSave. The code I have is below, including the "Rem" which allowed me to save to a template.

The problem is I need I3, I5 & I7 blank when the template is opened and I need the spreadsheet saved to a new name after being completed and want to ensure these cells are completed.

But I can't save with the code in effect (removing Rem) and keep the cells empty, I'm sure the solution is simple but my limited knowledge fails me on this.

Rem Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheet1.Range("I3").Value = "" Then
Cancel = True
MsgBox "Please complete Ref"
End If

If Sheet1.Range("I5").Value = "" Then
Cancel = True
MsgBox "Please complete - To be Banked"
End If

If Sheet1.Range("I7").Value = "" Then
Cancel = True
MsgBox "Please complete Dated"
End If
End Sub

I am trying to find an easier way to match a bank statement with a general ledger detail report in order to make sure that the checks balance and clear. Basically I could put information on 1 sheet like the following:

Column A and B are from bank Statement
Col A = Check Number
Col B = Check Amount

Column C and D are from General ledger
Col C = Check Number
Col D = Check Amount

I would like to make it so that they would match side by side and if they did not it would leave a blank space next to it. I am a little familiar with ecel but this formula is a little above my head. Any help would be appreciated.

Thanks

Hello,

I'm have an excel spreadsheet that contains about 200 columnss of information. Column A contains the name of each deal. I want to be able to click the deal and have the information from the 200 columns flow to a new tab that fills out a template. Is this a possibility? All the information would be on the original page, but it would just be condensed and presently differently on the new tab, almost like a pivot table but less work for the user I would be sharing this with.

Let me know if anyone has any ideas. Thanks.

My Master sheet has dates in column C and income/expenditure etc, in subsequent columns. I wish each cell in say column G "telecoms" to lookup its corresponding date in column C, and compare/lookup/match/if; bank! statement column A date, as the first criteria. if match is found then look for "telecoms" in a second column if true, express corresponding value in bank! column G. if any condition is not met then leave blank.
PREFERENCES:
1: It would be desirable if the formula didn't care whether my bank!statement was a list/sorted or not so the values always remain correct however you sort the Bank! sheet!
2: If this could be applied as a conditional format for the column?
3: Could you also let me Know where in the formula to put a third criteria
4: If a cell in bank! has been referenced then fill cell and fill cell in mastersheet!

It would be gratefully appreciated if you could extend the formula step by step so i can understand whats going on to help in future formula contruction

MANY THANKS.