In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
VBA Code of a custom control button, Scroll Bar, that mimics the same code as the corresponding form button, scroll bar? Where a cell is selected and the scroll bar changes the cell value from 0 to 100 in single intervals.
I followed the instruction in the TechRepublic article "10 steps to creating a scrolling Excel chart"; [URL].... The problem is that the scroll bar stops working at a certain point and will not bring up the last 8 months of year 4 of my data. I get error messages and the chart turns blank.
I also tried a different method (again for a scroll bar and a rolling chart) as outlined in [URL].... There are a number of similar articles with almost identical formulas. Here, the first 8 month of year one are inaccessible = error messages and chart turns blank.
I have 4 years worth of temperature data. The original data table from where the Index function draws the data is 1463 rows long (minus 2 rows for the header). The "magic" number where the scroll bar stops working is 995 for the current value. From a current value of 994 onwards, the entire matrix displays error messages "Ref#".
In the second case, the magic number is 1242 which equates to August of the first year. 1241 and lower for the current value will freeze the chart.
How the scroll bar control generates and gets hung up on either of these two magic numbers is a mystery to me.
I set the control properties at the beginning, i.e. Jan 1 of the first year to: Current value =1, Minimum =1 (I also tried 0), Maximum = 1500, Incremental =5, page =30, linked cell =$E$2. What is most puzzling to me is the fact that the scroll bar does work but only for part of the data set.
I have a scroll (form control) linked with data from another sheet. It scrolls nicely and works fine but I'd like to be able to update the data sheet and then the table with the scroll automatically update too.
I have a form that is dynamically created at runtime. I have assigned the controls to a frame in VBA and also attached a horizontal scrollbar manually to that frame.
How can I control the scrollbar in VBA to appear, when columns of controls added to the frame exceeds 10. If not I want the scrollbar to be invisible?
Private Sub UserForm_Initialize() Const cTextBoxHeight As Long = 16 Const cTextBoxWidth As Long = 40 Const cGap As Long = 10 Dim W As Integer Dim a As Integer
I have never written or used a macro before and I have a simple macro task to complete:
I have Column L (L4:L10) of seven scroll bars that move according to number changes in column F (F4:F10). I want to create a form control command button that resets the changes on the scroll bars according to static column of numbers in Column E (E4:E10).
I don't know if it matters, but I'll add that I plan to add a second button that does the same thing with a different column of data. I assume I'll simply replicate whatever procedure I use in the first button - is this okay?
I have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets:
I am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)
I need to make a vlookup which returns values for many 1000 material numbers. The values is located several other files. My problem is that i dont want to update all vlookups every time the other files (with my values) are getting updated.
e.g.
i want to have en cell A1: FileNameVersion1 and when someone updates the file the only thing i have to do is to change the name in cell A1 to FileNameVersion2.
I am building a table for staffing purposes, and I basically only need to controls to "validate"/approve the table.
1. The number of staff put in must equal the required amount of staff (E column) 2. The same staff cannot be put on twice for the same time period (which I have done by the OR(F7=G7,F7=H7,G7=H7) as part of the validation)
My problem is that sometimes I need 0 staff (when its closed), but when I leave it blank obviously it wont approve the table because of rule #2 above, since it thinks the blanks are the same staff member.
I have tried with some ISBLANK statements, but it wont work.
I would like a formula that will take the info from 2 cells (not adjacent to each other or in the same row) and then look these up in a table giving the answer in the 3rd column somewhere else on the worksheet.
I've got an excel worksheet that contains a list of buildings and their associated control valves for water supply (subset of table attached).
I'd like the user to be able to search by building name to display its associated valves and control information. I realize I could use the list function on the worksheet, but would prefer to set it up as front page to the workbook where data is displayed in a nicer format.
I guess my question is if there any way I can set up another worksheet with a drop down box that allows the user to select a building or type in the building name and have its corresponding data display?
Im using a table so I can sort diffrent catagories and I'm trying to put other functions to the right of the table I noticed as I change the sort on the table is moves the information outside the table as well is there a way to seperate that information or do I just need to move it above my table?
I have a worksheet with 10000 records. For example, entire data in ColA-ColZ. In that, ColA-ColF - Personal Information; ColG-J Group1; ColK-N Group2; ColO-R Group3; ColS-U Group4; ColW-Z Group5. I this case, the data to be copied into another sheet as follows:
1) the personal data should be copied repeatedly. 2) Each Group data should be copied next to personal data. 3) The group's name is mentioned at the top of the datasheet.
I have attached a sample workbook for your kind reference.
I have a data input sheet on a spreadsheet with a pick list where we can select an option, but I need another 'flat file friendly' option to appear on a second sheet where the data will be pulled from. For example
If on the front sheet Flat Roof Insulation is selected, it needs to return FRI into the other sheet, there are 45 options in the list.
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out.
I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook.
I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
I'm having an issue and I've tried thinking of all the work arounds and haven't come up with a usable method. I built a form that pulls info from a Pipeline table. I wanted to keep track of all the people that make edits on a separate table. Is there a way to take the original info and paste it on to another table by way of a click() and allow the others to make edits on the fly afterwards? This has been bugging me all week.
I'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.
I am using a pivot table to summarise information with a list of tonnes and sources from civic amenity sites. All was fine until I got to this month to do monthly reports. The pivot table appears to be treating what appears to be exactly the same categories of "CIVIC" and "TRANSFER" differently. By this I mean that although the values in the column appears to be "CIVIC" or "TRANSFER", some get treated differently in the pivot table making it pretty useless. I have made sure that all spellings of the words are the same. there are no spaces before or after the word e.g " CIVIC" but to no avail!
i have a large table and i need separate x and y coordinates(to plot on a graph) of a particular repeated value that is known to me in a large table of numbers. how can i do this?
I have 2 large XLS sheets that need to be split into seperate sheets.
The first is only 5 columns wide but the amount of rows changes day to day.
The second is a maximum of 7 columns wide and again the amount of rows will change.
I'm hoping for a VBA code to be able to do both on seperate books.
The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters. I would like to seperate the sheets by the company name and have the name appear as the sheet name.
Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.
I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.
I have to prepare sheet 2-sales for my job and I cannot find the way to fill the price and sales results depending from the three other inputs required of the table.I belive we will need the functions addif, match, & others but still can't make it. Another tag when there is no result it needs to show "No existe producto".
I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.
I have a macro that enables me to split my master table into separate tabs based on the first column. However, the column widths and the header format is not retained. Is there something I can add to the macro below to keep the formatting?
Code: Sub DispatchTimeSeriesToSheets() Dim ws As Worksheet Set ws = Sheets("MasterList") Dim LastRow As Long
I'm trying to set up a macro which will import data from one worksheet to a master sheet. I need it to copy the information into specific columns but not overwrite any existing information which is already in the Master Sheet, but I don't even know where to begin.
Just so you're clear on exactly what it is I'm trying to do... I have a Master Sheet which lists all of our suppliers prices, margins etc etc... However, when we use a new supplier we send them a greatly condensed version of the Master Sheet - We call it the Supplier Sheet (no big surprises there)!
When the supplier sends it back to me I have to type it all out manually which is kinda time consuming. I'd really like to set up a "push button" system which allows me to simply drag the Supplier Sheet into the workbook, add the info into the Master Sheet, then be able to delete the now useless Supplier Sheet.
(I have attached a test copy of the file - all of the columns in blue are the ones which need the data adding to).
I am familiar how to perform the task of bringing in an access table into excel, then using a data validation filter to control the data set in Excel 2003.
Is it possible to aggregate data in a pivot table from different categories?
I have excel 2003.
For PURE illustration, I have 6 columns, A-F, respectively:
"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.
I want to see in a final output:
Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.
When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.
It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.
