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



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 Tutorials

Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...
Apply Conditional Formatting to Multiple Cells with a Single Formula
How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...
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 colu ...
Top Excel Keyboard Shortcuts to Increase Productivity
I'll show you the top keyboard shortcuts for Excel that are sure to increase your productivity. These shortcuts are ...

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









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.


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!



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.

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.




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

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.


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







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.

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



I hope to make this a clear as possible. I have a basic excel table with dates down column A, and invoice total dollar amouts down columns B and C. I am trying to get a running average "Total daily spent" from these spreadsheets without creating a column D just to sum Columns B and C together. I am looking for a formula that will add each row in columns B with C then average it by the number of rows used. To try and clarify this, I need B2+C2 and B3+C3 then the average of the sum. But the rows are for 30 or 31 days and i want a "running" average which means i want it to compute "the boxes with values" so everyday as I add information, the formula auto recalculates it. Can anyone help? Thanks


I'm looking to have a simple formula reference another cell to find the range to use in the formula.
For example, say I have a column of numbers in column A.
Then I have a formula such as Average(A1:A20) in cell D1
And then maybe Average(A5:A10) in cell D2
And then maybe Average(A10:A15) in cell D3
And then maybe Average(A15:A20) in cell D4
So what I am doing is just changing the range of cells (rows) that I am using in the Average formula.
But instead of typing in different ranges in each formula, I would like to have a formula in D1 refer to another cell to determine the range to use.
For example, I want the starting and ending cell A1 & A20 to change according to what I have in say cell B1 and C1. So the Average formula would refer to say B1 to get the starting cell of the range and say C1 to get the ending cell of the range. Then I would list in B1 and C1 the starting and ending range cells or the row number desired.
Then I would repeat the same formula in D2, and that formula would refer to cells B2 and C2 to get its range to use. This would then continue for the other formulas.
So I am basically looking to tell the Average formula what starting and ending row to use. So far I haven't been able to figure out how to make a formula do that, and know what row/cell to use.
I realize I could name each range, (but then each formula in column D would still be different) but is there a way to have one formula so that I can just change the value in the B and C columns to tell the formula what rows to use in the Average formula? Or if I have to name the range is there a way to have the named range refer to another cell to find out its starting and ending row. Thanks


Hello All! Hope someone can help me with this. I'm trying to write a formula that uses todays date to determine a column reference and a VLOOKUP to determine a row reference (both in the same sheet) to have a certain cell displayed in another sheet.

I'll try to talk it through.

If todays date falls into the range of 'Schedule'!V5:W5 (column v is a start date and column w is an end date) then make the column reference 'Schedule'!B. If todays date falls between 'Schedule'!V6:W6 then make the column reference 'Schedule'!C. There are 17 date ranges so the possible column references are B through R.

I believe I have the VLOOKUP portion of the formula correct. This portion will return the row reference for the desired cell in 'Schedule'!. This part of the formula looks at a cell in column 'Data'!S and, based upon the text that is in that cell, returns the appropriate row reference.

For example, if todays date falls into the date range 'Schedule'!V7:W7 and the contents of cell 'Data'!$S8 is "CCC" then the cell the formula resides in will reference 'Schedule'!$D$7.

=INDIRECT("'Schedule'!TODAY(>={$V$5:$W$5,B;$V$6:$W$6,C;$V$7:$W$7,D;$V$8:$W$8,E;$V$9:$W$9,F;$V$10:$W$ 10,G;$V$11:$W$11,H;$V$12:$W$12,I;$V$13:$W$13,J;$V$14:$W$14,K;$V$15:$W$15,L;$V$16:$W$16,L;$V$17:$W$17 ,M;$V$18:$W$18,N;$V$19:$W$19,O;$V$20:$W$20,P;$V$21:$W$21,Q;$V$22:$W$22,R},2,FALSE))"&VLOOKUP($S8,{"A AA",5;"BBB",6;"CCC",7;"DDD",8;"EEE",9;"FFF",10;"GGG",11;"HHH",12;"III",13;"JJJ",14;"KKK",15;"LLL",16 ;"MMM",17;"NNN",18;"OOO",19;"PPP",20;"QQQ",21;"RRR",22;"SSS",23;"TTT",24;"UUU",25;"VVV",26;"WWW",27; "XXX",28;"YYY",29;"ZZZ",30;"111",31;"222",32;"333",33;"444",34;"555",35;"666",36},2,FALSE))

I tried to enter this but the computer said there were too many characters and suggested I use CONCATENATE. What am I missing? Is there a simpler way to to this?


This may be a little hard to explain, but hopefully someone can follow my ramblings..

I am trying to get a formula to work - average a sequence of numbers only if the number of numbers is greater than or equal to 9. I have this working (across sheets if that is of consequence), but am having troubles with the else part of my if statement.

What I want, is that if the number of numbers is less than 9, then it outputs a zero (in Q2). Then, based on this result I have another cell outputting either a Yes (if the average is 1), a maybe (if the average is >= 0.85) else a no (in S2). The trouble is that if there is less than 9 numbers, the output of zero is not recognised as a number less than 0.85 and hence the output becomes "Yes".

The cell Q2 has been told to be of numerical type (with 2 decimal places).

Is there a way to force a cell to become an integer?

At the moment my formulae a
Q2 - =IF(Sheet2!C3>=Sheet1!$U$7, AVERAGE(Sheet1!D33), "0")
S2 - =IF(Q3>=$U$3,"Yes",IF(Q3>=$U$4,"Maybe","No"))
where the absolute references refer to the minimum constraints for a Yes / Maybe output and (in Q2) sheet2!C3 is the count(A) to work out whether the number of numbers is >= 9.

Make sense? I hope so!




I have a question and I dont know if this and be done but I was wondering is there is a away to make a formula increase itself per colunm.

=L12&" - "&I12&" "&H12&" "&J12&" - "&K12 <---- this is the formula that I am using

Say this is in A12 and I want to copy this to A13.

Is there away to make the the number 12 (=L12&" - "&I12&" "&H12&" "&J12&" - "&K12) change to a number 13 and increment by 1 for each and ever line I enter this in. 14,15,16 and so on.

So =L12&" - "&I12&" "&H12&" "&J12&" - "&K12 would change to this =L13&" - "&I13&" "&H13&" "&J13&" - "&K13 and so on.

What I am doing is pulling words from 4 different columns and making one column. and I am trying to figure out the best way to do this so I am not having to type this formula for thousands of columns.

Any ideas?

I want the AVERAGE function to be able to include cells which also contain the "<" character.
At the same time, I want cells containing the "<" character to use 50% of the reference value for purposes of the average calculation. I have attached an example of the output I'm seeking.

I would like for the solution to be a formula or array I can put into the calcuation cell without the need for dummy cells or off-page conversions or calculations. I can do that, using a single formula is what I'm struggling with.


When selecting a cell to enter into a formula, there is a key or key
combination that will automatically make the cell an absolute cell reference
when you point-and-click. You can also highlight the cell reference in the
formula and use the shortcut to make it an absolute cell reference. This
saves typing the "$" sign. Help! I can't remember it.



Hi,
I would like to know if it is possible to copy a formula that contains a worksheet name and have that act like a relative cell reference. In other words, in a normal worksheet if you have a formula that is "=A1*A2" in cell A3 and you copy that cell to B3, it automatically changes the formula to
"=B1*B2"
So my question is: Is there a way to have Excel automatically change worksheet names as a reference? Here's my example:
I have 4 worksheets, W, X, Y & Z. In worksheet X, I have a formula like:
"=W!A1*5"
Is there a way to copy the formula to worksheet Z and have Excel automatically change the formula to: "=Y!A1*5" ?????
This will seriously save me a lot of work!
Thanks in advance for any tips!
-Joe


Hi All-
I am trying to use an array formula to average certain numbers in one column based on criteria from another column. However, I am generating incorrect results. I cannot see why it's not working. From what I can tell, the formula is adding up the correct cells but the dividing by the total number of cells to get the average (does that make sense).

This is the formula I wrote: {=AVERAGE(IF($F$4:$F$67="MM",$N$4:$N$67,0))}, and it's in the attached spreadsheet in cell N70. I want Excel to look at the numbers in column N and then give me an average of the numbers where the corresponding cell in column F has "MM" in it. Is that possible?

I was able to create a rather convoluted method using a COUNTIF formula and a conditional sum to generate the average, but I want to try to figure out why the array formula is not working. Thanks for whatever help you can provide, and please let me know what additional questions you might have.


I have the following formula =INT(IF(D40="",NOW(),D40)-B40)&" dys "&TEXT(IF(D40="",NOW(),D40)-B40,"h"" hr ""m"" mn"""). It returns the time between two dates. But now i have a spread sheet full of times for each month and i need to find the average time spent during each month.

02-Jan-08 02-Jan-08 0 dys 2 hr 16 mn
03-Jan-08 10-Jan-08 6 dys 21 hr 29 mn
07-Jan-08 07-Jan-08 0 dys 14 hr 29 mn
07-Jan-08 07-Jan-08 0 dys 6 hr 33 mn
07-Jan-08 07-Jan-08 0 dys 5 hr 37 mn
08-Jan-08 09-Jan-08 0 dys 18 hr 13 mn

= Average ?????

How would i calculate the average since my orginal formula deals with INT?

Any help would be greatly appreciated

KcNewb


Hi All,
I've read several threads about switching between relative, absolute, and
mixed references across several cells however these solutions seem to result
in formula with all relative or all absolute or all mixed.
I need to change the formula in lots of cells with a mix of types of
reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer
not to have to go though each of the cells with F4!
I would be very grateful for your help.





Hi,
In my macro, I am trying to insert an "IF" function into a cell that will average 2 rows if the difference between two cells is "> -2" and average a different two rows if the difference is not ">-2". The cells for which the difference is calculated as well as the ranges that need to be averaged are specified by variables within the macro. The pertinent portion of my macro is:



Please Login or Register  to view this content.


where,

LastRowWithData is an Integer
TimeClosestToCoolDownLocation is a String (address of cell)
CoolDownStartTimeSecondsLocation is a String (address of cell)
RowClosestToCoolDownTimeInSec is a String (this is a row number determined using an array function earlier in the macro)

Right now, as the macro is written, the following gets inserted into the cell:

=IF(R120C2 - R84C2>-2,AVERAGE("R84C2:R83C2"),AVERAGE("R83C2:R82C2") , which is close to what I need (i.e., the cell addresses and ranges are correct), except for the extra quotes around the two ranges within the AVERAGE function. Because of the extra quotes, I get a "#VALUE!" error in the cell the function is inserted into.

I realize that this is because I have double quotes around the ranges in the actual macro vba code, but if I use only single quotes around the ranges, I get a "Compile error: Expected: end of statement" at the first R (for Row) in the first AVERAGE function. If I remove the quotes all together from around the ranges in the AVERAGE functions, the macro runs, but I get a "Run-Time error '1004': Application-defined or object-defined error".

If anybody can help show me what I am doing wrong, it would be greatly appreciated.

Thank you for the help.

Hello,

I am using auto filter and the "subtotal" function to average a column. I get an error when trying to average all the rows, there are 12,000+, but when filtering on smaller ranges I get a value. Is there a limit on how many rows excel can average? Suggestion on how to average the larger ranges?

formula =subtotal(1,bu3:bu12533)... Excel 03

Thank you,

Dwayne


hi!
so i would like to take the average of colums E-AY in sheet2 and put the value of each ind column average into E2 to AY2 in sheet 2.
then do the same with standard deviation.

i have a formula
Code:

Sheets("Final").Select
 ravg = Application.WorksheetFunction.Average(Columns("E").Rows("2" & ":" & TotalRow))

Sheets("Output").Select
Range("E2").Value = ravg


but i would have to copy paste that like 50 times...is there anyway i could make this more efficient perhaps by looping?
thank you very much!



EDIT POSTED THE POST IN THE VBA GROUP