Excel 2010 :: Extract Hyperlinks Using Advanced Filters Using A Table
Jul 4, 2014
I'm creating a database that contains a columns of hyperlinks and background color.
I've created code on Excel 2010 with advanced filters to copy selected rows to another worksheet. The extract works fine and those cells with hyperlinks and various color codes from the original database "appear" to have been extracted correctly.
However, a closer look shows the hyperlinks on the target worksheet are just blue underlined texts...with the links no longer working. Is there a way for me to correct this?
View 9 Replies
ADVERTISEMENT
May 29, 2013
I've got an old Excel sheet with Stephen Bullen's function for returning the active filter criteria (Rob on Programming: Excel: Displaying Autofilter Criteria). My status sheet may be filtered in multiple ways, and when the user is happy with the filter selections, she can create a powerpoint file with a graph and a summary of the filtered table. As we are using Excel 2010, users are very likely to select more than two filters.
Example: Range A1:E100 has the following headers: Field, Installation, Project,Type, Phase.
The controller wants to filter on:
Field equals north or south or west Phase equals completed
The manager for Field South wants to filter on:
Field equals southType equals maintenance or repair or modification Installation begins with Zeus.
As the filters are not shown when I copy the table to powerpoint, I would like to create a summary of the user's active filters that is pasted into a sheet (for subsequent copying to powerpoint). For the users in the example above, that table would look something like this:
Controller:
Active filters
Field: north, south, west
Phase: completed
Manager, Field South:
Active filters
Field: south
Type: maintenance, repair, modification
Installation: Zeus*
I've looked at various functions intended to take Stephen Bullen's code into Excel 2010's multiple criteria world (e.g. this: User Defined Function to Display AutoFilter Criteria for More Than Two Criteria in Excel 2007 / Excel 2010), but I have not been able to convert it to a functioning macro.
Any code that could be used for this sort of task, or any tips for relevant code?
View 2 Replies
View Related
Jun 7, 2006
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 Related
Mar 25, 2013
I would like to obtain a list that is the equivalent of the list that I get when double clicking in the cell of a PivotTable.
IF i have for example
I would like to obtain all the lines that have "d" in column 4 in one table.
Is that possible with just formulas?
I got the number of occurrence in the series by doing a countif on column 4
I am trying to understand some formulas that include indirect, address, small, if and row but don't really get them...
I got this far but it doesn't work:
=INDIRECT(address(small(if(Fahrzeugpark!$AD$5:$AD$5000=AA21;
row(Fahrzeugpark!$AD$5:$AD$5000));column()-1)+1-row(AQ1;2)))
View 2 Replies
View Related
Mar 3, 2013
I'm trying to extract a list of names from an Excel 2010 table based on two criteria, thus:
=IFERROR(INDEX(Database[FullName],AGGREGATE(15,6,ROWS(Database[Age]-ROW('Database'!$A$2))/((Database[Age]>1.8)*(Database[Age]
View 4 Replies
View Related
Dec 24, 2013
Using Excel 2010
How can I use 2 filters in DAX Calculate?
I tried
MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field] && Tbl2,Tbl2[Field2])) It works with 1 Filter : MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field])) Just falls down when I add the second filter.
View 7 Replies
View Related
Apr 19, 2012
I was asked to create a Waterfall chart in PowerPoint, which stumped me both in its purpose and how to create it. Is this a standard chart, an add-in, or a chart created by modifying a standard chart?
Two related questions:
What is the best resource (book or web) for advanced charting in Excel/PowerPoint 2010? I just ordered "Charts and Graphs: Microsoft Excel 2010" (MrExcel Library) by Bill Jelen because it had the Waterfall chart in its index. That chart isn't in the "Excel 2010 Bible" or the older "Excel Charts" book by John Walkenbach and is only mentioned briefly in Jelen's "Excel 2010 in Depth."
I'm an advanced Excel user and was wondering whether there are still more charting options in Excel than there are in PowerPoint. I know that used to be the case, but it seems in 2010 that PowerPoint charting has nearly the same functionality as Excel charting or are there charts that are best done in Excel? (...putting aside linking/updating functionality).
View 4 Replies
View Related
Jul 10, 2009
I am having some problems trying to filter a list to display exactly what I want to see. The list has one column of part numbers, a second with due dates, and then another with quantity.
I want to use an advanced filter on the part numbers to only look at unique entries. Then I want to filter that list using a custom filter on the due dates to only view those due within a certain period. So ultimately I want to view only unique entries due during a given period.
I am able to apply one filter, but when I go to apply the second, the second filter removes the first. For example once I have filtered out duplicates, when I try to filter based on date all of the duplicates return.
View 7 Replies
View Related
May 29, 2007
I need to use the Advanced Filter tool to allow the user to filter (in-place) the Guests worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.
To automate the operation of the advanced filter tool, I need three macros. "SelectRoom" and "SelectGuest" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllGuests" should clear the criteria row and dispay all clients in the database.
The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllGuests" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Guests worksheet!
View 3 Replies
View Related
Oct 17, 2013
Using MS Excel 2010.
As I understand it hyperlinks set in a spreadsheet should work regardless of where you save and/or copy the spreadsheet i.e. as long as the directory location of the location / file the hyperlink is referencing doesn't change (notwithstanding, the user must have appropriate access rights to the relevant directory locations)
However, the situation I have is the spreadsheet was generated and saved to directory location A (intention being this would be the 'master' yearly template) and the hyperlinks set, all of which work when you open the spreadsheet in this directory location. But when I copy the spreadsheet to directory location B, which is intended to be the yearly 'register' location for the spreadsheet, none of the hyperlinks work? When I hold my cursor over a link in the copied spreadsheet, in directory B, I do not get a 'pop-up' path like I do in directory A and when I click a link I get an error message:
Microsoft Excel The address of this site is not valid. Check the address and try again.
As stated above the documents the hyperlinks relate to have not moved, they remain in the same template directory (i.e. the link path has not altered).
View 1 Replies
View Related
Aug 21, 2013
Hyperlinks are used to navigate internally throughout the workbook on a menu page and back. The hyperlinks started acting up after i saved to xlsm. Have since saved it back to xlsx but now the hyperlinks randomly disapper despite re-adding them over and over again.
View 2 Replies
View Related
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
Apr 27, 2009
using VBA I have a control sheet which summerises variouse counts & totals of data held on a detail sheet. Bu using filters and counting the visable rows.
Statistics on 50 columns of data held in several thousand (rows) mixed around eight business regionsheld in first column.
I could determin the number of affected rows by using Tick boxes on the control sheet and applying filters to the detail records
A) checkbox indicates if I need filtering on the type of data in my detail sheet and apply the filter
Selection.AutoFilter Field:=XX, Criteria1:="Y"
B) Because I could not have more than two criteria on an autofilter column I resorted to using Advanced Filter on the column with the Business UNIT's,
I Create a range write the criteria of the records to be filtered into the range, then apply an advanced filter using that range.
Both of these work well indevidually, but I am getting inconsistant results when I mix them
using the autofilter route I can select multiple tick boxes and the output is correct, and using the advanced filter I can select any combination of business units and the output is correct, however I cant get them to work together
View 4 Replies
View Related
Sep 17, 2008
I can't seem to get the hang of advanced filters with multiple criteria. BTW, I have no problem solving for a single criteria. I've found many instructions on how to do it, but zero specific syntax examples, and I'm obviously doing it wrong. For example: ....
View 9 Replies
View Related
Jan 26, 2013
In Excel 2007, Hyperlinks in cells pointing to jpg files in a subfolder display with internet explorer. On my WinXP/Intnernet Explorer 8/Office 2007 system these links work fine.
When sending the excel and jpg files to another person with Win7/Office 10, jpg hyperlinks work and launch Internet Explorer but come up blank. (Hyperlinks to PDF files work fine and show up in Acrobat, just the jpg's come up blank in Explorer.)
Questions:
What is causing this, a setting in Explorer/Win7 or Excel?
Can I change Excel hyperlink default software to use something other than Explorer to display the jpg files?
View 5 Replies
View Related
Jul 18, 2014
I have created a spreadsheet that will show me where people are working on what day, etc. however i want to be able to filter by week to create a list of say 2 particuar shifts - in this case "syl ld" and syl n so that the spreadheet would show the people who are working these shifts and I could print out. I have tried Multiple Filters and Advanced search but cannot achieve what I require
View 3 Replies
View Related
Jun 26, 2013
I'm Using Excel 2007 and would like to have some VBA to work with the following!
I have a simple pivot table (PivotTable1) in Sheet1 with three items in the Report Filter which has been named "ROUTE"
I have created a ComboBox in Sheet2 and have added the identical three items in via format control, cell link A1.
I would like to be able to use the combobox in sheet2 to operate the PivotTable Report Filter in Sheet1 as I would like to build a report whereas a user. Can only select the comboBox and does not see the pivotTable
Sounds simple but cannot get this to work no matter what I try.
View 5 Replies
View Related
Jun 12, 2013
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
View 2 Replies
View Related
Feb 28, 2007
I have a mixed list of materials in column 'A'. I have two macro/filters to extract (copy/paste)... one for those ending in "[G]", and one for all others (those not ending in"[G]"), to columns 'B' & 'C' respectively. The code looks like this;
Sub Filter_G()
Range("B2:B" & Range("B65536").End(xlUp).Row + 1).ClearContents
Range("A2:A" & Range("A65536").End(xlUp).Row + 1).Select
Selection. AutoFilter Field:=1, Criteria1:="=*[G]"
Range("A2:A" & Range("A65536").End(xlUp).Row + 1).Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Range("B2").Select
Selection.AutoFilter.......................
View 5 Replies
View Related
Apr 26, 2012
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
View 3 Replies
View Related
May 31, 2012
Excel 2010ABCDEFGH1EMPLeaderSamAsbertNoellaJackson2RosalineSam3LionelAsbert4KerryNoella5JohnnyNoella
6AliAsbert7RosalineSam8TimothyAsbert9TimothySam10ReginaldAsbert11PascualJackson12MichaelSam13ReginaldJackson
14MeganJackson15ShellySam16CandiceSam171819Sheet1
Here's my problem... Column A contains employee name & column B contains Team Leader name of the respective employee. Range D1:G1 should contain names of team leaders from column B. After that, depending on the name populated in D1, cells going downwards from cell D2 should contain name of employees of that team leader. Similar thing should be repeated for columns E, F, & G. If an employee shows up under two team leaders then it should be displayed under both lists.
Is there any formula/VBA code which can do this? Been after this for last 3-4 hours and now just lost in a maze of various Index Match combinations and array formulas..
View 9 Replies
View Related
Oct 30, 2013
I have lots of DVDs and decided to catalogue them using Excel 2010. I made use of 3 Sheets. In Sheet 3 there are 2 lists: Category (PG, M18, General, Adult) and Language (English, Chinese, Japanese, Korean, Spanish). Sheet 2 contains the data under the following headings: Title, Category, Language, Genre. I used Sheet 1 to create a drop down list for selecting a Title and then the information of the title are displayed, that is, its category, language and genre (I used vlookup). This is working fine.
Then I created another drop down list for Category. I want to be able to select a category, such as M18, and then a list of titles that fall under this category will be displayed. However, when I tried using vlookup with index, I failed to get the display of titles.
View 1 Replies
View Related
Dec 15, 2006
I have copied a webpage into excel It contains URL javascript link.
a) I wonder if I can copy this link out without keystroking each row.
b) When I point at the link, a few numbers come out. I want to extract these number out.
I am thinking of creating a macro so that it can be performed automatically because I have more than 300 rows.
View 5 Replies
View Related
Jul 9, 2012
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
View 1 Replies
View Related
Nov 2, 2008
I am trying to use an advanced filter to extract records that meet the criteria in the blue input cells. I can't get the criteria correct to allow me to meet the 3 conditions. There are duplicate names in the list so I will need to use unique records only option....
View 9 Replies
View Related
Oct 30, 2006
I am now trying to get the Index worksheet to extract data from the sheets that it has indexed, for example, it returns the sheet name, but i want it also to return the value that is contained in C2 and C3. Is this possible? I have attached the code which i am using below for ease of reference.
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index...................................
View 2 Replies
View Related
Jan 20, 2012
What criteria can I use to filter a list on and item and the latest date?
I need to extract a list of all items but only with the latest date of each ...
View 3 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Apr 28, 2014
I had a workbook in Excel 2003 that i just moved to 2010. In the 2003 file I had 2 pivot tables, one each on a worksheet, looking at the same data, just grouped differently. One yearly, one quarterly.
Now in the 2010 workbook whenever I change the grouping on pivot table, the other one changes also. It's like they are linked together or something.
View 3 Replies
View Related
Dec 14, 2012
Excel 2010. I have two very large tables of data:
Table 1 is a long list of unique items in no particular order, each of which belongs to a unique group of items, for example:
ItemsGroup ID
apple
pear
grape
fig
banana
guava
peach
mango
Table 2 lists all the Group ID numbers and in the same row as each Group ID number, shows the names of all the items belonging to that group, one item in each column. Different Groups have different numbers of items in them. For example:
Group IDItem1 Item2 Item3 etc.
214applepeargrape
33fig
7bananaguava
59peachmango
Is there a formula I can use in the second column of Table 1 to look for each item in Table 2 and tell me what group it is in? For example for "apple" or "pear" or "grape" it would look in Table 2 and return the Group ID number "214".
View 4 Replies
View Related
