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 RepliesI 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)
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......
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)
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 RelatedI 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
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.
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..
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] ...........
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.
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 RelatedI'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.
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?
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.
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 RelatedI 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.
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.
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
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies View RelatedI want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies View RelatedI'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".
My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies View Relatedhere is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
I want to sum data in COLUMN C that is related to COLUMN B if the COLUMN F is blank.
View 10 Replies View RelatedI have to have to have 2 criterias for it to pass before the items are counted
I have 2 columns of data.
1 being a list of clients names, and 1 being if the Connection/Abandoned
Client Column is colum "F"
Connection/Abandoned is colum "J"
I need to count how many Connected and how many abandoned for each client
So if "F" equals "BPS" and "J" equals "Connected", then count.
I have a spreadsheet which have data auto inported. (thanks to previous help on here). I have now come up against the next problem.
I wish to make the mark up variable depending on 5 options (workings I13;I17)
the choice is selected in column b after the data has been inported
then down on Rows 75 - 79 i have the totals of my choices.
Unfortunatly i do not know how to do a variable sum
I have attached a copy of the workbook.