Querying Date Range And Returning A Value?
Jul 28, 2014
I am trying to set up a query to search a date range within a table in one sheet based upon a more narrow date range in a second sheet AND return a simple count, median currency value or value based upon a ratio (see attachment).
Note: the Sale Price, List Price and Supply values will be based upon the filtered date range (each quarter) and return a value based upon that range. The Sale Price and List Price also need to be a median within each quarter value so I can chart the data.
Help.pdf
Here is what I need to return:
Num Sales - simple count from each quarter
Num Listings - simple count from each quarter
Sales Price - median value within each queried date range
List Price - median value within each queried date range
Supply - numeric value based upon absorption rate, supply = listings each quarter / absorption rate (absorption rate = sales each quarter / months each quarter)
View 5 Replies
ADVERTISEMENT
Aug 13, 2009
I have used several IF and AND functions in the past in order to check to see if 2 values = true and return a third.
(for example =IF(AND(A1>10,B1<20),C1,"")
However I can't seem to figure out the senario below.
I have 2 coulmns, Column A has dates and Column B has a numeric value
I need to check to see if column A falls between 2 dates and returns the sum of column B
For example if have 100 rows and 10 of those rows have dates that fall between 06/01/09 and 07/01/09 then add up column b for those 10 rows and return the value.
View 3 Replies
View Related
Mar 19, 2008
I currently have an Excel file with the ODBC connection and using my own SQL. Doing it this way would require the super user to go into each sheet then to the SQL each time to change the dates. I know there is a way to allow them to simply change the dates in cells H1 (or H1 and H2) then refresh data and whoolaa it would be updated.
My goal if possible via this board and all the knowledge available would be to have a spreadsheet allowing a super user to change the date in Cells H1 and H1 & H2 on sheet Tst1 and have the data returned to sheets (Tst2, Tst3) based on my own SQL statements.
Here are some parameters that I think you might need to know.
(And as you may guess I do not have much VBA experience so if at all possible use my naming convention shown below. and feel free to write for a first grader.)
User Name (not actual): "UID"
Server (not actual): "DEV"
Password (not actual): "PWD"
File Name: Excel_ODBC.xls
Sheet Name: "Tst2"
"Tst3"
Output Cell: "A1" for both sheets.
Oracle table name: UAB .................
View 9 Replies
View Related
Apr 5, 2007
I've been racking my brain for hours over the past few days trying to compile a spreadsheet for work. Currently the spreadsheet covers all the aspects of a auto loan process. It is used as a tool for auditors to check the work of others. The spreadsheet simply uses "P" to designate "Pass" or "F" to designate "Fail" on whichever section.
How do I reference the P's and the F's in the spreadsheet? I've had trouble getting excel to query any text in cells. Also, each loan application is started on the next line, so I need to make sure these formulas repeat as a new line is started - how would I go about doing this? Currently - Cell G3-CG3 contain the "P's" or "F's" - the formula I had used was {=IF(G3<>"P","FAIL","PASS")} - which does work, on a single cell. However, if I try to add the entire range in {=IF(G3:CG3<>"P","FAIL","PASS")} the cell returns #VALUE. Obviously, I'm inexperienced with excel in this fashion, and I need to know what formula I should be using.
View 10 Replies
View Related
Apr 16, 2007
I'm struggling to find a way to index data in an array that meets certain matching criteria. I am looking for an employee's rate on a given day by searching a database that lists the dates that an employee's rate was changed. I was hoping to solve it with a crafty index and match array formula but have been unable to find something that works so far. I have attached a simplified example of what I am trying to do.
View 2 Replies
View Related
Feb 27, 2014
I am trying to retrieve the "option" values and "inner text" from two dropdown lists on a website. Using the code below I can retrieve the data from the first list but the second dropdown is not populated until a value is selected in the first list.
Firstly, is it possible to specify a particular "select" element by using its "name" or "id" and if so how? Secondly, will I be able to populate the second list by looping through each of the values from the first list and retrieve the second list values each time?
I have been searching all day so far and cobbling together bits of code that I have found that do some but not all of the process I require.
[Code] .....
Below is a small section of the results so far. The second dropdown list will give the models based on the value selected in the first dropdown.
inner text value
Alfa Romeo 53
Aston Martin 69
Audi 16
Bentley 87
BMW 17
Chevrolet 77
Chrysler 54
Citroen 18
Dacia 92
Ferrari 95
Fiat 19
Ford 20
Honda 22
View 13 Replies
View Related
Jun 6, 2008
I have a list of stores (A1:AX) and corresponding customer affinity numbers (B1:BX). Each time a customer visits a store with her affinity car, a record is generated. So the data is quite simple - two columns.
A sample of my list:
StoreCustomer#Phl01180Det01808Nash02118Phl02542Hou02TE2Atl03094
The tricky part (as always) is the output. I would like to divide the customers into three groups:those who visit only 1-3 stores
those who visit 4-7 stores
those who visit more than 8 stores
I would like to create a way to view a list of stores in column A and see how many of its customers are in each of the three groups listed above. Does that make sense? Output might be:
Multi-Store CustomersLowMediumHighPhl23188Det240322Nash123489Phl3498790
View 9 Replies
View Related
Feb 2, 2012
I inserted the Date & Time picker Control into my worksheet. I have it linked to a cell. When I change the date, the new date goes into the cell, but it does not appear to be a "date" . The date formulas that use that cell do not evaluate properly. It looks different than when I type a date directly in to the cell (it is left justified). I tried formatting the cell as a date and the format property of the control is set to shortDate.
View 2 Replies
View Related
May 1, 2012
The message box in the Standard module procedure returns 00:00:06.
Standard Module
Code:
Sub New33()
Dim clsCampaign As CCampaign
Set clsCampaign = New CCampaign
clsCampaign.MailCode = "AWSXBETC"
[Code]....
Code:
Option Explicit
Public MailCode As String
Public LandDate As Date
Public MailDate As Date
Public FirstPanel As Date
Public FinalPanel As Date
View 1 Replies
View Related
Feb 12, 2008
how to write an excel workbook function that will return last friday's date?
For example: If Today is 2/12/2008, it will return 2/8/2008 as the date
View 6 Replies
View Related
May 27, 2012
I have a list of dates in column A which are in order but non consecutive. I am creating a new table with all consecutive dates. I need a formula to check whether a date is in column A and then give out a number in the corresponding column to the right. I am able to use Vlookup for this but if the date is not present in the first coumn it returns an N/A response. Is there a way of returning zero if the date is not present?
View 5 Replies
View Related
Apr 24, 2009
i have a column in a spreadsheet that stores a date and another column that i want to store a date category. I want the "date category" column to return the date from the "date" column as: eg Jan 09 (if the date is eg 24/01/09)
View 2 Replies
View Related
Feb 5, 2014
I have 2 tables where I need to pull data from one table into the other.
Table 1 - pull data here
A
Site
B
Begin Date
C
End Date
Rate
[Code]...
For example: site 77 in table 1, I need to pull whatever rate is shown for site 77 in table 2 for the date range specified on table 1. So the correct answer for the first row site 77 in column E would be .02. Notice there is a problem...mainly the dates do not match exactly. I just need the rate the applies for the majority of the time.
Another example. For table 1, site 174 from 2/11/11 to 2/22/12, the correct answer in column E (pulled from table 2) would be .02, even though Column D shows .00.
View 1 Replies
View Related
May 28, 2007
I have a spreadsheet with 2 worksheets within it. These are simply called sheet 1 and sheet 2.
On sheet 2 I have cell A1 containing =(TODAY())
Also on Sheet 2 I have cells A28 to A39 containing Month names and cells B28 to B39 containing values. Like this
January 1000.00
February 1000.00
March 2500.00
April 1250.00
May 1750.00
June 2200.00
etc
How can I return the sum of these values into Sheet 1 B18 based on the date. That is to say that I want to add up the sum of Jan, Feb, Mar, Apr, May up to and including callcualtaions made on 31st May but as soon as the date goes past 1st Jun I'd want to include June as well and so on.
View 9 Replies
View Related
Mar 20, 2014
I have a bit of code (See below) which basically looks at a list of data in rows 10:99 and then finds a date that is between cells c1 and b1. If there is one the it puts that date in if not it leaves cell blank.
[Code] .....
The problem I have now found is that is the dates in the sheet TR rows 10:99 and one of the dates is outside the range so for instance if the range set by c1 and b1 is 10/3/14 - 20/3/14 and in TR there are dates:
12/3/14
08/3/14
it wont return anything but if the dates are
12/3/14
08/3/14
15/3/14
it will return 15/3/14.
Is there anyway that I can change it so it wont return 0 if the last one is outside the range.
View 14 Replies
View Related
Nov 20, 2008
Take the displayed date from a cell, in this case formatted as yyyymmdd, and simply display the date as plain text, not converted to the 1900 date system. For example, I have a date cell that reads 20080610, but everything I have tried returns 39609. But I want it to return 20080610 in plain text so that I can use RIGHT and LEFT functions to extract the year, month and day.
View 2 Replies
View Related
Jun 25, 2014
I need an excel function to return the earliest and latest date for a particular/unique ID.
For example: In my sample workbook, the earliest date for all the columns with ID = 1 should return 11/1/2001 and the latest date should be 12/2/2011. If all the dates for a particular ID are the same, it should just return that date. For example, for ID = 2, all dates are 5/5/2010 so the earliest and latest dates should equal 5/5/2010. The earliest and latest dates have to be for each unique ID.
View 5 Replies
View Related
Mar 29, 2012
I have in my 'Daily' sheet I9 which needs a formula to look at the date in C9 and the scan range 'Weeks' F6:AN41 and find the cell with the matching date and then whatever text is in Row 2 of the column the date was in will be returned.
View 2 Replies
View Related
Apr 29, 2014
I have a spreadsheet that has amortization tables for several loans. I am trying to add a summary tab that will pull the next due date on each loan. Some of the loans are behind so I can't use a >today() function. I have been trying the following:
=MIN(IF(AND(ISBLANK(Smith!A:A),NOT(ISBLANK(Smith!B:B))),Smith!B:B))
Column A is date payment is actually made, and column B is due date, so I want to return the first column B that has nothing in column A.
View 1 Replies
View Related
Feb 25, 2010
I have an Excel spreadsheet (XP - 2007) listing Job Nos. in the first column, with several columns of Station assignments and dates.
Both planned dates and actual dates are included, adjacent to each other. The dates are not necessarily in a straight ascending or descending order. Separate arrays exist for: Plan Nametags, Actual Nametags, Plan Dates, Actual Dates.
Example:
Job No.Sta1 PlanSta1 ActSta2 PlanSta2 ActSta3 PlanSta3 Act1A10001-Feb-101-Feb-106-Feb-106-Feb-101-Mar-101A100116-Feb-1016-Feb-1019-Feb-1022-Feb-105-Mar-101A100225-Feb-1025-Feb-102-Mar-104-Mar-1010-Mar-10@
@
I need to capture two pieces of information for each Job No. on a daily basis:
1- The 'Planned' Station for the build, based on a match of the 'Plan' date fields to a pre-populated 'Report Date'.
I've been able to do this (using INDEX-MATCH function).
2- The 'Actual' Station location for the build, based on the Maximum 'Actual' date entry in the row for each build.
(In the example above, Job# 1A1001 would have an 'Actual' location of 'Sta2 Act'...)
I need to figure out how to accomplish step (2) above. I've made several attempts with INDEX-MATCH and LOOKUP functions, without success.
View 10 Replies
View Related
Sep 21, 2008
I've posted this question in another site http://en.allexperts.com/q/Excel-105...xExp_72253.htm , but i haven't gotten any answer. I'm attaching an example of the file to show an idea of what i'd like to do, please check first. As you can see, i have a Due Date and Priority columns. The Due Date column has a formula, that when i choose the Priority level, it calculates the Due Date from the current date and auto populates in the cell. The problem is, when i open the file the next day, and insert another Priority level, it updates everything even the previous Due Date entry. This is the formula i have in place for the Due Date:
=(TODAY())+SMALL(IF(WEEKDAY((TODAY())-1+ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0))))<6,ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0)))),(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0)))
Is there a way to make up a vb macro to be able to make the Due Date output static, without affecting the formula?
View 5 Replies
View Related
Mar 1, 2014
trying to get a specific value to appear if the value falls within two dates in which it was posted.
I have the following data
1/1/2014 in Cell AB2
$1000 in cell M3
1/1/2014 in cell P3
12/1/2014 in cell Q3.
What I'm trying to do, if Value 1/1/2014 AB2 falls between 1/1/2014(P3) and 12/1/2014 (q3), then I want it to return $1000 (M3), if it does not, then to post 0 or blank.
View 1 Replies
View Related
Jun 3, 2009
Just for fun (and a massive headache) - I am trying to do the following
=IF(INDIRECT("'"&$Q$1&"'!E2")="Table",IF(INDIRECT("'"&$Q$1&"'!H2")="Yes","Graph","Table"),IF(INDIREC T("'"&$Q$1&"'!E2")="Descriptive",INDIRECT("'"&$Q$1&"'!J2")," "))
Where Q1 is the name of the worksheet I want to access.
Where E2 is the type of information I am trying to display (Table or Descriptive)
Where H2 is if it needs a Graph or Not
Where J2 is a comments field
What I am wondering is if the following values are set
Q1= May-09
E2 = Table
H2 = No
How can I get it to return the information contained between cells P2 to S3 on worksheet May-09
View 3 Replies
View Related
Apr 4, 2014
I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.
Scenario:
Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.
Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.
View 10 Replies
View Related
Apr 25, 2014
I want to be able to return the value of the last non-blank cell in the horizontal range L3:BF3. This would need to account for any blank cells that might occur within the range. Those 'blank' cells would contain formulae and the non-blank cells would contain GCSE grades from A* down to U or National Curriculum levels in the format nx (e.g. 3a). I would need to be able to copy the formula down to other rows. The reason I'm not keen on an array formula is because they can slow things down, as I understand it.
I'm afraid I don't have a sample worksheet as this is all, for now at least, an emerging plan in my head. I should probably add that the grades will not necessarily increase in value in the range, so we're not looking for the largest.
View 10 Replies
View Related
Jul 5, 2012
I have a row of upto 20 numerical entries, (1,1,2,2,10,10,3,3,4,5,6,7,8,12,1,1,7,8,9,11) what formula do I use to go about getting excel to return the two highest numbers?
In this example I'd want excel to return the numbers, 12 and 11
View 1 Replies
View Related
Jun 6, 2014
Store 1
Store 2
Store 3
Store 4
Top Selling
Second
Third
Fourth
[code]....
I am trying to find two formulas:
Formula 1: Formula 1 will be entered into rows 6:9 (in this example). I need it to return the highest selling product (by store) in order form highest selling to lowest selling. I have a makeshift formula already but it cannot report in order (only the highest selling) and it cannot get past the one return index( limitation. When it finds two high sellers it prioritizes the ranking based on how well that product has sold across the other stores. Product 1 sold 13 times across all stores. Product 2 sold 9 times across all stores. So if product 1 & 2 tie for Store 3 (as shown in the example) Product 1 would be chosen based on its sales across the row.
Formula 2 : Same as formula 1 but with Stores (in column 6-9). It reports the highest selling store and treats ties the same way as formula 1.
View 4 Replies
View Related
Apr 30, 2008
The purpose of my code is to time production cycles for heavy equipment. There are five - six segments in the cycle, and it's possible that not every segment is included in each cycle. It's also possible that the cycle will not go in sequential order.
Here's a snippet of
Sub MacroD()
Dim LR As Long
LR = Range("D" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Unprotect
Range("D18:D" & LR).Value = Now
UserForm1.ListBox1.Text = "Time"
UserForm1.ListBox1.SetFocus
' UserForm1.Show
End Sub
Sub MacroF()
Dim LR As Long
LR = Range("F" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Unprotect
Range("F18:F" & LR).Value = Now
UserForm1.ListBox1.Text = "Time"
UserForm1.ListBox1.SetFocus
' UserForm1.Show
End Sub
My problem is that when the macro runs, it returns the time in more than one row and overwrites the previous timestamp. It starts in row 17, and will record the first time in 17 and 18. When the macro is run again, the time will be recorded in 18 and 19. When it's run a third time, the timestamp is recorded in 18, 19, and 20. So essentially, I'm losing all of my previously recorded data. When I finish, all I have left is the timestamp in row 17 and whatever the last time recorded is.
View 13 Replies
View Related
Jun 7, 2006
I am trying to create an advanced filter that filters my results into a row.
e.g.
Shane
Shane
Tom
Tom
Paul
Mike
would return
Shane Tom Paul Mike
I have tried changing this piece of code
Range("M1:M300").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns("M:M"), CopyToRange:=Rows("25:25"), Unique:=True
The problem is with
CopyToRange:=Rows("25:25")
I have tried using Range instead of Rows but I am not having any joy.
View 4 Replies
View Related
Nov 26, 2009
I have a problem with a calculation i am trying to do. I have to calculate holiday allocation for staff but i my company also offers length of service bonus days. After 5 years you get 1 extra day, after 10 years you get 2, after 15 you get 3 and so on. In my sheet i have a formula based on their start date that tells me their length of service in the following format: 10 years, 2 months.
I have put an IF Statement in for each of the different milestone criteria but the result is not coming back so i know i am missing something. Is there something wrong with the formula itself or is there a better one that will allow me to put the start and end of the range i want it to look at? For example can i tell it to say if between 5 years and 9 years 11 months put 1, if between 10 years and 14 years 11 months put 2 etc in column H.
View 3 Replies
View Related
