# Function/ Formula To Output A Cell Reference

Feb 16, 2006
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 14 Replies
ADVERTISEMENT
Sep 19, 2012

I have a column A with date formatted as dd/mm/yyyy. I then have a column B which is =A, and I reformat the date formats to a custom format DDDD which gives me the day of the week that the date relates to.

I then try and write an if statement

e.g. =If(b1="Friday",1,0)

B1 always references the date which is stored as 40118, when I need it to reference the actual formatted value that I see in the cell e.g. Friday?

View 4 Replies
View Related
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
May 17, 2008

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 2 Replies
View Related
Jan 30, 2014

I have problem with a spreadsheet that I am trying to create.

I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.

Now for planning purposes, I would like to see how much of each item I have on order.

I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:

=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.

For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is

A108: ="A"&MATCH(D11,'purchase order'!A:A,0).

Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.

View 6 Replies
View Related
May 22, 2008

how can I use the cell function inside a NPV fuction as one argument of the start for the NPV string to calculate?

View 3 Replies
View Related
Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

View 2 Replies
View Related
Sep 2, 2008

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 9 Replies
View Related
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
May 28, 2008

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 3 Replies
View Related
Aug 13, 2008

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 2 Replies
View Related
Mar 20, 2009

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 3 Replies
View Related
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
May 21, 2014

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.

View 5 Replies
View Related
Apr 3, 2013

language

batch

WC

FC

zh

1

36

0

id

1

0

0

[code]....

in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.

View 2 Replies
View Related
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
Aug 21, 2009

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 9 Replies
View Related
Apr 21, 2014

I can assign a number of copies to print by adjusting ActiveWindow.SelectedSheets.PrintOut Copies:= , but what I want it to have the number of copies auto adjust to the value of a cell that contains a formula. I was hoping it would something as simple as Copies:=cell but no luck...

View 2 Replies
View Related
Apr 20, 2014

something that looks like =CELL.MIN(reference)

View 3 Replies
View Related
Feb 19, 2014

I'm trying to write a formula that would filter data from one column based on condition entered in a cell. This one works great with single numbers or text in a reference cell:

=IF((a2=C$3);a2;"") copied down the column

but if I try to filter all numbers greater than 2 (I eneter ">"&2 in cell c3) it doesnt work. Is it possible with if formula or should I use something else?

View 4 Replies
View Related
Mar 5, 2009

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 11 Replies
View Related
Feb 19, 2010

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 10 Replies
View Related
Apr 24, 2007

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 3 Replies
View Related
Sep 5, 2007

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 2 Replies
View Related
Feb 28, 2008

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 5 Replies
View Related
Feb 11, 2013

I am using this formula to output data into a cell:

=MID(A1,4,2)

The cell value is 50. I am wanting it to appear as 5.0 instead. Can I do this by adding to the formula above or will I need to utilize another cell to accomplish this?

View 2 Replies
View Related
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
Jul 24, 2009

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 4 Replies
View Related
Jul 10, 2009

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 2 Replies
View Related
Jul 28, 2009

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 4 Replies
View Related
Oct 2, 2012

I've in cell A1 an entry like this: 123*456*7890

=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890

=ISTEXT(A2) in cell 'A3' gives me: TRUE

BUT, =A3+1 gives me: 1234567891

Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?

View 2 Replies
View Related
Oct 8, 2009

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 9 Replies
View Related
May 10, 2006

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 7 Replies
View Related
Apr 24, 2013

I have numbers in cell A1 (say 3545852) as a sum of a data range. I want to display it like this "Rs. 35,45,852.00 Cr."

How can i do this. I used text function like this

="Rs. " & text(A1,"0,00.00") & " Cr."

But it displays "Rs. 3545852.00 Cr."

I want thousand separator also.

View 7 Replies
View Related
Nov 29, 2006

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 6 Replies
View Related
Jul 27, 2006

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 2 Replies
View Related
Apr 30, 2009

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 5 Replies
View Related
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
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
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