Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Absolute Reference Using Variables In Formula


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Copy Formula With Absolute Reference & Increment Row Number Every X Cells
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 Replies!   View Related
Absolute Reference (cell Reference Behind The Table)
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:

View Replies!   View Related
Absolute Row Reference
column headers are people in my department spread verticall goin down column A on my lead sheet. I am trying to report metrics for each person in their row.

I am trying to link the metrics for reporting from 5 seperate weekly batch reports that represent 5 tabs within the same workbook.
The problem is that I have the total information for each metic in column H of all 5 tabs, but all metrics for individuals are reported vertically. (H6, H7, H8, H9)

Thus how do I get the following: =SUM('Week 1'!H4)+('Week 2'!H4)+('Week 3'!H4)+('Week 4'!H4)+('Week 5'!H4)

To Equal: =SUM('Week 1'!H5)+('Week 2'!H5)+('Week 3'!H5)+('Week 4'!H5)+('Week 5'!H5) When I copy the formula across the row for each individual

My column to stay constant, but my row to increase by 1 as I fill the formula across my spreadsheet.


View Replies!   View Related
Absolute Reference In The Column
explain with an example.

Cell A1 =A2/MAX(A2:A5)
Cell A2 1
Cell A3 2
Cell A4 3
Cell A5 4

Cell A1 is 0.25 right? Right. Now insert a cell (or row) between cell A1 and A2. Then cell A1 changes to =A3/MAX(A3:A6). I don't want this to change. I still want to formula in A! to be =A2/MAX(A2:A5). I've tried using the "$" absolute character and I've tried using the absolute reference in the R1C1 reference style both to no avail.

View Replies!   View Related
Absolute Reference Check
Is there any way to check a worksheet/workbook to ensure that all formulas contain absolute references?

View Replies!   View Related
Copy An Absolute Cell Reference
How do I copy an absolute cell reference from say a1 to a2 -100 so that the row reference increments with each row. Without the absolute cell reference '$' it works Ok. But with it every cell is the same as a1?

View Replies!   View Related
Absolute Cell Reference :: Dividends
I have a number in cell A2. I have hundreds of numbers (dividends) spanning across row 1. I want that number in A2 to be the divisor in my formular all across row 2.

In cell B2... if I type the formula "=B1/A2" i get the quotient I want. If I copy and paste this formula into cell C2 the divisor changes as well as the dividend. How can I "copy and paste" this so that the divisor stays the same and only the dividend is variable.

View Replies!   View Related
Absolute Reference To The Cell Even When Rows Are Inserted
I have a spreadsheet that gets updated from the top. Is there any way for me to mod my formulae so that they always begin with the topmost cell (row 2 in this case, and in many columns) but still extend downward?

More practical example:

I have an Average formula in column X which (at the moment) averages X2:X75. I would like, even when adding new rows at the top (in the row 2 position) the forumla to not need to be manually extended with every new entry. So, if I add three more entrie4s, it will then average X2:X78. I tried all manner of absolutes.

View Replies!   View Related
Invalid Function When Using Absolute Reference On Another Worksheet.
I'm creating a chart that will show a running average of a set of values. First column has the values and the second is a running average. Here's my sample data:

55.0
65.5
8732.7
425.5
521.4
8832.5
528.6
826.0
423.6

My formula for the second column is as follows, =AVERAGE($G$1:G1), =AVERAGE($G$1:G2)... and so on.'

My problem is when I try to run the same formula on a different worksheet to put on the current worksheet, I get an error from Excel that the formula is invalid. Here's the formula I use, =AVERAGE('Team Stats'!$A$I1:AI1), =AVERAGE('Team Stats'!$A$I1:AI2), ...

Is this a bug with Excel? Is the format wrong? I've tried just using the function wizard and it tells me that the absolute reference is invalid.

View Replies!   View Related
Udf Vb Looking To Define A Dynamic Absolute Reference
I'm trying to create a user defined function to drop into dblookup functions that will always reference the first cell in an associated column and the first cell in the row of an associated column.

I came up with the following, however the problem is that the udf always returns the values for the active cell's, row and columns.

Function SRCref2()
Application.Volatile

myfield1 = ActiveCell.Column
Dim1 = Columns(myfield1).Range("a1").Value
SRCref2 = Dim1
End Function

Function SRCref3()
Application.Volatile

myfield2 = ActiveCell.Row
Dim2 = Rows(myfield2).Range("a1").Value
SRCref3 = Dim2

End Function

I would nest these in a formula such as "=srcsum(srcref2(), srcref3())".
Ideally I could then use this same funtion anywhere instead of having to use and worry about the accuracy of "=srcsum($a$2,$b$1)"."=srcsum($a$3,$b$1)"."=srcsum($a$4,$b$5)".etc.

It seems like there should be a current cell method or something but I can't find it and I'm not smart enough it seems to use vb to designate some kind of floating intersect as a base for a row and column reference.

View Replies!   View Related
Countif Error With Absolute Range Reference
I've been testing the countif function and for some reason it isn't working for me.

I have the following text in cells A1:A3
"apple"
"banana"
"peach"

I have the following text in cells C1:C3
"apricot"
"banana"
"grape"

I put the following formula in B1 and copied down into B2 and B3.
=COUNTIF($C$1:$C$3,A1)

It is meant to count how many of each fruit in A1:A3 is also in C1:C3.
The banana is the only one that is in both lists so the formula in B2 should return 1.

But all three formula's return zero.

If I change the range in the countif formula to relative it works. eg., =COUNTIF(C1:C3,A1)

View Replies!   View Related
Absolute Cell Reference :: Alpha Characters To Increment
I've got a worksheet here that i need to update every week. The problem i'm having is that i have a table of values that's drawing values in from another larger table. For example in B9 the cell is referenceing the I2 cell in another sheet. When i pull down the formula in B9 i want the cell below to reference J2 and so on. In other words, I only want the alpha characters to increment. Tried messing around with dollar signs but it doesn't seem to be doing what i want.

View Replies!   View Related
Copy Selected Cells, Then Change To Absolute Cell Reference
I need a macro to do this small task for me. Preferably it should execute when I have selected a cell and press a command button. Here's the way it should work:

Copy the cell I have selected

View Replies!   View Related
Change A Macro To From And Absolute Cell Reference To Selected Cell.
i am currently using the macro below to import text files into a spreadsheet. Currently, it begins the import in cell A1 which is what I recorded it to do. how do I change the code to begin the import on the active cell?

View Replies!   View Related
Reference Cells Using Variables
I am having an error when I try to run it and it appears not to like my destination (line bolded) - WHAT AM I DOING WRONG.?? Numrows = Worksheets("File1"). Range("A65536").End(xlUp).Row 'get the Row count

' LOOKUP Looklist! (Col B)
'----------------------------
For d = 1 To 228
' looks in Looklist B2(2,2), check against G1(1,7)
If Worksheets("Looklist").Cells(d + 1, 2).Value = Worksheets("File1").Cells(1, d + 6).Value Then
' If matches then copies G-x(lastrow,7) to looklist L2(2,12)
Worksheets("File1").Cell(Numrows, d + 6).Copy _
Destination:=Worksheets("Looklist").Cells(d + 1, 12)
End If
Next d

View Replies!   View Related
Absolute Column And Absolute Row Problem
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 Replies!   View Related
VBA: How To Get Either Absolute Column Or Absolute Row Only
how to get either the absolute row or the absolute column of a cell.

Suppose the active cell is H27.
If we use:

ActiveCell.Address
we will get $H$27

How can we get either $H or $27 only assigned to a variable let it be MyVar.

Please keep in mind that converting the $H$27 to a string and then making string manipulations is not accepted, unless of course it's the only way to go.

Also it would be nice if I get answers on how to get the relative address column/row portions only, too. Like H or 27.

Another tip is that using .Column returns a number, not a letter.

View Replies!   View Related
Reference Variables / Constants In Add-in From Workbook
I'm wondering if it is possible to declare a constant in an add in, and then reference that constant from workbooks that use that add in?

Basically I want to set a version number in the add in, and check it from the workbook.

View Replies!   View Related
Using A Relative Or Absolute? Cell In Formula
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 Replies!   View Related
Anchor Cell In Formula. Absolute
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 Replies!   View Related
Reference Cells & Ranges With Numeric Variables
I have the following macro, which works fine (extraneous code not shown)...

For I = Start_ROW To Last_ROW_Z
Range("A" & I & " : " & "O" & I).Copy

But I want to make the "O" column identifier dynamic. So I've defined the column with the dimension...

Last_COL_Tracking = Sheets(" Tracking").Range("IV" & Start_ROW).End(xlToLeft).Column - 1

But this returns a column number, not the letter, so I can't use it in my original formula.

I tried converting to R1C1 with the formula...

Range ("R[" & I & "]C[1]:R[" & I & "]C[" & Last_COL_Tracking & "]")

...but this errors out.

View Replies!   View Related
Copy Absolute Formula, Without Changing Every Cell
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 Replies!   View Related
Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

View Replies!   View Related
Formula To Calculate The Absolute Total Of A Column Of Numbers
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 Replies!   View Related
Embed Formula In VBA (calculate The Absolute Week )
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 Replies!   View Related
Update A Formula To Add An Absolute Value Of Amounts In Cells
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 Replies!   View Related
Add Relative/Absolute Formula To Range Macro
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 Replies!   View Related
Add Vlookup, With Variables, To Cell To Reference Another Workbook Via Macro Code
I am trying to use VB to vlookup between to workbooks
1. Make active workbook WBK1
2. Make workbook being open WBK2
3. Copy and Paste between WBK1 and WBK2
4. Have a vlookup in WBK1 and bring in the values from WBK2
5. Close WKB2
6. Copy, Paste, and transpose values in wkb1 within wkb1

The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.

View Replies!   View Related
Formula Change To Gather The Absolute ABS Sum Instead Of The Net
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 Replies!   View Related
Change Multiple Formula Range References Between Absolute/Relative
Is there a way to absolute reference multiple cells at one time?

View Replies!   View Related
Stop Formula Column Reference Changing On Insert But Not Row Reference
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.

When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.

View Replies!   View Related
Cell Reference :: Formula To Reference New Cells
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View Replies!   View Related
Circular Reference With Formula But No Reference To It
=INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),1,),0))
Where
A1= "M16" and B2= "185%RPIT630"
'Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5=QxTermAge63
Can some on tell me why this is raising a Circular Reference!!


View Replies!   View Related
Assigning Variables In A Formula
I think I'm using the correct terminology with the term "variable", but to explain what I'm trying to do, I want to get RAND() to hold the first returned variable so I can compare it to other places RAND() has been used & if the returned value is the same as the other place, then run RAND() again.

Here is my basic formula (where $B$2 is 50):

=IF(ROUND(RAND()*$B$2, 0)+1=A4, ROUND(RAND()*$B$2, 0)+1, ROUND(RAND()*$B$2, 0)+1)

I'm trying to say in the formula above that if rand = what was already in A4 then run random again -- but this doesn't keep if from returning the same value as A4 on the second pass.

What would work is something like (where X is the assigned variable):

=IF(X=ROUND(RAND()*$B$2, 0)+1=A4, ROUND(RAND()*$B$2, 0)+1, X)

But it doesn't appear you can use X in a formula (only in a macro which I don't want to use) --

View Replies!   View Related
IF Formula For Sets Of Variables
If a value fallw within these ranges, then the next cell should calculate the answer according to the percentages outlined.

for exmaple:

If somethng costs 250.00 then the next cell should display - 250 x .50 and the answer would be 375.00

1-500 = 50%
501-1500 = 45%
1501 - 1600 = 40%
1601 - 2500 = 35%
2501+ = 30%

and automatically calculate the answer.

I know there's an IF formula, but I can't seem to figure out how to do multiple IF's ~

View Replies!   View Related
Nested If Formula/Function With Other Variables
I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))

View Replies!   View Related
Absolute Value Of Sheet To Absolute Cell Sheet?
I want to move the absolute value of sheet 2 $D$42 to absolute cell sheet $F$15. The !sheet2 $d$42 is not working.

View Replies!   View Related
How To Assign Formula Containing Values From Variables In VBA
I want to assign a cell a formula that changes base on variable, example

myVar=3
Cell(1,1).value = "=average(1:{myVar})"

what do i do to make this work instead of {myVar}?

View Replies!   View Related
Assign Formula Values Based On Variables
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.

=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))

View Replies!   View Related
Formula Won't Calculate When Only Some Of The Three Variables Are Input
In column H: =IF(ISERROR(L4-J4-K4),"",IF(OR(K4="0", K4=""), L4-J4, L4-J4-K4))
So, if formula is going to come up with an error, I don't need to know. If it's not an error: L - J - K. Except that sometimes there isn't information in J or K, and the formula won't calcluate L - 0 - 0, unless I physically enter in zeros.

=IF(ISERROR(L4-J4-K4),"",IF(K4=""), L4-J4, L4-J4-K4))
and
=IF(ISERROR(L4-J4-K4),"", L4-J4-K4))

I don't want the people using this to have to enter in a plethora of zeros in order to force the calculation. What am I doing wrong? I've attached workbook with the relevant information in it. You can see in the middle that if there is nothing entered, it won't work right.

View Replies!   View Related
Macro To Add Nested Formula To Cell With Variables
I want the range in the VLOOKUP below to display its actual value - ie "'6 June 08'!$A:$C" in the formula rather than the actual variable name 'strResult'.

Sub PreviousCount()
strDate = "6 June 08"
strColumnRange = "$A:$C"
strResult = "='" & strDate & "'!" & strColumnRange

Dim i As Long
i = Range("A2"). CurrentRegion.Rows.Count
Range("D2:D" & i).FormulaR1C1 = "=IF(RC[-3]="""", ""Column A blank!"", IF(ISNA(VLOOKUP(RC[-3],strResult,3,0)), ""NEW INSTALL"", VLOOKUP(RC[-3],strResult,3,0)))"
End Sub

View Replies!   View Related
Add R1C1 Formula With Code & Variables
I am using follwoing vb code to enter formula to sum a range. However it is giving sum of different range.

Cells(Row, Column).FormulaR1C1 = "=SUM(R[" & a & "]C[" & b & "]:R[" & x & "]C[" & y & "])"

Where a, b , x and y are variables containg starting row, starting column, last row and last column value like a=19; b=3; x=24 and y=3. When i check the formula in that cell, it appears as Sum(F25:F30)

View Replies!   View Related
Unzip Code - Works Without Variables, Breaks With Variables...
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View Replies!   View Related
Add Nest Formula/Function To Cell With Variables Via Macro Code
I would like to use a varible Cell for the following forumula:

ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"

I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).

View Replies!   View Related
Copy Pairs From 2 Columns To Formula Reference Cells & Copy Updated Formula Results
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.

View Replies!   View Related
How To Reference The Formula Not The Result
I am setting up a spreadsheet that we can use with EditGrid online so we can post it in Forums. These will be used to track contracts for NFL players. I have used multiple formulas to do this. The problem is that I need to do this for 32 teams over 10 years. So what I want to do is be able to have a master sheet and then just have the other 32 sheets pull those formulas from the master but use the info on the individual teams sheet. I'll give you some basics.

The first year starts out where you type in a players position, name, contract length and his rating. here is an example of one of my formulas. The very basic first one

=if(D13

View Replies!   View Related
Use A Cell As A Reference In A Formula
I currently have the formula =+'Mar-Final'!$C10*M3/N3.
I would like to have the 'Mar part be input from a cell.

For instance, if I put Feb in cell A3, then the formula would look like ='Feb-Final'!$C10*M3/N3.

View Replies!   View Related
Reference Last X Entries In Formula
I input sales for each day of the week on a separate sheet since the beginning of the year. I am taking the average of the last 12 (Tuesdays) but with each input I need to change the function. Say it was Average(B11:B22) with the new entry in B23 I would change Average(B12:B23) I would like it to do it automatically with each entry.

View Replies!   View Related
3d Reference In An INDEX Formula
i'm trying to search throgh multiple sheets using an INDEX formula.

=INDEX('12.31.09:11.15.09'!$AF$14:$AG$75,MATCH(E$2,'12.31.09:11.15.09'!$AC$14:$AC$75,0),MATCH($B61,' 12.31.09:11.15.09'!$AF$12:$AG$12,0))

In short, these are timesheets; in a separate sheet i'm using the above formula to find how many hrs were worked on all the other sheets for a given job# within a given date range. (in the above example, i'm only searching in timesheets between 11/15 and 12/31, but I will ultimately change that to include the entire year '12.31.09:01.15.09'). E2 is a referenced job number; B61 is a referenced date.

I'm getting a !VALUE error.
Can a 3d reference work inside an INDEX formula?


View Replies!   View Related
Formula That All Reference To Different Documents
I have 330 lines of formula that all reference to different excel documents. These files are donated by a 4 digit number. i.e. 4186.xls.

The main document that looks at these files needs to ability to acknowledge changes in the 4 digit number in its formulas and look at a different file if necessary.

EG. =sum("4186.xls"!E4:E5)

A very simple example of the formula i am using. I just need to actively change the 4186 that is in the formula to whatever is in the contents of another cell.

I might just be stupid. Infact, i admit i am no expert. That was my first =SUM formula actually.


View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved