When you put a label in a column, that is wider than the column, and you don't wrap it or shrink to fit, etc. it "spills" into adjacent columns so the whole label is displayed. In some cases this might be desirable, but in others not.
You can prevent it from spilling by putting a blank space into the next adjacent column -- that truncates the display of the label -- but clearly this is not a good solution.
Is there a way to prevent labels from doing this so that only the portion of the label that is in the cell where the label is entered is visible on the spreadsheet? Of course you can see the entire label by selecting the cell itself, and that's what I want to do without it spilling into other columns.
I inherited a spreadsheet from someone so trying to undo some of their features. Normally when you type in a long string of text into a cell it will automatically spill over into the adjacent cell(s). This workbook was set so that the text would be truncated (NOT wrapped) even though the adjacent cells are completely blank.
So in cell A1 I've typed "1. Update "TEP" "EP" range every quarter." and even though cells B1, C1, etc are empty, all I see in cell A1 is "1. Update "TEP" "E" I don't want to increase my column A width. How do I undo this so that the text well freely spill over into the adjcent columns?
I was wondering how I might be able to hide columns based on their 1st row labels with vba - I never know where these columns might be on different file I get but I want to hide the same columns every time. right now I am just specifying column numbers or letters but it's not working out:
I'm trying to come up with a way that will populate a table after each cell reaches a certain value.
My first column has rows with the total time required to perform a certain operation. The columns after that, represent operators that should be picking up a certain amount of tasks that do not exceed a set time.
Let's assume that the first row (total operation) has a total of 550 sec. The second column (1st operator) should assign a a value of no more than 200 sec. The third column (2nd operator) another 200 and the fourth column (3rd operator) another 150. So as to create a spill over effect. The second row has a different operator with a different time. Let's say 300. These tasks should be picked up by a fourth operator with a value of 200 and then a fifth with 100. All in all I would like the table to populate as below.
i run a Excel spreadsheet that adds payments that are made, and i have a row put there so i can make notes. sometimes these notes are quite long and if the cell to the left of it is empty the text will continue going across these cells and make my spreadsheet a real mess to understand. how do i make text stay only in it's given cell?
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1 2 3 4
Here's what each of those "mean" (I have this in another table):
1 - Sick 2 - Overtime 3 - Court 4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
I have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.
I am trying to create an invoice but the invoice show #N/A in certian columns when there is no number which already have formulas, how do I modify them using the if function?
I have a sheet that uses Blocks of cells eg: A1:X10,A12:X22 etc to hold data/text etc.
I am filling colums A to V with data and Ranking and summing them in W & X, I don't need to show K, L M ...for example, until they are totally filled, so I hide them manually until needed.
Have a Macro to print "results" A1:X10,A12:X22 etc, and I set a "print area" or "print selected"...works fine to fit to 1x1 Page.
Problem: If I hide L, M, N etc, the macro runs fine but still uses the unhidden size of the selection to fit the page ie: I get A-B-C-D...-W-X as required, but the other half of the page is blank, because the size selected is for all including the hidden columns.
Q: How can I (Simply) print what's visable to 1x1 Page, fitting more in each time I unhide a column or two.?
I am instantiating Excel 2003 from vb.net and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?
Dim rng As Range Dim i As Integer, counter As Integer
Set rng = Range("1:1")
i = 1
For counter = 1 To rng.Columns.Count
'If cell i in the range contains an "x", 'delete the column If rng.Cells(i) = "x" Then rng.Cells(i).EntireColumn.Delete Else i = i + 1 End If
Next
My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?
One of the fields I want them to enter is a library branch and I have used a data vaslidation list to provide a dropdown box.
To make it act more like a windows drop down box I have hidden 7 cells above this box with the 7 possible entries. This means that with autocomplete, if they start typing in the cell it will autocomplete the entry for them.
This work fine, but if someone presses enter from the cell above my hidden rows, one of the hiddenn cells is selected! WHY!
I have no explanation for this behaviour, nothing like it has ever happened before, and I have no idea how to get rid of it.
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
I have a feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.
I have a userform with several textboxes and 2 comboboxes. The data that I need protected from duplication is based in the comboboxes. If I ignore the other fields and presume the following it may be easier to understand:
The comboboxes are populated by the data stored in sheet1 and when the userform is complete, the OK button populates all the data into sheet2
Combobox1 contains names eg, John, Julie, Bob etc Combobox2 contains colours eg, Red, Blue, Green etc
I want the userform to allow the data to be entered into sheet2 only if it is not an exact duplicate of the choices in BOTH of the comboboxes
For Example John Red Julie Red Bob Blue John Blue
These are all ok, however, to try and enter John Red again would bring up a message box indicating a duplicate entry and prevent the data from being entered.
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2() Application. ScreenUpdating = False num = Sheets.Count Sheets("X-Axis").Activate Range(Columns(1), Columns(256)).Select Selection.EntireColumn.Hidden = False For a = 1 To 5 Sheets(num - a).Activate If ActiveSheet.Name = "A2 Data" Then Columns("A:Q").Select Range("A10").Activate Selection.EntireColumn.Hidden = False Sheets("X-Axis").Activate Columns("A:E").Select......................
In Excel 2003 you could use Labels in formula by enabling the Tools-->Options-->Calculation-->Accept Labels in Formula setting. In 2007 I can only find an option for using table names in formula. Is the use of standard labels no longer valid in 2007? If it is where do you enable it?
I have a basic bar graph showing the average home prices per quarter. I know I can turn the legend on and position it to show the average price on top of each bar in the graph. I would like to add an additional legend (on this chart) showing how many homes sold per quarter but not display it as a bar. I just want the number displayed maybe on the bar or below the bar. Can I add a separate (extra) legend?
My excel file brings up a userform as soon as it is opened. The user chooses an entry from the ComboBox and then clicks OK. This brings up another userform which has a Label at the top. I want the caption of this label to show the entry from the ComboBox on the first userform. This should be straightforward, but what I've done so far doesn't work!
I've tried:
Label1.Caption = ComboBox1.Value
within UserForm_Initialize(), but it doesn't work!
I have a file with a small example of sales and % of sales for about 7 persons.
I wanted to insert a pie chart but want to show labels inside the pie chart along with % of sales and the sales person name near each part. How to do this ?
I just received a massive worksheet full of information for mailing labels. It's all in columns-which is excel. Is there an easy way to set this up for mailing labels? I've tried to import it into word, but I'm not getting anywhere. Is it possible to change the way excel displays the cells? To a point where I can have the address beneath, then the state, zip, etc?
I have a PivotTable that is summarizing a data table. In the configuration panel, I've dragged one column ("C92") to the "Row Labels" area. Likewise, I've dragged the same column to the "Values" area, where it defaults to "Count of C93". So far, so good.
I should mention that my values in column C92 are the whole numbers 0-7 and the string "NA". But in the output chart, each of these values is listed twice! And each count of each value is different! I'm attaching a cut-down version of the spreadsheet that shows this behavior.
