copy windows, wb1, workbook set: Andy, Dim bk as workbook set bk = Workbooks.Open Filename:= D:AndyMyDocswb & _ textbox1.value & .xls Sheets( WK 01 ).Select Range( B3:B100 ).Select Selection.Copy Windows( wb1.xls ).Activate Range( A2 ).Select ActiveSheet.Paste you don...

Excel: occurance of character in formula, cell a1, format cells

cell a1, format cells, visual basic for applications: 1) The example you gave isn t a valid Excel formula. In Excel, you d use the formula: =1+2+3+4 2) Say you had a formula in cell A1, and you wanted another cell to display the number of + signs within the formula. This can only be accomplished using Visual...

Excel: How to open MS Excel 2007 data in MS Excel 2002?, free microsoft excel, google search

free microsoft excel, google search, excel tips tricks: There is a converter you can download from Microsoft s web site. I m sure you can find it with a Google search. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO...

Excel: To open a workbook in VBA, getopenfilename, dear tom

getopenfilename, dear tom, fname: Howard, Sounds like you want to display the file Open dialog: Dim fName as Variant fname = Application.GetOpenFilename() if fName = False then msgbox No file selected exit sub End if set bk = Workbooks.Open(fName) msgbox bk.FullName & has...

Excel: opening document, XLSX

XLSX: Dianne: XLSX is the extension of an Excel 2007 document. It is likely you have Excel 2003 or earlier and that is why you cannot open the file. Here are your options... 1) Contact the person who sent you the document and tell them to save it in an older...

Excel: Page Break View, www google co uk, page break

www google co uk, page break, google: you didn t give version of excel, but http://support.microsoft.com/kb/214691 http://support.microsoft.com/kb/201217 Both have an explanation of this and http://www.google.co.uk/search?hl=en&q=cannot+drag+page+break+preview+excel&meta= gives...

Excel: Pause And Continue If No Change, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, keypress event: How about a KeyPress event or a Button that changes a variable that your macro looks at to see whether or not to abort. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: Pause And Continue If No Change, contiue, good answer

contiue, good answer, stock trading: Steve Sorry I won t be able to get to the question for a while, I am in the middle of a major project and should have taken my name off of the available list. I suggest you reask the question of another and I am sure you can get a good answer. Richar...

Excel: Pivot Table, PIVOT TABLE COLUMNS

PIVOT TABLE COLUMNS: Pravin: When you set up your pivot table be sure to drag the Expense Group to the rows section of the pivot table and the Aging Analysis to the columns section of the pivot table. Drag the balance owed to the values section of the pivot table. If...

Excel: Pivot Table, best regard, pivot table

best regard, pivot table, cell references: Khurram, You know what the steps are. You wrote them in your question to me! Just use the cell references inside the table instead of A1, B1, etc. A B C Debit Credit Balance 1 =A1-B1 2 ...

Excel: Pivot Tables, pivot tables, pivot table

pivot tables, pivot table, work areas: Difficult to answer based on the information I ve got to go on - a pivot table will display a summary of the data on which it s based, so if this is set to only be the current month (for example) then refreshing it will give the data ONLY for the current month....

Excel: Pivot tables in Excel, pivot tables in excel, how to create pivot tables

pivot tables in excel, how to create pivot tables, column sections: Abhi: That is a VERY general question. The quick answer is you highlight the table of information to be summarized (for example, A1:C100). It is important the first row contain the titles for each column. From there you click insert- pivot table or data-...

Excel: Please help if you can, vlookup, car names

vlookup, car names, car data: Steve, Yes, a VLOOKUP is the answer. The first step is to read the help file for this function. It will tell you ALL about it. You need to add a column next to your list of car names. This column will hold the top speed for each car....

Excel: Populating other cells based on a choice from a drop-down in Excel, drop down menus, home budget

drop down menus, home budget, column c: You need VBA to do this, and since you do, it s much easier to do it all by using a UserForm -- if you send me s sample file and repeat what you re trying to do, I can help -- send to bobumlas@yahoo.com, use subject of AllExpertsQ . If you don t want to...

Excel: Possible Combinations Help, int row, allexperts

int row, allexperts, performance issues: You are right, I cannot think on an easy way to tweak the formulas to allow exceptions. If possible, you may sort the ranges to place the data that you don t want to include at the end, and modify the ranges on the formulas to fit. Regarding those ranges,...

Excel: Possible with cell validation?, quality column, macro code

quality column, macro code, quality cell: So that I don t have to retype what has already been typed then try this link. http://www.contextures.com/xlDataVal02.html Without going to the time and effort to create an applicable relevant example of what you have to test this I think it can be...

Excel: PROTECT WORKSHEET CODE, window structure, inputbox

window structure, inputbox, true windows: Roger the protection you are talking about is workbook level protection. It has two options - window and Structure. You can select either or both. suggest you do apply the workbook level protection manually with the macro recorder turned on to...

Excel: Prepopulating a calendar in excel using a source table, calendar tab, source table

calendar tab, source table, alot: Carolynn I would need to know what version of Excel you are using, how the table is set up and how much information would be needed for each entry. If possible it would be much easier to work something out if I could look at the file. My email is richard(at)pcexperthelp(dot)co...

Excel: Pricing Spreadsheet, validation tool, data validation

validation tool, data validation, column c: Zuber, You have made this super-duper-extra complicated by your choice of data structure. Having three TYPE columns makes this otherwise-easy problem exceptionally difficult. Now you have to decide which column matches the TYPE you ve chosen so you...

Excel: Print certain ranges in excel, chip pearson, events application

chip pearson, events application, mark taylor: Mark, If you want the row visible except when you print it, you would need to use the BeforePrint event. This event is in the Thisworkbook module for the workbook For detailed information on Excel events see Chip Pearson s page: http://www.cpearson.com/excel/events.aspx...

Excel: Processing Text Data in Worksheet, matching number, worth 1000

matching number, worth 1000, allexperts: Since the number you are looking for is 2 for example and it is and always will be followed by a comma, then you can do something like: =IF(ISERROR(FIND( 2, ,A2,1)), , x ) which looks for 2, You can then keep the data the way it is, no need to...

Excel: Pull data from antoher spreadsheet, excel, indirect.ext

excel, indirect.ext: If you are using INDIRECT, you may use the non-Microsoft INDIRECT.EXT function, that will allow it to work on closed workbooks. You can find it at: http://xcell05.free.fr/morefunc/english/indirect.ext.htm It is a free add-on that you need to download and...

Excel: Punchlist on Excel, room designation, question pool

room designation, question pool, punchlist: Matt, You didn t ask this question of me, but I saw it unanswered in the question pool. It seems to me this task could be handled with a pivot table. A pivot table is normally used for summarizing numerical data such as costs for categories, but if...

Excel: Punchlist spreadsheet, room designation, jargon word

room designation, jargon word, data validation: Matt, The easiest way to do this is to use a FILTER, also called an AUTOFILTER in Excel 2003. However, before I proceed, I may have misunderstood your question since I do not know the jargon word punchlist , something evidently so familiar...

Excel: partial values in a field, excel, text

excel, text: You can use the MID formula for that, something like: =MID(A1,4,4) In this case, it will take the value of the cell A1 and it will return 4 characters starting on the fourth position. It is extracted as text, if you want to compare it with numbers you may...

Excel: pasting and linking highlighted cells into another sheet that sums values, endless problems, employee database

endless problems, employee database, unified database: Al, It sounds like what you really want to do is to create an employee database which will contain all the information about each employee s pay records and work schedules. There s no need to create an individual sheet for each worker.... this...

Excel: For pasting values, target cells, barcode scanner

target cells, barcode scanner, visual basic for applications: If you don t know Visual Basic for Applications (vba) coding, then automating processes like this in Excel isn t possible. If you do know vba, then use code like this, on the worksheet object in the Visual Basic Editor: Private Sub Worksheet_Change(ByVal...

Excel: pasting vlookup formula without breaking macro, mental glitch, autofilter

mental glitch, autofilter, paster: Den, My typo/mental glitch: Calculation is an attribute of the workbook (the setting is stored with the workbook), but it is set and read at the application.Level. (so in the object module, it is an attribute of the Application/Excel) so change Thisworkbook...

Excel: pause, pause statement, clock time

pause statement, clock time, calculation time: Hi Dave, There is a way to pause a macro, but only in increments of one second. It uses the application object s Wait method. For example, this code will pause the macro for 10 seconds: Application.Wait Now + TimeSerial(0, 0, 10) The argument of...

Excel: percentages, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: If your data is in A1, you d type: =A1+(A1*0.10) Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts ...

Excel: pivot table ?, summarising data, pivot table

summarising data, pivot table, desk top: Not really a pivot table, as that is a means of summarising data (so you could count up all the articles due in June for example). I would SUGGEST using Outlook to do this - simply set up calender or task entries for the items you want - this ASSUMES you...

Excel: pivot table row fileld labels, label cells, row labels

label cells, row labels, pivot table: Nate, To clarify, you want to fill in these labels in another area of the worksheet, where you plan to do the analysis... NOT in the pivot table itself... is that what you mean? If it is what you mean, why not just make a referential copy of...

Excel: price decrease, excel, percentage

excel, percentage: You could use a formula like: =A1*0.982 If you want to change the original values, you could type 0.982 on a cell, go to menu Edit- Copy, select all the values that you want to modify, go to menu Edit- Paste special, and on the Operation section select...

Excel: projects for kids, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, course outlines: Pick up an Excel book at the local bookstore and pick out examples. Take a look at my Tips & Tricks page for ideas: http://www.599cd.com/tips/excel?key=AllExperts Take a look at my Excel course outlines for ideas: http://www.599cd.com/excel?key=AllExperts...

Excel: Query reg: Ms Excel, loan amt, macro solutions

loan amt, macro solutions, question thanks: Raghu, By now you must have solved this! If not, read on! It COULD be done with equations,only, but they will be terribly complicated and difficult to explain. A far easier method would be to process the original sheet with a MACRO....

Excel: Question on Excel, hh mm ss, timer function

hh mm ss, timer function, value time: If by Sheet, you mean you open another workbook, then the problem would be that you unqualified code refers to the active workbook which probably doesn t have a sheet1. You need to qualify your code. Assume the name of the workbook containing sheet1...

Excel: Quick Question on deleting rows, original version, cells

original version, cells: try Sub Deleter() Set currentcell = Worksheets( Sheet1 ).Range( d5 ) Do While Not IsEmpty(currentcell) Set nextCell = currentcell.Offset(1, 0) If currentcell.Value=0 and currentcell.offset(0,1).value=0 and currentcell.offset(0,2).value=0 Then...

Excel: Random Selection with Greater Odds, blank cell, random function

blank cell, random function, odd occasion: John, Are you using Excel 2003, or 2007? 2007 has a new RANDBETWEEN function that would let you limit the random numbers to between 1 and 6. You d have to use one of the rounding functions (there are TEN!) to create an integer from the random number....

Excel: IF function?, tie breakers, column c

tie breakers, column c, 2 steps: Paul: Your formula does the following: If cell D10 is blank then the formula returns an empty cell ( ). Now, the formula is written perfectly fine and it should be working; however, it will fail if Excel doesn t believe cell D10 is actually blank. ...

Excel: Ranking a number vs. a pivot table, index sheet, range index

index sheet, range index, pivot table: INDEX( Sheet 1 !$15:$200,0,MATCH(1E+307, Sheet 1 !$16:$16,1))) should give an invalid reference. the 0 parameter is not valid. So are you saying that if the last column of the PT were J, you d want to take the values in J3:J12 (or whatever without the last...

Excel: Rate of increase, cell b2, percentage increase

cell b2, percentage increase, percentage rate: Rex I would approach it simply First in a separate formula calculate the percentage rate of increase. Then calculate the average of those percentage rate increases. Assuming the numbers are in cells A1 to A5 The first formula would be in cell...

Excel: #REF! Error, pivot table, approach works

pivot table, approach works, tim tim: Tim, I would think that your attempt to use IF with an embedded ISERROR would be successful. I tried an experiment and ISERROR does indeed detect the Dreaded REF Error (DRE). But perhaps the problem lies in the way your DRE is generated, not really...

Excel: Having Realtime in excel without having to select F9, support folks, hermanson

support folks, hermanson, questioner: Allan, Thanks for the 10! This is from the FAQ for experts on the site. ================================================================== How do I dispute a rating I received? We will only change a rating if we receive an email from the questioner...

Excel: Recursive formula checking values, excel, lookup

excel, lookup: With that layout, you could use the following array formula on cell D2: =OFFSET($B$1,MAX(($A$2:$A$1000=$C2)*ROW($A$2:$A$1000))-1,0) Enter it with Ctrl+shift+Enter, and it will return the value on column B corresponding to the last entry matching the value...

Excel: Reference Cell, sheet1, equivalent value

sheet1, equivalent value, aidan: The picture doesn t make it very clear, but it s possibly INDIRECT that you are looking for =indirect( Sheet1!A & Sheet2!b3) with a 3 in sheet2 B3 would translate to =Sheet1!A3 and return the equivalent value Sorry if this was NOT what you...

Excel: Reference formula only from another sheet, ncaa basketball tournament, team bracket

ncaa basketball tournament, team bracket, excel formula: Nick, I am still confused by your explanation. You seem to have a fundamental misconception of what an Excel formula can do and what it can t do. So to help you out, I ll offer this: A formula exists in a cell. A cell can only hold ONE value....

Excel: References to ([LIST LAST ROW]+1), error type, dependants

error type, dependants, asterisk: Greg, It seems like you have already explored all the options, so I don t have much more to add. My advice is to upgrade to 2007, for, as you observed, this effect does not happen there. I played around with a sample table and also couldn t get the...

Excel: Referencing External Sheet Based On Text, excel, indirect.ext

excel, indirect.ext: This is possible with an Excel extension, that includes the INDIRECT.EXT formula. Check the add on and the help on this function at: http://xcell05.free.fr/morefunc/english/indirect.ext.htm It will allow you to craft text strings that reference values on...

Excel: Reg Sum Function, positive reply, sumproduct

positive reply, sumproduct, sum function: Girish, This produces 6.29 with those numbers in A1:A4 =SUMPRODUCT(--(INT(A2:A4)))+INT(SUMPRODUCT(100*(A2:A4-INT(A2:A4)))/40)+((SUMPRODUCT(100*(A2:A4-INT(A2:A4)))/40-INT(SUMPRODUCT(100*(A2:A4-INT(A2:A4)))/40))*40)/100 -- Regards, Tom Ogilvy ...

Excel: Reg Sum Function, positive reply, entering time

positive reply, entering time, sum function: GIRISH Here is the problem When you enter 1.12 excel thinks you are entering 1 plus 12/100 of 1. this could be 1 plus 12/100 of an hour, but it isn t 1 hour and 12 minutes. If you make your entries like this, 1:12, excel will know that you are entering...

Excel: Remove text in paranthese from cell, sdsi, collumn

sdsi, collumn, character position: Stephen, You re on the right track using SEARCH() and LEFT(). Add RIGHT() and LEN() to the mix and you ll have it! SEARCH() for ( as you did. Search returns character position N, where the ( is found. Take the LEFT() characters from 1 to...

Excel: Removing, return character, weird one

return character, weird one, problem thanks: Linda, So lets find out what these characters are. Then we can replace them with spaces. If spaces get put on the end, we can remove them with TRIM. Hopefully these characters are all the same. You ll have to do a little bit of experimenting....

Excel: Removing e-mail hyperlinks in Excel, excel expert, insert hyperlink

excel expert, insert hyperlink, proper terminology: From 2007? No wonder I don t remember. What I suggested will not work when multiple cells have been selected. Unfortunately, this command is available only when you have selected a single cell. To quickly deactivate all hyperlinks, you need to use a macro....

Excel: Removing multiple items from stock with excel, stock sheet, user interface

stock sheet, user interface, computer components: Mitchell, Try something like this: Sub UserInterfaceRemoveFromStockLoop() ////////////////////||||||||||||||||||||||||||| ************************Remove From Stock************************** |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||...

Excel: Retrieve data from multiple critierias, product id numbers, product id number

product id numbers, product id number, container size: Diav, Assume the table is in A2:D100 assume you put the container size in F1 product code in G1 country in H1 in J1 =IF(AND(F1 ,G1 ,H1 ),INDEX(D:D,SUMPRODUCT(--(A1:A100=F1),--(B1:B100=G1),--(C1:C100=H1),ROW(A1:A100)),1), ) -- ...

Excel: Returning multiple rows with same value, cell b10, example spreadsheet

cell b10, example spreadsheet, array formula: Bryan, Assuming you entered the formula correctly, that is indicative of the fact that you didn t array enter the formula. From the article - one of several such references enter the following array formula (CTRL+SHIFT+ENTER) in cell B10 to determine...

Excel: Round off any value with condition in excel 2003., number int, number 0

number int, number 0, frac: Nabam, This can be done quite easily, but first I need to be sure I understand the rules. Giving rules by example only, as you did, is not always enough. So I have to guess what your rules are from your examples. If I have guessed wrongly, then my...

Excel: Rows to Columns, pivot table, mail system

pivot table, mail system, style color: POSSIBLY the way to go would be data menu, pivot table and chart report - this would allow you to group the data, though not necessarily in the way you want. Otherwise, it would PROBABLY have to some form of array filter I think - which is fairly complex...

Excel: Run macro in one workbook on a different workbook, repetitive tasks, ogilvy

repetitive tasks, ogilvy, macros: Roger, Yes, thisworkbook refers to the workbook containing the macro. you need to change ThisWorkbook to ActiveWorkbook Sub ALLOW() Dim Sh As Worksheet For Each Sh In ActiveWorkbook.Worksheets Sh.Unprotect PASSWORD Next Sh End Sub of course...

Excel: How to read excel data and insert into sql db, confidential staff, rowcount

confidential staff, rowcount, staff database: Ok, well again a real world case - this doesn t loop through the sheets but something like dim sh for each sh in activeworkbook.worksheets do the loop, referencing the worksheet by sh next would give you the loop Sub ExportToSchemesDataBase()...

Excel: real time macro, range b23, decimal fraction

range b23, decimal fraction, time macro: I really would not recommend converting time to a FORMATed value, as that returns a string - so 9 is MORE than 11 as 1 comes before 9 alphabetically. My suggestion was to use the TIME itself - so msgbox now()-int(now()) would display a decimal fraction...

Excel: resize cells command, ogilvy, cells

ogilvy, cells: dave, if this is a vba question, I interpret this as you select a cell and you want to expand the selection to the area of contiguously filled cells around it activecell.CurrentRegion.Select if this is a non vba question ctrl+Shift+8 (ctrl+*) ...

Excel: rewording, additional info., geologic structure, geologic structures

geologic structure, geologic structures, region6: Kade, Let s say you put your formula in row 2 and it summed up what you wanted. You should then be able to copy that cell, go down 17 cells and paste it and it should get you the second sum you want. So Excel should adjust the formula relative to...

Excel: running Macro via a shortcut, code execution, screen updates

code execution, screen updates, shortcut key: Well, it seemed to work fine for me. Does this version of the macro do any better? Sub ScrollAllSheetsTopLeft() Dimension variables Dim sht As Worksheet Dim shtActiveSht As Worksheet Determine the activesheet at start of code execution...

Excel: Save As problem, worksheet menu bar, false application

worksheet menu bar, false application, true application: Jim, In the same module (thisworkbook module) Private Sub Workbook_BeforeClose(Cancel As Boolean) Application. CommandBars( Worksheet Menu Bar ) _ .Controls( File ).Controls( Save As... ).Enabled = True Application.CommandBars( Worksheet Menu...

Excel: Search for cell colour and return results in MsgBox, conditional formatting, red cells

conditional formatting, red cells, sheet names: Lee ========= Previous Post ===================== I am using Excel 2003 on WinXP SP2 and hoping you can help as I am very rusty on the little Excel/VBA knowledge I have. I have created a workbook used for tracking contacts with clients. There is one sheet...

Excel: Search using contents of a text box., excel vb, search routine

excel vb, search routine, column c: Hi Stewart, You could use code like this in the click event of your button (we re looking in column C of sheet1 here): Dim sStringToFind As String sStringToFind = Textbox1.Text With Worksheets( Sheet1 ) .Range( C:C ).Find(What:=...

Excel: Searching a column and adding row information to a new sheet, sh1, rng

sh1, rng, ogilvy: PJ, sure, I didn t give you an executable program. I gave you a code snippet that might do what you want but there wasn t enough information to do everything (such as determine which app to look for). I have no idea about anything that you have not told...

Excel: Searching in Excel, word doesn, single white female

word doesn, single white female, cells: Jeff, Its not only possible, its easy! For each word you want to search for, create a column of SEARCH equations. Refer to the word-to-search-for as a mixed reference to a cell at the top of the column ( as in B$1), and make the reference to the...

Excel: Searching in Excel, search worksheet, single white female

search worksheet, single white female, filter auto: Yes, and as always, lots of different ways - if the values are in different columns, then data, filter, auto filter and select your choices - OR use the SEARCH worksheet function with an OR and AND statement - eg with data in A2 =AND(ISERROR(SEARCH( single...

Excel: Separating Glucose Readings, glucose readings, lunch dinner

glucose readings, lunch dinner, time range: I follow the question, but not sure on the detail - is the data in a file that could be linked to, is it formatted in a way that excel can cope with? Having said all of that (which relates to GETTING the data into excel - and I m only mentioning it as if...

Excel: Show only Rows that contain any of certain values listed...., example id, hth

example id, hth, a1: Assuming your data starts in A2 (A1 being a header), enter this formula in G2 (assuming col G is unused-- the important thing is not to put it in row 1): =OR(A2=710,A2=1504,A2=332) Then select your data, use Data/Filter/Advanced filter, and enter $G$1:$G$2...

Excel: Sick Leave Accrual, sick leave accrual, third test

sick leave accrual, third test, n3: Karen, I have a couple of ideas for you. Would it be accurate enough to take the number of days between the end of the year and the hire date, divide by 30.417 (avg no of days per month over the entire year), take the integer part of the result,...

Excel: Simple formula, example spreadsheet, quantity column

example spreadsheet, quantity column, database function: Jade, There are many ways to do this. But none of them will allow you to destroy or delete the source rows containing the original information. However, you can either move or copy the results to another sheet. 1) Use the SUBTOTAL function....

Excel: Sorting Multiple Rows In Excel, trying sort, lottery numbers

trying sort, lottery numbers, colum: Neil, It all centers on use of the countif worksheet function. If you want to send me a sample worksheet so I can see how the numbers are laid out (you can put in dummy numbers), and tell me what you want to see, I can lay it out for you. twogilvy@msn.com...

Excel: Sorting multiple rows in Excel 2007, sort operation, set of numbers

sort operation, set of numbers, second choice: Mahmoud, That is the way Excel is designed. There is no option to select mulitple rows and have each row sorted independently of the others left to right. This is the same as the normal way to sort when you sort vertically. You should only select...

Excel: Sorting Options, alphabetical order, stacy

alphabetical order, stacy: Stacy I am assuming that the names are in one cell, the quickest option is to split the column and then sort the table on the 2nd column which will have the names. If you can tell me the version of Excel you are using I will send instructions on what to...

Excel: IF Statement, colume, automaticly

colume, automaticly, ogilvy: Arlie Assume you make the Y or N in A1, then in B1 =if(A1= , ,if(or(A1= Y ,A1= N ),1196000+row(), )) if the first entry is in A9 as an example, then you would just adjust the formula in B9 =IF(A9= , ,IF(OR(A9= Y ,A9= N ),1196000+ROW()-8,...

Excel: IF Statement, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vlookup: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that 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...

Excel: Students and Remaining Balance, cell a2, budget calculator

cell a2, budget calculator, cell a1: Issa: Before I write another formula to provide what you need I feel I should ask for clarification: What if there are 16 students? Is this now 2 groups since you have 2 sets of 8 students or do you want this to show as 1 GROUP + 6 REMAINING? In...

Excel: SUMIF with defined names, sumif function, sumif formula

sumif function, sumif formula, seperate sheet: You should be able to use range names in the sumif formula so I don t know why the formula is not working right and without being able to see the file then it is hard for me to troubleshoot. And unfortunately for you I don t accept emails with attachments....

Excel: SUMIFS, nathan daniel, sumproduct

nathan daniel, sumproduct, daniel daniel: Daniel: Lets assume column A has names like this: Nathan, Daniel, Daniel, Daniel Lets assume column B has dates in it like this: 1/1/09, 1/11/09, 1/14/09, 1/25/09 Finally, lets assume column C has amounts in it: 11, 12, 14, 56 Now lets assume...

Excel: Sum formula, n l1, sum formula

n l1, sum formula, sumproduct: If your data starts in D1 ( Date ), then if your test data is in L1:N1 (date, svc, s/no), this formula will add the times where it matches (expand the cells beyond row 100 if necessary) =SUMPRODUCT(N(L1=D$2:D$100),N(M1=H$2:$H100),N(N1=I$2:I$100),K$2:K$100...

Excel: Sumif, pivot table, hardeep

pivot table, hardeep, noida: The EASIEST way to do this would be via a pivot table, as this would automatically group things together and allow you to do an Average as well as a total. If for any reason a pivot table is not acceptable, then you COULD use a SUMPRODUCT function as follows...

Excel: Summing, skipping rows, sheets, arizona department of water resources, hydrologist

arizona department of water resources, hydrologist, timespan: Kade, I usually create blocks of references to discontiguous cells using the ADDRESS function inside an INDIRECT function. Before I proceed, I hope when you say sum blocks of between three and nine cells , that there isn t some variation in...

Excel: Summing, skipping rows, sheets, geologic structure, geologic structures

geologic structure, geologic structures, region6: Something along these lines would seem to do it for you =SUM(INDIRECT( A &MAX(1,(ROW()-1)*20)& : & A &MAX(3,((ROW()-1)*20)+3))) the INDIRECT worksheet function converts a text entry into a cell/range reference, so you can use (relatively) simple maths...

Excel: save the work sheet as individual file, current sheet, work sheets

current sheet, work sheets, formatting: (1) save as a text format file - these only support single sheets, so only the current sheet would be saved, but you d loose formatting and formulas (2) right click each sheet and move or copy to another workbook - save the resulting file and repeat until...

Excel: search data on multiple worksheets (looping), excel macros, data consolidation

excel macros, data consolidation, sum formula: Sub SearchForStrin() Dim LSearchRow As Integer Dim LCopyToRow As Integer For each sh in Array( 01 , 02 , 03 , 04 , 05 , 06 , 07 ) Sheets(sh).Select Start search in row 1 LSearchRow = 1 Start copying data to row 2 in Sheet2...

Excel: searching in vba, expired contracts, vba macro

expired contracts, vba macro, time jason: Jason, If your heart is set on this being a VBA macro, you ll have to ask a different expert. I don t advise on macros on this site. However, this seems like it would be really easy to do with a FILTER. Have you tried applying a FILTER to your...

Excel: sending a macro to someone else, excel, vba

excel, vba, import: You can save the macro as a file with extension .bas and send that file to him. To do so, open the Visual Basic editor, and on the project explorer right click on the name of the module that contains the macro. On the emergent menu you will have an option...

Excel: separate text in cell, excel, text to columns

excel, text to columns: In this case you can probably use the menu Data- Text to columns. Select the range of data with the information, start the Text to columns wizard, and select comma as the separator. On the third and last screen you can either overwrite the values, or place...

Excel: shared work book, track changes, multiple users

track changes, multiple users, corruption: Hi Nathalie, There is an option to track changes, do you have that turned on? (you ll have to unshare the workbook first I think). But. I strongly advise against using shared workbooks. I read lots of problem reports on this feature. Most of them...

Excel: show products as blank not #VALUE!, conditional formatting, format button

conditional formatting, format button, relative address: Paul, If you don t want to modify your formulas, then you could use conditional formatting to change the font to white (assuming a white background) if the cell contains an error value select the whole data range/table. Assume the activecell in the...

Excel: simple question on efficiencies, simple math formulas, pivot table

simple math formulas, pivot table, processing power: Nicolas, My feeling is that a pivot table would be much more efficient. The pivot table algorithm is built into Excel so Excel intrinsically knows how to do it . All a pivot table has to refer to is the data table. With equations, Excel has to read...

Excel: sliding scale, cell a1, sq metres

cell a1, sq metres, sq metre: Sorry, I should have had both references be the same cell. If A1 contains .9, then 150-50*A1 gives 150-50*.9 which is 150-45 which is 105, which is what you said you wanted. If the value is 1.1 in A1, then 150-50*a1 is 150-50*1.1 which is 150-55 which is 95,...

Excel: How do I sort this data?, highest score, ascending order

highest score, ascending order, aidan: I m confused, in that excel should be quite happy to sort the results of formulas into ascending order, ALTHOUGH I would normally expect to sort the entire sheet, so if you are sorting only a portion of it the formulas COULD get messed up. Otherwise, you...

Excel: sorting 2nd string, genevie, paul thanks

genevie, paul thanks, worksheet functions: genevie, You could write your own macro to do it. if you want to use the built in capabilities of Excel, you could copy your data to the next available column on the right, Then select the copied data and do Data= Text to columns. In the resulting...

Excel: spreadsheet - 2 items, continuous block, blank row

continuous block, blank row, header row: To ensure the sort works correctly either select NOTHING before doing the sort, OR select the entire data range - excel will then treat (in the first instance) the data from the header row to the first blank column and the first blank row as a continuous block...

Excel: If statement with a count, sumproduct, pivot table

sumproduct, pivot table, neat trick: The QUICKEST way to do a bulk analysis of this sort of data is to use a pivot table - data menu, pivot table and chart report (different in office 2007) - you would then drop the TYPE in the left hand area, the region in the top area, and either of these entries...

Excel: IF statement help, IF

IF: Hi David, Give this formula a try: =IF(H15 2000,H15*1.12,IF(H15 2500,H15*1.1,IF(H15 3000,H15*1.09,IF(H15 3500,H15*1.08,IF(H15 4000,H15*1.07,0)))))*1.25 All of your conditions multiply by $1.25 so that gets applied to the entire result. I leveraged...

Excel: If then...string, excel, vlookup

excel, vlookup: Yes, it is possible, but in this case my recommendation would be to use VLOOKUP instead of IF. To use VLOOKUP, craft a table with the different items that can be on the drop down and the price on a separate range (for example range A1:B20 on sheet2), and...

Excel: sub-tables in excel, cell b1, computer novices

cell b1, computer novices, left hand corner: Danny: How about this sample file? http://home.swbell.net/nate-sus/excel/fordanny.xls Note #1: The ultimate formula searches by capital letters. That is what I have made sure to use lower case in the word item . Assuming you will use something...

Excel: sum daily data by week, blank rows, sum sum

blank rows, sum sum, increments: Robyn, If your data is PERFECTLY REGULAR with absolutely NO skipped days, than you could build a simple equation which would sum the next seven days and copy it down. Write a formula next to one of the rows of data. It will be a simple SUM(H7:H13)....

Excel: sum data range, time interval, strange version

time interval, strange version, cell references: Stefano, I ve given you several weeks to solve your problem. From the wealth of information and ideas I gave you, you should have been able to extend my solutions to cover your data setup. Though I didn t provide the exact equations you would use,...

Excel: summing across columns using a criteria, array formulas, a1

array formulas, a1, aspx: David You can sum across columns using the Ctrl + Shift + Enter which will make the formuala an array. Have a look at this link on Array formulas http://www.cpearson.com/excel/ArrayFormulas.aspx If you are still having a problems send a copy of the...

Excel: summing figures in a colum depending a a set of conditons in two othes, question column, david answer

question column, david answer, minus signs: David, =SUMPRODUCT(($A$7:$A$239=12),($H$7:$H$239= general ),$AH$7:$AH$239) That formula is not what I suggested. the double minus signs in front of each condition was a critical part of what I suggested. Here would be your formula modified to work...

Excel: suning a cell, array formula, sum function

array formula, sum function, cell a1: Hi Ray, I devised this highly complex array formula that seems to do the job: This formula works on the number in cell A1. No need to adjust the reference in side the INDIRECT function: A1:A can be left unchanged. =SUM((INT(A1/10^(ROW(INDIRECT(...

Excel: Table to Text to Time, hh mm ss, duration notes

hh mm ss, duration notes, time duration: You can use paste special again to solve it. In this case, type the number 60 on a cell, copy it, select the range with the data that you want to modify from hh:mm to mm:ss, go to menu Edit- Paste special and select Divide on the Operation section. It will...

Excel: Tally in Excel, life signs, incorrect choice

life signs, incorrect choice, language world: Is this a count of the entries in a series of cells, or do you want to keep a running total of the number of times one cell is changed to a given value? The second requires VBA and is a little dangerous in that an incorrect choice would be counted even if...

Excel: Testing on dates in Excel, cell a1, time today

cell a1, time today, 3 years: Never only one way to do it - I d probably use EDATE if(a1 EDATE(TODAY(),-36), Blue , Green ) but you could also do =if(a1 DATEVALUE( 01/01/ & year(now())-3), Blue , Green ) (I m lazy so use NOW rather than TODAY - now returns date and time,...

Excel: Time Calculation, time calculation, lunch time

time calculation, lunch time, currency: Hours are stored as parts of a day. 1 hour = 1/24 of a day, formatted as a time. If you enter =1/24 in a cell, format it as h:mm AM/PM, you ll see 1:00 AM. So, if you have hours and a rate, you need to multiply them together and again by 24, and probably...

Excel: Tranpose Formulas, correct formulas, cell references

correct formulas, cell references, d2: Hi Vincent, Unfortunately, I don t have any great answers for you. The transpose option is only good for constant values and absolute cell references. In order to properly transpose the references, your formulas need to be = working out !$D$2, etc. This...

Excel: Transfer formula from another sheet, excel, multiple sheets

excel, multiple sheets: This works on Excel 2003, so it will probably work on 2007. Select all the worksheets where you want to have the formula (either with Shift or Ctrl on the tab with the name), and type the formula on cell C4 as you would do normally. When you go to the other...

Excel: Transfer One Worksheet's Data Across Multiple Worksheets, Sequentially, indirect address, sheet names

indirect address, sheet names, address portion: Bryan, Its an interesting idea to try to make a formula from a text string! You can certainly do such a thing, EXCEPT that you will end up with a text string, not a formula! You d have to copy and paste-special-values on the resulting text string,...

Excel: Trouble Including a Go To Special Refrence in a Charting Macro, mychart, sheet updates

mychart, sheet updates, check boxes: Set myChart=ActiveSheet.ChartObjects( Chart 7 ) isn t correct, because according to your followup, Chart 7 ISN T on the activesheet (Graph Sheet), but rather on the sheet Graph. Use this code: Sub changeChartSource() Dim myChart As ChartObject ...

Excel: True or False, question statement, a1

question statement, a1, cells: Duncan, All you have to do is write any logical equation into the cell(s). If you are comparing cells against the value 10, put 10 in a cell, like A1. Then your logical equation will be something like =J5=$A$1. Assuming J5 contains one of the...

Excel: Have the tab name pull from a cell, thier names, b14

thier names, b14, aidan: Not sure I follow - it sounds as though you want excel to rename a random worksheet with whatever you type in B14? That is PROBABLY possible, but I don t THINK it s what you meant (if it is, it sounds like a one off job to create all necessary sheets - which...

Excel: If with text, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: Use the MATCH function to find out if a value appears in another column. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: toggle button?, valuation model, checkbox

valuation model, checkbox, cool things: I don t know what you mean re the projects or on or out -- what does that actually mean? Protect the workbook? change formulas? In any case, it sounds like instead of a button, you can simply use a checkbox which is clearly on or off (checked or unchecked)....

Excel: tough excel question, excel, time formats

excel, time formats, microsoft excel: Phillip, The first important thing to consider is what format the date/times are in. My answer is assuming the format is 3/1/2008 10:00:00 AM . You can make it this format by choosing format cells. your formula in column e11 should be: =IF(((d11-c11)*24)...

Excel: transpose question, detail detail, blank cell

detail detail, blank cell, ogilvy: David, I would use a macro such as this one: Sub ChangeData() Dim r As Range, ar As Range, r1 As Range Set r = Columns(1).SpecialCells(xlConstants) For Each ar In r.Areas ar.Copy ar(1).Offset(0, 1).PasteSpecial Transpose:=True Next Set r1...

Excel: Update, individual department, master price

individual department, master price: for each individual sheet, for any item on the master list enter it s value as a linked cell - i.e. a formula like this =[Workbook name and path]Worksheetname!CellRef when the individual sheet is opened, it will prompt to update the values, and will...

Excel: User Form wont loop correctly, word data, searchin

word data, searchin, range c: Stewart, You need to change the code that goes to the last location to just go to the first location. Stuart Resnik s code for the next button assumes that you are working from top to bottom. there is nothing in your code that would cause the userform to...

Excel: Using ActiveCell as value in Range, gap down, windows document

gap down, windows document, contiguous data: Actually, CS is column 97 from the immediate window: ? Range( CS1 ).column 97 Anyway, you can adapt the below to reflect your actual situation Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim i As Long, r As Range, cell As Range Set sh =...

Excel: Using Excel to Match People to Classes, open subjects, logic problem

open subjects, logic problem, lookup functions: Ally, This is a massive logic problem, not easy to solve by any simple set of equations. I doubt that high schools use Excel for this type of task... this is a scheduling task that is best solved by repetitive application of a scheduling algorithm....

Excel: Using functions on selected data in Column, statistics formulas, column name

statistics formulas, column name, ogilvy: Rodger, first, you don t usually don t need to array enter sumproduct - not always the case, but should be here: Average: =SUMPRODUCT(Score,--(Date =Date_Beg),--(Date =Date_End))/SUMPRODUCT(--(Date =Date_Beg),--(Date =Date_End)) the above is fine...

Excel: Using INDEX with multiple criteria, g222, committee names

g222, committee names, table cells: Beth, If I understand the situation correctly, then this should do that: =IF(SUMPRODUCT(--(Table2!$G$2:$G$222=$A2),--(Table2!$H$2:$H$222=B$1))=0, ,INDEX(Table2!$B$1:$B$222,SUMPRODUCT(--(Table2!$G$2:$G$222=$A2),--(Table2!$H$2:$H$222=B$1),ROW($B$2:$B$222)),1))...

Excel: Using a Macros, blank copy, shold

blank copy, shold, question thanks: Tony, You said you had a macro that The sheet has protected cells so in the macros I take protection off to copy all cells and then put protection back on all sheets. You would need to integrate the code snippetapproach I suggested within your...

Excel: Using nested IF function combined with AND, OR, NOT, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, isnumber: I can t write the whole thing out for you, no. That s for you to learn. You need nested IF functions and possibly the ISBLANK, ISNUMBER, AND and OR functions. Here s some help: You can use the IF function to have Excel place a value in a cell based on...

Excel: Using the value in a range as the title for a command button, exit design, commandbutton

exit design, commandbutton, button title: From your main Excel menu, choose View, Toolbars, Control Toolbox. Use the Control Toolbox to enter your CommandButton on the sheet. Right-click on the button and choose Properties. In the Properties box, it will list a default name for your button, like CommandButton1....

Excel: urgent help, timb, formulae

timb, formulae, aj: Say your original text is in col A, e.g. with A1 being: a. k. timb and A2 being: aj patel In cell B1, enter the formula: =SUBSTITUTE(A1, , ) then copy it down col B. It will take each entry in col A and display it with all spaces removed...

Excel: userform textbox follow-up, paste words, xls files

paste words, xls files, brain storm: Attis, I believe I already provided a revised answer (according to the archives - 2/04/2009) to this question which incorporated the find command in response to your first followup. The answer may have started out with The Expert Can t Answer Your Question...

Excel: using a sort macro with multiple sheets, hello bill, sort command

hello bill, sort command, macros: change ActiveWorkbook.Worksheets( Sheet 1 ) to ActiveWorkbook.ActiverSheet - see below Sub SORT() SORT Macro Macro to sort column A small to large ActiveWorkbook.Activesheet.AutoFilter.SORT.SortFields. _ Clear ActiveWorkbook.Activesheet.AutoFilter.SORT.SortFields.Add...

Excel: using a vlookup text result in an IF statement, vlookup formula, logical test

vlookup formula, logical test, text result: Philip, First of all, you are using an overly complicated IF statement. =IF(D1=F1,TRUE,FALSE) can be replaced by =D1=F1 It may look funny, but this is a logical test whose answer can only be TRUE or FALSE. You don t need...

Excel: using a vlookup text result in an IF statement, vlookup formula, case conversion

vlookup formula, case conversion, text result: Difficult to follow - the formula for testing is more complicated than it needs to be =D1=F1 would give a true/false result without any tests. POSSIBLY you need a TRIM statement (leading/trailing spaces) or possibly a case conversion statement? Can...

Excel: #VALUE error, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, blank cell: You can use the NZ or ISBLANK functions. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts Cordially,...

Excel: Validation for duplicate records, excel, countif

excel, countif: In this case my recommendation would be to use conditional formatting for the task column, so it will be highlighted if the user enters a duplicate value. To do so, select the full column A (you can limit it to the rows you want, but this way it will always...

Excel: Validation - EXCEL, dependant, xls

dependant, xls, validation: Sorry, the question isn t clear - one cell can only contain one set of choices - are you wanting to make the choices in one cell dependant on the choice in another cell? if so, see http://www.aidanheritage.byethost3.com/excel/example%20choices.xls which...

Excel: VB Code, ogilvy, spreadsheet

ogilvy, spreadsheet, headings: Roger, these worked for me: Private Sub CommandButton1_click() remove filter me.AutoFilterMode = False End Sub Private Sub CommandButton2_click() add filter Dim r As Range Set r = me.Range( B2 ).CurrentRegion If r.Row = 1 Then Set r...

Excel: VB Macro, sname, rng

sname, rng, excel sheet: Rolf, Sub Datain() Dim sName As String Dim rng As Range, col As Long Dim rw As Long, l As String sName = Application.GetOpenFilename( _ FileFilter:= Text Files (*.Bls),*.Bls ) If sName = False Then Exit Sub Set rng = Range( A30 col = 0 rw...

Excel: In VB, selecting the first blank cell in a column, excel, vba

excel, vba: You can combine the COUNT function from Excel with the Offset from VBA, with something like: range( A1 ).Offset(application.WorksheetFunction.Count(range( A1 ).EntireColumn),0).Select If the range of data is continuous, this will select the first blank cell...

Excel: VBA copy values only, vba code, m75

vba code, m75, sick days: Let s say that what you want to do is copy what s in the range defined by: SickDay.Offset(-1, -1) and paste-special its VALUES to the range defined by NewSh.Range( E75:M75 ).Cells(0 & Rcount) The vba code is: SickDay.Offset(-1, -1).Copy NewSh.Range(...

Excel: VBA excel help, max match, max line

max match, max line, string str1: 1 - I don t know what matches is -- it s not native VBA code. 2 - I don t see matching in your example, so please send me the wb which contains the data and the VBA code so I can see what you mean. send to bobumlas@yahoo.com, use subject of AllExpertsQ...

Excel: VBA-help required, excel programming, problem thanks

excel programming, problem thanks, checkbox: Hi Rajat, You could do it like this: Sub EnableDisableBoxes() Dim oObj As OLEObject For Each oObj In ActiveSheet.OLEObjects If TypeName(oObj.Object) = CheckBox Then oObj.Object.Enabled = True End If Next...

Excel: VBA procedure, c cells, rw 1

c cells, rw 1, column c: jw I will assume you know how to use macros since you are asking for one. Sub ABC() Dim cell As Range, c As Range Dim i As Range, rw As Long Dim sh As Worksheet With Worksheets( sheet1 ) Set c = .Range(.Cells(1, C ), .Cells(1, C ).End(xlDown))...

Excel: VBA UnHide Row depending on variable, test sheet, command button

test sheet, command button, newby: Jimmy, according to your code, you want to unhide row 1 and all rows down and including the row number held in H11. If so: Private Sub CommandButton1_Click() Dim RowValue RowValue = Range( H11 ).Value Range( 1: & RowValue).EntireRow.Hidden = False...

Excel: View image within Word, image spreadsheet, 123456

image spreadsheet, 123456, allexperts: Something like this MIGHT do it for you - this however will clear ALL pictures, and requires a one cell table Sub Macro1() Macro1 Macro Macro recorded 14/11/2007 by Heather ActiveDocument.Shapes( Text Box 3 ).Select Selection.ShapeRange.TextFrame.TextRange.Select...

Excel: View Locked Cells on a spreadsheet, excel, find

excel, find: You can use the Find for that. Go to menu Edit- Find, open the Options, open the Format selection and on the protection tab mark the option locked. With that, you can click on Find All and it will list all the locked cells on the worksheet. The cells are...

Excel: VLOKUP PROBLEM, partial sentence, absolute reference

partial sentence, absolute reference, number column: Mark, There are several things which could be wrong. Most likely is that you have used the RANGE-LOOKUP value of 1 where you need zero. I d try that first. A good strategy is to assign a cell somewhere to hold this value, and then use...

Excel: VLOOKUP, IF, variable numbers, column c

variable numbers, column c, ascending order: Not sure how fixed the structure of the data is - the EASIEST way would be to build the lookup table with either an extra column, OR 3 fewer columns - such that you could have a unique value to match - for example, lets say you go with the extra column -...

Excel: Vlookup Problem, vlookup formula, ogilvy

vlookup formula, ogilvy, match: rubin, the lookup and match functions return a #N/A error when the item being looked for (usually the first argument in the function) is not found in the range being searched. You can trap this error with =if(iserror(formula), ,formula) Just...

Excel: Vlookup from another workbook - having trouble with the data update, macros, data sources

macros, data sources, xls: Kathy: The Short: VLOOKUP is a function that will work properly on a workbook that is closed. Thus, your scenario should work just fine. The Long: Unfortunately, your Excel isn t working the way it should...this means I cannot really answer the question...

Excel: vba macro help - creating new worksheets, sheet names, false application

sheet names, false application, vba macro: Sub CreateSheets() On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Set Curr = ActiveSheet For i = 3 To Range( A50000 ).End(xlUp).Row Err.Clear Worksheets.Add.Name = Curr.Cells(i,...

Excel: vba tip, column numbers, cell references

column numbers, cell references, simple matter: Mekdim, You could use VBA for this but if so, I can t help you because I don t offer VBA advice. However I would do this with a swath of ADDRESS and INDIRECT functions. Create a grid of ADDRESS functions in the shape you want your data....

Excel: vLOOKUP Formating, cell location, vba macro

cell location, vba macro, specifics: Hi Pradip, What you describe cannot be done with a worksheet function because these functions are not permitted to change any properties of a cell other than the Value property. The same is true of a VBA user-defined function (UDF). It is possible...

Excel: windows user name, holiday entitlement, cell a1

holiday entitlement, cell a1, ogilvy: Mark, if the username is on the left side of the list in say Sheet2 (assume in column A) in B1 of the sheet where the subject username appears as an example =if($A1 ,Vlookup($A1,Sheet2!$A:$Z,2,False)) in C1 =if($A1 ,Vlookup($A1,Sheet2!$A:$Z,3,False))...

Excel: How can I make this XL 2007 macro work for 7 more worksheets in the same book?, macro in excel, macro work

macro in excel, macro work, problem arises because: Sub sort() Dim i as integer Dim wks as Worksheet For i = 1 to 8 Set wks = thisWorkbook.Sheets(i) wks.sort.SortFields.Clear wks.AutoFilter.sort.SortFields.Add _ Key:=Range( A:A ), SortOn:=xlSortOnValues, Order:=xlAscending,...

Excel: XL , IF FUNCTION ,EVALUATING WRONG, douglas m smith, cell references

douglas m smith, cell references, cell values: Hi Mario, I suspect one of the cell values is actually 1 , a text value, instead of 1, a number. The way to tell is to formula cell, press F2 to edit the formula, and then highlight each of the cell references one at a time and press F9 to evaluate them....

Excel: ActiveX control causes "...reset project..." message on file open., combobox controls, project messages

combobox controls, project messages, vb environment: Without seeing/testing the actual workbook, I would guess this might work: Put in a Workbook_Open event code which simply has Application.DisplayAlerts = False If that doesn t work, and you d like me to take a look, feel free to email it to me at bobumlas@yahoo.com,...

Excel: Adding rows in a column automatically, blank rows, blank row

blank rows, blank row, undetermined number: Sub totalme() Dim rowcounter As Long Dim columncounter columncounter = ActiveCell.Column Dim endrowcounter, startrowcounter As Long For rowcounter = ActiveCell.Row To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 If Len(Cells(rowcounter,...

Excel: Advanced Excel sum of number of employees based on time of day, boss man, time cell

boss man, time cell, advanced excel: Jack, I thought so! Luckily, the main part of my answer is still completely correct. Start implementing it as I said, EXCEPT instead of referring to any one pair of the columns of start/end times, refer to a new column-pair which we will call the ...

Excel: Application Filesearch, novice user, floc

novice user, floc, excel sheet: It s hard to make it work given the current code doesn t seem to make sense -- look at this: If .Execute() 0 then For i=1 to .Foundfiles.Count PathInfo = .Foundfiles(i) FLoc = Standard_Forms_Dir & Fname Next i Else... The above...

Excel: Array Formula not working, array formula, sum function

array formula, sum function, p4000: Paul: Well, I cannot say for certain because an #N/A could be caused by a variety of things. First of all, the above array formula doesn t quite work the way you are intending. Instead, you should use the following: =SUM(([book1.xls]Stock!$P$2:$P$4000=...

Excel: Attainment Percentages, excel, if

excel, if: I cannot tell you if your boss is correct or not, as it depends on how the metric is defined and that is particular to each company. In any case, if you want to include an extra condition on transfers when target is zero, you could do something like: =IF(Adjusted...

Excel: Auto Populate Cells, mark monday, c101

mark monday, c101, a101: Ron, I think you need to take another crack at phrasing your question. There are too many things that don t make sense to me. Remember, I cannot see your worksheets and I don t know what your end goal is. For instance, though the equation you...

Excel: Auto-sizing of cells and comment boxes, dynamic size, issue question

dynamic size, issue question, empty space: ANY time you want EXCEL to do things automatically that it does not normally do automatically you will need to deploy macros that you have to write. And if you know anything about macro then I would suggest using userforms instead of the sheet itself to be...

Excel: Autofill in Excel, sheet test, speadsheet

sheet test, speadsheet, autofill: Pete, Try this: Private Sub CommandButton1_Click() Dim r2 As Range, r3 As Range Dim r4 As Range, r5 As Range Dim r6 As Range, bFirst As Boolean Dim LastRowInM As Long, r7 As Range Dim r As Range, ar As Range Set r = Range( A2:K2 ) LastRowInM...

Excel: Autofill form from database, vendor name, formul

vendor name, formul, ogilvy: Mike, I answered your second question within an hour of you asking it with specific examples oriented to the picture you attached. it looks like you just blew that answer off. Any ideas why it s always the same data? the reason you are getting the...

Excel: Autofilter based on Data Validation, cell c2, target address

cell c2, target address, data validation: Sarah, If you clear the cell C2 (make it blank), this will do it: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Address = $C$2 Then Set r = Me.AutoFilter.Range If Len(Trim(Target.Value)) 0 Then r.AutoFilter...

Excel: Autorun Macros, dear bill, davide

dear bill, davide, coop: After pressing the ENTER key EVERY TIME? This is not a good idea and you can t have this technique be turned off and then back on unless you do something manually to tell EXCEL to run the macro this time when I press the ENTER and then don t this time...

Excel: Auto_Exec, chip pearson, xls file

chip pearson, xls file, auto exec: Hi Martin, THe Auto_Open sub runs when you open the file it is stored in (in this case personal.xls), NOT when just ANY file is opened. Chip Pearson has a good tutorial on running code when a file is opened: http://www.cpearson.com/excel/AppEvent.asp...

Excel: Average_Excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Without reproducing the spreadsheet and trying it myself, I m going to guess that it s just a rounding error. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO...

Excel: add an excel graph in e-mail with VBA, outlook application, true body

outlook application, true body, vba: The chart object has an export method which enables you to export the chart as a graphics file. You can then use the Attachments.Add method of the outlook message to add the graphic as an attachment: With mailTender .Attachments.Add c:my documentsMyChart.png...

Excel: adding markup to excel sheet, csv file, seperate sheet

csv file, seperate sheet, question post: Andrew, If you mean you want to add that information to the csv file, then yes, I would see opening the file in Excel, putting in the formula to calculate the new column of values, then doing a save As using CSV as the file type. If you are just using...

Excel: arrange data in in the same columns, voice of experience, d price

voice of experience, d price, macros: You will not find a macro to do this, it will have to be written. Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS...

Excel: array formula problem, excel, array

excel, array: It looks like you are trying to add the numbers on column N for the values that satisfy one of the first two conditions and the third one. You could try with some array formula like: =SUM(((MAIN!D6:D550= U )+(MAIN!D6:D550= SFU ))*(MAIN!K6:K550= GB )*(MAIN!N6:N550))...

Excel: Some assistance and clarification would be GREATLY appreciated., vb game, seperate issues

vb game, seperate issues, blank entries: David, I you bind the list of the combobox/listbox to the worksheet range, by using the rowsource property, then it seems to link to the underlying values of the time/dates which are serial numbers. What I have found to be effective is to load it using...

Excel: auto copy data between sheets, using ms office, header rows

using ms office, header rows, blank row: Sorry for the delay in replying - it took me some time to find the time to write the macro you need I m not sure how many of the top 3 rows are header rows, so at the moment I ve assumed that they ALL are - this macro will do the work for you. Sub testme()...

Excel: auto-populating cells, cell c2, ogilvy

cell c2, ogilvy, spencer: Spencer Assume A2: First Name B2: Last Name then C2: =$A2& &$B2 or =Concatenate($A2, ,$B2) If you lists of names going down columns A & B, then you could now select cell C2 and drag fill down the column Hope that is what you were...

Excel: automating sum(X,Y), excel, references

excel, references: With your first question, if you select the cell C2, go to menu Edit- Copy, select the cell C3 and go to menu Edit- Paste the reference will change, adjusting to the row. There are shortcuts to this, like the small square on the lower right corner, that can...

Excel: autosum in vista excel, autosum, point moves

autosum, point moves, relative number: Jack, First off I want to point out that when you say you want to add 0-15, 15-30, 30-45 you are adding 15 twice, 30 twice, and probably 45 twice. I hope what you really mean to say is you want to add 1-15, 16-30, 31-45, 46-60, etc. My answer assumes...

Excel: Borders in excel 2003., cell contents, b32

cell contents, b32, control c: Nabam, Perform a COPY by selecting the cell region and then hitting CONTROL-C, as you (probably) usually do. Then you can CLEAR the cell contents which will leave behind the borders. But then look up in the menu (or ribbon) and find PASTE SPECIAL....

Excel: Borders upon occurance of text in column, word change, question thanks

word change, question thanks, ogilvy: Harry, Looks like a typo on my part: Set r = Range(Cells(1, EH ), Cells(Rows.Count, E ).End(xlUp)) should be Set r = Range(Cells(1, EH ), Cells(Rows.Count, EH ).End(xlUp)) this also had a typo: Instr(1,cell, Change ,vbTextcompate) should...

Excel: Breaking down an address column, rt rt, column method

rt rt, column method, conditional test: Charles, What if someone enters and address as 1234 Carl Lake Shore (and omits the Drive ? You need to handle that case too. I can think of several ways to do this: Method 1) Create a table of all possible street types; Circle, Way,...

Excel: barcode scanner-to-excel, barcode scanner, excel spreadsheet

barcode scanner, excel spreadsheet, barcodes: Dimitris, You ll have to check with your barcode scanner supplier to see what kind of output formats the devices create. That s the first step. You need to be sure that whatever the output format is, that you can get it into Excel easily. I have...

Excel: beginner - using excel to calculate cost, free microsoft excel, validation form

free microsoft excel, validation form, excel tips tricks: Sure it s possible. You ll probably need to know how to use the IF function though if you have to make decisions based on the type of product (CD/DVD). See: You can use the IF function to have Excel place a value in a cell based on one or more conditions...

Excel: Calculate balance after autodrafts, auto expenses, auto values

auto expenses, auto values, days of the month: Joshua, I would have the calendar of dates already set up to show the days of the month on which these (and other) expenses are made. Have a separate column for auto-expenses . In that separate column, have an IF statement which compares todays...

Excel: Calculating formula from seperate worksheets based on sheet name, sheet names, worksheet functions

sheet names, worksheet functions, functions work: Lonny, Let s say that your workbook tab order looks like this (sheet names:) Totals LaborStart Labor1 Labor2 Labor3 LaborEnd MatStart Material1 Material2 Material3 MatEnd in a cell in sheet totals you could have a formula like =Sum(LaborStart:LaborEnd!B9)...

Excel: Calculating OT for the week, OVERTIME

OVERTIME: Josh: Lets assume your hours worked is located in cell A1. This formula will give you the regular hours: =MIN(1/24*40,A1) This formula will give you the overtime: =MAX(A1-1/24*40,0) Be sure to format the cells as time (XX:XX), just like the...

Excel: Calculating ongoing 3 and 12 month averages, cell a1, cell c3

cell a1, cell c3, roling: Hi Paul, Let s assume that Month 1 is cell A1 and 473.7 is cell C3. Your formula in cell C3 would be =AVERAGE(B1:B3) If you copy that formula down to C4 (by copying and pasting, or just draggging), it will change to =AVERAGE(B2:B4) and so on,...

Excel: Calling a Named Range in a formula, excel, indirect

excel, indirect, name: You can use the INDIRECT for that, something like: =COUNTIF(INDIRECT( &B1& !E:E ),F3) I am supposing that the cell in B1 contains the sheet name, and that is reflected on how the parameter of the formula is constructed. If you have the full reference...

Excel: Calling Variables in other sheets and workbooks, cell b3, daily numbers

cell b3, daily numbers, indirect address: Dan, If you have working formulas that retrieve the correct cell references, then you probably are having problems applying them because you don t know about the INDIRECT function. When you put a cell reference (such as the ones you are retrieving using...

Excel: Cell Fill Color, conditional formatting, 31st july

conditional formatting, 31st july, excell: You can use conditional formatting to do what you want but I can t supply details because I don t quite follow. Can you give precise example of 2 rows, say A2:E3 s contents, and what you d like to see highlighted, and why, and is the criteria specifically...

Excel: Cell Fill Color, a2 b2 c2, conditional formatting

a2 b2 c2, conditional formatting, color question: Sarwar, I cannot send you a fully-working schedule system for free. It will take me many hours to develop and this is how I make my living. If you want me to create a working schedule for you, it would become a fee-based project through my professional...

Excel: Cell Fill Color, conditional formatting, 31st july

conditional formatting, 31st july, excell: You would need to use conditional formatting to check the date in the cell against the dates - but not clear what rules to follow here - if you d like to clarify (my email if you want to send me a file is aidan.heritage@virgin.net) I m more than happy to help...

Excel: Cell Link, week 1

week 1: In another sheet of the same workbook or within the same workbook? Either way as long as you identify the names of the sheets and the full path of the other workbook, if applicable, then the link should work. Obviously since you have typed the link here...

Excel: Changing Borders or background color of the active cells in Excel, format cells, visual basic for applications

format cells, visual basic for applications, financial groups: Select the cell(s). From main Excel menu, choose format, cells, Patterns. On that popup tab, you can choose the background pattern of the cell. Then switch to the Border tab, and you can add borders of several different weights, styles, and in any color. ...

Excel: Character Limit in Word Drop Down Menus, drop down menus, sentances

drop down menus, sentances, character limit: WHY do you need to include lengthy sentances in a drop down? As the drop down control is an activeX control, I d simply use VBA to populate the document with the choice of text from there - otherwise you are going to be stuck with the limitations you have...

Excel: Check box insert, obox, cbox

obox, cbox, caption property: Alan, I am not totally clear on whether you want two macros or one macro. Also as far as getting status, I have shown how by displaying it in a msgbox, but I don t know what you actually want to do. do you want to count how many are true or take some...

Excel: Checkbox Macro Enabling, control toolbox, getting some errors

control toolbox, getting some errors, g6: Jarib, I didn t try to debug Daemon or your code beyond what you were asking. Change with Worksheet( sheet1 ) to With worksheets( sheet1 ) Yes, If you want the cells locked if checked then, was a typo on my part. If you check the...

Excel: Checking info against other worksheets in a workbook Macros, false selection, yy

false selection, yy, copy paste: You can add this above your End Sub: ProofLoans then have a separate procedure so you CAN run it independently: Sub ProofLoans() Dim n as Integer On Error Resume Next For i=1 to Sheets( Loans ).Range( C65536 ).End(xlup).Row n=0 ...

Excel: Close shared workbook, procedure declaration, ogilvy

procedure declaration, ogilvy, boolean: Carol, Sorry, since you were using workbook events, I assumed you were familiar with them and new how to get the declaration from the dropdown at the top of the module. (it is always best to let Excel/VBA enter the declaration in the module for you by selecting...

Excel: CONDITIONAL FORMATTING HELP NEEDED, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, excel tutorials: You just need to figure out the difference in days. Make this its own column, then change your conditional formatting accordingly. There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or...

Excel: COUNTIF with data in two columns, excel, countif

excel, countif: Arthur... You can accomplish this using a nested if statement. If scores from A is in column A, scores from B is in column B and the pass rate is in column C, then the formula would be: =if(A2 =20,if(B2 =20, Result , ), ) Where result is what you...

Excel: Coding / if statment, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, paste link: You can pull data from one sheet to another by referring to it like this: =Sheet1!A2 This will allow you to reference data on other sheets. You can also Copy Paste Special Paste Link. Let me know if you have any other questions or comments....

Excel: Colored cells, colored fonts, colored cells

colored fonts, colored cells, format button: Select all the cells which you want to possibly change color. Use Format/Conditional Formatting. Change the dropdown which says between to equal to , then enter Buyer, then click the Format button, the patterns tab, and pick a green color. Then click OK,...

Excel: Colour AutoSUM, rcell, colorindex

rcell, colorindex, cell changes: Hi Stuart How are you? This is event driven programming: Your formula has to be recalculate whenever the colour of a cell changes. We have built-in events which run automatically a procedure(e.g. Private Sub Worksheet_Change(ByVal Target As Range in the...

Excel: Combined Formula, excel, if

excel, if: You could include the OR function to make a formula like: =IF(OR(A1=0,B1=0), Insufficient data ,IF(A1 B1,A1-B1,B1-A1)) This should give you the expected result. In this case, you could also simplify the formula a bit more with something like: =IF(OR(A1=0,B1=0),...

Excel: Comparable Sales Spreadsheet, column headings, excel expert

column headings, excel expert, property characteristics: Donald, Its difficult to make Excel retrieve multiple rows that meet multiple (or even single) criteria. It can be done, but trust me, you don t want to go there. You d have to become an Excel Expert to make it work. However, I see that you...

Excel: Comparing, colorindex, vba code

colorindex, vba code, conditional formatting: You d have a much easier time simply using Conditional Formatting. Please send a sampel wb to me at bobumlas@yahoo.com and I ll show yuo how to do it -- It s also better than VBA in that it s dynamic and you don t have to rerun the VBA code when something...

Excel: Comparing cells and then assigning score, false answers, student test

false answers, student test, correct answers: I m pretty sure this doesn t need a macro but can be done with a formula - IF I follow this correctly, you have (say) to columns of correct answers in row 1, then the student data on subsequent rows? This formula assumes that there are 10 questions, that...

Excel: Comparing multiple columns and conditions in Excel., interview dates, hiring manager

interview dates, hiring manager, heavy lifting: Kelly, Yes, there is a way. There are actually several ways. For Excel 2007: Use the COUNTIFS() function For Excel 2003: use the advanced (and undocumented) form of SUMPRODUCT. You can read about this at XLDYNAMIC.COM. For Either version:...

Excel: Comparing worksheets in excel, e mail address, countif

e mail address, countif, emails addresses: Jeff, If you want to write code, send me two dummy workbooks or data on two separate sheet with the layout of your data represented - the values can be dummies, but I need to know whete things are located so I know what to write in the code. You should...

Excel: Complet formulas for dynamic lookups, row labels, countif

row labels, countif, text don: Yolanda, What you want to do is quite possible, but I don t have enough information to get you started. I can only give you a general approach. Your question refers to specific cell ranges like D7 and D87-H88 but the image you provided doesn...

Excel: More Conditional Formating, conditional formatting, color border

conditional formatting, color border, cells: There are actually 4 - the format when none of the conditional formats are met + the 3 from the CF dialog. There is no way to increase the # of conditional formats aside from upgrading to Excel 2007. What can be done is VBA code to actually color the cells...

Excel: Conditional Formating, free microsoft excel, key performance indicators

free microsoft excel, key performance indicators, excel tips tricks: Sure, you would probably need an IF statement to determine the number of parameters that meet your requirements. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE...

Excel: Conditional Formatting, conditional formatting, c9

conditional formatting, c9, ogilvy: Evonne, the today() function gives today s date. Dates are stored as the number of days from a common base date, so you can use a formula like =((today()-$B9)/7) 6 where B9 holds the start date, would be a test if the employee has been employed...

Excel: Conditional Formatting, excel, conditional formatting

excel, conditional formatting: Matthew, Lets assume your effective date is in A1 and your end date is in B1. In C1, you would type: =B1-C1, and format the cell as a number with no deciomal places. Next, choose conditional formatting from the menu or the ribbon in 2007, and choose...

Excel: Conditional Formatting, Cell Upon Cell, shape position, hand column

shape position, hand column, co worker: Doug, There is no function which detects the color of a cell and reports a value: so you can t say If cell X is Green, then do this.... Since you have applied conditional formatting, each of the cells changes colors based on some condition that...

Excel: Conditional Formatting While Referencing Another Worksheet, conditional formatting, drop down menus

conditional formatting, drop down menus, cell changes: Dave, I think you would want to have your code in the calculate event if you want it to react to changes in the source sheet. The Change event only fires when you actually edit a cell in that sheet. Also, since this should be firing when this sheet is...

Excel: Conditional formatting, excel, date

excel, date: Sorry for the late, I didn t connect during the weekend. Regarding your question, you can use a formula on the conditional formatting, so for example if the current cell is A1 and the cell with today s date is B1, you could set the formula to something...

Excel: Conditional formatting -> Charts, dynamic chart, pie chart

dynamic chart, pie chart, b15: Aaron, You could apply a FILTER or ADVANCED FILTER to your data table. Use the same criterion to display & hide the rows in the data table as you are using for the conditional formatting criteria. The chart won t show values for the rows that the FILTER...

Excel: Conditional formatting to match any cell in a range, log spreadsheet, construction project management

log spreadsheet, construction project management, conditional formatting: Robert, There might be a way to prevent typos from ever occurring. If you don t like this idea for some reason, write back with a followup and I ll explore your original question. My idea is to make the list where you might type a possible bogus...

Excel: Conditional sum of filtered range, colc, colb

colc, colb, ashutosh: Ashutosh Khiré: Well, it is a very complication formula. I will try to explain to the best of my ability, but please understand it is complicated (I won t be able to make it simple and easy). First of all I m going to shorten to the formula to a smaller...

Excel: Consolidate Data From Other Files, person checks, excel workbooks

person checks, excel workbooks, daily basis: Zohar, You can create links to any cell in any other workbook, which will retrieve the value of the cell in the workbook being pointed to. But with constantly changing workbook names and so on, it may be difficult to maintain this.... since there is...

Excel: Converting the date to number of days, date format. excel

date format. excel: Waheed... You can do this by subtracting the two dates and then converting the cell format to number. For example, if Jan-3-2007 is in A1 and jul-25-2009 is in B1, then in C1 type the following: =B1-A1 Then, right click on C1 and choose format...

Excel: Converting File path to Hyperlink, adnan khan, documents and settings

adnan khan, documents and settings, excel sheet: Make sure the MakeHyperlinks sub is available to the workbook because this routine calls it: Sub MoveBToA() on error resume next application.displayalerts = false for i=1 to range( B65536 ).end(xlup).Row n=instr(cells(i,2).formula, [...

Excel: Converting Times, string manipulation functions, free microsoft excel

string manipulation functions, free microsoft excel, excell format: You ll have to use string manipulation functions like LEFT and RIGHT to break that string up into something you can convert to a standard time format. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch...

Excel: To Copy ranges from multiple workbooks to one sheet, mycell, sheet index

mycell, sheet index, k15: Well, I still don t see a question, like How do I make the code do...? . All you tell me is what you need. Regardless, unless I am overlooking something, I don t see where your variable ShName that refers to a sheet named Form is ever used. Form...

Excel: Copy total from diff sheets to one, sheet names, double sum

sheet names, double sum, age population: eram, Assume the total cell is in F100 on each sheet If you have the 1000 cities entered in A10:A1009, then in B10 put in =INDIRECT( &A1& !F100 ) then drag fill down the sheet. If you need something to enter the sheet names or just to go...

Excel: Copying Excel formula using drag down option, relative reference, f4 key

relative reference, f4 key, excel formula: The formula you need is =D2/$B$1 to save typing the dollars, press the F4 key with the cursor on B1 in the formula editor area. This is an ABSOLUTE reference, i.e. it refers absolutely (or always) to B1, whereas =D2/B1 is a RELATIVE reference so changes...

Excel: Copying multiple ranges between two open workbooks, work books, sushma

work books, sushma, ytd: Sushma, Here is one way: (with some made up ranges since you only provided one pair) Sub Copy() Dim v1, v2 Dim i as Long v1 = Array( AD8:AE17 , M12:N20 , P1:Z13 ) v2 = Array( B6 , D6 , F6 ) for i = lbound(v1) to Ubound(v1) Workbooks( YTD.xls ).Sheets...

Excel: Copying from multiple sequentially named worksheets to one mastersheet within same workbook., claim numbers, blank row

claim numbers, blank row, bd7: Sorry for the delay in replying - took me time to find time to write the macro! and even so, this macro is the basis for the process, but I m not sure what determines which range you want to copy - is it the last used row on the worksheet for example? ...

Excel: Copying and pasting between sheets, string source, joe joe

string source, joe joe, private function: Joe, guess that is a new behavior/feature of xl2007 Public Sub copy_paste(source As String, _ source_range As String, dest As String, _ dest_range As String) Sheets(source).Range(source_range).Copy Sheets(dest).Range(dest_range).PasteSpecial...

Excel: Copying VBA module of sxcel to a text, chip pearson, spath

chip pearson, spath, web page address: Saranthi_57, Chip Pearson has extensive information on working with code using code. It is probably better that I share that web page address with you so you have a great resource available to you: http://www.cpearson.com/excel/vbe.aspx for you particular...

Excel: Count, free microsoft excel, attendance sheet

free microsoft excel, attendance sheet, excel tips tricks: Sure, just make an IF function to set a cell equal to 1 if your conditions are met. Then add those up. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial...

Excel: Count the first instance of a value only, countif, pivot table

countif, pivot table, first instance: Your count is almost right but would return the same number at each instance of the organisation - so options are (1) use a pivot table to group the data together - but as you only want to count the organisations, possibly overkill (2) use data, filter,...

Excel: Count If function with filter, pivot table, workaround

pivot table, workaround, nate: Nate, I created a quick workaround for this, but I don t know if you ll like it. With the filter in place, rows will be hidden, as you know. So apply the filter so rows are hidden. Take a spare column to the right of the database (or even insert...

Excel: Count multiple columns in excel, sumproduct, males and females

sumproduct, males and females, sex male female: Rusty, Write a followup that includes the failed equations, and the results they reported. I can t tell what you did wrong unless I can see your work. However, if you are trying to COUNT things you should use COUNTIF(), not SUMIF(). That s...

Excel: Count unique data in unfiltered rows, counta, pivot table

counta, pivot table, occurances: Chris, Please start over as I don t understand what you re getting at. There are several things in your statements that puzzle me. You said: ....when I use a filter on it I need the numbers to change. A FILTER does NOT change the numbers...

Excel: Counting with check boxes, check boxes, excel spreadsheet

check boxes, excel spreadsheet, full contact: I THINK I d be inclined to have a small macro, attached to a button on the spreadsheet, which when clicked increased the value of a cell sub IncreaseMe range( b1 ).value=range( b1 ).value+1 end sub is all the sub needs to be - obviously replace B1...

Excel: Counting colors, blue indigo, color word

blue indigo, color word, simpler method: Stuart, Are these colors actually cells formatted as the colors, or are they the words RED, BLUE, INDIGO.... etc, typed into the cells? If they are the cells formatted as the colors, I don t know of any way to count them. if they are the words,...

Excel: Counting duplicates and listing the rows they appear in., computer name, pc1

computer name, pc1, different computer: Per, The reason you have this problem is because you have designed an unworkable data structure. It would be much better to re-create your database so it is a real database that you can ask questions of. What I would have done is make columns called...

Excel: Counting green digits., len function, vba code

len function, vba code, colorindex: This works on one cell at a time: =CountByColor(A1) Function countByColor(rg As Range) Application.Volatile True If rg.Cells.Count 1 Then countByColor = 0 Exit Function End If n = 0 For i = 1 To Len(rg) ...

Excel: Counting instances of "" depending if crityeria from another cell is met, evaluation score, double negatives

evaluation score, double negatives, pivot table: You COULD possibly use a pivot table to analyse the data (would give you all the results) but a multiple condition COUNTIF can be done like this =sumproduct(--(Evaluations!$AE$2:$AE$1000= What to do next ),--(Evaluations!AF2:AF1000=3)) The double negatives...

Excel: Counting number occurences of the data seen after a filter has been applied., excel, fiter

excel, fiter: There is no single formula that can do what you want. For example, you can use SUBTOTAL to aggregate only the visible rows on a column with a given operation, but you cannot include conditions on that formula. For example, a formula like this will count...

Excel: Counting times, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: Use IF to create a new value in a second column that s either 1 or 0 if the time falls in your range. Then COUNT that column at the bottom. You can use the IIF function to perform If/Then analyses on your data. Please see the following FREE video tutorial...

Excel: Create summary from a sheet that takes data from sheet that's calculated based on a drop-down cell, cell a1, customer name

cell a1, customer name, variable value: Shradda, This is called a SCENARIO. I haven t used this feature very much so I can t give you the details. But the idea is that you: a) feed in a variable value from a list of values: this would be the customer name b) the workbook calculates...

Excel: Creating a quote from an excel spreadsheet, spreadsheet problem, quotation request

spreadsheet problem, quotation request, quantity column: If I understand the question right, you can use data/Filter/Autofilter, and on the dropdown for the quantity column, you can filter by items 1, then you can select all the visible items (alt/; or goto special/visible cells) and copy them and paste to another...

Excel: Creating a variable that would make it easier for the macros, addre, drag down

addre, drag down, dest: Hi Shawn How are you? What about this? Sub Drop_Down() ActiveSheet.Unprotect Password:= 1234 Drop_Down Macro Macro recorded 3/26/2009 by shawnh Dim orig As Range, addre As String, dest As String addre = InputBox( Range from which you want...

Excel: Currency symbols, format cells, currency symbols

format cells, currency symbols, seminar book: The problem is, there s no real $ in the cells, it s only in the formatting. I m afraid you can t do a global edit/replace... you do need to unprotect, select the cells with currency & replace the formatting. Sorry -- no magic bullet here that I know of...

Excel: Custom cel format, free microsoft excel, format cells

free microsoft excel, format cells, excel tips tricks: Convert the cells ahead of time to TEXT (right-click, format cells) or prefix each item with a single quote. 10-41 This will force it to TEXT. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch...

Excel: calling macro when cell value changes, target address, drop down list

target address, drop down list, spreadsheet: Say you want this code to run when the changed range is entirely within col A (as when user changes a dropdown in that col), but not run if the changed range goes beyond col A (as when user deletes an entire row). The code whould be like this: Private Sub...

Excel: Getting a cell to reference a table, week 1, vba

week 1, vba, aidan: If you want something to update by clicking, it will need some VBA to achieve it - possibly easier would be to use a filter based on a dropdown selection - it s quite hard to read from the picture exactly what you are trying to do but if you d like to email...

Excel: change cell reference number by an n-amount, blank rows, cell reference

blank rows, cell reference, reference number: The INDIRECT worksheet function will convert something that looks like a reference into a reference so =indirect( D & 1293+35) would equate to =D1328 HOWEVER, you would still have to work out how to get the 35 and 1293 in place - perhaps using...

Excel: changeable data validation list range, data validation, reference example

data validation, reference example, dynamic range: I would do this with a dynamic range - defined by insert, names - the name can be anything, but would be =INDIRECT( FORM!$C$40:$C$ &39+COUNTA(ServicesRows)) Your concatenate is fine, but can be done the same with & signs, and as you can see, didn t need...

Excel: changing the colour of a number based on result, vlookup, b14

vlookup, b14, spreadsheet: Marcus I m guessing that you want to know how to make the cell format when the number is positive. You can do this by making the cell with the formula in it the active cell, then go to toolbar/format/conditional formatting/ select cell value is , select...

Excel: changing columns and sorting, excel, cut

excel, cut, sort: Sorry for the late, I have been busy on the weekend. To change the column Q to the front of the spreadsheet, you can select the full column by clicking on column header, go to menu Edit- Cut, select the full column A, right click and select Insert Cut Cells...

Excel: Make changing data static, audit schedule, max column

audit schedule, max column, audit management: Steven, This is a particularly vexing problem for Excel, but perhaps in your case there is a workable solution. How about if you add ten columns and keep a rotating history of the audit dates in them? The in an 11-th column take the MAX of the...

Excel: changing font letters from Capital letters to small letters, cell a1, excel sheet

cell a1, excel sheet, capital letters: Abdul The formula LOWER(A1)will change the value of A1 from upper case to lower case. You can apply this formula form one sheet to another to copy and convert the text. The formula =LOWER(Sheet1!A1) would copy the value in sheet1, cell A1 to the formula...

Excel: chart problem, isnumber, carrier name

isnumber, carrier name, dear bill: Sami, I can t tell you how to do it right until I know how you are doing it wrong. What is failing? What results do you get? Your problem is NOT due to filtering or conditional formatting. The chart will display only the data that the filter...

Excel: checkboxes, control toolbox, check boxes

control toolbox, check boxes, text boxes: Phil, The easiest way to do this is to have a macro delete any textboxes and then put in new text boxes according to your specifications. the code you show Loops through all the shapes in the worksheet (whether a textbox or not) and then as they are...

Excel: code to automatically hide rows, couple versions, autofilter

couple versions, autofilter, pivot tables: Elliot, You could select the column and do data= Filter= Autofilter and select the empty rows in the dropdown at the top of the column. This will hide the empty rows (in xl2007, the filter is the second to last button on the home ribbon). There...

Excel: Two columns into one in Excel, row move, do loop

row move, do loop, macro recorder: Laura, This actually quite difficult to do unless you write a simple macro to do it. You could try to write one yourself using the Macro recorder. This simple macro would perform ONE of the operations you need... insert row, move data (relatively)...

Excel: Two columns into one in Excel, vba, cells

vba, cells: You could do it with VBA, or possibly easier with a simple formula - which would go in a third column the first cell has =OFFSET($A$1,INT(ROW()/2),0) the second =OFFSET($B$1,INT((ROW()-1)/2),0) Select these two cells and then drag down to auto...

Excel: columns on scatter chart?, scatter chart, vertical columns

scatter chart, vertical columns, time scale: You CAN do it -- make sure the top of your x-values is blank and the top of your Y-values are labelled. Make your column chart, then make the scale a time-scale x-axis (which gives it the correct spacing), then format the x-axis as General. You need more details...

Excel: combining date & time from two columns, air quality reports, automatic conversion

air quality reports, automatic conversion, zero times: Renee, You ll LOVE how easy this is! All you have to do is ADD the DATE and the TIME with a simple PLUS! Then format the result as a date & time combined. This works because the DATE & TIME are stored as a specially-coded serial number....

Excel: compare two lists in excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vba programming: This is VERY difficult to do just in Excel. In Microsoft Access you could run something called a FIND DUPLICATES query, but in Excel you would require some VBA programming or a macro. Let me know if you have any other questions or comments. Also,...

Excel: compare time values, vba macros, time values

vba macros, time values, excel sheet: I get 0 for =Closetime(A1,B1:B2) because there is no time BEFORE the value in A1 in B1:B2. I don t get #Value. Where are you putting the function? It needs to be in a module. It IS automated -- you need only put the function in the cells where you want the...

Excel: conditional formating, conditional formatting, countif

conditional formatting, countif, menu choices: Sami, select B1:B20 with B1 as the activecell. Do format= Conditional Formatting then change Cell Value is to Formula is and put in a formula like =countif(Sheet1!$A:$A,Sheet1!$B1) change the sheet name to match you sheet. then click the format...

Excel: conditional formating and matching multiple, skill code, change colors

skill code, change colors, skill type: Laura, Its possible but somewhat involved. Read about the MATCH and INDEX functions and be sure you understand how these work, because they are the key to solving this. I presume that you want to lookup the number FROM the bigger table and have...

Excel: conditional formatting, excel conditional formatting, html excel

excel conditional formatting, html excel, written description: Theresa, This would be something that could be done with the conditional formatting feature of Excel - since that is you subject - it appears you know about it - so I am not sure what you specific question about the feature is. Fortunately there is a web...

Excel: convert text to .xyz, folder structure, file extension

folder structure, file extension, windows explorer: Rosli, I have never heard of a file extension of XYZ that means anything. So the only thing to do is SAVE AS, specifying whatever type you want the file to be, then, exit Excel, find the file in your folder structure and RENAME it (using Windows...

Excel: To make copies of sheet, exact scenario, list of departments

exact scenario, list of departments, excel workbook: Sub MakeSheets() Application.ScreenUpdating = False Set main = Sheets( Main ) Set newwb = Workbooks.Add On Error Resume Next For i = main.Range( A65536 ).End(xlUp).Row To 2 Step -1 ThisWorkbook.Worksheets( Info ).Copy After:=newwb.Sheets(Sheets.Count)...

Excel: copy first character in a cell, excel, data

excel, data, format: Probably Excel is converting the values into dates, so you may use YEAR instead of LEFT to get the value. Something like: =YEAR(A1) My suggestion in this case, if you want to store text, is to format the column that will hold the data from general to...

Excel: copy to list, target value, target range

target value, target range, hlookup: Chris Right click on the sheet tab of Sheet1, select view code and then paste in a macro like this Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, r1 As Range If Target.Count 1 Then Exit Sub If Len(Trim(Target.Value)) = 0...

Excel: copying cell color, conditional formatting, cell image

conditional formatting, cell image, color question: Pravin Assume the conditional formatting you show is for cell C9 and the text is in B9. in the conditional formatting dialog with B9 selected, you would change the cell value is dropdown to display Formula is for each condition and in the resulting...

Excel: copying hyperlinks, excel, cell

excel, cell: You are right, as I wrote the formula it repeats the name of the last sheet that was evaluated. Try this way: =CELL( filename ,A1) This should make it work for the current sheet every time. Sorry for the earlier mismatch. Hope this helps, Migue...

Excel: count the number is instances of two conditions, array formula, sumproduct

array formula, sumproduct, commercial markets: Kristin, Yes, there is a way. There are actually several ways. For Excel 2007: Use the COUNTIFS() function For Excel 2003: use the advanced (and undocumented) form of SUMPRODUCT. You can read about this at XLDYNAMIC.COM. For Either...

Excel: counting cells and conditional formatting, conditional formatting, red cells

conditional formatting, red cells, logical tests: You are right that there isn t any easy solution to this, BUT as you are using formulas to set the colours, the EASIEST way would be to use these formulas again to get the count - what I don t know is how complex the formulas are, but I m guessing we have...

Excel: counting cells and conditional formatting, conditional formatting, red cells

conditional formatting, red cells, background color: Rather than a function which would do it (there is nothing builtin -- you d need VBA code), you can use a function which tests the same conditions which make the cells red and another to test the same conditions which make the cells green. I can t help without...

Excel: counting cells and conditional formatting, conditional formatting, chip pearson

conditional formatting, chip pearson, bob phillips: Al, the easiest approach is to count the cells using the condition in conditional formatting. Since you have two columns, I would assume that the formulas are the same for all the cells - so you would just count based on the conditions in that formula....

Excel: creating records using vb, input value, value move

input value, value move, true range: James, First: As I said I broke the line into two lines to make it **easier to read in the email** so it wouldn t word wrap - however, I made it syntactically correct so it could be copied and pasted if it was. You don t have to break it into two lines...

Excel: how to cross duplicate? cross compare, excel, cross compare

excel, cross compare: This is a solution with formulas, and two extra columns. In this case I have entered the original data on range A1:B6 (with headers) on sheet1, and start the desired layout on sheet2 cell C1, with the two first columns reserved for extra formulas. With that,...

Excel: cross-sheets reference, change references, vladimir

change references, vladimir, a4: Vladimir The reference is to the cell, not the value in the cell, so when the values change in the referenced cell the linked values change. I can t think of a way to fix the link to the value. If you do your sort in the cells with the links in them...

Excel: cut and paste protected sheet, vba script, mouse type

vba script, mouse type, design criteria: Libby, Maybe something like this. Sub CutSelectedRow() Dim r as Range, r1 as Range With Worksheets( Main Excel ) If Selection.Parent.Name = .Name Then check to make sure a row is selected set r = selection On Error...

Excel: Data Validation list, data validation, a15

data validation, a15, ogilvy: Gloria, In excel 2003 and earlier, if you do Insert= Name= define, then select this name, in the refers to box it should have something like =Sheet1!$A$1:$A$10 If you put in additional entries in A1:A15, you should change this to include them. Click...

Excel: Date formatting, cell reference, text values

cell reference, text values, questioner: Greta, 1) I asked you to enter =ISTEXT(date-cell) and =ISNUMBER(date-cell) If you got NAME you must have done this improperly. read the function reference on how to use these two functions....

Excel: Date related problem, vlookup formula, dear tom

vlookup formula, dear tom, value error: Sami, If should be a matter of just doing an addition. i assume in C2 =A2+B2 format the cells as DD/MM/YYYY dates are stored as date serial numbers - the elapsed number of days from a base date. In Windows, the default base date is 1/1/1900...

Excel: Decreasing ComboBox Items, vba code, ogilvy

vba code, ogilvy, test 1: John, Use the click event of the combobox and after writing the selected item to whereever you are going to use it, then use the removeitem property of the combobox. It is easier for me to provide some sample code. Obviously you will need to write...

Excel: Deleting Blanks Macro Question, blank cells, first error

blank cells, first error, error handler: Jack, I saw the suggestion on the Application.CountA. I can t think of an occasion where that wouldn t work unless you have merged cells in the selection - then it might have problems - haven t tested it - but merged cells seem to cause problems with everything....

Excel: Deleting Cells and Parsing left over information, 7 digits, spredsheet

7 digits, spredsheet, internet explorer: The pictures always come through very small on AllExperts, so it s almost impossible to see what you need to do - I assume a web query wouldn t do it for you - is it possible to see some sample data in an actual sheet? My email is aidan.heritage@virgin.ne...

Excel: Deleting Duplicate Records based on 2 columns, duplicate records, question question

duplicate records, question question, column c: Your clarification didn t really clarify - I MEANT to ask but probably failed to do so, what constitues a duplicate record - is it when C and K are the same as another C and K? IN essence, the process is easy - either a delete if or an add if condition -...

Excel: Deleting Duplicate rows using 2 columns, logical test, column c

logical test, column c, macro code: Well spotted - I had in my head this was column A, and wrote the macro accordingly, then realised my error and corrected the starting cell but not the rest As to the code - this one is ALMOST readable as is basically, we need to keep a note of the current...

Excel: Deleting a row of data without deleting entire row, swift reply, question thanks

swift reply, question thanks, data changes: Alan, Sorry but I don t understand what you want to do. If you wanted to check the A162 to F261 on sheet3 and delete the rows in that section if any cell in the row contains #N/A produced by a formula I would do Sub ABC() Dim r As Range, r1 As...

Excel: Deleting rows considering two different columns, duplicate records, case c

duplicate records, case c, column c: Sorry, we re still not on the same page. If this is the case: C K 12345 02/05 12345 03/01 should one of these rows be deleted? (If so, which?) or, is this the case: C ...

Excel: Dependent Dropdown, excel, dependent list

excel, dependent list, camera: For the data validation, here is a link that explains a step by step process: http://contextures.com/xlDataVal02.html If you have any issues following it please let me know. Regarding the additional option, if you want to show the picture of the item...

Excel: Determining the existence of a sheet in a workbook, rng, boolean

rng, boolean, b2: Hi Quirino How are you? Please have a look at the following code which should preceed Dim i as Range of your procedure: Sub insertDelta() Dim i As Range Dim rng As Range, s As Worksheet, f As Integer, enc As Boolean f = 1 enc =...

Excel: Difference between Average and Sum/number in Excel, excel, sum

excel, sum, average: You answered my next question, because the most common reason of difference between those formulas are the empty cells. In this case, my recommendation would be to use the same range on both formulas, just in case there is any extra sheet considered on the...

Excel: Difference between First and Last date for same event, array formula, last date

array formula, last date, ogilvy: James, in D2 =IF(COUNTIF($A$2:A2,A2)=1,MAX(IF($A$2:$A$1000=$A2,$C$2:$C$1000))-MIN(IF($A$2:$A$1000=$A2,$C$2:$C$1000)), ) Entered with CTrl+Shift+Enter rather than just enter since this is an array formula. Then drag fill down next to your data. ...

Excel: Drop-down menus and additional sheets, drop down menus, vba code

drop down menus, vba code, drop down menu: It would need to use VBA running on the change event of the worksheet - though if using VBA I d be inclined to use a userform to get the data and populate it rather than hiding and unhiding sheets. If you would like to email me a sample file I can insert...

Excel: Dropped down menu and coloring a field, f2 g2, data validation

f2 g2, data validation, drop down menu: Assuming you mean by a drop down menu that you have used Data Validation, then you will need to deploy Conditional Formatting. You don t make it clear because you did not say if you want these cells to be a DIFFERENT color based on the selection made and/or...

Excel: Dynamic Graphs, dynamic graphs, xl2000

dynamic graphs, xl2000, dynamic range: Bill, I always use a defined name (with offset) as the source for the series when I want to have a dynamic range for the series. I suspect you could I tested on both xl2007 and xl2000 on putting in a formula directly as the source for a chart and I...

Excel: Dynamic Sheet Access, sheet names, sales ledger

sheet names, sales ledger, d7: You can put the sheet names in separate cells, like E1 & E2 or get the day of the month in E1&E2 by formulas, then refer to E1 & E2 in your formulas using indirect. For example instead of =SUM( 7:13 !B49) you can use =SUM(INDIRECT( &E1& : &E2& ;!B49 ))...

Excel: dashes!!!, isnumber, question title

isnumber, question title, proper answer: Annie, I need to know what these numbers ARE. Are they TEXT or are they NUMBERS? Pick one of the FAX numbers and write two equations nearby. If Cell F30 has one of these FAX numbers in it, then write ( in two cells, somewhere nearby), ...

Excel: data macro, voice of experience, macros

voice of experience, macros, fac: To setup the macro would basically be to write it - there is not much difference. Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to...

Excel: "date" formatting in mixed cell, help file, formatting

help file, formatting, cells: Elliot, Each time you &-in a number, embed the number inside a TEXT function. The second parameter of the TEXT function is a format code, which you can copy directly out of the formatting dialog box. The help file on the TEXT function will step-by-step...

Excel: How to differentiate file name and extract out data to paste in master file, cell a2, cell a1

cell a2, cell a1, c test: Hi Gimsiew, I assumed you meant in the next column as opposed to the next row, otherwise you d soon end up overwriting values (A2 becomes A3 becomes A4 and next becomes A5, where another value already has been entered). Use this VBA macro (run ProcessFiles,...

Excel: I am having dificulty applying your fix, positive integer, cells

positive integer, cells, hmmmm: Milton, Other people s worksheets are rarely self-explanatory . Which number (and I want you to name the EXACT cell!) do you want to ttansfer to where (name the EXACT cells!) ? I understand whatever the number is, you only want to transfer it...

Excel: How to disable editing while multiple sheets are selected?, false application, group mode

false application, group mode, target: For each workbook you want to ensure this doesn t happen, right-click the excel logo (near the file menu), select view code, put this in: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If ActiveWindow.SelectedSheets.Count...

Excel: disable message, dear bill, better solution

dear bill, better solution, suggestion: That all depends on how you have written the code and I have not seen the code to make an exact suggestion. You can trap for it implicitly using On Error statements or explicitly by using a series of IF statements. You will need to choose which is easier...

Excel: drop menus and matrix, drop menus, matrix rows

drop menus, matrix rows, index function: Drew, Certainly it is possible! What you need to do is make a matrix of all thew possibilities so you can look up the value you need from the appropriate intersection. This involves use of the MATCH & INDEX functions. So set up a table...

Excel: dropdown, data validation, column headings

data validation, column headings, column title: Octavio, Here is one of my methods for doing this. 1) make a table whose column headings are the names of each drop-down list you want. The first column should be the main one, call it MAIN. Populate it with the words you want to see in...

Excel: duplicate entries, duplicate sales, reseller name

duplicate sales, reseller name, sumproduct: Richard, in the next available column say column 51, so in cell AV2 =sumproduct(--($B$2:$B$5000=B2),--($F$2:$F$5000=F2),--($M$2:$M$5000=M2),--($AA$2:$AA$5000=AA2),--($AM$2:$AM$5000=AM2)) then drag fill down the column. Cells showing a number greater...

Excel: dynamic percentages, target weight, column c

target weight, column c, excel sheet: Charles Assume the weights (and only weights) are in column C starting in C1 Assume the target weight is in D1 then say in E1 Using your formula modified to get the last value in column C: =(OFFSET($C$1,MATCH(9999,$C:$C,1)-1,0)-D1)/D1 format...

Excel: Eccel Macro to delete a file, layout location, voice of experience

layout location, voice of experience, macros: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Editing sheet values with a custom made form, stock range, price sheets

stock range, price sheets, specification sheets: Mitchell, It should be E1 instead of A1 - must have been a typo on my part - I have it searching column 5 (column E), so the After argument must be in the range being searched. I don t see how it could delete anything. there is on code in there to...

Excel: Entering data in excel that will result in specified data appearing in another cell, cell b2, vlookup

cell b2, vlookup, saleman: Sure. If the service center is in column C: =VLOOKUP(A1,Sheet2!A1:C50,3,FALSE) basically: =VLOOKUP(ValueToLookUp,RangeToLookUp,WhichColumnInThatRange,FALSE) the False means you want an exact match. Something you wouldn t want if you were looking...

Excel: Entire Row Selection on a specifc criteria, coloured cells, conditional formatting

coloured cells, conditional formatting, cell interior: Azeem I guess by specified you mean you will select the cells/rows that you want it to look at: sub SelectColoredCells() Dim cell as Range, r as Range for each cell in selection if cell.Interior.ColorIndex = 6 then if r is nothing then ...

Excel: EXCEL, hyphen, parenthesis

hyphen, parenthesis, cursor: =SUBSTITUTE(SUBSTITUTE(A1, ( , ), ) , _ ) will do this for you where A1 is the cell you want to alter - put this in a blank column, then drag down, copy and put the cursor on the first REAL cell - use edit, paste special, values to overwrite the original...

Excel: EXCEL, layout location, voice of experience

layout location, voice of experience, macros: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Excel 2000, data validation, absolute references

data validation, absolute references, max lines: Dora, You have to use the decimal number validation, NOT the custom . Custom doesn t provide the MIN/MAX dialogs, which is where your IF statements must go. The statement you put into the MIN or MAX boxes must begin with an equal sign. If I...

Excel: Excel 2003, excel, header

excel, header: As far as I know, you cannot frame it within the header/footer section on the Page Setup. You probably know this by now, but my suggestion would be to format the first rows on the spreadsheet as you wish, and then repeat those rows. Hope this helps, Migue...

Excel: Excel 2003, free microsoft excel, cell a2

free microsoft excel, cell a2, excel tips tricks: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...

Excel: Excel 2003, vlookup function, absolute reference

vlookup function, absolute reference, list of names: Chris, For each value that you want to pull, use a VLOOKUP() function to retrieve it. The account num is the lookup-value, the main database is the table array, each value needs its own col-index-num, and the range-lookup,should be zero. Be sure...

Excel: Excel 2003 - Autocomplete, autocomplete, autocorrect

autocomplete, autocorrect, question thanks: Janet My apologies. You can t edit the list as it is taken from the preceeding cells, if you remove them from the list they wont show in the autocomplete. If you want to turn of the list have a look at this link http://support.microsoft.com/kb/263552...

Excel: Excel 2003 data entry, excel, enter

excel, enter: I would keep the ENTER key in the default option, and change the routine a bit to finalize the first and second column entry with TAB. This will make the cursor go to the right, and when you ENTER on the third column it will go to the beginning of a new line,...

Excel: Excel 2003 Formula, blank answer, f2

blank answer, f2, c2: Chad, Oops, I knew I should have re-read your question! =IF(OR(ISBLANK(c2),ISBLANK(f2)), ,IF(f2-c2 90, Yes , No )) You didn t say what you wanted if the days were NOT less than 90 days apart, so I made an equation that would say NO for...

Excel: Excel 2003 VBA to identify unmatched non-unique values, colour values, menu format

colour values, menu format, g data: Credits are in col A, Debits are in col B, and you want to match them up. First select Cols A:B, then choose from main Excel menu Then choose from menu Format, Conditional Formatting, Formula Is, =C1 0, Format, Patterns, [Yellow], OK. You say in your Subject:...

Excel: Excel 2004 v11.3 for Mac, countif, excel project

countif, excel project, result table: David, It would seem that I recently answered a very similar question like this. What s up with that? Anyway, I d do a COUNTIF() of each letter, for each column. Then apply the RANK function to the COUNTIF results. Or, you could SORT the COUNTIF...

Excel: Excel 2007 date calculation and formatting, logical function, conditional formatting

logical function, conditional formatting, undefined value: Pat, Excel always knows what date and time it is. Just put the TODAY() function in a cell and it will report the date. So you can add/subtract the current date from any other cell containing a date to find the number of days between dates, or you can...

Excel: Excel 2007 Smart tags, tag option, smart tags

tag option, smart tags, smart tag: Russell I can t seem to reproduce your problem, and of course that makes it difficult to solve. I haven t experienced the problem before. One thing you might try is copying the data to another spreadsheet that has the smart tags feature turned off,...

Excel: Excel 2007 Template, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vba programming: No way that I can think of that doesn t involve some VBA programming. Sorry. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Excel 2007 - VBA programming, seperate cells, inputed

seperate cells, inputed, ddo: Yes, I m happy to do VBA - but first I d need a little more information - is it always the number 5 you are looking for, an ddo the results need always to be in seperate cells? Also, you have 3 returns - is it always three returns? It may help to mail a...

Excel: Excel 2007 version, ancestry com, deleting files

ancestry com, deleting files, document names: If you use the FIND option, then you can select a file from this list and delete it (click it once, hit delete, or right click and delete from here) - that moves it to the recylcle bin, so you can empty the bin to delete it - holding down shift when hitting...

Excel: Excel 2007 version, ancestry com, kind of virus

ancestry com, kind of virus, excel expert: Wynneth, You said: I guess I should have 5,000+ lines of information. What? Where? In an Excel spreadsheet? I thought having 5,000 line is the Problem, not what you should have. I m confused. You said: The list is the DOCUMENT LIST...

Excel: Excel, homework problem, sumif

homework problem, sumif, countif: Mohammad, Because this sounds like a homework problem, I ll teach you how to solve it, but I won t solve it for you. It would help to know what Excel topic you were studying at the time, because that is probably what your teacher expects you to apply....

Excel: Excel, excel, time

excel, time: It depends on how you are storing the hours/minutes worked. If they are stored as time, a formula like this can be enough: =10*A1*24 Where 10 is the hourly rate. If they are stored like minutes, then you can change the formula to: =10*A1/60 If the values...

Excel: Excel, format cells, category 5

format cells, category 5, b1: Hi Dato How are you? Please do the following 1. Type in A1 =today() 2. Type in B1 10-07-09 3. Type in C1 =B1-A1 4. Format C1 to store a number e.g. Excel 2007 Cells - Format cells Number in Category 5. Save the workbook Cheers Adelaide...

Excel: Excel, unit rosters, medical status

unit rosters, medical status, e mail: SOUNDS perfect for a pivot table (but I don t know the layout of the data but if it s sortable, it s likely a good candidate) -- where you can put the unit rosters as a page field and you can use the built-in feature to Show Pages of the page field which...

Excel: Excel, current value, aidan

current value, aidan, b2: I m not sure what you are doing at the moment to get the values to go across to the two cells, but an IF statement would seem to be the way to go - I ll assume that Total Mileage is A1, 40p is B1 and 25p B2 in B2 =if(a1 =10000,a1,0) and in B2 =if(a1...

Excel: Excel, cell a2, olkahoma city

cell a2, olkahoma city, formula index: I ll assume both tables are on same sheet, starting in row 2, so that cell A2 is Stillwater, cell F2 is Oklahoma City, etc. In K2, enter: =A2&B2&C2&D2 and copy down col K as far as your data goes. You may white-font or hide this col if you d like....

Excel: Excel, colr, case white

colr, case white, vba code: I wouldn t (probably) use VBA for this - I d simply use a lookup table with the vlookup worksheet function. For VBA, the easiest way would be a select case function ReturnValue(Colr as string) select case lcase(Colr) case red returnvalue=1 case ...

Excel: Excel, excel application, client input

excel application, client input, formulae: Brian, I can think of several possible solutions. 1) when you protect a sheet, Excel offers you a list of checkboxes as to what you can allow the users to do. One of them is Edit Objects . Check that one. 2) put the charts on their own, separate...

Excel: Excel, separator character, excel spreadsheet

separator character, excel spreadsheet, data group: Naomi, This is a very common problem that has numerous solutions. 1) First thing to try is RELOADING the data into Excel in a different manner. If you imported this info as a TXT file, try importing (opening) it again, specifying a comma as the...

Excel: Excel, excel, match

excel, match: Use a formula like this on cell B2 of sheet1: =NOT(ISNA(MATCH(A2,Sheet2!$A$1:$A$10879,0))) I am assuming that the data is on column A, and that you have headers. This will return TRUE if the value is on the list and FALSE if not, copy it down the 800 numbers...

Excel: Excel ActiveWindow.ScrollRow, VBA Range

VBA Range: Hi Eric, Yes this is, but you need to pass the named row s row number to the command as part of the process: ActiveWindow.ScrollRow = Range( WhateverYouNamedYourRow ).Row Doug Smith Brainbench MVP for MS Excel www.brainbench.com www.abudant-solutions.com...

Excel: Excel Add in, aidan, macros

aidan, macros, heritage: Not easily - but if you record the macro, you can put it within the section I outlined - eg for each sh in activeworkbook.sheets sh.range( A1 ).value=1 next would set the value of A1 on all sheets to 1 - in other words, prefixing the macro you...

Excel: Excel Autocomplete & =value(), members name, autocomplete

members name, autocomplete, vba: see http://www.ozgrid.com/Excel/autocomplete-validation.htm for an explanation of the first part - as to the second part, I think you would need some VBA to achieve it - I m happy to help out with this if it helps, but would be useful to see the sheet...

Excel: Excel Autosum within a Macro???, autosum, d test

autosum, d test, two rows: Staci, if total will be in your last row in column A for example Dim r as Range set r = cells(rows.count,1).End(xlup) r is now a reference to last used cell in column A if instr(1,r, total ,vbTextcompare) = 0 then msgbox Total...

Excel: Excel array question, question question, a11

question question, a11, karel: Hi Jason, Pfew, still not entirely getting me head around this. So of the list of numbers 1,1,2,3,3,............,3,3 Do you want the row of the LAST largest number? All in one column? This does it for column A cells A1:A11: =MAX(IF(A1:A11=LARGE(A1:A11,1),ROW(1:11),0))...

Excel: Excel array sorting question, array formula, curly brackets

array formula, curly brackets, column c: Jason, You need an array formula to do this: =INDEX($C$1:$C$10,MATCH(LARGE($B$1:$B$10+ROW(1:10)/10000,1),$B$1:$B$10+ROW(1:10)/10000,0),1) entered with Ctrl+Shift+enter rather than just enter (since this is an array formula). After entering...

Excel: Excel Calculations, weekends and holidays, analysis toolpak

weekends and holidays, analysis toolpak, holiday dates: You need the WORKDAY function to do what you need - this is an add-in but a standard one - tools, add-ins, analysis toolpak - you THEN get this function which will add a given number of WORKING days to a date - it automatically discounts weekends, and you...

Excel: Excel Charts and formulas, excel, chart

excel, chart: If you have formulas, you may tweak them to show the #N/A error instead of a blank, as that will prevent those data points to appear on the chart at all. The formula you can use to generate the errors is NA(), and it can work something like: =IF(MONTH(TODAY())...

Excel: Excel Count Individuals not visits, excel count, time spans

excel count, time spans, pivot table: Simon, You ll notice that when you make a pivot table and you put the NAME in the row area, it gives you each name only ONCE, even if the name exists more than once in the data table. If you really make a pivot table that is limited to various time...

Excel: Excel - calculating time values which cross midnight & deducting breaks, work overtime, conditional tests

work overtime, conditional tests, pieces of the puzzle: Marmot, In cases like this, where there are lots of different possibilities to detect (did they work overtime, is there a break due, did they work past midnight, etc) , I find it best to add a bunch of columns off to the right that compute the various...

Excel: Excel - countif using ranges, tally chart, three colours

tally chart, three colours, score range: Sarah, You re looking for a multiple criteria record-summer/counter. Luckily there are several ways to do this! I can t believe you would even consider doing this by hand when there are all these spiffy neat functions just lying around, ready to...

Excel: Excel Data Swap, aidan, cells

aidan, cells, heritage: Not too clear how the swap is to take place - one option COULD be to select the block of cells you want to reorder and use a SORT to re-sort them independently of the rest of the data. Alternatively, a macro could be used to re-order - but the re-ordering...

Excel: Excel Dates, free microsoft excel, format cells

free microsoft excel, format cells, excel tips tricks: Select the column. Right-click on it. Select Format Cells. Change the date format accordingly. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go...

Excel: Excel, Flashing Colors on a conditional statement, sheet tab, conditional statement

sheet tab, conditional statement, rudimentary knowledge: Let s suppose the cell in question is D4. Right-click the sheet tab, select View code, put this in: Private Sub Worksheet_Calculate() DoInModl End Sub then Insert/Module, and put this in: Sub DoInModl() With Range( D4 ) If .Value ...

Excel: Excel Formatting, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, data validation: I would recommend using DATA VALIDATION and specifying TEXT as the allowed values. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Excel Formula, false answers, excel formula

false answers, excel formula, question thanks: I had assumed you had a list of values in another area - that would be the way to go - lets say 1 to 20 are in B1:B20 =countif($B$1:$B$20,A1) would give you the result. I GUESS it would be possible to do something without the values being explicit on...

Excel: Excel Formula, excel, references

excel, references: You may remove the $ sign from the 2 on the $E$2 reference, to make the row relative. This should make the formula easier to copy along the column, and it should return the proper values after any sort. =DATEDIF($E2, TODAY(), Y ) Hope this helps, Migue...

Excel: Excel Formula - IF and/or OFFSET??, excel, indirect

excel, indirect, offset: You can use the INDIRECT formula for that. For example: =OFFSET(INDIRECT(C10),8,0) This will return the value 8 rows below the cell referenced on cell C10. If you want the value 8 columns to the left, you can use a formula like: =OFFSET(INDIRECT(C10),0,-8)...

Excel: Excel Formulas, excel formulas, text string

excel formulas, text string, parenthesis: try =VALUE(RIGHT(LEFT(A1,SEARCH( ) ,A1)-1),LEN(LEFT(A1,SEARCH( ) ,A1)-1))-SEARCH( ( ,LEFT(A1,SEARCH( ) ,A1)-1)))) where A1 is the cell you are searching - I included VALUE to turn the text string into a number, though this part of the nest isn t strictly...

Excel: Excel Formulas, excel

excel: David, #1. in C10, you would put: =if(C7 0,if(c8 0,c9-(c9*5%),c9),c9) It does not make sense that c9 is the total of c9+c8, that would be a circular reference, so the formula above says, if c7 0 and c8 0 then in cell c10 show c9 minus 5%, otherwise just...

Excel: Excel Functions, g3 g4, dollar signs

g3 g4, dollar signs, excel functions: You cannot easily have a formula automatically down all 65,536 cells - and you probably wouldn t want it - but the formula =SUM($D$3:D4) put into G4 is a formula that can be dragged down to do the first part - the mixed reference here (use of dollar signs)...

Excel: Getting Excel to find items with a particular symbol or word and then paste it in a different column, FIND

FIND: Hi Julia, The FIND function can be used to identify characters in a string. Example: if cell A1 contained abc@yahoo.com, the following formula could test for the @ sign: =FIND( @ ,A1) The result would be 4, meaning it found the @ as the 4th character...

Excel: Excel formula doubt, excel formula, open tools

excel formula, open tools, referenc: Hi Ragin, Open Tools, options and click the calculation tab. Set iterations to one and check the iterate checkbox. Note: This setting is saved with your file BUT! it is set by the first file you open in Excel, so you may have to close down Excel before...

Excel: Excel formula...need help, logical tests, base details

logical tests, base details, sumif: I d be inclined to use a pivot table to do the data analysis, which would make the job automatic. Otherwise, it sounds like a multipe condition SUMIF function - which can be done with the sumproduct function =sumproduct(--(Range1=Test1),--(range2=test2),--(range3=test3),range4)...

Excel: Excel formula/ UDF, excel formula, udf

excel formula, udf, help menu: Hi Rishi How are you? I do not know any function for that. You may want to have a look at the list of functions on the help menu But I can suggest the following 1. Type the 5 values in A4:A8 2. Type in A1 the desired value 3. Type in B4 =abs(A8-A1)...

Excel: Excel formulas, sum formula, variable amounts

sum formula, variable amounts, d24: Johan, Something like this MIGHT be possible, but not the clever way you have tried. Whether it is possible or not depends on what type of changes you want to make to these formulas. If you want your formulas to simply operate on different...

Excel: Excel - If than functions, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: Eric, sorry for taking so long to get back to you. I loved your question so much, however, that I made a free video tutorial out of it for my TIPS AND TRICKS section on my web site. Here s the link: http://599cd.com/tips/excel/complex-if-example?key=AllExperts...

Excel: Excel Help, bump in the road, excel help

bump in the road, excel help, aidan: as you ve specified to copy as picture, that s probably the problem - try Worksheets( 1722 ).Range( AV6:BT13 ).Copy instead - this will be the same as a copy via normal copy and paste - although I don t know the reason for this macro happening of course...

Excel: Excel help, target address, keyboard shortcut

target address, keyboard shortcut, false application: Assuming N1 is the activecell, then use the macro you put in your original post and make the change I suggested. This will NOT enter a formula and will enter the current date/time in that cell. I can t see your file so I have to assume N1 is the activecell...

Excel: Excel lost my function!, free microsoft excel, sheet tabs

free microsoft excel, sheet tabs, excel tips tricks: Pat, I ve never encountered this problem before, but it sounds like Excel is missing an object library. If you right-click on any of your sheet tabs and go to VIEW CODE. Click on Tools References. For Excel 2007, for example, you should see the Microsoft...

Excel: Excel Macro, debug error, excel macro

debug error, excel macro, wks: I ran the code on my own laptop before pasting it here, and it ran for me with no problem. At the moment, I m on a laptop with Excel2003 only. It s possible that Excel2007 demands a new way of dealing with borders. If so, I m sorry I can t test that for...

Excel: Excel Macro, false application, option buttons

false application, option buttons, word selection: the word selection has no meaning on it s own, so it s interpreted as an undeclared variable, which as it s undeclared has no value so will never be equal to 1 within this subroutine. I would PERSONALLY have the option buttons given the same groupname to...

Excel: Excel Macro, cell a2, 6 cells

cell a2, 6 cells, excel macro: Then use the same method ActiveCell.Offset(0, 1).Select.Cut ActiveCell.Offset(x, y).Select ActiveSheet.Paste Then put all of this in a loop like: For X = 1 to 10 ActiveCell.Offset(0, X).Select.Cut ActiveCell.Offset(x+1, 0).Select ActiveSheet.Paste...

Excel: Excel Macro Error Handling, blank cells, daily basis

blank cells, daily basis, code selection: In the code below, the IF structure determines whether there are any blank cells within the Selection, and proceeds with selecting blank cells only after confirming that they exist: If Application.CountA(Selection) Selection.Cells.Count Then ...

Excel: Excel Macro help needed, excel macro, sure what this means

excel macro, sure what this means, control button: Eduardo, It was a typo on my part. the cInt(date) cause the overflow because the date serial is larger than the max integer. Here is the modification (which I tested and worked for me). Private Sub CommandButton1_Click() Dim res As Variant res =...

Excel: Excel Macro Question, excel macro, open application

excel macro, open application, msgbox: How do you know that the macro is not running when you open the file, assuming you open it BEFORE 9:45? Nothing will happen UNTIL 9:45 if you open it BEFORE 9:45. Try putting a msgbox as the first line of the code to see if that appears when you open the...

Excel: Excel Matching, ascii character, logical formula

ascii character, logical formula, excel help: Hi Fernando How are you? Please try the following 1. Type 1 in A1 2. Type in B1 = Product &CARÁCT(A1+64) The problem is that I do not know the name in English for the function Caract (A1+64). Please look up the Excel help menu for a function that...

Excel: Excel macro, array formula, jump height

array formula, jump height, excel macro: Steve, I would just reserve some cells at the top of the sheet where the user would enter the name, and then you could find the row of that persone using the match worksheet function. Then you could use the information from Match to do your calculations....

Excel: Excel - Numbers custom format, comma, custom format

comma, custom format: Ofer, In your example, wouldn t you want the number to be 1,234 instead of 12,34? It s the position of the comma that is different. Well, one way would be to divide your original number by 1,000 and use the result to be the display number. of...

Excel: Excel Protection, check boxes, excel worksheet

check boxes, excel worksheet, color font: Jessica, In Excel 2003 & 2007, when you protect a sheet, a menu appears which lists all the things you want to allow or disallow the users to do. There are check boxes for all kinds of things, so you should be able to allow the users to do (or not-do)...

Excel: Excel pivotting in vba, range statements, case range

range statements, case range, best guess: Joe, I don t see any reason to loop: Dim ap as Range set ap = Range( AP5:AP9,BB5:BB9,BN5:BN9,BZ5:BZ9,CL5:CL9,CX5:CX9 ) ap.Numberformat = 0.00% Of if you want to loop set ap = Range( AP5:AP9,BB5:BB9,BN5:BN9,BZ5:BZ9,CL5:CL9,CX5:CX9 ) for each...

Excel: Excel Question, insurance inventory, cell reference

insurance inventory, cell reference, farmers: Not sure what the source for the drop down lists are - I d have it as a named range, and to the side of this list of categories I d use a SUMIF formula =sumif(ColumnA,Category,ColumnE) Where ColumnA and ColumnE are the ranges for the category and value...

Excel: Excel Question, excel, lookup

excel, lookup: Does it really have to be a VLOOKUP? You can achieve the same result on a simpler way with a formula like: =AVERAGE(OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A100,0),1,1,4)) In fact, with this formula you can include new columns on the average just by changing...

Excel: Excel: Re-organise table data into list, array formulas, clever method

array formulas, clever method, input sheet: Dave, Would you mind sharing your workbook with me? I d like to see the results. If you will, you can send it to ExpertAtExcel@AOL.COM. Can you explain what you did to fix the circular reference? SUPER-CONGRATULATIONS on getting such...

Excel: Excel Splitting Rows, databses, custom filter

databses, custom filter, new location: A macro would be OK to do it, but you d need to know how to write one - the SIMPLEST route would be to use a blank column and enter =Mod(row(),3) Drag this down the entire data range (easiest way here - ctrl shift end, followed by Ctrl D to fill the...

Excel: Excel Stumper, stumper question, data validation

stumper question, data validation, diamter: Gwen, Thanks for your reply. So here are the steps you need to take to make this work. 1) Combine all the separate tables into one table. They all have the same structure so this is just a copy & paste. We ll call the resulting table the DATABASE....

Excel: Excel SUM shortcut on mouse, free microsoft excel, sum command

free microsoft excel, sum command, excel tips tricks: You would need a programmable mouse or special software for this. I don t think it s possible with just a standard mouse and Excel. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute...

Excel: Excel sorting copy and pasting to another sheet, column headings, pivot table

column headings, pivot table, filter option: Glenn You can use the Data Filter option, select the code you want and it will show only the ones you need, you can then copy and paste the cells to another table. You could also use a pivot table in the other tables but you would have to create it for...

Excel: Excel spread sheet, free microsoft excel, excel spread sheet

free microsoft excel, excel spread sheet, excel tips tricks: Not without a macro or some VBA code. Not that I know of, at least. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Excel IF statement for dates, conditional formatting, sop

conditional formatting, sop, excel if statement: Sherry, Your solution is to make a master summary sheet which brings in a copy of each persons summary. Then you can look at that sheet and see who needs training or not. If you already have the summary equations on each sheet, it should be easy to...

Excel: Excel to sum roll that match key word in col, allexperts, frontpage tips

allexperts, frontpage tips, spread sheet: Use the SUMIF function. Assuming you have 20 rows of data: =SUMIF(A1:A20, *repair* ,B1:B20) Enjoy. Let me know if you have any other questions or comments. Also, be sure to visit my FREE FrontPage Tips & Tricks Page at http://www.599cd.com/tips/FrontPage?key=AllExperts...

Excel: Excel Variable Use/Formula Reference in Cell, excel function, input cell

excel function, input cell, sums: Dan, There is an Excel function that does EXACTLY this! Its called SUMIF(). SUMIF uses a criteria to select which rows to sum. Normally, SUMIF sums the same column as the criteria is in..But, SUMIF has an extended mode that lets you specify...

Excel: Excel VB, excel vb, a1500

excel vb, a1500, test sheets: Hi Laura How are you? What about the following: Sub test() Dim FirstP As Range, LastP As Range, WaRange As Range Dim c As Range, Addr As String Rem Sheets( Dump WA_GSIL ).Select Set WaRange = Range( A1:A1500 ) For Each c In WaRange If c =...

Excel: Excel - VB/ Macros Help for Template, excel vb, template wizard

excel vb, template wizard, future dreams: OK, now I m thoroughly confused. I think it s time to send me the workbook/template. I don t know what you mean by Customer Name (I11,M11) and INV ### -- pretty confusing. I m hoping I ll get a better idea of what you want by seeing your workbook. Please...

Excel: Excel VBA, copy rows by date, string s2, excel vba

string s2, excel vba, datevalue: Marshall, the code you put in your post is identical to the code I sent you. Anyway, this has the ability to define 6 workbook names and the top row where to paste them in Down Time details. You and also specify the folder (I guess that is what you...

Excel: Excel VBA to search across multiple sheets, value case, word data

value case, word data, searchfor: Private Sub cmdSearchAcct_Click() Dim shtData As Worksheet Dim rngSearch As Range for each shtData in worksheets if instr(1,shtData.name, data ,vbTextcompare) then Set rngSearch = shtData.Range( A:A ) lisSearchresults.Clear...

Excel: Excel VBA search text in 3 columns, silicon controlled rectifier, input nand gate

silicon controlled rectifier, input nand gate, transistor bc547: Patricia, this code does what you want. since you don t need intermediate sheets, it copies results to Sheet2 Lightly tested, but it seemed to work well for me. Private Sub CommandButton1_Click() Dim FoundCount As Double Dim rFoundB As Range,...

Excel: Excel VBA write text based on another cell, cell c1, cell d1

cell c1, cell d1, cell b1: Daniel The formulas should look like this In cell A1 the formula would be =IF(B1=5, past due , ) This formula says if cell B1 is equal to 5 then put Past due in Cell A1, if it is not equal to 5 then put nothing in the cell. I am not sure if you...

Excel: Excel Visual Basic, boolean

boolean: Sorry -- try this: Private Sub Workbook_BeforePrint(Cancel As Boolean) If Range( H48 ).Value Yes and Range( H48 ).Value No then Cancel=True Msgbox You must enter Yes or No in this cell before printing. ,vbexclamation Range(...

Excel: Excel value changing, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more information on how to use the IF function: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts...

Excel: Excel - WORKDAY function to add partial days, excel, dates

excel, dates, decimal: You could combine the SUM and the WORKDAY, using the former for the decimals and later for the days. Something like: =WORKDAY(A1,B1)+MOD(B1,1)+MOD(A1,1) This way, if you use the result of the formula as the starting day for the next task, it will keep the...

Excel: Excel Worksheet w tables, excel worksheet, a1

excel worksheet, a1, worksheet function: from the description it sounds as though the indirect worksheet function would be what you need if the dropdown is in A1 and returns A then =indirect(a1 & !a1 ) would give the data at a1 on sheet A - but if a1 is changed to return B, this would THen...

Excel: Excel workbook with links crashing, www google co uk, google co uk

www google co uk, google co uk, macro security: I can see that this is an issue faced by others (http://www.google.co.uk/search?sourceid=navclient&ie=UTF-8&rlz=1T4RNWN_enGB318GB318&q=excel+2007+spreadsheet+with+links+crashes ) BUT cannot find any resolution to it- it s quite a lot of links, but it SHOULD...

Excel: Excell Times, military time, time format

military time, time format, right time: Put this formula in a cell, assuming A1 has the dates you mentioned =Left(Trim(A1),Len(Trim(A1))-1) This should strip the a or p from the end. Assuming you put this in B1, then in C1, for example, simply use =Timeserial(B1) UNTESTED ...

Excel: Excell Times, number fraction, cell a1

number fraction, cell a1, military time: Andres: Assuming the file actually has the items entered as time in Excel (and not as text), then you should be able to do this: 1) Right-click on the cell (or entire column) and choose format. Select Time on the number tab. Choose one of the military...

Excel: Extend Range of Macro Calculation, question thanks, lns

question thanks, lns, f3: ...Whenever a change any cell in the range specified x, the macro runs through all rows and calculations... That is what is supposed to do. It makes no determination of what the data MIGHT have been or WAS BEFORE you run the macro - it just evaluates...

Excel: External References in Excel - Reg, external references, excel sheet

external references, excel sheet, good answer: Krishnamoorthy I don t work with networked drives so I don t have much expertise in that area. I suggest when you do your copy that you try a copy/paste special/formulas and see if that works. I have tried to duplicate your situation and I can t without...

Excel: Extract text based on return carriage, carriage return character, text cells

carriage return character, text cells, ascii code: JJ, I don t quite see how you can have the data you describe, because when you enter a RETURN into a cell it terminates the data entry into that cell. Nevertheless, this is what you say you have! But I d suggest maybe you have ALT-ENTER as the...

Excel: Extract text from a cell, cell a2, numerical characters

cell a2, numerical characters, alphanumeric text: Abhijit, The way to do this is to try to FIND() each of the 10 numerical characters, and then take the MIN() of the 10 find results. Some FIND() results might come back #NA so you need to change those to a number larger than the text string LEN when...

Excel: Extract value from R9C15 from multiple sheets, 3d range, vlookup formula

3d range, vlookup formula, cell values: Marion, You re in luck! There is a way to create a 3D range through all your worksheets! Once you have that, you can handily get to the cell values. The help file has a great page on how to create, manipulate & use 3D ranges. Search for...

Excel: Extract values form one sheet and display in another sheet, cell a2, vlookup formula

cell a2, vlookup formula, excel formula: Geeta If I understand your sheet setup correctly, I believe you can use the VLOOKUP formula to find the matching id numbers and bring over the ratings. The formula might look like this. =IF(ISERROR(VLOOKUP(A2,Sheet1!A$2:I$90,9,FALSE)), ,VLOOKUP(A2,Sheet1!A$2:I$90,9,FALSE))...

Excel: Extracting Rows, blank file, autofilter

blank file, autofilter, ogilvy: in file B, next to the 1000 entries put in a formula like for example, in B2 of file2 =if(countif([File1.xls]Sheet1!A:A,A2) 0, Copy , No ) then drag fill that down the 1000 rows Now select column B (or the column with the formula) and do Data=...

Excel: Extracting Rows, excel, filter

excel, filter, copy: You could the MATCH formula and the autofilter to create the list, and then copy/paste the results. The formula can be something like this on file B: =NOT(ISNA(MATCH(A2,[Book1]Sheet1!$A$2:$A$101,0))) And then filter (menu Data- Filter- Autofilter) for the...

Excel: Extracting Rows, zeros and ones, blank file

zeros and ones, blank file: I d do this with a formula in a helper column =countif(FileARange,FileBcell) where FileARange is the 100 entries from file a, and FileBCell is the cell you are checking in the 1000 entries - drag this down, you will then get zeros and ones (IF duplicate...

Excel: Extracting Valid Data from an Excel Column, array formula, curly brackets

array formula, curly brackets, 6 cells: Alastair, You can do it, but it will only work if both workbooks are open (since you say you will be linking to several workbooks,then all the workbooks would need to be open). I don t think that is what you want, so I would suggest putting a sheet in...

Excel: editing data, trunc, 1234567

trunc, 1234567, number 3: John, Sounds crazy but here goes! Use a series of steps (which may require more than one cell). 1) divide the nuimber by 100,000. 2) TRUNC() number 1) so only the integer part remains. 3) subtract number 2) from number 1) to get...

Excel: excel 2003-data formatting, weather down, bob bob

weather down, bob bob, tab characters: Bob, I d suggest that you save the Excel file as a TXT type (using SAVE-AS). This will give you a tab-delimited file (I think). Then you can import this (or open it) with WORD, and use the Find & Replace (Special) to replace all the tab characters...

Excel: excel column header pointer, excel, references

excel, references, move: In order to move cells, you can either copy/paste/delete original or cut/paste. Both have different effects: - Copy will adjust the relative references on the formula (without dollar signs) to the new position. Formulas that referred to the original cells...

Excel: excel copy and paste, excel, vba

excel, vba: Even with VBA the PasteSpecial feature doesn t work in the way that you need. You could try with something like: Sub CopyPositives() Dim rngOrigin As Range, rngDest As Range Set rngOrigin = Sheets( Mapa3str ).Range( A3:ES530 ) Set...

Excel: excel two criteria sumif dispite reading help cant get it to work, array formulas, result table

array formulas, result table, value customer: Brett, There is certainly a way (or two) to do what you want, but before I write a big explanation I need to know a few additional things. 1) Do you know beforehand what the products for each customer are, or is that something you need to find...

Excel: excel date calculation, date function, workday

date function, workday, step 6: Kristine, It may not be simple, but I have thought of a way. I m developing this live as I type, and my method is to use a column for each step. At the end, you can combine the steps together if you wish. I usually actually create the steps in...

Excel: excel dates, format cells, decimal number

format cells, decimal number, whole number: 032609 is a decimal number, which to excel means you have typed a whole number - which it is happy to display as a date - 32609 is the serial number for 04/11/89 - you NEED to enter dates as dates, so March 26 is fine, 03/26 is fine but 032609 will get interpreted...

Excel: Having excel determine which weights to assign to each category to match desired percentages, data cells, hello bill

data cells, hello bill, circular references: Sheldon, I don t think this can be done without a macro. I tried several crafty and clever solutions, all of which involved setting the calculation method to MANUAL and the number of iterations to 1. This was necessary because it seems inevitable...

Excel: excel file, zeros, a9

zeros, a9, berth: Hi Berth How are you? That is an interesting question: it seems easy but it is not. I have btried the following 1. Suppose your data is in A1:A9 2. in B1 type =small(A1:A9, countif(A1:A9, =0 )+1) This works if there is no number smaller that zero....

Excel: excel find in array, excel, match

excel, match, array: You can use an array formula similar to this: =INDEX(L1:L5,MATCH(1,(A1:A5=N1)+(B1:B5=N1)+(C1:C5=N1)+(D1:D5=N1)+(E1:E5=N1)+(F1:F5=N1)+(G1:G5=N1)+(H1:H5=N1)+(I1:I5=N1)+(J1:J5=N1)+(K1:K5=N1),0)) Enter it with Ctrl+Shift+Enter. In the formula I am supposing...

Excel: excel formula, excel, date

excel, date, difference: You can treat dates as numbers and do the same operations that you would do between them. For example, in this case you could do something like: =A2-A1-7 Supposing A1 and A2 contain the dates. You may probably need to format the resulting cell as number...

Excel: excel formula, blank cell, cell references

blank cell, cell references, excel formula: It s certainly possible - it sounds like it is actually just a case of adding the two values up - a blank cell evaluates to zero - so it would be something along the lines of =BalanceCell-Receivable1-Receivable2 where the balanceCell, Receivable1 and...

Excel: excel formula, cell b1, cell a1

cell b1, cell a1, bj2: I ll interpret your question thusly. You ve got data that starts in cell A1 and continues down col A. You want to count the # of times that there s a cell with the value BJ2.1-BJ3.1 STM13 AND a cell immediately below it with the value CIRCUIT in...

Excel: excel formula, bj2, conditional test

bj2, conditional test, blank cell: Solay, The easiest way to do this is with a column of IF statements which check for the conditions you specify... if the conditions are met, increment the number one row above me , otherwise put in a -1 (minus 1). Furthermore, if the number above...

Excel: excel formula, excel, sumproduct

excel, sumproduct: Solay, To handle this task, you can use the sumproduct function: =SUMPRODUCT(--(A1:A13= GL2-RR2 STM10 ),--(A2:A14= CIRCUIT: )) Adjust the A1:A13 and A2:A14 rages to match your range. Joseph Harris http://articles.excelyogi.com/cool-products...

Excel: excel formula, cell b1, cell a1

cell b1, cell a1, rr2: I ll interpret your question thusly. You ve got data that starts in cell A1 and continues down col A. You want to count the # of times that there s a cell that begins with the string GL2-RR2 STM12 AND a cell immediately below it with the value CIRCUIT...

Excel: excel formula, greek letter sigma, free microsoft excel

greek letter sigma, free microsoft excel, sigma press: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little button on the query toolbar that looks like a wierd E (actually the Greek letter Sigma). Press that and you get a new Total row. Just add the fields you...

Excel: excel formula or Macro?, vlookup function, leftmost column

vlookup function, leftmost column, excel formula: Peggy, is it possible Yes since the source data has employeed name on the leftmost column of the data you would use the vlookup function It is explained very well in help - so start there. Here are much more extensive details on vlookup and the...

Excel: excel help, lip shape, best bet

lip shape, best bet, application work: Collins, Excel may not be your best bet to make your application work. The animation you describe (having one picture appear, and then be replaced by a second picture) MIGHT be possibly done with a macro; however, I don t do macros so I can t...

Excel: excel ranking, RANK

RANK: Hi Troy, RANK can go both ways, depending on how you set the third argument. =RANK(number,ref,order) If order is omitted (or set to zero or FALSE), then you get the default ranking in descending order. Change the order value to 1 (or TRUE or any...

Excel: getting excel sheet names, sheet names, myname

sheet names, myname, excel sheet: With a formula in each sheet: =MID(CELL( filename ),FIND( ] ,CELL( filename ))+1,255) With VBA Function: Function MyName() as String MyName=Application.Caller.Parent.Name End Function then use =MyName() in the sheet With VBA Function available...

Excel: excel tabs, category tab, tab section

category tab, tab section, info thanks: It COULD be done - it would need VBA to achieve it though, so would ONLY work if the users enabled macros the macro (one per master worksheet) would be along these lines Private Sub Worksheet_Activate() Sheets( Sheet2 ).Visible = False Sheets(...

Excel: excel Vba, runtime error 91, h2 h3

runtime error 91, h2 h3, excel vba: Randy, I missed your declaration Dim housecode1 As Range but the result of an inputbox is a string - so I had assumed you declared it string, but guess I didn t see you hadn t. So the solution to that is to declare it as string. The previously mentioned...

Excel: excel vlookup, core platform, f78

core platform, f78, doing the right thing: I m not sure I follow (I don t know what DealAnnualVolume is -- if it s 7, it s doing the right thing). It looks up the value in the FIRST column (A70:A78), and for a 7 it is on the 3rd row. If you want it to be the row before, what would happen if you looked...

Excel: execel, bottom right, answer the question

bottom right, answer the question: Not sure what you are asking - the formula =a1+b1 would add two values together. clicking on one cell and holding down ctrl then clicking on another cell should show the SUM in the bottom right of the screen copying one cell, selecting another...

Excel: how to expire Excel file after certain date, hardeep, john doe

hardeep, john doe, excel sheet: It will need macros - I d be inclined to have the master sheet set to VERY HIDDEN - so only if the macros are enabled would you be able to do anything - Sheets( Sheet1 ).Visible = xlSheetVeryHidden sets a given sheet to VERY hidden Sheets( Sheet1...

Excel: extract rows based on a number of values, copy worksheets, q cell

copy worksheets, q cell, phase number: Paul, If the other workbook is open in the same instance of excel or you want to have the macro open it. Assume it is open and it is named Master.xls with data on sheet1 Sub copyData() Dim r As Range, cell As Range, r2 As Range With Workbooks( Master.xls...

Excel: extracting name or word from a cell, array formula, curly brackets

array formula, curly brackets, ogilvy: Yu, In B1 put in this formula =MID(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDIRECT( 1: &LEN(A1))),1)),0),255) It must be entered/committed with Ctrl+shift+enter rather than just enter since it is an array formula. If you enter it properly, if you reselect...

Excel: Filter and Copy Macro, rcell, copy columns

rcell, copy columns, filter records: jeff, This was tested and worked for me: (designed my workbook to match what your code shows it is looking for or you described). If your workbook isn t laid out that way, then your mileage may vary. Sub Recall_BT2() Dim rr As Range, r1 As Range...

Excel: Filter Data on basis of Dates, excel advanced filter, filter data

excel advanced filter, filter data, question thanks: Well, my answer is still the same. You get FALSE because there are no dates between the dates you have typed. As far as a macro is concerned, then sorry, I don t write macros for people on this site. Please see my introduction. Try this link to learn...

Excel: Filter Macro in Excel '07, recruitment methods, macro in excel

recruitment methods, macro in excel, external candidate: Obviously I don t know the data structure, but to me it SOUNDS as though the query to Access should be the key- this could be modified to bring in ONLY those employees in the appropriate group - so I guess the way you are querying it needs modification (which...

Excel: Filter of my own, proprietary technique, database terminology

proprietary technique, database terminology, course reference: Dario, There is a way to make this happen but its VERY complex. I can outline it for you in a top level way but the actual equations will all depend on where the various cells are. 0) The general concept is to mark the rows (also called records...

Excel: Filtering questions answered with a yes or no in another spreadsheet (without using Autofilter), free microsoft excel, mexico cuba

free microsoft excel, mexico cuba, excel tips tricks: I couldn t explain quickly and easily how to have them all directly one after the other, but you could do something like this: =IF(Sheet1!B1= yes , USA , ) This would make the current cell say USA if Sheet1!B1 had a yes in it. You could do this...

Excel: Filtering records in Excel, cell a2, column c

cell a2, column c, home tab: Bianca, I will assume the employee always appears first in the data. Assume the 88 Employee appears in row 2 and the 88 is in cell A2 in the next available column (for illustration, I will use column C), so in C2 put in this formula =if(countif($A$2:A2,A2)...

Excel: Finding a 10 digit number within a text field, digit number, evelyn

digit number, evelyn, sequences: I m sorry but I m STIL confused. If there are 2 ranges of numbers like in your example, do you want to see the smallest to largest of EACH range so in your example you d see 9855123450 9855123451 9855123452 9855123453 ... 9855123460 9855123461 9855123420...

Excel: Finding duplicates, duplicat, ogilvy

duplicat, ogilvy, occurance: Evelyn, in B1 or the next available column (or insert a new column B) put in a formula =if(countif(A:A,A1) 1, Duplicate , ) then drag fill down the column. Also, that doesn t require that you sort the data first. If you want to ignore the first...

Excel: Finding next value, value question, cell reference

value question, cell reference, question thanks: Error trapped version first =IF(OR(A2=1,B2=1,C2=1),MIN(IF(ISERROR(MATCH(1,D3:$D$1000,0)),9999,MATCH(1,D3:$D$1000,0)),IF(ISERROR(MATCH(1,E3:$E$1000,0)),9999,MATCH(1,E3:$E$1000,0)),IF(ISERROR(MATCH(1,F3:$F$1000,0)),9999,MATCH(1,F3:$F$1000,0))), ) now to...

Excel: Finding number of characters in a cell and..., ltrim, range c

ltrim, range c, excel sheet: Hi Sirajuddin How are you? Please do as following: 1. Run Visual Basic editor (it depends on the Excel version.( For Excel 2003, Tools-Macro- Visual Basic Editor) 2. Insert - Module 3. Copy Sub leadingzeros() Dim R As Range, c As Range Set R =...

Excel: Finding number of characters in a cell and..., excel sheet, vba

excel sheet, vba, len: As I said it was untested and I assumed you knew something about macros and could have fixed the syntax error. Secondly, ONLY saying that you are getting some error and then expect me to know only from that WHAT the error is is like you telling a doctor...

Excel: Finding number of characters in a cell and..., cell a1, data cells

cell a1, data cells, excel sheet: Say for example that you have your data starting in cell A1 and extending down col A. In cell B1, enter the formula: =IF(LEN(A1)=5, 00: ,IF(LEN(A1)=7, 0 , ))&A1 and copy it down col B. Col B will then display your correct results. If you re familiar...

Excel: Finding number of characters in a cell and..., excel sheet, ms office 2007

excel sheet, ms office 2007, len: Siraduddin, This can easily be done with a compound (nested) IF statement. If the text is in cell A20, then in cell B20 you would write: =IF(LEN(A20)=5, 00: &A20,if(LEN(A20)=7, 0 &A20,A20)) Your question didn t say so, but I ve assumed...

Excel: Finding number of characters in a cell and..., cell references, excel sheet

cell references, excel sheet, a1: Sirajuddin You can use this formula =IF(LEN(A1)=5, 00: &A1,+IF(LEN(A1)=7, 0 &A1, )) Where A1 is the cell that contains the time that you want to convert. Remember to format the cell that you put the formula in as text. You should be able to...

Excel: Find_Sum, science problem, science world

science problem, science world, jpg file: Binh, Excel does not have a function to do this. Its actually a terribly complicated computer science problem, especially if you don t know how many number are involved. There are just so many possibilities. The only way I could approach this is...

Excel: Follow up to Earlier question, blank row, false application

blank row, false application, info column: Assuming the following: Your data, in its original state, starts in cell A1. The year values in col A continue down the column without break; the first blank cell in col A marks the end of the data. You want to insert a blank row in each case where the...

Excel: Follow up from Excel Issue, google search, dynamic ranges

google search, dynamic ranges, google: Dan, I ll refer you to my original answer which discussed DYNAMIC RANGES. This is what [I think] you need to learn to allow your ranges to adjust to the amount of data they encompass. They work great but are tricky to learn. Read about the OFFSET...

Excel: Formatting cell X based upon cell Y, conditional formatting, format button

conditional formatting, format button, home tab: Mike Select cells C9 down to C - last row you want to format. Make sure C9 is the active cell as the formula we will use will be set up to be relative to the activeCell (C9). In xl2003 and earlier, in the menu, choose Format, then conditional formatting...

Excel: Formula, format button, spreadsheet

format button, spreadsheet: Select all the dates, use Format/Conditional Formatting, change Cell Value Is to Formula Is , enter this formula (this assumes D1 is the Active cell -- change accordingly: =AND(D1 =TODAY(),D1 =TODAY()+90) then Click the Format button, and the Font (or...

Excel: Formula to calculate additional rent % of sales after a guaranteed fixed sum is met, preset limit, e32

preset limit, e32, e4: Milton, Here is an answer for you. Its very simple and I am sorry it took so long. What you want is this: If E29 E4 take 20% of the difference, other wise E29 = E4 so put in a zero. Piece of cake. The formula you need is in cell G31....

Excel: Formula in Excel, formula x, whole number

formula x, whole number, two ways: Hi Kay, Two ways: 1. Round the number in the original location using the ROUND function In the cell with P use: =ROUND(ORIGINAL_FORMULA,0) the other formula stays the same 2. round the number in the new formula using the ROUND function: X*Y/ROUND(P,0...

Excel: Formula Help!, excel, if

excel, if: I am not sure on what is the layout of the formulas, but probably you could use the IF function: =IF(A1 50,Value1,IF(A1 100,Value2,IF(A1 200,Value3,Other))) If this doesn t work for you please let me know, and send more details on how the data is structured...

Excel: Formula Keeps giving Circular Reference, circular reference, quote detail

circular reference, quote detail, person group: A circular reference means that a formula is referring to itself, so getting into a potential continuous loop - but the formula itself doesn t give me any clues as to what you are trying to resolve - I don t know where the error will be - but if you would...

Excel: Formula or Macro in Excel 2007?, macro in excel, a6

macro in excel, a6, c6: in C2: =IF(COUNTIF($A$2:A2,A2) 3,1,0) and fill down. in C6 in this example (the first delete) you ll see =IF(COUNTIF($A$2:A6,A6) 3,1,0) and the COUNTIF part will be 3 so this formula will return 1. Before you manually sort you need to copy all these results...

Excel: Formula Question for MS Excel 2003, Using SUMIF, row numbers, search range

row numbers, search range, shipping department: Ian, What you need is to have those ranges become DYNAMIC so they adjust to the data automatically. This is done with the OFFSET function, which is used to specify a range depending on other cell values in the spreadsheet. In your examples and...

Excel: Formula for Time Keeping, format cells, time type

format cells, time type, a3: In cell A1, enter 9:50 AM In cell A2, enter 7:15 PM Select cell A3. From main menu, choose Format, Cells, Number, Category: Time, Type: 37:30:55, OK. In cell A3, enter =A2-A1 A3 now displays 10:10:00, meaning that between 9:05 AM and...

Excel: Formula for Time keeping, excel, cell

excel, cell, format: When that appears is probably a matter of cell format. Try first to increase the column width, so you can see if it is an error or it is a format that didn t fit. If it is the later, you can change the cell format to something that fits better. If it is...

Excel: Formula vlookup, good answer, colum

good answer, colum, many thanks: Jody Sorry to be late in answering, I must have missed your question. I am not sure if your cumulative amounts are cumulative for the month or cumulative for the year. The combination of conditions that you want to measure are difficult for me to write...

Excel: Formulas in workbook, 3d range, desire one

3d range, desire one, line numbers: Robert, If you desire ONE formula to read ALL your worksheets, it is POSSIBLE but that might be a big long complex and difficult formula. Do you really want to condense ALL your worksheets into ONE NUMBER??? That s all that a single formula can do....

Excel: Formulation table, code column, blank cell

code column, blank cell, material code: Sub test1() Dim rw as Long Application.Goto Reference:= RawMaterialDatabase Range( Banco_MP ).AdvancedFilter _ Action:=xlFilterCopy, CriteriaRange:=Range _ ( RMfilter ), CopyToRange:=Range( RMoutput ), Unique:=True Application.Goto...

Excel: Frequency of Dates, cell reference, day of the year

cell reference, day of the year, histogram: Brandon, Do you mean, if you have 10 years of data, you want to summarize all 10 May 1sts, all 10 May 2nds, all 10 May 3rds.... etc? I think what I would do is make a NEW column and create a new date from the original date, a date that would...

Excel: Function to count number of days in months between two dates, data inputs, excel 2002

data inputs, excel 2002, categorised: So, 01/01/2009,12 01/02/2009,20 01/03/2009,22 01/04/2009,13 means that there are 12 days in Jan, 20 days in Feb, 22 days in March and finally 13 days in April starting at 1/15/2009 and ending at 4/18/2009. If do then these numbers represent the...

Excel: add an excel graph in e-mail with VBA, archive index, forum archive

Excel: add an excel graph in e-mail with VBA, archive index, forum archive, spreadsheet

Excel: Conditional formatting, answers to the questions

Excel: Conditional formatting, answers to the questions

Excel: Converting the date to number of days, leap year, a1

Excel: Converting the date to number of days, leap year, a1, datedif

Excel: To Copy ranges from multiple workbooks to one sheet

Excel: To Copy ranges from multiple workbooks to one sheet

Excel: Count unique data in unfiltered rows, c26, a26

Excel: Count unique data in unfiltered rows, c26, a26, c1

Excel: Currency symbols, exact functionality, mortgage calculator

Excel: Currency symbols, exact functionality, mortgage calculator, xls

Excel: combining date & time from two columns, time type, time column

Excel: combining date & time from two columns, time type, time column, column type

Excel: compare two lists in excel, e mail addresses, excel conditional formatting

Excel: compare two lists in excel, e mail addresses, excel conditional formatting, e mail address

Excel: convert text to .xyz, cheers

Excel: convert text to .xyz, cheers

Excel: copying cell color, colorindex, different color

Excel: copying cell color, colorindex, different color, cells

Excel: How to disable editing while multiple sheets are selected?, target

Excel: How to disable editing while multiple sheets are selected?, target

Excel: Excel 2003 - Autocomplete, blank cell, blank row

Excel: Excel 2003 - Autocomplete, blank cell, blank row, column data

Excel: Excel Formula - IF and/or OFFSET??, layouts

Excel: Excel Formula - IF and/or OFFSET??, layouts

Excel: Excel IF statement for dates, new question, sherry

Excel: Excel IF statement for dates, new question, sherry

Excel: Extend Range of Macro Calculation, unprotect cells, going in the right direction

Excel: Extend Range of Macro Calculation, unprotect cells, going in the right direction, target

Excel: editing data, a1, len

Excel: editing data, a1, len

Excel: Hide/show cells for specific users, white on white

Excel: Hide/show cells for specific users, white on white

Excel: index match to find all values matching the criteria in 1 column

Excel: index match to find all values matching the criteria in 1 column

Excel: Number format in concatenate, guru

Excel: Number format in concatenate, guru