# Absolute \$ Or INDIRCET Not Working Inside Formula

Aug 15, 2014

For some reason Absolute \$ or INDIRECT is not working inside my formula when I add a row to my chart. Formula keeps adjusting.

Here is my formula...=IFERROR(LARGE(IF(\$B\$5:\$B\$50=\$K108,\$A\$5:\$A\$50),1),"")

\$B\$5:\$B\$50 goes to \$B\$6:\$B\$51 AND \$A\$5:\$A\$50 goes to \$A\$6:\$A\$51..... it can't do that, throws my counts out, needs to stay in this range B5:B50 and A5:A50 when I add a row to chart

Also when I use INDIRECT

=IFERROR(INDIRECT(LARGE(IF("B5:B50"=\$K108),INDIRECT("A5:A50"),1),""))))

I keep getting a error with =\$K108, which is a cell reference in my formula which the formula needs to look at

So what do I do, or what formula do I need to use to stop this from adjusting as I add rows to a chart to stop formula from adjusting?

## Absolute Column And Relative Row Not Working In Formula?

Mar 15, 2014

I have a below formula which extracts the v and number attached to it I would like to copy this across all columns and change row for each column. So for example 1st column reference will be B4, the second column would have a reference B5 and so on, it's probably worth to mention that cells I am referencing to are in other sheet hence why there's Ref! before B4 is this why absolute column and relative row not working?

## Absolute Value Inside Countifs?

Jul 9, 2014

So I have a formula which contains a countifs that looks like this:

However, I want the formula to count values when ABS(\$AM:\$AM)=ABS(\$AM15) but I get an error if I try to put ABS(\$AM:\$AM) as my range. Is there anyway to set my range as the absolute value of Column?

I have also tried ABS(\$AM15:\$AM1000).

## Vlookup Inside IF Function Not Working?

Jan 7, 2014

I have the following formula that has ceased to work in excel. I don't think there are any errors in it so I'm unsure as to why the link no longer works. Neither of the files I'm using has moved location on the server.

=IF(H7>0,(I7*H7)+((I7*H7/100)*VLOOKUP(B7,'T:FILEFILEFILE'!\$B:\$E,4,FALSE))+0.25,0)

I'm not sure if this makes much of a difference but people opening the file use various versions of Microsoft Office.

## Conditional Code Not Working Inside A List Box

Feb 24, 2009

The following code worked for me until,I put inside a list box.

## INDIRECT Function Not Working Inside MATCH With Dynamic Ranges

Mar 25, 2014

I am getting a #REF error when using an INDIRECT function within a MATCH function to check against a dynamic named range. Basically, I am trying to get the row reference so that I can go back and extract other data from the row (in a table contained in another sheet) into the current worksheet.

I attach an example file for reference. The issue arises when a Dynamic Named Range is used. In the example file, if a value from a static range is chosen, the match with indirect function works, but it fails with the dynamic range.

Dynamic Ranges INDIRECT v2.xlsb

## Working On Document While It Is Still Inside Flashdisk Or External Hard Disk

Apr 15, 2014

Is it risky for someone to edit an excel document while it is still in the flashdisk or external hard disk? I was told that this habit can lead to loss of data or the data can be affected if someone does this. I was told that I have to copy/cut paste the document onto my desktop, edit/ work on it then move it back to the storage device. Is all that work necessary, or can I simply work on the document while it is still in the flashdisk or external hard disk..

## Add Formula With Absolute Row To A Cell

Dec 12, 2011

I want to add a formula with absolute row to a cell.

The desired formula in the cell is something like "=SUM(B\$5:B11)"

However, when i use the following code I keep getting error 1004 'Application-defined or object-defined error'

Code:
topCell.Cells(CurrentRow, 3)FormulaR1C1 = _
"=SUM(R-" & CurrentRow - 1 & "C[-1]:RC[-1])"

It works when the bracket [] is used for row, but it does not give me absolute row in the formula.

Code:
topCell.Cells(CurrentRow, 3)FormulaR1C1 = _
"=SUM(R[-" & CurrentRow - 1 & "]C[-1]:RC[-1])"

## Formula Change To Gather The Absolute ABS Sum Instead Of The Net

Jan 17, 2010

I am using the following forumla, it gathers the net sum of a column of \$ amounts.

I need to have it changed to gather the Absolute ABS total instead.

=SUMIFS(\$B\$23:\$B\$500,\$A\$23:\$A\$500,">="&\$V23,\$A\$23:\$A\$500,"<"&EDATE(\$V23,1))

V23 has a date "Month" value.

## Using A Relative Or Absolute? Cell In Formula

Feb 20, 2009

I'm working on my first real macro - so I'm a greenhorn. I've spent a few hours trying to research a solution, but most threads are too technical for me so I'm hoping someone can really help me out.

I am trying to use a formula that references a cell that changes day to day from when I use the macro. I need to use a formula which grabs from a cell in a non concrete location. Let me try and show you what I mean.

From I4:I10, I need a formula like this:

## Copy VLookup With Absolute Formula?

Feb 2, 2012

Formula is as follows:

=IF(ISERROR(VLOOKUP(\$A\$4,Day2,2,FALSE))," ",(VLOOKUP(\$A\$4,Day2,2,FALSE)))

However I need the formula to stay the same but the DAYS to change to DAY 3, Day 4, etc as I drag it along..

## Absolute Reference Using Variables In Formula

Dec 1, 2006

I have written some VBA code that is functional, but yields a formula that is somewhat confusing to the user.

I have two string variables called TippityTop and Anchor, both of which refer to specific cell addresses in the R1C1 style.

I attempted to define other variables that reference cells that are offset from TippityTop and Anchor without using the ActiveCell route. Try as I might, I could not produce the cell addresses that I wanted these new variables to reference.

I consulted many Excel Help screens and referred to a book on Excel VBA, but I finally took another route.

I now have:

ActiveCell.FormulaR1C1 = _
"=IF(AND(ISNA(MATCH(RC[-2],OFFSET(" & TippityTop & ",1,-2):OFFSET(" & Anchor & ",-2,-2),0)>0),ROUND(RC[-1],2)0),""DOV not in Treasury"","""")"

The problem is that this code yields this formula in Excel:

=IF(AND(ISNA(MATCH(L587,OFFSET(\$H\$585,1,-2):OFFSET(\$H\$798,-2,-2),0)>0),ROUND(M587,2)0),"DOV not in Treasury","")

Rather than OFFSET(\$H\$585,1,-2):OFFSET(\$H\$798,-2,-2), I would like the formula to say F586:F796, so that the end user can easily understand the logic.

## Anchor Cell In Formula. Absolute

Dec 8, 2006

I am doing percentages (very simple formula)

The formula looks like this =B7/B5
Now when I drag it to the box below I get =B8/B6
But I need the numerator to increment and
the denominator to stay the same.
So that it would look like this =B8/B5

## Absolute Column And Absolute Row Problem

Jan 14, 2009

I have a simple count formula which is suppose to be absolute.
=COUNT(\$L5:\$V5) and works till I add a column via a macro.
Macro adds a new column at column M then inserts data.
My problem is when I check the formula after macro it changes to
=COUNT(\$M5:\$W5) I thought using the \$ sign means Don't change?

## Inside A Value In A Formula

Nov 12, 2008

=BDP("067901108 cn corp cusip","px_last")

is there a way to insert 067901108 cn corp cusip from a different cell into the formula?

067901108 cn corp cusip <---A1
067901108 us corp cusip <---A2

something like this?

=BDP("A1","px_last") which equal to =BDP("067901108 cn corp cusip","px_last")

=BDP("A2","px_last") which equal to =BDP("067901108 us corp cusip","px_last")

## Update A Formula To Add An Absolute Value Of Amounts In Cells

Jan 24, 2009

I am using this formula to calculate a column of numbers that are both negative and positive numbers. =SUMPRODUCT(--(\$A\$5:\$A\$9647<=TODAY()),--(\$A\$5:\$A\$9647>TODAY()-365),\$C\$5:\$C\$9647). I need to keep this formula the same where it will calcuate on a rolling 365 day but I need the total to be only the absolute value (abs).

## Copy Absolute Formula, Without Changing Every Cell

Sep 23, 2009

I think this should be easier than I am making it out to be, but the answer is escaping me....

Among other things, I have a workbook with these worksheets in it: Hours, Cost, Profit, Revenue.

Columns A, B, C & D should be exactly the same on each worksheet. So, I have all the data for these columns entered into Hours, and then reference that worksheet on the other ones.

That works fine until I sort it differently and then instead of having row 2 reference row 2, it will be in row 9, etc.

Now I know I can use =+Hours!\$A\$2 for the absolute reference, but then i would manually have to change the reference on each cell.

SO - (finally the question) Is there a way to use the absolute reference without having to manually enter it into each cell?

## Embed Formula In VBA (calculate The Absolute Week )

Dec 28, 2009

I need to calculate the absolute week and have found a formula to do so. Is there a way to embed/execute this in VBA?

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date which you are trying to find the corresponding week number (for example Now().

## Add Relative/Absolute Formula To Range Macro

Sep 22, 2007

using a macro im trying to paste in a sum formula, however this will be pasted onto different sheets and the number of records will be different for each so i cannot use absolute locations. the only constant is that all ranges will start in row I9. when it runs the following code

Range("G" & (ActiveCell.Row)).Select
ActiveCell = "Total"
Range("I" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(I8:R[-1]C)"
Range("J" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(J8:R[-1]C)"
Range("I:J" & (ActiveCell.Row)).Select
Selection.Copy
Range("I4:J4").PasteSpecial

it fills in =SUM('I8':I13) how can i make it insert it without the ' ' around the I9? and how can i select the two cells? (again, cannot be absolute..)

Jun 21, 2014

I have an excel work book that I want to extract certain info from Each tab where in the result tab I use this formula to get required data from another tab.

I has about 24 tab , in the 25th one i collect data from all previous , using below formula inside each cell to get

=INDEX('2014 wk12'!\$C\$1:\$C\$17,MATCH("Total LTD Result",'2014 wk12'!\$A\$1:\$A\$17,0))

Where 2014 wk12 is a tab name , so , it works fine

excel iss.png

What i want is t replace the sheet name which in previous example is 2014 wk12 with relative name in column A

The closest i have in mind is to make it as below ( it is not working ) so what shall be the working formula of the below

=INDEX('Tab name from Column A '!\$C\$1:\$C\$17,MATCH("Total LTD Result",'Tab name from Column A'!\$A\$1:\$A\$17,0))

## Run Formula Inside Function

Mar 8, 2007

=StacServer|Windham! '40720'

This is the format of an external call that I am trying to run. I need to change the number (40720) based on the day of the month (i.e. if its the 5th, then use 40725.

I am attemting to use a function to do this and passing in the device name (Windham) and location (40720). Unfortunately, I have been unable to get a String to send back as a formula. My attempt was as follows :

= Location ("Windham", 40720, "D7")

Function Location(name, num, cell)
Dim psDay As Integer
Dim psLocal As String
psDay = Day(Now)
Location = (Range(cell).Formula = "=StacServer|" & name & "!'" & (num + psDay) & "'")
End Function

## Formula To Calculate The Absolute Total Of A Column Of Numbers

Jan 25, 2009

Need a formula to calculate a absolute total from a column of totals.

I thought this was simple, but the formula I made didn't work.

## Assigning Formula To Variable Inside VBA?

Feb 22, 2013

I tried to count rows from one sheet and to put this value as counter in VBA For-Next loop.

VB:
Sub Copy_ID()
'
' Copy_ID Macro

[Code]...

It ends with "Invalid qualifier" error on the counter.Formula line. I attached sample excel with macro that I listed here.

## Conditional Formatting Inside A Formula?

Jun 21, 2014

I have a challenge with conditional formatting. I have products A-K with sales current year and last year and want to have year on year growth. I want to have one column where the cell formatting changes based on data validation.

Let's say product A sold 60 items CY and 50 LY meaning YoY variance is +10 items or +20%. Hence, if the relevant cell says "%" then the YoY figure should say "+20%" (formatted as a percentage) and if "VALUES" then YoY should be 10 (formatted as numbers/absolute values).

Remember also that YoY growth can be +100% (or more), 0% and -100%.

## Nesting-Don't Use Blanks Inside Formula

Dec 6, 2005

I am currently using the STDEV formula. Is there a way, that i can make
the formula use data from nonblank cells, only. For example: A1, A2, A5 will
have numbers listed. A3 & A4 are blank. Can i get the formula to use only
A1, A2, & A5?

## Range Of Number Inside A Formula?

May 22, 2013

(isnumber(match({,12054, 12056, 12058}).....

How could I express a range of numbers to identify 12054-13001. How would that expression be written?

## Can't Get Formula To Work When Put Inside A Loop

Apr 14, 2014

LastRow = Worksheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
For i = 2 To LastRow
Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*\$AE\$1/365)/P2),0)"
If (Worksheets("Sheet1").Range("AE" & i).Value < 1.5) And _
((Worksheets("Sheet1").Range("K" & i).Value > 0) Or (Worksheets("Sheet1").Range("L" & i).Value > 0)) Then
Worksheets("Sheet1").Range("AE" & i).Font.Color = 255
End If
Next i

My loop here works just fine, and my Formula works in testing. However when I move my formula inside the loop I get an error.

Is the syntax wrong in this line = Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*\$AE\$1/365)/P2),0)"

## Copy Non Absolute Cell References In Conditional Formatting Formula?

Dec 3, 2012

I have this fairly simple formula which decides whether to shade a cell or not

=AND(\$X\$1<>"TBD",R3<>"None",AC3="Y")

This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.

## Excel 2007 :: Converting Formula From Absolute To Relative Reference?

Aug 13, 2013

I am migrating a bookkeepping spreadsheet from Quattro v12 (c2001) to Excel 2007 and have hit a stone wall over relative 3-D references.

In this workbook each sheet holds data for one month, and this includes some lagging-12-month calculations -- that is, summing a range on a single cell for the preceding 12 sheets (including this one).

Unexpectedly, this formula:
=ISUM('201206:201305'!M6)

gives absolute references, even though I put in no dollar signs after the sheet names. This means it gives the wrong answer when copied to the next sheet to start a new month.

I can find no reference at all to converting the above formula from absolute to relative reference.

## Copy Formula With Absolute Reference & Increment Row Number Every X Cells

Mar 25, 2009

I am creating a spreadsheet which creates a bulk of data from a front sheet.

The question is:
Is there a way to automatically copy a fixed formula for 100 cells using one fixed cell reference such as \$A\$1 and then automatically after 100 cells replace \$A\$1 with \$A\$2, after another 100 with \$A\$3 and so on?

Explanation with Example:

For example, Sheet1 contains the words "Green Tree" in Cell A1 and Sheet2 will then place "Green Tree" into 100 different sentences such as:

Plant a Green Tree
Grow a Green Tree today

This would be created with the formula ="Grow a "&'Sheet1'!\$A\$1& " today"

After 100 different variations using the formula I want to change that formula to reference cell A2 on Sheet1.

I know if I place "Red Tree" in Cell A2 and use the formula ="Grow a "&'Sheet1'!\$A\$2& " today" I can do this manually using find and replace for the 100 cells, but I want to do this for 100 different variations of Green Tree to create a 10,000 different sentences so I'd need to find and replace 100 times!