Temporarily Hide Transactions In Bank Statement?
Feb 3, 2014
I have downloaded some of my bank statements in excel format but they are just static data - ie, they are just numbers in boxes and the BALANCE column does not react when I take out a transaction.
I have put in a formula for the BALANCE column so it does now take its value from the previous day plus or minus transactions, but now I want to do additional things.
- How would I, for example, categorise several transactions as "HOLIDAY" [URL] ....... and then temporarily make them disappear so that I can see the effect of that on my balance? I can see how to hide/unhide transactions but that doesn't actually seem to have any impact on the balance column.
- Second query: how do I make my current spreadsheet a template so that when I download the next bunch of bank statements I can just apply all the formulae in this one to it?
View 1 Replies
ADVERTISEMENT
Jun 1, 2014
I'm trying to group a year's worth of bank transactions. The initial data that was cut from pdf files is a date, payee and amount
1) how can I search down col A and give the sum of all like Payees, then total each set of similar payees? Maybe if first 6 characters match, then total until it comes to a different set.
Total each set.
2) then, I need to assign a category to each set of payees, so if contains usps, then add category "postage"
3) formula to find all postage totals and combine for a grand total per category.
usps15.23postage
usps14.32postage
usps5.23postage
fedex5.25postage
fedex10.22postage
shell45.28fuel
shell99.38fuel
qt27.38fuel
qt44.88fuel
View 7 Replies
View Related
Jul 11, 2006
I have a workbook that opens full screen without any menus including the Worsheet Menu Bar. When the sheet opens the Title bars and Workshheet Menu Bars are visible
at first and then the sheet jumps to full screen without either. I tried to prevent seeing this screen jump by surrounding my code for hiding
all menus & viewing full screen with-
Application. ScreenUpdating= False
Application.ScreenUpdating=True
This works in other sections of code as intented but has no effect here. Even though I realize this has no functional benefit, because I am already able
to use the entire screen, I would like to find a way to hide this screen jump.
View 8 Replies
View Related
Oct 12, 2013
So I want to copy my bank statement into excel, but when I do, rather than separating each line into a row of separate cells, it separate it into a column of separate cells. and it does this for each row in the same column.
So:
A1 is in the right spot
A2 should be B1
A3 should be C1
A4 should be A2
A5 should be B2
A6 should be C2
etc....
Any way I can sort this out at the touch of a button? (or a few buttons)
View 3 Replies
View Related
Nov 5, 2009
Sheet6
RSTUVWX5Your PointsPoints Needed To Level UpCurrent LevelYour CreditWithdrawalDepositCredit67
80You Need 18,000 Pts.IntermediateYour Credit is 0 Pts
911,5006,500BeginnerYour Credit is 0 Pts25004000
1010,4547,546BeginnerYour Credit is 454 Pts
45411125005,500BeginnerYour Credit is 1000 Pts200035001000
Spreadsheet FormulasCellFormulaS8=IF($AB$6$Q$23,"You Are Credited With "&TEXT(X8-$Q$23,"#,##0")&" Pts","Your Credit is 0 Pts")
Excel tables to the web >> Excel Jeanie HTML 4
i have a "Game Points Bank Account" that keeps track of the points users earn or spend in the game...what i need help is creating a running total for the bank..ive tried everything nothing works..
(this is actually like a traditional bank account)
each player starts with 10,000 points
*Row 8 is the only row to have formulas*
*Rows 9-11 are typed in to show my desired results*
Here is the point value "Ranking System" for Column T
Sheet6
AGAH5Tier 16Beginner180007Beginner360008Beginner540009Intermediate7200010Intermediate9000011Intermediate10800012 13Tier 214Advanced12600015Advanced14400016Advanced16200017Expert18000018Expert19800019Expert21600020 21Tier 322Elite23400023Elite25200024Elite27000025Superior28800026Superior30600027Superior325000
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Aug 22, 2009
for statement to hide rows VB. In VB, how would the following look:
View 2 Replies
View Related
Jun 23, 2013
I'm attempting to put code in that will hide 2 individual columns when a particular cell in another column has no value in it. The cell, however, has a formula in it, and I'm not sure if that is my issue.
Essentially, if the formula in A32 returns a value, I need columns M and P to Hide. If it doesn't return a value, I need them to stay put. The value returning in A32 is a number, but it can be treated as text as it is an ID number and is not needed for any calculation.
Here's how I have it set up now:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A32") "" Then
Columns("M","P").EntireColumn.Hidden = True
Else
Columns("M","P").EntireColumn.Hidden = False
End If
End Sub
It's not working - nothing is happening.
View 5 Replies
View Related
Oct 29, 2009
Is there a way to, on a temp basis to remove the read only feature when enabled and then re-enable it after a VBA routine is excuted?
View 10 Replies
View Related
Apr 29, 2014
I've set up a method to register users so they can use my applications. Very simple, First Name/Last Name/Scan your ID card. Unfortunately, that is apparently not "idiot-proofed" enough for some of my users. They keep entering their full name into the first name section, then they'll repeat the same information (full name) in the last name section. I swear they're doing this kind of stuff just to annoy me and see if they can break what I build.
Anyway, is there a way to change a single cell so that if the user hits the spacebar, it will simply tab to the next cell.
View 6 Replies
View Related
Dec 7, 2006
I want to temporarily display a userform (maybe for 4seconds) when my spreadsheet loads.
I cannot grasp the ontime function, and from what I can tell most messages posted on here relate to using it for intervals, or to be used at a set time in the day. (I have looked at the Helpsheet for the Ontime function, but struggle to apply what is being said to my situation)
View 3 Replies
View Related
Jan 18, 2013
I have a vba function linked to conditional formatting that i want to temporarily stop while a sub to insert a line is running as the sub stops when it hits the sheet where the vba function operates. When i disable the vba function (or remove the vba function) the sub works fine, if the vba function is not disabled the sub stops.
View 4 Replies
View Related
Dec 14, 2011
I have a Sheet Activate code so that when going through a workbook, range a1 is always selected.
However, i have another macro, which navigates through each monthly sheet in the workbook, and uses cell a19 as a 'topleft' const cell, with a time delay, so that i can view a chart in the same place for each month on each monthly worksheet, kind of like a journey through time.
The problem i have, is that i need the sheet activate functionality when normally navigating through the sheet, but this overrides my macro which needs to open sheets with cell a19 as the top left cell.
So my question is, can you disable a sheet activate sub at the beginning of a macro, and activate it again at the end?
View 3 Replies
View Related
Jul 12, 2007
How do disable Private Sub Macro
I have a private sub macro for Sheet1 as shown below
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$2$" Then Sheets("Start").Select
Exit Sub
End Sub
And I have another macro (call ADDNumLine) that add additional data to the Sheet1. How do I temporally disable the Private Sub above when executing Macro AddNumLine?
View 9 Replies
View Related
Jan 11, 2007
I have a set of procedures that require auto recalc to be on to work correctly. I've tried application.volatile and Application.CalculateFull with no luck. I'm trying to put together code that makes sure recalc is on, but that first determines the current recalc status and then changes it back to Manual when necessary. I don't know how to determine the current status. The following code should work if the red sections are fixed.
Sub TempAuto()
Dim CurrentState As unknown
CurrentState = Application.Calculation status
Application.Calculation = xlAutomatic
If CurrentState = Manual Then
Application.Calculation = xlManual
End If
End Sub
View 8 Replies
View Related
Jun 14, 2008
I would like to run a macro on a worksheet that changes some cell values. Some of these cells have conditional formatting applied. With this particular macro I do not want Excel's Conditional formatting to react to these changes. How can I temporarily switch off Excel’s Conditional Formatting with VBA before I run the macro so that it doesn't react to these changes? What exactly triggers conditional Formatting to re-calculate?
View 2 Replies
View Related
Nov 9, 2011
How to temporarily disable PowerPoint charts which are linked to Excel. The problem is, whenever I copy a slide and try to paste it elsewhere, Office attempts to update all of the links in the entire PowerPoint. Since the slide deck has so many links, this takes an awful lot of time to do. I don't want to break the links completely as I would lose any ability to keep them dynamic. I've checked all of the advanced options within PowerPoint, but cannot find any way to temporarily disable links from updating.
View 1 Replies
View Related
Oct 25, 2011
I have a simple macro that cycles through the sheets in a workbook, and if the sheet's codename matches one of a defined list, some of its data is added to a summary sheet.
The macro works exactly as intended, but a strange thing happened yesterday: some data was missing from the summary sheet because one of the sheets was being ignored. This sheet is named 'MCP' on its tab, and has codename Sheet8.
Here's the strange part: on stepping through the code, cycling the sheets, I noticed that the sheet icon, name and codename had disappeared from the Microsoft Excel Objects folder in the Project Explorer. When the loop got to the sheet in question
Code:
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Debug.Print ws.Codename
The above code displayed its name (correctly) as 'MCP', but its codename was blank; the sheet was therefore skipped by the code because the codename had to match against a defined list.
While I was pondering this, and doing some web searches, the sheet then re-appeared in the Project Explorer and everything worked again.
My question is this: is it likely to be due to the workbook being shared? I know that workbook sharing in Excel is often discouraged, but this is a simple workbook only used by a maximum of three users (two of these had the workbook open at the time the issue was reported)
I've changed the code so that the sheet name is inspected in the event that the codename is blank, which should guard against the issue provided sheets are not deleted/renamed.
Windows XP, Excel 2003
View 4 Replies
View Related
Mar 25, 2014
I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.
Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.
View 1 Replies
View Related
Feb 16, 2008
I need to sort all my pay pal transactions I need all my debits in one row and credit in another.
View 14 Replies
View Related
May 11, 2009
I need to have a running counter of transactions within an account.
Solved with: C2=COUNTIF($B$4:B4,B4)
(and copy down)
Example:
ACCTNO AMT Trans#
100125.001
100121.002
100122.50 3
1001 2.00 4
1001 5.00 5
100127.00 6
1013 .50 1
1013 2.50 2
1013 13.00 3
I need to solve for the Trans# I've included it here for clarity, but I need to be able to get that number based on the ACCTNO. Notice the Counter Resets to 1 when the ACCTNO changes from 1001 to 1013.
View 6 Replies
View Related
Sep 3, 2009
I have a group of users in cell C1 and i wanted to count how many times they have process a payment as long as its value in Cell D1 is more then or equal to 1.
I tried sumif, but its totalling the amount. but i wanted is the number of transaction.
View 9 Replies
View Related
May 2, 2006
I found a link to a website on one of the forum pages. I had a look in the website and it showed a formula for calculating when Easter falls - I didn't know it could be worked out, but it can!!
I therefore decided to investigate further. I picked up another formula to calculate the first MOnday in May and I have now put together a little spreadsheet that will calculate all bank holidays in the year entered in cell B1. It also takes into account additional bank holidays that exist when Christmas Day and/or Boxing Day fall on a weekend.
View 3 Replies
View Related
Dec 1, 2006
I'm an amateur to macro as I'm only in a low level class at a university... But I'm attempting to make a macro for a bank interest calculator. It asks your type of account(which then assigns an interest rate to it), how much money is in the account, and also how long th emoney will be in the account. I used a "Select Case" for the account types, but I seem to be struggling for it to work, it won't put the value of the total into the assigned cell, or it's just not computing it(as I get "0" each time I run it)...
Public Function BankCalculator()
'Bank Calculator for different accounts
'declare variable
Dim shtBank As Workbook, strAct As String, intMon As Integer, strLong As Integer, intTotal As Double
Set shtBank = Application.Workbooks("Bank Calculator.xls")
'input box for amount of money, assign address
intMon = InputBox(prompt:="How much money do you currently have in the account?", _ ......................................
View 4 Replies
View Related
May 12, 2014
I would like to know the easiest way to temporarily keep a worksheet code from running while I am editing, then turn it back on when I am done. I was thinking a button with these commands(?)>
Application.ScreenUpdating = False
Application.EnableEvents = True
but I don't know which button to use, or if I would need a button for each.
View 2 Replies
View Related
Jan 25, 2014
I need to compare in and out of money in multiple bank accounts.
Imagine in row one i have all the "INs"
and in row two i have all the "OUTs"
Now, how do i compare say first transaction in row one to say 5 transactions in row 2 and find the relationship
It can be:
1. Transaction IN 1 = Transaction OUT 3
2. Transaction IN 1 = Transaction OUT 2+4
3. Transaction IN 1 = Transaction OUT 3, or Transaction OUT 2+4
So if its a direct relation it just displays where they are equal, if they aren't how it will display which multiple transactions will be equal, and if there are 2 different possibilities it will show both answers.
If its only In = Out its pretty straight forward, but how do i code it to search for combinations of transactions say 1+2+5 efficiently.
View 5 Replies
View Related
Mar 19, 2014
Solution to keep track of bank transaction in a budget - both in and out
With:
=SUMPRODUCT(--(Table[[CAT]:[CAT]]=$C5);--(Month(Table[[DATE]:[DATE]])=G$3);--(Table[[IN]:[IN]]))
Table1:
DATE | CAT | IN | OUT
----------------------
The transaction CATegory and Date place the amount from table - column IN to the corresponding cell in my budget.
I would like my bank account to reflect both income an expenses from 2 columns in Table: IN and OUT
Workbook example: [URL] ...........
View 3 Replies
View Related
Sep 7, 2009
1.1 In columns N and O, color the numbers in both the N and O cells green if, and only if, (a)the N cell's number is greater than O's, AND (b) both N and O cells' values are greater than the preceeding N and O cells' (i.e. a great value than one row higher in the column).
1.2 In columns N and O, color the numbers in both the N and O cells red if, and only if, (a)the N cell's number is less than O's, AND (b) both N and O cells' values are lesser than the preceeding N and O cells'(i.e. lesser values than one row higher in the column).
View 4 Replies
View Related
Oct 13, 2009
I have approximately 40 seperate sheets in one workbook. Each sheet is a unique part #. Each part has 6 different types of transactions possible. Let's say A-F. A-F each have a date associated when them of when the transaction occured. The transactions are sorted by date. I would like to write a formula that when Transaction A occurs what is the diffence in days until D transaction occurs. Or the time differnce between when B occured and the next F occured.
below is my datedif formula, but it obviously only works in a sequential order from top to bottom.
=IF(DATEDIF(M5,M6,"y")=0,"",DATEDIF(M52,M6,"y")&" years ")&IF(DATEDIF(M5,M6,"ym")=0,"",DATEDIF(M5,M6,"ym")&" months ")&DATEDIF(M5,M6,"md")&" days"
View 12 Replies
View Related
Aug 23, 2013
I have a spreadsheet with detail info for transactions. There are multiple columns...but these are the ones I'm concerned with. ie
Cell A4 has the date range ( 1 month) ie "04/01/2013 - 04/30/2013"
below starts on B5
Cell B - Cell D - Cell I
vendor - Qty - Profit
H20 Month $50 2 7.00
H20 $30 Mo Unl T&T 2 4.20
H20 Month $60 21 88.20
Page Plus Unl $55 3 22.29
PagePlus Unl $39.95 6 32.34
etc...
Cell A32 has the date range ( 1 month) ie "05/01/2013 - 05/312013"
and the vendor, qty and profit like above again....
The above
To the right of this I have :
Cell L2 Cell M2 Cell N2 Cell O2 Cell P2 Cell Q2 Cell R2
Company - April - Topup profit - May Topup Profit - June - Topup profit
H20 29 110.60 71 261.10 93 342.95
PagePlus 19 55.05 25 106.72 14 44.70
etc...
at the bottom, I have the totals for each cell from M2 thru R2.
How can i get L2 thru R2 to sum up the detail amounts on Column I by Column B ?
View 1 Replies
View Related
Feb 20, 2014
I want to SUM monetary transactions based on two criteria.
1.) if the transaction occurred within a certain month (Jan)
2.) based on the transactions category ("Obligations")
I have two formulas which successfully validate the data individually but I need to combine them so that both criteria must be met before data is summed.
=SUMIF(REGISTER!E3:E1000,"Obligations",REGISTER!G3:G1000)
=SUM((IF(MONTH(REGISTER!$B$3:$B$1000)=1,REGISTER!$G$3:$G$1000,0)))
View 9 Replies
View Related
