Creating Master Sorting And Improving Workbook View
Jul 31, 2012
I have an excel file as attached. I have two departments Digital and Industrial. Under this I have done a drop down option for different quarter for different projects.
When I open my excel file I just want to see the Quarter column under Digital but when I double click on Digital all other columns appear.
Second this is it possible to create a master filter or sorting for Quarter above the table so that if I select Q1 in it it will displays all the project and corresponding values in both Digital and Industrial.
Attachment 47491A.xlsx
View 4 Replies
ADVERTISEMENT
May 3, 2006
I have 9 identicle spreadsheets, one for each user on a shared workbook.
I want to create one master table linked to the 9 spreadsheets which will automatically update including when new records are added.
With this in place I can then create a pivot table
View 3 Replies
View Related
Apr 24, 2012
I am trying to create a master list in a new sheet based on the data contained in 4 separate tabs. The data is Username, Display Name, Company. Unfortunately some users have accounts in 2 of the companies (with the same username) and I need these to default to Company A.
The output need to be username, Display Name, Company.
View 1 Replies
View Related
Jan 22, 2009
I have created a spreadsheet which is tallying up various data. I have a number of different categories e.g. A, B, C, D and within each one of these are sub categories. I then have a tally for the number of each category and within that the number of each sub category. I then want to sort the data so that first the main category is sorted in order of highest tally to lowest. the sub categories however need to stay with their primary category. I then need to sort each sub category within the main category.
the data looks omething like this:
category tally sub cat number
A 6 A 3
B 4
C 6
B 9 A 1
B 6
View 9 Replies
View Related
Feb 12, 2009
I have created a UDF with much appreciated help from this forum but, now I have to consider another set of variables in this UDF which I am not too sure how to put it into the codes. I hope to get some help with this. Previously I have a set of variables classified under Codes that ranges from "AI to "II" now I have to introduce another set named "Status" they are
"Accepted"
"For Acceptance"
"Rejected"
Another request if possible is the UDF can incorporate the "If funtion" formula I have used together with this UDF. The formula basically says, if Agreed Column is "blank" use the value in "Estimate".
Function AwardImpact(Code As String, Amount As Single)
Select Case Code
Case Is = "AI", "ai" 'Transfer from DD Provisional Sum (-) to Award Value(+)
AwardImpact = Amount
Case Is = "BI", "bi" 'Transfer from Award Value(-) to DD Provisional Sum (+)
AwardImpact = -Amount
Case Is = "CI", "ci" 'Transfer from Other Packages (-) to Award Value(+)
AwardImpact = Amount
Case Is = "DI", "di" 'Transfer from Award Value (-) to Other Packages Value(+)
AwardImpact = -Amount
Case Is = "HI", "hi" 'Tranfer from Provisional Sum (-) to Award Value (+)
AwardImpact = Amount
Case Else
AwardImpact = 0
End Select
End Function
View 9 Replies
View Related
Jul 1, 2013
I am working on creating a medical master database. I have a master sheet with a huge number of columns. I have a couple of guys working with me, and they have their own sheets in the workbook.
So here is what I would like to do: anytime one of the guys enters data into his respective worksheet, I want the data to be copied over to the master sheet in a new entry.
The columns among the worksheets are different, and I would like the data to automatically sort in the appropriate column on the master sheet when it is copied.
I have attached a copy of the workbook.
I have included an example of what I would like to see. For example, if Charles puts in an entry in his worksheet, a new entry is created on the master sheet with the information from Charles' sheet sorted in the appropriate columns.
View 8 Replies
View Related
Jun 14, 2007
I have a spreadsheet that I am trying to use as the 'master'. I want to break down this master by category (Column D) and label the worksheet by category (AA, Code, SQL...)
I need for the code to search the D1:D500 column and find "AA", "Code" or "SQL" and place the entire respective row on each respective worksheet. Then sort by colmn E. There are columns a:h that need moved to each sheet.
View 3 Replies
View Related
Dec 21, 2009
I have a formula which is currently too long and which I'm sure could be done more efficiently. That plus Excel is saying the formula is too long. I have pasted the formula below and broken it up for clarity:
---------------------------------------------------------------------------------
=IF(AND(E3="Three",I3<=100),
(I3*VLOOKUP(C3,costsnetwork,10,FALSE)/100),
IF(AND(E3="Three",I3<=250),
(100*VLOOKUP(C3,costsnetwork,10,FALSE)/100)+((I3-100)*VLOOKUP(C3,costsnetwork,11,FALSE)/100),
IF(AND(E3="Three",I3<=850),
(100*VLOOKUP(C3,costsnetwork,10,FALSE)/100)+(250*VLOOKUP(C3,costsnetwork,11,FALSE)/100)+((I3-350)*VLOOKUP(C3,costsnetwork,12,FALSE)/100),
View 14 Replies
View Related
Aug 16, 2006
I have 1 workbook which summerizes 3 other workbooks. My question is, is there a way that if I hide a column(s) in 1 workbook (by either using Hide or Outline), the corresponding column(s) will also automatically be hidden in the Summery workbook?
Or, is there a way that I can make the Summery Workbook only display / Link to cells that is been displayed in the source workbooks? I hope to do this dynamically, so if something is hidden by Outline or Hide in one of the workbook, the affected cells in the Summary workbook will also go in hiding.
I am hoping to do this by some magical Excel Function, if not then by VBA (I'm not that good a VBA programmer), if not even that, then maybe some free Excel Tool someone knows?
View 6 Replies
View Related
Sep 17, 2009
I have multiple workbooks ( with unique names) under same folder. Each workbook has "Report-Corn" sheet.
I would like to copy cell "P15" from each (workbook-> report-corn -> P15) and paste to Master.xls workbook -> Sheet 1 one after another which is also located under same folder.
View 9 Replies
View Related
Jan 11, 2010
I am trying to create a custom sorting in VB. My sorting needs to be first on column B, then C and last on G. My data does not have headers.
View 5 Replies
View Related
Mar 20, 2014
Trying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
View 5 Replies
View Related
Dec 22, 2009
I have an excel document with about 7 worksheets in it. The users of these worksheets can save the document but they never save it with the page view starting from the top. How can i ensure that whenever this document is open the default view for all the contained worksheets is at the top (ie:cell a1).
View 2 Replies
View Related
Nov 8, 2011
Is there a way to setup personal views of a shared workbook ? For eg : I might hide columns f to j and the other person might have to hide columns h to m. Also, one person might be using ver 2003 and other person using 2010.
View 2 Replies
View Related
Apr 15, 2013
I need to create a macro to copy the data from time sheets of different employees into the Master sheet. ie., each employee's time sheet details in to a separate sheet. If any employee fails to provide his/her time sheet details. a mail must be sent to the respective employees regarding the Time sheet submission. I am providing the sample data of my files too.
Intentionally TS_Employee5.xlsx timesheet isn't provided. In which case, I have to send a mail to the respective employee, in order to remind him about the time sheet.
View 12 Replies
View Related
Aug 26, 2009
How to Automatic open others Workbook when we opened the Master Workbook.
Examp.: I have a workbook (Master.xls), and when this file be opened, and then others workbook (Transactions.xls, Order.xls, etc.) is opened too.
View 5 Replies
View Related
Feb 6, 2008
I have two workbooks that are used in conjunction with each other.
One is a template that creates a quote, the other is a workbook that has data which is used to populate the newly created quote. The data workbook is opened and closed by the opening and closing of the template. But beside the need for the user to see the data when they are selecting which items to add to the quote, there is no other reason for them to see it on the "View", "Switch Windows" for you 07 users, can't recall 03, or on the "Windows Status bar" at the bottom/side or where ever it's placed.
Is there anyway to hide this from the user, and or prevent the user from saving any changes on the data workbook.
Came across this code on MSDN but not sure how to incorporate it or if it's even applicable. [url]
Visual Basic (Declaration)
< BrowsableAttribute(False)> _
Public Property IsAddin As Boolean
Visual Basic (Usage)
Dim instance As Workbook
Dim value As Boolean
value = instance.IsAddin
instance.IsAddin = value
View 9 Replies
View Related
Mar 6, 2007
I have a workbook that, when opened, sets the view to full screen. When it is closed, I want the view reset to normal.
The problem is that the prompt to close full screen appears rather than the view automatically resetting to normal.
What additional codes is needed to fully automate this?
I have:
ActiveWorkbook.Save
Application.Workbooks("PCAR Log.xls").Close (0)
Application.DisplayFullScreen = False
View 4 Replies
View Related
Apr 9, 2014
I believe that someone showed me once that you could view the same workbook on two monitors with the vien arrange all mode, you could make changes on both screens and the changes would be saved to the same file similar to arranging two views. I cannot rememebr how to do this or even if it is possible.
View 5 Replies
View Related
Apr 18, 2013
I have hide workbook in view and hide option, when i unhide the same it should ask password. Is it possible.
View 1 Replies
View Related
Apr 10, 2008
I have a master workbook the needs to be updated from another workbook. I have adapted the following code that I found hidden away in this font of knowledge.
Sub test1()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim OldLastCell As Range
Dim lookRng As Range
Dim foundRng As Range
Dim iCel As Range
Dim loopRng As Range
If IsWbOpen("Update") Then
Set wb2 = Update
Else
Set wb2 = Workbooks.Open("C:Documents and SettingsCompaq_OwnerDesktopAreaUpdate.xls")
End If
Set wb1 = Workbooks("Original.xls")
With wb2. Sheets("Sheet1")...................
View 6 Replies
View Related
May 30, 2014
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
View 3 Replies
View Related
Sep 15, 2014
Is it a good idea to merge xls workbooks in to one master workbook?
I have read online that some say it is ok and nothing will happen and others say it isn't a good idea because macros and formulas will not work right once merge in to one workbook.
So I have many workbooks with 2 - 4 worksheets in them. All have formulas as well as macro's and everything is working fine just as it is. But I would like to have 1 Master workbook with all workbook/worksheets combined in to 1, so that I can stop opening so many workbooks.
View 1 Replies
View Related
Jul 1, 2014
I wish to be able to get data from several workbooks (.xls), and gather all the data in one master workbook.
As I said, I've got a number of workbooks with data I wish to copy and merge into one master worksheet. Each workbook contains a sheet called "MILESTONES" (project) in which I want to pick up data. The data ranges from A5:J88, but I think this can be expanded by the users (more rows). There is no guarantee that the users will enter data in all the columns. Columns A and B occur frequently blank, so the users usually starts entering in information in column C (heading = description).
The Master workbook contains of exactly the same headings, except that the columns has been expanded by 1. Column A1 has the heading "Workpack" which is the name of the workbook the information has been gathered from (description is in column D1). This name can be found in a sheet called "REPORT" in all of the workbooks, and I want this to be copied over, row by row so it matches the data. I don't know if it can cause any trouble, but the names are merged through C to J in row 12 in all of the workbooks.
The workbooks (or workpacks) and the master workbook (summary) are placed in the same folder. Let's say C:Project. The workbooks also changes names frequently (each month). Lastly, if there is a way to automatically update the master workbook every time it is opened.
View 5 Replies
View Related
Feb 16, 2014
I have spent many hours trying to research and create a code to enable me to create a master workbook with very little success
I have attached the "master workbook"!!!
I have a file C:workjobs
In this folder I have many workbooks all named differently normally with the customers name and a description of work completed ie: Mrs Jones, Bathroom refit, etc and the file is updated with new workbooks all the time as we complete new jobs
I need create a macro that automatically starts when the masterfile is opened that grabs all the information needed from all the individual workbooks and then inputs the totals into the cells in the masterworkbook so I can then analyse the information
The master workbook I have attached is exactly the same layout as the individual workbooks
View 3 Replies
View Related
Sep 24, 2013
at the moment I'm trying to consolidate four Workbooks to a Master Workbook.
The four Workbooks I want to merge have the same table structure but except the "Project Number" they have almost different data in their columns.
What I want to do is creating a Master Workbook in which all the Projects are listed once with the information of all four Lists.
While consolidating the data should be checked whether it is already in the Master Workbook or not:
If yes the Macro should copy the to adding data in the existing Project row and there in the correct column.
If not the Macro should create a new row for the Project.
View 3 Replies
View Related
Feb 7, 2009
I think this needs a For Each loop, but I am stuck on how to get it accompished. In the folder I will have about 26 workbooks with all different titles, but the beggining is always the same for the workbook and the worksheet (Bldg). There will be only one ws per wb and the wb I am using to collect all of the worksheets is Final.xls. The code attached works fine, but the only way right now I know how to capture the next wb/ws is with a call.
Sub CopyAllWrksht() ....
View 9 Replies
View Related
Sep 19, 2009
Using a Command button into my Master file, I'm looking for a code that give me an option to select an existing file that will have a variable name & path, open it and then copy all worksheets contained within it, into my Master workbook. Then close the selected file on completion.
Not sure if it's important but some of the copied worksheet names will contain copies. e.g. Apples, Apples (2), Pears, Pears (2). etc.
View 9 Replies
View Related
Aug 14, 2008
There are 7 files on the network that everyone updates. These are contact databases for clients, insurance companies, our agents, etc. Any changes that need to be made are made. The first person to open a file will get edit mode, the others will only get read-only mode. I rarely have to edit, so if I'm first then I'll invariably be asked to exit so others can update them. I've tried doing the share workbook, but it's not working. That's not what I'd like to do anyway.
I've made a workbook with 7 different sheets, each tab named after one of the network databases. I'd like for these sheets to automatically update if/when changes are made to the master - but keep my formatting intact. Right now I'm relying on import external data, but that doesn't update. Therefore, I have to delete and re-import every morning, losing all of my formatting. Surely Excel has an easier way to do this?
The databases are highly sensitive information so I can't really attach one - but I will attach a dummy example of what they all look like. Each file is hundreds of rows and maybe 15-20 columns, so the thought of linking each cell is more than I'm looking to do.
View 7 Replies
View Related
Aug 18, 2006
I have 600+ variations of the same workbook. Contained within each workbook is a worksheet, from which I need to copy and paste a range of cells into one "master" workbook. For example, in workbook 1 I need to copy rows 2:5 and paste that into the master workbook in cells 2:5. Then I need to open workbook 2, copy rows 2:5 and paste them into the master workbook starting at row 6. I have fumbled my way through everything except the pasting part into the Master workbook.
View 9 Replies
View Related
