I have renamed some cells to custom names eg. renamed cell A1 to FLIGHT and B1 to FLIGHT2.
When I apply a filter to the columns and autofilter, the renamed cell now refers to a different cell! ie. FLIGHT no longer has the same text value in it
This also only happens when I autofilter by alphabetically or highest/lowest values
We have a sheet which we use to cost products. To ensure that people don't enter prices incorrectly, I have created a lookup to another sheet which shows current prices. Therefore, I have a macro that if I click a button, it looks up the prices from the relevant document. The way I have written the macro is to clear the sheet of what is already there and then to open up, vlookup and then close the lookup sheet.
The problem I had with this was that if i renamed the sheet, the macro wouldn't work - I sussed that one out by changing filename in the macro to 'ThisWorkbook'.
The problem I now have, is that the boss would like the sheet to magically do the following;
Lookup the prices on two external sheets. IF the main sheet doesn't have the corresponding reference THEN automatically go and Lookup on the other sheet for it.
He doesn't want any references to filenames so that, as long as the sheets are stored in the same place, they can be copied/renamed and moved anywhere.
Example: I renamed cell A1 to Apple, A2 to Banana, A3 to Chair. Down in cell a50 I was using simple formulas a1+a2*a3. then copying the formula into column b50, c50,... Now when I use the formula SUM(Apple+Banana*Chair) and try to copy it into cell b50, It doesnt change the formula to SUM(B1+B2*B3).
View 5 Replies View Relatedif A7="in" delete contents of Q7
the contents in the a column are "in" or "out"
the contents of the Q column are all different so it will have to be cell based
i will have to apply this to the whole sheet
Set AutoFilter Value Wont Work
View 3 Replies View RelatedI am trying to get autofilter to work based on cell contents.
Code:
ActiveSheet.Range("$C$1:$C$18371").AutoFilter Field:=1, Criteria1:=">=1.5" _
, Operator:=xlAnd, Criteria2:="
For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively. I found a relevant thread here, but the code is beyond my understanding.
View 3 Replies View Relatedhow to make this recorded macro work with more than the sheet it was recorded on. I need to work with the visible selections. But I'm having all kinds of problems trying to pick just the visible cells. Not having any luck with this project. I tried to included a test book but It keeps giving me an error.
View 4 Replies View RelatedI am using Excel 2007 Enterprise edition and want to use speak cells command (Text to speech in 2003).
I have added the command for these in quick access tool bar but when i press any of these buttons, all the buttons of this category disabled.
Using windows 7 Ultimate
I've created a custom toolbar and populated it with a custom button. I have created a macro and it resides in ThisWorkbook. The macro is assigned to the custom button and the toolbar attached to the workbook. So far, so good. If I copy the workbook and rename it, when I click the button to run the macro it launches the original workbook and runs the macro. The macro effects changes on the renamed workbook copy. The macro name found in the assign macro dialogue starts with the original workbook name. The same effect if the macro resides in a module or sheet. Any known way to have the macro name change along with the renaming of the workbook such taht it refers to the currently opened workbook macros? Regards.
View 5 Replies View RelatedI'm designing a form to look at data contained on a spreadsheet. I aiming for the user to select filter criteria which will then be used to reduce the data based on the criteria selected using autofilter. How can I set the filter criteria using a form to give the same options as when you manually select the filter using the arrows at the top of the row? Can I get the filter selections into a list box?
View 5 Replies View RelatedThis code does not work the way I expect it to. Presently it just causes Excel to freeze.
The idea is that if you double-click a cell within the CurrentRegion (starting at A1) that AutoFilters will be applied and the selection criteria will be the target value:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyRng As Range
Dim MyCol As Long
Set MyRng = ActiveSheet.Range("A1").CurrentRegion
MyCol = Target.Column
If Not Application.Intersect(Target, MyRng) Is Nothing Then
ActiveSheet.AutoFilterMode = False
MyRng.AutoFilter Field:=MyCol, Criteria1:=Target.Value, Operator:=xlFilterValues
Cancel = True
End If
End Sub
I'm using Excel 2007. I'd like to be able to autofilter on a particular value that I put in cell J5. How can I do that syntactically?
View 4 Replies View RelatedIf I apply an autofilter in my worksheet the autofilter dropdownmenu stands in row 2 and not in row 1. How is this possible?
View 3 Replies View RelatedIs it possible to retrieve an arrary of the autofilter criteria. I know this is possible in pre 2007 but with 2007 onwards I can't find a way of doing it.
I know I can get all the available items in a list by using the scripting.dictionary and also by using the visible cells I could see what could be filtered but that is not really accurate for what I want.
E.G.
If I have multiple columns and look at the filtered information in one column I can retrieve an array of the visible cells from that column but that is not necessarily the criteria that is in the column. i.E. If another column has a filter rows may be filtered that would have otherwise been visible.
Code:
Selection.AutoFilter Field:=5, Criteria1:=Array("CHF", "DKK", "EUR", "GBP", "NOK", "SEK", "USD")
I am trying to use VBA to filter a list for not equal to. See line above. I want to filter a table I have for unknown Currencies basically.
I got a problem using AutoFilter with VBA in Excel.
It works well for regular filters, but filtering the date column does not work as intended. The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.
Here is my code:
Code:
ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
:=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
"
I've got an AUTOFILTER and would like to add only the visible cells in a particular column (column E). As the user changes the filter, the total would change - but I'm not sure where to even start with this one. I've attached a sample file.
View 2 Replies View RelatedI know there's no way of autofiltering colors with Excel 2003 but it can use function in VBA to identify the colors like 3 for red, 6 for yellow and so on.
I want to be able to insert a button on the "Summary" sheet to asign with macro that canautofilter the numbers in color cells in the same column. Also with the codes I've put in VBA gave out strange numbers on blank cell which I don't want these awkard numbers to appear after typing in =GetInteriorColorIndex(whatever the cell I type in).
I know how to filter based on cell value, and how to auto filter "does not contain", but is it possible to combine these? i.e. Filter OUT the value of a cell from a range?
View 3 Replies View RelatedIm sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies View RelatedI have several sheets with about 250,000 rows per sheet.
But, even I sort by Column A, there are STILL hundreds or rows that are total blank interspersed down the page . . . I can't autofilter for blanks because there is too much data . .
How can I get rid of them?
I'm working with three large data sets covering a month of data in 5 minute intervals. There is a single date and time column which I am using some custom formats to list Time in 12 hour format, AM or PM and Day of the week.
Based upon the various analysis scenarios, adding some Autofilter columns is meeting most of my needs. One issue I am having is that I can filter on a single day using the built in list that Autofilter provides. But I have been asked to look at weekend vs weekday.
Since Excel 2003 only offers two filter criteria, I thought I could use a custom filter with Saturday and Sunday as include or exclude criteria. But, what I am findings is that while the Autofilter shows each day in the dropdown, certain attempts to use them within the custom autofilter do not work.
For example, equals Saturday and equals Sunday correctly returns only weekend data. However, if I try changing both to Does not equals, the result is that no data is filtered.Some of my research indicates that the problem is that the date information being presented as days of the week would need to be text strings . This seems to shed some light on the issue as any attempts at using wildcards fail.
I have data that is formatted in an Excel 2010 table. The two columns in question are [Invoice #] and [Description]. In the description column I have descriptions of products as well as freight. The same invoice number would be tied to the product description as well as its associated freight. I need to filter out certain product types and their associated freight items.
The macro I wrote creates an array of invoice numbers that I want to filter out and leave the remaining invoices, but I can't get the filtering part to work. Here is my code:
Dim Invoices() As Variant 'array of invoice numbers
Dim Descriptions() As Variant 'array of Descriptions
Dim InvoiceFilter() As Variant 'array of invoice numbers to filter
Dim i As Integer 'counter
Dim j As Integer
j = 1
[Code]...
What happens now is that it filters out all values in the Invoice column instead of only the values in the InvoiceFilter array.
I really like the slicers in Excel when working with data scenarios, but I don't like having to scroll up and down through the list of options.
Is there a way to add a "Search" box at the top like the traditional drop-down auto-filter? Or, does any loophole to making one?
I am using Excel 2013. I have an autofilter turned on for the columns in the spreadsheet. When I click on the filter button at the top of a column, the filter values do not have any check boxes next to them so I can't check any values. how to fix it so that it has checkboxes? I've attached a screen shot.
View 2 Replies View RelatedI have been working on some files in excel for several days, saving regularly, etc. but have run into a huge problem. I was marking cells with a background color to organize and track my work, but the files being saved were .csv's.
From my research, it seems that .csv's only save data, not formatting such as cell background colors.
However, while I was working on the files, they displayed the colors correctly etc.
I am hoping that there is some sort of temporary file created by excel that would allow me to re-open my work and have the coloring appear so I can save the work as an .xlsx file with formatting.
I've been having tremendous issues with my Excel file, mostly with it crashing or not responding. Would this be because my file has so much data in it? If so, is there anyway to make Excel process all the data without crashing so much?
View 6 Replies View RelatedI have been using a macro in Word created from pieces all over the internet (credit to a user named matt198992 for recursion script). The code prompts a user for folder, then runs a macro called Publish as PDF to all word files in the folders/subfolders.
I want to adapt the code in Excel, but I am having trouble. Error on the line "Workbooks.Open Filename:="Path & DirN".
[Code].....
I charge £45 per hour with a couple of conditions - minimum 8hr day and then 1.5T after 12hrs.
Is it possible to have a table where I put in my hours and then Excel tells me how much I should charge?