Reference Last Row Used Within Formula
Jun 26, 2012
I have the following formula ...
Code:
=ROUND(SUMIF(W4:W4023,"Exisiting",Q4:Q4023)/MAX(1,COUNTIF(W4:W4023,"Exisiting")),2)
... on a spreadsheet that I use as a sort of template for a bunch of other spreadsheets that I manually generate.
Is there a way to have it always reference W4 through the last cell used in W and Q4 through the last cell used in Q. As it is now, I have to manually update the formula each time I add or remove rows to the sheet. I typically start with a blank sheet, with the exception of a group of headers and a row of formulas, and then paste in new data. If the above formula could automatically reference the last cell after I add in the new data, that would be great.
Can it be done with the Indirect function or by some other means?
View 3 Replies
ADVERTISEMENT
Aug 14, 2006
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
View 3 Replies
View Related
Mar 6, 2008
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
View 2 Replies
View Related
Feb 15, 2010
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
View 14 Replies
View Related
Mar 26, 2009
=INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),1,),0))
Where
A1= "M16" and B2= "185%RPIT630"
'Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5=QxTermAge63
Can some on tell me why this is raising a Circular Reference!!
View 9 Replies
View Related
Jan 8, 2012
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
View 4 Replies
View Related
Jun 24, 2008
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies
View Related
Sep 2, 2009
I've got the following formula that I want to tweak. As you can see from the formula, Henning is the name of the tab on the other workbook. If I were to add a column on the spreadsheet that has this formula, and put "Henning" in it, is there a way to use that cell in the formula so that it would still open the Henning tab in the other workbook? I'm trying to avoid having to modify each and every formula.
View 2 Replies
View Related
Jul 28, 2012
I am using the DOB formula of =DATEDIF(AA19,NOW(),"y") & " years, " & DATEDIF(AA19,NOW(),"ym") & " months, " & DATEDIF(AA19,NOW(),"md") & " days". In the next cell I want it to look at the previous cell and determine if "older than 2 years". I have tried the "search" and isnumber, but I keep getting the wrong answer.
View 1 Replies
View Related
Jun 20, 2013
I believe this is the last piece of the puzzle of a project I'm working on. Here are the components I have to work with:
Component 1:
VB:
Dim file As Range
Set file = Range("rgFileToBeChecked")
'this sets "file" as the full path
[Code]...
View 9 Replies
View Related
Mar 4, 2009
formulas i cannot get my head round at all i need e14 to work out how many times cell e1 appears in column h and in column b. so if yes is in e1. it then needs to look down column b and cross refernce it with column h and return the number of yes that appear in column b AND the same row in column h.
View 3 Replies
View Related
Jul 26, 2009
Hello,
I am trying to create a formula in Excel to preform a "SUM" on cells that only have a letter Y (as in "yes") in the cell next to them.
For example
View 3 Replies
View Related
Aug 12, 2009
i'm trying to search throgh multiple sheets using an INDEX formula.
=INDEX('12.31.09:11.15.09'!$AF$14:$AG$75,MATCH(E$2,'12.31.09:11.15.09'!$AC$14:$AC$75,0),MATCH($B61,' 12.31.09:11.15.09'!$AF$12:$AG$12,0))
In short, these are timesheets; in a separate sheet i'm using the above formula to find how many hrs were worked on all the other sheets for a given job# within a given date range. (in the above example, i'm only searching in timesheets between 11/15 and 12/31, but I will ultimately change that to include the entire year '12.31.09:01.15.09'). E2 is a referenced job number; B61 is a referenced date.
I'm getting a !VALUE error.
Can a 3d reference work inside an INDEX formula?
View 10 Replies
View Related
Aug 14, 2009
I'm trying to revise a salary payments workbook that isn't set up so well and I'm struggling with finding a good way to enter payments from an old sheet onto a new sheet. The values (for example) I want to appear on the new sheet are in range C2:AQ2 on the old sheet. Instead of going left-to-right, I want them to appear from top-to-bottom. The first formula I wrote was just a simple:
View 5 Replies
View Related
Mar 25, 2014
In DB2 I have = DB1. How can I drag this down and for each row I go down, I want to reference one more cell to the left of DB1, so in DB3, I would have DA1, and so on.
View 2 Replies
View Related
Nov 11, 2009
I am having trouble inserting a cell reference into a formula. I have a spreadseet that uses data from another workbook. The workbook name changes monthly, so i'd like to be able to place the workbook name in a cell and have the formulas in the spreadsheet use that cell to get the name of the workbook to get the data from. So:
if, for eg, the formula in my spreadsheet is ='[test data file.xlsx]Sheet1'!$B$4+'[test data file.xlsx]Sheet1'!$B$6
I would like to be able to enter test dta file.xlsx into cell A2 of my spreadsheet and replace a reference to A2 into the formulas. I've tried several ways, but can't seem to get it to work.
View 3 Replies
View Related
Jan 4, 2010
I am trying to create a formula, comparing the date in column A to 3 fixed dates, then calculate the value in column C:
=IF(OR(A142=Sheet1!B2,A142=Sheet1!B3,A142=Sheet1!B4),C141-B142+8,C141-B142)
Where Sheet1!B2, B3 and B4 are the fixed dates (5/15/2010, 8/31/2010 and 10/31/2010). I would like to copy the formula into multiple cells, but keep the references to the fixed dates the same. When I do a copy, paste, the resulting formula becomes:
=IF(OR(A143=Sheet1!B3,A143=Sheet1!B4,A143=Sheet1!B5),C142-B143+8,C142-B143)
I need the Sheet1 references to remain the same. Or, can I hard-code dates in a formula?
View 2 Replies
View Related
May 11, 2012
I need to display cell AV13 from sheet 2 on sheet 1. To do this I go to sheet one and in the cell I type =Sheet2!AV13 and voila I get my value. My problem is AV13 is determined by two list boxes, one finds the column AV (named the cell that holds this value as StageColumn) and the other finds the row (named SubCatRow). What I want to do is have a cell that gets this information from Sheet 2. I tried to concatenate the string, but all I get is the text.
The concatenation string was =concatenate("='Sheet2'!",F25)
I have F25 containing the formula =Address(StageColumn,SubCatRow)
View 1 Replies
View Related
Mar 24, 2013
i have a table of data and a formula at the right which gives the max of that data and would like to get the row header and column header of the cell that is giving me the maximum value
e.g on the below the maximum value is 24346.3 and i would like to have in another cell Australia Diversified REITs
Diversified REITs
Homebuilding
Industrial REITs
[Code].....
View 7 Replies
View Related
Jul 30, 2014
I have this formula (which will soon be much more complex...):
=COUNTIF('dt-a'!C2:C194,'what they want for each teacher'!D1)
I would like to be able to replace the sheet names with a reference to a cell which contains the sheet names.
This is so that a user can enter the sheet name in a cell without having to modify the formulae, break the formulae, and then come running to me ranting about things not working and how computers never work properly.
View 1 Replies
View Related
Aug 15, 2008
I have 330 lines of formula that all reference to different excel documents. These files are donated by a 4 digit number. i.e. 4186.xls.
The main document that looks at these files needs to ability to acknowledge changes in the 4 digit number in its formulas and look at a different file if necessary.
EG. =sum("4186.xls"!E4:E5)
A very simple example of the formula i am using. I just need to actively change the 4186 that is in the formula to whatever is in the contents of another cell.
I might just be stupid. Infact, i admit i am no expert. That was my first =SUM formula actually.
View 9 Replies
View Related
May 22, 2009
I am trying to figure out how to reference the formula from another cell. For example, say cell A1 has the formula =indirect(address(row(),2)). Assume B1 equals 10 and B2 equals 20. The formula in A1 would yield 10. How can I make A2 reference the formula in A1 so that A2 yields 20? I need it to actually reference the formula in A1, simply copying and pasting A1 or dragging A1 will not suffice.
View 9 Replies
View Related
Jul 11, 2009
I am setting up a spreadsheet that we can use with EditGrid online so we can post it in Forums. These will be used to track contracts for NFL players. I have used multiple formulas to do this. The problem is that I need to do this for 32 teams over 10 years. So what I want to do is be able to have a master sheet and then just have the other 32 sheets pull those formulas from the master but use the info on the individual teams sheet. I'll give you some basics.
The first year starts out where you type in a players position, name, contract length and his rating. here is an example of one of my formulas. The very basic first one
=if(D13
View 9 Replies
View Related
Feb 18, 2010
I currently have the formula =+'Mar-Final'!$C10*M3/N3.
I would like to have the 'Mar part be input from a cell.
For instance, if I put Feb in cell A3, then the formula would look like ='Feb-Final'!$C10*M3/N3.
View 9 Replies
View Related
May 15, 2007
I have a formula that calculates data from the workbook that the formula is in. I need it to do the same calculations only pull the data from another workbook. Here is a sample formula:
= SUM(X1504 + SUMPRODUCT((H1:H394=73)*(U1:U394="NVLIV"),S1:S394))
Would I do something to the nature of:
=SUM(C:/sample.xls[Sheet1$x1504] + SUMPROD...etc...?
Basically how do you reference another workbook that is closed?
View 4 Replies
View Related
Jun 5, 2007
I am very happy using Excel 2007 new table features.
1) I have a table with this header:
Name-Surname-Code-January-February...
2) I have this sumif formula to sum month values for each name
=SUM.IF(MyTable[Name];$G4;MyTable[January])
3) When I copy my formula to the right it shows
=SUM.IF(MyTable[Surname];$G4;MyTable[February])
The problem is that I canīt let the [Name] part fixed. I tried with [$Name$] thinking about excel 2003 logic, but I didnīt work. I need to copy my formula to the left, leting the [Name] fixed and allowing [Month] to change...
View 6 Replies
View Related
Jun 5, 2007
I input sales for each day of the week on a separate sheet since the beginning of the year. I am taking the average of the last 12 (Tuesdays) but with each input I need to change the function. Say it was Average(B11:B22) with the new entry in B23 I would change Average(B12:B23) I would like it to do it automatically with each entry.
View 9 Replies
View Related
Dec 27, 2007
I have a worksheet that changes monthly but has the same fields always. I have created a few macros, and one that is a 'RunAll' for ease of use. Sometimes there is an error and I have to go back and fix some data, So I am looking for a way to change the "SelectBzzAgent" Macro to link data to a new sheet instead of copying. I have attached my spreadsheet.
View 3 Replies
View Related
Jul 31, 2008
I have a column (A1:A26) with a series of numbers which I want to multiply by a second variable in another column (Sheetb:C1). So I write a formula for a new column - =A1*Sheetb!C1. I want to multiply each value in (A1:A26) with just the value from Sheetb:C1. However, when I drag the 'fill series' pointer down, it automatically increments the cell its is multiplying by i.e. 1st cell is A1 X SheetB!C1, 2nd cell is A2XSheetB!C2... However I want it all multiplied by SheetB!C1.
View 2 Replies
View Related
Oct 1, 2011
Version: Excel 2007 WinXP
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
View 2 Replies
View Related
