How to get the colour name by RGB value?

Excel

• Hi Having a bit of a problem with printing with Excel. I have itemised the area on the worksheet I want to print and it is a single page. When I print the page and also when Preview the page it only prints or displays the top 2/3 of the required info. The highlighted area on the sheet indicates should fit on my page an A4 page. I originally thought it may be the margins, but that is not the problem. Got any ideas? I did make it into a Macro but until I find out what is happening it is pointless automating the print. Thanks Eric Bayne QUESTION: Dear, Excel 2007 Suppose, I have anfile with 5 columns: Student Name, Nationality, Country, City, Hobbies. Let's say we have data of 100 students (100 lines). I need the following (not through the built in filter): 1. When I write down, name or country or city or hobbies to give me the whole line that contains theI wrote. 2. Will it be possible to get the auto-complete word, I mean while writing to suggest me the rest of the word. 3. Will it be possible to get all lines that contain the same word, I mean when I write France, it will list all Student living in France. Imad ANSWER: * Yes, what I would do (whether or not this is the fanciest way to do it I'm not sure, but it sure is simple!), is put vba code in the worksheet module. Worksheet_Change. Target it to the cell you will type in. If the value = "France", then loop through column C (for example), and if the value is France, then entirerow.copy, to the next available row in (wherever you want it). ---------- FOLLOW-UP ---------- QUESTION: Thanks, but I am not so familiar with VBA, would you please form it and quote it to me. I have two fields with different data. Column A has a 10 digit non repeating number that will always have 45 at the start and column F has several different dates. I need to find a way to count how many of Column A are populated within a data range I tried to put an example below. I need to find a way to count the items in column A within the date Rance 04/21/10 - 04/26/10. For the example below The correct answer should be 3. Hope this helps. Column A Column F 4500044229 04/26/10 4500044119 04/28/10 (Shouldn't count because this is not in the date Range Blank Space 04/30/10 (Should not count because column A is blank. 4500044333 04/21/10 4500044222 04/26/10 I tried using =SUMPRODUCT(IF(C131:C137>="45",1,0),IF(D131:D137="04/21/10-04/26/10",1,0)) But that didn't work. Robert QUESTION: Bob I'm very sorry about yesterday. I didn't even realize the mistake until after your last message. You are correct with the blackout oct 15 to may 15 my example 60 day inspection would be on june 15 not april 15. Soo sorry. ANSWER: You may have to modify this -- it assumes the date in C2 will never be inside the blackout period. the value of 213 in the formula is the # of days in the blackout period. for 30 days: =IF(AND(C2+30>=DATE(YEAR(C2),10,15),C2+30 =DATE(YEAR(C2),10,15),C2+60 =DATE(YEAR(C2),10,15),C2+90 I love your answers. If I have the number 50 in A1 and the number 10 in B1. In C1 I want to have the amount from A1 plus add .50 cents for every 1 that's in B1 (meaning since the number in B1 is 10 I want to add $5 so the total would be $55) How would I do that? ANSWER: Sydney, Hopefully you will like this answer. If I put this in C1 =A1+0.5*B1 I get 55 and that seems to reflect the rule you describe but but it could be a coincidence. If that isn't what you want, post a follow up with where I have gone wrong and with a clearer description of what you want (give me a couple of examples with results and why those results). ---------- FOLLOW-UP ---------- QUESTION: Yes you got it. Great answer, and I rated you accordingly. another question. Easy for you I'm sure! Which formula do I use to do the following? If cell A1 totals less than 12 numbers then add as many 0's necessary to the beginning of the cell to make sure it totals 12 numbers. Thanks again QUESTION: I have andocument that I am working on and need to figure out how to work out the following scenario....I have several tabs with different information that I need to have communicate with each other. Tab A includes a list of all people who have attended a health fair. Tab B includes a list of all people who have attended a seminar. I need to be able to see if any people who attended the health fair also attended the seminar without having to flip back and forth between tab A and tab B. Is there a way that I can get tab A (or a new tab if necessary) to recognize if John Doe attended both events and in turn compile the data from both? ANSWER: There are a few functions you could use to see if an item in one column appears in another column. For example, you could use the MATCH function to see if the value in column A1 appears anywhere in column C: =MATCH(A1,C:C,0) If it appears, you'll get a number (indicating which postion the value is in). Otherwise, you'll get an error (which you could supress with the IFERROR function). Hope this helps. ---------- FOLLOW-UP ---------- QUESTION: What if the values I am trying to match are in two separate tabs? And then if the same information exists in both tabs then I need it to transmit a date from the second tab into the first tab. How would I do that? im wanting to know if there's a simply way to find out if on anspreadsheet with a list of approx 9000 addresses theres a way a finding out any which are duplicated which i know some will be. I can use either windows xp or windows 7. Many thanks Mandy I use2003, i want to transfer data from worksheets in an another file. all seems straight forward, however the name of the file could change due to the date changing. the file name would be dropped in to a specific directory. I would like to be able to use a cell to put date into and this would look up the file. Additionally this would be YYYYMMDD "filename.xls. is there a way of putting together the date and the text file name and open the file to get the cell data? I have a column inwith about 4000 items in them and i would like to add the manufacturer name to all of the products at once as they all are from the same manufacturer. Is there a way I can add this text to all of them at once? I have created a suite of 5 spreadsheets that interlink which keep track of elderly residents in occupancy with staffing and income. All spreadsheets are seperate files and each has numerous pages divided into months and years. each new month I have to copy page to new month and then do a search and replace on each files new month to update the lookups from other files(ie January 2010 new page needs all references looking for dec09 changed to jan10 etc. Can you advise how I can use the tab name(being January 2010 and so on ) in the formulae in each page which looks up info. ( I would like it so that When i create a new page and add tab name I can get this in a cell on the page. If all the lookups to other files and pages have this cell variable in them then I only need to creat page and all formulae will be looking according to tab name. Is this possible? dale Hudson HOW TO PASTE A CONTINUOUS SET OF CELLS IN A COLUMN HAVING BLANKS IN BETWEEN THE VALUES? BASICALLY I WANT TO PASTE THE N NUMBER OF CELLS IN A COLUMN BY SKIPPING BLANKS IN BETWEEN AT A TIME.IF I AM PASTING BY PUTTING FILTER IN BLANK CELLS ALSO VALUES ARE COMING. Is this possible to hide or make invisible all sheets in an Nitesh Hello i want to write a entire row value in another sheet where the one cell value is same to the given value like sl.no name id location 1 ryan 1811 Newyork 2 vincent 1812 london 3 solomon 1813 seattle i want to write the all row value in the location cell where location = london the name and id should be write in the london sheet is it possible how to use in excel I keep a log of vehicle information for all vehicles that enter through the gates of a private community. We log these entries with license plate numbers and other information pertaining to the company or individual. Some days there are multiple entries for the same vehicle. I want to develop a lookup sheet using the license plate numbers as the reference. How would I set up just a worksheet that can lookup a vehicle number from the worksheets I use to log their entries onto property? The example would be: I have a vehicle that does not need to stop at the gate, and they pass on by the stop point. I read their license plate number and now what to lookup that plate number to see who that vehicle belong too. How would I do that from the information I already have logged on the worksheets. Here are my worksheet headings: (A%26 B) Name (C %26 D) Company (E %26 F) License Plate # (G) Year (H) Make (I) Model (J) Color The A%26B, C %26 D, and the E %26 F columns are merged together. I would want this lookup information to be in its own worksheet within thework book. We log the entries onto property by month. So we have 12 worksheets of entries plus 3 worksheets with personal vehicle information and I want one ¡°lookup¡± worksheet that pulls from the 3 pages of personal information. Can you help? I am doing a vlookup and I would like for the result to pull the contents of the cell as well as the color of the cell. Is it possible? This is venkat,i am working as RA.I had one simple doubt,that in one worksheet i had 2lakhs data and in another sheet 2146 data with same ids but i want to match this 2146 data with 2 lakhs data but how i can do it? I have a column which has numbers in it 3.1.1, 3.1.2 etc - is there a way of getting the numbers to automatically reproduce QUESTION: I am trying to copy a row from one worksheet to another worksheet in the same workbook. The code I am using is relatively simple but I can only get it to run in a new worksheet. I can open a new worksheet insert this code and it runs fine, but when I try to run this code in other worksheet that has charts, formulas and conditional formatting it will not run. Please help Here is a copy of the code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Columns("G:G")) Is Nothing Then If Target = "Yes" Then Target.EntireRow.Copy Destination:= _ Sheet2.Range("A65536").End(xlUp).Offset(1, 0) Target.EntireRow.ClearContents End If End If End Sub ANSWER: The code looks fine, it has worked for me on a clean worksheet, and I have not been able to replicate the issue. One potential change that you may try is to use paste values instead of the copy function. You can try with a code like: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Columns("G:G")) Is Nothing Then If Target = "Yes" Then Target.EntireRow.Copy Sheet2.Activate Sheet2.Range("A65536").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Target.Parent.Activate Target.EntireRow.ClearContents End If End If End Sub If it doesn't work, please post more details on the type of errors that you are encountering. ---------- FOLLOW-UP ---------- QUESTION: Miguel your answer gave me reason to look my code over again and a simple Option Compare Text solved my problem. I have another problem when the code returns to row in the first worksheet to ClearContents it also clears my formulas in the columns as well. What can I do to resolve this. Ive tried delete to no avail. Is there some code i can use that won't clear my formats? I am looking to see ifis capable of highlighting a cell red when a certainis written in it? And if so what forula do I use? I wantto highlight a cell red or font colour red when 'not priced' is written into a cell... Many === Answer === Excel is capable of highlighting cells with a certain word. Use the Conditional Formatting feature of Excel. Microsoft Office Online help on Conditional Formatting.http://office.microsoft.com/en-us/excel/HP051992781033.aspx?pid=CH010036941033 Let me know if you need further assistance. I'm working on a spreadsheet that has different main accounts with a prospective name to them. Below each account are various donations from different people to the account owner and then a total to to give the balance of the account. Is there away for me to group each account and the donors together so if i were to sort the spreadsheet it would appear by the accounts' owners name with me having to input the owners name in every single column for each donor?.... dear Richard i have 2 sheets and i want to add sheet 1 cell e34 with sheet 2 cell 34 please give me a simple formula Can you give me all fomula abriviation like sum what the meaning of that QUESTION: I have a range of 5 cells, each containing a number in each cell, and I want to find how many of those numbers fall into the following catagories,1-10,11-20,21-30 etc. ANSWER: Grace, You can use the Frequency worksheet function to do this. assume you have you numbers in A1:A5 no for you "bins", you can say put in: D1: 10 D2: 20 D3: 30 D4: 40 D5: 50 Now select E1:E6 (note we select one more cell than the cells in the Bin range) then go to the formula bar and enter =Frequency(A1:A5,D1:D5) and enter this by holding down the Ctrl and Shift keys while you hit the enter key. (since this is a multicell array formula). If you reselect any of the cells E1:E6 in the formula bar, you should see that your formula looks like this: {=Frequency(A1:A5,D1:D5)} Excel displays these additional curly brackets to indicate to your that the formula in that cell is being interpreted as an array formula. If you don't enter the formula as an array simulaneously enter to the 6 cells in column E, the formula won't work properly. E1 will show the number of cells in A1:A5 that are from 1 to 10 E2 will show the number of cells in A1:A5 that are from 11 to 20 E3 will show the number of cells in A1:A5 that are from 21 to 30 and so forth. E6 will show the number of cells in A1:A5 that exceed 50. (exceed the upper limit of the bins). I used these numbers as examples, but you can have more or fewer bins and so forth. If you have any problems with this, then just post back. ---------- FOLLOW-UP ---------- QUESTION: I would like to do the calculation horizonally such as A1:E1. I have done a frequency calcuation on the same cells, but when I enter your formula, it tell me I have a create circular action. I have done when the system tells me but I can not get it to calcuate correctly. i am fairly new to excel. I know the basics on how to get around and how to work some functions. but I was wondering if there is a way to add days of the year together. EX. I have Cell A2 with the number 0 and I want A3 and beyond to have the days of the year. is there a formula that would make A2 go up 0,1,2,3... etc. but only for days of the year. QUESTION: We are using " " this symbol informulaes. What is the use of this symbol and meaning. And the use && symbol in formulas. ANSWER: anything within a set of double quotes is a string, so "" indicates an empty string, i.e the cell would display nothing (as against perhaps an error value). The & sign is used to concatenate (which is an alternative function to do the same thing) two or more strings together - so ="Aidan" & " " & "Heritage" would display Aidan Heritage as the result of the formula. ---------- FOLLOW-UP ---------- QUESTION: Here I have another one Question, I want to find how many 8 AM in column, and the same column how many 8:30 A.M. Is there any formula to count and display the data in a particular cell. The final one , thatcan be as "Thursday 8 AM" or Friday 8 AM do". In this format, How can I count 8 AM? The 8 AM may printed on First of theor Middle of theor last. Please help me to do this. ANSWER: Is the data actually entered as text, if so you would be looking for a countif function =countif(YourRange,"8 AM*") which will count all text entries that contain the string 8 am within the full text (hence the two asterisks) My email if it helps with any follow ups is [email protected] ---------- FOLLOW-UP ---------- QUESTION: It was working well. But I set the range to whole column, it was not working. Is there any option? QUESTION: I have one workbook with 2 sheets. On one sheet I have a list of Customer #'s which some start with #'s and others start with Letters. I have them in ascending order in Column A. On sheet two I have a list of Customers column B with additional columns that contain other information. What I would like is Sheet1 to look at Sheet2 and find the matching customer # and then put what is in row but column f of sheet 2. Seems simple and I used this formula but it won't work on the customer #'s that begin with a letter. =IF(COUNTIF(Notes!$B$2:$B$23,A2)<1,"",LOOKUP(A2,Notes!$B$2:$F$23)) sheet 1 CUST # CUSTOMER NAME 1 A 2 B 3 C 4 D 5 E P1 F P2 G sheet2 Cust # Cust. Name Action Notes 1 A abc 8 K abcd 9 L abcde P1 F abcdef P15 X abcdefg Please help! ANSWER: Paste this formula in B2 cell of Sheet1. If the Customer No is in Column B in Notes Sheet then use the below formula for retrieving the Column F result. =IF(ISNA(VLOOKUP(A2,Notes!B:F,5,FALSE)),"C.No is Not Present in Notes Sheet",VLOOKUP(A2,Notes!B:F,5,FALSE)) If the Customer No is in Column A in Notes Sheet then use the below formula for retrieving the Column F result. =IF(ISNA(VLOOKUP(A2,Notes!A:F,6,FALSE)),"C.No is Not Present in Notes Sheet",VLOOKUP(A2,Notes!A:F,6,FALSE)) ---------- FOLLOW-UP ---------- QUESTION: It still doesn't pull the notes from the Customer #'s that begin with P. I will explain more maybe I'm missing something. I'm working with2003. I have 8 columns on Sheet1 Column A is the Customer # column. I would like the notes from sheet 2 to go in Column 8 or H on sheet1. On Sheet 2 I have 6 Columns. Column B contains the Customer #'s and Column F contains the notes. I copied your formula into Column H on sheet1 and pulled it down. It works on the Customer #'s that are just #'s but again gives "C.No is not present in notes sheet" with the customer #'s that begin with P. THIS IS WITH REFERENCE TO MY PROBLEM WITH HOUSE NUMBERING AND ITS TYPE AT MY CONSTRCTION SITE....=IF(ISNA(MATCH(A1,$H$1:$H$40,0)),"Type2","Type1") BUT NOW I WANT TO EXTEND IT TO MATCH FROM THREE DIFFERENT COLUMNS.. SAY H,J K IF ANY ENTRY IN A1 MATCHES THE LIST IN H THEN B1 SHOULD HAVE TYPE1 AND IF A1 MATCHES J THEN B1 SHOULD SHOW TYPE 2 AND FOR MATCH WITH K THE RESULT IN B1 SHOULD BE TYPE3. I HOPE IF I GET THIS, I CAN EXTEND IT TO ANY NUMBER THEN.... SORRY FOR BEING NAIVE BUT IF YOU COULD ADD A BIT OF DESCRIPTION ABOUT THE STATEMENT , I WOULD BE IN A POSITION TO EXPERIMENT WITH IT FURTER,... how do i create a questionnaire with multiple choice answers inor excel. i use office 2007. i kinda know how to create them on power point, but i want it in a format wherein online exams are conducted.. please help thanking you sashi kumar QUESTION: I have created a graph from data in an XL spreadsheet. I want to insert a photo over the graph but want to be able to see the graph behind the photo. Can this be done and how? ANSWER: In this case I would recommend to use the photo as the pattern for the fill area. To do so, create your chart, right click on an empty space of the plot area and select "Format Plot Area" on the emergent menu, on the Area section press the button "Fill Effects", and then go to the tab "Picture". The button "Select Picture" will allow you to browse your computer for the photo to include. ---------- FOLLOW-UP ---------- QUESTION: That set the photo over the entire chart area. I am trying to locate the photo in just a small section of the chart area while still allowing view of the chart underneath the photo. Any ideas? QUESTION: Hi Bob- I have a table with 3 columns on sheet 1 of a workbook. Column B B1:B233 contains names, columnC C1:C233 contains License # and Column D D1:D233 contains ID #'s. Column C and D are associated with Col B Names. I want to create different sorts of this table on Sheet 2 and Sheet 3. How can I do this so that when I add to sheet 1 or need to change information on sheet 1, the changes will carry to sheets 2 and 3, or subsequent sheets if I need them ? ANSWER: If you ctrl/click the other sheet tabs, then anything you do on the active sheet will be done on the other sheets as well. When you no longer want this to happen, right-click the sheet tab and select Ungroup Sheets. ---------- FOLLOW-UP ---------- QUESTION: Bob- Thanks for the guidance, but this does not work. My active sheet (sheet1) is sorted by name, sheet 2 is sorted by license number and sheet 3 is sorted by ID number. I open the active sheet then ctrl/click on sheet 2 then sheet 3----make changes to sheet 1, no changes are reflected on sheet 2 or 3 (I'm using2007) My question is: I have a range of data that looks something like this: Account 100000 100001 100002 100003 100004 What I would a formula to be written that can help me put the above information into a category e.g. for data ranging between 0 and 100003 it equals to Expense. If you could please kindly assist me with this matter. It would be much appreciated how can i use Vlookup

• Answer:

  Eric, Highlighting an area is the first step. You must then select that area as the print area. Inversions beforeYou must do the following. 1. File 2. Print Area 3. Select Print Area For versions aftergo to the Page Setup section of the ribbon. Phil Sure, but I need more information about which cells you're typing "france" in, where your source data is (another worksheet, the same worksheet, etc?) and if the same worksheet, then where do you want your data returned? another column(s) on the same worksheet? or a different worksheet? Your formula won't work as you have >= the text string 45 - which is nearly ok, but would allow 46* to be counted. Further, the DATE you are testing against a string try instead =SUMPRODUCT(--(LEFT(c2:c137,2)="45"),--(d2:d137>=DATE(2010,4,21)),--(d2:d137 =DATE(YEAR($C$2+30),10,15),$C$2+30 =DATE(YEAR($C$2+30)-1,10,15),$C$2+30 Dim sh1 As Worksheet Dim sh2 As Worksheet Dim r As Range, cell As Range, r1 As Range Dim r2 As Range, rr As Range Dim shp As Shape Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") Set rr = sh1.Range("A1", sh1.Cells(1, sh1.Columns.Count).End(xlToLeft)) For Each cell In rr sh2.Columns(cell.Column).ColumnWidth = cell.ColumnWidth Next Set r = sh1.Range("A1", sh1.Cells(Rows.Count, 1).End(xlUp)) For Each cell In r If cell.Row = 1 Then sh2.Rows.EntireRow.RowHeight = cell.RowHeight End If If cell.Interior.ColorIndex = 3 Then If r1 Is Nothing Then Set r1 = cell Else Set r1 = Union(r1, cell) End If End If Next If Not r1 Is Nothing Then For Each cell In r1 cell.EntireRow.Copy sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Offset(1, 0) Next End If For Each cell In r1 For Each shp In sh1.Shapes Set r2 = shp.TopLeftCell If Not Intersect(r2, r1.EntireRow) Is Nothing Then shp.Delete Exit For End If Next Next r1.EntireRow.Delete End Sub You have to run the macro and it will process the rows that are colored red. the macro should be placed in a general/standard module in your workbook (in the visual basic editor, do Insert=>Module) Make a copy of your workbook and test it on that. Sydney, first I will assume any number in A1 is a whole number then right click on A1 and choose format Cells go to the number tab and select cus put in this custom number format 000000000000 and click OK then, as an example, if you put 1250 in cell A1 it would display as 000000001250 if you needed to produce that number someplace else you could use =Text(A1,"000000000000") I believe that is what you want. If it is something different, please elaborate. You can refer to a value in a different tab like this: =Sheet2!A5 That will give you the value in cell A5 on Sheet2. Mandy There is more than one way to do what you want but to keep it simple, here is what I would do. 1st sort the list This will put the duplicates next to each other. 2nd in a column adjacent to your list use this formula =IF(A1=A2,"yes","") (This formula assumes that your list is in column A and begins in cell A1, you would have to change it accordingly) You can put the formula in the first appropriate cell (assuming cell B1) and then quickly drag it down (thereby copying it) 9000 rows. The formula will identify all duplicates by putting a yes next to all duplicated data in the list. To take it one step further--- If you want to eliminate the duplicates in a simple way you can do this--- 1st copy and paste special/values the data in your formula column thereby eliminating the formula and leaving only the spaces and the yes results. You can paint the entire column then right clic then select paste special then select values then ok. Now sort the column based on column B values. This will isolate the yes results. Then delete the rows that have the yes results, thus leaving only the non duplicates. Stephen: This can be done with the INDIRECT function. I'll provide an example; however, you will need to tweak it to suit your specific situation. Lets say the filename is "20100501 Data.xls" Lets say the function would normally look like this: ='C:\Users\Nathan\Desktop\[20100501 Data.xls]Sheet1'!$C$2 But you want it to be dynamic... Lets say the date is located in cell A1....here is the function you would use: =INDIRECT("'C:\Users\Nathan\Desktop\["&TEXT(A1,"yyyymmdd")&" Data.xls]Sheet1'!$C$2") Now, here is the tricky part. These type of functions can be a little difficult to get "just right". Any kind of typo on the location or filename and the function will not work. Finally, the INDIRECT function only works (or updates the value) if the file being referenced is actually open. Otherwise, the function will return #REF!. Good luck! You can use a formula like this on a separate column: =A1 & " Manufacturer" Then copy the whole range, select the original item names and use the menu Edit->Paste Special->Values. This will substitute the original values with the ones that have the manufacturer appended. I can tell you'll need to use CELL, ADDRESS, OFFSET between others But is going to take you a while to set them up. For example if you wish to know the full path of the file you are using you can use following formula =CELL("filename",A1) Now, if you only want the tab you can tweak it like this: =MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1),1)+1,999) afterwards you will need to use ADDRESS to create cell names for example: 'January 2010'!B14 =ADDRESS(14,2,4,1,"January 2010") But that is only to create the name, to use the data on that cell you the use indirect over that formula, like this: =INDIRECT(ADDRESS(14,2,4,1,"January 2010")) For example, let's say you want to VLOOKUP what is on B14 of tab "January 2010", you will use this formula like this: =VLOOKUP(INDIRECT(ADDRESS(14,2,4,1,"January 2010")), ¡­¡­¡­¡­ That is for a single cell, if you want to reference a range, like B14:D24 on that same tab, you can use OFFSET along with formulas above like this: =OFFSET(INDIRECT(ADDRESS(14,2,4,1,"January 2010")),0,0,10,3) Now, by itself, when creating a range with OFFSET it will return a ugly #REF! That is because you have to tell OFFSET what to do with that range, eg, if you want to SUM: =SUM(OFFSET(INDIRECT(ADDRESS(14,2,4,1,"January 2010")),0,0,10,3)) Respectively, you can use, COUNT, VLOOKUP, etc etc etc¡­ Now, I am just giving you the tools. I have created interlinked spreadsheets using this set of tools, but it have always take me a while. Skip Blanks in anData Series:- Assume that you are having the values like the below. * In cells C5:C9, enter the numbers 10, 20, , 40, ( meaning do not put anything in cells C7 and C9). * Now, in cells D5:D9, enter 50, 60, 70, 80, and 90. * Highlight cells C5:C9 and copy them. Click in cell D5 and go to the Edit menu and choose Paste Special. This time, put a check in the box that says Skip blanks and click OK. You will see that cells D5:D9 now show 10, 20, 70, 40, 90, becausedid not paste blank values over existing data. Copy Visible Cells When it is in Autofilter:- Method 1 Select the range Press F5 Click Special Click visible cells only Click OK. Copy Or Cntrl+C Move to where you want and Paste and press Cntrl+V Method 2 Select the range Press Alt+; (to select the visible cells only) Copy Or Cntrl+C Move to where you want and Paste and press Cntrl+V -- Sixthsense Nitish Sorry to be late in answering, but I have been away from the computer. I don't know a way to make it appear as if the sheet doesn't exist, but you can hide all of the columns in a sheet and thereby all of the data. To hide the columns first select the columns then right click then click on hide at the bottom of the pop up menu. I am pretty sure that with VBA code you can hide the sheets themselves but I am not expert in VBA so I cn't help you with that. You can do this with macros or with an array formula ¨C I¡¯ll assume array formula will be easier for you, so see the example at http://www.aidanheritage.byethost3.com/excel/Array_Filter.xls ¨C my email if it helps for any follow up questions is [email protected] Because you have multiple sheets, it is going to be tricky, though not impossible ¨C ideally the lookup would look on only one sheet to find a match ¨C I GUESS it would need a series of countif statements to determine which book to try to find a match in, and then something to return the row number that matches ¨C could I see a sample file (doesn¡¯t need to have real data in it) as I think it would be easier to put some formulas in place on this and let you see the result ¨C my email is [email protected] short answer is no, but you COULD use VBA to possibly do it - though it wouldn't be with a function as such, as the colour of the cell cannot be set by a UDF. The ONLY possible YES answer would be IF you could use conditional formatting to set the colour. This SOUNDS like it's probably vlookup - this file http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls gives an example of that formula (and many others) It's basically =vlookup(what,where,which,logical) WHAT is the value you want to look up (eg 123) WHERE is the location of the table containing this value (in the first column) and the data you want to return in any column - including the first WHICH is the column number you want to return the information from - this is the column number within your data block (the WHERE bit) so if the Where range is C5:D10 and you want information from column D the value would be 2 - D being the second column in the range C5:D10 LOGICAL is either true or false - generally FALSE which would return an error if the value isn't matched. TRUE would bring the closest lower match. www.aidanheritage.co.uk email [email protected] Esme I am not sure that I fully understand the question--do you mean reproduce in the same column but down in consecutive rows, reproduce in the same row but across in consecutive columns. In both of these cases you can copy the cell and paste it either down or across. If the numbers you indicated are in consecutive rows (for example in cell A1 3.1.1, in cell A2 3.1.2, in cell A3 3.1.3) and you want to extend those to cell A4 3.1.4, in cell A5 3.1.5, etc. then you can do this by using the copy and paste series feature, to do this make cell A3 (in my example) the active cell. Put the cursor on the bottom right corner, right click and hold the right clic button down and drag the cell down as far as you need to go, release the right click button and a drop down list will pop up. Select fill series. I am not sure that I have covered your question, but if not, give me some more details and I will get you another answer. I am not sure if this is what you are looking for, this will clear the cells that don't have a formula on them. Substitute the line: Target.EntireRow.ClearContents With the lines: For Each mycell In Selection.Cells If Not mycell.HasFormula Then mycell.ClearContents Next If this is not what you need, please send more details, Audri, If you sorted each grouping separately. Otherwise, you would would need to put some field in that would keep rows together as the first key field in the sort. If you have a separate column that contains the account name, and say that is in column A (as an example), then you could go to the next available column (say column K) and in K2 put in a formula like =if(A2<>"",A2,K1) then drag fill this down. Then you can select the column and do Edit=>Copy, then Edit=>Paste Special and select values (in xl2003 and earlier - in x2007, you would do this on the Home tab under the copy section in the paste dropdown). This replaces the formula with the value it produces. If the account names and other data were in column A (or another column), then it would depend on if there is some characteristic which can be checked to determine if the entry in column A is an account name. for example if the account name is a number and is stored as a number then the formula in column K would be =if(isnumber(A2),A2,K1) and then drag fill this down the column. So putting in the name may not be that much effort. Ainaa If the formula is in sheet1, the formula looks like this. =E34+Sheet2!E34 If the formula is in sheet2 the formula looks like this. =sheet1!E34+E34 If the formula is in sheet3 it looks like this. =Sheet1!E34+Sheet2!E34 Hope this helps Amet I am not sure exactly what your question is but-- There are many many formulas or functions inand I cannot list them all for you, but you can find most of them by going to the toolbar onand going to insert and click on function. You will be able to view a list of functions, when when you clic on a function an explanation of the function will pop up on your screen. Grace, How about sending me a sample workbook with your 5 cells and your bins already entered and I can get the formula working (show me where you want it) and send it back to your. You only listed A1:E1, so I don't actually know what is in A1 to E1. my email address is Bryant, When you say "but only for days of the year, I am not sure what days of the year are. You want it like a calendar so it numbers 1 - 31 for january, then 1 - 28 for february then 1 - 31 for march etc. If so, (if not, follow up with a clearer description of what you mean by days of the year) You can get sequential numbers with the row() formula so in A3 you would use row()-2 to produce a 1 to indicate the first day of the year. you would add this to the use of row()-2 to actually get a day of the year =DAY(DATE(YEAR(TODAY()),1,ROW()-2)) then select A3 drag fill it down the column. that produces 1 in A3 2 in A4 3 in A5 etc 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 . . . for example. Is that what you wanted? If you don't want the numbering to change each time the year changes, you can change the Year(today()) with a hard coded year like 2009. The month always stays as 1. countif should work fine with a full column - can you send me a sample file to the email I gave in the previous reply and I can try to determine if there is a problem - you might also need an * before the 8 as well as after the AM which I seemed to have missed out. I am sure that the below formula will definitely work if the Lookup value in Column A (i.e) Customer Numbers and the Customer Numbers in Column B in Notes Sheet are same. I hope that in Column A of Sheet1 and the data in the Column B of Notes sheet will be having any extra spaces or it may be having some extra characters or spaces inside the text. =IF(ISNA(VLOOKUP(A2,Notes!B:F,5,FALSE)),"C.No is Not Present in Notes Sheet",VLOOKUP(A2,Notes!B:F,5,FALSE)) So check the data properly... If you would like to send me the file for getting it resolved i have given below my mail id for your reference. My mail id: [email protected] OK assume H1:H40 (adjust it) is for Type1, I1:I40 for type 2, J1:J40 for Type3: =IF(NOT(ISNA(MATCH(A1,$H$1:$H$40,0))),"Type1","")&IF(NOT(ISNA(MATCH(A1,$I$1:$I$40,0))),"Type2","")&IF(NOT(ISNA(MATCH(A1,$J$1:$J$40,0))),"Type3","") and fill down. This is actually 3 complete IF-statements attached together (not nested) - makes for easier expansion. Each is a variation of: =IF(NOT(ISNA(MATCH(A1,$H$1:$H$40,0))),"Type1","") =MATCH(A1,$H$1:$H$40,0) will return either a number or #N/A. If it DOESN'T return #N/A, then A1 is in the list. ISNA is a function which tests for #N/A. That is, if ISNA(calculation) is true, then that calculation returned #N/A. So the statement =IF(NOT(ISNA... then it's testing for a number, and if that's true, then A1 must be somewhere in H1:H40. The trailing part of the statement is ,"") which returns nothing if it's NOT a Type1. That's a complete IF-statement. So it returns either Type1 or nothing. That's attached ("&" in the first formula) to IF(NOT(ISNA(MATCH(A1,$I$1:$I$40,0))),"Type2","") which is exactly the same but looking in I1:I40, etc. HTH Excel: In the column where you want the answers, select the entire column or the entire range where the drop down should be. then go to Data, Data Validation. on the Settings tab, change Allow from Any value, to LIST. then type your possibilities like this: Answer1, Answer2, Answer3 Sorry for the late, I have been out for the long weekend. I am not aware of a way of doing what you want using Excel, but you could use a graphics program to make the background part of the image transparent, and then use that modified image in Excel. Sorry, but I don't have much experience with that type of programs to give you instructions on how to do it. It can be done with fairly involved VBA to keep the lists sorted, etc, but why not take advantage of the easy builtin features and just use one sheet, and right-click in any column, select Sort/Sort A to Z and you have what you now need to maintain! So to sort by ID number, right-Click D1 (or any cell in D), Sort/Sort A to Z and you're done. Nothing to maintain. You could use an IF function, or the VLOOKUP function, depending on the complexity of your data. How many of these different options are there? If there are more than a couple, I would use VLOOKUP. Here's a tutorial: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoftthat allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts you use vlookup like this: =vlookup(e2,$a$1:$d$20,3,false) e2 is what you are searching for. $a$1:$d$20 is the range you are searching 3 is the 3rd column in the range. so, here 3 is column C. false means that you want exact match. This formula will look for the value from the 3rd column of the range (columns a through d) for a value in e2. There is good help withinfor vlookup formula. Just read that little bit. Mark