The formatting is set to general, which works for all but the top type of account number. I tried setting to a custom format like "#.#######" ...The only problem with that format is that the "." shows up whether there is a decimal in the number or not. What's the way around that?
I 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?
I have a list of numbers which I need to compare to a range of numbers. Each number in the list needs to be compared to the range, and depending where it falls within the range, a particular cell needs to be referenced in the result. I've attached the xls with the explanation.
I was going to do this with a nested IF, but with a max of 7 this will not work.
Attached File : Help with comparing to a range.xlsx
I want to show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?
I am close to finishing making up an Excel tool that takes data input from the user, does some analysis, accepts further input then prepares a report. My coding skills are not what you would call high level, so my code could probably be more efficient, but it works (well, except for this issue).
The tool opens a main multipage userform for data entry, then allows the user to view the data in the spreadsheet tables with the ability to call separate userforms to modify, delete or add records. The user then calls another userform to do some calculations and enter the appropriate results from a separate piece of software, then presses a button to prepare a report. All userforms are called from buttons in a separate userform (I'm not proficient enough and haven't had enough time to create a new ribbon in excel). At various times, messageboxes are used to convey information or prompt the user to do something - these are all simple ones with either only OK button, or OK and cancel buttons.
My problem is that when userforms are called, they sometimes appear but are totally blank (white) - see image.
blank_form.jpg
Clicking anywhere on the screen or pressing any key will bring them up properly and they seem to function normally thereafter.
In addition, the messageboxes sometimes do not appear - pressing any key will make them do so. Other than this they function normally.
The frustrating thing is that these problems occur inconsistently. I can add or change some code and it stops happening, then after testing (entering data and using the tool) starts happening again. Sometimes it just stops happening with no apparent reason, but starts again later after fixing some code or just entering some data. Not all userforms are affected at any one time and not all messageboxes have the problem at any one time.
The problem seems to occur in those subroutines where I access or manipulate data from other sheets before showing the userform or message box (but in most instances, I need to do this manipulation in order to present the correct information in the userform).
Some thought processes I have had, and unsuccessfully tried to fix the problem with (some coming from various web forums):
I thought I may have done "Application.ScreenUpdating = False" without resetting it to true, so I commented out all the "Application.ScreenUpdating = False" statements - problem stopped for a bit then started again.
Some of the userforms were modal, and even though they were not showing were still loaded so thought that this may be impacting on other userforms/message boxes, so changed all userforms to non modal - still had the problem when I opened the tool again.
Tried using "RePaint" and "DoEvents" at various points in the code after opening a userform or message box - no change.
I have tried exporting a form and its code, removing from the tool, then re-importing it. No success.
Tried the tool on another computer and the same problem occurred.
Lets say cells A1 to A5 contain these lines of information:
Till 174 (T0215) - till keeps turning itself off. Till 245 - stuck on windows screen 116 - keyboard is unresponsive Berkel Scale is constantly beeping ped not reading cards Till 156
How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.
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
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)
I'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 am adding shapes with text (msoShapeOval) to an image on a worksheet but I cant get the size of the "ShapeRange" to work dependant on the value assigned to "sTxtlen".
I had msgbox's within each Case but none were triggered allthough using another msgbox to show the length of sTxtLen is correct prior to the Select Case.
I have a list of account numbers with stock attached to each account. I want to show all account numbers that do not have any one of 5 items attached to it. See my attached (simplified) example, show all accounts that dont have Bananas or Oranges, account number 222 and 444 would be displayed.
I have a large workbook that contains hundreds of account codes and values. For example, cell A2 contains the account code "55021357122 Global Business Sales", cell A3 contains code "55021753221 Global Business Purchases" and so on through the column. Column B contains the values.
I need a formula that will sum all the values in column B if the middle 2 number are "21" (positions 4 & 5 in the number). My current range for column A is A6:A366. I've tried SumIFs but that fails on the array because it fails on the MID function.
I would like a formula to add up values in Col E based on a range of account numbers in Col C
For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.
I have account numbers in column A where the customers have purchased at least 1 accessory, where the accessory numbers are in column B. If they have purchased more than 1, their account number will be duplicated in column A. I would like to represent the data so that the account number is unique in column A, accessory 1 in column B, accessory 2 in column C and accessory 3 in column D.
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?
I am trying to use a Mid function to help me in my vba problem.
In my first table I have a list of account numbers. E.g. 'ZZ500543'
In my second table I have a list of account descriptions. E.g. 'Denis Morgan ZZ500543 leak from bath'
I need to match the account number in Table1 with the correct account description in Table2. I thought a Mid function would be the best option to pick out the account number in Table2? Is this right? How would I go about using it?
I am curious if there is a way to combine the content of rows based on a change in account number(column A). The tricky part of it is that an account number could be in the file once and another could be in the data twelve times. The list is sorted by account number. I want to take the product code and description and keep placing in the combined row for each account number. Attached is a sample of the data.
I am building a spreadsheet that references 'LHA rent rates' listed in other cells.
Example table Bed rate Code: LHA per week: 0 - £55.00 1 - £91.85 2 - £109.61
(Ie two columns with six cells. The first number before the dash are in a separate column. Each of these £ amounts will be in a separate cell.)
In another part of the sheet, i want to be able to type '0' in Cell 1, and this will trigger a formula in Cell 2 to retrieve the correct £ amount i.e., £55.00 in the 'example table' above. Likewise if i change Cell 1 to '2', then Cell 2 automatically updates to £109.61.
I think this would be a long IF formula in Cell 2, but how to do it.
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00 columns E, F respectively have cust #B100 & 40.00 columns G,H respectively have cust #C100 & 60.00 Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?
I have a standard block of text with numbers in it pulled from various calculations in a financial model. I have done this through a formula
e.g. ="You gross profit percentage is " & D9 & "% and your gross profit is $" & D10 & "." Problem is i'd like to format the numbers that pull through so they are easier to read. At the moment in the above example D10 results in $-600000000. I'd like it to look like $(600,000,000).
I am creating a register and have got a worksheet with everyone name and dates across the top, the dates are automatically created by entering one date, the dates are weekly. But i want to show when we are on holiday, could i do this by using conditional formatting and if yes, how and if no how else can i do this.
There is a clever piece of conditional formatting designed to show if a formula has been overwritten. It works by creating a range called (say) CellHasFormula and then entering =GET.CELLl(48,Indirect("rc",false)) in the reference box. In the cell you wish to conditionally format you enter =CellHasFormula. If the formula in that cell is overwritten, then the conditional formatting is displayed.
I have the following format in Excel, how I change to show just year as when I go to format, cells, it appears to be customed to be h:mm:ss but I need it as yyyy, which if I change to it shows 1900..
