Matching Value To Column And Row Dependency List?
Jul 31, 2013
I've been struggling with dependencies, so I have two sheets.. the first sheet contains a simple table:
EmployeeUsernameStatusStatus Note
Alex
Josh
Sarah
David
This is the second table:
Employee Username ActivePassword Change Days Since Change
Alex al2 Y Y 20
Alex alex Y Y 11
Alex aa2 N N 0
Josh josh2 Y Y 10
Sarah ssarah N N 0
Sarah sarah Y Y 1
David David Y Y 4
The problem is in the first table as I don't know how to populate the "Status Note" column which depends on these two columns The column Username which is a dependent list from table 2 that contains the username associated with each employee selection The column Status which is a dropdown list that contains (Active, Password Change, and Days Since Change)
For example if the following is selected for user Alex: al2 and Active how can I make Excel display Y in the status note column
EmployeeEmployee UsernameStatusStatus Note
Alex al2 Active Y
Josh
Sarah
David
View 3 Replies
ADVERTISEMENT
Aug 16, 2008
I'm a complete beginner when it comes to excel, so please bear with me. I am trying to make 1 list bring up a varrying 2nd list ie:
list 1 contains List 2 Contains List 3 Contains List 4 contains
Car1 Option 1 Option 4 Option 7
Car2 Option 2 Option 5 Option 8
Car3 Option 3 Option 6 Option 9
My question is this: How can I make it pull up a drop down list 2if I choose Car1 and drop down list 3 if I choose Car2 and drop down list 4 if I choose car 3. I need it to take only 2 columns? I read a different post about dependencies, and it was close to what I was looking for however it populated a column, instead of giving a 2nd drop down list.
View 3 Replies
View Related
May 21, 2013
Basically I would like to copy rows where the name of the person (in Column A) matches a separate this.
E.g.
Name
DOB
Peter
30/9/1956
Jim
09/1/1923
Paul
01/2/1966
Lookup list:
Peter
Jim
In Sheet2:
Name
DOB
Peter
30/9/1956
Jim
09/1/1923
Note that there are many names (this is just an abstract) so I think it requires a macro.
View 7 Replies
View Related
Sep 21, 2009
I have an excel document that is processing some amount of data and turn it into daily reports. After I make a report I use macro to separate it from the document into a new sheet and save it separately.
The problem is those separate reports still remain dependent on the main excel document where they were created. As the data changes in the mother excel file, it updates all the old reports on the computer and basically I loose the old data.
View 2 Replies
View Related
Sep 2, 2008
I am trying to build a worksheet that is sort of a Project Plan. Why am I not using Project - simple - not everyone in my company has Project, and exporting in Project 2007 is just nasty!
The issue can be described with a simple example:
Imagine the sheet with the following columns
Task Ref - Task Description - Dependency - Start Time - Duration - End Time
Duration is in Minutes, End time is calculated as a formula adding the Duration to the start time using the TIME function.
Dependency contains the Task Ref of the task that must complete before the task in question can start. I would like to have a formula for start time, that pulls in the value for end time of the task identified in the Task Ref column -
View 9 Replies
View Related
Jan 29, 2010
I have written a custom function to be called from worksheet cells. The function is basically a wrapper function for VLOOKUP on a single table. It makes cell formulas shorter, easier to read and self-documenting. Here's a much simplified version of the function:
View 3 Replies
View Related
Jun 11, 2013
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
[URL]....
[URL]....
I am aware there are people with more pressing problems than computer games and as such
View 7 Replies
View Related
Jan 27, 2014
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
View 6 Replies
View Related
Jun 7, 2014
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
View 2 Replies
View Related
Dec 20, 2013
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
View 9 Replies
View Related
Mar 27, 2014
I am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies
View Related
Apr 10, 2014
I have a list for example...
Ian
Andy
John
David
Ian
Andy
John
David
Max
I want to search or filter the list and find the only non matched record in the list and put this in another sheet ie max as this is the only unique name in the list, all the others are matched. Everything I have tried eg filter for unique records returns ian andy john david max, which is not what I want.
View 4 Replies
View Related
May 20, 2012
I am trying to automatically populate all the values matching a certain criteria. i.e.
I have following Data
CodeColourUse
1Red Dress
1Yellow Car
1Blue Paint
2Blue Car
3Blue Dress
3Red Paint
3Yellow Car
Now all in other sheet is to , if i just change a write a code , only related entries to that code must populate.
For example in Sheet 2 if i enter code = 1, following data should populate
ColourUse
Red Dress
YellowCar
Blue Paint
if i enter code = 2, the following data must populate
Colour Use
Blue Dress
Red Paint
Yellow Car
I have used combination of IF and VLOOKUP function but it didn't work well.
View 3 Replies
View Related
Apr 20, 2006
I have run across a problem that I am unable to solve with a combination of index, match and countif funtions. What I am trying to do is have the formula return disctinct symbols. I have a list of currencies on one sheet: audusd, eurusd, usdcad etc, more than one from each. Id like to return one from each onto another sheet...
View 9 Replies
View Related
Apr 28, 2009
I have made a spreadhseet that track what games an official referees (sheet 1), what games they are touch judge 1 for (Sheet 2) and what games they are touch judge 2 for (sheet 3).
Sheet 4 then adds all these up for me and tells me as an good approximate estimate how much each official should get paid in way of expenses.
Simple enough so far.
I have a list of 3 letter codes on sheet 4 for each official that I put in the matrix of games. It's using these 3 letter codes that it adds it all up. This works fine
How can I get it to tell me if any 3 letter codes entered into the matrix (Sheet 1!D4:CD82) Do not match my list (Sheet 4!A5:A71). (an error detection system if you like).
To make it more interesting I would like it to ignore blank cells (games not played yet).
I'm not bad on excel formulas, but I can't get my head around how to do this.
View 6 Replies
View Related
Sep 25, 2011
I have a list that looks sort of like this:
Last Name | First Name | Date | Service Day
Smith...........John....................Monday
Jones............Alex...................Thursday
etc..
This list goes on down for like 50 cells. Anyway, I'd like to make a new list based on Service Day so that I can organize the names under the service day so it should look something like this:
Monday | Tuesday | Wednesday | Thursday | etc..
Smith, John..............................Jones, Alex
etc...
I tried using the vlookup function but then it confused me when i also needed to match the names with their respective service days.
View 7 Replies
View Related
May 21, 2009
Im hoping there is an Excel Formula (not VBA Code) solution to this problem.
I understand how to use the Substitute command in Excel, but I would like to be able to provide a list of words and have the Substitute command use that list to replace every occurrence within a given cell with a blank ("").
The twist to this is, that the List of Words will be Dynamic, and thus the formula will need to account for that.
NOTE: the formula should NOT replace parts of words, e.g., if the List Word is "can" and the cell to be evaluated contains "candle" the formula should NOT replace the "can" in "candle" with "". Only whole word matches should be replaced.
View 9 Replies
View Related
Oct 3, 2007
Creating a formula to match two columns (D & H) and if there's a match, I wnat it to produce the value that's in column E, in column J (where the fomula will go).
View 9 Replies
View Related
Sep 21, 2013
I want to find the corresponding Disc Codes from a list and copy them in the DiscName column in the summary sheet. some lab names will have more than one Disc codes so when I run the macro it should bring up all the relevant Disc Codes matching with the Lab name to DiscName column.
Col 1 col 2 col 3 Lab name Disc Name(say abcd) xxxx yyyy zzzz ppppand
The list looks something like this.
Col 1 Col 2 Lab name Disc nameabcd xxxxxabcd yyyyyabcd zzzzzabcd pppppbcda
qqqqqbcda rrrrrbcda iiiiibcda jjjjjbcda kkkkk
View 1 Replies
View Related
Jun 19, 2014
I have a large sheet of data I'm trying to sort through. Each row of data has a column that contains a long string description. Each of these strings contains a three letter codes in all caps that I want to be able to pull out. I have a seperate sheet that has all of the possible three letter codes in one column. Is there a formula that can compare each string of text to this list of three letter codes and if it find a match put that code in a column on the original sheet?
Doc Number
String
Code
33333
This is an example string of text. An example three letter code would be HCB. The location of the code within the string varies.
HCB
33332
This is an example string of text. An example three letter code would be CDQ. The location of the code within the string varies.
CDQ
33331
This is an example string of text. An example three letter code would be RCA. The location of the code within the string varies.
RCA
Codes
DCP
HCB
RCA
CDQ
LCP
DCA
SCR
View 2 Replies
View Related
Jan 8, 2008
I have two worksheets: A and B.
Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B).
Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated.
Any particular Issue# field may have several issues in it, delimited by a comma.
Program is a program associated with the issue and this column also contains duplicates.
Worksheet B:Contains 1 column: Issue# (Col. A)
This is a unique list of issues#'s.
All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues.
A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list
There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about)
I really hope that makes sense, but if not...
Here's the best example I could come up with: ....
View 9 Replies
View Related
Jun 16, 2009
I have Workbook A populated with skus from a report and I want to remove all rows in this workbook that do not have a matching sku in Workbook B. Each workbook contains only one sheet and the skus are in column 1 of both workbooks.
Also, I am looking for a good resource/tutorial for working with workbooks, worksheets and ranges within. I don't anticipate this being the last time I will be confronted with this and would like to learn more.
View 4 Replies
View Related
Dec 15, 2013
I have 2 lists. One list consists of the names of agencies as they would appear in our company's data base. The other list is the official business names. I would like match our company list with the official business name list by matching at least 2 words from the name with a name that shows up in column C (Official agency name).
If possible, I would like to ignore preposition and article words since they are used often in agency names.
I am looking for a formula to do this
View 3 Replies
View Related
Jul 10, 2009
I have a problem with excel matching fields. I have a list of cities in column A, a list of state in column B, and then on worksheet 2 i have another list of cities in column A and states in column B. I then have dates in column C on the second worksheet.
The problem is that the list on worksheet 2 do not repeat the names of cities and states but on worksheet one there can be repeats.
I wanted to know if there was a command so that if Column A and Column B on worksheet 1 matched Column A and Column B on worksheet 2 then excel will copy the date in column C on worksheet 2 and paste it onto column C on worksheet 1.
View 6 Replies
View Related
Apr 1, 2014
I'm currently having a hard time creating a formula to verify if the contents of 2 cells match.
Example from Spreadsheet -
Column C: MATCH / NOT MATCH
Column D, Row 4: MSG
Column E, Row 4: SMSG
When I attempt to create a formula for Column C, it registers the "MSG" within "SMSG" and lists the result as "MATCH".
View 14 Replies
View Related
May 12, 2014
In the attached example I paste in a report from another system into Sheet2 (rough data layout in example).
In column B in Sheet1 I have tried using Match and Index formulas to try and return only the Local Shipment figures from on Sheet2 for each staff member.
The issue i'm having is that the data in Sheet2 can change based on how long the report is run. There is also no way I can change the way the raw data is set out in Sheet2 as it's an external system.
Basically i'm looking for a formula that isn't locked down to certain cells and can work with data moving up and down columns at different stages. The key data never changes from column A and F in sheet2. But what does change is whether each person has one or both of Sales & Local Shipment data in the reporting. This obviously changes based on what is completed each day.
So I need a formula that matches the persons name in column A in Sheet1 with Column A in Sheet2, then looks for the first mention of 'Local Shipment' below it and returns the cloumn F value which is in the same row as 'Local Shipment'.
View 3 Replies
View Related
Feb 21, 2009
I've been trying to find this on the web but haven't a clue now, I have a database query that I am pulling from an access database and now I am trying to match the last four characters of a referral string to a current location, let me show you what I mean. Example,
A(location string) B(current location) C(last location)
car/kitchen/house kitchen NO
kitchen/garage/car car YES
kitchen/car/garage car NO
I want to write a function in column C that matches the last four characters of the location string (column A) with the current location (column B) , and if they match have a YES in column C, or a conversely a NO.
View 6 Replies
View Related
Jul 7, 2011
Table 1
Code:
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June
[Code]....
I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:
AMWG16=01_0211-20
AMWG16=01_0212-16
and these entries will appear multiple times as well
AMWG16=01_0212-16
appears twice and the data is different and will need to be added up
How can I:
1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"
2. Take all the data that has been added up and combine the statistics, for example,
when its all done it should show
AMWG16=01_0212-16
in the first column, and then all the statistics from the two tables in the columns to the right.
I'm guesssing...
Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:
AMWG16=01_0211-20
Shows up three times, you'll need to sum up all the values and this needs to be done on both tables
THEN
You can combine the data
I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together
View 4 Replies
View Related
Mar 28, 2012
I m trying to match the values based on the Coulumn B
[IMG][URL]....
;base64,iVBORw0KGgoAAAANSUhEUgAABVYAAALYCAIAAAAYRj5jAAAgAElEQVR4nMy9Z1RU+Z73Oy/uWs9aM
/eZmTtzn5nT3doGoNLOu3KmCEXOQUURUcCAomIgmXOgDYCK5JyhcoQiQ5HNWcE2dNt9uo8dTve
ZM3PCfbF3RYJo95m5rs+qVb0bpMoO8P3s7+/3/zvrwYOL5cCB4dnk5bmTm2tnKCeHYDAnZzA7m2AgK2tg
/347/fv29e/b1793bx
[Code]...
View 3 Replies
View Related
May 7, 2012
I have an application that on the "Main" sheet, is to extract two numbers then search for them on my "Listpoint" sheet and finally return the text to the right of the search data (e.g. K3)
Working left to right - the user pastes upto 12 lines of code into C3-C22. Formula in E3 extracts object Nos. Formula in K3 substitutes "first" number if it is a zero (with number from A3).
Left to do - Uses data (H3) to search Listpoint sheet colums C and B for a match. then returns text from Column S.
Note Listpoint has 1000 rows to search.
Main
ABCDEFGHIJKL1 2Osn Number Prog Point Text Extracted
ObJNos Zero replaced Name of Object 1 38 10 IF POINT 0|199 ON OR POINT 8|191
ON THEN RETURN FALSE 0 1998 191 8 1998 191
Example found Text 4 20 IF POINT 0|106 OFF THEN RETURN FALSE "HOLD OFF"
[Code] ............
View 9 Replies
View Related
