I have an array of data that I want to be able to filter based on the selection in a dropdown box. For instance, If I select district manager, I want to show the data of only the people who are successors for district manager. For my own reasons I want to use this method rather than just applying the standard filters to the column headings.
I have attached a sample workbook : DummyData.xlsx
I got the attached dashboard example from a source online.
I was wondering how they linked their pivot chart with a drop down (data validation). This is the pivot chart on the bottom right that is labeled "Brick Styles".
Dashboard_Your_Scorecard_Complete_2010-1.xlsx
This is my first time usng a pivot table. I need to link the pivot table containing three fields to the array of data containing five fields below. Here is a sample workbook. I would like to make it so that when you select a criteria in a field in the pivot table, it will filter the data in the array below. Attachment 249295 I haven't recieved a reply in the other forum probably cause very few people view that particular forum. Heres the link: [URL]...
View 6 Replies View RelatedI'm trying to work out how to allow a formula to be changed by pointing part of the code at a drop down list containing worksheet tab names from another workbook.
View 2 Replies View RelatedI've created a name range and i'm now trying to link this to a series of pics
For example
Sheet 1 I have in A1 the drop down box (to be about 50 long)
Sheet 2 I have all pictures named as per the list in sheet 1 e.g
Named dropdown list
Apple
Banana
Pear
Corresponding pictures named
Apple
Banana
Pear
What I am trying to achieve is when "apple" is picked from the dropdown list that the "apple appears say in C1 on sheet 1
PS. Forumula based such as IF or Index etc ican't use VBA or macros yet !!!!!
I need to link this summary sheet projects to this plant and equipment list sheet to show each project numbers separate weekly cost hire on the summary sheet.
View 3 Replies View RelatedI have two identical drop down lists (months i.e. Jan, Feb, Mar ...) in two different sheets but in the same workbook. Is there any way where the user changes the month in sheet 1 & the other drop down list in sheet 2 changes automatically to the same month selected by the user in the sheet 1 or vice-versa?
View 1 Replies View RelatedI've had a search on here, but can't quite find what i'm looking for. I have a cover sheet with a drop down list which has customers in it. under it I have the customers address, phone etc. What I am trying to do is when I click on a particular customer, I want the address and phone numbers to all fill in automatically. I have tried a few things, but to no avail.
View 12 Replies View RelatedI am trying to attempt to create a payroll spreadsheet with certain aspects and with multiple worksheets. so what im trying to accomplish is this
1/ on my main worksheet would have the payroll template there would be 2 columns (1) would be "routes" (2) would be the dollar value of that route.
2/ i want to be able to pick from a drop down list in colume (1) which will have various routes such as A,
B, C, D, E etc
3/ once i have selected a route in colum (1) i want to have the dollar value that is associated to that route to show up in column (2)
ex/ route A worth $1
route B worth $2
create a dropdown list but thats about it, i have not been able to link any values together.
Objective: To copy visible data from a filtered table (excel 2007) into an array.
Attempted solution: arr = Range("table1[#All]").SpecialCells(xlCellTypeVisible).Value
Problem: This approach works fine until the first hidden row in encountered, at which point the array ends. Visible rows beyond this point are not in the array. Could someone tell me what I've missed, or suggest a suitable alternative. I'm aware I could probably loop through the range manually (though I'm not exactly sure what to test - I can't get the range.hidden property.), but it seems like there ought to be a better way.
I have a sheet of data that I can filter (e.g. only show male pupils).
On the next sheet I want to display the list of filtered results with no gaps.
As the user will be able to select which column of data to show from the first sheet I am referencing it as follows:
INDIRECT("'Progress Matrix'!"&ADDRESS(MATCH($B$6, 'Progress Matrix'!$A:$A, 0)+1,
MATCH(C$6, 'Progress Matrix'!$1:$1, 0))&":"&ADDRESS(300, MATCH(C$6, 'Progress Matrix'!$1:$1, 0))))
(However this formula no longer seems to work as I think I changed something).
I've tried to incorporate
SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))
to only show filtered results but with no success.
Progress Tracker.xlsm
how can i store the values of an autofilter's list in a array using VBA.
View 4 Replies View RelatedIs it possible to copy data that has been filtered on one tab into filtered data on another tab? I've attached an example, i'm trying to copy the values from column C on the 'From' tab to column D on the 'To' tab. I think the data is always going to be an exact fit in terms of the number of cells copied from and to.
View 1 Replies View RelatedI am trying to pull data from more than one drop down but don't the same data to show if already use, example as follow:
First drop down Contains:- Pants
Shirts
Hats
Shoes
Dresses
if I chose Hats it should not show up in the second drop down
second drop down Contains:- Pants
Shirts
Shoes
Dresses
Is this done in data validation or combo Box? if so how?
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.
=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)
But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.
I am using Excel 2007 on XP.
I have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.
I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.
The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:
Sub Update()
Dim bottomrow As Long
Dim My_Range As Range
bottomrow = Cells(Rows.Count, "C").End(xlUp).Row
Set My_Range = Range("A1:Y" & bottomrow)
[Code] .....
I have been trying to find a way to sum up/only show the filtered data in a sheet.
i want to beable to filter the data by year and then only show sum up the data shown not
the entire column, is there a formula that will let me do this?
You have two Workbooks open. Workbook 1 and Workbook 2.
You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.
You then save and close both Workbooks.
You then open Workbook 1 and edit the data. You save and close Workbook 1.
QUESTION
Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?
I have a database sheet with a ticket list which has various columns like business area, ticket number, location, status etc. I am creating a new sheet where i want to highlight only the tickets that are in pending status and I dont want all the columns from the main sheet but just a few. How can I link it so its automatic.
View 4 Replies View RelatedI am trying to develop the best way to link two tables in two separate sheets in the same workbook in Excel. One table in the main sheet has a large amount of data in it, and the table in the second sheet collects selective date from the table in the main sheet. This makes it easier for the user to view the more important data from the main table. I developed IF functions which display the data from the main table into the new simplified table. The simplified table takes the data of each row from the main table in row order (row 1, row 2, row 3, etc.). Now the issue I am running against is that when I insert a new row within the main table, the second table does not see the newly inserted row.
Here is an example:
Say that both tables are sychronized upto row 40, and I decide to insert a new row with new data after row 25 in the main table. The simplified table will not see the new row because it is already past row 25, and the next row it will recognize from the main table will be row 41. Is there a way to have a VBA macro link the amount of rows in the two tables and have it so the simplified table will even display the data for the new rows in the main table?
A B C
1Timber$200A-rating
2Metal$500C-rating
3plastic$100B-rating
A1:A3 was input into combo box list. If I select an option in combo box, how can I make B11 and B12 to update data accordingly? (ie, if Metal was selected, B11 should show $500 and B12 should show C-rating)
So I have 2 spreadsheets of Car Inventory Data that I want to export into what will be printed out as Addendum Stickers...basically we're offering a new product with all vehicles and need to update the Sticker Price to reflect the change. The main worksheet with all of the data I'm trying to export is as follows:
Column A:
Stock Number
Example: (Column, Row A3) T12345
Column B:
Model Number
Example: (Column, Row B3) 1234A
Column C:
VIN Number
Example: (Column, Row C3) ABCDEFG12A3456789
Column D:
Retail Price
Example: (Column, Row D3) $20,100
Column E:
New Product Cost
Example: (Column, Row E3) $399
Column F:
Model Name
Example: (Column, Row G3) Camry
----
Now the price of the new product being added in is the same: $399. All data runs in rows (A3-E3, A4-E4, etc.). I AutoSum'ed each row and output the new calculated price into Column F next to each row of vehicles.
----
I want to take the Stock Number, Model Number, VIN Number, Retail Price, New Product Price and New Updated Total or Columns A-F and output them into multiple sheets:
Example:
Company Name (B1-C1)
I have yet another general design or best practice question regarding linking data between sheets in a workbook.
So the example scenario is we have 12 individual customer pages shown amounts invoiced and credited for the financial year.
I want to create an overall summary page.
Is it best to:
1. Use straight link eg. =Data!A1
2. Use Vlookup
3. Use Indirect with Address function
4. Use Database functions
I believe providing you have enough available memory that there isn't an issue with any of the above methods.
If you look at the attached picture, it is from a workbook.
The frame contains data depending on what tabstrip is chosen.
It is controlled with hiding rows in vba, how on earth do you get a frame like that which contains links to another worksheet?
I have created a form that pops up when I open a new template. In that form I have inserted a combo box that I want to be able to select data from a separate spreadsheet that is on our server. What code do I need to write to link this data to my combo box. Below is what I have written.
Private Sub UserForm_Initialize()
'Add list entries to combobox. The value of each
'entry matches the corresponding ListIndex value
'in the combo box.
cbSiteAddress.AddItem ("P:AccountsBilling Schedule.xls'MASTER SCHEDULE'!A5:A103")
'Use drop-down list
cbSiteAddress.Style = fmStyleDropDownList
'Combo box values are ListIndex values
cbSiteAddress.BoundColumn = 0
'Set combo box to first entry
cbSiteAddress.ListIndex = 0
End Sub
I have a pivot table with multiple supervisors and emloyees, I need to be able to find the average of a supervisors department and compare it with an employee with the employee seeing everyone else's data.
I don't have access to the original data that drives this pivot table. Is there some way I can use a calculated field to contain the average performance of all the employees under this particular supervisor?
In Worksheet A I have hundreds of Rows... Column 2 has a Type in it and not all rows will have a type and then there are various data elements in column 3, 4, 5 and 6
What I would like to do is in a worksheet B have a formula on each of the cells on a row that would look to worksheet A and would find the first row to match the type value in column 2. I would then want to lookup the value of column 3, and 4, and 5 and 6. Then in worksheet B, the next row the formulas in the cells that would also match type and would continue with the NEXT row in worksheet A that matched the type and again retrieve the appropriate cells. It would continue stepping through the rows of worksheet A until I had all of the now displayed in worksheet B
Basically I want to built a subset of worksheet A into worksheet B using the value of the Type column in worksheet A.
I suspect I am going to need to use multiple forumulas nested together in some way to achieve this.
Is it possible to filter a top * for already filtered data. E.g, i have sales people each having their total sales per month , what im trying to achieve is when i filter for only 1 person i want to get the top 10 sales months for that person.
View 2 Replies View RelatedI'm attaching a sample workbook which contains two sheets. The 1st sheet is titled "2008" which contains sample data in various columns & rows. The 2nd sheet is titled "print data", which at this time just has a similar, but less titles than on the sheet "2008". What I'm trying to accomplish (if possible) is when data is filtered on the "2008" worksheet, that the data will appear just as it is viewed on the "Print Data" worksheet. In a sense I want it to mirror the data on "2008". The "Print Data" sheet contains a lot less columns of information, which typically is not needed for written summary reports. I'm trying to minimize the amount of paper when printing, etc. I'm just not sure how I can accomplish this task without some type of macro or vba code.