I created an Excel timesheet, no VBA, which 65 people are using. 64 without a problem but the 65th cannot print the sheet as others can. The last cell on the single sheet is AB61. For the 64 who can print it by selecting the fit to print on one page the percent is 71. For the one person with problems the percent goes down to 10. Even when she opens the read only version on the network it becomes 10%. I checked the magins and everything else I can think of. Short of reinstalling Excel (2000 with Windows 2000)
I just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%. I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.
I have used Option Buttons and Combo Boxes on my worksheet. When I print my worksheet, the controls which contain values do not print properly. They only print a fraction of their actual size relative to the rest of the worksheet. The properties are set so that they are printable. What setting do I need to change for them to print properly.
I have a spreadsheet with many cells that require a date. I have added calendar control 11.0 and running it with the code below. The problem is, that everytime the sheet is opened, the calendar gradually gets smaller and smaller each time the workbook is opened. I have tried resizing through formatting the object, but after opening the workbook several times, it's has shrunk down to unusable again.
Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("B161:B184,I161:I184,P161: P184,"), Target) Is Nothing Then Calendar1.Left = Target.Left Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True ' select Today's date in the Calendar Calendar1.Value = Date ElseIf Calendar1.Visible Then Calendar1.Visible = False End If End Sub
I'd like to use the SMALL() function in excel to pull out the second lowest unique value in a list, but I'm not sure there is a way to do this. For example, if the array is {1,1,3,10,2,6}, then SMALL(array,2) returns 1, but I'd like it to return 2. Is there a way I can modify this function or use a different one to achieve what I want?
I have a cell, M87. The score in M87 can be less than 13 or greater than 25. I need a formula within M94 which refers to M87, and outputs depending on the the following criteria. If M87 is less than 13 then output as D. If M87 is 14, 15, 16, or 17 then output as C. If M87 is 18, 19, 20, 21, 22, 23 or 24 then output as B. If M87 is greater than 24 then output as A.
I'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:
I'm creating a report that pulls certain record entries from another worksheet (Activity) in the same book. Originally, it only pulled one type of activity. "SO". Now I need it to pull "SO", "CC" and "BT" type activities into the same report. Currently my formula looks like this and works fine for only looking for the "SO" type:
I have an equation below that work great except when row 2 has a duplicate value. When this happen row 5 only return the first value in row 1 and not the other duplicated values.
I have a Pivot table (see picture) and in another field I want to be able to type in the Month and Year (e.g. January 2012) and return the worst performer of the 8 suppliers. I have been trying to use SMALL in conjunction with INDEX(MATCH(MATCH)) but it doesn't appear to be working.
I am sure there is a simple solution but it is evading me at the moment.
excel.jpg
This is where I am at with the formula. Maybe I can use the IF function somehow?
I have the formula (found in cell "C2") on the Report sheet. I need to perform a function, but I cannot get it to work on the sheet I need to pull information from. The sheet RecapWk12 has a small section pasted (with some cells edited for obvious reasons) from the actual workbook. I can get the formula in Report cell (A10) to work on pulling information from sheet2. You can see I am getting (#REF!) in cell C2.
I am trying to get the following thing started but my loop doesn't succeed. I've got 3 columns. I need to place a formula in column C if A is empty.
Example:
A2 contains data so do nothing. A3 is empty do: C3=B3+B4. A4 contains data so do nothing. A5 contains data so do nothing. A6 contains data so do nothing. A7 is empty do: C7=B7+B8.
Please see the attached worksheet for details. I would like the array function to search for instances of the word "FALSE" in column E and return the values of columns A:D when a match is found. I have done this successfully when the lookup value is a value in the first column of the range, but cannot seem to do so when the lookup value is in the last column of the range. I have received a #NUM! error each time.
I compile ratings for racehorses and am currently extracting the data I need using an array formula. Unfortunately there are are many thousands of lines of data and it can take quite some time for the data to pull through.
Here is a sample of the formula's I am currently using. I cannot take credit for the formulas by the way I found them on the web.
Basically I have 2 sheets (Database 2012, Database 2011) with the horse names with ratings next to them for each run, One sheet for this season one for last season.
These are sorted in date order. then I have a ratings sheet that shows the horse name and then to the right it has columns for each of its last 4 ratings.
The formulas I use look for the horse name on the database sheet then put the rating in latest rating in the first column then the 2nd latest in the next column and so on until the last 4 ratings are shown. If there are not 4 ratings to find the is an iserror at the beginning of the formula to populate that cell with a blank so that the errors dont show.
What I want to know is there a way of doing what the formula does using VBA and more importantly would it be quicker than using array formulas which are very slow to recalculate when there are 1000's of lines.
When I try to record macro, the small toolbar which has two buttons "stop recording" and "relative reference" do not appear. I need to turn relative reference on. Can you please guide me how can I make it visible to turn relative reference on?
The following code is a is designed to paste into "Y1", the value in column 5 of the active row when that cell is changed. This works perfectly if I select the value from a dropdown list and hit enter, or if I enter a value manually and hit the right or left arrow key. However if I manually change the value of the cell and then hit enter, it does not work because it looks for the value in the next row.
Does anyone have any idea what I can do to make the macro stay focused on the cell who's value changed even if I hit enter?
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim AR As Long 'Active Row AR = ActiveCell.Row If Target.Column = 5 Then ThisRow = Target.Row If ActiveCell.Value > 0 Then Selection.Copy
I have a form that has been set up at 55% for best view. The only problem I have is that all the dropdown boxes (List) that i have the text is extremly small. Is there anyway of changing the size of this text? just the size of the text in the dropdown not in the cell after the selection is made.
I'm writing an application in Excel, its a little over 5MB after I've run through UsedRegion and trimmed stuff, 94 worksheets. I'm encountering a fair number of crashes these days. My OS is fully patched as is MS Office. Running Windows server 2003 and Excel 2007.
I was wondering about the pros and cons of writing an application in one large workbook or many smaller ones. So far its been one large one but these crashes are really impeding my progress.
What's the best way to remedy these crashes or even start to diagnose?
more simple so i can get the range for my small and large formula's variable? What i try to reach here is:
Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp)) Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
All sheets are basically the same except some minor values, names and addresses. There is one page per sheet and all pages are in portrait format. The print preview shows all the pages in order but when I print it, it makes multiple print jobs of 1 to 2 pages each and prints them all out of order. I don't know why it is splitting up the workbook or why it changes the order. I print and reorder these weekly, which is a major pain. It comes out in the same order each time but it is the wrong order.
I am trying to have a file print in legal size if I have 56 lines filled in otherwise print in regular letter size. Does anyone know how to write this in VBA.
