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

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

- Indirect Reference To Table Column. 2007
- How to make an absolute reference to an excel 2007 table column
- Changing an Absolute Reference to a Relative Reference or Vice Versa in Excel 2007
- Refer to bound column in Userform combo box
- Extend subtotals to new columns in a table.
- Transferring formulas with absolute reference to multiple tabs
- Formulas: referring to specific column in named range
- References To Tables In Version 2007
- How to refer to current column in a formula?
- Refer to a Sheetname in a formula
- Applying absolute reference ($) to a group of formulas
- Absolute Reference to the Left Until Change in Row
- "Select Column method or property not available because some/all of object doesn't refer to table"
- "truly absolute references" to cells using X/Y coordinates
- Make column headers constant in formula
- Quick method to add absolute references in Excel using keyboard
- Reference to "current cell" in formula?
- How do I reference an entire column in an array formula?
- Refer to Specific Column in a Number
- Add A Sheet Formula Reference To A Column in Another Sheet
- Referring to text formats in formulae
- Cell has absolute reference to my add-in
- How to insert column in a table
- Absolute reference to sheet in other workbook

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

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

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

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.

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

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

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.

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

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?

Thanks again!

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

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!

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.

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,

**

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.

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

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.

I saw a thread in this forum discussing this.

Kris has replied with a code :

VB:Which part of this code should I edit to suit my requirement?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

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!

Thanks.

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

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.