Inserting rows into sheet that contains a formula

I have a typical financial sheet where the columns sum (sum(a5:a15)) and the 
rows sum (sum(a5:h5)). The summation columns are locked and the sheet 
protected.
Is there a technique whereby I can have the user insert a row which will 
keep the formats and row sum as well as changing the formula in the column 
sums?

Thanks.

Rick 

0
rstreet (3)
8/1/2009 8:39:31 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1060 Views

Similar Articles

[PageSpeed] 48

Hi Rick,

The following method is not foolproof but generally it works OK.
Insert a blank row between the bottom of data to be summed and the row with 
the sum formulas.
Include the blank row in the sum formula.
Protect the blank row along with the formulas and whatever else to be locked 
and protected.
Now when the user needs an extra row they cannot use the existing blank row 
so must insert one above it.
Because the insertion is in the middle of the range used for the sum 
formula, the sum formula automatically updates.
The Inserted row carries the formatting of the row above it.

-- 
Regards,

OssieMac


"rick" wrote:

> I have a typical financial sheet where the columns sum (sum(a5:a15)) and the 
> rows sum (sum(a5:h5)). The summation columns are locked and the sheet 
> protected.
> Is there a technique whereby I can have the user insert a row which will 
> keep the formats and row sum as well as changing the formula in the column 
> sums?
> 
> Thanks.
> 
> Rick 
> 
> 
0
OssieMac (238)
8/2/2009 2:14:02 AM
Thank you very much OssieMac.
"OssieMac" <OssieMac@discussions.microsoft.com> wrote in message 
news:1ED892D7-4BF2-48E5-A887-76284471E0DF@microsoft.com...
> Hi Rick,
>
> The following method is not foolproof but generally it works OK.
> Insert a blank row between the bottom of data to be summed and the row 
> with
> the sum formulas.
> Include the blank row in the sum formula.
> Protect the blank row along with the formulas and whatever else to be 
> locked
> and protected.
> Now when the user needs an extra row they cannot use the existing blank 
> row
> so must insert one above it.
> Because the insertion is in the middle of the range used for the sum
> formula, the sum formula automatically updates.
> The Inserted row carries the formatting of the row above it.
>
> -- 
> Regards,
>
> OssieMac
>
>
> "rick" wrote:
>
>> I have a typical financial sheet where the columns sum (sum(a5:a15)) and 
>> the
>> rows sum (sum(a5:h5)). The summation columns are locked and the sheet
>> protected.
>> Is there a technique whereby I can have the user insert a row which will
>> keep the formats and row sum as well as changing the formula in the 
>> column
>> sums?
>>
>> Thanks.
>>
>> Rick
>>
>> 

0
rstreet (3)
8/2/2009 3:14:27 PM
Reply:

Similar Artilces:

How do I insert: A {a symbol (member of)} B
I'm try to write an equation where A is a "subset" of Ω using the horizontal member with an equals line under it. I've look at math symbols and Word 2007 equation writer, but I don't see the symbol. Is there one? Are you looking for Unicode 2286, "subset or equal to"? (I don't know what you mean by "horizontal member.") Type 2286 on the regular keyboard, then Alt-X. Cambria Math, Arial Unicode MS, Lucida Sans, and Lucida Sans Unicode are fonts that include that character. On Jan 27, 11:52=C2=A0am, Phil <P...@discussions.micr...

Row Not Found at the Subscriber
http://sanssql.blogspot.com/2010/05/row-not-found-at-subscriber-replication.html Regards, Sandesh Segu http://sanssql.blogspot.com/ ...

Formula In Crystal Reports
Hi All, I'm just starting to learn Crystal right now but it's all very new to me so I was just curious if anyone knows if you can do formulas in it. I'd like to take a custom CRM field called setup cost on a product and add all the values of this field up in a quote. Ie if there are 3 products all with seperate setup costs I want to add those together. Or will this have to be done a different way? Thanks all Tom Hi, Crystal supports both VB and Crystal Reports formulas. the help files are quite good regarding formulas, and the 9.2.2 CR with enhanced edition has an excel...

Excel Formula #6
Is there a way to set a formula to calculate how many rows above (COUNT) with no text or numbers in the column? try =COUNTBLANK(F3:F9) -- Don Guillett SalesAid Software donaldb@281.com "deniseS" <dstafiej@dykema.com> wrote in message news:150b101c3c3fa$68119950$a601280a@phx.gbl... > Is there a way to set a formula to calculate how many rows > above (COUNT) with no text or numbers in the column? > =COUNTIF(A1:A100,"") -- HTH. Best wishes Harald Followup to newsgroup only please. "deniseS" <dstafiej@dykema.com> wrote in message news:15...

Selecting rows from various sheets #4
Cheers Fran -- sha ----------------------------------------------------------------------- shav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1198 View this thread: http://www.excelforum.com/showthread.php?threadid=26665 ...

conditional formatting of row based on cell
I want to apply conditional formatting to a whole row based on the contents of one cell in that row. For example, if cell L2 contains the word 'sport', i want the whole of row 2 to be shaded. I'm guessing this is pretty simple, but seem to be having a bit of a mental blockage. Any suggestions would be appreciated. Thanks :) formula is =$l$2="sport" "Katherine" <katherine@katherine.com> wrote in message news:107b01c372ef$4ec59e30$a101280a@phx.gbl... > > I want to apply conditional formatting to a whole row > based on the contents of one...

Automatcally Hiding Rows in Excel 2000 #2
Is there a way of automatically hiding rows in Excel 2000 based on a rule (ie if there is no data in a row, we want to hide that row without doing it manually)? We have a couple of ideas which involve VB code which reacts to data within that row but if anyone has done this before or knows of a way to do it, that would be great. Thanks in advance! Tom Starr http://groups.google.com/groups?threadm=3F625B17.4BF93224%40msn.com Was a reply you got to your first post. Tom Starr wrote: > > Is there a way of automatically hiding rows in Excel 2000 based on a rule > (ie if there is no...

Re: Is there a way to HIDE a row based on a value of a cell ?
Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

Updating 20k+ rows from Excel to access. vbscript? VBA? SQL?
Any suggestions? Some of these rows only need a few columns updated, and others need all of them updated. If possible if say row 800 column 'S' is filled in the access DB but is blank on the excel spreadsheet, if possible i like to keep the existing data in access and over right it with a blank cell. The spreadsheet is a export of the access DB, changes were made/merged from other spreadsheets so the Unique ID is the same and in column A. A script suggestions to complete this? Thanks :) dim dbe dim db dim ssql set dbe = createobj("dao.dbengine.36") set db = dbe.openda...

Inserting rows slow on large report
I have a large report I maintain with 3 data tabs and 2 report tabs, 1 for top 10 customers, about 400 rows, and another for all other customers, about 4000 rows. Every month I have to update it for any new customer/product combinations. I have optimized the formulas to the best of my abilities and gotten a full recalc time down from 3 min to 30 sec, which is acceptable. My biggest problem now is that every time I need to add a row (or delete one for that matter) it takes about 15 seconds (on the 4k row report, adding to the top 10 customers report takes 3-5 seconds), per row....

Formula too long error...
Hello all. I have the following formula in a cell that works just fine. However, I need to add one more SUMIF statement but I get an error stating that the formula is too long. Is there an easy way around this - thank you. NWO =SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E11",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$10000<>"110",IF(GoodBoth!$S$2:$S$10000<>"115",1,0))))))+SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E12",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBo...

Excel sheet protected
Hello all I have a space in my sheet1 that is blocked but I need work in this space with a VBA macro. How can I work in this blocked space by keeping (after macro) the cells blocked? Thanks henpat You could have your macro unprotect the worksheet, do its work and then reprotect the worksheet. With worksheets("sheet99") .unprotect password:="hi" 'do your work .protect password:="hi" end with henpat wrote: > > Hello all > I have a space in my sheet1 that is blocked but I need work in this space > with a VBA macro. > How can I wo...

Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

Multiple instances of a property sheet
I have two windows as part of my application, say W1 and W2. They are both derived from CFrameWnd. I have a class derived from CPropertySheet that is instantiated/displayed by both windows. When I create an instance of my property sheet (let's call it P1) from W1 (the property sheet is modal to W1) and then create another instance of my property sheet (P2) from W2 (modal to W2), I cannot close P1 by clicking OK or Cancel. Clicking OK or Cancel is completely ineffective; the dialog remains open. Then, if I close P2 by clicking OK or Cancel, both property sheets are closed. I augment CProp...

Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The Sheets are called Sunday - Saturday and I have an IF statement that needs to check for particular information on that sheet depending on what day it is. Have it currently functioning but it is taking up way to much space. I am unable to update my code with other functions that I need it to calculate until I can figure this part out. Code =IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...

Formatting four pages onto one 12x18 sheet
I'm trying to figure out how to format a 12 x 18 sheet with four individual 5.5 x 8.5 pages on it. Any ideas? The closest I can get is under page setup...selecting "postcards" and that will give me three pages per sheet, but I need four. Any ideas or thoughts are greatly appreciated. If your printer can be custom setup to 12 x 18 then you can divide it into four parts. Are you sure you aren't trying to setup a 11 x 17 landscaped sheet? In any event, setup your printer first. File, Page Setup, Advanced on the right, type 18 width, 12 height or 17 width, 11 ...

Why do sheet tabs keep disappearing? Box in Tools is checked.
When I open a new or existing Excel file, the sheet tabs at the bottom may or may not show up. I have repeatedly gone to Tools, Options, View and made sure the sheet tabs box is checked, but still no tabs. How do I get them back? I can't get from one page of a workbook to another. This is in Offfice 2003. Hi Arlie, Try Tools/Options/General In the Sheets in new workbook box, check and see how many sheets are set to appear when opening a new workbook. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://w...

2 existing spreadsheets show 1st line rather than header row to sort ...
(XL2003) This is odd, I haven't seen this ever before. I was working on spreadsheets yesterday for a mail merge. Two had headers rows. I cleaned up both documents, put print area and then tried to sort. In both cases, what came up in the sort was the first row below the header where one always sees the actual titles of the header row when one selects the Header Row radio button under "My List Has" in the "Sort" box. I fiddled and fiddled a bit and managed to get one of the workbooks to display the sort properly by the header row titles - without figuring out what ...

Compare Sheets #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but no...it just does nothing. However if I type 133...it shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

Formula #2
I am trying ot put in a formula. I have a two columns with Revenue listed. One is the Estimate, one is the actual. If there is not an amount in the Actual amount column, I added the cell that was in the Estimate cell. I color coded the cell because I am breakingit out by Sales Person. I have 3 Sales People. I went in and did the SUM and went into each cell that is colored coded to each person. I am getting an error. Please help. Thanks, Vanessa Not enough information to more than guess at what you might be doing wrong. How are you going into SUM? Are you clicking on the sum icon? ...

Formula question 03-03-10
I am trying to complete the following. In s141 i have a drop list with 'yes' and 'no' as possiblities. In x141 i have a drop list with 3 possible choices, .02 , .04 , or .08 g141 is the originating cell p159 is the answer cell. if s141 remains empty or has 'no' chosen from the drop list i need p159 to remain blank, however if 'yes' is in s141 then i need the answer from g141 multiplied by .02 or .04 or .08 (which ever one is chosen from the drop list in x141) to be displayed in p159. Can anybody help? Thanks in advance. Scoob...

how to deal with duplicate data rows
I need to interface with a program that generates a .csv file with row of data. It generates 2 or more rows for each instance uniquely identified by the docket/page combination Example: last first bank trustee docket page smith john ny bank (blank field) 12235 8907 (blank field) (blank field) cal western 12235 8907 smith janet ny bank (blank field) 12235 8907 I need to consolidate all three lines into one row by concating field that contain different data (first name column) and columns with blan spaces to look like the ...

more than 65, 500 rows
Is there a product that allows more than 65k rows? ...

Formula involving different sheets
Hi Using Excel 2003. In sheet 1, cell A1: =200/1200 In sheet 2, cell B2: =300/1200 In sheet 3 in a cell, I want to do =200/1200 + 300/1200 by cell reference. How do I do so? Thanks. Your formula would be: =sheet1!a1+sheet2!b2 The best way to create these formulae is to get Excel to do it. 1: Type = into your cell 2: Navigate to your sheet1!a1 cell. You will notice in the formula bar that Excel is adding its address to your formula 3: Type + 4: Navigate to your sheet2!b2 cell. 5: Hit enter Once you learn this, you will never type a cell address again. Regar...

how to automatically delete non-contiguous rows
Is it possible to define a large data field then delete non-contiguous rows, like every fifth row, without having to delete each row one at a time? Hi One way to insert a column with a formula and use SpecialCells(xlCellTypeBlanks) to delete the rows Test it on a copy of your workbook Sub test1() Application.ScreenUpdating = False Dim myRows As Long Range("A1").EntireColumn.Insert myRows = ActiveSheet.UsedRange.Rows.Count With Range(Cells(1, 1), Cells(myRows, 1)) .FormulaR1C1 = "=IF(MOD(ROW(),5)=1,""Keep"","""&...