This is probably something very simple, but I need to find a way to stop row heights expanding when text in a cell wraps. Basically, I have a spreadsheet of projects, and one column is for 'notes'. When the text in this column starts to wrap, Excel automatically increases the row height to make all the text visible - but I don't want that to happen.
I guess there's a button to press somewhere, but I can't find it!!!
Using Excel 2003
I need to set row heights to 35 and 10 respectively for say 1000 rows. The macro I created to test a few rows only works on the specified ranges.
Sub Macro1() ....
I have a program that generates a spreadsheet report. The programs output is sloppy so I am developing a macro to format it before printing. The spreadsheet creats a variable amount of records (one to several hundred) and each record has 3 rows.
The issue is; I don't know how to format the sheet so that each of the 3 rows has a specific width which would continue down consistantly for however many records are there?
I suppose I could simply format the entire sheet... but this seems clumsy :-)
*I have my document ordered hierarchically with group functions.
*Aesthetically I would like to have the font size get smaller the more levels into grouping they are.
*For aesthetic reasons I have merged cells rendering auto-fit useless as it auto-adjust to the first column's font size even if it has no characters in it
I find myself contemplating manually adjusting all 500 some rows of data.. probably by selecting ranges with ctrl or shift clicking my way through the document, grabbing ranges based on how high their grouping is, and adjusting the whole selected group at once, so they all have a uniform height setting.
Can I do some kind of conditional formating based on group level? like all group level 7 rows have a row height of X.
Code + Button to automate certain row heights.
1. Starting in row 5 the row height is to "30"...every 17 rows after row5 to have a height "30"...next row 22 is "30" , then row 39 is "30" and so on...
2. All rows in between row height "30" will be with a row height of "11"
3. Can this be associated with a button....
4. where would i place this code.....
Version:Excel 2010
Is there an option to have row heights set automatically based on the content of the text in the row, and given a specific column width?
I can right click, and set each row height individually.
I can select serveral rows stacked one on top of the other, each with limited text, and change all of those rows at the same time.
But when I select all rows, and try to change row height, the row height option is not there.
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
View 4 Replies View Related[qoute]I have five cells that I am working with cells A,B,C,D & E. cells B & C are time formated. Cells D & E are number formated and equal the number of hours between cells B & C. the difference is that if cell D is less that 2 hrs then cell E is to equal 2 hrs but if cell D is over 2 hrs then cell E equals the value of cell D. All this works fine but I am needing to add in the value of cell A. So if I have say CT in cell A then cell E will equal the value of cell D no matter if is less than 2 hrs[/quote]
View 5 Replies View Relatedin this example below i have record entrys of tps,rpm,etc etc in time.
typically 4 records every 1 second.
how can i make the data 10 rows for every second?
i.e can we take the column readings after D i.e row2 then at row4 and then insert 8rows between the 2 and enter averages.
1353ecu *ABCDEF1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPM21########13:47:190.55517.25491406.2532########13:47:190.80416.078431531.2543########13:47:191.03716.470591437.554########13:47:191.27223.529411343.75 Excel tables to the web >> Excel Jeanie HTML 4
the whole sheet is pretty big
1353ecu ABCDEFGHIJKLMNOPQ1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPMBatteryCoolantTempAirTempTimingAdvO2SensorSpeedInjDutyCycleKnockSumLoad1BOctaneFlagknock_flag21########13:47:190.55517.25491406.2513.63938195.8122210.956483.2854850031.2100032########13:47:190.80416.078431531.2513.71271195.8122200.897923.2854850025.2100043########13:47:191.03716
copying a document that was made in word into Excel. I set the print area and page size but it seems I can keep adding columns. When I check the print preview it still shows the one page but it appears wider.Will it automatically create a new page if the page size limit is reached?
View 2 Replies View RelatedIs it possible to make a self expanding table. What I have is a sheet with a table with loads of biking tracks with track number, date, nr of miles and so on...
I'd like to make a table on the second sheet so that I can see only the tracks that auto fill as I put in info about number of miles or date or track number.
And I'd like it to auto expand so I don't have a lot of wasted space on the page. I'd like the TOTAL amount of miles, when i put in multiple tracks, to move up or down when I add or remove tracks...
I have a bunch of nameds ranges that are essentially entire rows. For example, Rows 1 through 5 are named range "Range_A", "Range_B" is Row 10 through 20. I was looking for a way that I could have the value of one range assume the value and size of another range.
For example
Range_A = rows 1 through 5
Range_B = rows 10 through 20
Now, if I use the following macro all that happens is the first five rows of data is copied over to Range_B while Range_B's other 5 row values are not copied over.
Sub Example()
Range("Range_A").Value = Range("Range_B").Value
End Sub
What I want to have happen is have of Range_A expand to the same number of rows the other range has (which is 10 in this case) and assume the value of Range_B. Is there a way to do this? In my workbook I have a number of ranges that need to be replaced with other ranges and I want them to assume the size and values of the range they are supposed to equal.
I have a script that converts any List based Data Validation cell into a combo box when you click on it...What I am trying to do now is find a bit of code that will auto expand the combo box.
View 3 Replies View RelatedI have a problem in expanding/collapsing fields in pivot table.
I have source table with GROUP, SUBGROUP, ACCOUNT and AMOUNT. In pivot table I've put GROUP, SUBGROUP and ACCOUNT in Row labels respectivly, and AMOUNT in values. There are same names in SUBGROUP for different names in GROUP. (for example: groups are Production costs and Distribution costs and in each of them there are subgroups Personnel costs and Other costs).
The question is: Is it possible to expand only field Other costs in group Distribution costs while field Other costs in group Production costs stays collapsed?
I have a list (C5:C16) and I want people to be able to add more names to the bottom and it add to a dropdown list on another page.
View 6 Replies View RelatedThe table below has three rows and 7 columns. Row 3 shows cumulative sums of the values in Row 2. Sometimes, Row 2 may not have values (not even a 0). The formula for C3 is =SUM(B2:C2) and =SUM(B2:G2) for G3. The challenge is that I have a very long row and summing it individually would be inefficient.
Is there a way of automating the formula so that the cell ranges expand automatically which I could use to fill to the right?
A BCDEFG
1Row 1123456
2Row 21041503
3Row 3115202023
When not programming I sometimes use very long commands (e.g. multiple conditions with data importing from closed files). The command line in the rule expands such it hides the columns headings making the writing very uncomfortable. is there a way to force the line to be wider without spilling into the data (like another ruler line)?
View 3 Replies View RelatedI have data coming into my sheet from an Access file into columns b:l which automatically get covered by a named range which changes according to the size of the data coming in.
What I'd like to create is a named range which includes this original range as well as columns a and m:p. Is this possible using Offset at all?
On top of this my data from Access comes into row 2 and I'd like the named range to cover row 1 too.
I have thousands of phone numbers that hundreds of them are look like this:
123456-8 that means : 123456,123457,123458 or this
123456-57 that means : 123456,123457
There are not any standard of numbers after dash (-) in my data.
how can I expand them to new "rows"?
bob 123456-8 ->
bob 123456
bob 123457
bob 123458
So I have recorded a macro that filters for certain criteria in a master list of projects, then copies the projects that meets this criteria and pastes it into a timeline in another worksheet. However, because the list of projects gets bigger and smaller when the filter criteria changes, right now I have to create twenty extra rows so that the pasted list doesnt ourgrow my timeline. How I could input a few lines in my macro so that the table for the timeline expands as the number of prjects grows or shrinks.
One method I have tried is to copy the filtered data and "Insert Copied Cells" into the table, however I don't even get the option to insert copied cells after I have copied the data...
So, I have a list of data like this:
Type
Variety
Country of Origin
Price
[Code]....
Obviously I can do this using text to columns and transpose but typically I'm dealing with much bigger sheets where being able to do this automatically is a big time saving.
The column with commas in it can vary in position so I'd like this to work on the cell/column that is selected.
Ideally I'd like it to work it's way down the sheet expanding out every comma separated list in the selected column in this fashion but even something that worked on the current cell would be handy.
I think roughly I need to split the cell into an array using the comma as a seperator, possibly scrub the spaces from the text, copy the row as many times as there are things in this array (using ubound?) and then transpose the array into an area starting at the selected row and going down as far as there are things in the array but a bit lost with actual implementation.
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
[Code]....
Why do my named ranges expand each time I import data from Access? I then have to go my named ranges and reset them. The reason I am using named ranges is because of the indirect functions I am using.
View 9 Replies View RelatedI have a table of data to which new figures are added each month as follows:
MonthHeadcount
Jan-081867
Feb-081942
Mar-082020
Apr-082020
May-082020
Jun-081980
Jul-081941
Aug-082000
Sep-082000
Oct-082060
Nov-082060
Dec-082143
Jan-092101
Feb-092101
Mar-092186
Apr-092208
May-092319
Jun-092389
Jul-092437
Aug-092486
Sep-092462
"Month" is in A3, "Headcount" is in B3. Figures are on rows 4:24
What I need is a formula that will average the last 12 rows of data so that when a new record is added, the average will update automatically to include the newly added row, but still only look at the 12 rows (i.e. for the data as listed it would average rows 13:24 (Oct-08 to Sep-09), but when data for October is added, it would average rows 14:25 (Nov-08 to Oct-09).
I have this source am using in a validation list.
=INDIRECT(SUBSTITUTE($A$2," ","_")) and the source points to A2 cell.
My problem is I need to expand this code to the next say 50-100 cells below and when I try to code the cell the next cell refers to the same A2 when I need it to refer to next corresponding cell (Eg A3)
=INDIRECT(SUBSTITUTE($A$2," ","_"))
what I need is below:
=========================================================
Column A Column B
A2 =INDIRECT(SUBSTITUTE($A$2," ","_"))
A3 =INDIRECT(SUBSTITUTE($A$3," ","_"))
A4 =INDIRECT(SUBSTITUTE($A$4," ","_"))
I would like to use some VB code to search a Pivot Table, find a specific value and show the details relating to the value. I used the following code I have obtained from another thread in the forum
Sub Expand_Pivot_Values()
Dim rngFind As Range
Dim rngPTData As Range, rngPTC1 As Range
Dim rngF As Range, rngP As Range
Dim strFind As String
Set rngFind = ActiveSheet.Range("J3:J6")
'set the pivot table data range - see help file for property description
Set rngPTData = ActiveSheet.PivotTables(1).DataBodyRange
'set start cell for find
Set rngPTC1 = rngPTData.Cells(1)
For Each rngF In rngFind.Cells
'set variable to find........................
This code works fine but only identifies the first time the value appears in the pivot table and shows detail relating to that value. I need add to the code to search the pivot table and find recurring values. For example if the value is "2" I would like to show detail on each "2" value there is in the pivot table.
I have a problem setting up an offset range on my charting example ( attached).
My X axis will always be row A
I want my charting range to be more than just the one column and since the months will keep expanding I'm looking fopr the easiest method to do this.
My range could start at 1 row and finish at any other. User Defined.
Bu I want that whole range to be included in the charts. This is a stacked chart.
So in my example I'd liek the shaded bit as a stack chart. 3 bars for 3 months with 4 stacks in each one.
I have an Excel sheet that lists all of my dvd collection.
Its very easy to use, all I need to do is type the movies I have then click a sort button.
The everything goes into alphabetical order.
The problem I'm having is it only goes to 999 I have tried to extend it to 3000.
I can't seem to find the formula to allow me to extend the perimeter past 999.
I have to compare and combine data from multiple files and combine it into 1 master spreadsheet. I have attached a sample file. It is in bulgarian, but the language is of no importance. An example of the file: it contains data about repair maid on a truck. First column is just the number of the repair type and shall be filled upon completion of the table. the second column is the name of the repair itself. the 3rd column is the date the repair was made. the 4th column - the total work hours the repair was made. Some repairs were made more than once and thus the merged cells 3 to 9 in column B. I have 30 trucks which have more or less the same repairs, just a different number of each repair. A sample file of 1 truck is attached, called 1truck.
The master spreadsheet should be of the same type as the example, however showing data for all trucks (from different files) next to each other. (the attached "alltrucks") As you can see there are repairs done only on some trucks, and others are done multiple times. The master sheet should have all possible repairs from all trucks. So far I've been doing it manually, but takes way too much time...
I'm using a Combo Box the contents of which are on another worksheet.
This data is pulled in using a database query(MSQuery). The data source could be 5 lines long or thousands of lines long(Customer dependant).
I have tried specifying the listfillrange using $G5:$H8700 for example which works for the current dataset. If i change the dataset which only has range G5:H87 then the properties of the combobox listfillrange change to $G5:$H87 but the actual list has more than 82 lines and displays the 82 lines over and over again. I haven't counted the number of lines but it could be thousands.
I have two pivot tables where if one expands a subtotal (in this case 'manufacturer') to give the lines below (products made by the manufacturer), is it possible for this to be mirrored in the second pivot? I noted a previous comment on using slicers across two pivots but I cannot do this in this instance, although I am using Excel 2010.
View 1 Replies View Related