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

View 8 Replies
ADVERTISEMENT
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?

View 5 Replies
View Related
Jul 9, 2014

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

=COUNTIFS($AZ:$AZ,$AZ15,$AM:$AM,ABS($AM15),$AD:$AD,$AD15)

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

View 10 Replies
View Related
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.

View 10 Replies
View Related
Feb 24, 2009

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

View 4 Replies
View Related
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

View 3 Replies
View Related
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..

View 6 Replies
View Related
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])"

View 2 Replies
View Related
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.

View 3 Replies
View Related
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:

View 13 Replies
View Related
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..

View 8 Replies
View Related
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.

View 9 Replies
View Related
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

View 2 Replies
View Related
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?

View 9 Replies
View Related
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")

View 2 Replies
View Related
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).

View 2 Replies
View Related
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?

View 3 Replies
View Related
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().

View 4 Replies
View Related
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..)

View 3 Replies
View Related
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))

View 6 Replies
View Related
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

View 9 Replies
View Related
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.

View 3 Replies
View Related
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.

View 5 Replies
View Related
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%.

View 10 Replies
View Related
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?

View 9 Replies
View Related
May 22, 2013

I know about

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

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

View 5 Replies
View Related
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)"

View 5 Replies
View Related
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.

View 12 Replies
View Related
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.

View 5 Replies
View Related
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!

View 7 Replies
View Related