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. AB 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
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
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
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
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
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
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
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
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 20082009Clarion countyJanuary 09[3854009.xls]M805'!$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 3854009 portion of the formula) in the fromula I have atempted to use the following: =LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 20082009Clarion countyJanuary 09["'"p8"'.xls]M805'!$F$11:$G$41,1) but excel returns an error and highlights the following portion of the formula: '\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL
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
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 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 reorganize 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
