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)?
My questions is how to "freeze pane" a row of headings on the Results page and post the results onto row 2 and downward *without* deleted row 1.
If you look at the attached file, you will see a tab that I created called "My desired Results Page" that simply has a frozen row of cells. Instead of just displaying the rows from the Details page on the Results page, I'd like them there but with the headings at the top.
Is there code that can do this?
I 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 been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.
The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.
What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.
I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.
I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....
If anyone has any suggestions I would be eternaly greatful.
Passwords on the sheet for the different buttons are abc or cobra.
You can download my Spreadsheet here.
[url]
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 RelatedPrivate Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 1) = Now
End If
Application.EnableEvents = True
End Sub
everytime i lock the column a by using the protect sheet, the vba does not work or debugs. is there a way for me to lock the cell in column a when data is inputted or changed in column b? i am using date and time for column a
I 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 want to compare two columns. I would like to see if the contents of column B appear anywhere in column A, for any amount of rows, and if it does, to place the match in col C.
So in the example below, red in B1 would be checked in A1 through A1000 or however long A is. When any row has red, place that match in that row for C.
This would be repeated until all rows in B are complete.
Example
Col A: red
Col B: red
Col C: red
Col A: red
Col B: blue
Col C: not found
I have set a set of cells to calculate dates from other cells and locked it so that people can not edit the date. The date will be automatically populated. However when i lock the cell, a debug error comes up says runtime 1004. I reckoned that when the column or cell is locked from editing, it will also unable to populate or run calculations. am i right?
View 9 Replies View RelatedSee the attached excel workbook:
SAMPLE_PivotTable.xlsm
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 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 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.
I'm creating a map like image with Excel, and I've set the column width and row height to certain dimensions. Now I'm entering in some text into them, and I noticed that when I added in 2 digit numbers, the column width expanded a little bit. I want to prevent this from happening, and still be able to enter in the 2 digit text (since it WILL fit, and it's expanding to keep that extra bit of space).
Is there any way to lock the dimensions?
I'm using Excel 2003 and was wondering if there was a way I could Lock/Freeze the contents of a column. For Example:
Cell A1 has the formula =B1+C1+E1 - figure shown is £405
Cell A2 has the formula =C2+D2+E2 - figure shown is £650
Is there anyway I can freeze column A so when I delete column B,C,D,E ect the correct figure will still show?
In Excel 2007 I protect cells in certain cloumns in a large amount worksheets. By manually protecting or locking them. Once the sheets are protected without a password just blank and only select unlocked cells is ticked for sheet protection. I close the workbook and reopen it. These cells are now unprotected that were protected and theres always two ticks checked off for select locked cells and select unlocked cells.
View 9 Replies View RelatedIs 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‎
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 RelatedSimple 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.
