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 RepliesI am looking for formulation/organizational advice for a database of over 3000 MLB Baseball games. This database would break down each game based on 7 different factors that are evident in some games and abscent in others...
What I am looking to do is to create a formula that resembles a "choose your own adventure" sort of scenario...For example, If i wanted to immediatly view all the games in the database that exhibited 1 factor, say "Games where home team won by 5 or more," then I could easily view all of those games... Then, it would have the capability to go deeper, say now I only wanted to view "Games where home team won by 5 or more and away team got 6 or less hits"...Etc...
I 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 am making a simple map in excel. I have my floor plan on sheet 1 and the location of product on the floor on sheet 2.
Currently I have some formulas on sheet 1 that link to some sales data on sheet 2, but what I would really like to be able to do is use the search function on page 1 and it give me the location of the products in a given area.
The data is broken up into group location on the second sheet.
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.
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
Back Draws! ...
View 9 Replies View RelatedI have four columns containing about 200 addresses.
A Names
B Streets
C Cities
D Phone no.
If I write a name in D1 I want to get the corresponding street, city and phone no. in E1, F1 and G1.
I also want to copy the formula downwards let's say ten times so I can make a list.
If I for example just want to make a list of three names (D1-D3) I want the rest of the fields to stay blank.
I've created an Add-in (.xlam file) in Excel 2007 and installed and activated it (it shows up under "Active Application Add-ins" on the Add-in menu for new workbooks).
I can see the macros and code when I go to the VBA editor, but the macros don't show up in the regular macro list.
I know that I can write code for the new workbook that will reference the Add-in code, but I want other users to be able to install the add-in and run the macros in it right away without any knowledge of VBA. Is there a way to make this possible?
i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;
View 14 Replies View RelatedI have a spread sheet that tracks progress reports that are due every 90 days. I have a due date colum that will automatically get high lighted if the 90 days have passed to inform me or my staff that the report is late. I would like to have a colum with a button in each row that allows the reader of the spreadsheet to press "YES" for that accont's progress report having been turn in. When the "YES" button is pressed the date due date column advances to the next 90 day due date and the highlight would turn off.
I used the conditional format to change the due date column to Yellow if =TODAY() is less then or equal to the due date. But I can figure out a way to select something and turn the highlight off and advance the due date to the next 90 day due date.
I have been asked to make a formula that sums up the hours in a week and then tells me whether I am below or over the budgeted hours for that week!
I cant get it to work since I cant figure out how to have the same formula in all the cell that sums up early, mid and late shifts, ie 06-14, 14-22, 22-06.
The graveyard shift is a problem there since I get negative hours.
Further more, I need to have the formula ignore cells to be added up if it has letters in it, we write an R for Rostered day off.
............Beer........ Total
Stefan:....1........... 0,70€
Now i need a button that when i click on the name the amount 1 gets 2,3,4,5,6, ect and that it calculates the price up as a total
1- I 'm working with a table wit many Sheets.
I want to make a Report Sheet with selective ranges(cells)
the selective cells should paste as link ( in order to be updated cells)
2- then how to Zip this worksheet to zip file ( to save it)
3- If I have a Macro and i want to perform this macro to all sheet except one sheet how to that ?
I would like to:
1-Make Rows & columns 1/4" X 1/4"
2-Be able to enter dimensions into an input box that would then draw a square(or rectagle) with the inputed dimensions in INCHES.
I 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'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