Update Csa Formula With The Addition Of New Rows
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
ADVERTISEMENT
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
View Related
May 4, 2014
I have some formula's that look at a range of rows
[Code] .....
The trouble is I insert rows to add new data to my sheet, this then changes the formula. $A$9 then becomes $A$10, I don't want this. I want the start of the formula to stay the same. How do I do this?
View 3 Replies
View Related
Aug 12, 2007
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View 9 Replies
View Related
Aug 13, 2008
I am a new excel user. I a trying to write a certain formula but am having trouble. I want to write the formula to add a column of numbers, say H-10 through H-15. Each cell will have a number in it, but I want only to add the cells if the cell precedding it in the G-10 through G-15 Collumn is blank. For example if cells G-12 and G-14 have an "X" in them, then I do not want Cells H-12 and H-14 to be added. I only want the formula to add cells H-10,H-11,H-13, and H-15. I used just 6 cells for example, the column of cells to be added will be a lot longer.
View 3 Replies
View Related
Jan 11, 2013
I wish to sum specific rows in a spreadsheet as a Grand Total. I eventually end up with a string variable called "GrandTot" that holds "=C3+C13+C25+C31+C39+C50+C53"
This formula is correct, but when I insert it into the spreadsheet it totals zero. Only when I manually write the formula does it recognize the cells it supposed to add. I am very confused as to why this is happening.
On a possibly related note, when I highlight a column in the spreadsheet, the count is including blank cells.
View 3 Replies
View Related
May 1, 2013
Lets say I have a #2 in cell A1 and #4 in cell B1 and would like an output in C1 as "plus 2".
Right now I have =if(a1>b1,b1-a1,???) what can I put in the [value_if_false] to return "plus 2" or do I have to use another formula?
View 1 Replies
View Related
Oct 20, 2009
I would like to know the calculator formula for addition via columns.
Eg 1. If i were to place 135 into Column A ;
12.95 into Column C ;
i would need to yield a result of 147.95
Eg 2. Place 189 into Column A ;
12.95 into Column C
i would need to yield result of 201.95 and so on. in the attachment is the sample file.
View 2 Replies
View Related
Aug 6, 2009
you guys very kindly helped me with a spreadsheet a couple of months ago, but i now need to adapt it for another dept. I have completed as much as I can.
I need column C and E in the 'totals tab' to only calculate contract and upgrade sales respectively (found in 'service orders' tab). I also need Scott's and ash's individual sales to be calculated in corrisponding tabs. Most of the formulas are in place so just need them tweaked slightley.
View 4 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
Jan 21, 2010
look at my attachment and see what I am doing wrong in my formula? I have a hard time understanding the Sumproduct formula and when to use comma's, double negatives, addition, etc.
View 2 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
Apr 3, 2014
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
View 14 Replies
View Related
Jul 31, 2006
I’m working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.
So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).
I’m wondering if it’s possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).
I can copy the formula with the “Past Special” function but the function is not updating if I change the formula on the main sheet.
So is it possible to link it and that it’ll be updated automatically when I’m changing the formula on the main sheet?
View 9 Replies
View Related
Feb 2, 2007
I have 2 worksheets in a workbook: "Orders" and "History". Both sheets contain the exact same columns. The "Orders" page data gets wiped clean at the start of each month. What I'm looking for is a macro solution that will copy any new rows and update any old ones that have changed on the "Orders" page to the "History" page, so we can keep a running history of sales. Ideally, I'd like the macro to fire only when performing a Workbook Save or Workbook Save As.
"History" is in date order, so adding new entries from "Orders" should be easy, just by adding rows to the bottom. Updating existing rows will be harder, because I want it to update if the row has changed but not be deleted or changed when the "Orders" sheet is wiped clean the 1st of the month.
Additional:
The columns in both sheets are:
Order Date
Ship Date
Order Number
Customer
Item
Qty
Amount
Because each item gets a seperate line, there will be rows that have identical Order Date, Ship Date, Order Number, and Customer entries, so I suspect I will need to compare multiple columns when deciding if one has changed (the only column that *should* change after initial entry on the orders page is the Ship Date, which would initially be left blank when the order is first entered).
View 9 Replies
View Related
May 27, 2014
I create a report that shows one set of numbers against another set of numbers based on a transactions list I pull monthly. The Report tab is really simple and is based on the sumifs. Sometime, new accounts are created that I don't know about. They show up in the transactions table but not on the Report tab. Is there a way to automatically add new accounts/rows on my summary sheet so that no account is missed? Is there also a way where columns A and B on the Report tab are unique values that are pulled from the transactions tab? Hence, if a new number and description are added, the list just picks it up. In my case however, there are tens or hundred of lines with the same accounts/descriptions.
View 3 Replies
View Related
Mar 5, 2013
I have a worksheet used for a car game. Each row is designated to show one particular car that can be used in the game, so let's say that there are 40 cars taking up 40 rows.
Column A shows the car type and model; column B shows the engine that the cars has represented by a number (the higher the number the better the engine is); and so it continues, Column C shows the transmission; column D the brakes etc.
Underneath the 40 rows (lets say from row 45 to 65) I then have a table with spare-parts that can be used to replace the cars existing parts. This Parts-table is also arranged with engines in column B, transmissions in column C, brakes in column D etc...
This point of this layout is that it should be easy to compare each type of part in the spare-parts table with the corresponding type of part already used in any of the cars as everything is perfectly lined up by the columns.
About the spare-parts table: because the inventory in the spare-parts table often are added to or subtracted from, as parts are won in races or being put on the cars, I have created a macro to sort the parts in each column so as to put the best parts at the top of the table and the bad ones towards the bottom - so far so good.
The problem is this - as I add new cars, the table of spare-parts is pushed down worksheet and that means that the macro no longer will reference the right rows. So the question is this, how do I automatically update the macro to adjust for rows being added above it? I am not really interested in redesigning the worksheet.
View 2 Replies
View Related
Dec 23, 2013
I have joined some cells using '&' which is formed a formula. But till I am not pressing f2 button on that cell and then pressing enter key, the formula is not working.
I am attaching test sheet here, If you press F2 Button in cell H2, then press Enter key they only it will show the value of A1.
I just want to know how to automatic update it?
View 2 Replies
View Related
Jan 11, 2010
Is it possible to update certain rows only based on the choice selected from a dropdown list
I shall explain this scenario
I have attached a small excel sheet called TESTTHEM which i'm testing
and learning excel simultaneously. On the attached sheet
If i select a value in B4 as BusinessLogic, it should then lookup for relevent rows in MISC worksheet and copy C5:L5 from MISC worksheet and replace
the columns G4:P4
Is it possible to do this in excel?
View 13 Replies
View Related
Jul 2, 2006
I have found this sales forecasting template from the Microsoft excel template section on the web, however, when i insert the new rows, it does not automatically update the "linked" sheets. It is the "detailed sales pipleline management sheet".
View 4 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
Mar 16, 2009
I am hoping someone with excel experience can be of help to me with an unusual request for excel.
Assume cell A1 = 2, B1 = 3 and i wish the sum of this (5) to appear in cell C1. Very straight forward so far, however i wish the result to appear in C1 when i left click on a cell other than C1, say for example D7.
I can't use any macros for this.
View 11 Replies
View Related
Jun 11, 2009
I need to C8 - C19 only to add up jobs won by andrew (in current orders). It needs to be month specific. what i mean by that is I need the formula to do what its doing now (adding up the jobs by and putting the totals into the according cell depending on what month they were won.
View 2 Replies
View Related
Jun 14, 2009
I've got a column of numbers that represent the number of overs bowled in games of cricket. Whilst these are whole numbers (eg. 34 overs + 34 overs) the addition isn't a problem, but when they are incomplete overs (eg. 34.4 overs + 34.5 overs) then the addition if out of kilter as it sums them in base 10, and not in base 6. (As there are six balls in an over, not ten for anyone who doesn't know!)
View 2 Replies
View Related
Dec 28, 2009
I have multiple ranges in a spread sheet. I am trying to write a formula that will go out to each range in succession and look for a part number, upon finding return a quantity and them move on to the next range duplicating the above process. The formula should tally the grand total of all numbers found. I have it working except that not all of my items are in all ranges. If the item that I am searching for is in all ranges my formula works but if there is one or more of the ranges that doesn't have that particular value it returns an #n/a instead of totalling those that do have it. If I use a true instead of false in my [range_lookup] I get an incorrect answer. My formula for a given cell is listed below. This is with the true argument which does not work....
View 9 Replies
View Related
Jun 7, 2009
At work my cells that have formulas just stopped updating when I am working live within the workbook. But if I save the entries I made into the cells exit the program and go back in they update..
View 7 Replies
View Related
Aug 14, 2009
I have a very large data sets that I'm working with in excel 2007 and I have to continually filter and unfilter the data by groups. I want to know if there's a way that I can have the values from my formula update themselves each time I filter/unfilter the data. Suppose I have a table like this:......
Where there are two groups, A and B, and each person has an age, and then the third column we have a ranking. This ranking in third column is computed by sorting the data by age and then by group, and then we set the first entry to 1 and then every other entry has an "if" equation where if the group is the same as the previous column then we add 1 to the ranking, and if not then we set it equal to 1.
Now say that just as a thought experiment we want to only look at females and have the rankings for females. If you do a basic filter and select only show girls what you get is:.............
View 5 Replies
View Related
Oct 12, 2009
I have a list of budgets on a summary page. They correspond to a tab name with the budget detail.
I want the formula to automatically pick up the tab name from the list.
How can I use the list from the summary to point to a cell within a budget tab. an example is attached.
I realise some of the formulas on the detail tab need to be sorted but I needed to remove some sensitive work info from it :o) (Dont want to be losing my job)
View 2 Replies
View Related
May 10, 2012
i am trying to update the name of a worksheet with a formula but cant figure out how to do it.
the formula im using is
"=TEXT(MONTH(TODAY()),""00"")&""08""&TEXT(TODAY(),""YY"")"
basically it needs to return "mm08yy" and insert that as the worksheet name.
i can get it to work for a specific cell by using
Range("A1").FormulaR1C1 = _
but i cant use that when i have Sheet1.Name =
View 2 Replies
View Related
Nov 3, 2006
I have a sheet with formula in various cells that are referenced to a sheet that doesnt initially exist. For example one formula on the sheet is: =If( 'WF Tracker 2008'!$D$5=AN$4,INDEX('WF Tracker 2008'!$B$6:$P$10, MATCH($E36,'WF Tracker 2008'!$B$6:$B$10,), MATCH(AN$7,'WF Tracker 2008'!$B$6:$P$6,)),IF(AN26="N/A",AN31,"N/A"))
and initially, sheet 'WF Tracker 2008' doesnt exist so the formulas all produce #ref! values which I expect. My problem is that I Run a macro that creates the sheet 'WF Tracker 2008' but the formula dont want to refresh/update unless I individually go into each cells formula in the formula bar and hit return.
View 6 Replies
View Related
