I have a four columns, one with created date, one with proposed date, one with a name and one with a dollar amount, I want a formula that will look at the two columns with dates and report back based on the name, the total dollars of each line where the dates are prior to today.
View 1 RepliesI have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.
However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.
What I need is a formula that finds the ticker (actually it is a CRSP permanent number) and the date in Sheet 1 and returns the price in Sheet 2 (cell B2 etc.), and if no price is available, return 0.
I have attached a shrinked example (the actual dataset contains more than 150.000 observations). example.xlsx
I have a list of names in sheet1 column A and 3 more list of names on sheet2, sheet3 and sheet3. What I'm trying to do is find a match for each name on sheet1 column A on the other sheets and depending on which sheet it was found, I need a specific value returned to sheet1 column B.
For example, if A1 was found on sheet2, then on B1 I would need to return the value "Morning" but if it was found on sheet3, then I need the value "Afternoon" and if found on sheet3 I need the value "evening" returned to B1 and then the same for every other name on the list found on sheet1 column A.
What I tried was this.
This goes in Sheet1 B1 (then I would drag it down to search for the rest)
=IF(AND(MATCH(A1,'sheet2'!$E:$E,0)),"Morning",IF(AND(MATCH(A1,'sheet3'!$E:$E,0)),
"Afternoon", IF(AND(MATCH(A1,'sheet4'!$E:$E,0)),"Evening","Un-assigned"))
Now the problem with this is that it only works if there is a match on sheet2 but if there is no match, it just returns "N/A" and it does not move on to sheet3 and/or sheet4.
I also tried with vLOOKUP instead of MATCH and the same thing happens.
I also tried this and the only thing I get is "Un-Assigned"
=IF(AND(A1='sheet2'!E:E),"Morning",IF(AND(A1='sheet3'!E:E),"Afternoon",IF(AND(A1='sheet4'!E:E),"Evening","Un-Assigned")))
I am trying to display part of a worksheets new sheet based on some criteria. I have a worksheet with a list. I have a date field in the list. I need to breakup the into several sheets based on the dates. I want all of items in the list with a February date put onto a tab titled February and all items on the list with a March date on a tab titled March. I haven't been able to find a funtion or code that will do this. The new tabs don't have to be created through automation. i can create those manually. i am looking for a formula or possible code to put on the new sheets that will look up and return specific rows from the main worksheet with the master list. i attached a sample of my worksheet. I need to search by Column O titled Date of Next Review.
View 4 Replies View RelatedI have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows:
Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look. Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
The excel formula I am using is as follows: =Nth_Occurrence(AF2:AF622,AE2,2,0,1)
Where AE2 is the date I am looking up. My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.
I have used the below formula which works in a standard cell, however I would like to have this is a VBA code using the Target.Offset option, however I can not get this to work. There may also be a better way of doing this.
The code is looking to see if the 1st cell (A118) is empty or not, if it is empty do nothing otherwise it then looks up the value in the 2nd cell (B118) and assigs the appropriate name from the range values.
VB:
=If(A118="","",LOOKUP(B118,{0,0;0,"";1,"Main Bank";71,"PFS";80,"Main Bank";106,"Dry Clean / Photo";112,"SCO";141,"Cafe";168,""}))
(1) I've created a list in A2, which looks up F1:F27. I'd like to place an IF or Vlookup function in B2, that returns the corresponding cell from range G1:G27 based on selection in A2.
(2) Also, is it possible to include the fill coulour based on selections in A2 and B2?
I have data inputted into cells a-o in rows 1-250. I want to search, lets say row 1, (I do want to search all 250 rows if that info is relevant!) and if cell m is blank then I want to return all the info from row 1 into row 260. If there IS data in cell m then I want to return nothing. Bottom line I want to return all the data in all the rows that contains nothing in cell m.
View 3 Replies View RelatedI have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"
It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
I am trying to an If statement but the first part of the IF statement needs to be between a specific date range. Here is the formula I have:
=IF(AND(Sheet1!A4>=1/1/2008,Sheet1!A4
I have a list of venues and a start date, end date and number of items per day to be installed, which looks like
A - B - C - D
Venue 1 - 01/01/12 - 31/01/12 - 15
Venue 1 - 03/01/12 - 30/04/12 - 10
Venue 2 - 05/01/12 - 28/02/12 - 9
And I want to summarise this in a table with one row for each day of the year which looks like
A - B - C
1/1/12 - Venue 1 - 15
2/1/12 - Venue 1 - 15
3/1/12 - Venue 1 - 25
4/1/12 - Venue 1 - 25
5/1/12 - Venue 1 - 25
etc etc
I can't work out the formula to put in Column C on my second table which will lookup the venue name and then put in the sum of the daily values.
I have a Master workbook with 4 sheets named WEEK 1 to WEEK 4. At work we work around 13 4 week periods and need this workbook to be dated accordingly, that is Period 1 Week 1 and date of first day (Sunday) of that period. The ideal solution would be for the user to enter 1 to 13 into a cell and via a button all dates to be populated and a new workbook created for that period. I have attached a sample workbook with the 4 weekly sheets and a date range on another sheet to show the range I need the dates from. I hope the workbook explains what I need better than my explanation here!
View 2 Replies View RelatedFor those of you that love difficult ones, here's one..
I have a sheet that has a column of names.. Across the top row I have dates.. let's say from the 1st through the 31st.... in the body of the report i have different sales numbers and what I am trying to is the following...
1/1 1/2 1/3 1/4 1/5
Johnny 3 4 2 2 1
Becca 3 4 1 1 1
So let's say in this instance, mind you my real report is about 342 rows of names and stats.. Is look for Johnny between 1/3 and 1/4 and average those numbers between those 2 dates...
I've tried everything from sumproduct to vlookups and can't seem to get it right...
In worksheet1 I have a set of data. Column A is a value, column B and C are dates. In worksheet2 I have dates in Row 1 going out horizontially. I would like to create a fomula that returns the value in column A in worksheet1, if the date is between column B and C. I couldn't get my array to work. it's been a couple of years since I've wrote any indepth formulas.
View 7 Replies View RelatedI am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant.
View 9 Replies View RelatedI'm trying to create a formula that will lookup multiple criteria within a table, however the problem I am having is that one of the criteria needs to fall within a certain a date range. I've used index & match to look up the dates when they match exactly, but how to return the same value for when they fall within a date.
I have attached the spreadsheet - Date Range.xlsx! The requirement is to get the correct "pay" from the "Contingent Workers" sheet to the Timesheet sheet. So it will match the Employee ID, but also the date on the Timesheet tab will fall between/or be the exact date indicated on the contingent workers tab.
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.
View 7 Replies View RelatedI am trying to return the Min of a range of dates. The issue I have is a single date is repeated many times. Here is some sample data:
Sheet 1
Column A / Column B
1/11/11 / 15
1/11/11 / 20
1/11/11 / 5
1/15/11 / 30
1/15/11 / 35
Say on Sheet 2 I have a list of dates and Cell A1 has 1/11/11. In Sheet2!B1 I want a formula that uses the value in cell Sheet2!A1 and looks at the array on Sheet 1 that would return a result of 5 for the date showing (in this case 1/11/11).
I have an excel sheet where I would like a user to enter a start date and end date(say Feb 1, 2013 to Feb 28,2013). And these dates will be able to change to whatever month the user wishes to look up in a specific date range(column A has 365+ dates). This post is closely related to [URL]... but I cannot figure out how to get it to work from my case.
So working off the same worksheet, I have the beginning date in AC35 and the end date in AC36 in which the user enters. I would like the dates that fall in this entire month, including the beginning and ending dates to be referenced when looking for the specific day that contains a value I have in S35.
In other words, I have data in columns A through N. The dates are in column A and S35 is a value obtained from using Max(L185:L526) where L185:L185 is manually selected each time I want a different month. I don't want this to be a manual process of scrolling down the spreadsheet to get the next month.
Assume I have a simple date array
1/13/20142/12/20143/12/20144/11/20145/13/20146/11/2014
I want Excel to see where today's date falls and return how many work days it is from the date boundaries.For example, today is 2/8, which falls between 1/13/2014 and 2/12/2014
Cell I19
=LOOKUP(TODAY(),B2:Q2)- yields 1/13/2014
=NETWORKDAYS(I19,TODAY()) yields 20 workdays
But I want to also grab the 2/12/2014 date and get the number of workdays from that- all in one equation.If not one equation then 2 but not separated for each border date like I have above
I have a large table in the final tab of my excel book. This table contains data such as Company name, contact details, etc but also a date which I must contact them on (this is nothing sinister, just a curtsy call following a job ).
The problem I am having is that I am running a vlookup on the first tab on a cell referring to todays date. This only brings shows one record which is the first occurrence of this date. This isn't very useful to me
The problems I need solving are:
I would like to be able to vlookup a range of dates (i.e. 7 days before or after todays date) - how do it do this?
I would also like to be able to return multiple results (i.e. if there is more than one company within that date range I would like them to show).
I'm writing a macro that will import data from one workbook to another, based on yesterdays date. I have the import functionality working, and I've been playing all afternoon, but I can't find a way to find yesterdays date in a range and use that row number as the row offset value in this line:
Rather than having a set row offset value (in this case, 14) I would like to use the find function to lookup yesterdays date in range "B50:B80" in the worksheet I've designated as "sh1" and return the row number of the cell that has yesterdays date and use this value as the row offset value, replacing the hard coded 14 that is in there currently. The date values in the range are formatted as per Date Format.jpg.
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
I have this vlookup formula and as you can see when I run it works good. The only problem is that I would like to have formula that it something is not found I don't get #N/A , but rather a 0 or blank.
=VLOOKUP(A6,GL!A$1:E$76,5,FALSE)
LOOKUPDIVDPTCC SALES 46120
010775010775 49,028,244.58
010200010200 3,373,145.76
010700010700 113,667.00
010300010300 14,382.54
011000011000 (1,518,076.05)
010600010600 589.98
011900011900 6,280.31
010900010900#N/A
011100011100 313.08
010750010750 570.90
Column A lists types of employees (managers, engineers, sales reps, etc.), each of which earns a seperate hourly rate. Column C lists hours worked, and Column D lists the total pay (Cx*Bx).
So column B is missing. Is there a way to generate the hourly rates in column B for each employee type in collumn A, i.e. if it's an Engineer, return $29, if it's a Technician, return $18, without an infinite set of True/Fasle conditions.