Return A Value From A Table Based On Several Other Cells
Mar 26, 2014
So I've been trying to set up a table.
The table is made of 4 columns, with several times the same values. Each column has a specific range of value. No possible combination can exist twice. Meaning I won't have two rows that are identical. It looks like :
A1@X
A1@Y
A1&X
A2&X
B3#K
B3#O
B3£K
B4£K
C5$P
C5$M
C5*P
C6*P
The user tells me the value he wants in the 3 first columns, and I want to tell him what's in the 4th one.
View 3 Replies
ADVERTISEMENT
Jun 25, 2009
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies
View Related
Aug 11, 2013
I'm trying to return 2 values from a table selection, based on a value i.e. if I look up the table for "Team 1" - i'd like to return Team 1 v Team 6, Team 9 v Team 1, and so on, to a Fixtures Section in a different area of my Excel Sheet.
However, the look up value "Team 1" could be in column J or N. I've tried Index, Match, VLookup, IF statements etc....
See below:
J
K
L
M
N
[Code].....
View 1 Replies
View Related
Jul 26, 2014
Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?
View 4 Replies
View Related
May 8, 2014
As shown in the attachment, I'm trying to return value when matching cell B7 + D5 with cell G5:I7. Is there any way to do so?
Attached File: File Type: xlsx Book1.xlsx‎
View 3 Replies
View Related
Apr 23, 2014
I have a worksheet that I want to populate with specific data from another worksheet.
If all of the following are met:
1) Column A equals "763" and
2) Column K is formatted Red (255, 0, 0) from conditional formatting
3) Column P is blank
Then in my blank worksheet I need to display the data from Columns A, B, D, K, O
View 4 Replies
View Related
Oct 3, 2006
I am setting up a spreadsheet for a Stock Broker. I need to have a cell produce a value dependent on the value of another cell. Specifically, I need the calculation cell to change depending on whether another cell has the letter A, S, Q, or M. I am calculating the value of investment income based on the Payment terms (A;annual, S;semiannual, Q;quarterly, M;monthly) So if for example Cell A7=A, then Cell C7 =”specified formula for Annual income”
View 5 Replies
View Related
Dec 15, 2007
The data attached shows the length of individual fixations (A4:A8) and whether the fixation was ON (=1) or OFF (=0) a specific target (C4:C8). I want to know how much time was spent ON target for each 250ms time bin (i.e. between 0-250ms, then 250-500ms etc).
My problem is that each fixation is a different length and may cross over the time bin boundaries. IF(AND(THEN ) works for the first few time bins but I get stuck once the first fixation is accounted for. I have filled the rest in manually so you can get an idea of what I want it to do.
View 3 Replies
View Related
Oct 14, 2008
I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12).
What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400.
Is there such a formula or do I have to enter manually the amounts into an If function?
View 11 Replies
View Related
Jan 22, 2008
I have 5 options that I am designating as "Allocation Methods" for a number of line items. They are numbered 1-5, but i have custom formatted them so that they have text in the formatting. They are as follows;
Allocation Method
1 - Allocation per Hard Cost
2 - Allocation PSF of Proj. or Hotel
3 - Allocation per Loan
4 - Allocation per Parking Spaces
5 - Allocation for Park
I will allocate one of the above allocation methods to every line item in an allocation column by hardcoding 1 - 5 in a cell for each line item. What i cannot figure out (or figure out if it is even possible) is how, for instance, can I type in a 1 and it have it automatically formatted per number 1 above & then if I change my mind to method 2 and type 2 it will automatically format per number 2 above. I know I can do this by creating a reference column right beside by allocation column, but I would prefer not to use that method.
View 6 Replies
View Related
Sep 14, 2007
IF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.
Column - A - B - C - D - E
3 4 / /
5 6 9 / / /
I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.
View 8 Replies
View Related
Jan 29, 2010
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying:
Sub usstates()
Dim R As Range, s As String
Set R = Sheets(1).Range("J1:J50000")
For i = 5 To R.Rows.Count
X = UCase(R.Cells(10, 1))
If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then
R.Cells(i, 2) = "SC"
End If
Next i
End Sub.................
View 9 Replies
View Related
Dec 23, 2009
I have a pivot table and next to it I added some calculations which are based on data from the table. In my "real" file, the cells next to the PT are formatted with conditional formatting based on other values in the sheet (see attached sample file). What I now need to do is add borders to the cells in columns “D:G” only if the cells in “B5” and downwards are not empty. I have tried doing it with conditional formatting but there are some other rules which contradict this setting.
The idea is to make the cells in columns “D:G” look like they are a natural extension on the PT. However, the table can grow or shrink (in length) and I want to see borders only around cells that are on the same rows as the data in column “B” (staring with B5). I think that whatever solution you can provide (if you can...) should be based a workbook_change event, because in my “real” file, the length of the table is changed whenever I select a different “page” of the PT.
View 8 Replies
View Related
Dec 2, 2013
I am using Windows 7 and Excel 2010.
Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?
I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)
Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......
Here is the table:
I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green
LEGEND
Earned Value Limits
Milestone
RED
Yellow
Green
Turquoise
Blue
M2-M3
2.15
M4-M6
1.66
M7-M11
1.26
View 2 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Dec 5, 2013
I am trying to basically search for the last non zero entry in a column and then return a value in the same row as that last non zero entry.
For example:
My first column of the table is time, second column is submission/marking of coursework (denoted by +1 & -1) and the third column is the number of courseworks awaiting to be marked. My aim is to search the third column for the last non zero value then return the time (from first column) that last non zero value appears.
View 6 Replies
View Related
Dec 7, 2008
Part 1: Pricelist
The user presses 1 of 3 macro buttons to access the price list.
The buttons represent 1 of 3 parts of a construction project.
The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro,
depending on which button is pressed, hides all but the relevent quantity
col & total col.
Each job phase needs its own total & having it setup this way makes it
easier to enter new items into the table (only have to enter
the item once, rather than 3 times).
The first html shows a simplied example of the "price list" described above
(before & after the quantitiy & price cols are hidden).
The user adjust the quantity & the sheet calculates the price total.
Part 1: Material listThe material list is a list of items & quantities the user selected in the
quantity col of Price list. This list is to be printed & given to the workers.
It does not show price, only item & quantity.
This list, unlike pricelist, must be visiablly broken down by job phase.
I dont want the material list to be the price list repeated vertically
on a sheet 3 times. This would take up too much space & require new
items to be entered once in the pricelist & then 3 times in the mat list.
I would rather the mat list be on a seperate sheet from price list & have
a formula in cell matlistA1 that searches the phase 1 quantity col
(then phase2 & 3) for the first #>0.
When a #>0 is found, the formula
would return an item name into the cell on matlist. Then the formula
would run in matlistA2, & search for the next #>0.
(& so on until a complete mat list table, broken up into phases is created)
Sheet2
ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73
..............................
View 9 Replies
View Related
Aug 31, 2009
I have an 'existing results table' as per my attached sample. From this table I need to create a list of 'sold' for each annual date range - i.e. I need a separate list for items sold within that date range. see my example in red. *Note. I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
View 4 Replies
View Related
Apr 14, 2009
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
View 3 Replies
View Related
Mar 23, 2014
I uploaded a simplified version of the workbook I'm manipulating for reference. I'm looking for a formula where I can get the ROW heading of a table to be returned based on the max value of a certain column. My column headings in the table are months. I have separate place in the workbook where I have the Months listed in a Column (A9:A......) and in Cells (B9:B....) I want to return the row heading name from the table. The tricky part is that the column headings don't always stay in same place, so Jan isn't always in A1, sometimes it is in B1 or C1 of the table.
Essentially from my months listed is separate part of the workbook. In the cell next to each month I want to match that month with column in the table. In that column find the max value and return the row heading.
View 6 Replies
View Related
Mar 20, 2014
I am trying to create a formula to return values based on the Total of a Table in each worksheet.
Example:
Column A has a list of worksheet names
Column B I want to return the value of the Total from a table on the worksheet named in Column A.
Each Table might be different widths (i.e., not the same number of columns).
Problem, I want the formula to use the worksheet name to goto the correct worksheet, and retrieve the data.
Each worksheet will only have one table on it.
View 1 Replies
View Related
Dec 10, 2008
I am attempting to return a value in a table using an IF statement, but without referring to other values in the table.
My first column contains price break quantities;
5
10
20
50
70
100
The top row contains selling quantities;
1410192541517699105
The IF statement should return a "Y" in the cell if the value in the top row is greater than the value in the first column, but only for the relevant price break, i.e. the column with 51 in the first row should only have a "Y" in the row with 50. This would be much easier if I could work out how to post the workbook!
The formula cannot refer to other row values (otherwise it would be easy), and cannot use VB.
So far, I have come up with;
=IF(AND($C6
View 9 Replies
View Related
Apr 25, 2014
My aim is to return a value from a table (Price of models) using a two way lookup method.
E.g. The code is CA45-AI, I'm looking for a formula to match the first 2 letters 'CA' to the TYPE RANGE (cell range) so excel knows to return a value from the CA row.. then, match the number '45' (from the CA45-AI (cell b3), so in the end.. excel returns the value (price) '90'.
Another example, Cell B5 is RA34-AI, so excel should return a value of 80 from the Price Of Models (cell range).
View 6 Replies
View Related
Sep 10, 2009
Main (Sheet1) - Got a table with several informations relative to a number of a house
House1 (Sheet2) - Want the information extracted from Main (Sheet1) to a table.
House2 (Sheet3) - Want the information extracted from Main (Sheet1) to a table.
So basicly i want to extract the info from Sheet1 if it matches the Number of the House on sheet2.
With Vlookup can only get a cell value as i am not an expert in excel
See the attachment
View 2 Replies
View Related
Aug 5, 2012
I have a table with different values on different dates. To get the newest value on the lastest date I'm using this formula; =INDEX(F5:K5;MATCH(9,99999999999999E+160;F5:K5)).
However, how I can get the second latest value? In this example I want a formula to to return the value previous price;
Latest observation
Latest observation date
Previous price
previous price change date
01.08.2012
02.08.2012
03.08.2012
04.08.2012
05.08.2012
06.08.2012
[code]....
View 8 Replies
View Related
Jul 5, 2013
This is my first post here!
The Question:
This is a hard one, I promise! Is there a way/formula/VBA to return multiple unique values from a table or from multiple columns with criteria?
My Objective
I want to create a table that has unique company names in the first column (done already) and all the connections that belong to a given company in the columns #2....#n. Like this:
Column1 Column2 Column3 ..... Column n
Company X1: Co. ABC Co.XYZ Co.Acme
Company X2: Co. SEB Co. Becme Co. Cecme
....
Company Xn: Co. X1 Co. ABC Co. Acme
I have a small sample that has 20.000 rows and 25 columns of data of 1946 unique companies downloaded from my schools database. This means that doing this by hand would take forever!
The data has many duplicates which makes this complicated. The first column shows company name and columns 2...n present connections. Like this:
Column1 Column2 Column 3 ...... Column n
Company X1: Co. ABC Co. XYZ Co. Acme
Company X1: Co. ABC
Company X1: Co.Acme
Company X1: Co. XyZ Co. ABC
In this example Company X1 is connected to companies ABC, XYZ and Acme. The formula should be able to filter/omit overlapping entries.
View 1 Replies
View Related
Aug 13, 2008
I'm trying to create a function in VBA,
I have a table of data and need to create a function which requires value (string), range and column number. This will then look for the value within the range and return the data for every match of specified value within the table.
I'm happy for the return value to be in the form:
val1;val2;val3;val4;...;val[n]
View 9 Replies
View Related
Jul 22, 2009
I am trying to return a value from table below (column 4) based on values in the 1st 3 columns. I have data with the 1st 3 columns but need to lookup each and return the 4th so I have tried some formula's with if and vlookup statements but keep getting lost....
View 9 Replies
View Related
Sep 24, 2009
0000AAAAABBBBBCCCCCDDDDD
1111SugarRaisinDirt
2222MilkWaterWaterFlour
3333FlourSugarDirt
4444EggsWaterFlourSugar
5555WaterMilkDirt
I looked for a way to represent my data in a cleaner way than this but couldn't find any tools....a point in the right direction would be helpful. I guess the old plug-in that converted Excel->HTML isn't available?
Column A is a finished product and B-D are the ingredients to make it however values in Column A are also ingredients (sometimes multiple times). I want a formula that searches for values in Column A within the table B1:D5 and returns an array, if possible, of the finished products where Column A was used.
For example, searching for Sugar would return "Flour, Water". Searching for Water would return "Milk, Eggs" (I'd rather not have Milk listed twice but beggars can't be choosers).
View 9 Replies
View Related
Jul 14, 2009
I have a pivot table which I am interested in filtering in several different ways. The table contains a category for AREA on the Y axis and BLOCKS on the X axis. When unfiltered the tables displays AREA 1,2, and 3 and has a full list of parts. I can then filter the table by AREA to look at one area specifically, and when I do the BLOCKS list shrinks to only those which correspond to that specific AREA.
My problem is that I want to be able to reference on another sheet the value that corresponds to AREA 1 Total regardless of whether the table is filtered to only AREA 1 or if it is unfiltered. I cannot just reference the cell because AREA 1 Total is in a different cell when unfiltered then when filtered. Is there any way to do this?
View 5 Replies
View Related
