I have a spreadsheet showing a number of jobs with numerous columns for individual bits of data regarding the jobs. Two of these columns show a strat date and an end date for the jobs. What I require is a seperate worksheet that will show the rows that have the formula =Today() be equal to or in between the start and end dates. I've been trying for ages now and have a mental block.
View 2 Replies
I have a dynamic dropdown list in B3. The dynamic dropdown list in B4 is a result of my choice in B3, and my dynamic dropdown list B5 is a result of my choice in B4.
When changing a value in B3 the dropdown lists B4 and B5 will be reset. This is done by this VBA Script in my worksheet.
I have a set of data points with date and time shown in the picture. I need to take out all the data point that are at the 30 min interval. Meaning I only want to have the data points hourly only. What can I do to remove the :30 minute datas.
I have tried the find option typing In :30 and getting all the points but then have to replace, which I do not want to do. Need to remove them so they do not show up on the trends ( graphs) I made for them.
This is for excel 2007.
Seems I can't get picture up on the ipad, so the data points look like this
1/1/2012 0:00
1/1/2012 0:30
1/1/2012 1:00
1/1/2012 1:30
And so on for an entire year. Just need to remove the ones with the :30
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx‎
Trying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
I have a row of values with empty cells in between and I need to detect the first value(negative) and return the date of this numerical value, which is placed three cells above. It is basically a way to automatically find the date of the first transaction for an IRR calculation. I was thinking of using an HLOOKUP or some kind of a MATCH, INDEX structure but I think you need to match to a particular value there, or am I mistaken.
View 2 Replies View RelatedMy goal is to have a macro run when my workbook is opened to turn particular worksheet tabs red depending is a date is previous to the current date.
Example,
I have 5 sheets. One of those sheets states and end date of 26-Jul-2012. Today is 27-Jul-2012, therefore, from this time forward the tab should be in red.
Is there any way to make a cell's contents change automatically according to the present date?
View 9 Replies View RelatedI have a spreadsheet with 5000+ rows. On one column there is a user number, the user number repeats few times. Therefore, there is a different number of rows for each user.
Also, the first line for each user has a date on a different column.
I am interested in filtering out the users who do not match a certain date range. The issue is that the code I found hides all of the rows without dates and also the rows that are with the range of dates.
I have a table in excel which looks something like this:
Product Ref
Date
Description
1
29/05/2013 19:58:50
Product A
[Code] ....
All I'm trying to do is get the only the first dates for each of the related Productsrefs. So the result should look like this:
Product Ref
Date
Description
1
28/05/2013 19:26:48
Product A
[Code] ....
Is there a built in function in excel that can achieve this?
I have a userform which writes a date into a cell within a row within a table. The code is
[Code] .....
Everything works fine, the date gets sent to the cell as stated and is all correct. However, I also have vba which runs an advanced filter, which the following code is included in.
[Code] ....
P7 and O7 are the advanced filter criteria. Basically in the form I have a "completion date" which is posted with the first code I posted. So lets say that the date posted is 25/07/14, and I want to have a filter of all the dates which are coming up in the next 7 days to be shown in the Advanced Filter results area of my spreadsheet.
With the second part of code I posted, this results in nothing when using the advance filter. The original posted date in the cell (from the first code) is shown as "25/07/14". So when I run the advanced filter, nothing happens. HOWEVER, if I go to the cell containing "25/07/14", delete the contents and write "25/07/14" manually and then run the advanced filter (second code posted), the date shows up in the results working fine. But I dont understand how me typing it out again makes it work?
In AA1 i input the date based on the lookup.
I want to filter Column 7 (Col G) to what ever is in AA1.
Say i had the date 02/05/2012. My filtered range should only be dated 02/05/2012 but its not filtering to those dates
Code:
With Sheets("UNPRODUCTIVE")
With .Range("AA1")
.Formula = "=IFERROR(LOOKUP(2,1/(TEMPLATE!S12:S40""""),TEMPLATE!B12:B40),"""")"
.Value = .Value
.NumberFormat = "DD/MM/YYYY"
End With
On Error Resume Next
.Range("A1:T1").AutoFilter Field:=7, Criteria1:=.Range("AA1").Value 'filter with only the dates the macro is run for
On Error GoTo 0
End With
I want to be able to filter on an excel spreadsheet by dates between x and y. The values x and y are each in my userform textboxes, tbStart and tbEnd.
For whatever reason there seems to be a format issue. When I run the script I see that the data is filled correctly in the filtering fields but I don't see any results. I simply need to click ok when reviewing the filter and it works. This makes me think that there is something wrong with the format of my tbStart.Value and tbEnd.Value
Here are the variations I have tried:
The values on their own should always be formatted as date in the text box as I am using spin buttons to edit the date
with this code or similar
Me.tbEnd = Format(CDate(Me.tbEnd) + 1, "dd/mm/yyyy")
Me.tbEnd = Format(CDate(Me.tbEnd) - 1, "dd/mm/yyyy")
Version 1:
wsData.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
">=" & Me.tbStart.Value, Operator:=xlAnd, Criteria2:="=" &
CDate(Me.tbStart.Value), Operator:=xlAnd, Criteria2:="=" &
CDate(Format(Me.tbStart.Value, "dd/mm/yyyy")), Operator:=xlAnd, Criteria2:="
I have a column formatted as a date. I would like to filter out all rows which have a blank date value. I don't see any blank value to select when I try to select auto-filter for that row. Any suggestions?
I would like to filter out the blank values because I found that if I pivot on the data and there are blank values in the field, I can not use the group option for that field.
I have this table as my simple little database and I have to extract some info from it and keep track of dates and deadlines...
A
B
C
D
E
F
G
H
[Code]...
I have to get the following data, I need to lookup the last visit date out of all dates associated with a specific name in column "A".
This requires that I test an unsorted table for a value in column "A", i.e. "test2". Then determine which record has the latest date.
In other words, If I test for the value "test2" in column "A", I find I have 2 records, with dates ranged across "D4:F4 and D7:F7".
I need to return the latest date found in those ranges...
The purpose here is to write a function that will find a range of dates associated with a name in an unsorted table, from that range find the latest date, then add 90 days to that date and display the results....
I have been trying to employ a form of, =VLOOKUP(E3,$B$3:$D$11,MATCH($F$1,$B$1:$D$1,0),FALSE) and =INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num) combined with "IF" functions, but to be totally honest I am COMPLETELY lost here....

Situation:
I have two drop down lists 1) Country and 2) States/province
Country has list: [US, Canada]
If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list
Scenario:
First, I select "US" and choose the states to "Florida"
Next I change the country to "Canada" and forgot to choose province
Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
BUT the current value is still "Florida"
Probem:
Now I have "Canada" and "Florida" selected in the sheet
Question:
If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E
Finish date is column F
Owner is column L
I am working on a pivot table just like the one on the picture here [URL] .......
Ideally I wanted the sums by date to be displayed horizontally and not vertically as shown in the picture, but was unable to do so. Anyways, I want to be able to filter those dates, so I can display data just from an specific date, and then change this date whenever I want and the new data will show up.
I tried doing by the checkbox that appears under the "data" dropdown on top of the column; However, when I uncheck a specific date, it dissapears and I have to add it again if I want to see it. Is there a way to keep the values on the dropdown even after I uncheck them? Or even another way to filter the information? Below is the pic of the dropdown I get when trying to sort the data, But as I said If I uncheck one of the values, it dissapears from the list.
[URL] ........
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
I have a two sheet, in Sheet1 i have a data, and in sheet 2 i have a count of location wise data in it, which is from sheet1.. And beside it u will see a button "View Detail". When you click on that button it will pop up a userform which have listbox and 2 button.. Now in listbox i get a filtered value of column C of sheet1 (For eg. Mumbai, Pune, Chennai).. When i click on the value (Mumbai) in Listbox and then click on View Location Details. It should give me a details of mumbai based candidate, like name, pod no, location in the range of (H5:J100) of sheet2..
I wrote the code for it but its not giving me the correct data..
is there any way to NOT Displaying Items with No Data when filtering data in my pivot table?
For instance, I have 2 report filters: Category and Subcategory, when I select a category in the first filter I want to see only the options of subcategories with data in the second filter, I mean display only the subcategories of the Category previously filter.
Same scenario I have with a report with Directors and Organizations, when I filter one Director it would be nice to see only the organizations of this director and not all options on the data.
Firstly I have these 12 values as in the attached excel sheet (in the order: FY09, FY09Q4, FY09H1, FY09H2, FY10, FY10Q1, FY10Q2, FY10Q3, FY10Q4, FY10H1, FY10H2, FY11Q1) to be selected as Start Date and End Date. The Limitation on Start and End Date is that the End Date cannot be smaller than Start Date (for e.g.: If start Date is FY09Q4 the End Date can be FY09Q4 and above but not FY09). Now I have a separate set of Initiatives/Objectives for each time period (each Initiatives are marked in excel sheet 2) and Initiatives depend upon Start Date Selected so once Start Date is selected only the related List of Initiatives should be published.
Now the Validation and Naming I have used is not allowing me to use a list name as “FY09” and “FY10” as they are cell references in the Workbook. But I cannot also change the name to something as FY_09 as the data that would be there in the Template will finally be used without editing for upload to an application.
I have a main "ControlSheet" with a list of client and info in Columns A,B,C and E
In Column D of this "ControlSheet" I enter the Staff member who the client has been allocated to (e.g "Staff1")
A B C D E
1 Ref Data1 Data2 Staff Date Client Allocated
2 102 1000 10001 Staff1 01.06.2013
Each staff member (there are 7) has their own tab set up (e.g Staff1, Staff2, Staff3 etc)What I would like to achieve is when I choose the relevant staff member in column D of the "ControlSheet" (Lets say D2) I would like the Data from A2,B2,C2 and E2 of the "ControlSheet" to be copied to the next available/empty row of the Staff members own tab.
So in this example (above) A2-E2 would be copied to the "Staff1" tab as that is the staff member chosen If I was subsequently to change the Staff member chosen on the control sheet from say "Staff1" to "Staff2" I would then like the Data or Row to be removed from the "Staff1" Tab and added to the next row of "Staff2" Tab.
I hav a 2 columns. Column A contains data for 2 engineers, "paul" and "john".
Column B contains the hours they work each day for a month.
i would like to filter "johns" results and find out how many hours he works in total. i do not wish to use the autofilter, is there an easier or quicker way of filtering it???
john 11:03:08paul12:04:09john 15:11:44paul18:09:00john 05:34:24
Using columns, (A,B,C & D)Column "A" is used for main grouping (defined as "zone") which I've used conditional formatting to define that there's duplicates. However the subsequent columns B&C are to and from devices. There may be instances of inverse duplicates. How can I either use "If then" formulas or LOOKUP function to note these occurrences? Column "D" is merely the flag column to display the instances of occurrence.
EXAMPLE
ZONE DEVICE TARGET
138 [TRF] 1-2 SCR 1-2 G 3287XT12G 51GT12G
138 [TRF] 1-2 SCR 1-2 G 351GT12G 287XT12G
138 [TRF] 1-2 SCR 1-2 G 3287XT12G 51LT12G PH 2
138 [TRF] 1-2 SCR 1-2 G 3287XT12G 51LT12G PH 3
138 [TRF] 1-2 SCR 1-2 G 3367 12G DTT287XT12G
I have a workbook with three sheets.
Sheet 1 is a large data array (approx 8 columns x max 400 rows), than contains the inventory and cost information of a list of products in a running cafe.
Sheet 2 is is a recipe analysis tool that I want to be able to use data validations to cost out recipes, drawing information from sheet 1 in three dependent drop down boxes 1- category, 2- supplier, 3- item name, which then returns that items cost per standard of measure.
Sheet 3 is where I have started to try to generate a unique list for category and supplier to assist sheet 2 in referencing from sheet 1.
There is data overlap across the inventory items, with many items having the same category and or supplier.
Examples that I have found online use the index and match function, in conjunction with naming to return the array required for the drop down, however most of these examples I have found contain simple unique lists.
I cant seem to nut dynamic naming, which is integral as the list is expanding and contracting all the time.
So far I have been successful by manually naming a range (not a satisfactory solution as per above) and then using the indirect function to reference that named array from sheet 1.
See the attached example. I have created a dependent data validation. The list available in B2 is dependent on the item chosen in Cell A2.
What I would like to know is how do I expand this validation down columns A and B. I don't want to have to add the validation in each cell individually!
I cannot seem to add more dependents using this code. I think it is in the "If / then / else" structure that it is limited to only those three "groups". But I need to be able to add at least one more dependent list to the code.
View 4 Replies View RelatedI'm still curious about the sample from Contextures, http://www.contextures.com/excelfiles.html (DV0032 - Dependent Data Validation Combobox)
I have made a modification from its file (sample is attached). First drop list can be autocomplete, but the second cannot. For the comparing, I made row 2 in ValidationSample sheet is my modification, and row 3 is the original.
