I have 2 excel files A & B. In cell A1 of file B, I use an index formula to refer to a row in file A. "INDEX('[A.xlsb]A'!$10:$10,1,2)" is the formula used (referring row 10 in file A).
If I update the row number in another cell of file B, is it possible for this formula to refer to that cell to get the row number from file A?
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B
B1 contains 2
B2 contains "tribbles"
An imaginary function might go like this........
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
is it possible to refer to a cell by a variable cell number? For example suppose i want to refer to a cell on column B, by a value which is in cell C1, so the if C1 has the value 7, i would refer to cell B7, abd if it has the value 87 i would refer to cell B87.
View 2 Replies View RelatedI want to write a formula that inserts a column and then does a vlookup in that new column using the cell just to the left of it as the first variable. The problem is, this could be in any random column so I can't do a static reference to $A2 as an example. So, my question is...
Instead of using =vlookup($A2,Sheet1A1:B50,2,false)
How do I replace the $A2 part with some code that says, "the cell to the left of here..."
Sheet names in Col B
B2 : Sheet1
B3 : Sheet2
Etc
I tried
=INDIRECT(B3,$J$58)
at C3 where $J$58 is the cell I would like to reference on 50 different sheets
Formula returns #REF!
Col C ref's $J$58
Col D ref's $L$58
Col E ref's $N$58
Col F ref's $P$58
So as I copy the 4 formulas down, the ref to the correct sheet should update but the cell ref's should remain
What i would like to happen is after inputing a number into say column B have that number update a formula in cell D2.... so for example... lets say i have
B1 = 1000
B2 = 2000
B3 = 1400
B4 = ???
D2 = (B3-B2)/B2
is there a way to make it so when i enter a new number in B4 it will automatically update the formula to display the value for (B4-B3)/B3 and then continue on to repeat this process when i enter values into B5, B6, B7 ect?
Following is a summarized example of my data and what I am trying to accomplish.
[Column A] contains a list of account numbers. [Column B] contains current balances, [column C] contains balances from one month ago, [column D] contains balances from two months ago. Within the same spreadsheet I want the ability to type in the account number in one cell and then the column number in another cell. For example, If I type in the account number 1234 and the column number 3, I would get the balance from [column c]...if I typed in the column number 4, I would get the balance from [column D].
My first thought was to use a simple SUMIF formula that would compare the account number I type with the account numbers found in [column A]. The problem is getting a formula that can translate the number 3 to [column C] or the number 4 to [column D]. Note: the actual spreadsheet I am using extends out to column BI.
This is simuilar to the Column Index Number used in a VLOOKUP formula.
I'm trying to build an Index-Match formula to retrieve a number from a "data" sheet onto a "summary" sheet.
In example spreadsheet, I need to retrieve the stock price on a specific row from "data" sheet, and as an alternative, the most recent price.
I am trying to find a formula that populates a room number based on the number of hours used. I have a chart that is by building but multiple rooms within each.
Where I am having trouble is when there are more than one room with the same number of hours used then sometimes the formula populates the wrong room number.
Attached is my example spreadsheet of my chart. Book1.xlsx
I have shown what I need, what I have tried and a blank space to show where I need it to go.
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
View 3 Replies View RelatedI 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
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
View 4 Replies View RelatedI'm trying to use arrays in a macro!
Here's my
Sub ArrayTest()
'
' ArrayTest Macro
'
Dim Array1() As Variant, Array2() As Variant
Dim R As Integer, ilR1 As Integer, iLR2 As Integer
ilR1 = Cells(Rows.Count, 1).End(xlUp).Row
iLR2 = Cells(Rows.Count, 7).End(xlUp).Row
ReDim Array1(1 To ilR1)
ReDim Array2(1 To iLR2)
For R = 1 To ilR1
Array1(R) = Cells(R, 1) & Cells(R, 2) & Cells(R, 3)
Next R
For R = 1 To iLR2
Array2(R) = Cells(R, 7) & Cells(R, 8) & Cells(R, 9)
Next R
For R = 2 To UBound(Array1)
If WorksheetFunction.CountIf(?????, Array1(R)) > 0 Then Cells(R, 4) = "old" Else Cells(R, 4) = "new"
Next R
'
End Sub
Basically, the macro concatenates the content of three columns from two different tables into two arrays. Array1 contains the current stuff and Array2, the old one. If I CountIf items from Array1 into the content of Array2, I should get what's new (count 0).
Now, ? in the code should refer to the content of Array2. How do I do that?
The errors I get are 424 object required [Array(iLR2)], 1004 method 'Range' of object '_Global' failed [Range(Array2) or Range(Array2(2), Array2(iLR2))], or type mismatched [Array2].
I know I've asked before but I can neither find my previous question or the answer.
So, once again.... is it possible to refer to a named range in a CSE array formula?
I know how I would type a formula that way -- but I would be happy to see an example.
Most importantly, will it always work? Or are there pitfalls and dangers?
INDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))
I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.
I have a cell with a fairly long formula for concatenating a large number of other cells. Let's call it cell "A". Due to the formatting and end use of the data in cell "A", I have to copy and only paste the values of that cell into another cell, which I'll call cell "B". What I need to know is whether there is a way for the values in cell "B" to automatically update in the same way they do in cell "A" when data changes in any of the cells that are being concatentated.
View 2 Replies View RelatedI am linking to cells on a spreadsheet and in some cases the formula is showing in the cell and in others the contents of the linked cell. I have forced updating (F9 )and F2 enter with no effect. Is their something i am missing as I want all the links to update automatically. If I copy and paste a working formula into one of these cells it updates albeit with the wrong reference but if i go in and edit F2 etc then it shows as a formula.
View 4 Replies View RelatedI am trying to get an Index-Match formula to search a different sheet depending on the value selected in a particular cell using a dropdown list. See the attached workbook.
View 3 Replies View RelatedIs it possible to have a formula check the color index of a cell?
Example if A1 color index = 6 then X
I am using the formula below to sum across several worksheets, and the formula I am using works fine, but when I copy the formula across to other cells, I can't figure out how to have it update my cell range:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!G:G")))
The range A:A is ok to be static, but I need the G:G to be dynamic so when I drag it to the right it will change from G:G to H:H. I:I, etc.
I have a table which takes the average of the last three years. The formulas are in cells below the data. The data is set up to be first data down to the oldest data. each time data is added, a row is put in on top. how do i get the formula in the cell to update dynamically. What would the average formula look like in the cell? Do you just say =average(name of file!RANGENAME).
View 5 Replies View RelatedAttached file 140515 Matchsheet.xlsx has formula that finds costs matching two criteria.
But the formula depends on me typing in the specific Worksheet Name: So to find the cost for 'Type 1' products, I enter:
=IFERROR(INDEX('Type 1'!$E$5:$F$11,MATCH($C$4,'Type 1'!$C$5:$C$11,0),MATCH($D$4,'Type 1'!$E$4:$F$4,0)),"")
I want the formula to "see" the sheet name as whatever is in a specific cell.
E.g. Cell B4 says "Type 2". The formula will be:
=IFERROR(INDEX('[WHATEVER NAME IS IN B4]'!$E$5:$F$11,MATCH($C$4,'[WHATEVER NAME IS IN B4]'!$C$5:$C$11,0),MATCH($D$4,'[WHATEVER NAME IS IN B4]'!$E$4:$F$4,0)),"")
I work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.
One of the columns refers to the date on which a new application for funding can be made to that particular funder.
In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.
For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.
I have a selection that I am going through with a 'for each' and then performing actions if the cells meet a certain criteria. I have been referencing nearby cells with the offset function, but now my sheet is too big and I need to change the code often and the offset function is difficult, is there anyway to refer to cells without the offset function, preferably by column letter
PHP Code:
For Each Cel in SelectionIf Cel = x Thenvalue1 = Cel.Offset(0,39).Valuevalue2 = Cel.Offset(0,5).ValueEnd IfNext CelÂ
In conditional formatting, which formula do I use to refer to another cell? Trying
=(ISBLANK(K1))
without luck.
How can I use (with or without macro) cell text to refer to different worksheets inside a formula. For example I've formula =INDEX(Mary!B9:E17...) but I wanted to write that 'Mary' or whatever sheet name to a cell in one sheet and sheet refenrence in this index-formula would change accordingly.
View 5 Replies View RelatedI want to know all times this one presents the number 3 in the last 10 cells of the A column
For a static range I use this function
= COUNTIF(OFFSET(A16;-10;0;COUNT(A:A));3)
If I add a new cell which is the function for a dynamic range?
I can refer to the cell with this function = ADDRESS(MATCH(300;A:A);1)
but not as integrate it.
I have a cell containing a 'bullet' symbol (•) and a cell containing text.
If I make the bullet red and the text is black is there a way I can combine them in the same cell and the bullet remains red and the text black?
Essentially the situation Im in is I have a load of text I need to add coloured bullets to and I dont want to individually paste in and colour format all the bullet points because it would be too time consuming.
Basically i want to use a UDF to count cells in a range of a certain colour.
But i want to make it easy for the user to be able to change the colour the function counts, so i thought i could ask them to colour the cell in which the function is written.
Is there a way to tell the function to pick up the Interior.ColorIndex of the cell it is written in?
Use Application.caller.interior.colorindex