Here is the original table:
This is with a filter on:
You can see there that some inserted object (in this case, PDF files shown as icons), are moved. I need to find a way to immobilize every inserted object within each cell boundaries. I want to freely sort or filter and avoid this kind of problem that gets worse with more rows, columns and inserted objects.
I am using Excel 2007.
I have a spreadsheet with about 30 items in 30 rows. I have a picture object which corresponds to the item # and other info in each row. When I sort the rows, the picture objects don't get sorted with the rows and end up no longer corresponding with the item # and other info.
I have right clicked and set the properties for each picture object to "move and size with cells". This does not prevent the problem.
Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:
Date Name Craft Experience ...more info...
If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)
I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.
Example (Excel 2003): ..
Ive spent a few hours adding email hyperlinks to data in column B.
I now want to sort the data in the worksheet into ascending order using the data in column A.
The problem is, none of the hyperlinks from column B sort with the data in column A, they are still in the cell locations I had originally entered them.
I'm having problems when trying to apply an auto filter to my data. When I select the critieria from the auto-filter drop down, on any field heading, I get this messgae "Fixed Objects Will Move" about 18 times.
View 9 Replies View RelatedI have a database that lets me add columns and do the required vlookups however, when I filter with a particular rule it throws the 'Cannot Move Objects Off the Sheet' error. Also, I cannot see any rows beyond row number 4316 - could this be the reason for the error?
Under 'Advanced' options, I've gone to 'Display options for this workbook' and chosen the 'All; option under 'For Objects show', however, I'm stick not getting anywhere
Does anyone know the maximum number of rows an autofilter can handle? I have a spreadsheet at work that started to crash at approximately 1500 - 2000 rows. The autofilter would give a weird error "Fixed objects may move" and then the filter would get rid of half the information and the filter arrows would show on a random row (rather than on the title).
I had heard a rumor that autofilter can only handle so many rows of data, but couldn't find where the cutoff point is. The spreadsheet is also shared between multiple people, so i'm sure that adds to it. And yes I know that Excel isnt' supposed to be kept open by multiple people and used as a database,
in the table i have
A1-apples
A2-bananas
A3-eggs
A4-balls
A5-bananas
A6-apples
A7-apples
I need one list that will show me all items that are on list so i would get:
A50-apples
a51-bananas
a52-eggs
a53-balls
to simpilfy this list would show me what types are there in the first list
We have an ISP based antispam system which can sometimes incorrectly pickup messages as Spam. I would like to scan through all quarantined messages every week to check that this does not happen. I can export a report from the program to excel but what I would like to do is remove subject lines with common spam words (Viagra, Russian etc) so I don’t need to check these, this would in effect reduce the report by 85%.
Ideally the ‘spam word’ list would be on the second sheet as in example and could be added to as I find obvious words.
The ideal end result would either be all lines which have a word from the ‘Spamwords’ sheet are removed or all lines which do not match the words in the spam list are copied to a new sheet.
I have a list with rows containing NAME, CLUB and TIME (A5:C124).
I'd like to be able to create a new list which would contain the fastest 3 TEAMS along with the combined time (SUM) of the fastest 3 times for each CLUB. Not all CLUBS would have 3 entries and these would need to be excluded.
I need to do sorting and filtering the similar records to new worksheet. I need you help to do this job, give some idea or command to use for the following steps.
I have attached the worksheet newtran.xls.
1. I need to find the last column, the column will be increased or decreases. so that we need to find the last column at time of running the macro. Now the value is there till column DD
2. I need to concatenate the column B, D, F to last column find out at 1 step. Insert a new column and place the concatenated value.
3. Entire row will be Sorted based on concatenated value column.
4. Find the similar row on concatenated column and entire row need to be moved to new worksheet.
I have an excel database that contains a code to identify specific people.
NAME ADDRESS PHONE CODE
Jones 3 Quay St, PN 063586954 JU79N4
White 24 Dyk St, PN 063547786 9GVJ64
Smith 9 Random St, PN 063512698 4LN867
Butt 89 Yeah Pl, PN 063569986 D920HK
Handle 69 James Ct, PN 06 3549687 ZK26S84
If I wanted to filter the list so I only had codes that had Z, N, H in it. How do I do that??
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
I have a very big Excel file (62 MB). I need to be able to filter by one column (FACILITY) and sort the whole file using the (REGISTRATION NO) column as well as finding the missing sequence number in the (REGISTRATION NO) column. The problem is that the (FACILITY) column has more than 200 different facility name. I am thinking of macro as I have different Spreadsheet I need to do the same steps in each one separately.
I have attached a copy (example from the data that I have) .
So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.
First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?
Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.
I'm trying to wrap my head around this. I have in a formula in one sheet pointing to a cell in another. Once I insert a row the formula adjusts. I need them to remain static.
I have tried searching, however I'm not 100% on the terminology.I have tried the F4 shortcut and put the $ signs in, however they still change.
Ive been trying to find a way of inserting a picture into a defined merged cell. Once I insert the pic I would like it to auto size/fit into the merged cells.
I have only managed to do this by either resizing the pic's first, or running a retro macro to resize the image. Just wondering if there was an easier way?
I need a macro that can search text cells in column L for a space delimiter, then if the space is present, to split that cell into a row inserted below it, then copy the information in the rest of the row down. I'm not really up on my VBA.
how VLOOKUP can be used to look up cells with auto shapes or drawing objects with a number next to it. I keep getting the value in return but the auto shape/drawing object is omitted from the answer. I use Office 2013.
View 8 Replies View RelatedI am using Excel 2010 and have the problem as shown in the attached file.
Input Sheet shows the Data I have at present
Output Sheet is the desired result.
I need a macro which should create an "Output" sheet by doing the following on the Input Sheet
1)Insert a Blank Column before Column A
2)Unmerge the Region Heading and insert the respective Region Name in the newly inserted Column. Region Heading will be in Bold Font.
3)Repeat Step 2 for all Regions
4)Delete the Rows which was merged.
Please note that the number of Data Rows will vary for each Region.
I have shown two Regions for explanation purpose only. There will be several Regions in reality.
The result is shown on the Output sheet
Merge Problem - Forum.xlsx‎
Range("B25").Name = "EndMull"
Its fine but if i insert a new row or column then it mucks the whole thing up. Is there away of naming them but if any cells, row or columns are inserted the range will automatically adjust to suit1
is there a way to stick a few row of cells together so that when i move one they all move.. i have some rows that when i sort them i want them to move together with the other ones..
View 9 Replies View RelatedI have exported a report to Excel from a BI tool called ZAP.
Unfortunately the cells in the columns are merged by group eg. salesperson, product, country.
I need to be able to filter by each of the columns - the only way i can think of is to unmerge and copy the information to the rest of the "merge area".
This will be extremely time consuming as there are more than 1000 rows and at least 5 columns that I want to filter.
Sales PersonCountryCustomer NameProduct Model DetailJohn WUNITED KINGDOMAPCBasicPremiumStandardStAPLAccessoriesAcrylic FsPremiumFsStStyAPOAccessoriesPremiumStandardStAPMFsPremiumStandardSt
I had column A and
a1 = 1/2/1990
a2= 2/5/2000
a3 =4/3/1999
a4 = #N/A(From vlookups)
a5 = 6/4/2013 and
a4 = #N/A(From vlookups)
How would i filter out just the #n/a's
Something like:
If this workbook.sheets("sheet1").cells(t,1) = #n/a then
run code
Not sure because it's a error not actually #n/a right?
I am having a set of data. Every month I need to filter for some values and replace data in some cells.
In the attached file I have given the data and also the filter conditions and what column I need to change.
creating a macro where in the filters and the changes to be made are in the table. The macro should use these conditions and make the changes.
I have a macro that turns off filtering option when it runs. What I want is for to first warn the user that rows have been filtered and the Macro will not run, then once the filters are switch off, the macro will run. It does the last bit at the moment but switches off the filtering of protected cells and once the macro has run the sheet can no longer be filtered .
Sub NewRowSQT()
Dim ans As String
ans = MsgBox("Ensure you have selected a cell in column B immediately below the section you wish to add to or the Macro will FAIL!", vbYesNo)
[Code]....
Learn about the "copy only visible cells" when I've filtered down a filter. Now Excel copies that way by default everytime I copy multiple rows when I've filtered. But NOW I want to copy everything that is hidden between the filter as well, i.e. how it did it before.
How can I tell Excel to do that?
I have a column that contains combined data of numbers and letters. I want to filter that column to exclude cells that contain the letter "d" or "f". I am trying to create a pivot table from the results of the filter to only display the ID #s that do not contain the letters "f" or "d".
The data inside the fields is not the same length and is not listed in a particular order. I have over 14,000 records I need to filter.
Data set example: The items in red are an example of the cells I would like to exclude from my worksheet. Is there a way to filter without deleting the data?
445-0706634F946
5886-K949-D001
445-0716622F946
445-0706634F887
497-0480473
445-9995967F025
6569-9788-2342
9016-1300
9016-C765-875
123-S765-87645
I have a spreadsheet where I need to lock certain cells, only problem is this then takes away the ability to filter the columns which I also still need in that spreadsheet.
Is there a way to lock cells so that they can not be edited but also keep the filtering function?
Is it possible to use an advance filter to detect blank cells? Or can the criteria be set to detect all cells that don't contain a letter e.g. "Y"?
View 3 Replies View Related