Free Microsoft Excel 2013 Quick Reference

- Absolute reference to table column in formula
- How to make an absolute reference to an excel 2007 table column
- Extend subtotals to new columns in a table.
- Transferring formulas with absolute reference to multiple tabs
- How to summarise data in the same place in multiple worksheets?
- Incrementing absolute references
- Absolute references in VBA
- Named ranges: don't want absolute references
- Pivot table : formula to absolute reference a subtotal
- Converting row to table, then repeating (copying) table...
- Named ranges: don't want absolute references
- Refer to PivotTable by name in GetPivotData ?
- Remove absolute reference in vlookup column number?
- Table Sorting Rules in Excel 2007
- Create Custom List from Excel Table with Array Formula
- Absolute cell references with R1C1 notation
- Conditional Formatting problem...
- Something other than Absolute, Relative or Mixed references
- Formula Integrity Not Preserved During Sort in Excel 2000
- Formulas referencing pivot table cells

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

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

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

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.

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

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

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

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

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

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.

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!

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.

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.

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

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

=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

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

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

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.

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