Update Function In Cells Following Addition Row Insertion
Jan 14, 2014
I have a command button that runs a macro to insert two new rows between row 15 and 16 no real problem (see code & sheet below), but I would like the Total Hours and Total Cost rows to include the newly added rows i.e.
Rows 16 & 17
Cell B18 now contains =B2+B4+B6+B8+B10+B12+B14+B16
and
Cell B19 now contains =B3+B5+B7+B9+B11+B13+B15+B17
If another two rows are added then Total Hours and Total Cost Cells 20 & 21 will now be
Cell B20 now contains =B2+B4+B6+B8+B10+B12+B14+B16+B18
and
Cell B21 now contains =B3+B5+B7+B9+B11+B13+B15+B17+B19
Command button Code to insert row
Code:
Private Sub CommandButton1_Click()
Const fWhat As String = "EXTERNAL"
Dim sR As Range, fR As Range, fAdr As String, nRw As Long
Set sR = Range("A1:C187")
[Code] ..........
Worksheet Below
1
Hours
170
Cost
387.99
[Code] ...........
View 1 Replies
ADVERTISEMENT
Jun 20, 2007
I am working on a spreadsheet that matches each cell in Column B (text) with the data (text) in a constant cell; if there is a match, the data that corresponds to the data in Column B (text) will average (Column G, number) using a CSA formula, for example: =AVERAGE(IF($B$3:$B$106=A$110,$G$3:$G$106))
Now the formula above works well, only I have to update the spreadsheet, so when I add new rows the $B$3:$B$106 and $G$3:$G$106 portions are useless.
Trying to use the INDIRECT function that many people successfully use in this forum, produces a #VALUE error,
=AVERAGE(IF(INDIRECT("$B$3:B"&ROW()-4)=A$111,(INDIRECT("$G$3:G"&ROW()-4))))
View 9 Replies
View Related
Jan 27, 2009
I obviously know less about functions than I thought I did. I've got the attached spreadsheet set up except getting totals at the bottom. The production total L44, would be column A multiplied by the quantity entered in columns L and summed. Same for Total SF, square footage in column B times quantity in L and summed at the bottom. This would continue daily, needing sums under each column.
View 4 Replies
View Related
Mar 27, 2009
I imagine this is a simple conditional SUMIF function. I'd like a cell to add values in e.g. column "d" when that row meets certain criterion in column "a".
In other words, I have a column that has times recorded in minutes, and another that says a person's name which correlates with the times. I'd like a cell on another sheet to give a total sum of minutes for each person.
Ideally, part of the function would translate the minute count into hours/minutes, but I think I can figure out how to do that by changing the format in the cell...
View 6 Replies
View Related
Mar 23, 2009
I behind developed to a time a function of Addition with an only conditional criterion.
I would like to extend at least for three criteria, this function I function accurately as the function SUMPRODUCT alone that done in VBA.
Function VlookupAllSum(name As String, IntervalSearches As Range, IntervalReturn As Range) As Variant ' as integer para valores até 32.767
Dim Valor, Nome
Dim lin, col As Integer
Dim Total
Application.Volatile
lin = 1
col = lin
For Each Nome In IntervalSearches
If Nome = name Then
Valor = IntervalReturn(lin, col)................
View 9 Replies
View Related
Mar 18, 2014
I am trying to create a shift roster for my team. My team work in general shift (GS), second shift (SS), third shift (TS). There are situations where some team members come in any of these shift but had to leave midway due to feeling well, so that should be counted as half day. This half day scenario may be in any case GS/SS/TS. I am using COUNTIF to get the sum of GS/SS/TS for each employee which is giving a whole number. I also want to incorporate 3 different variations of half day as Half Day GS (HG), Half Day SS (HS), Half Day TS (HT). My expectation is that for each Half Day only 0.5 should be added in a appropriate shift, but COUNTIF is always counting this as 1. How I can achieve this using countif or any other Excel function? Sample sheet is attached.
View 3 Replies
View Related
May 28, 2013
I'm looking for a formula that acts the same way as the subtotal formula (revises if rows are hidden and only adds visible rows). Is there anyway to get this removal of hidden rows but for subtraction?
View 3 Replies
View Related
Feb 8, 2007
I wanted to have the weeks of the month down one column = 52 week.
down the next column I have different amounts of money in that week.
some months have 4 weeks and other have 5. I wanted a program to say:
If you see a month "x" look at the next column and take that amount. Then on the next row you have month "x" again (week 2) go to the next column and take that amount and add it to week one. And so on until all 4/5 week are added to give on result.
Then the same for the next month...
month amount/week amount/month
05-Mar 0
12-Mar 70
19-Mar 210
26-Mar 350 1050
02-Apr 420
09-Apr 455......
View 9 Replies
View Related
Jun 30, 2014
I have this in cell F5
CM26 (85) CM16 (39) CM25 (12)
I need to add the numbers in brackets up and put the total in G5.
View 3 Replies
View Related
Mar 27, 2009
I AM HAVING DATA OF 210 BRANCHES OF DIFFERENT ITEMS. EACH BRANCH HAS AROUND 100 TO 150 ITEMS. I WANT TO ADD THE VALUES OF EACH BRANCH AND I HAVE TO GET THE GRAND TOTAL VALUE IN A SINGLE SHEET. SUPPOSE IF ADD E10+E210+E350+E470 LIKE THIS AFTER SOME pLUSES I WILL GET THE FORMULA RANGE IS OVER. IS THERE ANY METHOD OF ADDING 210 BRANCHES ITEMS
View 9 Replies
View Related
Nov 19, 2008
make a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.
View 2 Replies
View Related
Aug 6, 2014
Any way to exclude colored cells from addition in a column. I will attach a simple example document.
I am building a spreadsheet to keep track of my monthly spending, and debt balance. I keep track of the total amount of bills I pay each month. As I pay them, their respective line is highlighted green in the spreadsheet. I would like to create a macro/formula which would sum the remaining cells in the column which are not highlighted to display the remaining monies which need to be paid out. I'm not sure if this matters but I am using office 2010.
billSample.xlsx‎
View 4 Replies
View Related
Jul 27, 2007
I have a spreadsheet with data in 4 different sections with a summary section at the top. Data titles are in column A with the data to the right, arranged by month.
I have created macros to sort the data in each section. Occasionally I need to add a new row to each section for a new data set. When I do this, it screws up my sorting macros for the sections below. It will sort the same rows that it sorted before, not taking into account that the data is now one row lower.
View 9 Replies
View Related
Apr 28, 2014
I have a large database, data as rows with individuals each carrying a unique ID code (RING). The database spans over 60 years - individuals are birds with metal rings carrying a unique ID - so some individuals have had their rings replaced (e.g. row 7 in the sample below). My task is take the old ring (C7) and replace any earlier records of the old ring (A3) with the new ring (A7).
View 6 Replies
View Related
Sep 4, 2013
I wanted to know:
Q1. How can we insert new rows in a protected sheet?
For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.
Or any way to address this issue via some other method like adding a scrollbar,etc.
View 5 Replies
View Related
Jun 27, 2012
I am working on a project of 4000+ entries. We will be identifying the number of seasons in a year. We would like to add a row in another worksheet for each of the seasons identified.
So say we had data in row 1 of worksheet 1. That row stated there were 3 seasons. Is there a way to automatically insert 3 rows into worksheet 2?
View 1 Replies
View Related
Jan 31, 2013
I have a need to insert formulas into a column using vba. I need to skip a row, when inserting the formulas, so a simple drag using vba would not work without some modification.
Here is the formula, that I need to insert... into column "BJ"
Example Cell BI11...=SUM(BH11-BH10)
Cell BI13...=SUM(BH13-BH12), the next cell needing a formula would be ..
Cell BI15...
The formula would be inserted, based on the last row used in column "C".
View 3 Replies
View Related
Jun 5, 2008
I would like to prevent the user from inserting rows. I know that ater versions of Excel have built in "Protection" features which would easily accomplish this. Unfortunately, our company uses Excel 2000.
I have keyed in a constant value in cell B100 ("bottom" of the practical range). I have tried ...
Private Sub Worksheet _Change (ByVal Target As Excel.Range)
MsgBox "Range" & Target.Address & "Was Changed."
If Range ("B100").Value = "" Then
Application.Undo
MsgBox ("Do Not Insert or Delete Rows")
EndIf
End Sub.
The problem with the above is: The "Change" macro does not seem to detect the insertion of rows. It does, however, detect the deletion of rows.
View 9 Replies
View Related
Feb 2, 2010
I know I can update the content of a cell on any sheet from other sheet using for example: =SHEET!$G$22. This works ok for text and numbers, but how to get the same result for a function?
Example: in sheet1, cell B3 I have =SUM(A5:A9). When I modify in sheet1, cell B3: =SUM(A1:A9) I need his formula to change on other sheets as well. How to do that?
View 9 Replies
View Related
Aug 14, 2008
Is there a way to renew / update the formula =NOW() without closing & reopening the file??
View 2 Replies
View Related
Dec 16, 2013
click on cell A4 -right click - insert cell- shift to right, so figure 2 moves to C4 pushing the yellow cell to D4 right?
what am trying to do is to make column c to be frozen or locked or whatever you want to call it, so if I insert a cell in A4 shifting to the right, the value in B4 jumps to D4 without shifting the yellow cell
View 2 Replies
View Related
Sep 24, 2008
I've been using this code to make menus for all my Excel sheets since NateO made it back in 2003.
- The workbook has 370 sheets, 365 of them are one day per month with the tab named ddmmmyy.
- The Main Menu in this file puts the months Jan, Feb, Mar, etc in cells A3:L3.
- In the single cells under each month it put the month day tab name (ddmmmyy) and hyperlinks it to the sheet tab.
I would like to do two new things.
1. Put the name of the month in everyother cell in row 2, so Jan would be in A2, Feb in C2 and Mar in E2, etc.
2. Put the day of the week for each date in the cell to the right of the date. So the column heading would be Jan in A2, 01Jan08 in A3 and Tue in B3.
I thought I could make another macro and just insert the rows after the Main Menu was built and then VBA copy/paste TEXT(cell,"ddd") down the ranges, but thought there might be a better idea out there...
Sub Workbook_TOC()
Dim ws As Worksheet
Dim wsNw As Worksheet
Dim z As Integer
View 9 Replies
View Related
Sep 14, 2007
Is it possible to make it so that a user cannot delete or add sheets to a work book, except through buttons assigned to macros? I've tried simply locking them, but these sheets need to be accessed to input and manipulate data. I have the macros necessary to delete/add sheets, but right now they are redundant since the user can simply do it manually.
View 3 Replies
View Related
Nov 23, 2007
I need to insert an arrow in a sheet, in a location and a direction which can be controlled by me (I don't mind hard coding the location and direction), based upon a cell value being positive, negitive or 0. I realise that it will require some code but can't find any infomation on doing anything like this through google or in the various VBA texts I own.
View 2 Replies
View Related
Jul 20, 2009
how to update a function when opening worksheet. I made
View 4 Replies
View Related
Jun 1, 2006
I have a function written in excel written in VBA. The function reads data from a worksheet and uses it in an array to solve based on the variable passed with the function. Now the issue is if I change variable that the are passed with the function it works fine. If, however I update values in the worksheet that the function is using as an array (sort of a look up table) the function doesn't see the change. I have to exit Excel and reopen it to get it to re calculate the function. Is there a better/faster way than continually closing and reopening excel?
View 3 Replies
View Related
May 15, 2006
I'd like to insert a picture background for multiple s/ sheets I've developed, however, even though the images I'm using are only 30kb's in size, it bumps my s/sheet file size from 900kb's to over 4mb's ! Is there a way I can stop this from happening, or, any way to restrict the background image to only a certain part of the worksheet as opposed to the entire thing?
View 3 Replies
View Related
Apr 21, 2009
I have a Big Question about the SMALL Function in Excel. I would like to know if it is possible to use a syntax that will make the small function update my graph dynamically.
Personnel transfer out of the Department on know dates... but they come in to the Department all the time. I would like to know if I can increase the "range of my data" even if I don't have data in the cells (i.e. B2:B6 have data, I want the range to include B7:B16, which has no data)... When I have no data in the cells I get errors/ circular references...
Ideally, I would like be able to add or remove personnel and the SMALL function would "organize/update" my graph dynamically. Is this possible?
View 6 Replies
View Related
May 26, 2009
I have an issue on manipulating the data in my worksheet below. I need to update column 'remark' when the customer has pay fully. Example customer 1003 and 1004 both pay completely so I need to update done in column remark. For customer 1002, because not fully pay, so cannot update done.
customer inv no invoice payment remark
1002 A12 100 100
1002 B45 120 0
1003 W12 90 90 done
1004 F12 30 30 done
1004 F17 45 45 done
May I know what function or coding can achieve this?
View 3 Replies
View Related
Apr 26, 2007
I have written a custom function which is called in lots of cells. It had an error so I modified it. But the modification did not automatically trigger recalculation in the cells where it is used. Neither did F9 (manual recalculation). The only thing that did is hitting F2 for the cell then ENTER. But what a pain to do that for every cell it's used.
View 5 Replies
View Related
