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?
In Column A, starting in row 8, I have a list of part nos. This list can be any number of rows long.
Further down the sheet, I want to put that same list into a horizontal array. I currently have this formula in the first column. It will, of course, pull the value from the 1st row.
=IF(ISBLANK(A8),"",A8)
However, when I fill across all of my columns, it changes the Cell reference to B8, C8, etc. whereas I need it to change to A9, A10, etc.
Writing a formula that will index the ROW reference as I fill across?
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
I have a workbook where one worksheet is referencing cells from another worksheet. I need every third cell to pull from every single cell on the other sheet.
I have found on other threads that =INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+1)) worked. However when I filled down it wont increase the row reference.
I'm trying to use the INDEX function to return a value from a named range. But I'm not directly typing the named range into the INDEX argument. Instead, I'm grabbing the named range from a vlookup table (essentially, another named range). So, if "vh" is the name of the named range, I'm not using the formula:
I am trying to use a match index formula to retrieve some data from another workbook...I am wondering if I can use a worksheet name as an IF condition, i.e. I want my match index formula to search the entire workbook, and return values for which there is a match, with the IF conditions being a specific code AND worksheet name.
So this looks for the dates found in C1 and D1, finds them in B1:B10, and returns the corresponding cells in A1:A10 for the array part of the PERCENTILE syntax.
I think the issue is that the lookup will return the value in A1:A10 rather than using the cell reference for the PERCENTILE.INC function...
I am currently using the below formula to add values (D6:D225 on sheets Mon, Tue, ...) given the criteria is met in any cells within the two columns across the five sheets (E6:E225 & N6:N225 on sheets Mon, Tue, ...).
When the references to the columns do not randomly change themselves to #REF! it works fine however, it does this often, forcing my to rewrite the formula.
**Why does it do that and is there a way to stop it?
Also, when I do have to rewrite I am forced to do each cell individually to change the number within the quotes. I have tried to use a cell reference there but when I do Excel just gives me a "0" as if there is no data to be retrieved.
I am using Excel 2010 on a company computer with the macros blocked so no VBA.
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
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
I have a document that is a subject index for book that has terms that reference page numbers. The structure is for example: Employment, 587, 592, 553, 605, 233 The term is always following by comma and then space and listing of page numbers that subject reference applies. In many cases the page number references are out of sequence and I need them to be in numerical sequence. i.e. Employment, 233, 553, 587, 592, 605 There are many subject terms in index and need to check and fix sequence of page references for each.
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.
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B B1 contains 2 B2 contains "tribbles"
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.
=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!!
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?
The first sheet is called Sites and the second sheet is called Call Log-Jan-08
On the Call Log in cell A3 we put the division # and then we want the information to automatically fill out for the cells B3, C3 & D3. We want this information to come from the Sites list. In the sites list the information starts in A2 thru K2 (A1 thru K1 is the header info) and goes thru A645 thru K645.
I can't figure out how to get my Index formula to work.
in B2 want to have value of cell T1 in B6 want to have value of cell T2 in B11 want to have value of cell T3 in B16 want to have value of cell T4 etc etc..
so actually in every 4th cell in B row need to have formula which will show data from T row as in example above!
I am trying a simple Index or Array formula and have been trying all day to get this work to no avail. The data is freight costs for shipping different size containers (20' and 40') to different ports (Lyndhurst, Port Coquitlam and Seattle).
I will have the port name plugged into one cell and the container size plugged into another cell and want to pull the corresponding freight cost out into another cell. The red text in the file are the data that I will plug in. The box below is where I would like the result.
Current I am using two spreadsheets for similar press and am combining the two. What I need help with this time is currently in cell c9 the formula is =ROUNDDOWN(17.7/C7,0) or =ROUNDDOWN(39.3/C7,0) depending on which press I am using. The presses are shown in cells d34 - q34. Indigo =17.7 gilboa = 39.3. I have added these numbers to a table in the scales tab a24:e26. Is there a way to write a formula in cell c9 on the input tab to combine the 2 above formulas and insert the correct 17.7 or 39.3 when required?
Guys im stuck on the fact of how to pull the correct information through for a named person.as from the example if i place say steve in the named box i would like all of steves scores to appear in the designated boxes.and so on.
I have attached a formula I have used to extract a value from another sheet (I have also attached where the formula is mean't to extract the value from) . My problem is that this formula is not returning what it should?
