Teach Yourself Excel Lesson Absolute References - Absolute References

I've got 4 columns of data in one spreadsheet that are linked to a source spreadsheet. It turns out that the data that I actually need to link to is 13 columns to the right of what I thought it should have been in the source spreadsheet. I can copy and paste the cells in the main spreadsheet 13 columns to the right, and they will be linking to the correct values in the source spreadsheet, but I can't seem to figure out a way to move those formulas back 13 columns to the left without the formulas changing. Is there a way that I can make them all absolute references to the source sheet or is there another easier way to get the correct links working?

Thank you.

Hello. I have a small problem with excel.

I have two worksheets in one workbook. I want the second one to always reference cell B2 in the first, regardless of the changes I make to the first worksheet.

The problem I have is this: I absolute reference cell B2, but if I insert a new row between row 1 and 2, the reference changes to cell B3! I don't want the reference to change, I want it to look at cell B2. Always. Regardless of any changes I make to the worksheet.

To elaborate further: The first worksheet that is being referenced is always changing. The second worksheet is always fixed. Whenever I make changes to the first worksheet, the reference changes to compensate any change. I don't want it to do this. I want it to always point at B2!

Why does excel have to be so damn smart! I don't want it to try and compensate for changes! I want it to point at B2! Is there a 'dumb reference' function???

I have a sheet1 with say 30 rows and 20 columns.
Col 1 has constants
Row 1 has constants
B2 has a formula such as =countif(datasheet!$A:$A,B$1)
C2 has a repeas of this as =countif(datasheet!$A:$A,C$1)
Etc., across all columns, down all rows.

However, if 'datasheet' requires adding a new column to the left of $A:$A, then the formula in sheet1 needs to change to read
=countif(datasheet!$B:$B,B$1)
However, it stays a $A:$A
My real sheet is large with hundreds of such formula using a variety of constructs. Is there an easy way to tell Excel to increment the column references if a new column in inserted into the referenced datasheet?.
Thanks.

Hello Bos ? anyone can help me, please..
How to change from absolute references to relative references.

Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365

Thank's b4

I've just installed office 2003. I use piovt tables a lot. Before I could use
an existing pivot table in formulas outside the table itself with great ease.
But now, everytime I create a formula outside the pivot table all references
are absolute and is full of no use formulas (like Get.Pivotdata(...). Only
with a lot of work I can go around this new "feature" in excel 2003. Is there
a way to solve this?

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.

This question actually involves both the use of Absolute References and Named Ranges within a Excel 2007 Table.

There is a Formula in a spreadsheet, say, E18 (A1 mode) that has the following variables (it also works):
Code:

=SUMIFS(Data_PI[Income],Data_PI[Accounting Period],E4,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],E6,Data_PI[Project],$B$16)

Data_PI is the name of the Table.

Then by using the cell handle to drag and copy the Formula to F18, the code changes, including the Table Headings, to the following:
Code:

=SUMIFS(Data_PI[Transfers&Adj],Data_PI[Free Balance],F4,Data_PI[DeptID],PI_Name,Data_PI[Balance Sept 1st],F6,Data_PI[LAB_NAME],$B$16)

Each of the names represents column headings of the Table. But the changing of the Table Headings is not wanted.
I tried setting each of the Table Ranges to a Named Range, as Named Ranges were supposed to be somewhat static, referring to what was entered. For Instance:
Code:

PI_PID = Data_PI[Project]

However, exact same issue arose, when trying to copy from E18 to F18. This time, the value of the Named Ranged [PI_PID] was changed to [PI_LabName], which had a Named Range value of:
Code:

PI_LabName = Data_PI[LAB_NAME]

Most Ranges can be set to absolute by using the $ ($E$18). The question is: Is there way to use Excel 2007 Table Ranges and make them absolute so they can be copied to other cells and not change?

Thanks,

Maverick

=(C41-B41)/365+(M1-C41)/365
I want the entire row of D to follow this format with it taking from M1. I keep getting M2, M3, etc when I copy and paste. The other numbers adjusting are fine but cell M1 is the anchor. How can I make cell M1 constant without having to go into every cell and manually change it?

Hi
I am doing a weekly roster with absolute references eg. =+SUMIF($C$4:$C$76,"Leo",$D$4:$D$76) entered under Monday. I wish to copy this to the other days of the week (other columns) without having to type it all again but I can't find a way to get the cell references to change. Can this be done.
Thanks


Edit: Thread moved to Worksheet Functions forum since this formula is not specific to Excel 2007.

I have a Total cell that is adding up many non sequential cells above in a cashflow.
I want to make these cells absolute but I also want to copy my Total cell across many other columns.
I know how to make a cell absolute but then I can't copy it across.

Hope there's a timesaving solution other than manually typing it in.

Many thanks

D.