I am currently building a data sheet using VLOOKUP. When there is no data to lookup it returns #N/A, which is fine. The problem is when I go to total these columns because of the NA it returns NA in the total box. Having tried the if isna formula I am now returning #VALUE!. I may be doing the IF ISNA formula wrong.
View 4 Replies
The problem I am having is that I would like to total using the VLOOKUP (or any other command, which i do not know off). Here is the scenario
I have two sheets.
In sheet 1 have:
Column A with the weeks (date).
Column B with numbers.
Column C with numbers.
In sheet 2 I have:
Cell A1 with the date (which I type in)
Cell A2 displays the number (according to the date that I have typed in Cell A1)
In Cell A5 I would like to display the total. If in Cell A1. I type 13/04/09 it would total 30 (06/04/09 & 13/04/09). I have attached a spreadsheet for reference.
I have 3 columns of data and am using an autofilter to sort it. At the bottom I have a formula =subtotal(9,cells requested) which magically shows me the sum of the data showing only (excludes all the figures which are hidden - it's fabulous). However I was wondering if it is possible to have the same formula but to count the number of things shown instead of sum and another to average.
View 3 Replies View RelatedMy company works 24/7 and with that some people on nights starting prior to midnight and then finishing after. Of course the "regular" formula works well in calculating between 00:00 onwards but I am getting an error when for instance the person starts at 20:00 and finishes at 04:00. I have tried numerous ways but still the error persists.
View 5 Replies View RelatedOn my spreadsheet I'm after a formula for cells E2 and F2 that I can drag down and it automatically populates the cells relevent to the day with the total hours for the Sessions (E2) and the total hours per day (F2). Hope this makes sense, but I'm sure you'll gather want I mean from the spreadsheet.
I have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?
View 9 Replies View RelatedI want to total column A & total column C but only if there is an amount in both cells on the same row
Can I do this with an array?
Do the columns have to be adjacent? I could make them if necessary.
Data
A C
11 12
21 0
0 17
15 15
11 9
in this example we exclude rows 2 & 3 from the total because one cell contains zero or is blank.
Total Column A = 37 (11+15+11)
Total Column C = 36 (12+15+9)
I'm currently working on a summary page for the defects in the building we are working on.
I've done up a summary page which reference's cells in other sheets which contain a TEXT(COUNT formula. When I goto SUM these referenced cells in my summary page I keep getting it returning '0'.
Example of what I'm doing:-
Defects Column in Summary Page
411 --> referenced from =Basements!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
56 --> referenced from =Level1!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
33 --> referenced from =Level2!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
When I go to do a simple SUM of 411,56,33 via =SUM(D3:D5) it returns a zero?
I've included a spreadsheet that show what Im trying end up with. Im looking to have a sheet that has a item indentification number along with a title and value. I would like to come up with a macro or formula that organizes the items by that identification number and totals them.
View 4 Replies View RelatedI work in HR MI and am compiling an equality spreadsheet. Out of everyone who is on the list I want to see how many men and women we have which I have figured out using = SUMIF(GENDER,"MALE",NUMBER) but from that I want to say theres 50 male in total and out of that 30 are White and 20 are Indian. Is there anything I can add to this formula so it looks at more than two defined sets of information??
View 2 Replies View RelatedI have a 1500 row spreadsheet that will change every month. It's set up in three columns Name, Amount, Total. The Total column is supposed to hold the total of all the rows with the same name field. I.e.
4445-8889 12
4445-8889 8 20
5598-7785 10
5598-7785 10
5598-7785 20 40
The problem is that the number of identical named rows is different for each name and changes each month. I want to build a macro that will out the correct total in the last column of the last entry with the same name.
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedI have tried nested ifs and vlookup to compare two sets of data and change the first set of data if it is than the first. But leave it alone if it either is the same or does not exist in the new set of data. It sometimes seems to work but i find it is not consistant. It looks simple but i think i am missing something.
if column A has identifiers and column B has results then it should work if the identifiers in column C are found in column A and it looks to see if column B and D are the same, then change B if different but leave it if either it is the same or not there.
A
code1
B
36
C
code1
D 33
On J7:J500 there is a list of names
James
Mark
Momeena
On A7:A500 there is a list of name with duplicates
James
Mark
Mark
Mark
Momeena
Momeena
Momeena
Mark
I need a vlookup
Range needs to be set from a7:a( a highlighted row)
Vlookup will cross ref values from J column against A (using the target range)
Then will add up column d that have the same name
So for instance
You have
A
James
Mark
Mark
Mark
Momeena
Momeena
Momeena
Mark
D
50
50
50
50
10
10
10
5
J
James= 50
Mark= 50+50+50
Momeena=10
I have attached a sample. I want to return a 'Team Number' to column C. The data is from another sheet, however in the sample I put it in column H and J. Should be simple VLOOKUP but columns won't match exactly.
View 9 Replies View RelatedI have a thread in here called "Inputing data values automatically based on data value in another column". I have determined that I need to use the VLookup function.
View 3 Replies View RelatedI have a problem to use vlookup in excel. I have an excel to run macro and other than i have two excel. One of them is list just a column which includes SAPkeys of people it looks like
SAP Key
xxxx
yyyy
xxxx
and i have another excel which i use as database. in this database sap keys of all people are written in "C" column. and there are other information about that person is written in "D", "E" and "F" columns.
what i want is bringing all information belong to sap key is written in different sheet. it would be like that after the macro run
SAP Key Name Location
xxx john london
yyy
zzzz hanks berlin
There is data which comprises of Part (#), Discount (%), Start Date & End Date. In this data Part (#) can repeat but Start Date & End Date periods will not overlap.
I want to write a formula which looks up into this whole data set and give me result as TRUE or FALSE and get Respective Discount (%) in another cell. True if for a specific Part (#) Discount (%) is applicable on Current Date.
Attaching sample excel file for example of data set.
I have the following sitation:
xls Sheet 2
ITEM EQUIPMENT
1234 a
1234 b
5678 c
5678 d
xls Sheet 1
PO ITEM EQUIPMENT
56 1234 **
99 5678 **
** in the equipment cell of sheeet 1 I want a drop down (data validation) with the possible equipment I can use forthis item only. I.e. for PO 56 Item 1234 only the equipment a & b should be in the drop down menu.
I have a problem with VLOOKUP not extracting the data I need from 1 workbook.
I want to populate 4 different sheets in the "108" workbook with data from the "1st" workbook. I've uploaded the workbooks to show the formulas I have already. The data in "1st" workbook changes daily and can have upwards of 2000 rows. I've simplified the list for the example.
In the "108" workbook I want to fill in the first three columns with the formula down to about row 500.
I've tried different formulas to do this using EXACT, MATCH, and VLOOKUP but to no avail.
i want to set vlookup but have spaces in the start of source and destination data is there any help for trailing this from vlookup. i have upload the sheet. i also tried vlookup("*"&.............) but not working
View 3 Replies View RelatedI am using the code below to put a data validation list into A2. How do I code in the vlookups into that so that a query can be looked up based on that value?
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,2)
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,3)
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,4)
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,5)
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,6)
I have put the spreadsheet here on box.net as it is too large to attach:
[URL] ..........
I have this spreadsheet with a list of products and their components in the sheet called "product_list" this information is downloaded from a plant pc in this format. to make it more readable and easier to edit i've made the "mix formula" sheet that using vlookup finds the product by product code and brings through only the relavant information. The problem is if you want to alter this information you have to try and find the relavant cell in the "product list" sheet. What would be easier is if you can edit the relevant info in the "mix formula" sheet and it would change the data in the "product list" sheet. Is there a way of doing this? I've attached the spreadheet with the relavant data to demonstrate.
View 2 Replies View RelatedCan I get VLookup to ignore certain data.
My situation is this,
Sheet 1 has
ColumnA Column B
Name Job#
Bob 1234
Bob 1235
Bob 1236
On Sheet 2 I have a combo box so I can pick any name from column A on sheet
one, then I want to use Vlookup, or whatever will work, to show me the Job
numbers that a selected person has worked on. So when I pick "bob" from my
combo box in A1, B1:B3 would display 1234, 1235, 1236.
I'm trying to get data from another tab, but only if it is greater than or less than a certain number. Here's what I'm using but doesn't seem to work.
=IF(ISNA(VLOOKUP(A13, 'Aim MTD'!A2:T63, 5, FALSE)),">=22.00%",VLOOKUP(A13, 'Aim MTD'!A2:T63, 5, FALSE))
A13 is targeting the name of the person
Aim MTD is the sheet tab the info is in
And I want to select the information only if the cell value is 22% or greater. If the cell value is less than 22%, I don't want it to read or to read zero. I'm not an excel expert (yet), but hope to be one day!
I have a table simmilar to this:
Reptile Dog Red
Reptile Tiger Purple
Mammal Tiger Stripes
Mammal Tiger Spots
Mammal Racoon Black
Mammal Cat Plaid
Mammal Dog Brown
Mammal Dog Purple
Reptile Dog Red
Reptile Tiger Purple
Tree Dog Orange
Tree Pine Green
I would like to use this data to populate within a seperate worksheet that reads:
Mammal, Tiger, Stripes
Mammal, Tiger, Spots
Mammal, Dog, Brown
Mammal, Dog, Purple
Essentially pulling all of one duplicate item within a column. The problem I ran into is when I run a vlookup within the entire table, it gave me duplicates.
Basically, it gave me...
Mammal, Tiger, Stripes
Mammal, Tiger, Stripes
Mammal, Tiger, Stripes
Mammal Tiger Spots
I know this is because of the array, just forwarding to the next item, and rerunning the lookup... since Mammal is not at the top... it has to wait till the array gets to the area of "mammal".
How do I create the list, so it will not create the duplicates... like in the example I gave.
I need to look up data from a different worksheet based on two criteria: (Main worksheet)
Company (A1), Fund Name (B1) and Stage (C1)
A | X
A | Y
A | Z
B | I
C | X
C | I
A-C are company names, I, X-Z are fund names and i need stage data
In (worksheet 2) I have the raw data, where the data for company, fund and stage are. I need to import the data for stage into the main worksheet given if company A matches fund X. I tried sumproduct but that doesnt allow non-numerical data.
I am trying to create a vlookup that will use data in another workbook. The workbook that should be used will change based on a cell in my current workbook (cell A2). So in cell A2 it will have Plan1, Plan2, etc.
This is what my formula looks like right now.
=VLOOKUP(A3,Plan1.xlsx!old, 4,FALSE)
Is there a way to make the table_array a formula so it will take cell A2 and string it together with .xlsx!old?
Message board virgin here, but I need help with an excel issue so excuse me if I don't follow protocol.
I'm using vlookup to return the salesperson by serial number, but run into a problem when my criteria has a duplicate value, but my answers does not.
Here's my example:
Serial#, Salesperson
123 Bill
456 Bob
456 Suzy
789 Bob
