Please see the screenshot : Excel-comments-misplaced-bug.png
when I filter my list (around 1000 records) and I want to edit a comment, the comments are at their original (unfiltered list's) place. is it solved in 2013 version ? or anyway to solve it ?
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies View RelatedI am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
Excel 2010 windows 8
I am in need of a formula to count filtered results. On Sheet1 (Job Flow) the user enters data continually . The most important is the Date in column D some measurements in columns N,O & P that gives a result as a code, example s15020 or c3005 in column Q. A vlookup is then pulls up a price for the code.
On sheet two Column A There is a list of all the codes. I need a formula that will give a count in column B for the amount of codes that was entered in to the data base "sold" for a date filtered time period, be it a week, month or year. In other words the codes in ,column A sheet 2, should reflect the amount of units for the time filter on page1.
I am using Excel 2010 on XP. I would like to apply conditional formatting to shade every group in alternating color. For example, I have a list of US states. I have a formula already to produce this:
Current Conditional Formatting Formula: =MOD(SUM(1/COUNTIF($a$2:$a2,$a$2:$a2)),2)=1
STATES (unfiltered)
AL
AL
AK
AK
AK
AR
AR
AZ
AZ
AZ
When I apply a filter, the rows remain shaded as they were originally:
STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ
I want the conditional format to change when I have filtered out items (DESIRED RESULT):
STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ
I would assume SUBTOTAL(3,...) would need to be incorported into the conditional formatting formula above, but I do not know how.
How we can select specific cells in the filtered data using VBA in Excel 2010.
I need to select 10th column, 5th row data .. or 10th column, 6th row data .. or 13th column, 8th row data...
How to select this data using VBA.. I am struck here .. If I give the below code.. the hidden cells inbetween the filtered data is getting selected.. only the first row is getting selected correctly.. i.e, ..rnVisible(1, 2).Select .. If I change the row like rnVisible(2, 12).Select its selecting the hidden cells ..
Dim rnVisible As Range
Set rnVisible = ActiveSheet.Rows("2:10000").SpecialCells(xlCellTypeVisible)
rnVisible(2, 12).Select
MsgBox ActiveCell.Address
[Code ........
I am working with a very large spreadsheet 10k references... I need to add sequential numbers in a column to identify the references but I need to use he filter in the author column due to the way my referencing software exports the data...
When I try to use the pull down autofill it just keeps repeating the last or second last number of the cell - the autofil box that usually appears has disappeared.
Im using excel 2010
I'm trying to copy a whole bunch of tables with identical layout to a master table, so I can create a whole bunch of pivot tables that include data from ALL the various tables. The source tables MAY be filtered, and I can't work out how to copy them easily while also INCLUDING any hidden/filtered rows while at the same time leaving any filter settings on the source tables intact.
If I use something like range("Table1").Listobject.DataBodyRange.Copy then it only copies the VISIBLE rows.
But I want ALL rows to be copied to a master table.
1. I don't want to unfilter the sources tables, because users might still want the source tables to remain exactly as the user filtered them. (However, it doesn't matter if the DESTINATION list is filtered or not). I realise that I could copy the entire sheet to a temp sheet, then unfilter any tables on that sheet and THEN copy these to the master list. But wan't to know if there's a simpler way.
2. I DON'T want to use SQL to create a pivot table directly from the tables, because the tables will have further information added to them from time to time, and so if I use SQL to make a pivot directly from them, I'll have to recreate the pivot cache using that SQL query each time, which might muck up the settings in any existing pivot tables. I realise that I could use SQL to copy the data to a 'staging area', and just point the pivot table at that.
3. I can't use PowerPivot, because its not installed in this environment.
I'm using Excel 2010. I have a spread sheet with sales data covering 3 years and multiple customers. I was able to create formulas such as this to calculate the figures for the entire sheet (all customers) by year.
=SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13").
I believe that in order to use filters that show this date for a specific customer I need to convert this to a SUMPRODUCT formula, I've tried this multiple times and had no luck.
I can't seem to find a way to copy specific highlighted/selected cells in a filtered list column and paste them outside of Excel 2003. i.e. in Notepad.
It seems to copy all the data between what is selected.
My Filtered list shows rows 5, 28, 35, 40 and 56
If I selected A5, A28 and A40 and select copy
If I paste it into Notepad, it adds A5, A28, A35 and A40
However if I paste my selected copied cell into Excel it works perfect and only adds A5, A28 and A40.
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View RelatedI'm using Excel 2010 on a Windows 7 machine.
I have one sheet with a large list of dates and total prices.
I have a second sheet with a list of dates and unit prices.
I want to divide the total price in my first list by the unit price on my second list.
I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.
My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.
So I have a guest list workbook. There are two sheets. On the first one is a list of names on Column A. On Column B is a classification: 'C' if confirmed; "D" for declined; "I" for pending. I want to have all the names with "C" on sheet 1 appear on sheet 2 automatically.
View 4 Replies View RelatedI am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View RelatedI have one excel workbook (called “workbookA”) stored on network device (hard drive with its own ip address). The idea is not to allow users to save that excel workbook locally on individual pcs; otherwise, others wont be able to see updates done by a particular user that saved the workbook on his or her computer (regardless of intention).
The idea I have is to have a second workbook (called “workbookB”) on the same network drive; and cell $A$1 of this workbookB is being referenced by workbookA.
$A$1 cell of workbookB has value of ‘20’. And, cell A8 of workbookA refers to that cell
My code below checks the value of A8 cell of workbookA against the value of $A$1 cell of workbookB has value of ‘20’. If the values do not match, cell A9 of workbookA displays “Unauthorized Copy”. (One can even elect to have the VBA code to overwrite all cells - thus destroying the workbook.)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("$A$8") "'[NO_NO.xls]SHEET00'!$A$1" Then
' places "Unauthorized Copy" to Cell $A$9
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets("FID").Unprotect Password:="password"
Range("'[FID.xls]FID'!$A$9").Value = "UNAUTHORIZED COPY"
Sheets("FID").Protect Password:="password"
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
I feel embarrassed, but this is the best idea I’ve got. Plus I am getting "Run-time error '1004'; Method 'Range' of object '_Worksheet' failed" & whenever the workbook is being opened, excel asks is the links (referenced) are to be updated or not (whole another story). Are there any simpler & more interesting ideas to ensure that the user gets alerted whenever the workbookA is removed from (saved in a location other then) the network device?
Data example is displayed below. I need to know how to find the average, not date average, but count. What is the average per day? Example on 2/9/2014 there are 12 entries but on 2/10/2014 there is only 1. Example: For all the lines of data that I have, what is average per day?
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
[code].....
I have a Macro in Excel 2010 which emails each tab to a distribution list.
However Outlook asks for permission each time to send the email, is there a way to disable this ?, or get Outlook to grant permission for a set amount of time ?
I have the following data in column H ....
Excel 2010H2Ms L Sentinella3Mr R and Mrs A Agarwal4Dr H M Bingham and Mrs G M VersaceSheet1
I would like to return (into columns I and J) only the surnames that exist in each cell, so it would return something like this ...
Excel 2010HIJ2Ms L SentinellaSentinella3Mr R and Mrs A AgarwalAgarwal4Dr H M Bingham and Mrs G M VersaceBinghamVersaaceSheet1
Notice, there are two surnames in H4, so one of the surnames would be returned into I4 and the other one into J4.
I'm able to write formula to strip out everything and return the surname but only if there's one surname in the cell, I can't figure how to do it when there are two.
I am doing a scatter plot of several columns and have the x-axis grid lines turned on. What is apparent is that the grid lines and tick marks are slightly off. In my case, when the point value is 6.004 the tick mark and grid line for the value 6 draws through that point.
I am using Office 2003. This was tried in Office 2007 as well with the same result.
I am using Excel 2010 on Windows 7. Here is a link to the worksheet I have a question about : Example.xlsx
The only two relevant sheets here are "TestScores" and "Area 1." What I am trying to do is copy names over from the TestScores sheet to the Area 1 sheet based their test scores. It is important to know that a passing score is anything 85 or above. If you don't pass Phase 1, you move onto Phase 2, and if you don't pass that you move onto Phase 3.
Let me first explain how the TestScores sheet works. Here is a screenshot of the sheet:
I copied in column C, the names, from a roster sheet. Columns A, B, and D all VLOOKUP information from the roster as well. You can ignore columns E & F. Column G is just a test name that is manually inserted. They are all in the same order since this is an example worksheet I am doing the program on before I input the real data. Column H is also manually inserted, with data validation to only allow values 0-100 and a few different relevant terms (MS, EXT ABS, N/E). These next two rows are the formulas in columns I and J, starting in row 3.
I=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(H3="","",IF(H3>=85,"N/A (PASS)","Need")))))
J=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(I3="","",IF(I3="Need","",IF(I3>=85,"N/A (PASS)","Need"))))))
What these formulas do is the following: If N/E, MS, or EXT ABS is in column H, it copies those over to the next two columns. If someone scores 84 or below, it says "Need" in the next column. If they score 85 or above, it says "N/A (PASS) in the next column(s). The user is meant to write over these to insert the phase 2 and 3 scores.
Here is the "Area 1" spreadsheet I am working on that has the problem.
First off, I created these formulas by comparing them to another post and replacing my ranges and criteria. In column A, it returns all the names of Team Members who passed in Phase 1 (have a score 85+ in "TestScores" column H). The array formula is below and it works great.
{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$H$3:$H$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(A$6:A6))),"")}
In column E I have a similar formula, except it returns the names of those Team Members who have "Need" in column I of "TestScores." This formula also works great. The formula in column D is just a VLOOKUP based on column E and works well, too.
{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500="Need",ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(E$6:E6))),"")}
Here is where my problem is. In column F, I want to return all the names of people who passed Phase 2, which means they have a score of 85 or above in column I of the "TestScores" sheet. However, not only is it not returning the right names, I can't even figure out what criteria the names is returns has. The current list, in (mostly, oops) red, is incorrect. The formula, below, is almost the same as the formulas above, with only 1 difference in each case.
=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(F$6:F7))),"")
It refers to column I, instead of H, compared to the formula in column A. Its criteria is >=85 instead of ="Need", in comparison to the formula in column E. Nonetheless, it still isn't returning the right names! The other weird thing, is if I replace the ">=85" with "=90" it will return the names of team members who scored 90.
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 RelatedI'm trying to create a set of three dynamic/dependent drop down lists of date values, based on a set of imported data. Depending on whether the preferred choice is to list all dates in a range or just dates on the 15th, the available start and end drop down lists will change.
In addition, if possible I would then like to display a list of valid dates (not sure if this needs to be VBA).
The attached sheet shows/explains what I am attempting but here it is as well:
If B3 is set to show all dates in the month then:
the first dropdown option for C3 will be the first date of IMPORT (D2)
the first dropdown option for C4 will be the C3 value, to the last date in IMPORT (D) column
If B3 is set to show 15th of the month then:
the dropdown for C3 will be the first date of IMPORT (D) column with a date of 15th the dropdown for C4 will be in the range of C3 to the last date of IMPORT (D) column with a date of 15th only dates of 15th will be listed in both C3 and C4 dropdownlists
General
the dates in IMPORT column D are taken from IMPORT column A, B & C, which will be imported and will always be date sequential the number of rows of date entries will change with every import but there will never be any blank rows between dates the values in IMPORT columns A, B & C will always be numerical, and can be referenced if required [ideally] the LIST OF DATES will be populated with the date range, based on the option selected in B3
I'm fairly familiar with Excel (2010) and I have also done some VBA but I'm new to dynamic, nested dropdown lists and I'm unsure what the most practical way is to achieve this.
How do i change the background colour in a list box?
An example is attached.
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
The setup of my worksheet looks like this:
Excel 2010ABCDEFG1Tch grpTchr Split27B-MA5AWAWAWAWAWAW37B-MA4CB1,JDCB1,JDJD,CB1MMF,JDMMF,JDMMF,JD47B-MA3SXGSXGSZBSZBSZBSZBTchGrps
A great chance to use the HTML Maker for the first time!
What I would like to do here is show how many of each value there is in the range.
So for 7B-MA5, it would say 6. For 7B-MA4, it would be 2 / 1 / 3, since there are 3 unique values and their counts respectively. Finally for 7B-MA3 it would say 3 / 3
A formula option would be great but I'll also take a macro option since it is a macro that generates these lists in the first place, so I could just add the new code to the end of it.
I have been using Excel to record the routine daily issue of items to different groups in a matrix layout, I use a different workbook for each month with worksheets for each group. The matrix takes the form of the item issued being the left hand column and the date issued the top row of the matrix, the quantity issued is recorded at the intersection. Each item can have a different quantity issued on different days. I'm using Excel 2011 for Mac but could use PC Excel 2010. Is there a way to convert the data held in this way to a list? What I'd like to achieve is a list showing the Item, the Quantities Issued and the the Issue dates
View 9 Replies View RelatedCreating a spreadsheet in Excel 2010 and am creating the dropdowns from a separate sheet in the workbook. There are no spaces and I don't know why I keep getting the "The Source currently evaluates to an error."
I am entering the Data Validation to reference the first cell I need "=INDIRECT(H3)"
I have put the spreadsheet on dropbox. When you select the "Sequencing Platform" drop down, it need to give just the dropdown for the platform selected.
[Code] ..........
How can I get the dropdown list to not include blank cells in the list reguardless if the sort filter is used or not.
Sheet 1 (STATS) is the dropdownlist Cell B12
Sheet 2 (Orders) is the data, I cant convert to tables because the cells are active and storted Column B2: is the data named.
so I have to use a formula to do this.
Using Excel 2010
See attached sample
Sample.xlsm
I am looking to insert a drop down box in Excel 2010 which will allow users to select multiple items. At the moment I have data validation drop down lists in other columns, however I need one column to allow for multiple selections.
Basically I have a drop down which allows for a region to be selected, my next drop down (in the next column) I need to allow users to either select "ALL", or multiple countries which fall within the region selected previously. I need a check box so that users can select multiple countries, and deselect if an incorrect country is selected in error.
I've defined a number of variables in my spreadsheet. When I want to use one I type "=variablename" and 'm presented with a list menu.jpg
In the above example, I have several variables starting with the word "Harvard" (a town, not the university :-)
How do I select one of these without having to double click with the mouse.
Up and down arrows allow me to highlight an item but I cannot find a keystroke that allows me to select the highlighted item. Instead I have to lift my hand off the keyboard and use the mouse.
Excel 2010
