Macro Which Picks Out Dates And The Data Relating To Them
Oct 29, 2008
At the moment all our work is logged by codes eg. A1Misc, B5Change, N9Important. As well as the codes, we enter the date in the column next to the code along with another Column to show the amount.
I need a Macro to look at the date column and only take dates which have Sept then go to the column next to it and pick up the codes individually and sum up all the amounts for the codes.
I have attached an example which shows from A13 downwards what I need the macro to show when finished.
View 3 Replies
ADVERTISEMENT
Jul 10, 2009
I have two workbooks kept open - ABC and 123.
I need a macro or a formula which picks up the workbook name - ABC
Simply I will always run a macro in the workbook 123. So i should get the workbook ABC automatically
View 8 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
Oct 4, 2009
I have worked out (through reading and what not) how to link a combo box to a range in a worksheet. What I need to do is relate what is selected in that combo box, to another combo box which picks from another range. I have a data spreadsheet called "Offices". It's set up as follows
(Sorry, it's really frustrating trying to format it to a table type thing but you get the gist)
ROW NUM______COLUMN A____________COLUMN B
---------------------------------------------------------------
1_____________DistrictOffice1_______...fice1@test.com
---------------------------------------------------------------
2_________________________________OFFICE 1
3_________________________________OFFICE 2
4_________________________________OFFICE 3
5_________________________________OFFICE 4
---------------------------------------------------------------
6_____________DistrictOffice2_______...fice2@test.com
---------------------------------------------------------------
7_________________________________OFFICE 2...............
View 4 Replies
View Related
Sep 11, 2009
I'm having difficulty creating a nested "if" or range formula that basically only reads the first two characters of the data in a given cell. See attached spreadsheet.
In column "A" I have listed the product ID. If the product ID has the first 2 characters 'DV' then it should yield the result of Dryer. The main issue is creating a formula that only picks up on the first two characters.
View 4 Replies
View Related
Jun 5, 2009
picks up the drifferences between low and high numbers for example 1-10 is low 11-29 high and 0 is a alternate so say i start typing in 1,2,3,11,15 in the top cell they start decending down the rows automtically and the program has 2 boxs indicating 3 lows and 2 highs and wen i type in a 0 it resets the two boxs to 0
View 5 Replies
View Related
Nov 14, 2009
I want to be able to define on excel 2007 what gets cut when I cut and paste
cause I just want text, comments, and font formatting, i dont want the fill of the background or border when I cut and paste
View 9 Replies
View Related
Nov 12, 2008
I've done some tab changes relating to a cell. HOWEVER: I find that once I've done the input, no amount of changing the data in the (proper) cell or recalculating!
First go-round:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target.Count > 1 Then Exit Sub
If Target.Address "L6" Then Exit Sub
Sheets(Sh.Name).Name = [L6].Value
End Sub
Second go-round:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(False, False) "F6" Then Exit Sub
ActiveSheet.Name = Format(Range("F6").Value, "mm-dd-yyyy")
End Sub
and last but not least:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
On Error Resume Next
Sh.Name = Format(Range("L6").Value, "mm-dd-yyyy")
End Sub
There are 53 sheets in the file.
View 9 Replies
View Related
Aug 11, 2009
I have been working on a spreadsheet with 3 sheets and have done everything except the last function which I just can't work out. Data is entered on the first sheet in a list with an item number, description, dates etc and then finally, if they are a certain type of item, a variation, a 'variation number' in a column.
The next sheet is then called 'variations' and here starts my problem. I want to have a formula that picks up if a variation number has been entered on sheet one in the 'variation number' column and if so, add that item into this sheet, in order.
So to summarise, I need sheet 2 to search on sheet 1 in a specific column for numbers 1-30(ish) for a series of items. I then need sheet 2 to list each of these in order, copying over each item that goes with the number from sheet 1 in the same format.
View 5 Replies
View Related
Jan 10, 2008
The thing is that i have alot of sheets (more than 20) and my program picks 2 values from the same cell locations on each sheet. This leeds to more than 30 arguments in my formula. I am gonna use this for alot of excelfiles constructed in the same way, but with varying order and names on the sheets.
View 9 Replies
View Related
Feb 12, 2009
I have a list of names in column A and I would like to create an index number based on these names. Due to other information on the sheet each name is repeated several times but I would like to assign the same index number to the same name. So that for instance, if there are 500 records but only 130 names thre would only be 130 index numbers.
View 2 Replies
View Related
May 11, 2006
I am trying to relate a specific cells day to a date.
for example:
Today's date is : 11 May 2006
Therefore the day would be: Monday.
I want to link the day cell to the date cell, so when the date changes so does the day.
View 2 Replies
View Related
Dec 6, 2007
I have a group of validation list boxes, all with the same choices, that when a user selects a certain item from them I want a popup box with extra info in it to appear. Is it possible for the user to click on the popup box once to make it disappear again?
View 6 Replies
View Related
Apr 30, 2014
I am having trouble with the following macro,
[Code] .....
When I press F8 and step through the macro, the StartDate and EndDate both seem to populate correctly.
The macro seems to go through the motions but returns NO results, unlike the recorded macro.
Macro code to "Clear Filter".
View 2 Replies
View Related
Jun 17, 2014
Where is that I have 2 sheets of data, sheet1 contains an ever expanding list of data and I'm trying to create a macro that deletes multiple rows of data from this sheet if the date held within column K is less than the date defined in sheet2.
View 4 Replies
View Related
Oct 6, 2009
Sheet1!A1 = Sat
Sheet2! A2 = I need this to equal Sun....
I tried =sheet1!A1+1 but got #value
View 9 Replies
View Related
Jun 21, 2013
I am trying to make a formula use an IF statement to identify different cells on a different sheet to pull raw data from.
However I've never tried formulas involving multiple sheets before.
This is the formula i get a circular reference for =IF('Store Input'!I6:J6=Lewis,(('Store Input'!C6:D6/1.2)/100*0.75))
I am not entirely sure what im doing thats causing confusion but ill explain the desired effect.
If a Cell has a value of "Name" then take data from cell "X" and do calculation /1.2 /100 *0.75 on a separate sheet.
Further more i want it to apply to multiple cell possibilities. so like the one above except repeated on different cells.
So as well as the above, i want it to perform the same operation for a set of cells further to its right and so and so forth.
View 1 Replies
View Related
Jul 16, 2007
I am using Excel 2002. I have a spreadsheet with a list of dates. How can I get conditional formatting to highlight the cell, if the date listed is before the current date.
View 9 Replies
View Related
Jun 16, 2014
Using the text to columns option on a comma separated file in csv format leads to the right preview in the text to columns wizard.
The column titled "ATTIC: Zone ..." shows the desired format in the preview window. Please look at the screenshot 1.jpg.
After pushing the finish button to obtain the result the number format gets suddenly changed and differs from the preview.
Again check for the "ATTIC: Zone ..." column as reference: 2.jpg
Is there a menu where one can look up, or specify how to format data to force the right comma placement? What settings might be wrong?
View 3 Replies
View Related
Oct 18, 2009
i have a spreadsheet that contains a column for each day and i need to input the count from another tab for certain data...example as follows:
column A shows types of fruit - apples, bananas or oranges, row 1 contains the date (todays date, weekdays only) - the count of each fruit needs to be entered in rows 2 (apples), 3 (bananas) & 4 (oranges) for each fruit for each date
i copy and paste a daily report into a new tab in this workbook -Raw Data (the data is always in the same format and the info i need is in column J...."555" = apples, "666" = bananas & 777" = oranges)
i have used the below formula which works:
apples =COUNTIF('Raw Data'!$J$5:$J$65536,"555") etc
however the daily report that is pasted into the Raw Data tab only relates to the current day (date is present in this tab in A1)...how do i get each daily column to only display a count if the date in row 1 matches the date in Raw Data tab A1?
also how do i keep the previous days' data to keep what the count was for that day (instead of counting the current count of the new data only relevent for today)?
View 11 Replies
View Related
Jul 11, 2014
I'm trying to do the following
Dim wkb As Workbook
Dim wkb2 As Workbook
Set wkb = ActiveWorkbook
Set wkb2 = Workbooks("F:SuppliersBT Monthly Invoice2014BT Macros.xlsm")
but get a subscript out of range error relating to setting wkb2
The file BT Macros is open - this is where the Macro is stored. What have I done wrong when trying to reference it?
View 5 Replies
View Related
Apr 30, 2013
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
View 3 Replies
View Related
Nov 21, 2013
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies
View Related
Oct 26, 2013
I copy/paste the data from this website [URL] ........
How I can sort all the data based upon the "Date"? The date data is not recognized when I copy/paste.
View 1 Replies
View Related
Mar 6, 2014
I need a macro as part of a bigger project that looks at dates in column "A" and deletes all rows that have a date older than today & yesterday.
View 3 Replies
View Related
Nov 22, 2008
I need a macro which will give me the number of lines between two search dates.
The dates are in the format DD/MM/YY e.g. 01/02/08
The dates are in Column F starting from F8 down the bottom ( which is always being added to).
I either need the macro to ask for the two serch dates, or I could just enter the two search dates in say cells A1 and A2
The serch could just be a "text" search e.g. look for string 01/06/08 in Column F make a note of the line number, keep looking down Column F for say 31/11/08, when found count the number of lines between the two.
View 9 Replies
View Related
Jan 9, 2007
I recorded a macro to custom AutoFilter 2 columns in a worksheet. The information will be filtered between 2 dates; i.e. >= cell B2 and <= cell C2.
Both cells are formated to dd/mm/yy so i entered 1/12/06 (for 1st Dec) in cell B2 and 31/12/06 (for 31st Dec) in cell C2.
When i enter these 2 dates, click the button to run the macro and check the custom filter in the "Planning" worksheet it appears that the dates change to 12/01/06 and 21/12/06 and no records appear - although i know at least 65 rows should show.
If i then enter the dates in B2 and C2 as mm/dd/yy instead (but leave the date format of the cell as dd/mm/yy) the filter works perfectly; i.e. enter 12/01/06 and 12/31/06. However I plan to issue this spreadsheet out to other staff and as every other date in the spreadsheet is in the uk format dd/mm/yy i dont want to confuse them by insisting they use the mm/dd/yy format for this one function.
Has anyone ever encountered this before? and if so is there some way i can fix it?
Heres the code for the filter:
Sub Monthly_Stats()
Sheets("Planning").Select
Selection.AutoFilter Field:=5, Criteria1:=">=" & Sheets("Filtered Statistics").Range("B2").Value, Operator:=xlAnd _
, Criteria2:="<=" & Sheets("Filtered Statistics").Range("C2").Value, Operator:=xlAnd
Selection.AutoFilter Field:=82, Criteria1:="<>"
End Sub
View 3 Replies
View Related
Feb 14, 2007
How can i make a macro that inserts todays date, tomorrows date, the next day and the day after that??
View 2 Replies
View Related
Jun 11, 2008
Im trying to display dates in excel sheet using macros. can anybody help me how to show Workday(x,x) in VBA.
View 7 Replies
View Related
Mar 10, 2014
My Process goes like this: We receive work in the excel sheet and in the column K(Period will be in YYYYMMDD)
First I want macros by which I can change YYYYMMDD TO MMDDYY.
then
It should compare column J and column K.
see the below example.
Period date (column K) Product end date (column L)
Column J column K
20131031 10/31/2013 = If column J & K dates are equal then "No action required"
20131031 7/31/2013 = If column J is higher then column K dates then "Action required"
20130930 11/2/2013 = If column J is lesser then column K dates then "No action required."
I need comments in column Q.
View 1 Replies
View Related
