how do i semi-lock a group of cells to allow lookups formula...

how do i semi-lock a group of cells to allow lookups formulas without 
allowing user changes
0
quander (4)
11/19/2004 5:09:10 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
663 Views

Similar Articles

[PageSpeed] 51

Do you mean you want to be able to reference these cells in lookup formulas 
contained in other cells?
If so, leaving these cells locked (Format | Cells | Protection), and then 
Protecting the Sheet (from teh Tools Menu) should be enough.
You may want to make sure you have Select Locked and Select Unlocked Cells 
Checked, if a list of Protection Options pops up (Excel 2002 and later).

tj

"quander" wrote:

> how do i semi-lock a group of cells to allow lookups formulas without 
> allowing user changes
0
tjtjjtjt (488)
11/19/2004 5:19:05 PM
The sheet doe’s get protected and cells are “properly” locked and unlocked, 
but there is a strange occurrence that I find difficult to replete in some of 
my cell groupings. I have lookup cells that reference other sheets. When I 
change the data in the other sheets and go back to this sheet with a locked 
cell the data does not change. If I do it with an unlocked cell the data 
changed but the user can also change the cell. Some how I have a semi-grayed 
locked selection in some of these lookups that allow me to change the data in 
the other sheet and have it change in this sheet with out allowing the users 
to access the cells. Does anyone know how I can repeat this????

"tjtjjtjt" wrote:

> Do you mean you want to be able to reference these cells in lookup formulas 
> contained in other cells?
> If so, leaving these cells locked (Format | Cells | Protection), and then 
> Protecting the Sheet (from teh Tools Menu) should be enough.
> You may want to make sure you have Select Locked and Select Unlocked Cells 
> Checked, if a list of Protection Options pops up (Excel 2002 and later).
> 
> tj
> 
> "quander" wrote:
> 
> > how do i semi-lock a group of cells to allow lookups formulas without 
> > allowing user changes
0
quander (4)
11/19/2004 5:57:04 PM
Reply:

Similar Artilces:

Conditional Formula formatting
I'm trying to get a cell color coded (B36) based on a formula result in another cell (H3), based on several different parameters, i.e.: If H3>60, I want cell B36 background to turn green If H3 <=60 AND => 30, I want cell B36 background to turn yellow If H3 <30, I want cell B36 background to turn red I've tried multiple conditional formatting, IF functions, and simple formulas and nothing seems to work -- I don't get any color formatting no matter what formula variation I try. Any suggestions? Select cell B36 and go to the conditional format dialog b...

Using =sum to add values of vlookup formula
Is it possible to add the values given by a vlookup formula? Eg. I've 2 colums - Month 1 & Month 2 The values for month 1 & Month 2 are taken using a vlookup formula - s when u try & do an =sum to add month 1 & month 2, it give 0 as there i no value in either column, just a formul -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=27237 It should give the sum. Are you sur...

Formula Problem2
I am trying to raise a formula that gives me a sum of two cells when there is a positive value present and the following appears to satisfy this. =IF(H55<>"",H55+H56,"") The problem is that if the cell H55 is actually empty, I want to sum two different cells in this case H54+H56. Is there any way of achieving this with the OR or some other function? I received some very helpful replies to this enquiry 30th June 2004 but.............. I am getting there :-) Every one of the suggestions worked but only when I deleted the content of cell H55. Cell H55 actually ...

Adding an extra validation to this formula.
Here is the formual and here is what I am trying to add to it. =CONCATENATE(IF(Monday!R33="4D","EST 4D",Monday!R33)," ",IF(OR(AND(F34=TRUE,G34=TRUE),AND(F34=FALSE,G34=FALSE)),H34,IF(F34=TRUE,"X","Y"))," ",Monday!D33) I would like to add if B34=True, Put in Monday!A5 and "Z" in instead of H34 or "X" OR "Y' =IF(Monday!R33="4D","EST 4D",Monday!R33)&" "& IF(B34=TRUE,Monday!A5 & " Z", IF(OR(AND(F34=TRUE,G34=TRUE),AND(F34=FALSE,G34=FALSE)),H34, IF(F34=TRUE...

Using MsgBox in Formula
Hi All, Just wondering if it is possible to use MsgBox in a Formula (withou VBA) ie =IF(A1<=10, MsgBox"Order more stock") except when I do it thi way I get the old Formula error... I thought it be good to use instea of using conditional formating to change the cell red or without havin to program it in VBA Cheers in advance guzz -- guzz ----------------------------------------------------------------------- guzzy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2243 View this thread: http://www.excelforum.com/showthread.php?threadid=48073 Good e...

Formula para promedios con suma/divisi�n/cursos
Hola: Amigos Yo sumo la columna A1:A10 en la celda A11, en A12 quiero evaluar cualtas celdas estan utilizazdas por decir tengo 8 celdas llenas y 2 vacias por lo tanto quiero que me evalue cuantas celdas estan utilizadas y que la suma optenida en A11 se divida entre elnumero dee celdas utilizadas, para el ejemplo seria 8. Gracias -- Guido Quintanilla G�mez Ayacucho - Per� Hola NOTA: formula en frances (me parece que debe ser el mismo en espanol ?) >cuantas celdas estan utilizadas =NB.SI(A1:A10;"<>0") (no se cuenta las celdas sin valor) o =NB.SI(A1:A10;"<>&qu...

Scrolling down on a cell
This is a multi-part message in MIME format. ------=_NextPart_000_0015_01C62DB2.1D5CFDE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have more than a 1,000 lines of text per cell and several cell in a = worksheet. I cannot see all content in a cell and wonder if there is a = way to insert a scroll down on the right side. Thanks White ------=_NextPart_000_0015_01C62DB2.1D5CFDE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Tran...

how do you take an answer in a cell and converting it from grams .
I have a spread sheet set up for some test I run for my job. I'm kinda' teaching myself as i go, how to use all that Excel can offer. right now I have the spred sheet set up so it will give me the difference between two cells and present the answer in a different cell, which is great. Now I wanna' take that result and convert it from Grams to Miligrams. I tried using this as a formula and it didn't work: (i will use A1 as an example cell) =CONVERT(A1,"G","MG") The Error that came up is: #NAME? I figure this is because it actually wants me to pu...

group/ungroup feature excel 2003
Is there anyway to permanently lock the "Group/Ungroup" feature so that it is permanently inoperative. It is a pain in the ass. Paul Hi you could protect your worksheet and uncheck this option in the protection dialog -- Regards Frank Kabel Frankfurt, Germany Paul Manson wrote: > Is there anyway to permanently lock the "Group/Ungroup" feature so > that it is permanently inoperative. > > It is a pain in the ass. > > Paul I just now looked in the "protection" dialog box. There is no option for "group/ungroup" Paul On some fine ...

Multiple validation rule for a single cell
Hello: I have a question? Example: If I have a validation rule for "Time In" on cell A01 Allow: Custom Formula: =AND(B10<A10, B10>"12:00:00 AM"+0, B10<"11:59:00 PM"+0) AND "Time Out" on cell B01 Allow: Custom Formula: =AND(A10>B10, A10>"12:00:00 AM"+0, A10<"11:59:00 PM"+0) How can I add validation for if the Time In cell has enter a value than Time out can not be empty or null. Vise versa, if the Time Out cell has enter a value than Time In can not be empty or null Any help will be appreciated. Thanks....

locking formulas??
Is there a way to lock formulas? I would like other users to be able to input info, but not be able to change any of the formulas. Hi you can apply worksheet protection to the sheet first select the cells you want people to access and choose format / cells / protection & untick locked next choose tools / protection / protect sheet - you can add a password, if required. now people can only change the cells that you "unlocked" above hope this helps Cheers JulieD "internet team" <internetteam@discussions.microsoft.com> wrote in message news:6A0BF5CB-DFF9-4850...

How do you hide the beginning, not the end of the text in a cell .
How do you hide the beginning, not the end of the text in a cell when the row height is decreased? I want to shrink a row height but not hide the end of the text in a cell. I want to hide the beginning of the text. Try formatting it to right align Format, Cells, Alignment, Horizontal, Righ -- Alex Delamai ----------------------------------------------------------------------- Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127 View this thread: http://www.excelforum.com/showthread.php?threadid=27416 Cliff Format>Cells>Alignment &quo...

Autofit in Merged Cells?
Is there a way to get merged cells to resize with context (autofit)? I can't seem to get it in 2003. Hi Mick- Not sure what you're looking for. The width of a merged cell will be equal to the total width of the columns it spans. The merged cell be widened or narrowed as those column's widths are adjusted. If I've misunderstood, please post back with more detail |:>) "Mick" wrote: > Is there a way to get merged cells to resize with context (autofit)? I can't > seem to get it in 2003. The Height is what I'm most concerned with. I want the te...

Address in one cell
Hi I have a spreadsheet where the whole of address appears in one cell. Is there anyway I can change this so that each line of the address goes into separate cells across the row? Thanks Shona One way which might help is given in this previous post .. : http://tinyurl.com/2unlh -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com ---- "SS" <Student.13@uk.bosch.com> wrote in message news:c8f4uu$o00$1@ns1.fe.internet.bosch.com... > Hi > > I have a spreadsheet where the whole of address appears in one cell. Is > there an...

How to make Excel Chart not to display empty cells?
I have a range of IF formulas. Sometime IF returns number, sometimes "". The problem is that chart displays "" as value 0. Is there a way to force chart not to display those empty values? IF(condition; number_value; "") Marko Marko: In your If statement, replace "" with Na(). Excel will place a #N/A in the cells that were getting the "". Excel charting recognizes #N/A and will not go to 0. See this post for more details. http://processtrends.com/pg_charts_missing_data.htm ...Kelly koday@processtrends.com "Marko Pinteric&q...

formula needed for counting months
I have a column of dates formated mm/dd/yyyy. I need a formula tha will count the number of occurances for, say, January. Any ideas -- Shocke ----------------------------------------------------------------------- Shocked's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1489 View this thread: http://www.excelforum.com/showthread.php?threadid=31435 Hi 0SUMPRODUCT(--(MONTH(A1:A100)=1),--(A1:A100<>"")) "Shocked" wrote: > > I have a column of dates formated mm/dd/yyyy. I need a formula that > will count the number of occuran...

total formula
example, cell b4 contains 3. How can I get cell c4 to display b4 contents and only add to total when b4 changes. Total being displayed in c4. entered 3 in b4 for units ordered yesterday entered 2 in b4 for units ordered today want total to equal 5 but only want to enter orders daily, if this makes sense. You need an accumulator. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub Application.EnableEvents = False Range("C4").Value = ...

Grouping in a Report
I have created a report that is grouped by job. within the job it tells me expenses and income per job within 4 accounts. I want to group the report as well by the event date with the oldest job first. I have tried multiple ways of grouping but each time it does not group the jobs by date as they are. Is there a way I can take this existing report and get the oldest job to be in ascending order based on the event date. In the sorting and grouping dialog box add the event date, but don't use a group header or footer, just sort on each value (ascending). -- Arvin Meyer, MCP, MVP http...

Grant security group permissions to multiple mailboxes
Our Exchange 2000 Server is the host of a few hundred client mailboxes (for business functionality). Internally, we have the need to add a security group representing a dept. to all of the show mailboxes. This security group needs two things: full mailbox access and "send as" permission on each mailbox. Now, I know how to grant permissions one mailbox at a time--but, what I need to know is if it's possible, or if there is a tool out there that can add the security group globally to all mailboxes at once? All of these client mailboxes reside in an AD organizational unit. Th...

export email sent to dist group
I highly doubt this is possible but want to be sure. For compliance reasons my company needs to provide all email's sent from to a dist group during a specific time frame. Since dist groups aren't mailboxes, I'm guessing it can't be done. But in case I'm not aware of another way - is there a way to search and export sent emails somehow? We do have off-site compliance setup - where all these email go...but the admin who knows it won't be available for several hours and I wanted to get a jump on it. Thanks for any info- ...

'Access Denied' error during storage group restore on Exchange 2000 server
Does anyone know why this might be occuring? I have built a copy of our W2k Exchange 2000 server in a lab partly to perform a disaster recovery test and partly to restore a mailbox from tape from storage group 1 (we do not backup individual mailboxes, and to restore one we need to restore the storage group and pluck out the mailbox using Power Controls). It is a rough copy (running different hardware and has different disk sizes and letters). I brought it on to the domain by using DCPromo off a PDC copy in the lab (built from a hard disk off a domain PDC). After that I ran setup / forestprep a...

replace ' in cells
I have a spreadsheet where all the fields (except those with numbers) have a ' before the data in the cell. The ' does not show up in the cell, but only in the formula line. CTRL+H does not work, nor does it work using the ~ with the ' (either before or after the ' character). I need to remove this character in order for my formulas to work. The spreadsheet has over 10K rows and 170 columns. Thank you, Susie 1.. In an empty cell on the worksheet, type the value 1. 2.. Select the cell that contains the value 1, and click Copy on the Edit menu. 3.. S...

Creating a Formula to Format Column automatically? #6
What if I wanted to do more than three conditions -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 You can have 4 conditions. 1. The default 2,3,4 other conditions. More than that would require Code, most likely Event code. Example.... Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = I...

Formula to return data using hyperlink?
i want to make formula to return data from another excel sheet using hyperlink to this sheet?? Why use HYPERLINK? VLOOKUP or INDEX/MATCH can do this for you. Hope this helps. Pete On Dec 11, 2:59=A0pm, Mohamed <Moha...@discussions.microsoft.com> wrote: > i want to make formula to return data from another excel sheet using > hyperlink to this sheet?? Hi, A hyperling would take you to the location but to return the value try this =Sheet1!A1 Mike "Mohamed" wrote: > i want to make formula to return data from another excel sheet using >...

FRX row format, MDA codes should allow me to use wild card charac.
FRX row format, MDA codes should allow me to use wild card characters, such as 11-1100-00,[Group.CODE???] 11-1100-00,[Group.CODE001]+11-1100-00,[Group.CODE002] -- Regards, Raja Raja, When using MDA Group and/or MDA Code within FRX you must explicitly enter the code IN FULL Use of wildcards is not supported, nor permitted. You will have to list each Code individually as you illustrate in your second line. Robert "Raja V" <rvijayarangan@hotmail.com> wrote in message news:84D34C61-13BC-42DF-B393-3F16E3E9C151@microsoft.com... > FRX row format, MDA codes should allow me to...