I have a simple Staff Rote which is fed by data on Time Sheet. Currently it is working ok with two definitions;
T : Time
B: Break
If I introduce a 3rd and 4th definition like;
A: Leave
X:non working day
formule will get even more complicated.
Is there a way of simpifying this with a code and introducing the new definitions.
I am having trouble using an if statement with a vlookup.
The first calculation i am trying to do is calculate the cost of storage for the product, each product (3 of them) has it's own charging matrix which i have turned into a table on another sheet and therefore want to use the vlookup to return the correct values (meaning that i can change the underlying table and not have to change the formula each time a price change occurs), the sheet calculates the dwell time between entering and leaving, if dwell time is less than 7 days the charge is the number of days * products <7 days rate, if it is >7days then the charge is 7 days at the products lower rate and then the balance at the higher rate. I have a formula that is a longwinded way of calculating it for one size only but would like to add vloopup so that dependent on the units size the storage is calculated.
I have attached a sheet as an example, you will see the data table is quite large and as we consume extra services i plan to use a tick box to indicate use and then add this to a running subtotal again through an if and vlookup.
I'm trying to nest the following:
=IF(VLOOKUP($F6,'Dept location'!$A:$B,2,FALSE)>0,VLOOKUP($F6,'Dept location'!$A:$B,2,FALSE),FALSE)
=IF(VLOOKUP($H6,'Project location'!$C:$K,9,FALSE)>0,VLOOKUP($H6,'Project location'!$C:$K,9,FALSE),FALSE)
=IF(VLOOKUP($V6,'Dept location'!$A:$B,2,FALSE)>0,VLOOKUP($V6,'Dept location'!$A:$B,2,FALSE),FALSE)
To start with, not sure I have the ">0" part correct in the above IF statements. That expression is meant to be asking it: Is the VLOOKUP finding valid data (result #N/A)?
Next, comes the nesting part. If the VLOOKUP isn't finding valid data (result = #N/A), then I want it to move on to the next IF statement.
I have 3 different columns I want it to look at; hence, the 3 IF statements. In the end, if none of the three IF statements results in any valid data, I want it to give the result "Research".
I have an If statement I am trying to get to work but not sure of the flow. Each part works seperately but not together. What I need is if column E is blank leave blank, if cell DI2 is Yes then use columns DJ to DP for the lookup if DI2 is No than use columns O to U for the lookup. The first 2 lines are the individual formulas that work independently. The 3rd was my last attempt at combining them.
=IF($E21="","",VLOOKUP($E21,$O$5:$U$476,2,FALSE))
=IF($DI$2="YES",VLOOKUP($E21,$DJ$5:$DP$366,2,FALSE))
[Code]....
I have three named ranges that I need to do lookups for First I need the formula to do a look up at the HUMANALOOKUP range and then if a value is not found go to LABLOOKUP and then if not found go to CHCSLOOKUP and if not found return a blank. the column layout of each worksheet is the same so the column index number same throughout the formula.
View 2 Replies View RelatedI have an array (partArray) that contains a number of parts that I need to get values for. The part array is populated (Thanks Norie!) by bring in the contents of a cell and splitting them into array elements. I then run them through a trim loop to remove the white spaces. Up till here thefunction works fine.
I created a partRSL array to hold the matching value for each part ( I know i can use multidimensional arrays but i dont understand them yet and need to deliver a prototype to get some approval from senior mgmt ).
The idea is that as we run through each element of the partArray we will vlookup the corrseponding value and assign it to the appropriate element of the partRSL.
When I use the function below it returns a #value. I think that the element of the partArray i want to lookup is not being used as a string and so the lookup is failing.
As a test to see that this is working i have the function returning the value of element 0 in the partRSL array as it is distinct from the rest. (It should be returning a number (integer)
Function FindAlt(option_address)
' Initialise the array
Dim partArray() As String
Dim partRSL() As String
Dim i As Integer
'Populate Array by spliting the comma separated contents of the select cell and assigning each to an array element
partArray = Split(option_address, ",")
'This works fine
For i = LBound(partArray) To UBound(partArray)
partArray(i) = Trim(partArray(i))
Count = Count + 1
Next
I am trying to write a UDF function where I lookup if a value is on a range on different sheet. I would like to be able to write what I want the result to be in the function. I also hard coded that the function need 3 range and 3 results, but I know it is possible the make the number of component vary depending on what the user writes. I would like my function to do that.
Here is what I have written so far :
VB:
Function find_para2(var As String, rng As Range, result1 As String, rng2 As Range, result2 As String, rng3 As Range, result3 As String)
If WorksheetFunction.IsNA(WorksheetFunction.VLookup(var, rng.Address, 1, False)) = False Then
find_para = find_para & result1 & ", "
[Code] .....
I need returning a working hyperlink from a vlookup. I have a table with part #'s and hyperlinks. On another tab I want to type in a part number, have it search the table and return the working hyperlink. This will allow the user to just click it and go to that tab.
Here is my example spreadsheet : Book1.xlsm
I have a vlookup for one cell into another sheet however it retruns #n/a(cell A1) the cell adjacent is an if command (Cell B1) it reads if(b1>0,1,0) the only problem is if the cell (A1) will not return zero just #n/a how can I turn the #n/a into a zero? so cell A1 will return a zero and not #n/a
View 6 Replies View RelatedI have a list of cells and for each of them I preform VLOOKUP in a certain table.
Now, I want to sum the values all the VLOOKUPs without viewing the results of each VLOOKUP. In other words, I don't want a cell for each VLOOKUP result. I am only interested in their sum.
Is there a way to do this?
I want to vlookup one value into two different tables and return the 2nd column in the respective table it finds the value in. if it does not find the value in either table, return "". Currently I have the formula below, but it is not working 100%.
=IF(E6="Shipped","Printed",IF(NOT(ISNA(VLOOKUP(C6,Master!$GP$4:$GQ$5000,2,0)=1)),"Printed",
IF(NOT(ISNA(VLOOKUP(C6,Master!$HD$4:$HE$5000,2,0)=1)),"Ready","")))
Any recommendations on vlookups being able to recognize a value that does exist within a table? It returns an N/A and the formatting is the same for both references.
View 9 Replies View RelatedI am trying to write a formula that will give more than 1 return for a vlookup statement.
The info i am looking up is sku code (col a) and expiry date (col b).
There are more than one entry for some skus , but I am only getting returned the first expiry date.
I know how to do a vlookup and I know how to do an if statement. I also know how to do an IF(ISERROR or IF(ISNA ....
However, I can not get this to work:
=IF(C73<7,VLOOKUP(D73,'Package Mix'!$G$39:$H$45,2,FALSE),IF(C73<13,VLOOKUP(D73,'Package Mix'!$G$6:$H$36,2,FALSE),IF(C73>23,VLOOKUP(D73,'Package Mix'!$G$48:$H$56,2,FALSE),"")))
I can not wrap my head around how to make this work w/ IF(ISNA ....
Also, why must you use < or > .... I actually want the numbers to be 6,12, and 24... However, it seems when I use an equal sign it fails.
I am trying to create an asset management database using excel, and would like to be able to add custom notes to each entry (index) so as time goes on, I can refresh my memory what we have done with a particular item. I have attached the current file I have created.
Essentially, when I use the GREEN drop down menu in the top right, I want it to display the notes/comments that I enter in the rightmost column of the data, as the attached file illustrates.
However, I want to be able to hide that rightmost column and be able to edit the data from the new area (white space) it is being displayed in, however as I have it set up right now, if I edit the cell, it edits the forumla and poof goes all the comments and the formula becomes messed up.
I have a table that has the data below
Month Jan Feb Mar Apr
Brand 0 1 2 3
I want to do a vlookup with match to find the month and then return the value of a rolling 3 months.. So if i look up Apr i want to look at the rolling 3 prior months average ie, average of (Jan,feb and March). i have the vlookup formulas working, its when i include the multiple vlookups (Match -1, -2 etc, the average is calculating the blank cells for some reason..
I have done a V-lookup that auto populates my project report doc with different lead times/dates depending on the product selected. Problem I have it that the dates being pulled through are for all calendar dates and not just working week days.
I know you can select a selection of dates and fill as week days however all of the dates on my primary sheet either have the project start date minus lead time formula or the vlook up formula in it. Filling week days overwrites any of this.
I'm attempting to work with a v-lookup that will have multipe results. In column A of my spreadsheet are invoices numbers and in column B are account numbers. An invoice can have multiple accounts. For example, there are two lines for invoice "ABC", each with a different result in column B (see below)
Invoice Account
ABC Cash
ABC Receivable
My goal is to have the vlookup bring in both values, but in separate rows (see below)
Column A Column B Column C
ABC Cash Receivable
I'm having issues with a formula that I am using to add three vlookups together. The problem is that there may or may not be data in one of the columns I am looking up with the vlookup. I am looking up hold times for reps each week then adding them together to get a monthly sum. This formula works when data is available for all 4 weeks. When I have a rep that started in the middle of the month there isn't data for all 4 weeks so I want Excel to just treat that as a 0 or Null, Currently it just provides a 0.0 for the whole thing instead of adding what is there.
=(IFERROR(VLOOKUP([@Name],Sheet2!A:H,2,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,4,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,6,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,8,0),0))
I would like some conditional formatting to happen when the vlookup is true AND false.
The lookup is looking for a number, if the number is there, i want it to colour the background RED. When it is not there (the vlookup says #N/A) i want to have it coloured YELLOW.
Basically i need to type in a formula in sheet 1 under each of the months that will match the account name and location from B1 and C1 to the data range in sheet 2 (Columns B and C) and return whatever value is listed on sheet 2 under column E. My gut tells me I need to use multiple look ups but I've tried this and got numerous errors.
View 9 Replies View RelatedI have the following formula that works great when everything is in a straight range:
=IF(B60=1,INDEX($A$3:$A$22,MATCH(B59,B3:B22,FALSE),1),"No Skin")
However I am trying to make it work where the data is broken up into multiple sections, for example instead of the
range being a3:a22 it would be a3:a6,a10:a15,a:20:a22 etc....
I tired this but it gave me an #N/A
=IF(B63=1,INDEX(($A$6:$A$9,$A$20:$A$23,$A$34:$A$37,$A$48:$A$51),MATCH(B62,(B6:B9,B20:B23,B34:B37,B48:B51),FALSE),1),"No Skin")
The will eventually end in a series of pivottables. I am currently at the step where I need to add vlookups. This is for an inventory management report that averages 20,000 rows and 20 columns of data. I add a few columns (using vlookups) to increase visibilty to the operations dept.
The first thing I need help with is creating the VBA language for using a vlookup table from another workbook that pastes the vlookup formula every cell to the bottom of the data
Then I need to utilize an IF formula for one column that says IF c2 is blank use vlookup-formula1, and IF it is not blank use vlookup-formula2, and then paste at every cell all the way down.
I hope someone can help, and I hope I didn't write too much to where everyone will skip pver this thread.
I am currently to this state, and ready to continue.
--------------------------------------------------
Sub OHRTODDI1()
'
' OHR1 Macro
' Macro recorded 7/22/2009 by iahopbxm
'
' Keyboard Shortcut: Ctrl+o
'
' Sort and Add column Headers
Cells.Select
Selection.ColumnWidth = 9.43...........
I have two lists of IDs in 2 columns. I want to sort of do a vlookup of each row in one column to see if it is in the next column, and I want to sum a third column that goes along with column 2 based on whether the same IDs from column one is in the second column. And I want to do all this in one cell. For example:
A B C
TestA TestZ 10
TestB TestY 20
TestC TestB 8
TestD TestA 7
I need one cell to calculate for me that based on the IDs in column A, column B matches with the IDs TestB and TestA, therefore giving me the sum of 15 (8+7). Doing a vlookup and auto-filling down and then summing up is not an option, so I'm trying to calculate it within one cell if possible. And I can't get sumproduct to work because the matching IDs are not necessarily in the same rows.
I am trying to use a vlookup from input from a combobox. In other words, if a user selects "Los Angeles" from a combo-box titled "city", I want the state of California to appear in a cell labeled "State" (A3).
View 2 Replies View RelatedI have 3 sheets in my workbook, DataA, DataB, and DataC
Data A contains:
ID_customer
and 4 variables A-D
DataB contains
ID_Customer
and 4 different variable W,X,Y,Z
Data C contains
ID_customer
and other 4 different variables
Request: I need to analyses the relationship between the variables using a Pivot table.
My thoughts so far: I think that I need to combine all the information onto one sheet first and then use the pivot table function, but its currently unable to combine onto one sheet, due to Id_ customer numbers appearing several times in DataB and DataC (and not always the same number of times in both), I need to retain all the information from DataB and DataC
I have attached a sample of my data : DataA.xlsx
I am trying to write a formula that is based on a vlookup that will return multiple values. Attached is a spreadsheet that is an example for what I am trying to do. On the download tab it is an example of a general ledger. On the total tab I want to enter a GL# and have it list all the entries for that GL number. I know I can try an array, but I have not been able to figure that out.
View 6 Replies View RelatedI have a number of Vlooks set up to reference cost information set up against Names in various workbooks.
The problem is wherever a name is mis-spelt then the lookup fails to match the values.
Instead of doing a long long formula that is =VLOOKUP(x1,....)+VLOOKUP(x2,.....) is there a faster way of doing it in the one formula?
View 7 Replies View RelatedI created a macro in an Excel workbook which uses MS Query to return 2 tables of data, and insert them into the workbook. Then, another macro uses a VLOOKUP to enter the values from the tables into their respective rows in other worksheets. Everything worked like a charm, but when I duplicated the workbook (copy and paste) in order to use it for other companies, the VLOOKUPs now return an error (only in the dupe workbooks; the original still works fine). After troubleshooting the issue, I determined that the VLOOKUPs cannot "see" the tables - almost as if they have become invisible. I even tried deleting the query macro from the dupe books and recreating it, but still the data is invisible to my VLOOKUPs.
View 3 Replies View Related