Free Microsoft Excel 2013 Quick Reference

Absolute reference to table column in formula Results

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

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

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

I have a large workbook with 50 or so worksheets that have the same layout.
I would like to create a summary table of data in the worksheets in a table
on a new worksheet. My aim is that each row in the table will be one of the
worksheets and each column will be a specific cell in the worksheet.
I can do this manually by copying formulae with absolute references to one
of the sheets, and then changing the name of the sheet in the formula, but
this would be quite laborious.
Does anyone know a quick way to do this, through Excel functions? I do not
know how to do macros or Visual Basic.

On WorksheetA I have a long list of names that are currently 1 row apart (i.e. Name1 is $A$1, Name2 is $A$2, etc.) in the same row as the name there are a number of columns of values such that a row looks like this:
Name1 23 36 1 15.2
Name2 24 34 0 17.8
etc.

I want a table on another worksheet to refer to each of these absolute cell references - is there an easy way to do this without having to type in every absolute reference in every cell in the new table on worksheet 2? Basically I want to increment the reference by one row going down or by one cell going across rather than having to type in each cell indivually?

I know I can cut and paste special the values or the formulas into the 2nd worksheet but I want to refer to the actual cell in every case

I thought after doing two rows I could pick up the corner of the selected box and pull it down the required number of rows but that doesn't work it merely replicated the last row.

Not sure I have made myself clear

Thanks for reading this anyway -
Ashbo

Sorry all, another one for you. I am inserting a VLOOKUP formula via vba and the copying it down the column. I'm struggling to figure out how to make the table array absolute references.

I want (once it gets into the cell):
=VLOOKUP(A2,Final_Mailing_List!$B$1:$AZ$5000,1,FALSE)

the vba I'm using is (ignore the minor cell reference differences, I'm focusing on the $ issue!):
ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-1],Final_Mailing_List!R[-1]C[-1]:R[5000]C[50],1,FALSE)"

How do I get Final_Mailing_List!R[-1]C[-1]:R[5000]C[50] to not be different when I copy it down the column? I've tried Putting cell references in, but failed.

Thanks folks

Here's my dilemma, and it's a large one as my company utilizes a large
model where this is an issue. When named ranges are references, they
are - by default - absolute references. This is a problem because
named ranges are very good identifiers to decode a formula (and our
model uses very long, complicated formulas).

Say I have a table with 50 rows, one for each state. The other columns
are RATES, MODS and SURCHG, with range names all around. Elsewhere in
the file, I have a cell with this formula (for CA, e.g, and I can type
it in just like this): CA_RATE * CA_MOD+CA_SURCHG.

Problem is, say Delaware is right below California in my source table.
I would like to simply copy this formula and have it read: DE_RATE *
DE_MOD+DE_SURCHG. But, since my range name references are absolute, I
can't copy down and get what I want. I have to manually go in and
change all the "CA"s to "DE"s. Frustrating.

I could just type in the actual R1C1 reference for my named ranges, but
then my formula bar lacks the range names and transparency I'm looking
for. I've tried doing that and doing Range-Name-Apply, but that
doesn't seem to work (plus, would that really require me to highlight
all 50 range names first?).

I think the Lotus transition options have something to do with it. In
my file, ONE CELL show this. There is a formula that shows reference
to cell K9, but when I check "Transition formula entry," it changes K9
to the range name. When I edit, it reads as "K9" and I can change it
to a mixed/absolute as I'd like. However, I can't recreate this in
another file. What's the magic bullet I'm missing here.

FYI: Excel 2002 in a Win2000 environment.

--
sonicblue
------------------------------------------------------------------------
sonicblue's Profile: http://www.excelforum.com/member.php...o&userid=28990
View this thread: http://www.excelforum.com/showthread...hreadid=487199

hi all.
ive been an avid reader of the forums for some time now; time to
register so i can post my question, which is rather urgent. (aren't
they all ?)

the attachment is a simplified version of the pivot table i have going
on.

the sourcedata is set so the production amount is reported under
'manufacturing', although there are costs in other types. i want to
have a 3rd column that would, for each product, do cost of that type /
production amount subtotal at each row. so it would basically be cost
breakdown at each row, with the total cost at the subtotal level.

i cant link to that specific or subtotal production amount, so i always
get 0 cost/production where production amount is 0.

if i were to manipulate the data so that production amount was
artificially distributed across all types, then the subtotal cost would
be wrong.

i tried with calculated formula/item to no avail. i have explored the
feasibility of trying to reference to the production amount data within
the calculated field. can't yet get it to work. help appreciated.

do i use consolidation here ?

thanks in advance for the help

+-------------------------------------------------------------------+
|Filename: ptformula.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4044 |
+-------------------------------------------------------------------+

--
fax
------------------------------------------------------------------------
fax's Profile: http://www.excelforum.com/member.php...o&userid=28912
View this thread: http://www.excelforum.com/showthread...hreadid=486566

I just completed a workbook for our NFL pool at work. I got mildly hung-up on one task, and I am presuming there has got to be a better way to accomplish this. It goes as follows:

On worksheet #1 I have everybody's picks in row format -- 40 entries total -- for ease of entry:

- - NAME | N 1 | N 2 | N 3 | N 4 | A 1 | A 2 | A 3 | A 4 | TIE
_1. Dave | NYG | SEA | GB_ | DAL | SD_ | CLE | JAC | NE_ | IND
_2. Mark | SEA | WAS | GB_ | NYG | SD_ | NYJ | PIT | IND | NE_
_3. Mary | ARI | TB_ | DAL | GB_ | SD_ | IND | BUF | PIT | SF_
...
40. Zack | ...

On the second worksheet, I'm tracking team wins.

On a third sheet, I list everybody's picks in table format, and add in the wins, to print and post up as a weekly update:

1 |Dave| ________ |Wk1|Wk2|Wk3|...|Wk17
. |NFC | Giants__ | 1 | _ | _ |...
. | .. | Seahawks | - | _ | _ |...
. | .. | Packers_ | 1 | _ | _ |...
. | .. | Cowboys_ | 1 | _ | _ |...
. |————————————————————————————————
. |AFC | Chargers | - | _ | _ |...
. | .. | Browns__ | - | _ | _ |...
. | .. | Jaguars_ | - | _ | _ |...
. | .. | Patriots | 1 | _ | _ |...
. |————————————————————————————————
. | .......Total: | 4 | _ | _ |...
. |————————————————————————————————
. |TIE |
. |BRKR| Colts___ | - | _ | _ |...

2 |Mark| ________ |Wk1|Wk2|Wk3|...|Wk17
...

Simple enough, but on this 3rd worksheet, I want to be able to easily copy this simple 2-dimensional table, one after the other after the other, 40 times down the page. The problem is that each table takes 14 rows, so under normal copy-paste technique, "Name" on my second table will refer to the 14th player on my first worksheet, etc.

I managed a tricky little work-around with this, where I've assigned a #1 in front of the first table, and a formula in front of each subsequent table scans up the column and adds one, so they number themselves 1 thru 40 as I paste them down the worksheet. Then each person's name is looked up on the first worksheet, using the INDIRECT function + a formula involving the table #. From there, their 9 team picks are carried over via VLOOKUP from their data page, and team wins are similarly brought in from the team win page.

All fine and good, but using INDIRECT removes a lot of portability from the table, since it's an absolute reference. I can't indiscriminately lengthen the table or jostle around the layout of the entry sheet without screwing up the output. I'm also concerned that when I come back to this next season, it's going to be a huge hassle (attempting to recall what I did to make this work) if we add more entrants.

Anyhow, back to my main question: was there an easier way for me to have done this?

I'd be happy to email you a copy if you're interested in taking a look at it.

- Nate

Here's my dilemma, and it's a large one as my company utilizes a large model where this is an issue. When named ranges are references, they are - by default - absolute references. This is a problem because named ranges are very good identifiers to decode a formula (and our model uses very long, complicated formulas).

Say I have a table with 50 rows, one for each state. The other columns are RATES, MODS and SURCHG, with range names all around. Elsewhere in the file, I have a cell with this formula (for CA, e.g, and I can type it in just like this): CA_RATE * CA_MOD+CA_SURCHG.

Problem is, say Delaware is right below California in my source table. I would like to simply copy this formula and have it read: DE_RATE * DE_MOD+DE_SURCHG. But, since my range name references are absolute, I can't copy down and get what I want. I have to manually go in and change all the "CA"s to "DE"s. Frustrating.

I could just type in the actual R1C1 reference for my named ranges, but then my formula bar lacks the range names and transparency I'm looking for. I've tried doing that and doing Range-Name-Apply, but that doesn't seem to work (plus, would that really require me to highlight all 50 range names first?).

I think the Lotus transition options have something to do with it. In my file, ONE CELL show this. There is a formula that shows reference to cell K9, but when I check "Transition formula entry," it changes K9 to the range name. When I edit, it reads as "K9" and I can change it to a mixed/absolute as I'd like. However, I can't recreate this in another file. What's the magic bullet I'm missing here.

FYI: Excel 2002 in a Win2000 environment.

Hello,

I have cells in one worksheet which contain formulas referring to cells in a PivotTable in another worksheet. I've used the GETPIVOTDATA function to set the formulas, and they look like this:

=GETPIVOTDATA("Sum",OtherSheet!$A$3,"Class","RCO","Date'",DATE(2007,7,1))

However, the PivotTable may be moved to another location within the "OtherSheet" worksheet, and so I'd like for all my formulas to continue to work if the table is moved. Ideally I'd like to replace the "$A$3" reference in the formula above to one utilizing the name of the PivotTable. I've tried variations of formulas like:

=GETPIVOTDATA("Sum'",OtherSheet!PivotTables("NameOfPT").tablerange2,"Class'","RCO","Date'",DATE(2007 ,7,1))

I can't find anything to work without getting some kind of #NAME? error or similar.

Does anyone know how to refer to a PivotTable location by name with the GETPIVOTDATA function?

An alternative idea is to use a cell reference without the GETPIVOTDATA function. Since I know exactly which absolute column to reference on the other sheet, if I could use some function or property of the PivotTable that returns the absolute row number for a specific item (e.g. "RCO" in the above example) in the rowrange, then I could use that row and the column I already know as the reference in the cell formula. If anyone knows how to do that, I would appreciate that as well!

Many thanks!

Hello there,

Is there any way to set the column number of a VLOOKUP formula as a NON absolute reference?

In this example I want to copy this formula so it works the same way in any column.

=VLOOKUP($C2,Data!$C$2:$O$26,2,FALSE)

I have the column of the lookup value ($C2) and the Table Array (Data!$C$2:$O$26) as absolute references.

But I need the Column Index Number (2) NOT to be an absolute reference in order for my copying to work so it can take the data from exactly the same corresponding column in my Data sheet.

Hope the question is clear. Thanks.

The Table should have only one header row.
Do not leave any blank cells in the header row.

Do not select a column or a row in a List before sorting; instead, select only a single cell. Clicking the Sort icon automatically sorts the entire List/Table, and the data will be sorted according to the selected cells field.

Sorting Formulas:

When sorting data beware of formulas in the cells. Sorting data linked by formulas to other cells, or to cells in other sheets, can distort the calculations. Be meticulous when sorting a List/Table containing linked formulas and defined Names (which are defined with absolute references by default), or with formulas that have absolute references.

Insert an additional column with ascending numbers (that is, 1, 2, 3, and so on) before sorting the data (do not use a formula). If a List includes a column with consecutive dates, use this column as the first sorting column.

I'm attempting to create a Custom List from a Data Set - let me explain.

Original Data Table:
Data1.jpg

Solution I'm Seeking:
Data2.jpg

My parameters would be to return the (1) Column Label, (2) Column Header, & (3) Amount for any absolute values in the data set greater than 5,000,000.

Just to be clear - (6,000,000) would be included in the Custom List since the absolute value is 6,000,000 which is greater than 5,000,000.

After much research (Array Formula's are not my forte...), I put together the following array formula:

{=SMALL(IF(ABS($D$3:$N$28)>=5000000,ROW($D$3:$N$28)),ROW(1:1))}

This will give you the Row Reference Number for each instance of a value greater than 5,000,000

Data3.jpg

I pasted above the corresponding value from the Data Set for ease of understanding...notice the numbers in RED - those are Subtotal lines...which I would love to efficiently exclude from my Custom List...but I have no idea how! lol That's really a side problem I'm not worried about right now, but if someone wants to include a fix in the solution I would be grateful.

THE MAIN PROBLEM:

So now that the above formula returned the proper Row Reference Number - I thought to use Index Function in order to return the cells actual value within the array thus completing a pivotal piece of my Custom List:

{=INDEX($A$1:$N$28,SMALL(IF(ABS($D$3:$N$28)>=5000000,ROW($D$3:$N$28)),ROW(1:1)),COLUMN($D$3:$N$28))}

Data4.jpg

But as you can see in the screenshot - The Column Reference Number is not returning the correct value...it's simply repeating Column Reference "4" over and over again...instead of changing to 6, 8, 10, 12, 14, or 16 - depending upon where the greater than 5M value is located in the Data Set.

How should the Array Formula be modified in order to return the correct Column Reference number?

(Again, see 'Solution I'm Seeking:" screenshot above for reference to what I want the final solution to look like)

Is there a more efficient way of creating a custom list like this? I'm really trying to learn Array formula's...so if someone can modify my formulas to make it work using an Array - that would be perfect!

Thanks for help.

Data6.xlsx

I am building a macro that is being forced to use R1C1 notation due to the fact that my macro is based entirely on the current position of the ActiveCell.

What I need this part of my code to do is in essence =LOOKUP(RC[-1],$A$1:$AA$1)... however I know I cannot mix R1C1 and A1 notations. The LOOKUP is cross referencing a date versus another table to find what policy term the date falls under. Here is the code I have been able to figure out that will at least work for the first cell.

Code:
However, I need to autofill the formula down. When I do that, my reference range goes completely out of whack. Is it possible
to keep the reference range in-tact while autofilling?

Here is my entire code.

Code:
Sub PolicyFill()
Dim LR As Integer
LR = Cells(Rows.Count, ActiveCell.Offset(0, -1).column).End(xlUp).row
ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-1],R[" & (-1) * (ActiveCell.row - 1) & "]C[" & (-1) * (ActiveCell.column - 1) & "]:R["
& (-1) * (ActiveCell.row - 1) & "]C[" & (-1) * (ActiveCell.column - 1) + 26 & "])"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=YEAR(RC[-1]) & "" - "" & YEAR(RC[-1])+1"
ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(LR, ActiveCell.column))
ActiveCell.Offset(0, 1).Select
ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(LR, ActiveCell.column))
End Sub


I've got this OR/AND conditional formatting formula in all cells across 4 columns:

=OR(AND(F22>$C$6,ISNUMBER(SEARCH("downstream",$B$8))),AND(F22>$C$4,ISNUMBER(SEARCH("upstream",$B$8))))

It works great. When I apply my pivot filter in $B$8 and choose either "upstream" or "downstream" it correctly colors my pivot table cells 'Red' as specified by my conditional format criteria, if the cell value is also greater than > than the absolute reference cell threshold value in $C$6. Great, right?

But when I add a field in my pivot table where I need it to look at a different absolute reference cell value, which I need it to again color the cell G22 'Red', IF the cell value is less than < than the absolute reference cell threshold value in $D$6, suddenly the formula doesn't work properly. It shades some cells 'Red', that are lower in value than $D$6, and some that are higher. It also shades rows 'Red' outside the pivot table area...

Here's my formula that doesn't work (see below). I think it has something to do with the condition if the cell value is zero '0' or 'blank'. I think I need to make this formula more robust to accomodate those 2 conditions. But then again, why did the first formula condition work perfectly?

=OR(AND(G22

Hi All and Happy New Year,

I want to write a formula like:

=INDIRECT(COLUMN()&"1")

but the obvious drawback is that column returns a numeric and not an alpha
reference.

The reason I want this is have a formula that can be copied down and across
that refers to the top cell in each column to which is copied.

Hopefully my header line makes it clear that playing around with "$" signs
is not the answer.

I could put in a table with 1 to 26 and A to Z in it and run a Vlookup over
it but that just seems too OTT.

Probably missing the bleeding obvious again - that and 2.5 weeks off with
primo SI NZ sunshine (and beer) rotting my brain.

Cheers,

Matt

Greetings! I've used Excel for years, but this one caught me off guard. In
Excel 2000, I have the following data:

A B
1 3 1
2 1 10
3 2 100
4 5 1000
5 4 =B1+B2+B3

When I sort all five rows by column A, my formula gives me a #REF! error and
the formula is changed to "=#REF!+B1+B2". Doesn't Excel track the relative
changes of formulas during a sort? It seems to have tracked the 10 and the
100 in the table above but somehow lost track of where the 1 went.

This came to my attention when a formula returned an invalid value after a
sort -- even worse, I'd rather have an error than bad data!

Can anyone shed some light on this and is there a workaround? I tried
absolute references, but that didn't work.

Thanks in advance!
--
Kevin

I am using Excel Professional 2003. When I create formulas that reference a
pivot table cell by clicking that cell when entering the formula, Excel
automaticaly inserts "GETPIVOTDATA", the pivot row and heading titles, and an
absolute cell reference instead of the simple cell address. So, when I copy
that formula to other cells the formulas do not reference the subsequent rows
in the pivot table.

How do I change the settings in Excel to reflect the simple cell address
[ex. P5] instead of the pivot table name and address [ex.
=(GETPIVOTDATA("column title",$P$5,"row title") ]. I can change it manually,
but that is not efficient.

I haven't had this issue with other versions of Excel.