Interrogate A Sheet For Specific Data Based On Months

Oct 18, 2008

I'm trying to interrogate a sheet for specific data based on months, I can't seem to set the input to the range any help much appreciated
Mike54

Private Sub updatestats_Click()

Dim Mth As Variant
Mth = InputBox("Please enter the month you wish to analyse")
Dim AL As Integer
Dim January As Range
Dim February As Range
Dim cl As Range

Set January = Range("B4:B57")
Set February = Range("B58:B113")

'March = Range("B4:B57")
'April = Range("B4:B57")
'May = Range("B4:B57")
'June = Range("B4:B57")
'July = Range("B4:B57")
'August = Range("B4:B57")
'September = Range("B4:B57")
'October = Range("B4:B57")
'November = Range("B4:B57")
'December = Range("B4:B57")

For Each cl In Mth

If cl.Value = "Annual Leave" Then
AL = AL + 1
End If

Next cl

Cells(4, 14).Value = AL

End Sub

View 9 Replies


ADVERTISEMENT

Summary Column Based On Specific Months

Jul 3, 2008

i've attached a file of what i'm working with. only the Data Entry worksheet and Data View worksheet are what i am concerned with

i'm building this billing workbook based on a monthly calendar. i want to have the summary column be a SUM of only the months billed thus far. for example, if the current month is november and i'm entering in billing information for the month of october, how would i be able to show a SUM of only months already billed? in other words, how can i summarize july:september, then see that summary in a column, and then bill october without seeing the effect on the summary column.

currently, the summary column is a summary of all months. i need it to be relative to the Current Date entered in the Data Entry Worksheet.

View 11 Replies View Related

Entering Data Onto Main Sheet That Automatically Transfer To Specific Sheet?

Feb 9, 2014

I remember years ago that I made a couple formulas that on sheet one would transfer to another sheet when I enter in sheet one.

View 5 Replies View Related

Move A Row To Another Sheet Based On A Specific Value (name)

Dec 4, 2013

I have a large document with a lot of names listed. I want to make up new sheets for each person listed. Is there a formula that I can enter that will move the entire row based on the person's name? See attached.

View 3 Replies View Related

Summing Values For Specific Months And Years?

Mar 27, 2014

I will set a base example first:

9 Nov 2012 Apple 5
12 Dec 2012 Apple 3
14 January 2013 Banana 8
17 January 2013 Apple 6
20 January 2014 Apple 3

I would like a sumifs formula that will only add the results for Apple - as follows:

Nov 2012 5
Dec 2012 3
Jan 2013 6 (i.e. does not include Banana - and also does not include Jan 14 Apple)

Although I have experience with sumifs I have not been able to use the correct syntax to identify the month AND year.

Ultimately I want to look through many hundreds of dates, and add anything that fits two criteria - 1) a 'name' criteria, and 2) it falls within a certainly month and year.

View 6 Replies View Related

Add Information To Specific Sheet Based On ComboBox Selection?

Jan 29, 2014

I have filled up my ComboBox1 with the sheets I want to add information to. My problem now is how to place the info in the correct sheet when I click CommandButton1.

I've been working on this for a few hours now. I got this to work on the first code I found but it didn't work afterward. I just deleted the whole thing and started again.

View 1 Replies View Related

Calculate Number Of Months In Specific Year Between Two Dates?

May 19, 2014

I am trying to calculate the number of months in a specific year between two dates.

For example.

Start date 01/06/2012
End Date 01/02/2013

Number of months in 2012 = 6
Number of months in 2013 = 2

How can I write a formula to give me the answer of 6 & 2 from the start and finish dates?

View 10 Replies View Related

Calulate The Number Of Years And Months From A Specific Date

Jan 22, 2009

I would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.

Such as someone reaching 40 injury free years in June of this year I can let them know.

View 9 Replies View Related

VBA Copy / Paste Specific Cells From A Sheet To Another Based On Condition (reference)

Nov 7, 2012

Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.

In attached file, see:
- worksheet ANALYSIS:
* datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2,
* then need to find this reference in worksheet called GROUP SAVE,
* and based on this reference, paste above cells value in corresponding "Indicators" column number

THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.

View 3 Replies View Related

Data From Sheet A To Appear In Sheet B Based On Data Posted In A Column In Sheet A?

Jan 1, 2014

I have a workbook where Sheet A is a Master Order Form. Employees will input the quantities they need in Column C. The sheet will be otherwise protected. Sheet B is a "printable" order form that only contains the rows from Sheet A where the Quantity on Sheet A is not blank and is >=1. This will allow me to print sheet B with no blank rows for the items I don't need.

I found the following formula which claims to be able to do what I want:

=IF(ROWS($1:1)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($1:1),C:C,0)))

I can't get it to work correctly across two sheets.

I've attached Sample of spreadsheet for review : HDForm_Test01.xlsx

View 6 Replies View Related

VBA Code To Pull Data From A Sheet Based On Date And Add That Data To Another Sheet?

Aug 8, 2014

I use my workbook to track sales data from one store to the next. I use my workbooks to compare data from year to year. Each year's data is displayed on a separate worksheet. '2013' has 2013's data, '2014' has 2014's. On the 2014 sheet, I have a Prior Year's Sales that pulls data from the previous year's spreadsheet using a formula which I just drag down each day I enter sales. I would like to automate this process and have the VBA code check for today's date and automatically pull the previous year's sales data from the '2013' sheet and put it in the appropriate cell on the '2014' worksheet. I hope I have explained this well enough to understand. I've included a link to my workbook for reference.

I had to use dropbox since I can't post a file over 1 MB. The file size is around 1.25 MB.

View 5 Replies View Related

Copying Specific Data To Another Sheet ..

Apr 25, 2008

I'm making a Task list with assignments for each of my team members. Columns A & B describes the ID number of the task, Column C describes the Task itself, Columns D to J are hidden, Column K describes the author of the task.

Column L is the one that have the name (or names) of the person who is in charge of doing that task. What I need to do is to create a macro that searches the name of that person in Column L and once it finds it it will create another sheet with the name of that person. And add to that sheet the entire row of his task..

View 14 Replies View Related

Getting And Modifying Data From Specific Row Within A Sheet With Userform

Jul 11, 2014

I have an excel sheet with a few buttons which open up separate user forms:

The first button "Add Exhibitor" works by bringing up a user form which enters data into the separate excel sheet called amends in the next available row, the data is initially selected by a stand number which if it already exists in the "amends" sheets brings up an error.

The second button "Modify Exhibitor" is what i am having the problem with. When the user clicks this button it brings up an identical form but for modification. What i need it to do is when the user selects the stand number in the drop down box: For example: H1-A-01, i need it to pull the data from the row in the "amends" data sheet which matches that stand number and put it into the user form so the user can modify and make changes, i would ideally like to be able to track these changes as well.

I am new to VBA and have tried many things so far online, i have managed to get the company name from the correct row and column to work but i cant figure out how i did it or how to make the rest of the data do the same?

The code i am using is below & an example of the form with data removed can be downloaded from here: [URL] ....

[Code] .....

View 2 Replies View Related

Auto Copy Specific Data From One Sheet To Other

Jul 4, 2006

I just want to check that if it is possible to copy specific cells from one row to another sheet by using macro or any other script.

Attached is a sheet which explains what exectly I want

The sheet one is having Source data
Sheet three is the result sheet

If I put some code numbers (in number form) it should search the data from source sheet and update the same in result sheet in different rows

View 9 Replies View Related

Copying Data Based On Specific Data

Apr 2, 2008

I have a worksheet named newvehicles with data in columns N to T. I have the branch names in column S.

I need VBA code that will copy all the branch names that are the same as well as their corresponding data i.e the data in columns N to T into a new worksheet with the same name as the branch being copied for eg if column S contains BR1, BR2, BR3, BR4, BR5 etc, then I need all the data for BR1, BR2 etc to be copied into a worksheet Br1, Br2 etc

The values are in columns P:R ("newvehicles"). I need these values to be added using VBA code as soon as this data has been copied accross. When pasted into the new worksheets, it can then be from the first column onwards

View 9 Replies View Related

Macro To Copy Data From One Workbook To Another On Specific Sheet?

Aug 28, 2009

I have two workbook having 8 worksheet each. Every day I need to copy data from previous day worksheet and paste it on new workbook with same sheet name.

For example :-
Workbook1 is having sheet named aa, gg, tk .....
Workbook2 is having sheet named as same as Workbook 1

Now, I want macro to copy data from Workbook1, sheetname 'aa' and paste the same in workbook2 in sheet name 'aa' and likewise

View 6 Replies View Related

Userforms - Populate Data On Specific Sheet And Columns?

Mar 25, 2014

i'm having a hard time with trying to populate data from a userform to a specific column under a specific sheet. I've attached the file if you want to look at it. The file is basically a exam type with user logging and auto score computing.

The file is made of 6 sheets, 1st sheet is the log-in page where takers are required to enter their ID number and last name. 3 sheets of dummy test items, an Admin sheet that computes the score per test item and Database which I'm trying to compile all data in one column.

What I'm trying to do is that when they enter their ID and Last Name and then answer the 3 tests, they will be recorded in the database sheet in 1 column containg their ID, name and all the test results so that checking,grading and identifying who took the test would be easy.

So far I think I've done the part for the scores but I can't seem to get the userform for emp# and last name to populate on the field marked in the database.

View 1 Replies View Related

VBA Folder Path - Extract Some Data From Specific Sheet

May 7, 2012

I am using some code to loop through a folder and extract some data from a specific sheet.

Now say a user moves the file to another folder etc... The macro will pull up an error, now is there a way i can have it dynamic linked to the spreadsheet so no matter what folder it gets moved to,

My folder path will adjust to that or need to take another route?

View 2 Replies View Related

Auto Populating Based On Specific Data

Jan 7, 2014

I have a unique task i am trying to achieve (when i say unique i mean i am out of my depth). I am trying to take specific data from a couple of different sheets to populate other sheets whether it be copying the text of fill cell (color)

Excel Automation Test.pdf

I have Attached a PDF detailing.

View 2 Replies View Related

Import The Individuals Data From The Master Sheet To The Individual Sheet Based On The Employee Name

Feb 6, 2008

I have a master spreadsheet that list several columns about employees(name, date, event name, etc.). Then I have an indivdual sheet for each employee. I am trying to import the column information for each employee onto their individual sheet based on their name. It is possible to import the individuals data from the master sheet to the individual sheet based on the employee name?

View 9 Replies View Related

Pulling Information In Seperate Sheet Based On Data In Current Sheet

Jun 1, 2009

I've got a bit of a quandary here that my novice programming skills can't seem to solve.

Here is the scenario. I work for a photographic library. We have a spreadsheet that contains 5 or so columns, and a couple of thousand rows. Essentially each row contains an imagine code (the number we use to file away the imagine, ie. US_NY_NYC_1 ) in the first cell, and the image information in the following cells (ie metadata, keywords, photographer, date, etc).

so a typical row would look as follows:

Filename | Keywords | Photographer | Metadata | Location |

all the keyword, photographer, metadata, and location information can only be tied to the one image code whose cell begins the row.

Now, here is my dilemma. This sheet (lets call it the master sheet) contains all the images we have in a given batch. However, when a client orders images, we will send them most images in that batch, but not all, and we may send a few additional. So I have a separate sheet that contains only those image codes that the client requested. What I need to do is essentially take a code from the second sheet, find that same code in the master sheet, and copy over all the cells associated with that code's row. If the number does not exist in the master sheet, simply leave that row with only the code in the first cell, and the rest blank, and move on.

so for example, in my second sheet lets say I have the following A_B_C . I would like for excel to find that same code in the master sheet, and then copy over the metadata, photographer, date, etc, associated with that code, and paste it into the second sheet, so that that row in the second sheet now looks identical to the row with the same image code in the master sheet . If A_B_C does not exist in the master spreadsheet, then I would like excel to simply leave that code in its own row, leave the rest of the row blank, and move on to the next code.

View 12 Replies View Related

Macro To Copy Specific Data From One Sheet And Paste Into Two Sheets

Mar 24, 2009

There's one sheet that has a list of customers (this is updated very frequently). These customers are either ‘New’ or ‘Existing (as listed in a corresponding column) - would it be possible to have a macro that, when run, will place a copy of existing customers into a second worksheet (“view list – new”), and a copy of new customers into a third worksheet (“view list – existing”), with the main worksheet still listing both existing and new customers.

As the main sheet that lists all customers is updated very frequently (with customers being added and removed throughout the day), would it be possible to clear all contents of the other two sheets that each list of 'existing' and 'new' customers will be copied into, before the two lists are copied into each of the two sheets (just to ensure there isn’t any data in there from the previous time each list was copied in).

I’ve given this a go via the ‘record macro’ function - some users of this WB use excel 2000, and others use excel 2007, but it only seems to work on excel 2007 (which is what it was recorded on)…

View 3 Replies View Related

Transfer The Data In Those 5 Fields To 5 Specific Cells In A Hidden Sheet

Apr 3, 2009

I am trying to figure out how to do the coding for a command button in a userform I've created. It has 5 textbox fields. I want the command button when clicked to transfer the data in those 5 fields to 5 specific cells in a hidden sheet. Then I also want that button to launch a word document.

Anyone know how I can go about this or where I can go that explains the specific coding?

I've tried searching with mixed confusing results & read through http://www.contextures.com/xlUserForm01.html#Top & http://www.theofficeexperts.com/down...ExcelDownloads examples.

View 7 Replies View Related

Sheet Of Data - Delete Rows That Do Not Meet Specific Criteria

Nov 29, 2011

I have a sheet of data. The data starts in row 4 and can be variable in length. I want to delete rows that do not meet a specific criteria, for example a location, Leeds, which is in column "i" of the table. I have written the below but it seems to delete data from row 1 rather than row 4 and repeat down each row

Code below

Sub deleterows()
Dim i As Integer
Dim intCounter As Integer
Dim strLocation() As String

p = Range(Cells(4, 6), Cells(4, 6).End(xlDown)).count

[Code] ..........

View 3 Replies View Related

Trying To Import Specific Data From A Separate Sheet To Add To An Existing Table

Apr 25, 2006

I'm trying to set up a macro which will import data from one worksheet to a master sheet. I need it to copy the information into specific columns but not overwrite any existing information which is already in the Master Sheet, but I don't even know where to begin.

Just so you're clear on exactly what it is I'm trying to do... I have a Master Sheet which lists all of our suppliers prices, margins etc etc... However, when we use a new supplier we send them a greatly condensed version of the Master Sheet - We call it the Supplier Sheet (no big surprises there)!

When the supplier sends it back to me I have to type it all out manually which is kinda time consuming. I'd really like to set up a "push button" system which allows me to simply drag the Supplier Sheet into the workbook, add the info into the Master Sheet, then be able to delete the now useless Supplier Sheet.

(I have attached a test copy of the file - all of the columns in blue are the ones which need the data adding to).

View 6 Replies View Related

Macro To Pull Data From Multiple Sheet To Main Sheet Based On Column Headings

Sep 13, 2012

I need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.

View 8 Replies View Related

Formula To Find The Sum Of A Set Of Data Based On Specific Criteria

May 20, 2014

automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.

View 3 Replies View Related

Returning A Certain Result Based On A Specific Data Range

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

Analyze Specific Data Based On Separate Criteria

Apr 22, 2013

I have a huge list of survey responses which asks people to identify their location and then rate how important 6 facilities in their area is (rating 1 - 4, 4 being most important). So the table has cells are similar to this, except that there are about 5000 responses with about 2500 different locations identified

Location
Location
Location

[Code].....

I have been able to analyse the average results for the whole dataset by using the following formulas:

=COUNTIF(A2:C4,"camden") Count number of occurrences a location is listed
=ROUND(AVERAGE(D2:D4),2) Average response for medical centres
=COUNTIF(D2:D4,1) Counts number of times '1' comes up, would repeat '2', '3', and '4' to create a pie graph

But what I would really like to do it be able to count each response for each question by location (for example "Camden"). I've tried both of these but they don't seem to be working.

=IF(A2:C4,"camden"),COUNTIF(D2:D4),1
=COUNTIF(D2:D4),1,IF(A2:C4,"camden")

View 6 Replies View Related

Moving Months On Summary Sheet?

Jan 6, 2012

I have this spreadsheet that is being used on a monthly basis, as the months goes by, I populate the current month with a downloaded data. My problem is, the current month is linked to a summary sheet. How do I update the Summary Sheet if, lets say last month Dec. 2011 I populated it last month with the SUmmary Sheet linked into it, then this month Jan. 2012, if I populate it, the Summary Sheet is still linked to Dec. not the current month Jan. 2012. Is there an easy way to "move" the SUmmary sheet as well as the months gone by? what I'm doing now is, I insert a new column in the column where the data is being linked, then copy/paste the data to the new column to preserve the numbers, then I overrite the current month download to the column where the SUmmary SHeet is being linked so that the SUmmary will be current. I find this process a bit manual and I should be watchful of the links because sometimes it doesnt work on the formula as I made the changes.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved