Excel 2010 :: Build A String For Criteria1 Field In VBA For Auto Filtering

Oct 3, 2013

Code:

ActiveSheet.Range("$A$1:$AM$14502").AutoFilter Field:=1, Criteria1:= _ "30/06/2013"

When I run the above on my Worksheet to Filter for Rows not equal to 30/06/13 this works fine

I want amend the Criteria1 to a string that is derived off a value in my worksheet I have done this as below where Range("D8").Value is 30/06/13

It is deleting al my data and not keeping the rows with 30/06/13 in it.

Code:

Dim rng As Range
Dim LastQtrDate As Date
Dim LastQtrDateString As String
LastQtrDate = shtControlTab.Range("D8").Value
LastQtrDateString = "" & LastQtrDate

[Code]...

View 2 Replies


ADVERTISEMENT

Setting Criteria1 / Range In Auto-Filter

Mar 2, 2012

Currently (as of Today() = 3/02/2012) in my code my autofiltered range rng is SET to:

? rng.Address
$A$4:$AJ$121

The next
rng.AutoFilter Field:=12, Criteria1:=""

View 3 Replies View Related

Excel 2010 :: Lookup Formula With 2 Criteria To Build Schedule

May 6, 2014

I have a schedule that covers 3 shifts for the entire month. I have another sheet that is used for crew sign in that I want to auto populate with all the people on that shift for that day.

I need to be able to use 2 different cells on the sign in sheet for reference for the formula (one for the day, and the other for the shift. ex. Day 3rd, shift D). I'm wanting to use an array formula to accomplish this. I've uploaded a stripped schedule as this is for the military and can't have any data on it that pertains to what or who it's for.

BTW our current method is to due it manually for every shift for every day that wastes about 12-16 man hours every week. I have excel 2010 at work currently. Access is out of the question and Macro's/vba may or may not work as security is always being increased on our systems so either a single or multiple formula is the route I'm looking to do that will work the the next few years I hope.

View 6 Replies View Related

Excel 2010 :: Filtering Results Into New Worksheet?

Sep 30, 2012

I am trying to set up a simple sports picking list using Excel 2010. I would like to be able to carry out several filers and then extract results to a new sheet to allow printing.

For example, there are four available terms and I would like to be able to filter by term/sport and student. Ideally I would like to be able to add a command button once this is working.

View 5 Replies View Related

Excel 2010 :: Filtering Subtotal Quantities

Sep 6, 2013

I'm using excel 2010.

I'm trying to filter a data set to see only those groups that have a subtotal count of 3 or greater. The subtotals are at each change in customer to count the number of divisions that customer is associated with.

In other words, how can I see a list of only those customers who have the permission to shop 3 or more brands?

Excel Help.xlsx

View 1 Replies View Related

Excel 2010 :: Filtering Multiple Columns (OR)

Jul 18, 2012

I have a worksheet with

Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)

There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.

I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.

For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.

I am using Excel 2010.

Name
Primary
Secondary
Tertiary

Person 1
x

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

View 3 Replies View Related

Excel 2010 :: Filtering Pivot Table?

Sep 13, 2012

I have a pivot table in 2010- is there a way to filter the data using an external reference from the pivot table? I'd like to put the value in another cell and have the pivot update automatically when I type a new value in that cell.

View 2 Replies View Related

Excel 2010 :: Function Or Formula For Advanced Filtering?

Apr 29, 2014

I am looking for a function or a formula that performs the same process in Excel 2010 as:

Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.

View 2 Replies View Related

Excel 2010 :: Filtering Dates And Counting Occurrences?

May 28, 2014

I am using Excel 2010, and trying to count the number of times a certain name occurs within a specified date period. The date range start date is manually entered in F7, and the date range end date is manually entered in F8. I need to be able to choose different reporting periods. The answer to this question goes into F3.

I also want to know how many working days (Column D) it takes per name. A working day is Monday-Friday, less public holidays. In this period there have been public holidays from 24 December until 2 January (inclusive), 20 January, 6 February, 18 April, 21 April, and 25 April. I put these dates manually into the worksheet "Holidays".

For the number of times a certain name occurs I tried the COUNTIFS function but keep getting a "zero" value. I used the NETWORKINGDAYS function for the working days and have incorporated the holiday periods. That seems to work OK and I can live with the negative date values showing here.

The really tricky part for me - can I combine the COUNTIFS (assuming it works) and NETWORKINGDAYS functions into a single function to only count the working days of a specific name. As some names have yet to be completed (i.e. there is no data in Column C) how do I not count those? The answer to this question goes into F4.

There will be about 6 of these spreadsheets, and I intend to link all worksheet data into a "master" spreadsheet which automatically updates whenever it is opened. The separate spreadsheets are stored in a document management system rather than a network drive so I can't state a filepath.

View 7 Replies View Related

Excel 2010 :: Pivot Table - Value Field - Multiply

Apr 25, 2014

I have a Pivot table showing costs of several types of items.

The issue is, the cost of an item is not in U$, it is in amounts of the unit value (example: Unit value of an item (cell A1) = U$500, and Item Cost (cell B1) = 2, the total cost is U$1000).

So instead of showing just the Item Cost (U$500) in the pivot table, I need to show 500*2 (A1*B1) which would be U$1000.

Can this be done in Excel 2010?

I don't want to create a new column with that multiplication, because I'm doing a monthly cost for the items.

View 3 Replies View Related

Excel 2010 :: Populating Field From Dropdown List

Oct 24, 2011

I am using windows 7, excel 2010 I have a work book, my price list is sheet one and my list of customers and what discounts they get per item in sheet 2.

I choose customer on sheet 1, say i choose a & L sales, well on sheet 2 it shows they get a discount of 45 percent. so I want the 45 percent to show in cell 4r on sheet one, but if the dropdown box shows customer a & V it should show 35 percent because that is what it is on sheet two in cell 3c.

sheet two has 158 customers and each get a difference discount for that product

View 3 Replies View Related

Excel 2010 :: Creating Mandatory Field Before Saving A File?

Aug 22, 2013

coding mandatory field in Excel 2010.

This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it

Sub NextInvoice()
Range("J2").Value = Range("J2").Value + 1
Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook

[code].....

I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.

View 1 Replies View Related

Auto Populate Field With Date When Other Field Is Selected From Validation List

Jul 4, 2013

In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.

Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.

Currently Col K has a validation list which comprises dates from 1st of each month from July to June.

I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.

The information is currently part of a Table in Excel 2007 that has about 500 records.

IdeasList

*
J
K

2
Status
Month Completed

3
In Progress
*

4
Complete
Jul-13

5
*
*

6
*
*

Data Validation in Spreadsheet

Cell
Allow
Datas
Input 1
Input 2

J4
List
*
=ValidationList_Status
*

K4
List
*
=ValidationList_Months
*

View 2 Replies View Related

Excel 2010 :: Pivot Table Add Calculated Field Based On Certain Text In Report Filter

May 29, 2014

Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.

I used the formula below in a calculated field and it does not match the values using the added column to the data file.

=IF(ISERROR(SEARCH("*transportation*",'Transaction Type')),Quantity, 0)

I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data.

View 2 Replies View Related

Auto Populate A Field In One Tab By Comparing Another Field In A Different Tab?

Jan 25, 2013

I have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.

I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.

View 2 Replies View Related

Pivot Chart - Page Field Filtering

Oct 2, 2009

One of the few flaws I have found using pivot tables is that when selecting from the the page field it does not filter the other page fields to match your current selection.
It does in the actual table, so why not the page fields?

For example we have a pivot table like the following
|Country| (ALL)
|City| (ALL)

|Count of People|
|YEAR| ________ |Gender|
_______________Male__________Female
2008__________1000 _________500000
2009__________9999999_______1


If I select England from the country, when I go to select a City I do not want to be able to select only the cities in England and not every city in the world.

The Attached File shows you how to deal with this Problem.

View 2 Replies View Related

Filtering Out Entries With Null Date Field

Nov 21, 2006

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.

View 9 Replies View Related

Excel 2010 :: Auto Complete With Dropdown List?

May 21, 2012

My Excel 2007 has "auto complete with a drop down list". When I type in the first character, a drop down list appears listing all the entries in that column tha start with that character. I click on one of the entries and the cell is "auto completed" with that entry. Sure is handy. I try to find that functionally in Excell at work, Office Professional 2010, and no bueno for kaki.

View 3 Replies View Related

Excel 2010 :: Auto Capitalizing Certain Cities When Entered

Jun 21, 2013

If I enter the city "Dunn" it auto capitalizes it. If I type my name "mark" in there it doesn't. I don't want it to auto "do" anything. How can I turn it off? I've tried copying into a new book and that didn't work so it doesn't seem to be formula driven. The issue is happening on a coworkers machine, but I cant seem to recreate it on mine. Hers is 2007 while mine is 2010.

View 9 Replies View Related

Excel 2010 :: How To Make File Auto Save To PDF

Aug 16, 2013

I have workbook I would like to auto save to PDF copy file in different location every time the original file is save maybe some VBA code

View 4 Replies View Related

Excel 2010 :: Auto Update Cell Data

Nov 1, 2011

I would like make a cell in a report auto update with the most recent data entered in another cell from an input table either in the form of a formula or code to ensure that the most recent data is recorded and reported.

View 3 Replies View Related

Excel 2010 :: Auto Summary From Non-Blank Cells

Aug 21, 2012

I have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 I want to create an auto summary or something.

Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.

Also I am using Excel 2010.

View 6 Replies View Related

Excel 2010 :: Auto Sort When Sheet Is Refreshed (F9)

Nov 6, 2012

I've only recently began to use excel, but I've really dived deep into it, I'm clueless when it comes to VBA but now I'm stepping into that realm. Anyway, I'm using this code....

Code:

Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Me.Sort.SortFields.Clear
Me.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Me.Sort

[Code].....

Code:

Credits to VOG

Link: auto-sort for excel 2010.... to auto sort 2 columns of data. However, it is extremely tedious to enter data as it sorts itself every time you enter a new data point. Moreover, using any random number generator to fill the columns takes ages! Is there anyway to make it so the code only runs when the worksheet is refreshed (F9 pressed.)

View 2 Replies View Related

Excel 2010 :: Auto Email Attachment Corrupt

Jan 7, 2013

It copies one of the work books, Saves it and then emails this as an attachement to a few people.

All users are on Excel 2010, however the file originally was created in Excel 2003. The File Extension Was .xls as was the attachment.

First time I sent the email this year it came through corrupt. No changes had taken place in the workbooks or the code.

I have now updated the spreadsheet and saved it as a .xlsm

I have also updated the code so the attachments are .xlsx

The email attachments are always corrupt.

Code:
Sub copy2()
Application.ScreenUpdating = False
Dim fname As String 'filename
Sheets(Array("To Order", "WO To Chase", "PO To Chase", "Contact", "WO Report")).Copy
fname = Year(Date) & " " & Month(Date) & Day(Date) & " Chase & Order" 'filename!!
ActiveWorkbook.SaveAs FileName:= _

[code]...

where the corruption might come from? Its not via the email as if I go to the saved file its corrupt as well.

View 7 Replies View Related

Excel 2010 :: How To Make File Auto Save To PDF

Aug 16, 2013

I have workbook I would like to auto save to PDF copy file in different location every time the original file is save maybe some VGA code is this possible ???

View 1 Replies View Related

Excel 2010 :: How To Auto Sort - Largest To Smallest Value

Nov 4, 2013

See post below. I want to have the column listed as per to auto sort to the largest to smallest value. This value will change throughout time.

I have data in columns a-n and rows 3-14. I want column "l" or the 12th column to auto sort from largest to smallest value.

View 1 Replies View Related

Excel 2010 :: Set Row Height Based On First 14 Characters In String?

Feb 23, 2012

Excel 2010; I am setting row heights on the basis of some parameters. I have the following statement;

If targetCell.Value = "Photo Comment: " Then
targetCell.RowHeight = 185

But what I really want is to recognize only the phrase;

"Photo Comment:" in a text string that might be much longer e.g,

"Photo Comment: The photo above depicts yata, yata.........."

So, I want to recognize the first 14 character as "Photo Comment:" and then have the row height adjust to 185. So it involves "Left" and "Len" (I'm thinkin') but the syntax is beyond me.

View 1 Replies View Related

Excel 2010 :: Nth Character In A String - Parsing Shortcut?

Feb 1, 2013

I have an Excel sheet with lots of shortcuts in the first column. It's the entire shortcut, including the .lnk extension.

What I need to do is to be able to parse out the string to make the shortcut human readable when I create the hyperlink. I'm saving the output as HTML for a user to open these shortcuts. It's on an internal LAN & secure. Using various versions of Excel (2010, 2007 & 2003, predominately the first). Trying to make an ugly, 140+ character shortcut into a more readable form for web navigation purposes.

Up to a point, the shortcuts are similar:
a123meworkfolder1folder2folder3shortcut1.lnk
a123meworkfolder1folder4folder5shortcut1.lnk
a123meworkfolder1folder6folder7shortcut1.lnk
a123meworkfolder1folder8folder9shortcut1.lnk

but, later on, they vary from that point out.

a123meworkfolder1folder10folder10Afolder11shortcut1.lnk
a123meworkfolder1folder_12folder12Afolder12Bshortcut1.lnk

The strings are the same up to the 7th occurrence of the "" character, but after that, the foldernames start to change & the shortcut may be down a level (an extra folder) or more. Also, the lengths of the strings change as well, so I can only do positional parsing up to a point.

View 4 Replies View Related

Excel 2010 :: Auto-copy When Highlighting Multiple Cells

Jan 30, 2013

Recently ran into this problem where when I select(highlight) multiple cells, it automatically copies the highlighted cells.

[URL]

This auto-copy also occurs periodically when I select(highlight) a single cell.

Notes:

1. I do not have a copy hotkey on my mouse, as this also happen when I use the trackpad.

2. This only happens in excel. When I select multiple items on my desktop, it does not auto copy.

View 1 Replies View Related

Excel 2010 :: Auto-increment Alphabet With Existing Data?

Mar 16, 2012

I have made a custom list in Excel 2010 so it auto-increments the alphabet using the auto increment pull down/copy square on the bottom right of a cell in Excel. My question is I have some data in a sheet such as this

600
600
600
600
601
601
601
601
602
602
602

What I want to do is add a letter of the alphabet to the end of these numbers and have the alphabet auto increment based on the data above like this:

600
600A
600B
600C
601
601A
601B
601C
602
602A
602B

I thought it would be simple since I now have a custom list but every formula I try fails.

View 5 Replies View Related







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