I need to find a formula to show the time allowed for each person, now they recomend combining 'if' with 'lookup'. now I may be having a blonde moment but I have tried to figure it out for a while now and i am stuck.
View 14 RepliesHere's my formula that gives me an answer of zero.
=IF(IF(L14="L",(LOOKUP(B14,data!Y1:Y14831,data!AO1:AO14831)),0),IF(L14="L+R",(LOOKUP(B14,data!Y1:Y14 831,data!AQ1:AQ14831)),0),0)
L14 is a dropdown box, selection is L or L+R
The L true result is 30 and L+R result is 60
It should be possible to combine these should it not?
I would do a screen shot but it's quite huge.
Am trying to figure out the best way to combine into 1 cell an ISNA(Match... and a Lookup array that will run if the ISNA(match ... returns a false value.
Right now it's running in 2 separate cells ISNA(Match ... and then an if statement that if the ISNA(Match returns false, that the look-up runs, if it doesn't return false, then the value is 0)
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 have some code where I use and statements, as follows:
View 4 Replies View RelatedI want to see if a goal has been met and get a "Yes" or "No" so I wrote
=If(A1>=A2),"Yes","No"
But on months not worked where goal and production both equal 0 then I am getting a return of Yes. How can I say is A1 >= to A2, but if both are 0 return N/A?
I am having problems combining AND and OR into a single statement. To start I have a statement =IF(AND(B978="P",R978=0,S978=0),"processed",""). If B=P and R=blank and S=blank then display PROCEED else make blank.
What I want to do is check if S is blank or "N" but when I try to add an OR I get error messages regarding missings )'s =IF(AND(B978="P",R978=0,OR(S978=0,S978="N"),"processed","").
I have two vba the first one records the date & time of a cell change in column a and places this in column b
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a:a")) Is Nothing Then
Target.Offset(0, 1) = Date & " " & Time
End If
End Sub
The second one is a function to get the User logged in to windows.
Function UNameWindows() As String
UNameWindows = Environ("USERNAME")
End Function
What I really could do with is a way of incorporating the user name in the first function so it records date, time and user?
I have two seperate columns, First Name (Column B) and Last Name (Column C). Is there a way a macro can combine the two into one cell Last Name, First Name.
View 3 Replies View RelatedI 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] .....
Trying to combine Column A (First Name) with Column B (Last Name) Probably very simple.!
View 2 Replies View RelatedI have a big amount of data and want to consolidate it.
I have identical text in Columns 1,3 and 4, but the text in Column 2 is different for all.
How do I make 1 row instead of 3 with Column 2 consolidated?
See the attachment : Excel_list.xlsx
I have a problem in excel that is very urgent to resolve. I have a table that contains duplicate records e.g two people living at same address. I want to merge those records whose address field value is same. Find attached an excel sheet that contains the exact data and the exact output that I want. I know it requires VBA coding.
View 4 Replies View RelatedI have two different bits of code as follows:
View 3 Replies View Relatedok - I have numbers that need to be converted to 12-digit numbers with leading zeros if they are less than twelve digits. for example, 1234567 would turn into 000001234567 to have 12 digits. to do this, i use:
=rept(0,12-LEN(A1))&A1
additionally, i need to strip off the last three digits and replace them with three zeros. my example would now become 000001234000. assuming the result of my first formula (above) is in cell B1, i would use:
=INT(B1/1000)&"000"
Is there a way to combine these two functions into one formula to make this conversion process more painless? Or is there another formula/function I can use that I haven't thought of or do not know?
I'm having a mental block here. I need the below statement to have a max value of (B14*B4) in the instance where
(B17-B19)*0.7 exceeds (B14*B4),
IF(B17-B19
I got 2 macros that I want to run in one go. So to explain:
Here is the data of Sheet1 before the macro is run:
A
B
C
D
E
[Code]....
Keep in mind, when Macro2 is run it puts the data on Column W then X then Y etc as soon as the Column reaches 20 it moves on to the next one.
So my question is:
Is there anyway to combine both macros to just run it once instead of running it individually?
Just starting to learn VBA. I have the 2 following codes.Can they be combined into 1 code.I have 2 buttons one say hide rows and the other button says unhide rows....can 1 button be used and when the rows are visible the button says hide rows and when the rows are hidden the button says unhide rows
Sub hiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = True
End Sub
Sub unhiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = False
End Sub
I have two subs which I've been trying to combine into one. Essentially all that is changing is the slicer name and range value.
Here's what I have:
Code:
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
Dim sItem As Excel.SlicerItem
Dim myString As String
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
myString = myString & "," & sItem.Name
[code].....
But it's erroring out on me.
how to combine these two codes into one?
Code:
Sub DelTemp()
Dim LR As Long, i As Long
Dim pt As PivotTable
[Code].....
I have two macros that I need to run as one and I am not sure how to call the second macro from within the first. I'm sure it's easy...If you know how... Thank you in advance.
--------------------------------------
macro 1
Sub PrintUsedRange()
'
Dim ws As Worksheet
'
For Each ws In Worksheets
Application.CutCopyMode = False
ws.PageSetup.PrintArea = ws.UsedRange.Address
With ws.PageSetup
.Zoom = False
.CenterHorizontally = True
.Orientation = xlPortrait
.FitToPagesWide = False
.FitToPagesTall = 1
End With
Next ws
ActiveWorkbook.PrintOut............
Four worksheets (A,B,C,D) all begin with row2 and end with the last non-blank cell.
What VBA code would merge the information from the four worksheets to Worksheet E?