I'm using Excel 2007. I know how to protect specific cells within a worksheet, but I have not been able to find any information online pertaining to locking or protecting inserted header/footer data, even if I protect the worksheet.
I'm not concerned about setting specific VBA commands to ensure the header/footer prints, but rather preventing other users from being able to edit the data upon distribution of the worksheet.
I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.
I'm using Excel 2007
The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)
The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .
It's just six columns of data. The short list is in AY from AY2 : AY15
The long list is in BB from BB2:BB505
The column header "AL" is in AY1 and NL is in AZ1
Possible to have formula in Header or Footer in excel 2007?
I want to put concatenate formula. Eg. "=concatenate(weekending," ",'sheet1'!a1)"
Using Excel 2007. When I put password protection on the template, the template child doc also gets the same password protection. I want to prevent tampering on the template, but I want the child doc to be fully editable for the end user. After all, isn't that the purpose of a template? I just don't want anyone to be able to edit the template itself, which also seems like the purpose of a template. Am I missing something about not seeing how to configure this obvious functionality?
View 1 Replies View RelatedI know how to protect a worksheet with a password so that no one can amend the contents or view the formulas in the cells. And then we can un-protect the worksheet by clicking on the "review" tab in excel 2007 and so on. But recently I came across an excel sheet which was password protected and my query is that the "Review" tab was greyed out. What kind of protection was this that clicking on "review" tab option is also unavailable. And how does these kind of sheets get unlocked then.
View 2 Replies View RelatedIn Excel 03 I could just password protect a template and prompt for to open the template at read only is there a way to do the same thing in Excel 07?
View 1 Replies View RelatedI'm trying to incorporate a Cell result in my header (the one you select from "Page Setup"). Is this possible? I would like to avoid macro if possible and prefer some type of formula.
Windows XP with Excel 2007
I have a lot of formulas that I don't want to be changed or deleted, but I still want the user to be able to enter data into the cells.
If I use "Protect sheet" I can't select a cell or enter something into it. I sure there is some setting I have to change, but I'm not sure what to select.
I'm using excel 2007
I have an intern that is working with me, and I have a file that I need updated but one of the columns has confidential information so I would like to just hide & password protect this column this way he can't access/view this information but still have access to modify the rest of the spreadsheet.
Is there a way to do this in Excel 2007?
currently using Excel 2007 with Windows Vista.
I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).
Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))
which I found under: Find row, find value, then return column heading
However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.
The worksheet layout is as follows:
Date
A
B
C
D
Date
Rank
Header
[code]....
The return value under Header should be C.
I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
I am working on a "3 worksheet" excel workbook. The first worksheet does not require any header.
I'd like to enter data into the second sheet (say cells A1 and B1), and use VBA to pull from those cells to generate the same custom header for both the second and third worksheets.
For example, I'd like the header to pull "# 123456" from cell A1, and "789" from B1 in sheet two, putting them in a centered header for both sheets two and three (same reference cells from sheet two for both, not new values of A1 and B1 from sheet 3 for sheet 3 header). I'd like to format in a way that looks something like this:
#123456
789
I'm currently running Excel 07, and was able to pull from a cell on one worksheet into that sheet's header but couldn't get it to span multiple sheets.
I have an Excel 2007 workbook which has five sheets in. I just wondered whether it is possible to hide all five of them when the workbook is first opened. Also I would like the user to be asked for a password when they wish to unhide a sheet, with the password being different for each of the sheets.
Is there also a way to do this which won't be affected by Excel disabling all Macros when the application initially opens.
I've got a workbook where I need to protect certain cells by locking them.
The trouble is when I do this I lose the ability to apply formatting and to insert symbols in other cells?
I've checked the obvious options for when you're locking cells but nothing seems to be stopping this there.
Is there a reason it disables these options, a work around or am I just being dumb?
I'm using Excel 2007 by the way.
Is there a way to protect the Header or the Footer from deletion? (or changes) I don't neccessary want to protect the whole sheet.
View 10 Replies View RelatedThe other day I inserted a row in a shared workbook and then saved it. On my screen, the inserted row was visible and was the correct row height of 12.75. My goal was to insert the row so another user sitting next to me could enter information in the new row.
After I saved the workbook, and after the user sitting next to me saved her workbook, the row I inserted should have appeared exactly the same on her screen, but it did not. Instead, the inserted row showed up as a hidden row on her screen. It was there, but the row height was collapsed to the point of invisibility.
I repeated my attempt to insert a row which the other user could edit several times, but each time I got the same result. I could not make the row show up on her screen with a row height of 12.75.
Shared workbook in Microsoft Excel 2003?
build a spreadsheet that reads information off of a Master sheet onto 4 other sheets. The hope is that by making changes ONLY to the Master sheet that the other 4 will update automatically.
And then we ran into the trouble of not being able to insert new lines onto the Master sheet without throwing everything off on the other sheets.
(I've attached an example.)
For instance, if I go to the Master sheet in the Test.xlsx attachment, I've left out Lima from the alphabet. So, I insert a new row onto the Master, switch back to the Formula sheet where it should (theoretically) just update the cells to display the new data.
Not so. The Formula sheet just skips the new A13 and keeps on going.
the primary one seems to be using Offset. Well, the coworker will be adding and deleting many, many rows over the course of the year, as it is a product log and we change our products often.
i have a question with regards to the shading of a table in excel 2010.The grey/white shading of the sheet should adjust automatically when lines are inserted / deleted...what would be the most efficient way?
View 6 Replies View RelatedI send these kind of mails to intimate people about upcoming meeting, the format I prepare in Excel 2013 and paste it in Outlook 2013 (as a table, not as an image)
Now one of the columns I put as 'Days Remaining' which basically tells the users how many days are there to the review, the image below will show how it looks:
Now the numbers of days remaining will be correct the day I send the mail, but when somebody opens the mail at a later date, it would not sort of show the true number.
Is there a way to auto-update this number inside the Outlook mail, as it works in an Excel Sheet?
I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
View 1 Replies View RelatedI want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.
=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)
But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.
I am using Excel 2007 on XP.
I am building a rather good size userform. It is getting really lengthy code-wise because I want certain things to be enabled and disabled on the form as they click on certain options.
I know I can hard code the lines but so I am trying to come up with some coding elements where I will not have to add 20+ lines of code for everytime I want to add a new feature.
Here is one of the functions that I have, There is going to be several of these:
Private Sub chkMore2_Click()
If chkMore2 = True Then
cboProtocol2.BackStyle = fmBackStyleOpaque
cboApplication2.BackStyle = fmBackStyleOpaque
txtLowPort2.BackStyle = fmBackStyleOpaque
[Code] .......
So If I click chkMore2 ... all of the 2nd elements will be editable. If I click chkMore3 ... then all of the 3rd elements will be editable, etc. etc.
So I tried to play with this code example:
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Call Changeit(1, "yes")
Else
Call Changeit(1, "no")
[Code] ........
Excel 2007 does not like the .concatenate element.
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
I have a workbook (excel07) that I use to import data onto our purpose built database. I have some macros to sort the data and send it to the correct columns however the one stage I still have to do manually is assign each row its own unique sorting Code (in a bespoke column "B2:B999").
These are objectives and the code should contain three seperate parts (part 1a and b do not need to be seperated);
1a. The characters "PO" should be the first in the code (for sorting purposes)
1b. The persons initials (first letter of the first name and last name) found in column J2:J999. [They are sorted by name so It would also be good to add a number after the initials seperated by a space incase of multiple objectives however I could defintly live with typing these in manually]
e.g. "Joe Blogs" second objective = JB02
2. The numerical part of the stategic objective they have selected found in column H2:H999. [incase you need this these range from 1.1-1.4, 2.1-2.5,3.1-3.6 and 4.1-4.5 all with a short text strings after which shouldnt be included in the code]
e.g. "1.1 We are Committed to being nice" = 1.1
3. The year they are applicable for which will all be "12/13" however I will need to change this next March.
Therefore the final code should appear as "POJB02 3.1 12/13"
There is a 16 Character limit on these codes but all of that information is needed for sorting, filtering and report generation, the spaces could be replaced by "_" if that makes things easier.
I am using Excel 2010 and have the problem as shown in the attached file.
Input Sheet shows the Data I have at present
Output Sheet is the desired result.
I need a macro which should create an "Output" sheet by doing the following on the Input Sheet
1)Insert a Blank Column before Column A
2)Unmerge the Region Heading and insert the respective Region Name in the newly inserted Column. Region Heading will be in Bold Font.
3)Repeat Step 2 for all Regions
4)Delete the Rows which was merged.
Please note that the number of Data Rows will vary for each Region.
I have shown two Regions for explanation purpose only. There will be several Regions in reality.
The result is shown on the Output sheet
Merge Problem - Forum.xlsx
I'm currently working on a spreadsheet for my husbands work in a school environment. All was going well until I hit a wall with the alphanumeric data of the current uk stats system. Is there any way of getting my 4c...4b...4a...etc. to chart?
View 1 Replies View RelatedExtracting data from an excel file, the data needed in lies in one column.
in this line
<GPSPosition pntRef="GPS_Auto_0080" wgsLatitude="33.334329659885" wgsLongitude="44.405736558207" wgsHeight="27.615921">
i need to get a table with three columns shows
wgsLatitude, wgs Longitude , wgsHeight
33.334329659885 ,44.405736558207 , 27.615921
etc
and get rid of all other information
this is an example of the how the file look like
---------Column N -------------------
</Feature>
</GPSPosition>
</GPSSetup>
<GPSSetup id="GPSSetupID_18" GPSReceiverDetailsID="GPS_1" antennaHeight="1.660000" stationName="GPS_Auto_0080">
<GPSPosition pntRef="GPS_Auto_0080" wgsLatitude="33.334329659885" wgsLongitude="44.405736558207" wgsHeight="27.615921">
[code].....
seems from above that there are too many data not needed the only thing need to be exracting is one lies of GPSPosition Line.
I have a table with names of employees in Column A, the data for each employee is written in columns B, C and D.
I'd like to be able to type in this data in cells E12-E14 and have Excel bring up all of the names in the table that are associated with this data, and preferably separate them with commas.
I'm working with Excel 2007, without VBA/Marcros.
Please see the example file: find_name_example.xlsx