I have a spreadsheet which was created some time ago by an unknown person - I have the password for it and have unprotected it. On some of the worksheets the column & row labels are missing.
View 2 Replies
I have a friend using Excel for Macs 2011 and the column and row headings (ABC & 123) are not showing on the spreadsheets on the screen. How to turn them on. I fgure they got turned off somehow?????
View 5 Replies View RelatedI produced several charts in Excel 2007. They display just fine when using Excel 2007, but when we try to open it in our office computers (which uses Excel 2010), the Axis and Legend Labels simply vanish.I know there's these fixes for Excel 2007, deleting a buggy MS hotfix or installing the KB2597962 fix. But the problem is, I need it to display in our Excel 2010 computers.
View 6 Replies View RelatedI 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.
View 1 Replies View RelatedI have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
I have a workbook. Numbers On The Left And Letters At The Top of all sheets cannot be seen. How can I make them visible?
View 2 Replies View RelatedI use a macro to format a debtors ledger. This report shows the amounts that customers owe broken down by the age of the debt, ie Current, 30, 60, 90, 120 days.
To make it easier to read I want macro code that will replace these mostly numeric headings with months ie April, March, February, January, December.
I want to save a worksheet as a CSV but Excel saves the column headings as the first line of the saved csv file. I don't want the column headings in the saved file.
View 3 Replies View RelatedI 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 UserForm and what I'm trying too do is change the color property of all the labels on the form simultaneously.
View 9 Replies View RelatedSee the attached excel workbook:
SAMPLE_PivotTable.xlsm
The first five columns of my table contain row titles. I'd like to keep these columns stationary while I scroll horizontally through the remainder of the columns.
As it is now, only the row number remains stationary, but I really don't need the numbers, just the row titles.
Is there any way to do this? And would this also be possible with column headings (so I can scroll down without them moving up)?
I want to put in the column headings "listbox" exactly as it is in the sheet, but non-contiguous columns
View 3 Replies View RelatedI have a sheet where a name could appear multiple times in a 22 column data range. I would like to list all the names on a second sheet in Column A and in column B (and further columns as necessary) return the heading name of column where the name is found in cells next to the name.
I think Count If will work for giving me the number of times the name appears in sheet one but is there a way to return the column headings along the same row as the name?
I want to place this forumla "=IF(INT(B3/$U$1)=(B3/$U$1),1,0)" across my worksheet.
Now, I'm okay when dragging down as it works fine with the B3 changing to B4 and the U1 staying as U1.
However, I would like it so that when I drag it horizontally, the U1 becomes V1. I want it so that I can do this and it stays within the $. So when dragging sideways it becomes "=IF(INT(B3/$V$1)=(B3/$V$1),1,0)".
if this is possible and if so, how to do it?
All my columns headings are now numbers instead of letters. how do i change them back to letters
View 5 Replies View RelatedI am trying to write an advanced sorting macro that sorts on more than 3 columns. I need to poulate a combo box with the column headings, so that I can choose which column to sort on first, second etc. Now I am a complete beginner at this sort of thing, but I believe that ranges are required. I have tried using rowsource properties, but the combo box only ever displays one column heading.
View 2 Replies View RelatedI have a rather large workbook with a lot of VBA behind it (about 1MB with no data, just formatting and VBA code). It all seems to work perfectly, except one sheet. It is a list of about 35 options with checkboxes beside each option. When the sheet is made visible through VBA code, the user has problems when scrolling the sheet.
It's hard to describe, but it all looks perfect until either the scroll-wheel or the scroll-bar is used to move around the sheet. The display then goes completely screwy. The heading row (which is actually a frozen pane anyway) ends up repeated all down the sheet, as do the column headers (A|B|C|D|E...). If i select another sheet and then back to this one, it's fine, it appears to be only when it is un-hidden through VBA.
I have attached two screenshots as an example... the only thing I have done between the un-scrolled one and the scrolled one is scroll the mouse wheel down and back up once.
Is there a way I can return a column heading(s) if text is present in a row?
Refer the attachment... ozgrid.xls
Names are listed down the left, headings along the top, with data in the corresponding cells. For each unique name i.e 'Steven' is there a way to list each heading data is found i.e 'Shop2, Shop4'???
I have a table in the format
User Application 1 Application 2 Application 3 ... Application N
ABC aaa bbb ccc ddd
XYZ bbb yyy
LMN ccc ddd
I want to convert it into :
User Application
ABC aaa
ABC bbb
ABC ccc
ABC ddd
XYZ bbb
XYZ yyy
LMN ccc
LMN ddd
I'm using the code below to extract data from a 'Source' sheet to populate a "Yearly Extract Summary" 'destination sheet. With the unique distinct values copied from column I on the 'Source' ("All Data") sheet to column B on the 'Destination' ("Desired Output") sheet. In addition the values from column J on the 'Source' sheet are summed and paste under the relevant month on the 'Destination' sheet.
[Code] .......
The code works fine and the correct figures populate the correct columns and rows on the 'Destination' sheet.
As you can see from the code above, the monthly values have to be hard coded to match the column headings and this is fine when using a static 12 month period. But I'm now wanting to use a rolling 12 month period, which, at the moment, necessitates the need for me to change the code each month so I'd like to change the code but unsure where to even begin, how to produce the initial script.
I'd still like maintain the existing functionality in this section of code:
[Code] ...........
I have attached a file which contain 3 sheets.
The "All Data" 'Source' sheet,
The "Output" sheet, used for testing, and
The "Desired Output" sheet which shows the results using the current code
To run the code, please use the button at the top of the "All Data" sheet.
Sum Categories Test2.xls‎
Simple macro formula. I want to have my function to insert a column and copy over some data headings here is what I have...
Sub Macro17()
'
' Macro17 Macro
'
'
Selection.End(xlToLeft).Select
range("D5").Select
[Code] .........
The issue I am having is that is it inserts the column AA (end of the original data) and I want it to insert at the end of all the data. For example if I run the macro once it posts in column AA, I want the next result to post in AB but it posts in AA.
I need to copy Paste Column A,B,C and D column into the end of the sheet with their headings.
View 8 Replies View Related1) I have 2 worksheets that contain 2 matching name columns; " Date" and the primary column A "Symbol". The rest of the column headings in each of the worksheets are different. Data (from internet sources) is inserted into the worksheets in table format on daily basis
Sheet 1 name = Data 1
Sheet 2 name = Data 2
Master worksheet name = MERGED DATA
2) I use the Match and Index formula for both sheets
the following formula is inserted in row 2 of Data 1 and Data 2 sheets (other than Col A "Symbol")
for column B in sheet 1
=If(Or(ISERROR(MATCH(sheet1_DataLookup,$A5:$A65536,0)),sheet1_DataLookup=""),"",INDEX(B5:B65536,MATCH(sheet1_DataLookup,$A5:$A65536,0),1))................
What is the best way to filter a list of data with dual headers, using both those headers as the filter criteria? I’d like to extract 1 column of data to copy to another location using something like Advanced Filter to select 2 criteria ( Upper header and Lower header), and copy the one column with the matching header values.
The worksheet has the following peculiarities:
- 2-row headers
- Top row headers (main categories) are merged, spanning several columns (I can un- merge if necessary)
- Bottom row headers are sub-categories and have repeating names
Although the top headers are merged and the bottom headers have repeating names, each of the 2 headers combined creates unique labels for each column.
In column B & C, starting at row 2, there are first names & surnames. In column H there is a grade for each name, eg B. Columns AC through AH hold subject codes for each name, eg 18E/Hs1 (potentially all columns could contain a code or only 1). The identifying part of the subject code is the first 2 letters after the / . So in the example the subject code 18E/Hs1 the 'Hs' signifies it is History. A table of subject code and their subject name is contained in a named range called Subject_ID (see below). I intend to make this range dynamic.
Subject .Subject
Code......Name
HsHistory
EnEnglish
ChChemistry
ITIT
PhPhilosophy
MpMaths (Pure)
PtPart-time
PyPhysics
SoSociology
BsBusiness Studies
SSSports Science
FrFrench
BiBuilding.
For each name (starting at row 2) I want to achieve the following: Scan across the range containing the subject codes (AC:AH), identify the first two letters after the / and match it to the subject name in the list. Paste the subject name to a cell starting at AI1 and then insert the grade (contained in column H) for that student in the corresponding subject column. The next unique subject name should then miss a column be pasted in AK1.
Example
......B ......C........H.. AC..........AD...........AE..........AF........AG.........AH.........
Matt........Pia......D....18A/Hs1...18B/En1....18B/Hs3
should result in the word History in AI1 and English in AK1 and the letter D in AI2 & AK2. Note as there is already a reference to History this is not repeated again.
I hope this is clear. I have enclosed a sample workbook with expected output and colour coded the subject names so that the order that they are pasted in is evident.
is there a way I can return a column heading(s) if text is present in a row?
refer the attachment...
names are listed down the left, headings along the top, with data in the corresponding cells. For each unique name i.e 'Steven' is there a way to list each heading data is found i.e 'Shop2, Shop4'?
I have a few row headings on a fresh worksheet (called "New" like so for A1, B1 and C1 for example):
Date Amount Title
I then have raw data on another worksheet. This data has many column headings (more than on the "New" sheet) and the data for that heading in the column underneath heading. So the Date column will have say 50 rows of dates in the column. What I want to do in VBA is match the headings from the "New" worksheet to the raw data worksheet ("Data") and then copy and paste the data into the column under the row heading in "New" from "Data".
with the code for this? I have tried using MATCH and I can't get it to work. I'm also looking for an efficient way to do this I'm sure I am doing it a very inefficient way.
I have a workbook with 2 worksheets, Sheet1 and Sheet2 have the same column headings in Row 1 but they are not in the same positions.
What I need to do is read the column header in Sheet2 Column A (Cell A1) and copy the data from A2 to last row and paste it in Sheet1 in the first empty cell under the same column heading, which may be the Column D position instead of Column A like in Sheet2.
It needs to loop through all the columns in Sheet2, copying the relevant data to under the correct header in Sheet1.
I have a very large spreadsheet that is a consolidation of approximately 105 separate of contacts lists. The new consolidated spreadsheet contains demographic data, and a column for the name of each of the 105 lists. Each contact in the consolidated list had an "X" placed in the column if they were a member of that particular list. Each contact can be in more than one list. This is not very useful for me.
Here's a visual representation of the current list along with what I need:
FName
LName
Address
Phone
City
List1
List2
List3
List4
List5
List6
[Code] .....
I would like to add a column to the spreadsheet and call it "Source"
And then have a macro or VB script to do the following:
1. As shown above, put the column name (ie "List2") in the "Source" column whenever there is an "X" in that row for the corresponding Column.
2. Because each contact can be in more than one list, I need to append the column names so that "Source" column contains all of the column names (Lists) that had an "X"
3. Once all of the list names have been added to the "Source" column, I will delete the list columns.
