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.
I have the following formula: =IF(ISBLANK('WP'!D4), "", 'WP'!D4). where WP is the name of the sheet. I need to reference the sheet not by name, but by it's number (for example WP could be my second sheet, but tomorrow the name of my second sheet could change to WT).
I have a workbook with several sheets, this question focuses on two sheets. 'Coded Prehospital Data' and 'Missing Pre Hospital Data', for brevity here i will refer to them as coded and missing respectively.
In coded the cells are populated using an index/match fx to another sheet and if that returns an error the fx looks to the same cell in missing for a value
Here is an example of the formula in coded cell C2:
=IF(OR(ISERROR(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE))), ISBLANK(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))),VLOOKUP( 'Missing Pre Hospital Data'!D2,AGEUNITSCODE,2,FALSE),INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))
Without having to constantly switch between sheets I would like to set up a conditional format that fills a cell in missing yellow when that cell is an error in coded.
This is what I have done to achieve this with no success:
1) Select cell C2 in missing and add rule based on formula 2) Enter =OR(ISNA('CODED PREHOSPITAL DATA'!$C2),ISBLANK('CODED PREHOSPITAL DATA'!$C2)) 3) Enter the custom formatting I decided on 4) In the "Applies To" box I have done two things: 1) drag the cursor from c2 to an28 which auto fills the applied to dialogue box with 'Missing Prehospital Data'!$C$2:$AN$28 and 2) Free type in the dialogue box 'Missing Prehospital Data'!$C2:$AN28
My problem is that I need the formatting for each cell in missing to refer to its sister cell in coded but it continues to refer only to coded c2
Normally when you double-click on a formula you see its component cells highlighted. But when I do this with a formula that uses cells from different worksheets NO cells are highlighted. I wonder if anyone knows how to get the highlighting working for such formulas. Or is there an add-on which enables this?
After having searched for a while I can't seem to find any formula that will look at a cell for reference as to which sheet to go to and return a certain cell's contents.
For example, in column A, I have the text "Sheet1" and in column B, I need the formula to return the contents of cell B15 from Sheet1, based upon column A.
I have Excel 2007, here's what i want to do. I've got a workbook created with several worksheets in it. I want to input the name of a project in say, cell A1 on sheet1. Then on sheet2 cell C4, I want the text I input into the afore mentioned cell to show. I know how do do this with numbers (using the autosum button), but I can't figure out how to do it with text, though it's probably very simple & I'll be embarassed when I get an answer. Following is EXACTLY what I'm trying to do if the above example is confusing.
I have a Project Cost sheet and on it I input (text) a description of a building to be constructed. The cells next to it will be the associated costs of that particular building. There will be several buildings listed on the sheet. On a Sales Figures Sheet in the same workbook, I want to list those same buildings and then their corresponding sales prices. I want these descriptions to be automatically pulled from the costs sheet, so that I only have to input the sales figures.
I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating.
I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect?
I need to copy a range of values from sheet 3 to sheet1. When I recorded macro, I got the below code. But, instead of RC in the 4th line <ActiveCell.FormulaR1C1 = "=Sheet2!RC"> I need to pass values like A1,A20 etc.
Since the values range & column to be copied would be varying dynamically, (say for first iteration it would be A1: A20 & for second iteration, I need to copy C1: C20.) how to pass these variables to the macro and use it instead of the static "RC". Whatever column I specify, it should copy from that <column>StartRow to that <column> EndRow.
Sub Updtval(StartRow As Integer, EndRow As Integer, f As Integer) Sheets("Sheet1").Select Range("A1").Select ActiveCell.FormulaR1C1 = "=Sheet2!RC" Range("A1").Select Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault End Sub
Currently I have a worksheet which is organized to have two pages on it. I will try to explain, i.e. imagine one page of the worksheet in the hypothetical range of A1:D26, then another page in the range E27:K50. So it is staggered on the same sheet. Is there an easy way to copy the range of E27:K50 onto another worksheet.
Aside from the regular copy and paste, I am having problems because there are formulas in the 2nd range (E27:K50) which loose their references once copied. i.e the formula SUM(A1:A10) I would like to be SUM('Master List'!A1:A10) on the new sheet. Is there an easier way then going through all the formulas and adding the master list part.
I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say
C1 = A1+B1+data file c1 Data file is another file in other location.
I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)
Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.
I have an excel workbook that has many spreadsheets (each one sheet has a client name)I have another excel workbook that has these client names on one sheet (on a list) and next to every name I have a number (i.e total turnover of the year).
In the first workbook (where every client has his sheet (tab named after the client) I want to have a cell that equals to the sum of some cells on the other workbook, that refer to the specific client
(it looks like this ='[comissions NF 10-14.xls]comissions 14(auto)'!$J$81+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$197+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$313+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$429)
I want this sum to be added to every sheet of this workbook. each sheet refers to a client, so $J$81, $J$197 etc must be changed for the correct cell that refers to the name of the client. The tab names are alphabetical and so s the list.
Is there any way to do it, without re-entering the formula to each one?
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
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
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 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!!
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:
I have a spreadsheet that shows a large number of folks we had working in a particular division from Jan. 1st until now, 1800 +. Some of the folks are on the sheet twice due to having more than one role. The sheet lists last name, first name, skill description, pay rate, company they worked at...etc. Most of the folks on the list are not currently working but some are. I have another spread sheet that lists the folks that ARE currently working. I'm trying to see if there is a way to compare the two sheets, via a formula, that will be able to identify when the first name, last name, company name, and skill description are the same and then have those identified deleted from the first sheet.
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?
To get the content of cells in other sheets of the workbook you refer to the 'Name' of the sheet and write e.g.:
[Code] ....
However, I need to refer to the '(Name)' of the sheet rather than the 'Name', i.e. 'Sheet1' rather than 'Fruit', and I would like to do it in a formula not using VBA.
I have a spreadsheet, that I have been using for years to estimate jobs. I started learning excel, by building this sheet. It is still a work in progress, as am I. I am always trying to figure out ways to make my life simpler by making the sheet more intelligent. I would like to be able to go from one sheet, to another and click on a part name, which would return a value associated with that part name, to an additive formula on the first sheet, to build and assembly. And, eventualy build a parts list from it as well. For ordering parts.
Sheet 2 contains my checking ledger and sheet 1 is my summary page that I would like to have show current balance from the ledger. Is there a way to reference a value from sheet 2 on sheet 1?
I am using a sumif function that will sum a reference off another sheet. I want to use a cell in the current sheet(where the formula exist) to reference the target sheet. Is this possible.
For instance: I believe you have to use indirect for to have formula reference a sheet name I have two sheets, "Win" & "Lose" On "Win" sheet I have this function =sumif(indirect(J180)G1:G20,"room",E1:E20) On cell J180 of "Win" tab will be "Lose". I am trying to have the formula reference J180 as part of the function and have it sum anything in the "Lose" tab that has the word "room" in it.
How do you reference a table on another sheet so that when you sort that original table it doesn't change the reference you made on that other sheet.
i know how to do this when i am referencing a cell. for instance if i am referencing a cell on sheet 5 and that comes out to something like =Sheet5!B2, i know that when i sort the data differently in sheet 5, that reference will know to change to =Sheet5!B18 or where ever that original reference ends up in this new sorting. but if I do =Table5[2] or something, and i sort that table differently, the reference will change to the new data in that cell and will not follow old reference through the new sort.
I have a the following function: =INDIRECT(ADDRESS(SUMPRODUCT((F35:G45=K40)*ROW(F35:G45))-21,SUMPRODUCT((F35:G45=K40)*COLUMN(F35:G45))))
I'm trying to put the get the value of it from a different Sheet. say this function is in Sheet1 and I wish to place the equation in Sheet3 - what should I change? I tried INDIRECT(ADDRESS(SUMPRODUCT((Sheet1!F35:G45=K40)... nothing works.
what the function does? there are 2 tables. table 1 has values and table 2 has values as well. K40 is the user input --it reflects the number in table2 and the return value is the 'reference' in table1. for example, if table2 in F41 has the number 54 and the reference for it in table1 is $344, when the user types 54 in K40 the returned result in the function will be $344
