I have a loads of folders with different account names on my c: drive. What I want to do is put in part of the name rather than al ofl i.e. Manchester United, would just put in Manchester I have seen the * used but was not sure how this works
View 2 RepliesI've been working with ledgers to analyze companies financials in detail mostly using pivot tables and charts.
I usually get the data in a format that has the account name and number as a title on top and then each entry for this account under the title. In order to get my pivots working I need to copy the account name and number to each row until the next title.
Here is a picture to understand what I mean: Makrokyssäri.png
So is there a formula or a macro that could avoid doing the copying manually?
I want to list file names from a directory and sub directories and hyperlink them. I have been able to list the file names onto an excel sheet, but I cannot get it to hyperlink to the file.
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
[Code]....
Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)
[Code] .........
I receive a spreadsheet with over 400 lines of data every month. One row (J) contains information from companies with account details. The problem is that the info uses different formats, so the data strings within the cells can be quite long (sometimes over 100 characters, including spaces, dashes and slashes). The one identifier within each data string is a name, usually somewhere in the middle.
So for example a reference to JOHN SMITH might be "123456-01-smith/john-01/01/10-London-Edinburgh" or "2010-01-01-123456/SMITHJOHNMR/123456". I have a list of names (Column Y) and what I need is some way (probably VBA) for Excel to look down the list of references and compare them to the list of names, and if one of the names appears anywhere with in a text string, to put the name in column K. So if 4546/JohnSmith-01/08/13 or Leeds-London-123SmithJohn789 appears, put John Smith in column K, if 0123-MrFredBlogs-0321-5 appears, put Fred Bolggs in column K.
I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?
View 9 Replies View Relatedto write a macro to put the background color where a certain figure sum is zero. Eg. Column A contain as
5
-5.25
12
-5
-12
If one select A1 to A5 and run the macro the color for all cell should be green except for A2. Infact the macro will point out which amount is not deposited into the bank. (All +tives are for Deposits and all –tives are for sales)
I am busy writing projects and the macros that I write reference external files (no surprise there). Normally, all referenced files are on the company's main G: drive but recently the company have started the process of re-appraising all access authorities for the folders in the G: drive. This has necessitated that they create a new drive (called J:) and folders and users are being migrated over there as they have been cleaned and approved.
All of this means that some of my users reference J: and some G: and my macros are intermittently falling over because of it. For each user, all the files that they will need will be on either the J: or the G: drive, but not both.
All of the above leads me to ask the following question... How can I build into the macros a "Check what drive you're on and use that letter" line of code
Attached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
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.
I’m trying to get the difference between times, BUT I want to account for “negative” time. So if I project that an employee will finish a task at 2 am, and they finished at 2:30 am, I want the result to be -:30, not just :30. If they finished at 1:30 then it would just be :30. Hence, my dilemma. I can get the difference, but didn't think Excel could recognize negative times. Right now I am using =MOD((A1-A2),1) or =(A1-A2+(A1<A2))*24/24 and in cells A1+A2 I am using military time.
View 2 Replies View RelatedI need to produce a grid displaying data pulled from local references within the workbook but Excel will not auto fill the references in the right way.
i need
='Account (1)'!$C$5
='Account (2)'!$C$5
='Account (3)'!$C$5
down to
='Account (498)'!$C$5
='Account (499)'!$C$5
='Account (500)'!$C$5
is there a way to imput these references using VBA script?
If you Have a list of account numbers and amounts, is there a way in one forumla to get the average per account?
View 9 Replies View RelatedThe expense account we work with involves several currencies due to the international nature of the business. With that, each row must show the currency involved and the formula used from one line to the next makes it repeat the currency until changed i.e. if cell B22 has a date entered then Cell H22 will reflect the currency from Cell H21 =IF(B22="","",H21). If Cell H21 showed CDA for Canadian currency then H22 would also become CDA. Once changed manually then all cells below will now reflect the new currency until changed again.
Using the lookup function we collect the individual amounts of each currency and run totals at the bottom. Therefore, if there were three entries in SGD (Singapore Dollars) and two entries in EUR (European Euros) etc. that each row will do a lookup by the three letter currency code and collect the sum of each currency. SGD could repeat later again and when changed manually will be included in the lookup.
At the present we are entering the three digit codes manually i.e. SGD. What we would like to do is automatically have the bottom be able to pickup the currency change when a new currency is entered on the individual rows on the top part of the expense sheet. So if Cells H21:H23 were SGD and Cells H24:H25 were ERU that Cell A58 would show SGD and cell A59 would somehow be able to grab the ERU etc. One of the same currencies may repeat itself and the lookup will capture the additional totals but SGD would only show once.
I am working with percentages, and trying to avoid #DIV/0! Errors at the same time.
Lets assume column A holds last years figures, and column B holds this years figures, column C calculates the difference in percentages.
I am having dificulty where say A1 = 0 B1 = 10 (C1 Should be 100%)
then what happens if A2 = 5 and B2 = 0 (C2 Should be - 100%)
Is there a common formula that will account for all possible + or - figures in A and/or B that will also suppress #DIV/0! Errors?
I have agreed to create a simple accounts system for a friend of my mothers. I have no problem when it comes to manipulating cells to interact ( sums) and then creating an aesthetically pleasing spreadsheet. However, as with all things excel, there is an easier way to doing something and a person usually doesn't realise that till the end.
I was hoping that somebody might be able to direct me to a resource that would be ideal for constructing a 12 month revenue and expenses account. I just want it to be as simple as possible for the lady that I am doing this for (all I want her to do is enter how much she made for one day and how much she spent), and everything around this I can make interact.
I've created a simple budget sheet outlining various group expenses eg: Fixed, Flexible, Variable, and their totals sums.
How can I add all totals while still allowing for a new row to be inserted in any group?
eg:..............Row E
1........Fixed Expense group
2..................$20.00
3..................$40.00
4..................$40.00
5....Total sum $100.00
6.......Flexible Expenses group
7...................$10.00
8...................$20.00
9...................$20.00
10..................$10.00
11..................$50.00
12...Total sum $110.00
13......Variable expenses group
14..................$10.00
15..................$10.00
16..................$10.00
17..................$20.00
18..................$10.00
19....Total sum $60.00
20--------------------
21..Grand Total $270.00
Currently I am using this ......
View 9 Replies View RelatedI use the Hyperlink to open PDF files that are on the CD drive from my spreadsheet. I am on Windows XP and the CD drive is the D drive. In Vista the CD drive is the E drive.
When I take the spreadsheet to a Vista machine the link fails as there is no D drive.
Is there any to code the path of the Hyperlink so it will go to th default CD drive for that computer.
I am trying to find the revenue generated by different groups of accounts to figure out which group is worth the most, and rank the others.Here is a attached screenshot of what I am working with, cell BK22 is the revenue of all of the accounts coded AADA but i need to find an easier way other than plugging it in for all 3500 cells.
View 6 Replies View RelatedI currently have account numbers formatted 1.234.5678 and need to convert them all to 1234.5678. how this can be done without manually keying 22,000 account numbers?
View 3 Replies View RelatedABaccountamount60000-502,503.4060000-601,143.4860000-70847.7260000-80282.1860000-85238
.1870000-50156.8560000-89200.0080000-80300.0090000-70400.0070000-6020.00
i need formula that will sum column "B" if the last two digits of column "A" = 50,60 or 70.
I have account numbers in Col F and values H on sheet1
I want to set up a formula on sheet account classification to add the values in Col H based on a range of account numbers from 3000 to 3017.
I receive a large text file each month from a vendor, This file contains account numbers, account descriptions and costs associated with the previous months period. There are 5 separate business units included in this report. I am looking for a way to translate the account numbers pprovided by the vendor to fit with in the account numbers we use. A Vlookup will work by creating a Map that translates their numbers to match ours, but this would need to be copied in monthly and as there are 15k rows or more it will likely take a while to process. I am wondering if others have attempted this and have advise as to what works best.
My plan was to create an Excel file that reflects the account numbers provided with our account numbers and then use a VLookup to match the data received with this master file as it were. ( So I would have a file called AccountMap which contains 4 columns, Cols A & B is the info provided by the vendor and Cols C & D are our codes and descriptions ). Then when the data arrives I would enter my vLookup and go from there.
Presently the steps are:
Open Vendor Text File in Excel
Copy a Business Unit and related Data to new WorkSheet
Insert Vlookup to map the vendor accounts to our accounting system format
Rearrange the data to meet our import requirements
Export the modified range as a Text file
Import into Accounting system
We have a form that is completed by our employees and emailed to a central group email address that up to 8 employees within our team can access. When one of our 8 team members open the file sent they need to add comments to it and then save it in a central directory on one of our network drives.
Now the problem I have is that we all have the network drive mapped to a different letter.
Is there a way that I can have a macro (see below for current macro that does not work) save to a network path and not a drive letter.
I have a an excel workbook that is distributed on a flash drive. Currently a Save As Macro copies "A1" as the file name and then opens a screen to allows the user to save that filename to a location that the user selects. What I would like to have happen is for a command button to start a macro that would automatically save the file back to the flash drive that the master workbook is located.
View 3 Replies View RelatedI have a program that relies on a remote drive being open and ready. My current tactic (which works perfectly well) is to have a file in the destination drive and ask the date that file was created. If an error occurs I know the destination drive is not ready.
Code:
On Error Resume Next
temp = FileDateTime(destDrive & "xDeletedStuff" & "testFile.txt")
If Err Then
MsgBox "Destination Drive / Folder Not Ready"
Exit Sub
End If
On Error GoTo 0
Idealy I would rather not use the "testFile" I would prefer to just test to see if the folder existed, "destDrive & "xDeletedStuff"
I have part of the code below and need to modify it so that others who only have C: instead of D: to use it
Filename = "D:TempBudgetMonthly.xls"
FileExists = (Dir(Filename) "")
If FileExists = False Then
msg = "The data file Monthly.xls does not exist !! Load the required file and run the report again !"
If MsgBox(msg, vbExclamation + vbOKCancel) = vbOK Then
End If
Exit Sub
End If
I need code to replace all D: to C: for the entire PROJECT not MODULE.
I am trying to get a excel to list all the file names in a drive along with date last modified, author and file size. I have got the following bit of code to pull back the file names, but i am struggling to find anything that will pull back the other data, I think its something to do with the DIR function, but im not sure........
Range("B1").Select
D = ActiveCell
Cells(2, 1) = "Filenames"
r = 3
f = Dir(D, 7)
Do While f ""
Cells(r, 1) = f
r = r + 1
f = Dir
Loop
End Sub
where "B1" is the file path of the drive
I want to create and add-in that will allow a user to select a file (using the in built dialog boxes), the file will then be copied across the network to another server to which the user doesn't have access - I do have access so would hard code in the appropriate username and password in to the script.
The way I had done this before was to look through the users mapped drive to find a free one, map the network drive connecting as myself, save the file and then disconnect the drive.
I am able to map and disconnect the drive, but can't work out how to find an empty drive to use (thus preventing me from overwriting any mappings they may already have).