Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Making Cell References Super-absolute

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How do I make a cell reference so permanent that even inserting rows or
columns above, below, or within the ranges of the formula does not
affect the reference?

In other words, "=AVERAGE($E$7:$E$11)" is good and well, until I insert
a row of cells in row 7, and then Excel changes my formula to
"=AVERAGE($E$8:$E$12)". I don't want Excel to change my formula.

I'll also be happy to consider other workarounds-- but the people who
will be using this template are technology-illiterate.

Thanks.



View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

Similar Topics







How do I insert the $ in every formula in a whole column?


The background is:

Column B contains:

=Average(A1;A2) resulting in "40"

=Average(A3;A4) resulting in "53"

=Average(A5;A6) resulting in "50"


When I now employ the Sort-function, the cell references are changed:

=Average(#Reference!;A1)

=MITTELWERT(A1;A2)

=MITTELWERT(A2;A3)


And if I'd lock the cell reference as I copy the Average-function; the copying fails as I continously refer to the same cells.


Any solution? My own idea would be to insert $ afterwards, but I don't know if it's possible?




Hello, I am having trouble copying something with formula. I have a 2 sheets in the same work book. One of them is the weighted average. My formula works wells just when i try to drag it down it does not come out they way i need it too.

This is the formula in C1
= IF('Weighted Average'!M1 = "",A1+B1,IF( 'Weighted Average'!M1>0.001,'Weighted Average'!M1))

On the weighted average page where i'm pulling the value of M1. It has to skips two cell and the next value would be M4.

So cells C1 to C3 would have these formulas in them.

= IF('Weighted Average'!M1 = "",A1+B1,IF( 'Weighted Average'!M1>0.001,'Weighted Average'!M1))
= IF('Weighted Average'!M4 = "",A2+B2,IF( 'Weighted Average'!M4>0.001,'Weighted Average'!M4))
= IF('Weighted Average'!M7 = "",A3+B3,IF( 'Weighted Average'!M7>0.001,'Weighted Average'!M7))

However, here is the problem when i select cells C1 to C3 and drag it down to C400 the sequence is messed up.

Here is how C1 to C6 will look if i drag it down.

= IF('Weighted Average'!M1 = "",A1+B1,IF( 'Weighted Average'!M1>0.001,'Weighted Average'!M1))
= IF('Weighted Average'!M4 = "",A2+B2,IF( 'Weighted Average'!M4>0.001,'Weighted Average'!M4))
= IF('Weighted Average'!M7 = "",A3+B3,IF( 'Weighted Average'!M7>0.001,'Weighted Average'!M7))
= IF('Weighted Average'!M4 = "",A4+B4,IF( 'Weighted Average'!M4>0.001,'Weighted Average'!M4))
= IF('Weighted Average'!M7 = "",A5+B5,IF( 'Weighted Average'!M7>0.001,'Weighted Average'!M7))
= IF('Weighted Average'!M8 = "",A6+B6,IF( 'Weighted Average'!M8>0.001,'Weighted Average'!M8))

As you can see the A and B cells copy just fine in a sequence. But the M cells go out of wack. Is there any way to do this without manually changing each M?
I just want the value to add 3 to every M. As in, M1, M4, M7, M10, M13....

Here is an attachment of what I am trying to do
Exceltest.xlsx

Is it possible to use "find and replace" command to amend the formula?

Ex. we have the formulas like this:

=AVERAGE(O10:P10)
=AVERAGE(Q10:T10)
=AVERAGE(U10:X10)

and we wanted to change them into

=IFERROR(AVERAGE(O10:P10),"")
=IFERROR(AVERAGE(Q10:T10),"")
=IFERROR(AVERAGE(U10:X10),"")

There should be some trick to keep the cell references intact while changing the formula only.

I tried replacing "average" with "iferror(average", but Excel does not like that notation. On the other hand, it is possible to replace "average" to "ifferror", but it leaves a lot of work to be done manually.

Thanks.


hello i have the following macro that changes the changes one of the references in the cells...

Code:

Sub CreateFormulas()
Dim ws As Worksheet
Dim MainPath As String
Dim CityBk As String
Dim Rw As Long
Application.DisplayAlerts = False

MainPath = "='C:\Documents and Settings\Archieve 2011\"

For Each ws In Worksheets
    If ws.Range("C5") = "Period" Then
        CityBk = "\[" & ws.Name & ".xls]G.S Branch'!"
        For Rw = 7 To 1119 Step 21
            Range("C" & Rw + 1, "M" & Rw + 20).Formula = MainPath & Range("C" & Rw) & CityBk & "C" & Rw - 2
            Range("N" & Rw + 1, "Y" & Rw + 20).Formula = MainPath & Range("C" & Rw) & CityBk & "O" & Rw - 2
        Next Rw
    End If
Next ws

End Sub


i have attached a sample workbook, if you notice, each range in columns (E:Y) uses P1W1, P1W2...P12W5 to derive the end result....and at the end the cell reference is an abolute value.

when i run the above macro, although it does what i want by inserting the correct period/week i.e. P1W1 ..or P2W1 in its corresponding column, it doesnt however keep the i.e. $E$8 or $Y$28 or $H$45 etc absolute, it makes it relative and thus drags the formula down.......so can anyone help pls its been realy hampering the whole file...i have 33 more worksheets to add an this macro does it rvery quick, just changes the references. thx you, happy holidays


Hi - I have a problem that I thought would be solved by using absolute references, but it's not. I have a spreadsheet that looks like this:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 Brooks Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
As you would expect, C3 displays "different", all other cells in column C are blank. So far so good. However, what I want to do, now that I've identified the "different" row, is to move A3 down one row, so things look like this:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
5 Brooks etc...........
Please note that I'm doing this because there is other information in each row, not because my OCD is out of control. Also, in my real formula I'm handling cells that are blank. Anyway, my problem is that I don't get the above. Rather, Excel adjust the values in my formula so I get:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A4=B3),"different","")
4 Brooks Brooks =IF(NOT(A5=B4),"different","")
5 Brooks etc...........
This is NOT what I want. I want the formula in C3 to continue to reference A3, not A4. As I understand it, absolute references are only for when you want to copy the cell that they are used in, which I'm not doing. Anyway, I tried saying $A$3, but it didn't help.
How do I stop this behavior?
Thanks!

Hello,

I want to copy a formula with an absolute reference that needs to be
changed after a number of cells, like:

=A1/AVERAGE(A$1:A$10) for the first 10 rows
=A11/AVERAGE(A$11:A$20) for the next 10 rows
=A21/AVERAGE(A$21:A$30) for the next 10 rows and so on

Is there any way to obtain this result with a single copy operation,
without having to manually edit the absolute reference?

Thankyou,
/_urka




I have a list of grade from 2~4 (i have long list but for sample i made it short). I want to get average of all grades one by one as well as by making some groups. Like 2,3, "2~4" (all groups) then "3~4". All the things were going nicely with a drop down menu. But when i put average on grde "3~4" only then the problem occurred. Although I've done my work but still I think there must be some improvements.
I'm attaching sheet and giving the formula which l want to make shorten.
Quote:

=IF($B$16=2,AVERAGE(IF($C$4:$C$11=2,$E$4:$E$11)),IF($B$16=3,AVERAGE(IF($C$4:$C$11=2,$E$4:$E$11)),
IF($B$16=4,AVERAGE(IF($C$4:$C$11=2,$E$4:$E$11)),IF($B$16="2~4",AVERAGE($E$4:$E$11),
IF($B$16="3~4",(SUM(($C$4:$C$11=3)*$E$4:$E$11)+SUM(($C$4:$C$11=4)*$E$4:$E$11))/(COUNT(IF(C4:C11=3,C4:C11))+COUNT(IF(C4:C11=4,C4:C11)) ))))))


The last part of formula i think very long and there must be some cool method to done this.
Quote:

=IF($B$16="3~4",(SUM(($C$4:$C$11=3)*$E$4:$E$11)+SUM(($C$4:$C$11=4)*$E$4:$E$11))/(COUNT(IF(C4:C11=3,C4:C11))+COUNT(IF(C4:C11=4,C4:C11))))

Thx in advance for spending time.




I have created one row of 737 columns. Basically, i had to convert a well formatted data entry sheet into one row so that it can be uploaded to a work database.

After creating my one row of 737 columns, I now have discovered that I did not make my references absolute. Thus, when I copy row one, the origin of my reference changes to the next row when pasting on the next line.
my first row, where cell F9 is the proper reference: =IF(ISBLANK('ESA Timesheet'!F9), "", 'ESA Timesheet'!F9)



my second (pasted row), where the reference still needs to be F9, or more accurately $F$9: =IF(ISBLANK('ESA Timesheet'!F10), "", 'ESA Timesheet'!F10)





Is there a way i can avoid putting in four $s in each of my 737 cells? all the references are in place, i just need to stop the automatic adjustment to the next row, as a relative reference would allow.

when I create a sheet where I have taken an average of 4 cells (A1:D1), but in one of the 4 cells instead of a number there is a text "N/A" (on purpose), the average in the separate cell shows "#VALUE!". How can I make sure that instead of showing that it shows "N/A"?

When I use the "=AVERAGE (A1:D1)+if(I8=#value, N/A) then it tells me there is a circular reference. Same thing happens with SUBTITUTE formula. Any ideas?

Let me know if I should explain my question in more details. thanks


I have some complicated macros which help create quotes for our sales department. I am using a "configurator" workbook which contains a few template sheets and over 50 ranges which point to various variables/templates. In the process of creating the quotes I end up opening/creating a new workbook and copying the template ranges/sheets to the new workbook (through the macro). This new "Quotes" workbook will contain a coversheet with many variables containing named ranges which subsequent sheets will need to reference with "=NAMED_RANGE" or something like that.

Right now excel is annoying me because when it copies references of named ranges from one workbook to another, the cell tries to refer back to the "configuration" workbook (although it doesn't say so). I want to be able to use something like "=ThisWorkbook!NAMED_RANGE" which would solve all my problems but it looks like MS isn't going to make it that easy for me (I can't find any info on a special reference like ThisWorkbook for formulas).

The only workaround I could think of would be to create a macro to scan through all the cells with references to named ranges in the new "Quote" workbook and insert the new workbook name in front of them (i.e. =quote1.xls!NAMED_RANGE). This would take forever and then I would have problems if the user renamed the workbook.

Anyone have any suggestions? Please I'm dying here.


Hi all,

I'm going to give this another shot and try to state it a different way.

This is what I have so far:

{=AVERAGE(IF(ISNUMBER('[Jul06AMEFVER.xls]1'!$B16:$L16),ABS('[Jul06AMEFVER.xls]1'!$B16:$L16)))}

What this is stating is I want to average the absolute value of cells B16:L16 only if the cell contains a number. The good thing is that this works; however, you see in the bold part that it is only referring to TAB 1 in the Jul06AMEFVER.xls workbook.

My problem is that I want to take the average of all absolute values of cells B16:L16 on TAB 1 through TAB 31. I tried this:

{=AVERAGE(IF(ISNUMBER('[Jul06AMEFVER.xls]1:31'!$B16:$L16),ABS('[Jul06AMEFVER.xls]1:31'!$B16:$L16)))}

But the answer is "0". I checked when the data isn't filled in there is an "X" so that is why the ISNUMBER function is there. I would like the formula to do an ongoing average while the data is being filled in. If the referenced cell (aka B16:L16) as an "X" or blank space don't count it in the average.

I hope you all understand what I'm trying to do. Been working on this for two days now and running out of ideas. Thoughts. Thanks so much.

Jason


Hello, I've looked all over the net trying to find a solution, but here I am. Your help is appreciated.

I am in need a formula (for "'Sheet1'") to find the smallest number in a range of cells from ("'Sheet2'"). But, I also need that formula reference to the cell range on "'Sheet2'" to be absolute so that I can insert colums in "'Sheet2'"without the formula automatically updating which column it references.

The problem is that I need to be able to auto fill the formula on "'Sheet1'".

I've been researching, guessing, and testing for 4 hours now and here
Below is the closest thing to my desired formula that I've gotten to work.

=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J4:$N4)),1)

But, when I auto fill, it comes out like this:

=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J4:$N4)),1)
=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J5:$N5)),1)
=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J6:$N6)),1).....and so on

I need it to come out in a way that the column reference is absolute, but the row changes (preferably based on the row that the formula is in)

Please, help me.


I have this code
Code:

Private Sub workbook_open()
    Sheets(1).Range("BF3").Formula = "=AVERAGE(B3:BB3)"
    Sheets(1).Range("BF4").Formula = "=AVERAGE(B4:BB4)"
    Sheets(1).Range("BF5").Formula = "=AVERAGE(B5:BB5)"
    Sheets(1).Range("BF6").Formula = "=AVERAGE(B6:BB6)"
    Sheets(1).Range("BF7").Formula = "=AVERAGE(B7:BB7)"
    Sheets(1).Range("BF8").Formula = "=AVERAGE(B8:BB8)"
End Sub


to make sure that those formulas are in their cell everytime the workbook is open. I have used it in other workbooks and it works fine. When I Try to run this, it does nothing. No error, no results... nothing. If anybody can spot whats wrong it would be really appreciated.

thanks


I'm trying to get a formula working that is basically for a rating chart. The chart is somewhat simple, but the formula is a bit complex (for me anyway)

So the details are the rating is on three axis with three levels, and here is what I need to do.

At the moment the formula below works, but it isn't 100% accurate because I actually need to take an average of F5+G5 then take that value and add it to H5 then divide by two to get the proper average of the two charts. (if that makes sense.

=IF(AVERAGE(F5:H5)>2.33,"High",IF(AVERAGE(F5:H5)>1.67,"Medium",IF(AVERAGE(F5:H5)<1.67,"Low")))

Here is the formula that I thought would have worked, but Excel begs to differ

=IF(SUM((AVERAGE(F5:G5))+H5)/2>2.33,"High",IF(SUM((AVERAGE(F5:G5))+H5)/2>1.67,"Medium",IF(SUM((AVERAGE(F5:G5))+H5)/2<1.67,"Low")))

I played with the brackets quite a bit thinking I had something in the wrong order, but nothing seems to work. I always get FALSE in the field and an error as well when I enter the formula.

Any help would be much appreciated!

Matt




Good day,

I have a worksheet that has a large number of columns (based on the day of the week) and a large number of rows (based on the status of certain application checks).

In every other column I have the following formula: IF(ISNUMBER(SEARCH(TEXT(O1, "dddd"), 'Client Backup Set Up'!$C3)), "TRUE", "")

I want to fill the rows below it, in the same column, with the same formula but only changing the second cell reference (if that's the correct terminology).

Let me explain with an example.

If I drag the formula down the row, I get the following results.



Please Login or Register  to view this content.


However, what I want to see is this ...



Please Login or Register  to view this content.


As you can see, the first reference to a cell doesn't change. It stays as O1 in every row. The second reference changes by one in each new row (C3, C4, C5 etc.).

Is it possible to restrict certain cell references so that they don't change by adding a character to the formula or by any other means?

Hi,

A problem came from a user today. Set up: IN A1: " =SUM(A2:A6) " and in A8:
" =SUM(A9:A13) " and fill in some figures in A9:A13.

Cut A10 and paste it to A4. everything is ok.
If I cut A9 you get "Circular Reference error, because the formula in A8 has
changed to: " =SUM(A4:A13) " and the old SUM it still there in A8.

Why? Should it be that way, and what is the purpose? Any workarounds?

/Kind regards





Hi. I have a need to make two cells reference each other. ie the formula of A1 is "=B1" and the formula of B1 is "=A1". I want this so that my user can enter a number in either of these cells and it will then populate the other cell.

Excel errors when I open this files saying "Excel cannot calculate a formula. Cell references in the formula refer to the formula's result. etc etc"

I think I can resolve this by clicking the iterations box in the "Tools, Options, calculations" box but this sets the paramaters for the whole of excel not just this spread sheet. Any advise would be appreciated.


Hi

I have been making a mistake by not including $ in my formula for absolute reference. Now, i could like to amend this mistake. Is there a quick way of doing it?

Basically, i have 24 columns and 400+ rows for 4 worksheets each to amend. Hence, i am looking for a short cut rather than just to press "F4" button for each reference.

Thanks for the help in advance.




Here is the formula I am trying to write. looking for an average of a range of 3 cells that contain averages of other cells where the answer is DIV O, here is my formula.

=IF(J10:J12>0),SUM(J10:J12)/COUNTIF(J10:J12)>"0",0), I know there has to be a way to make this work.

J10 -J12 give and average of individual ranges using this formula "=SUM(D2:D32)/COUNTIF(D2:D32,">0")"

J10 has a number, J11 and J12 have "DIV 0" because no data has occurred to complete J11 and J12

trying to return 0 instead of DIV "0"

Hi, just a quick question really, I have a feeling it can probably be resolved quite quickly but I just don't know how.

Basically, I have a formula containing absolute cell references, but whenever I insert a new row into the table that the reference is looking at, the cells bound by the absolute reference change.

I thought the point of using absolute cell references is that they don't change?

Anyway, is there some way around this, to avoid the cell reference changing when a row is inserted?

Thanks in advance for any help.


Hi. I'm getting a Run-time 1004 error when trying to insert 3 blank lines (it pops up at the first "Insert" line). Excel is complaining about shifting non-blank cells off the page, but doing "CTRL-END" highlights cell D198, which is the extent of my data, so I don't understand the problem. The step before this was copying a pivot table, pasting as values, then pasting as formats. Any help on fixing this would be greatly appreciated.

Thanks, John

Code:

Sub Trouble()
    'Plug in the formula to figure the average monthly change
    Sheets("Paste").Activate
    With Sheets("Paste")
        Range("A1").EntireRow.Insert Shift:=xlDown
        Range("A1").EntireRow.Insert Shift:=xlDown
        Range("A1").EntireRow.Insert Shift:=xlDown
        Columns("A:A").Insert Shift:=xlToRight
        Range("A1").Value = "Date Columns Divisor"
        Range("A5").Value = "Average Monthly Change"
        Range("B1").Name = "Months"
        Range("B1").FormulaR1C1 = "=Counta(5:5)-5"
    Range("IV3").End(xlToLeft).Offset(0, 1).FormulaR1C1 = "=(-D6 + Offset(D6, 0, $B$1)) / Months"
    End With
End Sub







Hi everyone!

I need a formula that is able to calculate the average of every 5th row (sort of in 5 row blocks), so long as they are aligned with another row. It seems strange, but I need it to fit the specification of some MATLAB output. In the event that there's a blank, it should just ignore it and not change the counting in the blocks. However, if a new ID is detected in A, the counting needs to start over.

For example, I have Columns A and B, but need the average in C

A B C
1 ID #
2 1_01a x
3 x
4 x
5 x
6 x [average]
7 x
8 x
9 x
10
11 x [average]
12 x
13 x
14 x
15 x [average]
16 1_02_b
17 x
18 x
19 x
20 x
21 x [average]

I've tried some different things with average ifs, but I can't get excel to count the rows. Any help from anyone would be super, super, appreciated. Thanks!



Good day all,

I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".

I tried the below macro to unlock the sheet, insert row and lock the sheet again:



Please Login or Register  to view this content.


So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.

Best regards.

Hello, I am trying to use a cell reference that refers to the name of a named range in a formula. For example, in the formula "=average(a2,>1)", the cell reference "A2" actually contains the text of the named range "DATA1.1.1_". Is this even possible? If not, is there a way I can make this work? I have a about 800 very long formula's that I need to write and the only thing that is changing in each formula is one named range. I would like be able to set a list of named ranges, then just autofill the formula down the column.


Hi

I have a formula with circular reference. When a I put any valur in any of
these reference cells the formaula gets overwritten by the value. Is there
any way that I can put values in any of these cells without overwriting the
formuls to try various permutations. Below is the example:-

Cell A B C
500 10 2%
Formula +C1*A1 B1/A1

Cell B1 & C1 are having formula with cicular reference. What I am trying to
get is that if I change the B1 value to 50 from 10, then C1 should
automatically change to 10% & vice versa. OR If C1 is changed to 10% B1
should change to 50 so that I can try as many combinationas possible.

The problem here is that once i put "50" in B1 or "10%" in C1 the formula
gets overwritten with the absolute value.

Would be thank ful if anybody can suggest any solution for this?

Regards
Abhi