How To Protect Alternate Cells Of Column
Apr 16, 2013
Is there any possibility to protect alternate cells of column see below for example. I want to lock cells B1, B6, B11 and B16 only so that no one is able to delete the average rate.
A B
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
View 3 Replies
ADVERTISEMENT
Nov 15, 2013
Can I protect cells so that people can't change them, but at the same time they are able to delete rows and columns?
If I remove the locking and use hide only then the cell why go empty if someone double cliks it.
View 8 Replies
View Related
Nov 29, 2013
I have a single column of data and need to convert it into 2 columns, by alternating each row:
1
2
3
4
5
want to convert to:
1
2
3
4
5
6
I've seen a previous post from 2007 where someone asked for a formula to do the reverse of this. 'Domenic' provided a formula =INDEX($A$2:$B$6,INT((ROWS(D$2:D2)-1)/2)+1,MOD(ROWS(D$2:D2)-1,2)+1) which converted
a
1
b
2
c
3
d
4
[code]....
how to reverse this formula?
View 1 Replies
View Related
Apr 24, 2008
I need to sum the contents of every fourth cell in row A .... ie sum(A1 + E1 + I1 ... BY1)
Can I do this via the formula bar, or do I need to use a VBA routine?
View 9 Replies
View Related
Apr 18, 2013
I have a row range to sum only alternate cells.
How can i do this job by means of formula.
View 9 Replies
View Related
Jul 17, 2006
I can't remember any formula to do the task. I have a time series with quarterly sales. The dates are in format given below.
01/01/1990 - first quarter of 1990
02/01/1990 - second quarter of 1990
03/01/1990 - Third quarter of 1990
04/01/1990 - fourth quarter of 1990
I need to add the sales of all first quarters , all second quarters..and so on.
Is there any formula that an elegant way or I have to do it manually?
View 5 Replies
View Related
Jun 19, 2007
I have a table of data that is laid out in multiples of two columns. I have attached a simplified example. Essentially all I need to do is count the contents of every second cell when it equals a specific value. If you look at my attached example it will be clearer. how I can do this using formula? (I don't want to use VBA in this instance).
View 7 Replies
View Related
Mar 17, 2009
I have a spreadsheet for work rosters. Each person on the roster has a row with info on what they're doing for a given week. Split into the am and pm session of each day.
All I want to do is be able to fill the row with the same value if they're doing the same thing for the week - eg if they're on leave, I'd like to hit CTRL-L and have the row populated with 'LEAVE' in each alternate cell, rather than typing it manually. Note that it is each alternate cell, not each cell as the alternate cell has different info.
So a row would look like this:
Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |
For the am and pm session of the five working days. And just to complicate matters, each 'cell' on my spreadsheet is actually 4 merged cells, and the alternate 'cell' that I want left alone is two merged cells! This is the macro generated when I do a simple record - it does what I want, but obviously jumps to the original row that I recorded it in whenever I run it - I need it to fill the row that I start it from. I recorded starting in cell I133:L134
View 3 Replies
View Related
Jun 14, 2007
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
View 3 Replies
View Related
Jun 20, 2007
Here's another question for you excel junkies to solve, while I try to understand what y'all are doing. I have two worksheets named "MASTER" and "BEDS". What I need to do, in sheet "MASTER" is go down column ("K:K"). "K:K" has unique data in it. then for each row...
"X" .value = offset minus one
"Y".value = no offset
now I need to go to sheet "BEDS" and find "Y" in column "A:A" then
from "Y" offset + 3.value = "TRUE"
"Y" offset +4.value = "X"
there should only be one instance of "Y" in column "A:A"..............
View 3 Replies
View Related
Dec 22, 2005
Would like a script that will go to each sheet in the workbook, and fill in light grey background, each alternate row that contains data, EXCLUDING the 1st row (header row).
View 9 Replies
View Related
Oct 27, 2008
I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.
The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.
For instance:
Column A
CPST4
CPST5
FLADS3EC
FLADS2EC
FIL4
CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.
MacOffice Excel 08 is used so no VB code can be used.
View 9 Replies
View Related
Dec 6, 2006
I have a spreadsheet that I would like to loop through column "C" and if criteria is met copy and paste A:C on sheet1 to sheet2 over multiple columns alternately. What I mean by alternately is that I would past the first row in column A6 then the second in E6 then the third in A7 and so on until all items are copied. I purposely left a blank column between both columns of information. I've tried sorting/and advance filtering and couldn't get it to work.
Example:
loop through column "C" If I have the Letter "A" copy data to column "A" and "E" alternately back an forth until I no longer meet the criteria. I start putting data on the 6th row due to header information in rows 1-5.
... If column "C" is the letter "B" copy to column I,M,Q,U
Lastly I could always have less rows of information than I do columns. SO the last column could be empty. I always sort my data by column "C" so data will be sequential.
View 9 Replies
View Related
Jan 8, 2010
I have tried to go around the long way to achieve this but came up with pages of pointless code .... I know there is a better way I just dont know enough about VB to do it myself ... And I know this is EASY for many :-)
--------
Cell ranges h11 to as11 are a totals row.
If the total is 0, colorindex is set to vbpatternnone, if >= 1, then colorindex is set to vbpatterngray. Easy right ? I just dont kn ow how to do FROM/IF/DO range loops...
--------
Details:
The code in worksheet_SelectionChange will contain the following:
1: From range h11 to as11, variable1 = application.interior.colorindex of the cell.
2: Check if the cell is >=1 or <=0 ....
3: If >=1 then set application.interior.colorindex = vbpatterngray. Go to #5.
' (This inserts a pattern over the original color of the cell)
4: If <=0 then set application.interior.colorindex = vbpatternNONE
ALSO set application.interior.colorindex = variable1
' (This clears the cell pattern and returns it to original color)
5. Repeat steps to clear cell pattern and restore color / or insert pattern for all cells from range H11:AS11
6. End sub
View 4 Replies
View Related
Sep 26, 2011
I am wondering if it possible to automate the copying of data from particular cells, based on a value in a different cell, into a different format.
So to go from this simplified table:
AB1NameLevel2Arthur2a3Briony3c4Catherine3b5David3a6Edward2a7Felicity3c8George3c
to something like this:
FGHI12a3c3b3a2ArthurBrionyCatherineDavid3EdwardFelicity
4
George
At the moment I do it all manually, and it takes forever. I am sure there must be a simple way of doing it. I am using Excel 2003, but could work in a newer version if required.
View 9 Replies
View Related
Feb 25, 2014
When I add a table in Excel, I can choose from Table Styles, different styles that set one color for even rows and another color for odd rows.
Is there a way to say to this Table Style that assign one color for rows that have the same string in column A in consecutive rows and another color when the string in colum A changes?
I mean,
If A1= XYZ, A2=XYZ assign blue to row 1 and 2.
If A3:A9=FTG assign green to rows 3 to 9.
If A10:A13=LLKF assign blue again to rows 10 to 13.
If A14:A22=WUR882 assign green again to rows 14 to 22.
and so on.
View 2 Replies
View Related
Aug 19, 2009
i have spent hours on with just no idea how to do it.
Write a function named MyOddSum(R) that returns the sum of values in the odd columns in the range R. Use a double For Next loop structure to do the summation. (Hint: Use R.Rows.Count and R.Columns.Count to set the index counters for the double For Next loops. Also, use Step keyword to specify odd columns.)
View 9 Replies
View Related
Jan 31, 2014
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
View 1 Replies
View Related
Dec 4, 2006
way of protecting an array of cells on the condition that information is entered in another array of cells or vice versa. So, put simply, if data goes into a1 then a2 becomes protected (or vice versa).
View 14 Replies
View Related
Dec 22, 2011
I try this method but data entry not allowed
Here is a scenario to protect
formula cells but allow access to data cells:
. Select all cells in the worksheet by clicking on the top left corner of the sheet.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Uncheck the Locked and
Hidden check boxes, then click OK.
These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to
protect the formula cells, and here is how we do it:
. Select all cells that have formulas.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Check the Locked and Hidden check boxes, then click OK.
. On the Tools menu click
Protection and select Protect sheet
View 7 Replies
View Related
Jan 17, 2013
Some sensitive data is held on worksheets that are used by people that do not have access to see the data.
It would be most convenient to keep the data in these worksheets but hide it from view of the users.
Is there a way to password protect a users ability to unhide a column?
View 2 Replies
View Related
Apr 17, 2009
How can i protect the particular cells
Ex.
All the column g
View 9 Replies
View Related
Dec 19, 2012
I want to lock column as and when your fill his report
E.g. in column C user filled report on 1st of this month
On second day column C must be locked or protected
User must enter in D column only
View 11 Replies
View Related
Jul 14, 2009
we are using a shared excel file in a network. Is there a way to protect cells/columns from updating by other users, and i can only update them.
View 9 Replies
View Related
Sep 14, 2006
I know how to protect a sheet and unlock some cells, but how can I lock the format of ALL cells while still allowing users to enter information? Ifve been instructing people to Paste Special  Values, but of course itfs more difficult. For example, Ifd like to protect a sheet but allow cells B2:C2 to be editable in value only – NOT in format. I tried searching the forum for a solution but was unable to find anything.
View 2 Replies
View Related
Nov 30, 2006
how do i protect a cell from being changed? I don't want to protect the whole worksheet, just a few cells within a worksheet? I have tried the Allow Range Users function, but cannot get it to work? Kind regards, Michele
View 8 Replies
View Related
Mar 17, 2007
I am running a loop that changes the value in one cell at a time. Since this is for our entire company, I am trying to make it "idiot-proof" like most of you have done.
While in the loop, I want to unprotect a cell, change the value, then reprotect the cell. But the rest of the worksheet is going to be unprotected for now. is that possible? Or do I have to protect the whole sheet, and then .Unprotect the whole sheet first and then. Protect it at the end of the macro.
View 3 Replies
View Related
May 22, 2007
I would like to protect some cells of a spreadsheet, without using Excel's protection, because I only want to prevent the deletion of those cells - the cells must remain editable.
View 4 Replies
View Related
May 16, 2008
I have compiled a spreadsheet for my co-workers with basic formulas. I would like to know how to protect the formula from being accidently deleted. My spreadsheet now will calculate what is needed but if someone accidently clicks on the cell containing the formula and clears or backspaces it erases this formula. I'm sure there is a way to prevent this.
View 2 Replies
View Related
Dec 3, 2012
using Excel 2010.I have a column (column X )which i want to hide and lock so that only i can access the column.I have some information here i need to hide and protect.Is a code/password a option
View 1 Replies
View Related
