Free Microsoft Excel 2013 Quick Reference

Absolute reference to table column in formula

Hello,
What is the equivalent of the "$" when referring to table columns?

So if I wanted a reference's row to change but not column, I'd use $A1 so I could drag it down and across and it refer to the correct row and column.

How do I do the same with a table column reference, so that no matter where I drag the formula it always refers to the same column?

Thanks a lot,
JJ


Post your answer or comment

comments powered by Disqus
Hi folks,

A happy return to the world of Ozgrid for me!

Have started getting properly acquainted with some of 2007's new features, particularly the Tables functionality.

I want to refer to a column in a table, but the column will be determined by another value (1 column per month).

If I just enter the formula, the Autocomplete gives me:

=IF(TblData[[#This Row],[Compare?]],TblData[[#This Row],[Jan-08]],0)

where I want something LIKE:

=IF(TblData[[#This Row],[Compare?]],TblData[[#This Row],$A$1],0)

where the Compare? column contains TRUE/FALSE and A1 contains the month/column I want to retrieve.

Is this possible within a "Table" or should I just revert to INDEX and MATCH?

Thanks

I've got a (major) problem with the new excel 2007 table
functionality: an absolute reference to a column within the table
doesn't seem to work. This question pas posted before by someone else
but nobody seems to know the answer.

Problem:
In the good ol' days (excel 2003) you coluld use the - $ - sign for an
absolute column or row reference. $A1 would copy down to $A2 but would
copy the right as $A1 still. (see example)

The new 2007 table format gives a much clearer reference but the
reference seems to be relative for columns. If you copy a cell with
calculation with table columns references they behave as relative
column references.

---
Example:

I've twelve columns with months (C to N) and turnover in 100 rows.
Column A is customer (100) an colomn B is Business unit(4).

If I sum in row 102 I only have to write once:
=Sum(C2:C101)
in C102 and copy cell value over all months (C102 to N102) in this
row. This is called a relative reference.

Now, in row 103 to106 I want to sum by the 4 Business units defined
in B103 to B106.
I'll write in C103:
=sumproduct(($B$2:$B$101=$B103)*(C$2:C$101))
copying this cel over C103 to N106 would do the trick.

Now suppose i had put the above data in a 2007 table named turnover,
my sum =Sum(B2:B101) looks like this: =SUM(turnover[[january]]). This
works great copying as it is a relative reference. The sumproduct
would look like this in C103:
sumproduct((turnover[[businessunit]]=$B103)*(turnover[[january]]))

I want to have an absolute reference to turnover[[businessunit]] and
en relative reference to turnover[[january]]. I want something like
(see $ in formula)
=sumproduct((turnover[$[businessunit]]=$B103)*(turnover[[january]]))
but this doesn't work, nor everything else i tried and the internet is
very quit about this topic. Am i the only one have this problem or am
i the only one using tables? Is there - $ - like functionality for a
excel 2007 table column?

P.s.
Copying the formula to D103 leads to the incorrect:
=sumproduct((turnover[[january]]=$B103)*(turnover[[february]]))
(because the january column is next to the businessunit coloumn)

----

If you have the same problem, please post a 'support reply' to keep
this post active


When a formula is copied, a Relative reference is used. A Relative reference is the distance, in rows and columns, between the reference and the cell containing the formula.
For example: In cell A1, enter the number 100, and in cell B1, enter the formula =A1. Cell B1 is in a one column distance from cell A1. When the formula is copied from cell B1 to cell B10, the distance between the reference and the cell containing the formula remains one column, and the formula in cell B10 is =A10.

Select cell B1 (as in previous example). In the Formula Bar, select A1, (or just click on A1). Then press F4. The result is =$A$1.
Copy the contents of cell B1 to cell B10. Notice that the formula does not change, the formula reference remains constant as =$A$1.

The F4 keyboard shortcut has four combinations:
State 1: Absolute reference to both column and row, =$A$1
State 2: Relative reference (column) and Absolute reference (row), =A$1
State 3: Absolute reference (column) and Relative reference (row), =$A1
State 4: Relative reference to both column and row, =A1

Hi

I have a user form which contains a number of combo boxes. For the sake of description the left hand boxes describe how things stand today the right hand boxes ask user to supply entries for the future state. The number of rows in each combo box is 4 and both left and right sided combo boxes share the same list.

As I know that certain pairings in the left and right list row entries are not possible(and therefore user input errors) I need to test the users selection before submitting selections to the work sheet.

In my head I am thinking if I can add an extra column to my list (a numerical score) and increase the column count to two column in my combo boxes I could test logically the numerical values in the left and right side. So for instance if the left combo selection was "Disabled before accident" with a numeric value of 1 (in column two of the list row data) and the user entry in the right combo was " Not disabled after accident" with a list row second column value of 2 I could simply test for combo box left column 2 is equal to combo box right column 2 to validate each side.

I still need the user entry in the first column "Disabled before accident" for example to populate my cell in the worksheet so how would i use the numeric value in the second column of the combo boxes just for the purposes of the Userforms vba to test the user selection?

Many thanks

Hey folks,
Love using tables in 2007, but get frustrated at adding columns and copying formulas between columns. On a table with subtotals, when you add a new column, the subtotals don't follow. Or with column formulas that reference other column data like

=Time[[#This Row],[Column143]]+1

that you want copy to other columns, the formulas stay absolute: Column143 will show up in all columns as exactly that.

The only way I've found to get these to work correctly is to start in a good cell and drag the 'extend formula' + in the corner to the new cells.

I got 2 problems with this:
1. I can never remember this.
2. I'm a UNIX guy, so my mouse skillz are lame.
Is there a keystroke combination with -c / paste special that can do this instead or any other method to achieve the same result?

Much grass in advance.

The formula that is giving me trouble is VLOOKUP. I need to use absolute
references for the table array so that if the table or the cell that has the
VLOOKUP is moved the cells automatically update. The problem occurs when I
need to copy this information from one tab to another. The table array has
absolute references (for the column and the row) so the cells do not update
or change to some new specified cells on the new tab. I still need the cells
to be absolute references but I would like for them to update to the cell in
the new tab. Can I have my cake and eat it?
--
Tim

Hi All,

Is there a way of referring to a specific column in a named range in a formula?

I have a range named "Database" that is 37 columns wide and a variable # of rows deep. I want to write a formula that finds all instances of a text string in column 5 of "Database and adds up the corresponding values in column 7 - essentially a sumif using co-ordinates to a named range.

Any thoughts?

Cheers,

Jen

The new table feature of Excel 2007 is IMO the best improvement over the 2003 version.

I have 3 questions about references to these tables:

1) When you reference to a cell in a table on the same row, you get the reference "#This Row". Why isn't there a "#This Column" reference?

2) Normally when you want to "fix" a reference to a cell you use the $ sign by hitting F4 (i.e. A1 -> $A$1). How can you do this with tables (Column1 will not change to $Column1 when hitting F4)?

3) When I have = sum(Column1) above Column1 and I drag this to a cell above Column2 I get =sum(Column2). However, when I have the formula {=sum(Column1)} this doesn't work. Why?

Thanks in advance.

Say I've named the range of rows 10 through 50 as "Rowset."

Then I want to put a formula in C8 that returns how many cells in C10:C50 are equal to "0".

I can use COUNTIF(C:C Rowset,"0"), which works fine.

But what if I also want to do the same thing in D8, E8, etc. Do I have to explicity say

COUNTIF(D:D Rowset,"0")
COUNTIF(E:E Rowset,"0")

etc.? Or is there some way I can refer to the current column without naming it?

I realize I can define the first formula as above and then copy and paste to get corresponding formulas in the other cooumns. I just wondered if there was one formula I could define so that it would work the same in any column.

Thanks in advance for any help!

Jim Guinness
Eastern Massachusettts, USA

My sheetnames are named with numbers : 1, 2, 3 to 20.

On a seperate sheet (called "employees") I have a column A (with numbers 1 down to 20) in column B I have employee names e.g. 1 is Jo Soap, 2 is Mary Poppins etc.

I want to create a formula on each sheet (1,2,3) stating that if the sheetname is equal to the number on the "employees" sheet, type the employee name.

How do I refer to the sheetname in a simple formula?

A colleague of mine made a spreadsheet which has a whole array of formulas in, however he did not use absolute references ($) in the formulas and now I need to change the worksheet which will make the formulas incorrect - is there a quick way to apply the $ sign's to a whole block of different formulas instead of going into each one individually?

Thanks again!

I am aware of a few ways to reference a cell to the left of the active cell,
such as:
=INDIRECT("RC[-1]",0)
=OFFSET(B1,-1,0)

I am curious to know if there is a way to link to this cell as an absolute
reference, such as being in cell B1, and referencing $A$1, and then moving
down to the next cell, by means of:
ActiveCell.Offset(1, 0).Select

…and continuing in this fashion while checking values in Column A:
If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then
‘do stuff here =INDIRECT("RC[-1]",0)
‘do stuff here =OFFSET(B1,-1,0)

If ActiveCell.Offset(0, -1) ActiveCell.Offset(-1, 1) Then
‘ stop the OFFSET function or INDIRECT function, and make new absolute
reference to left of the currently active cell.

I am having a difficult time getting the getting the absolute reference to
stick in VBA, and then getting it to return to a relative reference when:
Offset(0, -1) Offset(-1, 1)

Does anyone have any ideas about this?

Thanks a bunch!
Ryan--

I'm attempting to write a macro which pastes Excel
cells into Word, and manipulates the formating. When I
run the macro, even on the same table used to record
the macro, I get:

RunTime Error 4605 "Select Column method or
property not available because some or all of object
doesn't refer to table occurs."

asterisked line causes the error (as indicated by a
yellow arrow) :

End With
Selection.Tables(1).Select
Selection.Columns.PreferredWidthType =
wdPreferredWidthPoints
Selection.Columns.PreferredWidth =
InchesToPoints(0.92)
Selection.Move Unit:=wdColumn, Count:=1
** Selection.SelectColumn**
Selection.Collapse Direction:=wdCollapseStart
Selection.Move Unit:=wdColumn, Count:=-1
Selection.SelectColumn
Selection.Collapse Direction:=wdCollapseStart
Selection.Move Unit:=wdColumn, Count:=-1
Selection.SelectColumn
Selection.Move Unit:=wdColumn, Count:=1
Selection.SelectColumn

Any ideas?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi everyone.

My scenario is a very common one.

sheet 2 ("data sheet"): data
sheet 1 ("results sheet"): results from calculations based on data on sheet 2

I want the formulas one sheet 1 to have absolute references to the data cells on sheet 2 … so that a formula referencing 'data'!A1 will *always* reference the top left cell on sheet 2, no matter what happens on sheet 2 (cutting, pasting, deleting, inserting rows, etc.). This is what I will call henceforth "truly absolute reference" in this thread.

One of the rationales behind this approach is that you can lock the results sheet (even with a password) and hand the whole workbook to co-workers who fill in the data sheet or data sheets. Because otherwise they would inevitably screw up your formulas on sheet1, creating #REF! errors, etc. when they cut/paste/insert_rows/delete_rows on sheet2.

Many users find themselves in exactly the same scenario, by way of example:

Originally Posted by brandoncartwrig I'm using Excel 2003 for payroll deductions. I have one workbook with two worksheets. One worksheet (information sheet) is were I enter all of the information. The other (payroll sheet) calculates the information from the first. I want to use an indirect formula so if a co-worker goes into the information sheet and cuts and pastes something, it won't change the formula on the payroll deduction sheet.
I have posted an example .XLS file as attachment. But please bear in mind that it is intended as a way of exemplification only, while the solutions should apply to the general *concept* (calculations on sheet 1 with truly absolute references to the data on sheet 2).

In this simplified example of one of my personal projects, I have a list of audio files on two data sheets together with some attributes (artist, album title). The main (calculations) sheet compares the data sheets and checks if the attributes are identical or not. There is a column labeled "sync" which checks if the file lists on all 3 sheets are in sync. It returns "ok" if e.g. in row 7 of all sheets the same audio file is listed.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The general question is: HOW CAN I CREATE TRULY ABSOLUTE REFERENCES ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The problem of the answer not being obvious lies in Excel's misleading terminology. (To explain why, I will use 2 expressions: "referrer" and "reference" with "referrer" being the formula which references (=refers to) the reference ... and with "reference" being the cell referenced by the referrer.) What Excel calls "absolute reference" is actually only just a copy protection for the referrer and has nothing to do whatsoever with a truly absolute reference which will always point to a cell with given X/Y coordinates. To corroborate this I quote Chip Pearson at Pearson Software Consulting:

Even with an absolute referencing style, Excel will still change row and column references when you insert a row or column. To have a truly absolute cell reference that will not change under any circumstances, use the INDIRECT function. For example =INDIRECT("A1") will always refer to cell A1, regardless of any changes made to the worksheet. I am not saying the dollar symbol is useless the way it is implemented. But Excel should have called it "copy protection" or something along those lines. What Excel apparently failed to implement is a truly absolute reference, always referring to a specific cell (with X/Y coordinates). This is what should be called "absolute reference" and should have been implemented in a way just as user-friendly as the dollar sign. Why they didn't just pick a different symbol, e.g. the pagraph sign is beyond me. This is how I would have designed it:

$A$1 ..... term: "copy
protection" ........ function: REFERRER does not change when moved (i.e. the current function of $A$1)
§A§1 ..... term: "absolute reference" ..... function: REFERENCE does not change (even when the content of the
referenced cell is moved elsewhere).


This is the reason why others and I myself have been trying to create a list of truly absolute references

§A§1
§A§2
§A§3
§A§4
§A§5
§A§6
[...]
§A§100

So what is the bottom line? Is it true, that truly absolute references simply do not exist Excel? Is the user forced to workaround this indirectly by way of the INDIRECT function (which is basically just fooling Excel into not updating a reference because it is hidden inside a string)? Is there no other way? If so, that seems to be a http://de.wikipedia.org/wiki/Schildbürger type of scenario.

Hello,

I have a set of data formatted as a table. Formulas refer to the columns in the table using things like "=IF(MyTable[[#All],[Status]]=Active,...". My problem is that when I add a new column or drag the formula to the right the column reference changes. For example if "Date" is the column next to "Status" on the right, when I drag my formula to the right, it says "=IF(MyTable[[#All],[Date]]=Active,..." but I still want it to be "Status". If I add a column directly to the left of Status, say "Rate", then my original formula now changes to "=IF(MyTable[[#All],[Rate]]=Active,...". This last example is what is more important to me to fix.

Is there something I can do in my formula that keeps the "Status" as the column of interest even when I add new columns to the left?

Thank you,
**

It would be useful for absolute references to be added in Excel when holding
a key down on the keyboard, instead of having to re-edit each cell every
time. Say, hold down the mouse button, press 'r' to toggle rows between
absolute and relative, and the same for 'c' and columns.

Hi! (Excel 2000)

How do I make reference to the current cell in the formula of that cell.

I want to type in a currency number, then when I press enter, a calculation will give me a result which will be the current value of the present cell instead of what I entered.

Then I will apply the rule to each respective cell in the same column, with each cell having the same formula but using it's respective cell reference. (I'm hoping the paint tool knows to use each respective cell value).

For instance:

IF(CELL("type",[THIS CELL]) = 'v', [THIS CELL]/7)

I need the proper syntax for THIS CELL.

Thanks

Andy

Hi,

I know that in SUBTOTAL formulas in some other formulas you can reference an entire column in another worksheet as follows:

worksheet1B:B

However, this does not seem to work in array formulas (the ones where you use CTRL+SHFT+ENTER). I have an =AVERAGE(IF... formula and I have to reference like rows 2 to 5000.

Is there a way to reference an entire column in array formulas? Thanks.

How do I refer to a specific column in a single number. For example, if I have a dollar amount in a field, I want to calculate a value based on the number in the 10's column, the 100's column or the 1000's column. I'm trying to avoid having to type a dollar amount using separate field for each column. Thank you for any pointers.

I have to copy a range of data from third column of sheet2 to 5th column on sheet1 and so on..

I saw a thread in this forum discussing this.
Kris has replied with a code :


	VB:
	
Worksheets("Sheet1").Cells(StartRow, Col).Resize(EndRow).FormulaR1C1 = "=Sheet2!RC" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Which part of this code should I edit to suit my requirement?

I am interested in counting only those cells within a range whose text is not indented. So far, I've had zero luck even finding a variable or COUNTIF formula that even comes close to addressing this.

This isn't the first time I've needed to refer to a cell's formatting (font, bold/unbold, font size, font color, etc.) within a formula. Any ideas?

Thanks!

I have a user defined function that I've saved in an add-in. I've also had several of my co-workers save the add-in on their computers. In my spreadsheets, when I reference the user defined function, ie. blacks(x,x,x,x,x,x), everything works fine. When my co-workers open up the same spreadsheet on their computers, the cell in which i reference my user defined function, now has an absolute reference back to my add-in (ie. 'C:Documents and Settings<user>Application DataMicrosoftAddInsBlacks.xla'!blacks($P5,$O5,$AB5,$Q5,$Z5,$AD5)). How do I keep it a relative reference instead of an absolute reference? Any help would be greatly appreciated.

Thanks.

I need to insert column in a specified table depending upon the name of the other column.

like if the name of the column is "xyz" then i need to insert a column left to it.

Note: i na table.

kindly help me with the vba code for the above

I have a macro that copies specific data from one workbook to another. I need to be able to reference a specific sheet in that other workbook absolutely.

The macro checks that the workbook to copy the data into is open that then stores that workbook in a variable.

Set OtherWorkbook = Workbooks("Other Workbook.xls")

Now i need to refer to another sheet in the workbook absolutely in order to select it.

OtherWorkbook.Sheets(XX) won't work because the index number changes when the sheets are moved around, which will be changed by the user.

OtherWorkbook.Sheets("Tab Title") won't work because that has a good possibility of being changed by the user.

I'd like to use the Sheet (Name) property in VBA, but syntax like:
OtherWorkBook.Sheet12.Select doesn't work either.

Thanks.


No luck finding an answer? You could always try Google.