# Function For Reference Of Cell Row/column

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

## Inserting An INDEX,MATCH Function Into A HYPERLINK Function Instead Of Cell Reference

Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

## Using Variable Column Reference In VBA Worksheet Function

Jul 15, 2014

I have the following line in VBA which works.

[Code]....

I have a variable for the column adress of the second part of the formula so it looks like this:

[Code] ....

But it says I don't have the correct syntax.

## Contains Function: Function That States If A Cell In The Email Column Contains A String Or Value From The Names Column

Oct 13, 2009

On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,

I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.

## Reference To Cell Column Letter By Reference To Another Cell Value

Apr 25, 2014

Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.

## Function That Returns MIN (or MAX) Cell Reference

Apr 20, 2014

something that looks like =CELL.MIN(reference)

## IF Function With A Condition From Reference Cell?

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?

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

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

## Excel Function To Visualize Cell Reference

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

## Countif Function With An Inequality Reference To A Cell

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

## Match Function For Cell Reference Row Number

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.

## Start Function Range Reference Row From Cell Value

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?

## Variable Cell Row Reference In Formula/Function

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.

## Formula/Function Reference To Cell Address In Another Cell

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?

## Using Formula With Cell Reference To Work In VLOOKUP Function

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.

## Reference Every Nth Cell In A Column

Aug 26, 2009

I have a data entry spreadsheet that contains 120 data entry panels each of 10 rows. Each panel is separated by 3 rows (1 of which is hidden). When entering data to the panel the user is required to enter a date in column F of the data entry panel. The number of used rows in each data entry panel can vary from 1 to 10.

The entry of a date in the first Column F cell in each entry panel stamps today's date in Col F in the first row below each data panel (the hidden row) and that date is then linked to a separate summary sheet to show the date of data entry. The code I'm using for the date stamping routine is below, and although it works it is painfully slow and I'm sure very inefficient. I've had to break the code into the three sections as shown below because there seems to be a limit to the number of individual cells I can reference in each of the range statements. (Is there a limit or is the length of the range statement causing some other problem?)

## Keep Reference To A Cell When Column Is Deleted

Nov 28, 2007

I have a spreadsheet in excel with scheduling information, each group of data (job) is made up over 2 columns and 6 rows and is in a block (this is so it is easy to view and manouvre)

I have a link in another worksheet that extracts information from the cell if a criteria in the group is met!

At the end of each day I need to delete the columns (B and C, which make up that day) in the schedule.

When I do this all the references in the other worksheet are messed up, even though the next day is moved to columns B and C.

I would like the link to the other worksheet to automaticaly recognise the new values in column B and C as the reference.

## Layout Cell Reference Column

Jul 8, 2009

I am not sure how this is possible without doing copy pate transpose or individually refereing each cell to source. I would like the heading to be presented as in sheet1 which is sourced from Source sheet but if anychanges are made to source it updates the Sheet1. I have attached an example to illustrate. (Headers are not fixed number)

## Column Reference To Cell Transfer

Jan 18, 2010

I am trying to get information from 1 sheet to another. What i need to do is take the information from sheet 2 column H2:H26 and put it in sheet 1 cell I4. i already have the vlookup information in the other cells that i need but for what i need this for is different than a vlookup. basically i want to be able to type in a name (from sheet 2) in cell I4 (sheet1) and all the information that i need auto populates for me like i already have. I was able to do this with a drop down menu but that wont work as the information will change weekly,

## Reference Changing Column On Specified Cell?

Dec 9, 2013

I have 2 Sheets in Excel: Sheet 1 & Sheet 2

Sheet 1 has some basic data (numbers) in a column, say AB1, AB2, AB4, AB15 (specific cells)

Sheet 2 basically references the data in a nicer template, with the basic forumala: ="Sheet 1"!AB1, etc..

Now...Sheet 1 has a new column inserted and new data added weekly, so AC1, AC2, AC4. AC15

How do I ensure that Sheet 2 recognises the new Column Insert (incremental letter) and references it automatically?

## Change Column And Cell Reference Name?

Apr 25, 2014

I have a massive excel sheet with 300,000 rows and 100+ columns. When reading back through my formulas, it can get very confusing. Example: "=(AZ9*5 + 1)+BH9/2 +(AP9*0.75)" I then have to figure out what each column letter is representing. Instead of this, could I rename a column so I can reference the variable name? Example:

Instead of =CONCATENATE(A4, " ", B4)

have

=CONCATENATE(FirstName4, " ", LastName4)

## Reference Column To A Single Cell

Jan 11, 2007

I'd like to reference Column A to a single cell in a different sheet.

=StoresServiced!A2

Returns the value of A2 in the sheet called StoresServiced to another sheet.

What I want to do is, the values from A2 to A102 to show in one cell, separated by commas. So if theres value in A2 (8009) and A3 (8010), I want the active cell (ex. M43) to look like this -> 8009,8010 -- so on and so fort

## Change Reference Cell From Column To Row

Jul 25, 2008

I am using this formula

=IF(\$A5="Select","",MAX(INDIRECT(SUBSTITUTE(\$A5,"/","")&"m1440"&"!d:d")))

It looks for the sheet based on A5
It finds the max number from the sheet in Column D

This is what i would like to do please....

On this found sheet - Column A has dates in this format yyyy.mm.dd

I would like to enter a date in a cell (say for example AA5 on main sheet)

Can the formula above be adapted to:

Look for sheet based on A5
then
Look at date entered in AA5
then
use Vllookup to find row containing date in AA5 (making table range the ENTIRE SHEET found based on A5)
then
Return value from Column D

## LOOK For The First Full Cell In Column That Is In Reference To Cell On The Left?

Sep 4, 2013

I have got this working so far:

=LOOKUP(D9,'Financial Snapshot'!\$H\$5:\$I\$95)
D9 = 1.01
D10 = 1.02 etc

[Code]....

## Reference Cell In Macro Using Row & Column Numbers From Cell

Aug 11, 2008

I'm trying to reference a location on a spreadsheet in a macro by using the values in two cells (the idea is to then paste to this location):

D2 is 17 (the row)
B4 is 2 (the column)

therefore the location is B17 (R17C2), but can I get this to work...? The values in these cells can change depending on selections made which is why I want to use the cell references rather than the absolute!

## Using VLookup (But Reference A Cell As Column Index)

Aug 10, 2012

How can I do the following....if for example I have the following vlookup;

Code:
=vlookup(A12, 'sheet 2 \$A\$2:\$ID50\$, 3, false

How can I change the column index i.e. the 3, to reference to a cell.

Code:
=vlookup(A12, 'sheet 2 \$A\$2:\$ID50\$, H1, false

The above example doesn't work but I'm sure something can be done using TEXT or VALUE

## Incrementing Cell Reference In Column For Data In Row?

Sep 7, 2012

Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example numbers 50,60,70,80 are put in cells A6 to D6 (horizontal)

What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6

## Stop Formula Column Reference Changing On Insert But Not Row Reference

Mar 6, 2008

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.

When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.

## Conditional Multiple Column Unlock Via Cell Reference?

Aug 2, 2013

I have an inventory log that requires multiple cells in different columns to be unlocked based on a reference cell's input.

So, if a cell in column E has "MORNING" entered then cells L/M/N are unlocked and said user can input data for that row, and only that row. If anything else is in E, then L/M/N are left locked.

Is there a way to do this without coding, just using regular IF() in the cell directly; IF(ISTEXT(E3)=MORNING, Unlock, KeepLock)? I know that's nowhere close to being a legitimate statement, but it's the best way I can translate my thoughts.