Is it possible to create independent filters. I have data for five departments and sales for different quarters of the years. These sales for the different quarters are independent. But If I select Q1 for first department and Q2 for second department it do not give me the result that I seek i.e. sales for first department for Q1 and Sales for second department for Q2.
View 9 Repliesbuilding dynamic charts and filters, so What I'm hoping to accomplish to build a bar chart that looks like the following:
________________________________________________
Filter: State | Filter: Region | Filter: Segment |
Revenues |||||||||||||||| + (% of Total Rev)
COGS ||||||||||| + (% of Total COGS)
Margin |||| + (% of Total Margin)
-------------------------------------------------------
My data is built in the following format on a different tab than the proposed chart:
State: | Region: | Segment: | Account: | Amount: |
MN | Midwest | Major | Revenues | $$$
MN | Midwest | Major | COGS | $$$
MN | Midwest | Major | Margin | $$$
MN | Midwest | Major | % of Total Revenues | %%%
MN | Midwest | Major | % of Total COGS | %%%
MN | Midwest | Major | % of Total Margin | %%%
[Code] ........
It seems like it'll be easy to use a pivotchart, but at the same time, there's a lot of formatting to do, which makes it tricky, especially if I have many states to deal with and the multiple combinations.
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies View RelatedI have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
View 9 Replies View RelatedIf the issue is not immediately clear:
If I select, for example, columns B:E and group (Data -> group and outline -> group). These can be hidden/unhidden easily with the line and + button created in the process. A similar set of columns (e.g. P:S) will behave in exactly the same way. If I have grouped columns B:E however and then try to group F:H, I end up with one large group spanning B:H.
If it is possible to create these 'separate' or 'independent' adjacent groups, I'd love to know how; if not, I'd be interested to know if anyone else has found this irritating
How can I make the files path independent. Lets say I have 6 workbooks, one of them having macro that connects all the six books. While working on my pc, I entered the file locations by myself. The macro runs fine.
Now I have to send the files to someone and would not like them to add their download directory at most places in the code. I want that after downloading, they have to only open the main file and press the button to run macro. How should I do that.
Right now am using this approach:-
sub main{
str 1file;
set 1file = Workbooks.Open ("c:/downloads...../1stfile.xls")
and similar code for calling other files.
I have two spreadsheets which carry the same information in three cells. Instead of just placing a link, =B2 etc., I would like to have the cells linked to each other through code.
For the one sheet I can use this with no problem, but as soon as I place this worksheet change event in the other sheet but reverse the cells references Excel gets unhappy.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("B2, M2, N2")
[Code]....
Any proper way to update a cell no matter which spreadsheet the change takes place?
Sheet1 >> change is made to cell B2 and that same value is applied to Sheet cell C4.
I need a macro that can autofill irrespective of where the source cells are. Like, this is what I want to do:
Select two cells containing a descending order such as 50, 40. Hit the hotkey and the macro should autofill the same way as double clicking the bottom right corner of the selection.
The problem is that the two source cells can be anywhere in the sheet, and autofill range isn't fixed. Essentially, I just want a keyboard shortcut for Excel's own Autofill function.
I want this macro to be rid of the mouse when I'm working on excel (it really slows me down to use the mouse every time I want to autofill).
I am working with two tables in one sheet. The first table requires seven columns. Under this table, i want to have another table that has 14 columns right under it so that their widths are the same. Is this possible? It would be like having two sheets in one.
View 2 Replies View RelatedIn the attached spread sheet, i have two set of data, for two different months.Columns A to D have the value for the first month and columns F to I have the values for the second month. In the first set of Values i have the dollar value in the column "D" and in the second set of values i have the dollar value in column "F".
I want a macro, to compare these two cells and arrange the data accordingly. If both the values are the same, then both the lines corresponding to the values can remain in the same row. If one value is greater that other, then the set of values which have the lower dollar value should come to the next row. I have attachec the sample data. The lines highlighted in green is the raw data and the one in yellow is the finished data.
I am developing a Excel gantt chat i have chosen to display the bars by the lenght of the cell because the data is also most real time so as the time reduces so will the bars however the problem comes when there is tasks being done under one above it as i cant have the cells with different widths
What would be the best way to get around it as i want to keep the almost and the appearance of the real time nature of the cell width equal to the value.
The following code works fine if the data is sorted from oldest to newest. I need for it to work when the data is sorted from newest to oldest. For this to work, I would need a change that works something like this: "total1 = total1 + close_(a, 1) * a" would be "total1 = total1 + close_(a, 1) * b" with "b" incremented in the opposite direction of "a". I code very infrequently & have tried a lot of different things, searched the Internet, etc. all to no avail.
For instance, with "close" data in column "I", the data sorted as follows works with the formula
"=WMA(OFFSET(I2,0,0,9,1) )" to correctly return "791.50", but the opposite sort returns the wrong number.
The correct calculation is: (807.19*9+798.38*8+793.81*7+793.06*6+776.28*5+769.48*4+773.2*3+789.01*2+793.87*1)/(9+8+7+6+5+4+3+2+1).
DateClose
11/12/2012793.87
11/13/2012789.01
11/14/2012773.2
11/15/2012769.48
11/16/2012776.28
11/19/2012793.06
11/20/2012793.81
11/21/2012798.38
11/23/2012807.18
Code:
Public Function WMA(close_)
total1 = 0
n = WorksheetFunction.Count(close_)
divisor = (n * (n + 1)) / 2
[Code] ..........
I'm trying to write a formula that will return the sum of multiple cells within a larger matrix, based on a number that is manually inputted into a separate table. As you can see in the top below, I have data that is grouped by both "Zone" and by individual "Stations". The Zones/Stations on the y-axis are "Start" points, while the Zones/Stations on the x-axis are "Finish" points. What I'd like to be able to do is input a Zone ID number into the bottom table (Shown as "1" below) and have the formula either return the greatest sum of "Finish" numbers. In this example, the greatest sum is 12, which is all numbers going from Station 1 and 2 in Zone 1 to Stations 1/2 in Zone 2 (3+2+4+3). In the bottom table, you'll see the "Greatest Finish" as "2", which is Zone 2. And the total from this sum is 12, as described previously. If there is a way to return either the "Total #" or "Greatest Finish" zone.
The actual matrix I am working with is much larger (150x150), and not all zones have the same amount of stations. I'm assuming this might complicate things a bit.
Finish
Zone 1
Zone 1
Zone 2
[Code]...
I extract text into a new column using =Left(). I want to make that column permanent. That is, I want to delete the source column but keep me extracted column. How?
View 2 Replies View RelatedFor the past 4 months I have been desperate to find a way to split my sheet so that I can determine the column widths above and below that point independently.
For example:
small width from a1-b1
but
really large width from a2-b2
Is there a way to do that?
From every sheet I want to create the same type of graph, so a macro could be very useful.
First I recorded a macro. The worked fine, but it could only be used by only one worksheet. Every time I used the macro it returned to the original worksheet where I created the macro.
With some searching I found out how to change the code so that the macro could be used for the active sheet.
But now I get stuck with the following error: "compile error: invalid qualification" (translated from Dutch).
With error tracking I get stuck at the first line "Sub grafiek()"
I have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.
Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file
I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.
The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->
Joe A 1001
Joe A 1002
Joe A 1003
Jill B 1001
Jill B 1002
Jill B 1003
To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.
My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?
Code:
Sub AccessList()
Sheets("requests").Select
Dim rng As Range, c As Range
Dim rng1 As Range, c1 As Range
Dim rng2 As Range, c2 As Range
[Code]....
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies View RelatedI'm trying to set up a dependent cell (B) based on what is in the independent cell (A)
If A is blank, B should be blank
If A has any value other than X, B should display a set string Y
If A contains X, B should contain a drop down list with a range of choices.
I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.
is it possible to use two filters within a the same field.
ie. right now i can filter using the top 10 based on a value field.
what if i want to see the top 10 customers profit and along any drop in revenue from previous years..
something like this.
the information below was copied out of a pivot table that included the % difference.
Code and Name
YearCheck
Profit
Difference
[Code].....
I have a worksheet that is unprotected but I am unable to apply filters to it. The option is greyed out if I try and select autofilters.
Another strange thing is that on one of the worksheets when I select Data, Filters there is a tick to the left of Autofilter but it is also greyed out, so in effect I cannot turn it off even tho I can't see the filters!
I have tried to popst a screen shot but unsure how to do it. I think maybe a screen shot would help to explain my issue better so if anyone knows how to do this......
Let's say that A1:C5 looks like this:
Square Red $5
A2 is empty Blue $6
A3 is empty Orange $2
Circle Puple $10
A5 is empty Black $18
A2 and A3 could say "Square" and A5 could say "Circle" ... this is just how I have it set up right now.
In C1 I have a drop down list ... a list of "Square" and "Cirlce".
***QUESTION***
Is there a way for a user to select "square" from the drop down list and in D1 have excel populate a list of all the Squares colors and prices?
Example of what excel would populate in D1:E3:
Red $5
Blue $6
Orange $2
The thing is that I don't think filters is the answer. The reason being is that I want the user to be able to select the item from the list and have excel automatically populate the information. The user should not have to filter the list manually.
In my Sheet I have AutoFilter on multiple columns.
I want to view all rows where in different colomns there are values.
For example
Colomn D, Row 6 has a value
Colomn E, Row 6 has no value, But Colomn E, Row 7 has a value.
I want to view then Row 6 and 7, by setting an OR filter 9 or something like that .
by setting filter on Colomn D with ( not empty Cell ) and Filter on Colomn E with (not empty Cell ) it is only a AND function.
How can I get an OR function. ( Colomn D OR Colomn E )?
I'm trying to modify this code in order to do the following.
I want to choose first column and then the criteria for filter, then i want to choose a second column and criteria to filter the remain values from the first filter.
Here is my
Sub Filter()
Dim Myrange As Range
Dim CriteriaVal As Variant
Dim CriteriaVal2 As Variant
Dim KillColumn As Integer
Dim KillColumn2 As Integer
Dim ActiveColumn As String
Dim AC
Dim LastRow As Long
Dim rng As Range
I have several pagefields. Once something particular has been selected in one of them, I would like the remaining options in the other pagefields to be updated or refreshed showing only those where there is a correlation with the selected pagefield. In other words, exactly how your typical Excel filter works.
I assume there is no option to change the way pagefields interact to be like filters. How could this be done in VBA?
First post so please excuse me if this has been covered before but I am trying to get the results of a dropdown menu in one worksheet to become the criteria for a filter in another worksheet (both in teh same workbook).
I thought I could just add a reference to the dropdown cell into the autofilter (in VBA editor) but it doe't seem to be that simple.
Any ideas?
I am using Excel 2007. I have a list of 100 names all ranked from 1-100. Is there a way to create a macro that can filter out the top 20, a specific name and also any names with the cell colours blue and yellow?
At the moment using an advanced filter based on criteria I can filter out the top 20 and the specific name I want but can't work out how to leave the coloured rows in as well.
Subtotal doesn't add cells hidden under a filter column but it does when grouping. How can I get groups to change a subtotal based on whether they are hidden or not. What I'm really trying to do is use conditional formatting to change the format when a group is expanded vs collapsed.
View 3 Replies View RelatedI'm creating a spreadsheet with data I've exported from a survey.
My data begins column headings on row 10 and spans across several columns. I've applied filters to the headings.
Above column 10 I have some summary data using the countif and counta functions. Is there away to have my summary fields change as I apply my filters to the main body of data in different ways?