Blocking fields to avoid use in formulas

Is there any possibility to block the particular field in order to
avoid users to use invalid fields in formulas ?


hokah (84)
11/8/2005 11:39:02 AM
excel 39879 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 34

You can lock and unlock cells (then protect the worksheet) so that users can't
change certain cells.

But if you allow them to type a formula into a cell, then I can't think of
anyway of stopping them from pointing at A1 when A1 shouldn't be used.

Is that what you meant?

If it is, maybe someone else has a way...

If it's not, maybe you should rephrase your question.

Piotr wrote:
> Is there any possibility to block the particular field in order to
> avoid users to use invalid fields in formulas ?
> regards
> Peter


Dave Peterson
petersod (12005)
11/8/2005 1:55:04 PM
Yes thats what I ment

I wish to stop the user from select, for example A1 in formula becouse
it shouldn't be used in formulas.

hokah (84)
11/8/2005 2:25:17 PM

Similar Artilces:

MS Excel 2003 cannot auto calculate formula, need to press F9 each time
hi, I don't know why my excel 2003 new worksheet cannot auto calulate formula (eg. summation), i need to press F9 and it will refresh and show the new figure. there is "calculate" word at the left hand bottom of the screen. what is the likely reason ? it was running fine 2 weeks ago. any advise is greatly appreciated. rgds. Tools>Options>Calculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel <> wrote in message | hi, | | I don't know w...

Formula Help (to many expresions)
Could one of you give me a hand with this... I'm trying to put a formula in a spreadsheet that has too many expressions in it. I understand there is a limit to the number of equations that can be in a formula but there must be a way around the cap. Or maybe another way to write the formula? What I am trying to say in the formula is that if... If X is less than 09 then B1 = what's in cell C2 If X is less than 25 then B1 = what's in cell C3 If X is less than 51 then B1 = what's in cell C4 The expression I have written looks like this... =IF(X<10,"N/A",IF(X<...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

Viewing an Excel sheet w/out all the empty fields...
How do you create a spreadsheet that only shows the fileds with data in them? -How do you get rid of all the empty rows/columns, to ease viewing for those who are easily confused by excel spreadsheets? (I don't know how else to emaplin my question... I just don't want the extra columns & rows there, if that's possible...) Please help... Hi! >I just don't want the extra columns & rows there, if that's possible... Just hide them! Suppose the last column in your sheet that contains data is column H. You can hide columns I:IV so that after column H all you ...

simple countif formula
Column B in my spreadsheet consists of 10 rows with one letter in each cell. I would like a formula to count cells B1,B3,B5,B7 and B9 only if the value in each of those cells is "H". I have tried a simple formula like this =countif(B1,B3,B5,B7,B9,"H") but it does not work. Thanks for your help with this formula. -- Brian Try =SUMPRODUCT(--(MOD(ROW(B1:B9),2)=1),--(B1:B9="H")) HTH Bob "Brian" <> wrote in message > Column B in ...

Validation Rule for field values
I have a database that includes a phone number field. I want to prevent a user from creating a new record that contains a phone number that is currently in a record that exists in the database.I am using a form for data entry into the table. Somehow I'd like for there to be a check to see if the phone number typed into the phone number field to be checked against existing phone numbers in the database.I've researched various sources but haven't been able to find the solution. I'm not familiar with VB so any replies that suggest using that will probably not work for me. Sorry, j...

Pivot table fields missing on all computers
I have Excel 2007 installed on two computers. I also have Excel 2010 beta installed on one of the computers. I am unable to create a pivot table on ANY of them. None will show the field list. All copies of Excel work fine in all other aspects. I have searched the net for solutions and found many others have the same problem but few suggestions to fix it. Many questions never got any response at all. I did find a reference to try removing the .xlb file from the XLStart folder. I did, with no results. I have done all the proper show/hide clicks. I even tried using the ...

Using XP Briefcase with money file
I put a copy of my money file into the laptop briefcase and I am able to keep them in sych for a while. For some reason I get locked out, the computer knows the file has been updated but it will not let the desk top file update the laptop file, I get the following message; "The source file can not be opened" "Check to see if the other program has the file opened, and close the file before updating the briefcase." Other files update just fine. When I get this message, I can't even drag a new copy of the Money file to my briefcase, I have to rename the file an...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

OR formula, ?????
I have Worksheet A containing a list of data: Red, Yellow, Orange, Purple which is defined in NAME MANAGER as COLOR Worksheet B on CELL A1, is user input data A2 has the following formula =OR(A1=COLOR) User input : Red = FALSE Yellow = FALSE Orange = True Purple = FALSE The OR formula should produce TRUE value on the cell for all input that is true. However, it is not the case. Where is the formula wrong? Try this instead: =3DISNUMBER(MATCH(A1,COLOR,0)) Hope this helps. Pete On Feb 24, 12:19=A0am, a...

Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below. Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"") Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"") Cell H163 is for Tax. I am almost finished creating an order form. I would like to get the SUM of 3 different columns that are separated. I am not able auto fill strait down the column, because the information is separated in groups with titles, and the cells are not identically sized. I tried varations of this formula: =IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...

Exchange Features Tab in ADUC when using Windows 2000 SP3
Hi, I would like to run Active Directory Users and Computers with Exchange 2003 Admin tools I'm limited to Windows 2000 Prof/SP3 Everything works,except from the Exchange Features tab. When selecting this tab, ADUC hangs. Any ideas. (I have to stay on SP3!) br TN Terminal Services is always my first choice. On Wed, 13 Apr 2005 23:35:22 +0200, "newsMS" <> wrote: >Hi, > >I would like to run Active Directory Users and Computers with Exchange 2003 >Admin tools >I'm limited to Windows 2000 Prof/SP3 > >Everything works,except...

Formula #27
How do I enter a formula to calculate a 7% sales tax? If A1 holds the pre-tax price then =A1*7% will compute the sales tax while =A1*1.7 will compute the price_with_tax-included. Now all this is mathematically correct but we work in dollars and cents (or pound and pennies etc.), so we need to do some rounding to the nearest cent sales tax: =ROUND(A1*7%,2) price-with-tax =ROUND(A1*1.07,2) best wishes -- Bernard V Liengme Microsoft Excel MVP remove caps from email "MissM07" <> wrote in message news:344FF6D4-...

Advanced Formula troubles
I need to do the following calculation: ((1-((1-AE5)*10))*V14) but only when: ((1-((1-AE5)*10))*V14)>=0 or <=V14*1.5 If greater than or = to V14*1.5 then =V14*1.5 And if less than or = 0 then =0 Replace CALC with your calculation =IF(AND(CALC>=0,CALC<=v14*1.5),CALC,IF(CALC<0,0,CALC)) Your last statement is confusing. "And if less than or = 0 then =0"...equal to zero is mentioned in the 1st condition..So this should be 'less than' . -- Jacob (MVP - Excel) "Eves" wrote: > I need to do the following calculati...

Using a variable for a chart limit
Since I got instant help on my last query, is there any way to use a cell reference as an axis minimum or maximum in format axis? It seems impossible, but there is a lot of experience out there. Thanks in advance. -- Vince F Hi, See Jon's information Cheers Andy Vince F wrote: > Since I got instant help on my last query, is there any way to use a cell > reference as an axis minimum or maximum in format axis? It seems impossible, > but there is a lot of experience out there. Thanks in advance. -- Andy Pop...

How do I make door hangers using publisher?
Is this even possible? Mary Sauer has a website with a generous collection of goodies including door hangers. Take a look at just above George Washington's portrait (way down the page) at, -- Don - Publisher 2000� Vancouver, USA "medinapie" <> wrote in message > Is this even possible? I have a sample on my web page. Scroll down -- on the left. Some cute ones here that you could mod...

using program to record audio comment and compress audio to MP3
I can use VBA to record an audio comment into a Word file using Selection.InlineShapes.AddOLEObject ClassType:="SoundRec", FileName:="", _ LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _ "C:\WINDOWS\system32\sndrec32.exe", IconIndex:=0, IconLabel:="Wave Sound" BUT but by default the file is stored as a PCM at 44kHz and 16 bit stereo whcih is about 200kB per second Sound recorder (which is used to do the recording) allows me to manually convert this to a MP3 and a much smaller file e.g. 3kB per second Is there any way I can aut...

Getting an UnBound control value into a Table field??????
I have a form that calculates a production rate in "parts per hour". This form needs to be able to differentiate between Line work, Cutter work, Side work, and Blister work. I created a drop down list for selecting the "Study Type" as listed above. I then created a text box for each "study type". When I select the "study type" each text box checks to see if the selection applies to it and makes the calculation if it does apply. The code for the "Line" study is as follows: =IIf([StudyType]="Line",2700?/[SecondsPerPart])...

Browsing Blocked
It looks like some type of virus or something has block IE8 from browsing sites. I have turned off all add-ons, clean cache etc and nothing works. It is also pinned to my profile. If another user logs in it works fine. How can I fix this problem? You'll have to explain in more detail what "block IE8 from browsing" means. Any idea what version of Windows you have, including the service pack number? --- Leonard Grey Errare humanum est Lee wrote: > It looks like some type of virus or something has block IE8 from browsing > sites. I have turned off all add...

Can I use oulook from home and have access at work?
When I set up my outlook I lose all my inbox messages online and would not be able to read my email from work. Is there a way to have my inbox available on outlook and remain online? Just to clarify, are you asking how to set up Outlook at home to receive email from work? If that's your question, you need to ask your IT Admin or Exchange Admin if you can use OWA and how that's done. That wouldn't have email coming into your Outlook account but it is a form of Outlook, Outlook Web Access. This may or may not be allowed. Your IT staff would of course have all kinds of securi...

=Sheet!A1 formula alternative
I have 2 sheets. Sheet 1 contains text data from A1:A200 but not in consecutive order (for example): Sheet1 A 1 textone 2 texttwo 3 4 textmisc 5 6 textother I need Sheet2!B1:B50 to grab all the data from Sheet1!A1:A200 and list them in the order that they were entered in Sheet1 as shown below: Sheet1 Sheet 2 A B 1 textone 1 textone 2 texttwo 2 texttwo 3 3 textmisc 4 textmisc ...

formula help #42
What formula would I use to search down a column find a name and report the number in the next column, this would be multiple times, the numbers to be added together. The added number reported then to be multiplied by another number and then to be subtracted from another fixed number in a specific cell. Thanks in advance Jason You can sum the corresponding cells matched without having a dedicated column of numbers. =SUMIF(A1:A100,"Name",B1:B100) =(SUMIF(A1:A100,"Name",B1:B100)*AnotherNumber)-SpecificCell HTH, Paul -- "Boenerge" <Boenerge@discussions...

Jscript to hide unpopulated fields
Hi We have created two custom entities named suppliers and business partners. We would prefer not to create account records and use the relationship type field so we created these custom entities. We now have on the Contact form three seperate fields that a contact could be related to. These fields are parent customer, parent supplier and parent business partner. A Contact should only be associated with one parent record. We would like to write Jscript which hides the remaining fields if one of the fields is populated. If a user associates a Contact with a parent customer then the ...