I have pulled the mode from the sheet called Daily for cells B5:B33
=MODE(Daily!B5:B33)
I need the second and third most occurring figures, Im not sure if it matters but they are times
I tried this but I don't seem to be getting it right for the 2nd mode
=MODE(IF(Daily!B5:B33<>MODE(Daily!B5:B33),Daily!B5:B33,""))
I already have a couple of VBA commands on my sheet. so I've tried to add:
[Code] .....
but this isnt making the sheet automatically go full screen.
I like a particular workbook to open in full screen mode.
How would I do that?
Can you change the height of a TextBox in a workbook when not in Design Mode? Like just grab the bottom and resize.
View 2 Replies View RelatedI'm using Excel 2010. I have a workbook with only 1 worksheet in it and it will be sent to several people. I want that excel file to open in Full View when the end user opens it. Is this possible without any VBA codes?
View 2 Replies View RelatedI am trying to quickly transfer all worksheets in a directory into one worksheet listing all worksheet names in the tabs in number order.
The formula have so far is below. But it does not name the individual tabs as the worksheet names in no order.
Sub GetSheets ()
Path = "Y:
Filename = Dir(Path & "*.xls")
Do While filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
Sheet.Copy After:=ThisWorkbook.Sheets (1)
Next Sheet
Workbooks (Filename) . Close
Filename = Dir ()
Loop
End Sub
The worksheets appear as follows 1982-001, 1982-003 up to 1982-250 ( I want them in single workbook but as multiple tabs in number order)
I wanted to know if there is any way possible to get vba to insert a formula in a cell to each worksheet in each workbook in a folder and then using loop to extract all the info from each worksheet of each workbook in same folder into a master workbook?
View 1 Replies View RelatedI'm using some workbook-scoped named formulas to define some dynamic ranges which will be referred to by numerous worksheets. The named ranges are defined like:
NAME: gTable_costDetailsEquipment
REFERS TO: =globalParameters!$B$5:INDEX(globalParameters!$B$5:$C$1048576,1+countAdjacentNonBlank
(globalParameters!$B$5,"down"),1+countAdjacentNonBlank(globalParameters!$B$5,"right"))
From either of the tabs "Reports" or "DOR_Template" the user can press the large "+" icon to add a report (which copies the template or the last report to a new sheet).
When this Sheet copy takes place, excel is repeating my named formulas - this time it's making LOCAL versions scoped to the newly made worksheet.
I've used this copy sheet trick before and have never had excel create new, locally scoped, named formula for each workbook level name.
I also just recently started using excel 2013, is this a problem with the new version? I've just never seen this problem, usually workbook-level names are NOT duplicated on sheet copy.
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
I have code that i use to copy a worksheet of information in one workbook to a worksheet in another workbook. All i need is some guidance on how to copy a second worksheet from workbook 1 to a second worksheet in workbook 2. Should be fairl straight forward.
In the below i am copying the sheet Phone_data to a second work book sheet also called Phone_Data, i would like to include in the same proccess a sheet called Sur_Data from workbook 1 copy to a sheet called Sur_Data_R in the second. All the other features like find next empty row also apply.
I have an open workbook (A) and this is where the code should reside. I want to use VBA to copy the content of an entire worksheet from a closed workbook (B) to an existing worksheet in workbook A. How would you accomplish this?
View 7 Replies View RelatedI am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
View 4 Replies View RelatedI need to copy a range of values (actually two ranges). The ranges are of fixed size. Le't say A2:D20 and E2:H20.
Both workbooks have the exact same layout, and they both have the same named worksheet (in this case it's called Entry). However the actual filename of the source workbook is unknown. I know numerous users have changed the filename of the xls file.
I want to instruct them to open the old and new workbook, and open a third workbook containing the copy macro, they run the macro and it copies the data.
How can I reference a known worksheet name but of an unknown workbook name esp when the source and destination share the worksheet name?
I have a sheet named sheet9 and i want to copy the data from this workbook-sheet9 to a workbook called import data and sheet named "database".
View 5 Replies View RelatedI am trying to transfer a value from the active worksheet in one workbook to another workbook. I keep getting a subscript out of range error on the line that uses activeworksheet command when I run my code.
View 3 Replies View RelatedI have a Excel workbook with two sheets - 'MainDataSheet' and 'ArchiveSheet' .
The 'MainDataSheet' has 5 columns and one of the column is 'status'.
The 'MainDataSheet' will have a command button 'MoveData' which will trigger the VBA macro to move data rows from 'MainDataSheet' to 'ArchiveSheet' . Only the rows having value set to "MOVE" in the 'status' column have to be moved.
I am working with multiple workbooks with several tabs in each one. I need the forumula to update the "sheet name" from the source workbook even if the destination workbook is closed.
this is a portion of the forumula I am working with:
=SUM(IF(AND([L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4>=182,[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet26!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet27!$I$4
I want to find out the most frequently occurring value for the below
example. Based on the example below I want to know Column A = Sales, Column
B = s682, which value is the most frequently occurred.
Column A: Column B: Column C:
Sales s682 4
Sales s681 1
Marketing s683 5
Sales s682 4
Finance s682 4
Sales s683 5
Sales s682 2
I have a formula in the Name Define part of excel in workbook that refers to another workbook. =IF(COUNTIF([DaysWorking.xls]Sunday!$A:$A,'2009'!I1)>0,1,"")
The DaysWorking.xls workbook is a data workbook, users may change the data and after they are done, they may exit the program but not save it. The DaysWorking.xls workbook should always revert back to the original form. I've put a read only property on it, but the problem lies when the user saves the file under Save/As function. Excel then updates the link to whatever the user saves the file as. Is there a way to make sure that the formula never get's changed?
I am creating a formula - that will look up a value within a seperate workbook. I want the workbook reference to change based on a cell which contains the date.
So workbook name is "Report 120309.xls"
Cell: A1 contains the date "120309"
Current formula ='[Report 120309.xls]Sheet 1'!$F$50. Can I replace the data within the formula with the date in the cell.
Is there a good/easy formula to use when trying to find the mean, median and mode for a column? If so, can someone post it? I'd like to make it as easy as possible (obviously!).
View 6 Replies View RelatedI have a large data set. Within the column I have determined the mode. How
do I find the second most common number, third most common, etc.?
240
240
240
240
240
240
240
240
288
288
288
300
I want to use a formula to detect which name is appearing most in a selected range of cells. The Mode formula gives this result but for numbers. I was thinking of the probability of having to combine the LEN function with the MODE function.
View 9 Replies View Related=mode(x#:x#) will give the most frequently ocurring value in the range.
But I want to be able to use mode like subtotal where it will show only the most frequent value within the filtered list...
I need a formula that will find the mode from a range of numbers but ignore the 0's. I have worked out the below array formula which seems to work.
{=IF(MODE(IF(N18:P25>0,N18:P25))=1,"Low",IF(MODE(IF(N18:P25>0,N18:P25))=2,"Secure",IF(MODE(IF(N18:P2 5>0,N18:P25))=3,"High")))}
The only thing is, if only one cell contains a number other than 0 (eg. 1,2 or 3) then it returns #N/A. Ideally i'd like it to show the appropriate response for the number that is in the number range. For example - if one cell shows the number 2 and the rest are 0's, i'd like it to show Secure(the response for 2) rather than #N/A.
My display scale is 125%. A college's is set to %100. This causes errors when I place Shapes to saved locations, .Top & .Left programmatically. Based on Google searches, it appears that setting the auto scale mode to DPI may solve this problem. How do I set it?
View 5 Replies View RelatedHow would i find the mode of a range of numbers that changes?
For example, I want a simple out put of the Mode for the numbers after Aand D, and E and so on, without having to do it manually. A function perhaps?
I have attached spreadsheet. The 2 left columns are the raw data and the right columns are what i want the outcome (modes) to be. Is there a way I can do this without having to go through this manunally for every one?
I have the following code
Columns("A:A").Insert Shift:=xlToRight
Range("A1").Value = "Date added"
Range("A2", "A" & Cells(Rows.Count, "B").End(xlUp).Row).Value = Date
Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$L$3115").AutoFilter Field:=2, Criteria1:=Array( _
"1", "2", "3", "4", "5"), Operator:=xlFilterValues
but how do I delete what has been filtered without deleting the headers?
For some strange reason the workbook I have been using on a daily basis has suddenly decided to go into design mode and stay there. No matter how many times I click the design mode button in and out it always stays in and I can't use my buttons.....it just selects them?
View 9 Replies View RelatedWe have a spreadsheet at work that is saved as Micosoft.xlsx [share].
I think the "[share]" means that more than one person can access it at the same time. How do you create a document like this.