I found a way (on this board) to spell out numbers...I.E.: 140 = "One Hundred Forty" .
But is there a way to convert "One Hundred Forty" to 140 and format as a number or general or pretty much anything but text? I thought would be as easy as Cell Format > Number
I'm trying to write a formula for the following simple situation. Eventually I will need to use this formula for several hundred people with several hundred choices.
Like I said, this is a simplified example that I will eventually need to expand out to hundreds of people. Lets say I have 5 people, we will call them #1, #2, etc to #5 and I have 5 cars, red, blue, black etc. Person #1 gets his first choice, person number 2 gets second choice etc. But if person #2's first choice is the same as person #1's, obviously he can't have that choice and must go to his second choice. Some people may not have a preference and won't get assigned any color.
I have a giant sheet that culminates in a graph. It is for various regions. When used in a country with in-control currency, you end up with a nice healthy number that looks good on the graph. When our Zimbabwe office uses it with their crazy inflation and high prices, we get a crazy number that goes into scientific notation...
Anyway, my question:
Given a number like 4,200,000 or 6,500 or 345,123, I want to be able to show as few trailing numbers as possible, and give a nice K or B or D or however you abbreviate Quadrillion (kM? for Zimbabwe) after it.
And, if possible, I'd like to do it with number formats, but I am flexible so long as it doesn't use VBA.
The lookup table would be:
Sheet1 AB1<1,000As is21,000K31,000,000Mil.41,000,000,000Bil.5>1,000,000,000,000Tril. Excel tables to the web >> Excel Jeanie HTML 4
All numbers have 2 significant digits, and I want to keep 2, so if it ends up as 4,200,000, I want the answer to read 4.2 Mil, etc.
I tried doing this with a lookup, and couldn't handle it.
I have a spreadsheet for a couple hundred rows of data, and 6 columns. The fifth column contains a date. What I need to figure out is this:for every 3 rows of data, if the date diffes in column 5 (E), highlight this row and the previous 2 rows
Currently, I've been doing this all manually, row by row - needless to say, it takes me a few hours or depending on how much other work I need to do, a few days.
In my spreadsheet, I have approx. 300 'blocks' of data, one for each 'vendor'. I am tracking 5 stats for each vendor for 12 months. Each 'block' has 5 formulas WITH conditional formatting (only one condition used) for each month, so each 'block' has 60 individual cells with conditional formatting. Excel throws an error when I try to copy the block of formulas and formats down the sheet a few hundred times. I have figured out it isn't the formulas that is the problem. It must be some kind of limit Excel 2003 has for how many cells in a workbook can have conditional formatting.
View 9 Replies View RelatedI have a layout something like the following:
A1
A2
A3
[Code]....
Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).
Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".
Ultimately I am looking to have a matrix which contains all the distances between each location:
A1
A2
A3
B1
[Code].....
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)
Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines
Sheet2 (has current material codes plus current pricing), has about 36000 lines
I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
I have a spreadsheet with some scripting that automatically emails the report to whoever is on the sheet. I'm not sure what kind of scripting it is. I think my boss found it somewhere.
Whenever I scroll either down or up, the first 3 rows disappear and I'm not able to scroll back up. The scrollbar shows that I'm not at the top, but it won't let me go back up. The first 3 rows contain the button to send the email. If I close out without saving and open it again, its fine, until I scroll up or down.
Copying information from various sheets from one workbook to paste into similar sheets in another workbook? It would involve switching back and forth between workbooks.
Back Draws! ...
View 9 Replies View RelatedI have a spreadsheet with over 20,000 rows. From another spreadsheet I need to find if any of a list of 90 customers are within the first, using a simple IF and vlookup formula which comes back yes or no.
I am using: =IF(A2 = VLOOKUP(A2,ihcust!B:B,1),"YES","NO")
However, although this works when they are spelt exact in both sheets if they are not it will say no.
eg. If I was Looking up "Joe's Icecreams" from the list of 90 but in the other sheet it is listed as "Joe's Icecreams (admin)" it would not recognise it and come back no.
Is there away to get around this problem
I am trying to create an IF formula so when i try to get the percentage change if lets say the 2008 number was 0 and the 2009 number increased by 9 than i would get an error so i want the if formula to come back as n/a.
View 5 Replies View RelatedI'm setting up a payroll for several employees in one workbook. I start from the particular employee's worksheet and then I need to go to another sheet where various information is stored and then come back to the original employee's sheet. The current macro works fine if the various information is stored on each employee's worksheet but I can't figure out how to go to another sheet and then come back to my active cell in employee sheet.
[Code] .....
I am currently using this code which automatically saves a back up copy to another location on my system.
However, the only way i can get it to work is to assign it to a command button. Is there any way in which this will work once i just click the save button.
I am currently using this method to create a back-up of the current workbook to another location using a yes/no message box.
If the user clicks no, the workbook will be closed...also if they have not saved any changed data excel also gives them this option in case they close by mistake.
Its the yes option i am having issues with.
If they click yes...the back-up code runs fine and saves the file elsewhere...but for some reason the code loops and runs again. Not sure why this is happening but i do not want it to. Also again if there is any unsaved changes...excel does not give them the option to save this first...i would like this happen.
I want to write a formula that counts back a given no. of columns and averages those cells.
Example:
cell P4 - 4 = cell L4
AVERAGE cell P4 to cell L4
To obtain the number of cells I am going to look back I will be using a VLOOKUP... So that number will be open to change.
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
I have this formula in Column L. The calculations are working fine and I close the file. After I email the workbook from one computer to another and then resave it, every row in Column L that has the formula turns to #NAME?
Do you know why this is happening?
Once I reach Column G and I press enter, instead of going to Col I, How can I get Excel to back back to Col A, but on the next row?
View 5 Replies View RelatedColumn A disappeared and didn't come back. I even arrow over to the left margin, but the furthest it goes is to column B. How can I get my column A back?
View 5 Replies View RelatedI have made a sheet with 10 input boxes, some for text and some for numbers. If you make a mistake now you must click though the rest of the questions to start again and make a change. Is there a way to add a back button that would take you to the last question? Or possibly make the cancel button stop all questions? Here is a sample of the code
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strname As String
If Target.Address = Range("k11").Address Then
[Code].....
I am a new to VBA. I want to add a range of numbers to another range of numbers. However second range should always add to itself with first corresponding array values. For example my firsr array is A1:F10 and second array is A15:F25. Now i want A15 = A15+A1, A16= A16+A2, A17= A17+A3 and so on. Every time second array should retain itself value and get added with first corresponding array values.
View 5 Replies View RelatedI've got a Macro that generates three reports for me, one at a time.
After a report has been generated it creates an Email and attachs the report to the Email, I then check the report is ok before sending out.
The problem is, that once the first Email has been generated Excel is pushed into the background and the macro sloooooooows down.
I believe (though I will quite happily be wrong) this is because Excel is no longer the PC's main concern.
What I was wondering was, is it possible to bring Excel back to the foreground once the Email has been generated.
I have cut and paste a series of numbers from my online bank account statement, however, when I go to add a total to the spreadsheet it comes back as zero. I have removed the currency sign from in front of it, I have changed the column format to be numbers but the total still reports a zero.
However, if I type in the number the value is recognized.
I'm finalizing an Excel app - which uses Userforms only. On opening the app I use the code line Application.Visible = False
After clicking on my Label2: (I'm taken to the Web site, but when I click the
< (Back button - Return) I am returned to my Userform BUT my Excel App (Menus, etc) is now showing up. How can I keep it "turned-off"?
Private Sub Label2_Click()
Link = "http://www.jmmay.com/"
On Error GoTo TheEnd
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
'Unload Me
Exit Sub
TheEnd:
MsgBox "Cannot open Hyperlink"
End Sub
With "edit directly in cell" turned off, if you double click through in a cell containing a formula to the cell being refferenced, is there a quick way of getting back to the original cell you clicked on?
View 9 Replies View RelatedIs it possible to make a back-reference in a cell formula similar to regular expressions?
Example:
=IF(I15=FALSE, [H17 * INDEX(Percentages,I17+1)], IF(A17="", $1, 0))
...where [ ] indicates the actual reference and $1 indicates the reference number
I am developing a macro where I need to open a file, copy a value from a cell in that file and paste it in a cell of the original file. I need to do this several times from the same two files. My problem is that I don't have a command that moves back to the newly opened file to copy another value and paste it, again, in a cell of the original file. I can't use a specific file name to move to the newly opened file because the new file name changes every day. Here is the command that I use to open a new file:
Dim fn As Variant
fn = Application. GetOpenFilename("Excel-files,*.xls", _
1, "Select The Previous Day's Borrowing File To Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
' the user didn't select a file
Debug.Print "Selected file: " & fn
Workbooks.Open fn
I need to move back to file fn several times, but, as I stated above, the file fn changes every day.
How to make sure that every workbook I create has the option of "backing up before saving" turned-on? I need a Excel-wide option, i.e. one that applies to all workbooks created, The known to me facility of turning the option on every time I create a workbook is not satisfactory because often I forget to turn the option on when creating a new book -- and I create many.
View 2 Replies View RelatedIf back Order if morethan 0 and due date is < today "over due" will appear. and if backorder more 0 due date is today, "due today" will appear.
View 3 Replies View RelatedHere is my formula:
=IFERROR(INDEX(Stored!$D$22:$AN$795, MATCH(D11,Stored!$E$22:$E$795,0), MATCH(E10,Stored!$C$22:$AN$22,0)),0)
For some reason it keeps pulling back the value that is one cell to the right, so one column off.
What could it be.
In this example it should pull back N363 and it is pulling back O363.