Multi-level Lookup
Oct 24, 2007
I'm looking for a formula to pull the correct piece of data from one worksheet and display it in another. I have tried VLOOKUP, but it is only returning values from the first instance of my lookup value. Below I have included a sample of what the data from the first sheet looks like. My lookup value is entered on Sheet2 in cell A1. The formula I have used is as follows...
=VLOOKUP(A1,Sheet1!A2:D13,4,FALSE)
The result this would return for a value of 111111111 is 15, but I'm looking for one more level of depth in this lookup, by being able to pull not necessarily the cost of the first code, but of a specific code......
View 3 Replies
ADVERTISEMENT
May 15, 2008
I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.
Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.
View 9 Replies
View Related
Apr 19, 2012
I have the following function. the problem with this excel is i give it "C:" as directory, it search only one level of sub folders i.e it will search the filename in
C: est
C: est3
It does not go deeper. i.e it wont look in folder that is for example
C: estanotherlevel
C: est3anotherlevelmylevel3
How to modify my function so that it goes to the deepest folder. is there a way to do it?
Function GetFile(directory As String, filename As String)
Dim FSO As Object
Dim fldr As Object
Dim subfldr As Object
Dim file As Object
Dim fullname As String
[code].....
View 2 Replies
View Related
Nov 19, 2009
Been trying to figure out whats wrong with my formula. The 4th column doesn't seem to produce the intended result. I have attached here for reference.
View 4 Replies
View Related
Feb 9, 2010
create a multi level BOM in excel:
i have a formula
A=a+b+c+B
B=a+d+e
if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.
also i may take 50% of A +50% of B the resulting formula must appear.
i attached an exemple file.
View 14 Replies
View Related
Mar 13, 2014
So our company gets Material Statements which basically state what chemicals and how much of said chemicals go into producing X item.
I need to figure out the percentage of MASS for each chemical.
So data might look like this:
Total Weight of product: 500 grams or mg, etc.
Sub-Item - Contact - 450 g
Material - Copper - 425 g
Substance - Nickel - .05 g
Substance - Copper - 422 g
Substance - Lead - .02 g
Material - PVC Insul - 5 g
Substance - Polysomethings - .5 g
Substance - Red Dye - .4 g
Substance - Plastic - 4.1 g
So basically the sub-item line is 450/500
first material line is 425/450
substance is .05/425
the substance lines use vlookup to find the material line to divide by. formula i'm using is below:
=IF(B2="Sub-Item",I2/$M$2,IF(B2="Material",I2/VLOOKUP("Sub-Item",$B$2:I2,8,FALSE),IF(B2="Substance",I2/VLOOKUP("Material",$B$2:I2,8,FALSE),"n/A")))
B2 is either sub-item/material/substance
Col I is the mass of that line
M2 is the total weight of the item
Formula works until you get to the Polysomething lines in the above example data. instead of taking the material weight from PVC Insul, it takes it from Copper.
View 1 Replies
View Related
Feb 25, 2010
I need to set up an excel sheet with dependent data validation that is multiple levels deep.
ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.
Is this possible?
Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.
View 9 Replies
View Related
Dec 23, 2008
I am looking for some assistance to make pivot tables with multiple data values against a control item. A sample excel file is attached herein with. I am quite new to pivot tables was unable to find out a solution self.
View 2 Replies
View Related
Jul 24, 2009
Below is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.
Would a macro make the task more automated?
View 9 Replies
View Related
Feb 16, 2012
I am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.
View 3 Replies
View Related
Jul 31, 2008
In the attached file I managed to sum up the Quantities per Material & Category. The Original DATA is presented in columns A:E. The requested results/Formulas are at Cells B2:B4. My Question is very simple: Is there a way to sum up the Quantities WITHOUT the Help-Column G !? If so - how ?
View 9 Replies
View Related
Jun 16, 2008
I am currently using the formula below to lookup a name and its corresponding code. I type the code into cell A13. The name is fixed at B1. This formula then finds the name (B1) in a table and matches the column header of the code which I type into A13 and returns a value. This allows me to type in different codes and quickly see the value corresponding to the name. I also want to be able to type the coding into A13 and be able to see a listing of all of the names attached to that coding.
VLOOKUP(BETA!B1,MAINTABLE,MATCH(BETA!$A13,MAIN!1:1,0),FALSE)
View 9 Replies
View Related
Dec 8, 2007
I have a rather complicated question, complicated for me. I have one workbook with 24 worksheets. The first three worksheets are names of people, ie kim smith, jack white, bill blue. The next 23 sheets are held in reserve for new people and are currently labeled isr 4-23.
I have used your site to get to a point where I can generate a cell to reflect the tab name. I have used these formulas to accomplish that:
=CELL("filename",'ISR 4'!$A$1)
="'"&MID(F6, FIND("]",F6)+1,256)&"'!"
This generates 'ISR 4'! in the destination cell. I have taken this list of sheet names and crated a dropdown list. Now I would like to apply two tests to this list as well as another list.
SheetnamesWeekofYearHits
Depending on who is picked in the sheetname list and depending on what week is picked in the week list I want to go to the sheetname tab, that week and grab cell b1 or b2 (week 1 b1, week 2 b2 and so on for 52 weeks)
View 9 Replies
View Related
Dec 19, 2007
I setup a Vlookup formula (in sheet 1) to find the figure in column C (in sheet 2) based on the company name and branch name but the result is incorrect.
View 3 Replies
View Related
Jun 12, 2014
I make budgets that have line items that have standard price based on 2 factors: a description, and a number. I want to keep these items in 2 different cells. I also have different clients, and the costs are different. I could simplify so that I can use a VLOOKUP with HLOOKUP and separate the clients but I would prefer not to.
Screen shot 2014-06-12 at 12.36.47 PM.png
View 1 Replies
View Related
Apr 27, 2012
I'm trying to create a formula that looks for matches in 3 ranges and exports a specific value dependent on if there is a match or not into a new cell.
IE:
If any value in Column A = D1 AND any value in Column B = E2 then enter corresponding value from Column C into F2, if not then place "New Data" in F2.
If it makes it easier to conceptualize: Columns A,B,C are in one worksheet. Columns D,E,F are in another worksheet.
Worksheet 1
Column A Column B Column C
1 Frog Eye Head
2 Rabbit Eye Head
3 Cat Nose Head
4 Horse FR Hoof Leg
5 Dog Nose Head
Worksheet 2
Column A Column B Column C
1 Horse Eye "New Data" (no match from column A or B)
2 Cat Nose Head
3 Dog Nose Head
4 Rabbbit FR Paw "New Data"
5 Horse FR Hoof Leg
I need a Formula for Worksheet 2, Column C that searches Worksheet 1 Columns A & B and places the corresponding match from Column C (if there is a match).
I've tried Lookup and IF/AND formulas to no avail.
View 9 Replies
View Related
Mar 20, 2014
What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..
and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.
and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..
View 3 Replies
View Related
Dec 20, 2013
I am using a list like this.
A
B
C
D
1
Teacher
Name
Color
Size
[Code] ....
I need formulas that can evaluate the table above and provide the information below. If there needs to be multiple steps/formulas, I'm okay with that.
Two sticking points, BOLD - be listed twice with the same teacher. I don't want them counted twice in the "Size 3" column. BOLD & italicized- same student could be in two different teachers' classes. They need to be counted under both.
Teacher
Red
Green
Blue
Size 3
[Code] ...........
View 5 Replies
View Related
Sep 4, 2008
a lookup and match of two values.
For example, in Sheet1 I have a unique transaction reference number and a £ value.
eg. 60231568 £38.05
In the next sheet (Sheet 2), I have a summary of data, and I need to return the company name...
60231568 £38.04 Company X
60231568 £15.12 Company Y
60231568 £10.11 Company Z
60245871 £105.11 Company Q
The look up needs to match trans no, amount (within 3 pence threshold) and return in this example company X.
View 9 Replies
View Related
Dec 11, 2007
I consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.
View 5 Replies
View Related
Dec 18, 2008
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system.
I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip
Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST:
SCH0001, Lindsey Schubert, 75230
SCH0002, Thomas Schoembs, 53132
ADA0001, Samantha Adams, 28205
...
What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary.
Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
View 3 Replies
View Related
Dec 13, 2006
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)
it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!
i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).
any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!
should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
View 8 Replies
View Related
Oct 24, 2007
I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex)
On the report I have values 123A and 1234A
On the data tab I have table that has.
Ref Tot Value Desc Value1 Value2 Value3
1 123A Widgets 1 2 3
2 123A Widgets 4 5 6
Ref Tot Value Desc Value1 Value2 Value3
1 123B Nuts 7 8 9
5 123B Nuts 1 3 5
Ref Tot Value Desc Value1 Value2 Value3
7 1234A Bolts 2 4 6
11 1234A Bolts 3 5 8
So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.
View 14 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related
Jun 30, 2014
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
View 5 Replies
View Related
Jul 16, 2013
Let's say I have one column of;
1
2
3
4
5
6
7
8
9
What is the most efficient way to change this into '3-columns & multi-rows' like this?:
1 2 3
4 5 6
7 8 9
The actual list is a lot longer and numbers are not in order.
View 7 Replies
View Related
Aug 21, 2013
I have multiple rows within a cell separated by Alt+Enter, and would like to combine them as follows:
Desired Result
First
First
Name
Name
First Name
First Name
View 2 Replies
View Related
Jul 24, 2012
How can I obtain the sheet-level name through VBA? I'm not referring to the sheet (tab) name but the name as defined though Insert/Define or Insert/Create, etc .
I know the way to get the cell level name goes something like this:
MsgBox Sheet1.Range("A1").Name.Name
But i don't know the correct syntax for sheet level.
View 4 Replies
View Related
Feb 10, 2009
I have this memory game I want to set out on the computer. In table A, Row 2 (B2:E2) I (Moti) start off being the first player, where I place a series of different items. (In Example is given letters but can be kitchen article food and so…. Separated by coma) these Items are shown to player Jack, for a few seconds, so he can memorize them and Answer in (B3:E3) ....
View 9 Replies
View Related
Sep 22, 2009
I was wondering if it was possible to do a SUMIF function depending on the indent level, e.g. sum all of the values which have an indent of 2 in the row headings.
View 2 Replies
View Related
