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?
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.
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.
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)
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
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
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.
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)
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.
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?
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?
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
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?
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.
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.
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.
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.
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.
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.
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?", _ ......................................
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.
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.
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
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).
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"
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.
