Getting Cell Reference Of Formula?

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


ADVERTISEMENT

Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference

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

Change Formula Cell Reference Based On Another Cell Reference

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

Cell Reference :: Formula To Reference New Cells

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

Excel 2007 :: Formula To Return First Cell Reference In Another Cell Formula

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

Using DOB Formula As Reference Cell

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

Formula To Reference A Cell

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

Reference Cell With A Formula?

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

Reference The Formula Of Another Cell

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

Use A Cell As A Reference In A Formula

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

Fix Formula Cell Reference

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

Use Text In A Cell For A Formula As Cell Reference To Create Range

Mar 10, 2013

I 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

View 4 Replies View Related

Maintaining Cell Reference According To Its Physical Position From Formula Cell?

Jan 29, 2014

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.

View 3 Replies View Related

Use A Worksheet Reference Cell In A Formula?

Apr 10, 2014

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.

View 6 Replies View Related

Formula With Cell To Reference Other Workbook?

Mar 19, 2014

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))

View 1 Replies View Related

Unable To Use Cell For Reference Which Has Formula?

May 4, 2014

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.

View 3 Replies View Related

Varying A Cell Reference In A Formula

Jan 6, 2009

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).

View 4 Replies View Related

Cell Reference For A Formula To + 5 Each Time

Apr 20, 2009

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 Related

Replace Name In Formula With Cell Reference

Sep 15, 2009

In 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 Related

Formula Result As Cell Reference

Oct 1, 2009

I 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 Related

How To Reference Cell With Date In Formula

Jul 22, 2011

I 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.

View 6 Replies View Related

Using Formula To Calculate Cell Reference

Oct 12, 2010

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 Related

Autofilter Cell Reference Formula

May 21, 2008

I'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.

View 9 Replies View Related

Use A Formula As A Cell Reference In A Function

Aug 3, 2005

I have a workbook with a Summary sheet and then a series of sheets for each month/year. Onthe summary sheet I use column A for the month/year as a date formatted "mmm yy" ie the same as the sheet names.

in columns B,C,D I want formulae that sums or averages a fixed range on each sheet but I would like the formula to take the sheet name from Columa A rather than hard coding it.

So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the "Dec 04" from the text in cell A3

View 14 Replies View Related

Returning A Cell Reference With A Formula

Feb 17, 2006

I am wondering if there is a way to return a cell reference by having excel
look at a table with certain parameters. For instance...
If there were a table with repeated dates such as:
Feb 12 1500 1530
Feb 12 1745 1750
Feb 13 1215 1245
Feb 13 1610 1700
Feb 13 1850 1900
Feb 14 1050 1140
Is there a way to return the cell number (ie A6) for the first instance of
Feb 14?

View 13 Replies View Related

Formula To Display Cell Reference

May 6, 2006

I built a formula but I need it to display the cell reference (column letter
& row number), not TRUE or FALSE, of the cell(s) that matches the criterias.

I have provided the formula to demonstrate what I'm trying to do.

=IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"cell
reference")

The cell reference should indicate the cell in column "$F" along with the
row number.

View 10 Replies View Related

Using Cell Value In File Reference Formula

Jul 29, 2009

I have the following (working) formula that pulls the largest value from an array in a closed workbook on a network share:

=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09[385-4009.xls]M-805'!$F$11:$G$41,1)

I want to be able to use a cell value within the open workbook to change the name of the referenced workbook (i.e. the 385-4009 portion of the formula) in the fromula

I have atempted to use the following:

=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09["'"p8"'.xls]M-805'!$F$11:$G$41,1)

but excel returns an error and highlights the following portion of the formula:

'\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL

View 2 Replies View Related

Building Reference Cell Formula?

Mar 19, 2012

Fairly simple question, but I don't remember syntax.

The simplist cell reference is =F27

If you want to make the column reference a variable and the row reference a variable (=ColumnVarable:RowVariable) What's the syntax?

View 3 Replies View Related

Formula To Display Its Own Cell Reference As Its Value?

Feb 14, 2014

Say, the cell's reference is A1, I want the value of the cell to be A1. If I'm in BB12, I want the value of the cell to be BB12

View 3 Replies View Related

Change Cell Reference In A Formula

Jun 23, 2014

I have a cell which is referencing another (=Sheet2!A1) that and I want excel to update the reference by 50 rows each time (Sheet2!A51) .

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved