Protecting Formulas in Tables

In Excel 2007 I have a formatted table, 10 columns wide, in which 5 of the 
columns have formulas.

There is a nice feature in Excel 2007 that allows me to type a value in a 
cell which is directly beneath the table and the table then expands to 
“consume” that value and automatically fills the remaining columns in the 
table with, where appropriate, the formulas.

I want to protect the table so that the formulas are not inadvertently 
overwritten AND take advantage of this feature.

However, if I lock and hide the cells, then Protect the worksheet, this 
feature is disabled.

Can anyone help?
0
Utf
4/13/2010 8:57:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
4072 Views

Similar Articles

[PageSpeed] 37

Hi Hugh

The only way to do this is to resize your table first.
Click within table>Design tab>Resize>make the table as large as you are 
likely to need.
Select the cells where you want to enter data>Format>Protection>remove 
Locked.
Right click on sheet tab>Protect sheet.

The disadvantage of course is that if you are using the table as a 
source for other things e.e Pivot Table, then the range will contain 
lots of blank rows.

The only other way is via code.
The following event code was written for a Table called Table2
Data is entered in columns A and B, and columns C and D contain formulae.
You would need to amend to suit your situation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Dim tr As Long
     tr = Target.Row
     If Target.Count > 1 Then Exit Sub
     If tr = 1 Then Exit Sub
     If Target.Column <> 1 Then Exit Sub
     ActiveSheet.Unprotect
     ActiveSheet.ListObjects("Table2").Resize Range("$A$1:$D" & tr)
     Range(Cells(tr + 1, "A"), Cells(tr + 1, "B")).Locked = False
     ActiveSheet.Protect
End Sub

To install
Copy code as above
Right click on sheet tab>View Code
Paste code into white pane that appears
Alt+F11 to return to Excel
--
Regards
Roger Govier

Hugh wrote:
> In Excel 2007 I have a formatted table, 10 columns wide, in which 5 of the 
> columns have formulas.
> 
> There is a nice feature in Excel 2007 that allows me to type a value in a 
> cell which is directly beneath the table and the table then expands to 
> “consume” that value and automatically fills the remaining columns in the 
> table with, where appropriate, the formulas.
> 
> I want to protect the table so that the formulas are not inadvertently 
> overwritten AND take advantage of this feature.
> 
> However, if I lock and hide the cells, then Protect the worksheet, this 
> feature is disabled.
> 
> Can anyone help?
0
Roger
4/13/2010 9:37:53 AM
Thanks for the help.

It's disappointing that these two functions won't work in harmony.

I've elected to leave the formulas unprotected and, if they get overwritten 
they can be repaired.

"Roger Govier" wrote:

> Hi Hugh
> 
> The only way to do this is to resize your table first.
> Click within table>Design tab>Resize>make the table as large as you are 
> likely to need.
> Select the cells where you want to enter data>Format>Protection>remove 
> Locked.
> Right click on sheet tab>Protect sheet.
> 
> The disadvantage of course is that if you are using the table as a 
> source for other things e.e Pivot Table, then the range will contain 
> lots of blank rows.
> 
> The only other way is via code.
> The following event code was written for a Table called Table2
> Data is entered in columns A and B, and columns C and D contain formulae.
> You would need to amend to suit your situation.
> 
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>      Dim tr As Long
>      tr = Target.Row
>      If Target.Count > 1 Then Exit Sub
>      If tr = 1 Then Exit Sub
>      If Target.Column <> 1 Then Exit Sub
>      ActiveSheet.Unprotect
>      ActiveSheet.ListObjects("Table2").Resize Range("$A$1:$D" & tr)
>      Range(Cells(tr + 1, "A"), Cells(tr + 1, "B")).Locked = False
>      ActiveSheet.Protect
> End Sub
> 
> To install
> Copy code as above
> Right click on sheet tab>View Code
> Paste code into white pane that appears
> Alt+F11 to return to Excel
> --
> Regards
> Roger Govier
> 
> Hugh wrote:
> > In Excel 2007 I have a formatted table, 10 columns wide, in which 5 of the 
> > columns have formulas.
> > 
> > There is a nice feature in Excel 2007 that allows me to type a value in a 
> > cell which is directly beneath the table and the table then expands to 
> > “consume” that value and automatically fills the remaining columns in the 
> > table with, where appropriate, the formulas.
> > 
> > I want to protect the table so that the formulas are not inadvertently 
> > overwritten AND take advantage of this feature.
> > 
> > However, if I lock and hide the cells, then Protect the worksheet, this 
> > feature is disabled.
> > 
> > Can anyone help?
> .
> 
0
Utf
4/13/2010 12:56:01 PM
Reply:

Similar Artilces:

Pivot Table Totals When Using Pages
Dear fellow Excel 2003 users, I have a pivot table that lists hours of several employees. A manager asked if there was a way to view just the non-billable hours. No sweat, I added a page field and selected non-billable and the sum of hours and the grand total showed just the non-billable hours. However, he was hoping to see the data section of the pivot table show just the non-billable hours with a column next to it showing the percentage of non-billable compared to the grand total of billable and non-billable. Problem: after scouring Google and the Help file, I cannot find a way to kee...

Strange formula popping up
Hello. I have a problem in excel 2003. My SS has lots and lots o figures on it. I used format with no $ , 2 decimals, and chose (1234 56). However every time I try to enter an amount in the minus in an cell it comes out strange. For instance say I want to enter (250.00). It does this: - 250.F8 (or any other cell ). and then jumps to the cel next to it and does the little flashing 'outline the cell' thingy. tried deleting and clearing all the formulas and then reformatting bu it keeps doing it. Amounts in the black come out just fine such a 1234.56. It only does it in a cell with ...

protecting outlined data
I want to be able to use the expand/collapse function in outlines when the worksheet is protected. Copy this macro in a normal module It will run automatic when you open the workbook You can also use the workbook open event Sub auto_open() With Worksheets("sheet1") .Protect Password:="rbelecki", userinterfaceonly:=True .EnableOutlining = True End With End Sub Look in the VBA help for more information about protect(userinterfaceonly) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "rbelecki" <rbelecki@l...

Password Protect Outlook #2
I work in an offcie environment and sometimes have to leave my PC unattended for just a few minutes. Unfortunately some confidential emails have been read by my staff and I need a way of safeguarding them other than turning off the PC, is there any way I can password protect access into my Outlook 2000, so all I do is close Outlook. (Sorry still operating under W95) Many thanks You can set a password on a Personal Folders File. If the folder list isn't visible in Outlook, click View | Folder List. Right-click the root folder (usually "Personal Folders" or "Outlook T...

Quicken Password protected
I just got Money 2004 Deluxe, I am trying to import my data from Quicken and the program says there is a password for my quicken account that is required, but I never had a password. No way to get past this. Any ideas? If your data file is Q04, Money04 can only deal with Q03 or older. Otherwise, who knows? "Stephen Weiss" <stephenaweiss@comcast.net> wrote in message news:012f01c3cb22$9a957ac0$a101280a@phx.gbl... > I just got Money 2004 Deluxe, I am trying to import my > data from Quicken and the program says there is a password > for my quicken account that is req...

formula question #8
Hi, Well I've looked in my excel books and I have excel for dummies (and yes I need it) :-), I am sure there must be a way and have tried several things hoping to hit on the right one. I want a result in one cell, reliant upon two different cells having a $amount over zero. I tried this formula but it only comes back with the word "true" or "false" and only works for one cell with an amount over zero. =OR((D4>0)*(G4>0),'TDBonus'!K3,0). In other words if D4 is more than zero or G4 is more than zero, then I want the cell to display what is on another ...

Autofill Formulas
Hello. I have a workbook with 31 sheets of data. Each sheet is a form with a record of data. I would like extract the data into a list. For example, range A5 is the same field on each sheet, so I want to autofill a formula reference the same cell on different sheets. I suspect I need a macro for this, but wanted to make sure first. Can you autofill like this =Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5? -- Thanks, Mike =INDIRECT("Sheet" & (ROW()) & "!A5") Entered in A1 of new sheet. Copy down to A31............will increment the Sheet numbe...

Password protection on split database
I have a database which is split into front end and back end. The front end is password protected but the backend isn't Is it possible to protect both with the same password? I've tried protecting the backend with a password but when I open the front end it can't "see" the backend. Any ideas? Thanks Tony Put a password on the backend, then, after opening the front-end, use the Linked Table Manager to update the links (just like you would if you had moved the backend). You should be prompted for the 'missing' password during this process. You should only...

How to protect 2. axis in an Excel Chart to stay if data changes
A Pivot table is created on Tab1 in an Excel workbook. On Tab2 there is a Line Chart created with data from Tab 1 using 2 axis. If data in Tab1 change the 2nd axis on Tab 2 disappears, jumps ober and overrules axis 1. How can I protect chart area with 2 axis being able to change referring data in the pivot table. ...

grouping dates in pivot table
I have an outstanding checklist which contains two accounts (account 2110 and 2189) I have created a pivot table using the date of the check on the left, the account on top and the amounts as the data. Example Account 2110 2189 Date 7/3/04 105 176 7/6/04 116 218 8/3/04 786 197 8/7/04 214 767 I am trying to group the dates so it will read total amount of outstanding checks for the month. Example: Account 2110 2189 Date July '04 ...

pivot table empty cell
I need to have an empty cell be in the pivot table. I have multiple pivot tables I have generated for a report. I have created a worksheet that reads the data from the pivot table (so it looks nicer). I have tried to check the box in options that says to put a 0 or another symbol (*) in an empty cell and it doesn't work. I want people to be able to input their own data and refresh the pivot table. If I have four response options (sometimes, always, never, usually) and the new data does not have data for one of the options then the table that reads the data gets messed up with its numb...

Pivot Table error message in Excel for Mac 2008
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) When I try to create a Pivot Table Report, I get the following error message: <br><br>&quot;Filename.xlsx cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding&quot; <br><br>The document is stored on my C: drive and is not read-only. I have tried copying my data into a new Excel file with the same result. <br><br>Any ideas? You've wondered into the Excel for Mac forum. go ...

Cell ref is it possibile to calculate / change this in a formula
Any SUPER USERS uot there? I am trying to construct a formula where I need to copy it down 6500+ rows but I need one of the cell refs to be calculated in sted of having its row number to raise by one for each row copied. sample, Normel coping: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26) What I am trying to get: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16) For every line the for...

How to I use ** without Excel thinking I want to type a formula?
I am using Excel (Office 2003) to create a table and I need to use two of these: * to indicate significant results for a research study. How do I tell Excel that I just want to display this symbol after a three digit number without it thinking I want to type a formula? Turn off Lotus transition under tools>options>transition and uncheck transition formula entry Regards, Peo Sjoblom "Buff" wrote: > I am using Excel (Office 2003) to create a table and I need to use two of > these: * to indicate significant results for a research study. How do I tell > Excel th...

Protection
Hi everyone OK my problem is I am using an offie computer that everyone has access to and we cannot - and do not want to - change this. Is there a way that I can put a password for my profile on Microsoft outlook so that no one can read the new AND the old messages found in my inbox? You can put a password on your PST file, presuming you use a PST file... Emad Kamel wrote: > Hi everyone, > OK my problem is I am using an offie computer that everyone has > access to and we cannot - and do not want to - change this. Is there > a way that I can put a password for my profile on Micr...

Rounding Formulas
I'm working in a spreasheet that has a lot of formulas, if I want to round the number that a formula shows, do I need to do that in a different colmn or is there something I can add to the formula to round it to the nearest dollar? Here is an example if the formula I'm often using in this spreadsheet. =((C22*G11)*G17) =round((C22*G11)*G17),0) should do it. If you want the result to the nearest PENNY, do this: =round((C22*G11)*G17*100),0)/100 "Chandra" wrote: > I'm working in a spreasheet that has a lot of formulas, if I want to round > the number that...

formula required
Hi, Cell A1 has the information. abcdef200912070005643<ready>,abcdef200912070005644<ready>,abcdef200912070005645<ready> In Cell B1 i need the answer, abcdef200912070005643 Thanks in Advance!!!! Vicky "Vicky" <Vicky@discussions.microsoft.com> wrote: > In Cell B1 i need the answer, abcdef200912070005643 So, type abcdef200912070005643 into B1 and press Enter. Okay, that's probably not the answer you were looking for ;-). But the point is: you don't say much to tell us what kind of answer you are looking for. If you want...

Percentages in Pivot Tables
I have two pivot tables in my worksheet - one is calculating the % value correctly, one isn't. Basically the one that isn't is a pivot table summarising data from three files. The % from the 'Grand Total' row is actually adding up the percentage column, instead of dividing the two earlier columns. I don't think I am making myself too clear, but if there is someone who thinks they could help me and wouldn't mind looking at my spreadsheet, I would be really grateful as I am tearing my hair out! Is the % column coming from your source data? If so, instead of usin...

CANNOT PROTECT
Trying to password and read only protect a Microsoft Word docuement that is embedded in Excel. When I open the "protected" document, the read-only prompt does not appear...and I can still make changes and close the docuement. HELP! Try this: create the document directly in word, protect it and save it to disk. Then copy the text you want and past it special as an OLE object in excel Peter ...

How to create a formula with multiple contraints and answers
Please help! :) I need a formula that basically says this: If cell C7 is less than 80%, then C8 equals 0% If cell C7 is greater than or equal to 80%, but less than 90%, then C8 equals 75% If cell C7 is greater than or equal to 90%, but less than 100%, then C8 equals 85% If cell C7 is greater than or equal to 100%, but less than 125%, then C8 equals 100% If cell C7 is greater than or equal to 125%, then C8 equals 140% Any help would be appreciated! One way: =IF(C7>=125%,140%,IF(C7>=100%,100%,IF(C7>=90%,85%,IF(C7>=80%,75%,0)))) Regards Trevor "torky1" <torky1@...

Help Needed: "IF" formula/logic too limited
I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...

Pivot Table Wizard in 2007
I've recently started using Office 2007. I have 3 pivot tables that I need to update the data source each month. I was able to do this through the options page, however, 2 of the tables are swapping some data and I wanted to look at the wizard (that I'm familiar with) to see what the issue is, but I can't make it appear. Thanks, Jane N;703332 Wrote: > I've recently started using Office 2007. I have 3 pivot tables that I need > to update the data source each month. I was able to do this through the > options page, however, 2 of the tables are sw...

Spreadsheet password protection
How do you password protect a spreadsheet. I can't find anything in the help section. You can prevent un authorised access by giving a password to open the workbook itself, so that no one else can open it (Go to Tools, Options and then Security Tab). You can do it there if you have already saved the file, or you can go to "save as" then "Tools" and "General Options" where you can give password. On the other hand you can protect work sheets and ranges for different people by using "Tools" and then "Protection" Tab -- M Imran Buha...

How to protect cells?
I would like to protect a range of cells (A2:D24) with a password for a group of users that would make changes to cells in this range. Then on the same sheet, also protect a range of cells (E2:E24) from everyone but me. Is this possible? Thanks. Mark, Please don't multipost. See the thread in .programming. John "Mark F." <m7829@yahoo.com> wrote in message news:FSzQb.10797$6o4.894@fe2.texas.rr.com... > I would like to protect a range of cells (A2:D24) with a password for a > group of users that would make changes to cells in this range. Then on > the same she...

amortization tables
how do i create amortization tables in Excel 2003 You can either create one yourself with Excel's financial functions; see http://office.microsoft.com/en-us/excel/HA011117451033.aspx or use one of many templates, like these: http://office.microsoft.com/en-us/templates/results.aspx?qu=amortization&av=TPL000 -- Kind regards, Niek Otten Microsoft MVP - Excel "Mpho" <Mpho@discussions.microsoft.com> wrote in message news:39A31A72-2793-402D-A1D1-FA45B6D64920@microsoft.com... | how do i create amortization tables in Excel 2003 ...