Making Cell References Super-absolute

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

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.

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 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

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'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

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.

I hope you can help with my problem.
I have a range of cells I want to average, the contents of those sells are :

=IF(B7<=1,"",IF(B7=C7,"1",IF(B7<>C7,"0")))
=IF(B8<=1,"",IF(B8=C8,"1",IF(B8<>C8,"0")))
=IF(B9<=1,"",IF(B9=C9,"1",IF(B9<>C9,"0")))

ETC.........
Those formulas give me a result of either, 1, 0, or an empty cell.

The formula for the Average calc. I am using is :

{=AVERAGE(P7:P169)}

The result of this is :

#DIV/!

Any sugestions ?????????
Tannks !!!!

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

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.

Hello!

I need to be able to average the times in a certain cell in the "first call" and "last call" columns across various sheets, but the cell that needs to be averaged will not be in the same row on some (or all) sheets because I order the rows from greatest to least at the end of the week based on column AA, it will however, be in the same column. The cell the formula is in also need to be blank if nothing is present to average.

I have been able to do this with other types of values such as normal numbers with format 0;-0;"" (example: 154) and another form of time with format [h]:mm:ss;;; (example: 1:17:46). However, the formula I am using is not suited to average a 1:30 PM format because the formula uses the totals from another sheet and then counts the number of notblank cells that match the cell in that row.

Here are the two forumli if your curious(they work great, with the only thing that could be improved being the average formula because it has to have the same column A order as the totals sheet):
Code:

sum
=SUM(SUMIF(INDIRECT("'Week "&{1,2,3,4,5}&"'!A:A"),$A7,INDIRECT("'Week "&{1,2,3,4,5}&"'!"&ADDRESS(1,COLUMN())&":"&ADDRESS(20000,COLUMN()))))

average
=IFERROR(TOTALS!K7/SUMPRODUCT(COUNTIFS(INDIRECT("'Week "&{1,2,3,4,5}&"'!A1:A1000"),$A7,INDIRECT("'Week "&{1,2,3,4,5}&"'!"&ADDRESS(1,COLUMN())&":"&ADDRESS(1000,COLUMN())),">0")),"")

I will attach a sample of the book I am working on so you know what I am talking about. If you need more detail just ask.

Thanks so much for trying to help me, and good luck with this excel puzzle!

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

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

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?

I want to preserve the "empty" status of cells from one sheet to another so
that when they are plotted or averaged they behave correctly. For example,
the initial sheet has some values that are not declared (read as ",,," in a
CSV file). A second sheet is used to assemble parts of the first sheet for
analysis and uses "paste link". However, "paste link" makes empty cells from
the first sheet into zeros on the second. I can use an IF() to test for zero
(but not empty?) and then make them either "" or na(). However, making them
"" is fine for average() but not good for a chart, which assumes they are
zero. Making them NA() is good for charts but no good for average(). Is there
a function that simply replicates the keystroke of 'delete'?

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.

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!