Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Function/ Formula To Output A Cell Reference


is there a formula that would have as it's output the cell address of the
same cell where the formula has been entered.

e.g. In cell A10 I input a formula whose output is the cell reference A10.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Use A Formula As A Cell Reference In A Function
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 Replies!   View Related
Variable Cell Row Reference In Formula/Function
I will show with an example of what I would like to do. I italized and underscored the part of the formula.

---A----B
1 24 formulaB

formulaB = "= CELL("contents",DifferentSheet!G(A1))"

I want B1 to contain the value from the cell G24 in sheet "DifferentSheet". I want to use the value in A1 to determine what row on "DifferentSheet" I should use.

View Replies!   View Related
Formula/Function Reference To Cell Address In Another Cell
how can I use the cell function inside a NPV fuction as one argument of the start for the NPV string to calculate?

View Replies!   View Related
Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference
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 Replies!   View Related
Make A Formula Output A Blank Cell?
I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.

View Replies!   View Related
Using A Formula As The Reference In An Offset Function
Is there any way to use a formula rather than a direct cell reference in the Reference argument of an Offset function?

For example, rather than this:
=OFFSET(H3,0,-7,1,1)
I want to do this:
=OFFSET(max(H:H),0,-7,1,1)

Effectively replacing the starting cell of the offset function with a dynamic formula that returns the maximum value of an array.


View Replies!   View Related
Variable Reference In GetPivotData Function/Formula
I am trying to do a "getpivotdata" formula on a cell in certain file that gets info from a pivot table on another file. However the name of the second file (the one where the pivot table is found on) might be different each time so I created a variable for the second file name but I canít figure out a way to use this variable in the get pivot data formula as I am not very experience with VBA.

Template = Application.InputBox("What is the name of the file ending with (.xls)")
If Template = False Then
cont1 = MsgBox("Please write the name correctly using (.xls)!", vbOKOnly)
If cont1 = vbOK Then
Exit Sub
End If
End If

View Replies!   View Related
Function/Formula To Reference Changing Rows
I have a table of consolidated data that consists of the date at which certain payments occur and their amounts that other sheets need to reference to perform calculations. I am currently using the formula below to put the data under the correct date column. =INDEX(ImportedData!$B$10:$DB$10,MATCH(B3,ImportedData!$B$4:$DB$4,FALSE)). The problem that I have is that the INDEX range, currently B10:DB10, will need to change depending on the number of files that are consolidated ie with more files the row will increase.

View Replies!   View Related
Dividing Output Of Sumproduct Formula By Count Of Specific Cell Contents
I've got the following sumproduct formula (which seems to work):

=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))

B17 is a keyword to be found in the Product_Keywords range

I'd like to modify it so that:

ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1

Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,
but how would I keep track of the number of commas in the cell?

View Replies!   View Related
Cell Reference :: Formula To Reference New Cells
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 Replies!   View Related
Excel Function To Visualize Cell Reference
i have two excel files, with many data inside

i need a function that do this:

in the cell A1 i want to visualize the cell A1 of second file
in the cell A2 i want to visualize the cell A13 of second file
in the cell A3 i want to visualize the cell A25 of second file

i explain: in the first file i need a column that in each line show the first cell + 12, then +24, then +36.

file 1 is 1.xls
file 2 is 2.xls
for example A1 have the value 1

if in 1.xls cell A1 I insert =[2.xls]worksheet1!A1 and in A2 =[2.xls]worksheet1!A1+12 in A2 i obtain the value of A1+12=13, but i need to obtain the valour of A1+12=value of A13.thanks

View Replies!   View Related
Function For Reference Of Cell Row/column
I have a column of numbers, on which i would like to perform subtraction, in a way that the cell in the (i)th row will be subtracted from the one in the (i+x)th row, while x is a parameter that the user can change as he wishes.

My problem is how to refer to the cell address and still use this conditional reference...
If anyone has an idea how to realize this calculation (which should be peanuts in Matlab, but apparently more problematic in Excel),

View Replies!   View Related
Countif Function With An Inequality Reference To A Cell
I have a problem using COUNTIF function. I want to count cells say in range A1:A10 that are smaller that a value in a cell let's say B1. Neither of these works:

=COUNTIF(A1:A10; "<B1")
=COUNTIF(A1:A10; <B1)

How to do it? I saw your answer of Dave to a similar problem, but I still do not understand it.......

View Replies!   View Related
Match Function For Cell Reference Row Number
I am trying to acccess a cell value from a seperate worksheet. The cell reference needs to be generated by a seperate formula. In this example

=DSInfo!C28 // provides the correct result i.e the contents of cell C28 on worsheet DSInfo =MATCH(A4,DSInfo!C1:C35,0) //provides the correct row number - in this case 28

However on trying to combine the two =DSInfo!C&MATCH(A4,DSInfo!C1:C35,0) // provides only a formula error. I'm guessing this is a simple syntax error on my part but after hours of trying various ways I'm still having no luck.

View Replies!   View Related
Start Function Range Reference Row From Cell Value
Is there a way to start match from a relative position?

Say a match I have in column "A" returns 344. Is it possible to start a new match for column "B" from that spot, instead of having it return a match starting at the top?

View Replies!   View Related
Using Match Function Output As A Variable
I am having trouble using the MATCH function in my code. I am looking for a match in one worksheet from a cell in another. I have defined these cells and ranges as variables. However, it doesn't seem to be working with my application. I would like to find the row of the matching cell and use the output of the MATCH function to determine the next step in my code with an IF function. Here is my code. (NOTE: this is my edited code, I removed irrelevant pieces. The major problem is the MATCH function.)

View Replies!   View Related
Converting The Output From A Match Function Into A Location
I would like to know if it is possible for the output from a match function to be correlated to a location? The output I receive is just a number. I would like to use this number as the row, but I would like to hyperlink it into a formula so that I can create a macro for this process.

In essence I would like for a number in one cell to become a location in another cell.

View Replies!   View Related
Storing/Copying/Pasting MID Function Output
I am having some trouble with the "mid()" function. I have it saved as a variable which pulls 6 digits from another variable. I was trying to paste from the mid variable into my excel worksheet but it keeps on telling me that there is an object required error.

The goal is to input the "mid()" output in my code into a cell in the spreadsheet.

Here is some sample

View Replies!   View Related
VBA To Give Static Output Of LEFT Function
What I would like to do is output the first 4 characters of a cell into another cell. eg Cell A1 has "1234 Sydney". If I just use the manual formula "=left(A1,4)" it would give me the correct output "1234" in say cell B2, but as a formula. As I need to automate this in a macro, how could you code this in VBA to output the String "1234" into cell B2(ie the actual output rather than a formula)?

View Replies!   View Related
VBA Function: One Range As Input, Two Ranges As Output
I want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:

Public Function xyz(a() as Variant) as Double

There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?

A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.

An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.

View Replies!   View Related
Multiply The Value Of Strings/integers For An Output In A Function Macro
I am attempting to multiply the value of strings/integers for an output in a function macro. I know it's a simple question, but a simple answer would be suffice. I'm in the middle of a massive brain fart...

View Replies!   View Related
Function Back Variables: Function Give Two Or More Output Variables
Can a Function give two or more output variables. e.g.

Sub a()
x = 5
result = Y(x)
End Sub

Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.

View Replies!   View Related
Index - Match Function To Output Data Based On User Input
I have a list of data that I'm using the index - match function to output data based on user input.

For example, I have:

1 10
2 15
3 20

So when '1' is selected another cell outputs '10'.

However what I want is for example if 1.25 is input, I want it to interpolate to spit out the correct number between 10 and 15.

View Replies!   View Related
Use A Cell As A Reference In A Formula
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 Replies!   View Related
Reference The Formula Of Another Cell
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 Replies!   View Related
Formula To Reference A Cell
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 Replies!   View Related
Convert Cell Reference Text To Cell Formula
I am wanting to convert a cell reference text
"=$A$1"
to an actual cell reference
=$A$1

Manually I can go through each cell and click F2 + Enter and Excel automatically changes it.

I have tried recording a macro whereby I click through each cell with F2 + Enter but the VBA writes the actual formula "=$A$1" rather than the process. This does not work as the cell reference is variable.

I'm NOT wanting an external cell to convert it for me
i.e. INDIRECT(CELL)
because I am wanting to copy the answer to another independent spreadsheet

I'm NOT wanting to paste values
i.e. return the answer from cell $A$1
because I want the cell reference to remain within the cell.

View Replies!   View Related
Returning A Cell Reference With A Formula
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 Replies!   View Related
Replace Name In Formula With Cell Reference
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 Replies!   View Related
Formula To Display Cell Reference
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 Replies!   View Related
Way To Change A Cell Reference Within A Formula
Is there a way to change a cell reference within a formula. ie: If the formula is =A1 (in reference to cell A1) and I wanted a new cell to have the reference =A10. Can I make a formula which was A1+9, and therefore lookup A10.

View Replies!   View Related
Reference Cell Colour In Formula
Is it possible to use the colour of a cell in a SUMIF formula? i.e On the worksheet differnt entries are colour coded depending on what they relate to. If I only want to add all the cells that are Blue can it be done?

View Replies!   View Related
Formula To Increase Cell Reference
I have to reference to another sheet a fixed increase of moving 6 rows across. What would be the formula for it?

For example.
First formula would be =Sheet1!A1
What would be the formula for the subsequent cells. (eg. =Sheet1!A1+6) obviously doesn't work.

View Replies!   View Related
Varying A Cell Reference In A 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).

View Replies!   View Related
Autofilter Cell Reference Formula
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 Replies!   View Related
Using Cell Value In File Reference Formula
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 Replies!   View Related
Formula Result As Cell Reference
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 Replies!   View Related
Cell Reference For A Formula To + 5 Each Time
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 Replies!   View Related
Using Cell Values In Reference Formula
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 Replies!   View Related
Formula Using Sheet Reference In Cell
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.

View Replies!   View Related
Fix Formula Cell Reference
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 Replies!   View Related
Formula Reference To Cell On Another Sheet
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.

View Replies!   View Related
SUMIF Formula With Reference To A Cell
I attached a file I made conditional formatting on cells C4..G6 which makes any number less than value in cell C15 invisible. In line " Total" I used SUMIF formulas which ignore any value less than 30. However the formulas should not contain static numbers but rather a reference to cell C15. But I simply cannot make it work.

View Replies!   View Related
Cell Date In Formula Reference
Using cell contents in formula to Populating cells with values from closed workbooks
I am trying to create a summary table of data (e.g. orders) from previous weeks. In the table I am creating, the left hand column contains the Monday date of the week to be summarised along the row. I want the cells to show the values from other spreadsheets when those spreadsheets are closed, but I want the formula to pick up the date of the week that is in column A. I have attached a sample spreadsheet to try and make it clearer.

View Replies!   View Related
Return Cell Reference Of Large Formula
I am creating a summary financial sheet in Excel 2003, Win XP. It needs to show the top 10 sales variances from a large data list, along with the associated department.

I can bring in the top 10 largest variances using Large(array,k), but because variances are either positive or negative, I'm not really showing the true top 10. To fix this I can use absolute values via Large(abs(array),k) but then I don't know how to convert the value back to its original sign (positive or negative).

If I could somehow get the cell reference that the Large(abs(array),k) formula points to, I could do something like this:

=Large(abs(array),k) * (Large Cell Reference/Large Cell Reference)

I'm afraid to use the address function because of duplicate sales variances. I often have several variances of with same value, so I might not truly be pulling the address that the large(abs(array),k) is pointing to.

View Replies!   View Related
Vlook Up But The Reference Cell Indicates Formula Error
I am trying to do Vlook up but the reference cell indicates formula error even if this has values.

I have to do F2 in every cell to make the Vlookup work. Is there any way to remove this?

View Replies!   View Related
Sheet Cell Reference And INDIRECT Formula
I have this formula in excel 2007:

=COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H").

Blad is Dutch for sheet, by the way.

In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.

W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.

I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.

View Replies!   View Related
Formula To Reference A Specific Variable Cell
Not been around for some time now but have to do my annual update for this workbook and implement some new changes.

In the attached example workbook, I have created some formulas which reference a particular cell for each client e.g.

View Replies!   View Related
Formula For Cell Value Based On Reference Or Position
HTML Imagine the following table (Ax, etc is the cell position):

A1=John C1=Name D1=Date E1=Version F1=IP
A2=10/2/2008 C2=A1 D2=A2 .... ....
A3=Client version 7.01 C3=A1+5pos D3=A2+5pos
A4=172.24.3.5 C4=A1+10pos D4=A2+10pos

A6=Michael
A7=10/2/2009
A8=Client version 7.03
A9=172.24.3.80

A11=Susan
A12=2/2/2007
A13=Client version 7.02
A14=172.24.3.200

I have a table of data on the left side and I want to re-organize just like the right side. Let's take C2 as an example, the value for that cell = A1;
Now, C3 in the other hand, the value for that cell = A1 + 5 positions down;
C4 = A1 + 10 positions down;. The same type of formula applies for the remaining columns on the right. This seems like an easy solution, but it's not easy when you don't know it... :

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved