Test If A Cell Is Reference In An Array
May 4, 2006
I find myself needing this often and hope there is an elegant formula that can make this easier.
Is there a formula that will test if a particular cell is referenced somewhere in another array or vector? Specifically, I find that I have to aggregate long lists into categories to fit budget formats of various lenders and investors. For example, my detail budget has separate rows for Water, Sewer, Garbage, Electrical, and Gas. These expenses have to be aggregated on one funder’s budgets as “Utilities.” Sometimes after going through this I find that my totals don’t add up, i.e. I left an item out of the aggregated budget. I would like to be able to add a column on the detail budget to test if each budget item has been referenced in the aggregated budget.
View 3 Replies
ADVERTISEMENT
Aug 21, 2013
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
View 3 Replies
View Related
Apr 25, 2007
I am currently working on code that uses either arrays or ranges. I often resize local array based on the dimensions of the input. However, I am running into trouble because arrays use ubound for dimensions, but ranges use ether rows.count/columns.count or ubound on value2. How can I determine whether a variable is an array or a range. the IsArray function happily passes a range variable, so that doesn't work.
View 2 Replies
View Related
Aug 7, 2009
Ok, I'm sure that there is something really obvious here, but I'm just not seeing it....
Test Data starting at cell A1:
View 14 Replies
View Related
Feb 11, 2010
I'm setting up an Excel (2000) sheet to record the results of a competition at my local photographic society. I'm trying to make it as simple as possible to fill out by the people using it, so have shaded the cells that require the user to put data in. I'll post a screenshot of the sheet, which is named "DPI", to make it easier:
(EDIT) screenshot removed - see attached file in post #3 below instead
I use one row for each member entering the competition. Each member can submit up to three photographs for judging. The names in B5:B30 are tied to the member number in A5:A30, and pulled in with VLOOKUP from a separate sheet containing all the club's members. The names in this example are, obviously, fakes. ;-)
The three blocks are to enter the image titles and associated scores. I have a range called "scores" which is defined as =DPI!$D$5:$D$30,DPI!$G$5:$G$30,DPI!$J$5:$J$30
The rank columns are simply calculated using the RANK function. E.g. the formula in E5 is =IF(ISBLANK(A5),"",RANK(D5,scores,0))
The cells at the top are calculated using the LARGE function upon the defined range "scores" (although they could equally use the SMALL function on the rank columsn instead).
The bit I'm stuck at is what I want it to say along the top. eg: "Leg 1 Winner: 30 is {insert image title} by {insert member name}"
Using the winner as an example, I want to take the winning score shown in D1, find it in the named array "scores", and return the image title to the left of it. Obviously I can't use VLOOKUP as the data is to the left of the lookup value as well as being in multiple columns. I wanted to use OFFSET to return the value of the column to the left, but to do that I need to pick up the reference of the cell containing "30" i.e. the winning score shown in cell D1.
How can I find this cell reference? Or is there a better way? I thought about the old MATCH/INDEX function, but INDEX doesn't seem to work very well with data in non-contiguous arrays as I'd have to specify which block to look in.
Once I can get the image title returned I hopefully sholdn't have any bother extending it to return the member name from a fixed column, but I can't figure out how to get the reference of the cells in the "scores" array that contain the winning scores shown in D1, D2 and D3.
View 9 Replies
View Related
May 27, 2009
Sheet 1 contains:
Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)
Sheet 2 contains:
Item Data
ABC 2
I'm trying to get the vlookup to return the value "2"
Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.
The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?
I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.
View 3 Replies
View Related
Feb 11, 2014
I have a report for which I would like to set up a Quarterly summary section. There are 6 working teams listed in my table and I have created two cells with data validation to allow me to specify which team and for which quarter I would like to pull data. My intent is to have a formula in one cell which can extrapolate the quarterly team information based on the values in the data-validated cells.
My worksheet is set up so that:
The data table (tblD) contains metrics for all teams for the full yearTeam names appear in Column A of the table and the reporting month appears in Column BCell M10 has data validation so that the only choices are: Q1, Q2, Q3, or Q4Cells X1:X4 contain the quarterly values mentioned aboveCells Y1:Y4 contain array references, so that Cell X1 = Q1 and Cell Y1 = {"January", "February", "March"}Cell N10 contains a VLOOKUP which returns the appropriate array from Column Y based on value in Cell M10Cell L11 has data validation so that the only choices are the 6 team names
Now I was able to get quarterly information by manually typing the 3-month array into my formula like so:
Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],{"January","February","March"}))
BUT! When I try to use a formula which points to the VLOOKUP cell (N10)--thus allowing me to make the display dynamic--the result is always 0.
Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],$N$10))
While inspecting the formula in the formula bar, I noticed that the values listed in the array appeared with double quotes (i.e. "{""January"",""February"",""March""}"). I tried every permutation of typing the array in the lookup cells I could think of, but none would allow my formula to properly calculate the total I needed.
{"January", "February", "March"}
{January, February, March}
January, February, March
Although I don't often utilize arrays in my formulas, I do employ them from time to time. I'm hoping there's some factor I might have overlooked which keeps my formula from working. is there an issue preventing array references stored in cells from being used in formula?
View 6 Replies
View Related
Mar 19, 2014
Using Excel 2013,
It trying to see if the Month() of a date is in a Array / Range
I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12
View 2 Replies
View Related
Jan 9, 2009
In a worksheet is a table:
a b c d
e f g h
i j h l
m n o p
q r s t
I'm looking for a procedure to (with the help of
an array) reproduce this table exactly under the original one
with one row between them but in the folowing form:
q m i e a
r n j f b
s o h g c
t p l h d
the procedure should be applicable on any haphazard
regardless of its place on a worksheet
View 9 Replies
View Related
May 27, 2009
Using Excel 2003, I'm summarizing info in one sheet (Review) with data in another worksheet (Work Orders). The following SUMPRODUCT calc worked fine...
View 2 Replies
View Related
Nov 9, 2009
How do I test if a cell has #N/A?
View 4 Replies
View Related
Jan 28, 2013
I have the below formula
=IFERROR(INDEX(Settlements!Account_No.,AGGREGATE(15,6,(ROW(Settlements!Account_No.))/(Settlements!Settlement_Date=TODAY()+1),ROWS(N$2:N2))),"")
I would like to copy it across and use different named ranges for the red part, hence have it reference to a cell with the name of the range.
i.e. (which doesn't work)
=IFERROR(INDEX(Indirect("Settlements!"&N1),AGGREGATE(15,6,(ROW(Settlements!Account_No.))/(Settlements!Settlement_Date=TODAY()+1),ROWS(N$2:N2))),"")
View 9 Replies
View Related
Jun 24, 2014
I have attached what my problem is. Have a look, and let me know what you think...have spent hours trying INDEX, MATCH, IF, ISTEXT...
JamesExcel problem.pdfExcel problem.pdf
View 2 Replies
View Related
Jul 15, 2014
Using VBA, I am importing data from Access (Access2007) into Excel. The code that I use works as required but I need to refine it so that I can filter for the records that I need to import. I can import ALL records. I can import selected records by manually changing my SQL query. I would like to reference an array of values or a range of cells so that I do not need to manually change my query every time I want to run it.
[Code].....
This code gets all records for "Jeff" and "Bob" from the Access database. I need to query about 100 names at a time so manually inputting them is going to take forever. How I can make it work?
By using an array:
[Code].......
or
by using a range of cells:
View 8 Replies
View Related
Aug 7, 2013
I have a array as
01-12-2008 pump-1 25
01-12-2008 pump-2 28
02-12-2008 pump-1 24
03-12-2008 pump-1 26
03-12-2008 pump-2 38
03-12-2008 pump-3 35
I need sum value as
03-12-2008 pump-1 75(=26+24+25)
02-12-2008 pump-2 if no answer blank
03-12-2008 pump-2 66(=38+28)
View 9 Replies
View Related
Mar 4, 2008
I am trying to figure out a method for calling the ith number in an array that was defined in another function. The mean function is working and the result is (1 x variables) array. Then, I want to use that array in the sdev function. I am having trouble pulling the ith number from the mean function. Also, is there a way to make variables constant so that they do not need to be declared for every function.
Sub stats()
periods = Range("periods")
variables = Range("variables")
Redim X(periods, variables) As Double
Redim uX(variables) As Double
Redim sdX(variables) As Double
'Load Data
For i = 1 To variables
For t = 1 To periods
X(t, i) = Cells(4 + t, 2 + i)
Next
Next
'Calculate Mean (run 'mean' function')
uX() = mean(X)................................
View 7 Replies
View Related
Oct 10, 2012
I need a UDF to test a single cell if it is colored. If it is the result returned should be a 1, if not the result will be blank. This is what I have so far, but I don't know much about VBA.
Function Filled(MyCell As Range)
If MyCell.Interior.ColorIndex > 0 Then
Result = 1
Else: MyCell = ""
End If
End Function
View 3 Replies
View Related
Dec 31, 2007
I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 =
=LINEST(K8:K50,M8:N50,TRUE,TRUE) .
Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE)
Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0!
I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.
View 9 Replies
View Related
Dec 28, 2008
I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.
View 3 Replies
View Related
Aug 19, 2009
How would I write an IF Then Statement to test if a cell is blank? (meaning it could have "Div/0!", text, 0, or "n/a".)
View 4 Replies
View Related
Feb 17, 2012
I am trying to build a macro that test for the value of each cell of a range (in a column), and if found, then the value of the cell of the same row (another column) will be set to 1. If not, then the macro writes a formula to get some data from BBG (this part is ok). this is what I have done so far but I have an error message, telling me "Not Else with out if" .
Code:
Sub Fx()
With Worksheets("DivRelease")
Dim LastLig As Long
[Code].....
View 9 Replies
View Related
Sep 10, 2008
Is there formula to use if I want an IF statement to add a date "TODAY()" to a cell if a corresponding cell is highlighted? I would search for this in the archives if I had a clue what to search for. "Conditional formatting", maybe?
In Col J "Appt. Date", I want to add today's date if the cell in Col C of that row is green.
How would that work? My worksheet is posted below: ......
View 10 Replies
View Related
Dec 16, 2006
I want to add an IF statement to my macro that will run only if a cell on the worksheet is not blank (empty). I know how to use the if statements but can't figure out how to test a cell for contents.
View 4 Replies
View Related
Jul 10, 2007
I'm testing to see if a cell has nothing in it, and if that is true then it will execute the code. My problem is that even though the cell is blank the field does have a drop down list (list validation set for that field) below is the code i used, but doesn't work.
If Range("b3").Value = " " Then
View 2 Replies
View Related
Jan 26, 2008
I want to reference 2 cells to open a msgbox. Currently I use this formula in a cell "=IF(E4="C",IF(A4>30,"OT","")) I am trying to write a macro that will open a msgbox instead. This is what I've tried so far
Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1, 1).Address = "$E$4" Then
If Target(1, 2).Address = "$A$4" Then
If Target(1, 1) = "C" Then
If Target(1, 2) > "30" Then MsgBox("1")
End If
If Target(1, 1).Address = "$E$4" Then
If Target(1, 2).Address = "$A$4" Then
Target(1, 1) = "F" Then
Target(1,2) > "38" Then MsgBox("2")
End If
View 3 Replies
View Related
Jun 25, 2014
i want to change the table_array reference without changing formula in every worksheet. I tried using a new worksheet and naming it the same as what is referenced in vlookup table_array but it messed up all the data.
I have to use a different worksheet every month so need a way to change reference OR how to change array data without messing up the worksheets with the vlookup
View 9 Replies
View Related
Feb 27, 2014
My company has complicated time sheets because we have several tasks that are billed differently to different people. Once a month we have to sit down and compile everything from several forms and so forth. I have created a worksheet that pulls all the numbers together so that they can simply be copied and pasted into our reports. The hope was to simply copy this worksheet into a time sheet workbook and it will pull out all of the correct numbers. Although all the time sheet workbooks are set up the same way, whenever I copy the file into another time sheet workbook the program keeps its references from the workbook it was in. Make sense? is there a formula that I can insert into an array to tell it to pull the information from the worksheet with the same name, but in the active workbook?
here is one of the equations I am working with:
{=TRANSPOSE('1st week'!A10:L48)}
so it would look something like this maybe
{=TRANSPOSE('[active workbook]1st week'!A10:L48)} but this doesn't work of course.
View 3 Replies
View Related
Aug 22, 2014
Basically I have a column (lets call it column A) whereby I manually fill the cells green once I have received some documents, another column which has a numeric value in it (column B) and I want to create a third column which basically just copies column B but ONLY if column A is filled with a colour (actual colour doesn't matter cause I only use green)
I tried using the IF function but I don't know how to use cell colour as the logical test
View 3 Replies
View Related
Feb 28, 2008
I'm trying to write a macro to test whether a cell has any neighbouring cells that match it's value.
I'd like this to be cumulative so that the more matches, the higher the value.
I'd then like the result to be written in another cell to set up a separate grid.
So far I'm getting error messages and I don't know why:
View 9 Replies
View Related
Jun 15, 2009
In column AX2:AX2000 there is a value(alpha numeric) MB60176685 and in column AX2:AX2000 there are various values.
I would like to test if any cell in AZ 2:2000 is greater than 200. If yes then blank AZ in that row. If less than 200 go to the next row. The value of AX will remain the same in that row.
SEE EXAMPLE of results BELOW
AX AZ
201
MB400174444 10
MB400174444 155
View 2 Replies
View Related
