One of my longest running problems with Excel is how to use a cell value to point to a reference. If I have three sheets, 'Main' and 'Data 1' and 'Data 2'. In the Main sheet I'd like to be able to have a value of either 1 or 2 in cell A1. Then in cell B1, use the value of A1 to point to cell A1 in either Data 1 or Data 2 sheets.
View 3 RepliesI 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 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?
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.
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.
I making a template for multiple users to import data into for analysis. I want them to be able to delete and drag-and-drop data to get rid of any bogus values. However i need forumlas that reference those to NOT follow the values as there as moved. As a simple example say you have two columns, column A is a set of random numbers, column B is the number from coumn A times 2. If take the bottom half of column A and drag it up one cell, overwriting a cell, now the cell in column B next to the overwritten cell has a #REF err and ever cell below that now references a Cell in column A from the previous row. I want a formula that references a static address and will not follow the cell if it is moved.
If I use named ranges rather than specific cells references it works for the above example, however for my actual form it does not. Even though the ranges and formulas stay the same I start getting incorrect values. If there is no other way to this I will try to figure why the named ranges do not work.
How do I reference value of a cell in VBA example..
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A$1:$XFD$10000").AutoFilter Field:=2, Criteria1:= _
"NAME"
I want to replace hard coded "NAME" with the contents of a cell in another sheet?
I've got a workbook with about 1,000 worksheets in it and I need to add up the values across the sheets from the same cell reference. I can get this bit to work with this formula:
=SUM('1:2241'!BJ3)
So this adds up the contents of cell BJ3 across all worksheets located between 1 and 2241. However I also need to count the number of times a zero appears (zeros only not blanks) so I thought this might work:
=COUNTIF('1:2241'!BJ3, "=0")
However it just returns the #VALUE error.
The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.
Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’
Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’
Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’
From the following example:
A1 TAG B1 mileage
G10-1622 15246
G10-1622 15246
G10-1583 15246
G10-1622 28659
G10-1195 28659
G10-1622 28659
desired results
Enter tag in cell
G10-1662
Mileage
15246
15246
28659
28659
I would like to enter a particular TAG # in I5 and return all the mutiple associated data
See attached : tire2.0.xls
Here is a made up scenario which tries to emulate my query:
I have a spreadsheet with two tabs - one called Model and the other called Prod. In the Prod tab, I have the following text entered in single cell - A1
My name is
I was born in
I Live in
What I would like to do is add cell values from the Model tab at the end of each line. For example;
My name is JOE
I was born in 1915
I Live in AUSTRALIA
I tried the following but it didn't work.
My name is Model!A1
I was born in Model!A2
I Live in Model!A3
I believe it didnt work was because the cell starts of with text rather than a formula
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 RelatedHello,
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
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)
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].....
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 RelatedI 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.
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 RelatedI have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
I need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.
Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1.
After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.
I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.
I have a formula to extract the worksheet name and add the "!" to it so it can be used in a copied formula... I'm just having trouble writing the formula to reference the text in the cell with the worksheet name. If it's easier, I also have the list of worksheets without the "!" - e.g., Sheet1, Sheet2, Sheet3, etc...
I want the formula to calculate, in my example, the sum of 2 cells on a different worksheet.
So finally got my sumrpod working using this formula
=SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C8:$C10000=A5))
Is there a way i can link 'Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm] to a cell so that when the next months tracker is created and the filename changes i simply change the filename in the cell which im using to reference the filepath/filename.
e.g A1 = South Pac Load Tracker - MAR14.xlsm
Formula would read =SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[' & a1 & ;]SPECIALS TEAM NZ'!$C8:$C10000=A5))
I have one coloumnA with formula =IF(F4="","",(H4-F4)) ------ the result I want to use for reference in which I am unable to use the result value using below formula.
I have one more cell B with month & date
Currently using formula =SUMPRODUCT((TEXT(A4:A1501,"mmmyyyy")=$B4&$C4)*('Credit Cards&Loans Transactions'!J4:J1501))
Cell B4 has month & C4 has year.
J4:j1501 cell has the formula "=IF(F4="","",(H4-F4)) " , I want to use the result of cell in above formula.
I am trying to identify some combination of formulas or functions that will allow me to adjust the cell reference within a function based on a value in another cell. For example, I have a series of values listed from cell C50 to cell W50. I want to use a function to calculate the internal rate of return over a range of the cells: =IRR(C50:W50)
So far, so good.
However, I want the size of the range to be determined by a value entered in another cell, let's say B2. If the value in cell B2 is "5", I would want the formula to consider only the first five columns: =IRR(C50:G50). If the value in cell B2 is "10", I would want the formula to consider only the first ten columns: =IRR(C50:L50).
I need the cell reference for a formula to + 5 each time I copy down, however, it seems to only + 1 no matter what I try?! The formula is a simple =sum(A4:A8). I would like to be able to drag it down so that the next one states =sum(A9:A13) and so on.
View 5 Replies View RelatedIn the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.
View 10 Replies View RelatedI need to use the result of a simple calculation in one cell on Sheet1 (=A1+2) as the row in a cell reference on a different sheet. eg =Sheet2!A"n" where "n" is the result of formula.
View 4 Replies View RelatedI have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:
=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=9/9/2011",NextPaymentDueDate,"<=9/22/11")
It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:
A2 contains 9/9/2011
B2 contains 9/22/2011
=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=A2",NextPaymentDueDate,"<=B2")
but it doesn't work.
Is it possible to use a formula to calculate a cell reference within a formula? e.g. is there some way I could reference cell C47 in a formula by saying C(40+A2) where A2 contains the value 7. I often want to sum a certain number of cells in a list, from the first one to the nth one and need to be able to calculate rather than hard-code n.
View 5 Replies View RelatedI've been trying to make a formula using autofilter to search data inbetween a date range which i would like to be specified from 2 ceels on the workbook.
Basically i'm trying to get it to function like access or a search box.
The bit i'm having trouble with is when i record the macro i get to the custom filter option but i am unsure how to link from there to the cells in the spreadsheet.