valid numeric expression, random number generator, multiple choice questions: The rnd function returns a random number - from help Returns a Single containing a random number. Syntax Rnd[(number)] The optional number argument is a Single or any valid numeric expression. Return Values If number is Rnd generates Less...

Excel: if and question, question column, ogilvy

question column, ogilvy, c2: John, c2: =if(A2= OP ,if(B2= Mod , Good , Bad ),if(A2= IP , , Neither OP or IP )) you said A2 would have either OP or IP , if that is in fact true then I only need to check for OP and if not that, then appear blank c2: =if(A2= OP ,if(B2= Mod...

Excel: question, term spreadsheet, cell a1

term spreadsheet, cell a1, countif: Masha, everywhere you have R11, replace it with Trim(r11) and the leading space will be removed for purposed of evaluation in your formula. for example =RIGHT(Trim(R11),LEN(Trim(R11))-FIND( ,Trim(R11))) since right comes from the right side, you...

Excel: question, purpose formula, bush george

purpose formula, bush george, george w bush: Masha the formula to extract the last name did not work the last formula you suggested was:=RIGHT(TRIM(S11),LEN(TRIM(S11))-FIND( ,TRIM(S11))) Hold on. Don t drag my name through the mud. I used YOUR formula and showed you how you would apply...

Excel: question on excel, relevant cells, excel sheet

relevant cells, excel sheet, screen shots: sharad You have me more confused. It seems we are back to different workbooks instead of different worksheets. Also, if the cells are linked, what is the need for a macro or anything? If the cells are linked, when one is changed the other will change....

Excel: Random HTML can not delete, that same functionality, right click menu

that same functionality, right click menu, input boxes: Excel 2003 has that same functionality. Make sure you show the COntrol toolbox toolbar (right-click menu, select it from the list). Then you can click the first button to go into design mode. F5 will get you to the goto dialog, Special to go to the specials...

Excel: Re: How to Find, Copy, & Paste, confidential reasons, copy paste

confidential reasons, copy paste, number 3: Sub copyOver() Dim wksSource As Worksheet, wksDest As Worksheet Dim rngSourceRow As Range, rngDestRow As Range Dim rngNumbTimes As Range Set wksSource = ThisWorkbook.Sheets( data ) Set wksDest = ThisWorkbook.Sheets( destsheet ) ...

Excel: Reduction of Excel Workbook file size, excel workbook, performance changes

excel workbook, performance changes, backup copy: Sumanth Just because your file has 28 sheets doesn t imply that it has to be big at all. I would start (with a backup copy of course) by deleting sheets one at a time and seeing what impact that has on the file size. You may find that you have on sheet...

Excel: Removing bottom border, format cells, conventional options

format cells, conventional options, bottom border: Hello William, Thank you for your question and the opportunity to answer it. Unfortunately, I cannot see your worksheet, so providing an accurate answer is going to be difficult. Does the line only affect one row? If so, you might highlight that...

Excel: removing duplicates from excel sheet, cell b2, different fonts

cell b2, different fonts, cell a2: Judy Sorry I don t remember an answer to Renee and I couldn t find a question from her. So I am a bit confused regarding your question. I will try to give you somewhat of an answer. Assume you have a list of items in column A rows 1 thru 100, with...

Excel: Save an active worksheet in a workbook to another workbook, xml template, macro recorder

xml template, macro recorder, ogilvy: Jn if you just want to save it as a workbook ActiveSheet.copy this makes the copy a one sheet workbook Activeworkbook.SaveAs C:myfilder & activesheet.name & .xls , FileFormat:=xlNormal If you are using excel 2003 or earlier, xlNormal...

Excel: Search Macro Modification, search worksheet, whatfor

search worksheet, whatfor, inputbox: Hi Brian, Like this: Option Explicit Option Compare Text ignore case Sub SearchSheets() Dim FirstAddress As String, WhatFor As String Dim Cell As Range Dim oSh As Worksheet Dim bFound As Boolean StartAgain: Application.ScreenUpdating...

Excel: Searching text in cells for words, array formula, curly brackets

array formula, curly brackets, blank cell: Chris, Yes. In Excel 2003 and earlier, formulas could be 1024 characters in length. - departed to check help - Looks like that is still a limit (from help for Excel 2010) Length of formula contents 1,024 characters what you can do...

Excel: Sheets names, cell a1, support attachments

cell a1, support attachments, sheet names: bobert, very hard to give you any type of a comprehensive answer with such a question. I know nothing about how your sheet is laid out and don t really know what you want to summarize. How do I get the totals for the above hours from the diffent...

Excel: Simple Excel shop spreadsheet, counta, excel spreadsheet

counta, excel spreadsheet, working solution: You can use vba, but easier would be to use a formula that did it for them - if this is a sum all the lines below me sort of formula then =SUM(INDIRECT( a2:a &COUNTA(A:A))) as an example would give you a formula that added all rows from A2 to however...

Excel: Simple excel question, countif, time span

countif, time span, column c: What s the criteria for knowing if someone is entering or leaving? Also I d need to know the way the data is structured -- does column A consist of something like: 1600 1700 1800 etc or 0700-1600 0700-1700 0700-1800 etc or how else? Does column...

Excel: Simple excel spreadsheet, Sixthsense Excel

Sixthsense Excel: Hi Harry, What I would suggest is that the best method is you have to make it in Ms-Access. Because once you created a Database in Ms-Access then the user need not to change or extend the formula based on the data addition or deletion. Access will take...

Excel: Simple Stock inventory counting, vba programmer, excel vba

vba programmer, excel vba, keyboard entry: Brendan, right click on the sheet tab where you are doing this entry. Select View Code. Paste in this procedure: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, r1 As Range If Target.Count 1 Then Exit Sub If Target.Column...

Excel: Sorting content of columns based on check boxes, multiple choice questions, cell b5

multiple choice questions, cell b5, check boxes: Jenni You can approach this in a couple of ways 1-- you could use the data/filter/autofilter/ and select the type of questions you want to filter. 2-- you can use a formula that will bring over only the questions that meet the criteria for example the...

Excel: Speeding up vBA macro, vba macro, raw output

vba macro, raw output, output string: Well, I m glad my first advice did help. But to take this a real step further, your code needs a serious rewrite. What I would be doing if I were to redo this: - Remove all selecting of cells and sheets, this is almost never needed and takes a lot of time....

Excel: "If Statement" Macro Question, cell a1, efg

cell a1, efg, a10: Hi Al, Sub Demo() Dim oCell As Range For Each oCell In Range( A1:A10 ) If InStr(oCell.Value, ) 0 Then ThisWorkbook.SaveAs Replace(oCell.Value, , ) Exit Sub End If Next End Sub This...

Excel: SUMIF returns most frequent text string, array formula, position vacancy

array formula, position vacancy, staff type: Laureen, if you have excel 2007 or later, then this worked the same way as your formula (array entered as well): {=INDEX(rng,MATCH(MAX(COUNTIFS(rng1,G1,rng,rng)),COUNTIFS(rng1,G1,rng,rng),0))} G1 holds the type of staff your evaluating and rng1 is...

Excel: simple interest formula, simple interest formula, amortization schedule

simple interest formula, amortization schedule, excel spread sheet: Patsey, I am not an accountant or a financial advisor, so I wouldn t want to advise you on how loans work. You can download some free excel templates related to mortgages here http://office.microsoft.com/en-us/templates/results.aspx?qu=mortgage&origin=CT010152732...

Excel: Stopping Excel changing the source of code assigned to a button, control toolbox, open macro

control toolbox, open macro, button work: Colin, No I never said that. I said Range( B10 ).Select used in a sheet module would require that the sheet where the code is located be the active sheet when the command was executed or you would get an error. If you wanted to click a button (and...

Excel: Trying to .., sheet tabs, sheet names

sheet tabs, sheet names, sheet tab: Rachel 1) What do you mean by merge? Do you want to put all the worksheets in a single workbook? Or, is there some data in each document that you want to stack in a worksheet? How do you know what is to be consolidated? I am sure we can do what you want,...

Excel: Trying to find a conditional formula for copying/pasting above cell, ogilvy, offsets

ogilvy, offsets, d6: Amanda, this was tested and worked exactly as I understand you to describe in your test situation. Sub copytext() Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, E ).End(xlUp).Row For i = lastrow To 2 Step -1 If Len(Trim(Cells(i, ...

Excel: Trying to set a dates font to change 30 days later, relative address, home tab

relative address, home tab, ogilvy: Barrett, you mean you want to individually format 3 different cells or you want cell G5 to change if any of the cells listed are older than 90 days? I will assume you want to individually format 3 different cells. so for G5 =(today()-$G$5) =90...

Excel: time formulas, destination cell, time format

destination cell, time format, finish time: Hello Anna, Thank you for your question. If I understand you correctly, you want 10 in the cell if it is equal to or greater than 10, but you want the amount in A1 in the destination cell if A1 is less than 10. Try this formula: =IF(A1 =10, 10 ,A1)...

Excel: trigger macro on cell change, target value, target address

target value, target address, pivot table: Bill, worksheets( Sheet5 ).PivotTables( PivotTable1 ).RefreshTable Of course you will need to adjust the above to refer to the actual table. (change the worksheet name and the pivottable name to match yours). You can get the table name by going...

Excel: Use of VB to extract the selected value from autofilter, graph title, autofilter

graph title, autofilter, lct: Hi Justin, It isn t that hard: Sub Demo() Dim osh As Worksheet Dim oFi As Filter Dim lCt As Long Set osh = ActiveSheet For Each oFi In osh.AutoFilter.Filters lCt = lCt + 1 If oFi.On Then MsgBox...

Excel: Using VB code to extract selected value from autofilter, graph title, autofilter

graph title, autofilter, ogilvy: Justin, this would be the basic approach if only a single criteria is selected. Sub findFilterValue() Dim r As Range, r1 As Range, r2 As Range Dim i As Long, s As String With ActiveSheet If .AutoFilterMode Then Set r = .AutoFilter.Range...

Excel: userform with 2 combo boxes and a textbox, combo boxes, horizontal range

combo boxes, horizontal range, as3: As i understand it you have a rectangle of data so you need to use the results of both comboboxes to get the result you want. If B2 is the result for the first name and first week, then you can do Private Sub cboEmp_Click() if cboLW.ListIndex...

Excel: V lookup, vlookup, google

vlookup, google, customer code: Your question is a bit vague. Are you talking about putting in a lookup value and have a bunch of information appear in various cells all from the matching row of data that lookup value is from in a database? Like putting in an customer code and having...

Excel: #VALUE! errors with =IF, valid values, exclamation point

valid values, exclamation point, b2: Hello David, Thank you for your question. There is a problem with your formula. First, there are too many arguments for an IF Function. An IF Function should first test something, then execute the first thing ( Yes ) if true, or the other command (...

Excel: VBA 2003 loop trouble, home servers, databook

home servers, databook, nps: Hi JHow are you? I have just checked a few statements and the code seems fine. Please have a look at Sub SrcSurvVolstot2() Application.ScreenUpdating = False Dim strName As Variant Dim FindPatch As Variant Dim strDt As Variant Dim r As Range...

Excel: VBA code in excel 2007, target value, target address

target value, target address, data validation: Andy, Your code looked fine to me. Just to test: I pasted in your code in a sheet module and altered it to this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $F$9 Then If LCase(Target.Value) = item a Then MsgBox...

Excel: VBA code to locate last cell in column, letter designation, data cells

letter designation, data cells, up arrow: John, go to the bottom of your worksheet (all the way to the bottom). That is what Cells(rows.count, B ) does (for column B or the column you specify). row.count tells me the maximum number of rows in the spreadsheet. so Cells(row, col) is...

Excel: VBA: insert rows for missing dates, novice level, type mismatch

novice level, type mismatch, port column: Bob, that would be an indication that you have Text in one of the cells in the column being processed or you have an error value. (another possibility is that someone has cleared a cell using the spacebar - so it just looks blank). If you need me to...

Excel: VBA link to specific worksheet (part 2), information competence, salary basis

information competence, salary basis, outlook folders: Stuart, this is untested, but unless I missed something, this should do it. Sub ExportInbox() On Error GoTo ErrHandler Dim appExcel As Excel.Application Dim wkb As Excel.Workbook Dim wks As Excel.Worksheet Dim rng As Excel.Range Dim strSheet As...

Excel: VBA Macro to split data into batches & subtotal them, vba macro, numeric data

vba macro, numeric data, column c: Omar, Sub SplitData2() Dim rw As Long, cnt As Long, start As Long Dim lastrow As Long, i As Long Dim Total As Double rw = 2 cnt = 2 Do While Len(Trim(Cells(rw, J ))) 0 If Cells(rw, C ) Cells(rw - 1, C ) Or cnt 950 Then Rows(rw).Resize(3).EntireRow.Insert...

Excel: VBA OptionButton.Enabled = False, optionbutton, design mode

optionbutton, design mode, sydeny: Raza I could not figure out how to do this. I think the error is caused by the need to be in design mode to modify your option buttons. But, changing to design mode stops the code associated with the command button from working. I found this code to...

Excel: VBA Query, do in Loop, home servers, databook

home servers, databook, nps: j FindPatch = ActiveCell.Value Do Until ActiveCell.Value = Workbooks(strDt & strName).Activate Range( qref_1 ).Select Set r = Nothing Set r = Selection.Find(What:=FindPatch) If Not r Is Nothing Then r.Select...

Excel: VBA To Remove Data, conditional formatting, loop through

conditional formatting, loop through, ogilvy: James so you want to loop through all the cells in column B and if there is an entry in the same row in column D, then clear the value in column B Test this on a copy of your workbook or worksheet until you are satisfied it does what you want. Sub...

Excel: VBA Solution, cellc, invigilators

cellc, invigilators, column c: Dominica, Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim rw As Long Dim r As Range, r1 As Range, r2 As Range Dim cell As Range, cell2 As Range Set sh = ActiveSheet Worksheets.Add After:=Worksheets(Worksheets.Count) Set sh1 = ActiveSheet ...

Excel: Visual Basic Code for Excel, Sixthsense Excel

Sixthsense Excel: Hi Andrew, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim expertise in Excel VBA such as Bob Umlas,...

Excel: Visual Basic Code for Excel, e52, command button

e52, command button, copy paste: =AVERAGE( Residual Valuation:residual valuation (3) !E52) or =AVERAGE( Residual Valuation:residual valuation (12) !E52) etc - depends on how many there are. If this # is unknown, then create an end sheet which exists after the last one; it can be...

Excel: Visual Basic Code for Excel, module option, command button

module option, command button, osh: Hi Andrew, Try pasting this code in a normal module: Option Explicit Sub FindSomething() Dim oSh As Worksheet Dim oFound As Range Loop through all sheets in current workbook For Each oSh In Worksheets On Error Resume...

Excel: Vlookup across multiple worksheets/workbooks, optional range, vlookup

optional range, vlookup, udf: Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ Col_num As Integer, Optional Range_look As Boolean) Dim wSheet As Worksheet Dim vFound On Error Resume Next For Each wSheet In Workbooks(Tble_Array.Parent.Parent.Name).Sheets...

Excel: Vlookup an IF statement, valid combination, excel vlookup

valid combination, excel vlookup, vlookup function: Im not clear where the data comes from but it sounds as though its something like =if(and(vlookup(account,table,column,false)=Test1, vlookup(account,table,column,false)=test2), vlookup(account,table,column,false),n/a) replace my psuedo formula...

Excel: v grafe nastavit nezobrazovanie 0, Sixthsense Excel

Sixthsense Excel: Hi lubio21, Thanks for sending your question on my way, but I am unable to read your query since it is in Slovakian Language. I request you to send your query in English so that i can able to understand your problem and provide my solution. --- Sixthsens...

Excel: validation on a sum formula, cell b10, sum formula

cell b10, sum formula, target: Say cell B10 is a sum, and you want an error popup whenever its value is over 60. Set calculation to automatic. In the Visual Basic Editor, in the worksheet object, enter the procedure Private Sub Worksheet_Change(ByVal Target As Range) If Range(...

Excel: value plus percentage, richard florida, krystle

richard florida, krystle, rands: Krystle From your question I cannot tell if you want to add 12% of the value in A1 or 12% of some other number. Assuming it is 12% of A1 then the formula would be =A1*1.12 or =A1+(A1*12%). If it is an amoutn form another cell then substitute the other...

Excel: variable off set figure, databook, thanks in advance

databook, thanks in advance, offsets: j, OK. Glad you got it working. Everything isn t always clear right out of the box (in both directions). It is always better to inquire than to fire off a negative rating like chris (who got an excellent answer in my opinion). anyway, thanks for...

Excel: various a, array formula, sheet1

array formula, sheet1, cell c3: Mukesh on sheet2 assume the letter C appears in cell C3 You can find where the letter entered in C3 of sheet2 appears on sheet1 in column Awith a formula like this =if(Sheet1!A2:A40=Sheet2!C3,Row(A2:A40)) if you highlighted this command in the...

Excel: Widen Data Validation List Comprised of Merged Cells, data validation, column width

data validation, column width, h45: Yes, you can use various methods - in this case, it is only columns from H to AC that are being considered, which are columns 8 to 29 so Private Sub Worksheet_SelectionChange(ByVal Target As Range) if target.column 7 and target.column 30 then Application.ScreenUpdating...

Excel: Workbook, cell reference, sheet1

cell reference, sheet1, c13: Simone Did you copy the information into the new workbook or link it. Also what version of excel are you using? To create a formula from different sheets type in the = in the cell in the last sheet you want the formula to be in then click on the tab that...

Excel: want to display data from bottom to top, pratham, counta

pratham, counta, rakesh: Rakesh, assume A1: rakesh . . . A7: pratham then in B1 B1: =Offset($A$1,CountA(A:A)-row(),0) and drag fill down the column The formula is specific to the 7 entries starting in A1. You can adjust the approach to match your actual...

Excel: Zeros, binary representation, single quote

binary representation, single quote, string text: Xavier, when Excel stores a number and is allowed to determine how it is displayed, it does not display leading zeros. That is because it is not storing the characters 012 that you use in your example. It is storing the binary representation of 12....

Excel: 2003 to 2007?, fileformat, kind assistance

fileformat, kind assistance, dear tom: attis, I guess I didn t understand your intent. Then all you need to do is remove the shNew.copy command and the commands related to creating the .csv files Sub test() Dim sh As Worksheet Dim rng As Range Dim c As Range Dim List As...

Excel: Adding Additional Sheets to your macro, array storage, spath

array storage, spath, storage data: Yogi, Sub AddSheets() Dim shts As Object Dim shA As Worksheet, shB As Worksheet Dim sh As Worksheet, cnt As Long, v As Variant Dim bk As Workbook, sPath As String, sName As String Dim i As Long, j As Long sPath = J:MyfolderMyfiles v = Array(...

Excel: Adding Certain Cell entries and totaling them, cell entries, pivot table

cell entries, pivot table, digit numbers: Bill On way would be to use a pivot table if you want to use formulas, you can put your list of values in say M1: 1 M2: 2 M3: 3 Assume the original list is in column A N1: =countif(A:A,M1) then drag fill down if they really are single...

Excel: Ageing, excel macros, sorry david

excel macros, sorry david, attachement: Hello Meenakshi, Thank you for the screen shot. Unfortunately, I still am not able to determine what you are trying to accomplish. If I could see the formulas contained in the spreadsheet, I might could better understand what you are trying to accomplish....

Excel: Ageing, excel macros, true database

excel macros, true database, exact data: The pictured example makes no sense to me. What do each of those titles mean? 1st Inv Due on the 10th of the month? What are 1-days , 2-days A true data base does not put multiple entries, even for the same client, on a single row. A true database...

Excel: Arranging Hyperlinks in order, yuksel, better solution

yuksel, better solution, hyperlinks: Hello Abdullah, Thank you for your question. If you want to sort the hyperlinks in order by their actual name as you described, I believe you will need to have the actual name in the cell, not the number you presently have. Once the actual name in in...

Excel: Auto pop up of result, column c, ogilvy

column c, ogilvy, two rows: Trang in the situation you describe, you would need an IF statement - but I assume you want the cell to look blank until you enter the % value - this can be done with the IF statement say in C2 =if(And(isnumber(A2),isnumber(B2)),A2*B2, ) then...

Excel: array, array formula, silly number

array formula, silly number, b1000: sumproduct would work if you wanted to count or sum values, but otherwise you are right it needs an array formula =min(if(c1:c1000=10,b1:b1000,if(a1:a1000 =45,b1:b1000,9999999))) entered with Ctrl Shift Enter to make it an array formula - replace my...

Excel: automatically enter data entered in one cell into another., d22, e7

d22, e7, spreadsheet: george Can t you just link the cells? In BC22 you would have =BC7. What you put in BC7 will show up in BC22. If you need BC22 to be blank until you enter something in BC7, then you could use a formula like =if(BC7 .BC7, ) I hope this helps....

Excel: Bar Graph Axis Data, bar graph, spreadsheet

bar graph, spreadsheet, axis: I am not too good on charts. I know it is quite annoying when Excel guesses wrong. You may be able to simply delete the extraneous series, then right-click on a good series, pick Select Data and make edits to the series to get what you want. The best place...

Excel: barcode scanner find in excel, bar code scanners, barcode scanner

bar code scanners, barcode scanner, target address: Mary, I have no knowledge of your specific scanner, but most bar code scanners provide an interface that simulates typing the number into the active application - so I assume that is what you have. First, I hope you were asking a VBA question - otherwise...

Excel: Calculate Business Date, program syntax, federal holidays

program syntax, federal holidays, indicated number: The WORKDAY function is the one you are going to need - I ve copied the data from help. I d use something like =Workday(date(year(now()),month(now(),1),b2) to get the date WORKDAY Returns a number that represents a date that is the indicated...

Excel: Cell block, d3 d4 d5, c4 c5

d3 d4 d5, c4 c5, format cells: Select C:D, use Format cells & Unlock them on the Protection tab, then protect the worksheet with a password (I used XYZ here). Next, right-click the sheet tab, select View Code, then paste this in: Private Sub Worksheet_Change(ByVal Target As Range) ...

Excel: Changing a formula based on a cell value, tab sheet, year group

tab sheet, year group, student id: Daniel I assume you are the same Daniel who s quesiton I answered last night, you are welcome, but I see that you aren t using my VLOOKUP solution. To your current question-Although I am not sure, I believe you are referencing a change in a tab or...

Excel: Chart in Excel, bottom portion, s books

bottom portion, s books: Helen I don t understand what you are trying to do. I am not too good on charts anyway. This is where I go for anything to do with charting: http://peltiertech.com/ You may be able to get some help there. Jon literally wrote the book on Excel charting,...

Excel: Charting, horizontal axis, time intervals

horizontal axis, time intervals, reply thanks: Alana, I won t argue the point, but the time values were spaced at .05 intervals - they may have looked random as they don t come up as rounded time values (such as every 30 minutes. The .05 interval would be shown as every hour and 12 minutes - I checked...

Excel: Charting, category axis, time intervals

category axis, time intervals, excel user: Excel can handle date-based category axis, but not time-based, it seems. What I would do is ensure I have all the 1/2-hour increments in my column D, even tho there s no corresponding data. In the cells in E where there s blanks, I d enter =NA() to make the...

Excel: COUNT IF (DIV/O!), countif, question thanks

countif, question thanks, p3: zero divided by anything other than zero is fine as it returns zero - at least, it s fine as far as an error is concerned. To handle just that part =if(p3=0,1,COUNTIFS(Q$11:Q$88, n/a ,$C$11:$C$88, Pre-Close ,Q$11:Q$88, )/P3) Otherwise =if(or(p3=0,COUNTIFS(Q$11:Q$88,...

Excel: Combine two sets of data into one, dear tom, ogilvy

dear tom, ogilvy, n1: Michael for you example, I would put M1: A M2: B M3: C M4: D M5: E hopefully you can do that by copying the data from one of the sets (set #1 in your example) then assume in your example Set #1 is columns A:B and Set #2 is in columns C:D...

Excel: Combined Spreadsheets, cell reference, c13

cell reference, c13, new question: Simone The only way to keep the totals the same would be to change the cell reference in the formula to the actual figure. ie. you want to remove sheet 2 so you have to change the cell reference sheet2!C13 to 102 (or whatever the number is). Richar...

Excel: Combining multiple variables in a cell from one single "parent", cell c2, excel function

cell c2, excel function, list of names: I cannot think of a native excel function that would do this, but you could use a user defined function - press Alt F11 to get into VBA, then insert module and paste this Public Function combineName(rar As String, ChkRange As Range, OffVar As Long) As String...

Excel: Conditional Cell Value - look up tabe, cell d5, direct email

cell d5, direct email, cell values: your first method failed as you have referred to NECA as though it is a named range - you can fix this version by using insert,name to define NECA as the two columns of data on the NECA tab. The second one was closer, but you reffered to the twoColumnTable...

Excel: Conditional Formatting of Cells based on a Date Entered in Adjacent Cell, conditional formatting, header row

conditional formatting, header row, vba: Hello Anne, Thank you for the additional information and the screen shot. Let s try this. First, highlight the range of your data (A2 to H10 in this example. DO NOT highlight the first row with your headers.) Click on the Home tab, then on Conditional...

Excel: Conditional formating, b19, formating

b19, formating, b16: Andre I am a bit confused with what you are trying to do. I think you have three conditions If the first one is met you want the format of the cell to be red. If the second is met you want -------------------------------amber If the third is met you...

Excel: Conditional formatting, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: Go to Conditional Formatting Highlight Cells Rules Less Than. In the value box put: =TODAY()-180 That s the easiest way to get close enough to six months ago. If you need it EXACTLY six months ago, put: =DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))...

Excel: Conditional Statements, doe john, a2 test

doe john, a2 test, logical tests: Craig, this is what I understand you to want If Doe, John !A2 is true then you want to display an X and quit now if Doe, John !A2 does not equal true then check two conditions: Doe, John !A2 = false and B2 A1 if those...

Excel: Conditional statement for combobox, vlookup function, conditional statement

vlookup function, conditional statement, combobox: It SOUNDS like you simply need to use a vlookup function in the next cell to pick up the text =if(isblank(YourCell), ,Vlookup,YourCell,YourDataTable,2,false)) where YourCell is the cell with the data, YourDataTable is a 2 column table with the drop...

Excel: Constructing a certain formula, data cells, destination cell

data cells, destination cell, data conversion: Hello Peggy, Let s try again. First, without seeing your workbook, I do not know why it would not work in it, yet work in another workbook. You might check and make sure the calculation is set to automatic, and not to manual. That is a possibility. ...

Excel: Converting Excel to CSV, csv file, comma delimited

csv file, comma delimited, xls file: You need to understand what your trying to use, a CSV file means comma delimited values . When you save an XLS file as a CSV, it puts all the cell values for one row into a single text string separated by COMMAS. If you have commas IN your text strings,...

Excel: Converting Excel to CSV, xls file, richard florida

xls file, richard florida, csv: Abhishek I can t get the concatenate formula =concatenate(A2,B2)to produce 1,000,101.1 Where A2 is equal to 10001 I am missing the comma after the first 1. Having said that, I believe that what will work for you is to use copy/paste special/values...

Excel: Copy Cell.value, string constants, blank cells

string constants, blank cells, sh2: Mohammed I assume you don t have blank cells mixed in with your data in column L Sub ABC() Dim sh2 as Worksheet, sh1 as Worksheet Dim r2 as Range set sh2 = worksheets( Sheet2 ) set sh1 = worksheets( Sheet1 ) set r2 = sh2.Range( L2 ,sh2.Cells(sh2.row,...

Excel: Copy Data, blank cell, khaja

blank cell, khaja, f101: Khaja, Sub Macro1() Dim r as Range With Worksheets( Sheet1 ) set r = .cells(.rows.count, F ).End(xlup) if r.row 3 then set r = .cells(3, F ) else set r = r.offset(1,0) end if r.value = Copied End With End Sub -- Regards, Tom...

Excel: Copy word data in excel, word data, copy word

word data, copy word, ab1: Hi Hamid How are you? Please see the following code Sub SentenceToCells() Dim R As Range Set R = ActiveSheet.Range( E28 ) I = 1: j = 2 R = R + Do While I Len(R.Cells(1, 1)) t = Do While Mid(R.Cells(1, 1), I, 1) ...

Excel: Count if with two conditions, a5 e5, lotto statistics

a5 e5, lotto statistics, sumproduct: Francisco, in Excel 2007 and later you could use =Countifs(A5:E5, 10 ,A5:E5, =20 ) (countifs with an s was introduced in Excel 2007) in Excel 2003 and earlier it would be =Countif(A5:E5, 10 )-Countif(A5:E5, 20 ) or =sumproduct(--(A5:E5...

Excel: Countif with time criteria, countif, time criteria

countif, time criteria, time problem: Damian, =COUNTIFS(A:A, a ,B:B, = &TIME(6,0,0),B:B, = &TIME(22,0,0)) Note that I am using COUNTIFS with the S on the end tested and worked for me. Now that does 6 AM to 10 PM. If you mean 10PM to 6 AM, and you are only using times (not dates...

Excel: Creating an "overview" sheet, b1 cells, cell b1

b1 cells, cell b1, sheet1: If you re trying to SUM the cell B1 from Sheet1 through Sheet3, the formula would be: =SUM(Sheet1:Sheet3!B1) If you want this technique to work forever regardless of how many sheets you add, use this common trick: 1) Put your SUMMARY sheet as the...

Excel: Currency, cell b2, cell b1

cell b2, cell b1, info mail: Maria There is a formula that can tell you what format is being used in a cell. That formula is =CELL( format ,B1) If the cell B1 is formatted in US dollars then the formula will produce C2 . If the cell is formatted in Pounds then the formula...

Excel: More on charting, rob bovey, xy scatter

rob bovey, xy scatter, blank area: Alana, you would have to change your 2nd and 3rd series to be constant values such as 20 and 40 then you would have to designate the columns with the real values as labels associated with your dummy constant values. Rob Bovey has an XY chart labeller...

Excel: collecting data in arrays using if statements, axis values, yaxis

axis values, yaxis, a200: Jennifer, here is a guess values in B1:B200 with x markers in column A in the same row Dim xaxis(1 to 100) as double, yaxis(1 to 100) as double Dim xcnt as Long, ycnt as Long, cell as Range xcnt = 0 ycnt = 0 for each cell in Range( A1:A200 )...

Excel: command button deletion of selection, blank cells, empty cells

blank cells, empty cells, lct: Hello Tom, I believe the problem with the code to delete rows is my error which I believe will be eliminated by changing the loop to: For lCt = oRng.Rows.Count To 1 Step -1 With oRng.Rows(lCt).EntireRow count number of non-blank cells ...

Excel: comparing two columns returning multiple matching rows from third column, c 900, office microsoft

c 900, office microsoft, ogilvy: Jose, This is reasonably complex, so let me refer you to this site where the author has taken some care to layout the procedure with some illustrations and so forth: http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx...

Excel: comparing info on two lists, seperate files, column c

seperate files, column c, trying to find a way: It SEEMS like what you want, from your sample image, is to fill in column C with the routing date if it matches. If that s true, then this formula, entered in C2 and filled down, will do the job (Format column C as Dates): =IF(ISNA(MATCH(A2,D:D,0)), ,INDEX(E:E,MATCH(A2,D:D,0)))...

Excel: copying a formula, calculation mode, thanks john

calculation mode, thanks john, increment: John It seems like you want each cell in column B to be equal to the number above it times .075; unless your calculation mode is set to manual, the formula should return something different in each cell, unless B3 is zero, in which case they will all be...

Excel: copying formulas, sheet names, working solution

sheet names, working solution, number column: Presumptions -- Sheet Names are Sheet1 and Sheet2 . Now if you want to say Sheet1!C8 in Sheet2 Cell I4 to bring in total from Sheet1, you may use following formula - =INDIRECT( Sheet1!R & COLUMN()-1 & C & ROW()-1,FALSE) Now here, I do not know...

Excel: How to create an excel master workbook?, excel workbooks, excel workbook

excel workbooks, excel workbook, copy paste: You can easily pick up just the values without formatting by putting references into each sheet, like =[Sample.xlsm]Sheet1!A1 then fill right & down; and in sheet2, put =[OtherSample.xlsm]Sheet1!A1 and fill right & down - in both cases fill to where the last...

Excel: csv files, csv files, excel2002

csv files, excel2002, richard florida: Leif I really do not know if there is an automatic conveerter. I believe that the issue relates to the way a file has been saved and the version it was saved in. Sorryf I can t give you a good answer. I suggest you ask the other excel allexperts and...

Excel: cumulative cells, cell h4, stock sheet

cell h4, stock sheet, vague description: Hello Parveshen, Thank you for the additional information and the screen shot. By placing the formula =I4=H4 in I4, that will copy the value in H4 to I4. Then you will need to Copy and Paste Special -- Values on cell I4 to keep the value in cell...

Excel: 'curser' customization, sheet tab, a15

sheet tab, a15, interior color: As long as you re not permanently using color on other cells, this works: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.Color = xlNone Target.Interior.Color...

Excel: Data copy in a variable, cell a1, cell values

cell a1, cell values, sh2: Masood based on the change you made: Sub gatherdata() Dim sh1 As Worksheet, sh2 As Worksheet Dim cell As Range, s As String, z As String, final As String Dim r2 As Range, i As Long, sCol As Long, aCol As Long, mCol As Long Dim lastcol As Long Set...

Excel: Data Set, frequency graph, time gaps

frequency graph, time gaps, maximum density: It SOUNDS as though it s basically a multiple condition countif - you want to count all instances of 4.2m at 03:00 - you didn t specify version of excel -if 2007 onwards, then COUNTIFS would do it, otherwise =sumproduct(--(b2:b10000=3/24),--(c2:c10000=4.2))...

Excel: Data set, frequency graph, time gaps

frequency graph, time gaps, maximum density: Kim, there are many different approaches you could use to attack this problem. One is you could compute the average and standard deviation to determine what the distibution of depths looks like for a specific time of day. You could build a pivot table...

Excel: Derive high frequency character within same column, string s1, dimensional array

string s1, dimensional array, excel programming: CK ---------------------------- revision ------- OK - I got it to work with UNICODE!! See the picture. Can t say that is what you need however. ---------------------------- end of revision --- put the following code (both procedures) in a general...

Excel: Display a cell if another cell value is met, seperate cells, empty column

seperate cells, empty column, custom filter: This could get confusing. I would add a helper column to the data sheet. The helper column look at the SHEET2 criteria cell(s) and test the row to see if it matches the changeable criteria. If the row matches, the formula puts a 1 in the first row that...

Excel: Dynamic Date In Pivot Table, scratch sheet, pivot table

scratch sheet, pivot table, intermediate data: Sami, I can only give you a general answer since I have no knowledge of your data or your pivot tables. But there is no built in way I can think of that would dynamically determine what is shown in the pivot table. There are two approaches that I can...

Excel: display time greater than 24 hours in label, mm ss, lable

mm ss, lable, ogilvy: Xin, the format statement does not support all the formatting options a cell supports. Since your cell appears as you want your lable to appear, then instead of using format like this: lblActualHr.Caption = Format(.range( B1 ).Offset(cboEmp.ListIndex,...

Excel: displaying a value based on another cells value, student id number, vlookup

student id number, vlookup, google: I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you re searching. INDEX/MATCH has none of those restrictions. Here s some sample sheets showing what I think you re looking...

Excel: duplicates, spreadsheet column, fish bird

spreadsheet column, fish bird, loop error: Jeni the only way to have it automatically entered would be to run a macro that searches both columns for non matching items and then copies and pastes. A formula would need to be added each time to each row and changed or you will get a loop error. You...

Excel: Excel 2003 - Conditional Format on numeric in alpha column and alpha in numeric column, number column, text column

number column, text column, column c: Devi You can do that with Conditional formatting. Select columns A and B and then conditional formatting. Add a new rule; =isnumeric(a1) and then put in the format you want for numbers in the text column. Similarly, select column C and add the conditional...

Excel: Excel 2003 conditional formatting, colour shade, school holiday dates

colour shade, school holiday dates, school holidays: Ron I still don t understand how your data is arranged. You have a column of holidays, public and school? How do you know which is which? Is there another column that identifies which? If a holiday covers multiple days, are all the days listed in the column?...

Excel: Excel 2003 VBA, home servers, false application

home servers, false application, int name: j you need to remove these declarations: Dim tot_Name_Ranges As Variant Dim ann_Name_Ranges As Variant Dim int_Name_Ranges As Variant Dim hu_Name_Ranges As Variant Dim od_Name_Ranges As Variant you don t declare subroutines like they are variables....

Excel: Excel 2003 VBA Loop, microsoft sans serif, keyboard shortcut

microsoft sans serif, keyboard shortcut, strikethrough: Steve, Here is a possibility, but no guarantees: I assume you are always searching beyond where you are - so if you return to a row before or equal to the first invoice ID entry, then it should quit looping Test this on a copy of your data. this...

Excel: Excel 2007 Comparing Data, numeric text, alpha numeric

numeric text, alpha numeric, control numbers: Dave, if all control numbers are in column E in sheet 2, then you say if the control number from column A of sheet 1 matches a control number in Sheet 2, column E then check columns F and G of sheet 2. But you say check to determine if it matches. Check...

Excel: Excel 2007 IF or SUMPRODUCT 2, cell c2, percentage difference

cell c2, percentage difference, simon simon: Simon Just as simple would be to change the formula from = 2010-11 !C2- 2009-10 !C2 to =( 2010-11 !C2- 2009-10 !C2)/ 2009-10 !C2 but to avoid error,s you might want to expand it to =if(And(isnumber( 2010-11 !C2),isnumber( 2009-10 !C2),...

Excel: Excel 2010 Lists and Data Validation, data validation, excel template

data validation, excel template, drop down list: Hello Donald, Thank you for your question. Unfortunately, you will have to create your drop down lists again. There are a number of settings and such that do not migrate from one version to another, even on the same computer. This is one of them. ...

Excel: Excel, Sixthsense

Sixthsense: Hi Aman, It can be done only by using VBA and as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim expertise in Excel VBA such as Bob Umlas, Tom Oligvy,...

Excel: Excel, cell a1, target value

cell a1, target value, reply asap: Aman 32 in cell a1 3 in b1 now a1 must come to 29 put my code in a worksheet and followed you instructions. a1 ended up holding 29 (was 32) see the picture. If that isn t what you wanted, then it does exactly what you said. I wouldn...

Excel: Excel, validation basics, data validation

validation basics, data validation, last question: aman, it sounds like you want to pick from a list of products in the cells in column A. You don t need a macro to do this. You can use the list option under data validation. You would need a list of your products someplace and then apply data validation...

Excel: Excel, vlookup formula, question sir

vlookup formula, question sir, drop down list: Aman, Answer really hasn t changed. Here I propose a specific range for your table of products and rates in M1:N3 as shown below M1: apple N1: 50 M2: banana N2: 20 M3: orange N3: 25 then the formulas in B1 and D1: B1: =if(A1=...

Excel: Excel, international baccalaureate, answer mark

international baccalaureate, answer mark, assignment grades: Mark I don t get a #value error I get a #N/A . What happens in the scenario when they are all different and you don t have two or more of the same values? Can you reply to richard(dot)littlewing(at)gmail(dot)com as I will be on vacation from allexperts....

Excel: Excel, ogilvy, a1

ogilvy, a1, spreadsheet: David, A1: 3/25/2011 1:37:50 PM B1: 4/05/2010 2:30:00 AM C1: =A1-B1 gives me 354.463773148149 days. If I multiply that by 24, it will give me the total hours If I again multily by 60, it will give me the total minutes so change the formula...

Excel: Excel, negative questions, cell reference

negative questions, cell reference, excel spreadsheet: =6-SurveyResult (where SurveyResult is the cell reference) in a seperate column would be the easiest way of doing this - if you need to keep ONLY this data, copy the results of this formula, select the first original cell and use edit, paste special, values...

Excel: Excel, target value, target address

target value, target address, macro code: Aman, SIR THE ABOVE MACRO WORKS ONLY IN B1 AND D1 I WANT IT TO GET WORKED IN WHOLE COLUMN You say that like I screwed up. You never asked to have it work for the whole column you would need to tell me that specifically - you only asked for B1 and...

Excel: Excel Cell Colour Change, conditional formatting, cells

conditional formatting, cells, highlight: Yes, of course, accomplished through conditional formatting with 100s of possible tricks. Here s one way, assuming the CF formula is being applied to cells A2:A100. 1) Highlight A2:A100 It s important to note which cell you clicked first, as this is...

Excel: Excel CF color change, conditional formatting, cell a1

conditional formatting, cell a1, len: If you want to change the color of call A1 based on: 1) Len(B1) exactly equals 10 AND Len(C1) equals exactly 6, then the formula is: =AND(LEN(B1)=10,LEN(C1)=6) 2) Len(B1) exactly equals 10 OR Len(C1) equals exactly 6, then the formula is:...

Excel: Excel File Save As Macro in VB?, worksheet cells, character extension

worksheet cells, character extension, saveas: Gene There is a lot of stuff going on in your first sentence. What exactly do you want to happen and when? It is pretty easy to have a button that saves a file with a file name that is built from a combination of available information, hard coded information,...

Excel: Excel Formula, camp funston, ammo supply

camp funston, ammo supply, camp forsyth: Hello Dawn, Thank you for your follow-up. Use this formula instead: =VLOOKUP(M165,$S$1:$T$9,2,1) Your formula was only looking down to row 6, but you need it to search down to row 9. I tested the above formula on a spreadsheet and 7214 returned...

Excel: Excel Formula, chief executive director, director column

chief executive director, director column, excel formula: =SUMPRODUCT(--(Logs!A1:A3000= Chief Executive ),--(MONTH(Logs!e1:e3000)=4)) would give you the method - you need to have this as a defined range NOT the full column. You might also want to consider using a pivot table, though you would need to use an alternative...

Excel: Excel Formula, vlookup function, excel formula

vlookup function, excel formula, customer address: Hello Denise, Thank you for your question. Unfortunately, this site does not allow us to accept any personal email or attachments. I will point you in the right direction, however. It sounds like you need a VLOOKUP function to lookup the Customer ID...

Excel: Excel Formulas, format dialog, blank rows

format dialog, blank rows, excel spreadsheet: It s not blank -- it s 14 days after 1/1/1900, or 1/15/1900. To sort this at the bottom you re going to need either a helper column to sort on, or change the formula you have from =A1+14 to something like =IF(A1=0, ,A1+14) but then you wouldn t see a dash,...

Excel: Excel formual, graph line, line graph

graph line, line graph, grades children: Helen, I don t know who you asked on 3/29, but it wasn t me. If you want to send a sample file to twogilvy@msn.com with your data and an clear explanation of what you want in terms of a graph (bar graph, line graph, etc), I will try to help you out...

Excel: Excel formula, array formula, excel formula

array formula, excel formula, google: Your question appears simple until I look at your existing formula, it seems like it is using a TEXT function in the middle of that all. So your strings in P2:R2 aren t actual time values? I would need to see the actual data to figure accurately what you...

Excel: Excel, Inserting Checkbox (form control) linked to fields with VBA, blank text, vba macro

blank text, vba macro, n50: Allen, Make sure the text is right justified and the column width is bigger than normal to give room to see both. Sub AddCheckboxes() Dim shp As CheckBox Dim r As Range Dim r1 As Range, r2 As Range Set r1 = Range( N4:AA50 ) ...

Excel: Excel Macro, cell a2, cell a1

cell a2, cell a1, customer id: If you re using Excel 2003 or earlier, you must specify the range of cells to examine for this. Do not try to use full columns, only the range needed and maybe a little more. =SUMPRODUCT(--($A$1:$A$100=$H$1), --($B$1:$B$100=$B2), $C$1:$C$100) ...

Excel: Excel Menus, drop down menus, command button

drop down menus, command button, menu bars: Yes this can be done. However, it requires making changes to the user s menu bars and it is quite easy to mess up other people s Excel installation. Since you can t really guarantee what will happen on their computer, I tend to NOT do things like this,...

Excel: Excel VBA, to compare 2 columns, countif, zero value

countif, zero value, cells: I m guessing you will want to filter them, so I d use a formula - it s a simple countif =countif($a$2:$a$20000,i2) will check the value in I2 against all cells in column A and a non zero value indicates a duplicated value. Reverse the I and the A to...

Excel: Excel2003-VBA, smsg, apologies

smsg, apologies, cells: Hi Sri, This code seems to do teh trick: Sub TestForDuplicates() Dim oCell As Range Dim oDuplicateCells As Range Dim sMsg As String For Each oCell In Intersect(ActiveSheet.UsedRange, Range( B:J )).Cells If Application.WorksheetFunction.CountIf(Intersect(ActiveSheet.UsedRange,...

Excel: Export, Sort/Filter, csv file, filter function

csv file, filter function, sort function: Hello Mark, Thank you for your question. I hope I have understood your problem correctly. It sounds like you have several issues going on, but the most critical is to sort the entire data range by a single column. Unfortunately, I cannot see your...

Excel: ecxell, array formula, whole numbers

array formula, whole numbers, ascending order: Hello dharmasiri, Thank you for your follow-up. The formula I gave you works for me in a spreadsheet I created to test it. I don t know why it does not work for you. Remember it is an array formula. You MUST press Control + Shift+ Enter for it to...

Excel: email from excel, mail selection, range outlook

mail selection, range outlook, outlook application: Hi again Billy, I m not sure at this point why it isn t working for you. If you can, please email me your workbook so I can debug it. My e-mail is VBAexpert-AT-myway.com (replace -AT- with @). Also, please let me know which versions of Outlook and...

Excel: email excel sheet, de bruin, sh1

de bruin, sh1, bk1: billy with the sheet active that has the information you want to send. I am assuming you want to send the information as a workbook attachment Sub SendWorkbook() dim sh as worksheet, sh1 as worksheet Dim bk1 as workbook set sh = Activesheet workbooks.add...

Excel: about entering figures into cell, 3 zeros, 123456789987654321

3 zeros, 123456789987654321, numeric entry: having the cell formatted as a number is the correct way to go, but I m confused about zeros being added at the end - this isn t normal for excel. The only exceptions I m aware of would apply to any numeric entry - can you let me know which version of excel...

Excel: excel 2000, data validation, vlookup

data validation, vlookup, john doe: Kris, if by box you mean a cell and by popup you mean to have the cell display the phone number, then assume you have alist of names in column M and corresponding phone numbers in column N next to the name. (column M would probably be the source for your...

Excel: excel -, vba macros, student details

vba macros, student details, student names: Mark You can create a named range for the first sheet then place the named range in the second sheet, this will allow for removing and inserting new names (you will have to increase the named range if you insert more names). the Second question would...

Excel: excel, inexperienced user, b100

inexperienced user, b100, ogilvy: Bobbie, They should give the same results. The other person s (Bob Umlas?) is more efficient (it only sums the range once - mine sums the range twice if the sum is positive). I think mine is more intuitive although that might not be a consideration....

Excel: excel formula, Sixthsense Excel

Sixthsense Excel: Hi Arun Thareja, PT! - This refers to the Sheet Name . C7 - Cell reference * - Multiplication symbol equivalent to (X) -1 - For converting c7 cell value to plus value if C7 is minus value and for converting the c7 cell value as minus value...

Excel: excel question, ogilvy, b2

ogilvy, b2, date time: Mark, your picture just shows times and no dates. I will assume you have dates and times as you stated. assume A2 holds the date 04/08/11 13:40 when the file is open in windows In the mac it shows a data about 4 years later. insert a column...

Excel: extracting text from multiple excel files, text cells, exact layout

text cells, exact layout, sheet layout: nate, open the workbook where you will record the information. Make the sheet where you want the information the active sheet. do Alt+F11 go get to the visual basic editor. In the menu there choose Insert= Module paste in the below code in that module...

Excel: Filter and Sort Dates, format cells, simpler method

format cells, simpler method, ascending order: Hello Sara, Thank you for your follow-up question. Are all of the days two digits? For example, are the days 1 through 9 shown as 01, 02, etc.? If so, you can insert a helper column next to the column with the dates and convert the dates from 29apr2011...

Excel: Find command, aidan, heritage

aidan, heritage, texts: A little difficult to advise as it should work - could I see a sample worksheet with an indication of what you are looking for - I can check that in my copy of Excel and suggest POSSIBLE reasons at that point (hopefully!) My email is aidan.heritage@virgin.ne...

Excel: Find and replace with VBA, nike shoes, commerce store

nike shoes, commerce store, lower case: Peter I don t totally understand what you are trying to do. The following formula in column B will make the changes you described to the strings in column A =LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, , , ), , - ), . , )) I am not sure what you...

Excel: Finding the difference from two drop down lists, ogilvy, c1

ogilvy, c1, fraction: Lee, dates are stored as the elapsed number of days from a base date - likewise, time is the fraction of a day. So all you need to do is subtract the End time from the start time and multiply by 24. Format the cell as general as an example: A1:...

Excel: Finding text in cell, vlookup, b box

vlookup, b box, f12: Hi Kye, Hope the example below helps. F H 9 a apple 10 f fruit 11 b box 12 b =VLOOKUP(F12,$L$14:$M$18,2,0) (Formula to use) 13 e emu 14 h horse Data table L M 14 a apple 15 b box 16 e emu...

Excel: Format cell, richard florida, z1

richard florida, z1, florida usa: Joshua The formula refers to another cell other than that in which the formula resides. So if you want the data to appear in cell B1, then the formula would be in cell B1. Therefore when you input into cell A1 ABCDEFG what would appear in cell B1 would...

Excel: Formula to compare, then calculate on two worksheets, turnaround time, rudimentary knowledge

turnaround time, rudimentary knowledge, thanks for your patience: Jeane, If I copied and pasted you explanation of what you want then For the sake of clarity, A2 can be the cell of note and spreadsheets named April and May. If April A2 is larger than May A2, then subtract May A2 from April A2. If April A2 is smaller...

Excel: Charting

Excel: Charting

Excel: Problem with file, flash drive, correct answer

Excel: Problem with file, flash drive, correct answer

Excel: find number in a value, exact number, ogilvy

exact number, ogilvy, kole: Kole, If B2 contains the value to be checked (1234 in your example) =IF(ISNUMBER(SEARCH( 2 ,B2)), Contains a 2 , Doesn t contain a 2 ) that checks for the number 2 for the number 5 =IF(ISNUMBER(SEARCH( 5 ,B2)), Contains a 2 , Doesn t contain...

Excel: formulas, conditional formatting, decimal fraction

conditional formatting, decimal fraction, dollar symbol: Hi Lisa, YES it is possible. But here are cases- CASE 1 If you have $10000.00, Excel considers it as 5 digit. Because Excel considers it as 10000 DISPLAYED as 2 digit decimal with Dollar Symbol. CASE 2 If you have $10234.45, Excel will consider it...

Excel: Graphing percentage changes, percentage changes, pivot table

percentage changes, pivot table, percentage change: Jeanne, I was a little confused by this posting - but I went back and looked at my sent items and it appears I responded to the Allexperts notification of your question rather than responding to your email. I have just now forwarded that response to your...

Excel: More Help for inventory., scanner operator, cancel button

scanner operator, cancel button, inputbox: Brendan, I think you need to scan into an inputbox rather than into the cell directly. So this code puts up an inputbox. When you see that, do the scan. It will put up a second inputbox for the location. When you want to quit, just click the cancel...

Excel: HLOOKUP, hlookup, table a1

hlookup, table a1, excel 2002: Michele, Hlookup is fine. You just have to understand its capabilities. By making the 4th argument True, you are getting the behavior you describe. If you make it false, it then looks for an exact match. The only concern would be when it doesn t find...

Excel: Hyperlink and Compare, zeenet, blank cell

zeenet, blank cell, macro code: Ahmed, Sub Mylink() Dim r As Range, cell As Range Dim linkIt As String, Linkform As String Set r = Range( A3 , Range( A3 ).End(xlDown)) cntry = for lngg = hi lob = isp For Each cell In r if len(trim(cell)) = 0 then exit sub linkIt...

Excel: Ignoring blank lines, text numbers, question thanks

text numbers, question thanks, numeric data: Nancy Marsh, here is the way I would approach it. Just use a loop and some arrays to hold the special data needed for each instructor. Sub InstructorComments() Comments for all 4 Instructors Report Macro Dim r As Range Dim r1 As Range Dim...

Excel: Import to Excel with VB Script, spath, myfolder

spath, myfolder, sname: Steven, Sub Auto_Open() Dim sPath as string, sName as String Dim bk as Workbook sPath = c:myfolderMyfile must end with a sname = dir(spath & *.* ) if sname = then msgbox No file found in directory & sPath exit sub end...

Excel: Import Hyperlinked File List on open, file folders, hyperlinked

file folders, hyperlinked, documents and settings: Ky, I am neither on Mr Excel nor Excel Banter (which I have never heard of actually), so I am not sure how you have heard of me. In any event, I can certainly give you code to list all the files from a root directory all the way down. That code can be...

Excel: Index/ match formula, validations, j7

validations, j7, f7: I don t quite follow what s the criteria for returning Yes/no or True/False -- as you have it, you re returning the corresponding value in F3:F7 where AM2 matches J3:J7 (as you know). But where would yes/no come into play? Feel free to include with your explanation...

Excel: Index formula using cell value for sheet reference, cell a1, differnt name

cell a1, differnt name, function index: Sorry, mistyped the brackets - I ve done it in excel rather than trying to type it this time - try =INDEX(indirect(A1( cursor & !R$1:R$20 ),MATCH(1,(indirect(A1& !A$1:A20 )=$E13)*(indirect(A1& !L$1:L20 )=$R13),0)) BASICALLY it s your original formula...

Excel: Lost Personal Macro Workbook, t test, many things

t test, many things, clue: I don t use Excel 2007, so can t test anything regarding this. Excel 2007 specifically changed MANY things regarding macros. Your workbooks must be named Something.XLSM if you want macros to work. If you save a file as Something.XLSX then all macros...

Excel: checkboxes, oleobj, sheet names

oleobj, sheet names, c17: billy, Place 1, then you can copy it by holding down the control key and dragging down. That is probably the easiest manual way to do it (or copy and paste). But, if you are linking the checkboxes to cells, any manual method does not change the link -...

Excel: looking up data from one list, and pulling it into another, cell a1, f region

cell a1, f region, sumproduct: Sandy, Since you didn t give any cell locations, I will make some up. I assume both lists are on the same sheet (although this isn t a requirement) and that each combination of region and category identifies a unique row. list1 A2:C100 column...

Excel: lookup the value in column 3, cell b2, cell d2

cell b2, cell d2, cell a2: I created the formula by using your sample data to ensure that I gave you the right formula - it needs to be entered exactly as above (with ranges changed as necessary) - happy to look at a sample file if that helps - my email being aidan.heritage@virgin.ne...

Excel: lookup the value in column 3, cell b2, cell d2

cell b2, cell d2, cell a2: sahil I don t understand your question. I think you will eventually need the OFFSET function, with an embedded MATCH function, but, I need more information about what you are doing. Where are the numbers you are trying to match coming from, and where...

Excel: MACROS: saving 2 files in different locations + have only ONE of them protected, excel workbook, ogilvy

excel workbook, ogilvy, xls: Mary, If you are using xl2003 or earlier, do Alt+F11 to go into the Visual Basic Editor. in the project explorer, fine your workbook and under your workbook right click on the thisworkbook entry and select view code. Paste in code like this: Private...

Excel: Macro, blank cells, macro recorder

blank cells, macro recorder, b100: I m not sure what macro VBA in view means. The steps I gave above, you can turn on the macro recorder and let it record you doing those steps. Then you can reuse that macro anytime you want since it basically does the exact same thing each time...

Excel: Macro to Copy 2 Worksheets from masterworkbook to all the Workbooks in a folder, xlxm, spath

xlxm, spath, myfolder: Yogi, This assumes Master Inspection Form has an extension of xlxm and it is open in the same instance of excel as the workbook containing the macro (or the macro is in the Master Inspection Form workbook). Sub AddSheets() Dim shts As Object Dim...

Excel: Macro to show a number inside a box, cell interior, excel sheet

cell interior, excel sheet, rar: You have to make an actual reference to the object - personally, I would be inclined to have the box as a cell on the sheet - then it s just a formula. Otherwise, use the change event of the workbook to update things - happy to look at a sample file - you...

Excel: Macro to uncheck a row of checkboxes, check boxes, checkboxes

check boxes, checkboxes, cols: To clear cols B:K of the row of the active cell: Sub SAMPLE() Dim colsToClear As Range Set colsToClear = ActiveSheet.Range( B1:K1 ) Intersect(ActiveCell.EntireRow, colsToClear.EntireColumn).ClearContents End Sub I cannot answer the checkbox...

Excel: Macros - What if, macro scripts, blank cell

macro scripts, blank cell, absolute references: On Error Resume Next means that if you encounter an error, don t show that annoying Macro error. It s done here, because if range( Larry doesn t have any blanks, that s legitimate, not an error. 1):Office 2007+, to remove duplicates: Sub RemoveDups()...

Excel: Matching Sheets, types of fruits, amsf

types of fruits, amsf, cmsl: Your data is organized in such a way that it becomes quite difficult to do what you re asking. It CAN be done, but as it is, it s a pretty big request to put to AllExperts.Com, where it s usually a Q&A, not can you write this involved macro for me. So,...

Excel: Monthly Tabulation, question pool, report cards

question pool, report cards, tabulation: I find the pictures on here quite hard to read (probably my advancing years (!!) ) but am happy to try to help - at face value, it sounds as though it s a case of summing IF the month is the required month, but I cannot see the layout properly - is it possible...

Excel: Multiple What If Formula, value error, 599cd

value error, 599cd, cordially: Your syntax is wrong. IF looks like this: =IF(X=Y,T,F) If you want to nest, it looks like this: =IF(X=Y,T,IF(X=Y,T,F)) You re not nesting your functions right. Your second IF is actually OUTSIDE of the first one. Clean it up. Mind your parentheses....

Excel: making windows exel 2010 work for me, spread sheet, web server

spread sheet, web server, images: I m not sure what you are asking here - Excel 2010 can publish to the web, but it only publishes it s own content - you mention images etc which SOUNDS as though you are trying to build a full website - which would need FTP access to the web server. If you...

Excel: moving charts, vertical scrollbar, horizontal scrollbar

vertical scrollbar, horizontal scrollbar, command button: Hi Eric, I tried your code in Excel 2007 with a chart embedded in a worksheet and did not experience the trouble you describe. The chart did not move or change size. If you would like, you can email me your workbook to I can see if I experience it with...

Excel: multiplying within a cell, cell d2, multiplication factor

cell d2, multiplication factor, number 0: The simplest method is to use separate cells. This allows it to occur using just formulas. 1) Pick a cell to hold your multiplication factor. Let s say you selected Z1. In Z1 put the number 0.1238 2) Then lets say you want to enter values in column...

Excel: Need help with a formula to create a time sheet, time and a half, time sheet

time and a half, time sheet, daily basis: Hello Ian, Thank you for your follow-up question. I am glad the previous advice helped you. Assuming the total hours are in A1 and the hourly rate of pay is in B1, then use the following formula. If your number of hours are equal to or less than 40,...

Excel: Need help to get a specific data in a date range, sheet1, autofilter

sheet1, autofilter, column headers: Sub copyTodaysRowsOnly() assume Excel 2007; that we re xferring data from Sheet1 to Sheet2; and that the Sheet1 data starts with column headers name in A1, email in B1, date in C1 Dim wksFrom As Worksheet, wksTo As Worksheet Dim mainTbl...

Excel: NUMERIC VALUE CONVERT INTO TEXT FORMAT, third party solutions, microsoft

third party solutions, microsoft: Hi Deepak, Unfortunately, Microsoft provides no built-in function to do this in Excel. But there are lots of third-party solutions available, some for free. You can find a free one at this address: http://www.meadinkent.co.uk/xlnumberstext.htm ...

Excel: need clarification on references, inturn, global search

inturn, global search, baa: creating a new name doesn t change the name of the old name, it simply gives you a duplicated named range. I d simply do a global search and replace. I would ACTUALLY re-write your formula to use a lookup table by creating all possible values in the table...

Excel: need help with IF statement, r24, sheet tab

r24, sheet tab, e24: Right-click the sheet tab for CV001 and put in this code: Private Sub Worksheet_Calculate() If Me.Range( R24 ).Value Then Me.Range( B24:E24 ).Copy Sheets( WO_CV001 ).Range( B24 ) Me.Range( R24 ).Copy Sheets( WO_CV001 ).Range(...

Excel: need help with IF statement, r24, e24

r24, e24, copy paste: Formulas cannot copy/paste, formulas can only do calculations and/or display references to other cells. On the sheet WO_CV001 you could put this formula in B24: =IF(CV001!$R24= , , CV001!B24) Now copy that cell across C24:E24 and into R24 as...

Excel: Odds & Evens, digit number, free tutorials

digit number, free tutorials, 599cd: Sure. You could use =MID(A1,9,1) to determine what the 9th character is. Then it s just a matter of math. Add up all of the males (COUNTIF) and divide by the total number of items (COUNT). There are free tutorials on my web site that can help you with some...

Excel: optimizing named ranges, j35, excel application

j35, excel application, string format: You possibly don t need an algorithm - simply use this syntax Range( Range_A , Range_B ).ClearContents to clear multiple ranges - in my case, just two, but lots could work - I m not clear what the test about clearing or not clearing is - if this is...

Excel: Passing parameters to a Macro in Excel, macro in excel, input parameters

macro in excel, input parameters, seperate line: Yes, this is easy enough dim VarA,VarB,VarC,VarD Open c: empoctopusreports.txt For Input As #1 Input #1, VarA Input #1, VarB Input #1, VarC Input #1, VarD close #1 would read data from the named file (mine came from an actual example!) and stores...

Excel: Paste Link Excel 2010, run time error, run time error 1004

run time error, run time error 1004, paste link: Ian, I don t have excel 2010 here, but I have access to it at home. If you want to send the file I am sure I can find a workaround if there is a problem. Make sure you two versions of the code are in the workbook as well and tell me a good range (from...

Excel: Pop up warning box, cell reference, popup menu

cell reference, popup menu, thanks in advance: Richard, If you want a box to pop up, then you would need a macro to do that. make your workbook as a window (rather than full screen). Then right click on the caption border at the top (has the name of the workbook in it). In the resulting popup...

Excel: Possible to randomize results?, randomization, google

randomization, google, ccc: Assuming you have data in columns A:F, when you say randomize the columns do you mean: 1)randomize just the values in column A, then separately randomize the data in column B, then C, etc.? 2) randomize the order of the data but keep each row of values...

Excel: Printing charts in office 2007, printing charts, hi bob

printing charts, hi bob, print preview: 1 - I m not aware of this issue 2 - Excel 2010 has fixed MANY issues with charts, so upgrading is probably better 3 - if you want me to look at the issue with your file, send it to me at bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what...

Excel: Problem with file, collection inventory, flashdrive

collection inventory, flashdrive, flash drive: This question is a little confusing flash drives are only available when plugged in - but you mention hard disk - if the file is Saved AS when opened from the flash drive and it is saved TO the hard drive,then the up to date copy is on the hard drive. ...

Excel: Problem with file, collection inventory, flash drive

collection inventory, flash drive, harddrive: Wynneth What are you clicking on to try to open the file? A desktop icon? The list of recent files? It seems you are probably not clicking on the file itself since that would make it unlikely to get the cannot find error. Or, is it a link to another file...

Excel: Problem with file, collection inventory, flash drive

collection inventory, flash drive, harddrive: Just a guess, since you should be able to drag it from the flash drive to the hard drive, but go to office button/excel options/advanced, and scroll down to near the bottom and Deselect the checkbox Ignore Other applications that use Dynamic Data Exchange...

Excel: Profit Margin Formula, profit margin formula, spredsheet

profit margin formula, spredsheet, profit margins: It can be done - I think it s more a lesson in maths (for me rather than you!) rather than excel. I THINK that the formula is =M5/(1-V5) But I m not a retailer and MAY have misunderstood how to work out the percentage profit margin - hopefully it s...

Excel: printing landscape & portrait in one worksheet, printout sheets, landscape portrait

printout sheets, landscape portrait, sheet tab: If it s for someone else, it s be easier to split it into 3 sheets, the 2nd being landscape, and have a macro handle the print (it d take over when you/they press print). Assuming Excel 2007 or 2010, it d have to be saved as either an xls (2003 format) or...

Excel: protected cells get unprotected after copy, vilas, desai

vilas, desai, cells: Vilas I think you will find that the locked cells that you copied are still locked on the destination sheet, but, the destination sheet is not protected. It should be pretty easy to protect the destination sheet after you paste your data. ActiveSheet.Protect...

Excel: Quarterly count of all active cases, column c, office excel

column c, office excel, quarterly report: Dave, I assume you still want to restrict the count to the specified quarter: =SUMPRODUCT(--($A$1:$A$200 DATEVALUE( 3/31/2011 )),--ISNUMBER($A$1:$A$200),(LEN(TRIM($B$1:$B$200))=0)+($B$1:$B$200 =DATEVALUE( 1/1/2011 )),--($C$1:$C$200= successful )) ...

Excel: question on excel, vlookup, google

vlookup, google, data tables: I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the data tables you re searching. INDEX/MATCH has none of those restrictions. Here s some sample sheets showing what I think you...

Excel: Regrouping Worksheets, xls, simple answer

xls, simple answer, team rosters: Ana, if the folder is not open, then that would be an indication that the folder is marked as read only on if the personal.xls file already exists, it is marked as read only. You would need to open windows explorer and go to the Excel start folder...

Excel: Report, rare occurance, csv file

rare occurance, csv file, time thing: Richard This is a little old now, as I have been pretty tied up the last week. You may have already got it worked out. Without any macro experience, I think the best way to proceed at first is to: 1. Import your data 2. Insert a temporary row anywhere...

Excel: Require a formula to to me when a certain date is reached on a spread sheet., conditional formatting, red cells

conditional formatting, red cells, security base: Select L3:L143 (the cells to the right of the dates). Home tab, Conditional Formatting, New Rule. Click Use a formula to determine which cells to format Enter =K3+31 =TODAY() Click Format Select the Fill tab Select the color you want Click OK twice...

Excel: Row numbers, row numbers, number 1

row numbers, number 1: Hello Stu, Thank you for your question. I hope I am understanding you correctly. Are you wanting the top row of Excel to be row 2 and not row 1? If so, that is not possible. The row identifications are hard-coded into the software and cannot be changed....

Excel: referencing data in another workbook, cell a1, correct steps

cell a1, correct steps, xls: Unfortunately, that is still the correct steps. Either you re not doing what your said you re doing, or something is amiss in your setups. The resulting formula (when both workbooks are open) should look something like: = [!Workbook2.xls]Sheet1 !$A$1...

Excel: return value of the index of column, cell b2, cell b1

cell b2, cell b1, column headings: SAHIL I noted that you say cell B2 will contain A and you also say Cell B2 will contain the ranbetween formula, I m assuming that is not exactly correct and row 1 will be the row with the column headings A,B,C,D, etc co cell B1 will contain the column...

Excel: return value of the index of column, cell b2, o21

cell b2, o21, blank cell: Sahil, As you can see from the attached picture, the formula I gave you works exactly as I described. That appears to not be what you want. However, I have used your random number formulas and the picture also highlights the futility of your current request....

Excel: return value of the index of column, cell b2, o21

cell b2, o21, a15: What do you mean, to get the corresponding values ? I didn t know wht you specifically asked about cell A15 -- did you want this to apply to ALL the cells from A2:A21? If so, then the formula should anchor the references; =INDEX($1:$1,MIN(IF($B$2:$P$21=$A15,COLUMN($B$2:$P$2),...

Excel: return value of the index of column, cell b2, o21

cell b2, o21, a15: sahil I really don t understand what you are trying to do; but, it seems like you will probably need to use the OFFSET function in some way or another. It appears you want the cell that is offset from column A (I can t tell what row in column A though) by...

Excel: return value of the index of column, cell b2, array formula

cell b2, array formula, o21: I think I ve followed this - I assumed that the column names are ACTUALLY in row 1 as you have mentioned row 2 twice in the data above. =OFFSET(A1,0,MIN(IF($A$15=$B$2:$O$21,COLUMN($B$2:$O$21),99))-1) entered as an array formula (ctrl shift enter) SHOULD...

Excel: returning values in adjacent cells, array formula, cell values

array formula, cell values, a39: Ron, in your formula you first check whether the Index function is successful by including it inside and iserror statement. If it returns an error, you show a null string ( ). If it doesn t return an error you then repeat the Index function. That is...

Excel: Select characters to the left of VBA, string s1, iloc

string s1, iloc, allexperts: J, http://www.allexperts.com/central/awards/topcat_index.htm shows that I have not received it recently. I guess too many people need tax help or need help fixing their Chrysler. g but thanks for the thoughts. as far as fast, the Chrysler...

Excel: Sheet in a sheet, column headings, window names

column headings, window names, gridlines: View tab, click New Window twice. then click Arrange All, and check Windows of active workbook . You can either let it default to Tiled or you can choose Vertical (or other options) When you re done viewing this way, before you close the WORKBOOK, you...

Excel: Spin button decimal increments, spinner control, whole numbers

spinner control, whole numbers, decimal points: The spinner control will only do whole numbers - I would suggest that you link this to a cell that isn t used in the formula, and probably set to invisible by using white text on white background(hiding behind the control would be a good place) and then having...

Excel: SUM if the particular condition is true, long time, new formula

long time, new formula: Nadeem I would use a SUMPRODUCT formula. It seems you actually have two conditions. Excel 2007/2010 has a new formula SUMIFS that you could use, but I prefer SUMPRODUCT which has been around for a long time. I think you are summing the data in column...

Excel: Sum Total Of Different sheets in a workbook, cell a1, richard florida

cell a1, richard florida, sum total: Amonia You can use a formula like this-- This assumes that you have already created sums in each sheet and you want to total those sums in sheet1 The formula in sheet1 might look like this. =Sheet2!A100+Sheet3!A100+Sheet4!A100+Sheet5!A100 etc,etc,etc...

Excel: Sum of values in a range selecting interior color green only, colorindex, cell interior

colorindex, cell interior, color index: Barry S Yes a macro could do that - but best I can do is give you a macro with some assumptions and you would have to put in the specifics. Sub SumGreen() Dim r As Range, tot As Double, cell As Range Dim lGreen As Long Set r = Range( A1:A200 ) lGreen...

Excel: Summary Worksheet, h8, vbe

h8, vbe, cells: Press Alt/F11 (gets you to the VBE), then use Insert/Module, then paste this in: Function GetData(SheetNo As Integer, CellRef As Range) GetData = Sheets(SheetNo).Range(CellRef.Address) End Function Then in the new worksheet (probably should be the...

Excel: search for a value then display the value x cells to the right of it., x cells, student id number

x cells, student id number, move x: Daniel Yes you can do what you want. First ---do your first lookup, Then reference that result in your second lookup. (I am assuming that you do not know the student ID number but are looking it up by name or some other reference) The formula...

Excel: set week number using vba, column label, trunc

column label, trunc, pivot table: xin, you don t need vba code. just take you date and divide by 7. That will give you the number of weeks since the beginning of the 19th century Say in E2 you have you date for Feb 20, 2011. That date is stored in Excel as 40594 which is the number...

Excel: speech macro related query in excel 2003, run time error, jagdish sharma

run time error, jagdish sharma, vba: 1) Does your speech macro read text in cells? If so, you will run into this error when the text in the cells being read is longer than 900 characters. Reduce the length of strings in all cells to less than 900 characters. 2) All Excel file types are macro...

Excel: A stickler, array formula, array formulas

array formula, array formulas, sumproduct: Taylor, in D1 put in the formula =IF(ROW() =COUNT($A:$A),INDEX($A:$A,SMALL(IF(ISNUMBER($A$1:$A$100),ROW($A$1:$A$100)),ROW()),1), ) Enter it with Ctrl+Shift+Enter since it is an array formula. then drag fill down as far as you think you will...

Excel: Tab names in Excel, sheet names, cell location

sheet names, cell location, c6: Hi Bill, Okay, here s an example that I believe you will find helpful. Suppose that you enter 2011 in C6 of the Summary sheet and you want the outyear sheets to be automatically named FY 2012 , FY 2013 , FY 2014 , etc. In each of the outyear sheets...

Excel: Thanks! One more question!, column labels, cable list

column labels, cable list, new question: TJ, basically you would have to countif against each sheet/book combination =if(sum(countif([Book2.xls]Sheet1!A:A,A2),countif([Book2.xls]Sheet2!A:A,A2),countif([Book2.xls]Sheet3!A:A,A2),countif([Book3.xls]Sheet1!A:A,A2),countif([Book3.xls]Sheet2!A:A,A2),countif([Book3.xls]Sheet3!A:A,A2))...

Excel: Triggering macros with DDE linked cells., instr function, dde links

instr function, dde links, ubound: Sergio, I assume you are using this macro: Sub Test() Dim wbLinks As Variant Dim i As Integer wbLinks = ThisWorkbook.LinkSources(xlOLELinks) If Not IsEmpty(wbLinks) Then For i = 1 To UBound(wbLinks) ThisWorkbook.SetLinkOnData...

Excel: timesheet, cell b2, format cells

cell b2, format cells, column labels: Hi samjith, I m not sure I know exactly what you want. If you want to be able to simply type the name of the Month in a cell on the sheet and have the column labels update automatically to represent the days of that month, this is easy. For example, let...

Excel: Update another sheet from a summary sheet, iteration count, circular reference

iteration count, circular reference, iterations: Turn iterations on & to 1. In cell A1, enter =A1+1 (obvious circular reference). Now press F9. Each time you press F9, ONE calculation iteration will happen. Now go back & turn iterations to 99 and press F9. You will see it increment to 99 more than before,...

Excel: User Defined Function, payment mode, case structure

payment mode, case structure, case mode: clarkson The formula works fine for me. You are not going to be able to just type in M without the parentheses unless is is a range name. The formula as you have it, works fine when I have it in column C, and in column A I have m and in column B I have...

Excel: Variable 2010 sort code, macro in excel, code question

macro in excel, code question, sort code: Alan, Range( A2 ,cells(rows.count, A ).End(xlup)).Resize(,16).Select ActiveWorkbook.Worksheets( Leader ).Sort.SortFields.Clear ActiveWorkbook.Worksheets( Leader ).Sort.SortFields.Add Key:=Range( O3:O42 ) _ , SortOn:=xlSortOnValues, Order:=xlAscending,...

Excel: Variable Offset (VBA), home servers, mybook

home servers, mybook, databook: J, if you have 7 alternatives and you need to assign unique values based on the alternative being worked with, then a case statement is certainly appropriate. You can move it down to the subroutine if you want. Otherways would be if then else constructs,...

Excel: VB Macro to delete a character in a cell, then move on, leftmost column, beggining

leftmost column, beggining, s01: Adam Sub removeS() Dim cell As Range, r As Range, r1 As Range Dim lastrow As Long, col As Long Set r1 = ActiveSheet.UsedRange Set r1 = r1(r1.Count) lastrow = r1.Row col = Selection(1).Column For Each cell In Selection If cell.Row lastrow Then...

Excel: VBA 2003, run time error, silly mistake

run time error, silly mistake, home servers: j I can t really debug a runtime error looking at static code. I don t see anything specifically wrong. I think you are on flaky ground if you just open an workbook and start using ActiveCell how do you know what the activecell is when the workbook...

Excel: VBA 2003, home servers, databook

home servers, databook, thanks in advance: I m happy to look at the actual workbook (aidan.heritage@virgin.net) BUT I suspect the problem is easily resolved - you have passed the range as a variable called Q, but you are are selecting the range CALLED Q as you enclosed it in quotation marks Range(...

Excel: VBA : Above and Beyond Sending Excel ranges to Outlook Mail, mail question, outlook mail

mail question, outlook mail, temp folders: Hi Mark, I m truly sorry you have had this problem during my long vacation. I believe the problem will be solved by loading the appropriate object references for Word. To do this, in the Visual Basic Editor go to the Tools References menu and check the...

Excel: VBA? Or advanced formula to search for name, and copy certain cells, current sheet, g2

current sheet, g2, xls: You can can use VLOOKUP:. =VLOOKUP(A2, [Master.xls]Sheet1 !$A:$Z, 2, 0) This will lookup the value of A2 on the current sheet in the other workbook and sheetname as demonstrated, looking for the match in Column A and then returning the value from...

Excel: VBA Code, killme, doevents

killme, doevents, excel sheet: Usually, workbooks can t delete themselves. But after some digging, I came up with this. In the Workbook_Open event, put this: Private Sub Workbook_Open() If Date #4/20/2011# Then KillMe End Sub in a regular module, put this: Sub KillMe() ...

Excel: VBA Code, sheet1, code question

sheet1, code question, sh1: Abdul Jaleel that was a typo. I misspelled address. and an omission - printarea is an attribute of pagesetup. so it should have been sh1.PageSetup.PrintArea = r1.Address(1,1,xlA1,True) But if you already have you PrintArea defined to the correct...

Excel: VBA Feet & Inch Conversion & Calculation, cell a2, inch conversion

cell a2, inch conversion, cell a1: Mark, Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Set r = Range( Convert ) If target.count 1 then exit sub If Not Intersect(Target, Range( e:e )) Is Nothing Then Target.Offset(0, 8) = Date & & Time End If If Not...

Excel: VBA Macro for Excell 2003, column letter, blank row

column letter, blank row, up arrow: Maria, assume the last row can be determined by checking column A Sub AddaBlankRow() With ActiveSheet .Cells(.Rows.Count, A ).End(xlUp).EntireRow.Insert End With End Sub change the A to another column letter if you can t use column A to determine...

Excel: VBA macro with if formula, vba macro, e300

vba macro, e300, well thanks: Inka This will do what you asked. Sub Macro4() Range( E2:E300 ).Select Selection.FormulaR1C1 = =IF(RC[-1] 0,RC[-1]+RC[-2],RC[-1]-RC[-2]) End Sub Unless it is being embedded in a more comprehensive macro, I am not sure what good it will...

Excel: VBA Questions, scripting runtime, wkb

scripting runtime, wkb, getfolder: Sub copySheets() From within any module in the Visual Basic Editor, choose from the main menu Tools, References and make sure that Microsoft Scripting Runtime is checked. Then run this sub to copy all sheets from all files in folder C:TEMP into...

Excel: VBA Solution - Copy Specific Sheet from all workbooks ina folder, paste values, folder path

paste values, folder path, sname: Yogi, assume the master workbook is the workbook that contains the code Sub ABC() Dim ans As String, sname As String Dim bk As Workbook, sh As Worksheet ans = InputBox( Enter Path ) If Len(Trim(ans)) = 0 Then Exit Sub If Right(ans, 1) ...

Excel: VBA validate in between time, msgbox, random time

msgbox, random time, vertical line: Damian, You have divided a day into 4 pieces ----|----|----|---- so the first vertical line is 6 AM. The second vertical line is 2 PM and the third vertical line is 10 PM. you can see that the inner intervals are bounded by vertical lines. ...

Excel: VLookup, vlookup, cctf

vlookup, cctf, question pool: Harry, you didn t ask me this question - I found it in the question pool. if you only have one row to look at, then why use Vlookup Dim abc as Variant if Sheets( Sheet1 ).Range( B11 ).Value = Sheets( w_CCTF_Total ).Range( B2 ) then abc = sheets(...

Excel: Vlookup, vlookup formula, cell a1

vlookup formula, cell a1, dropdown menu: It d be easier to help if I had a sample workbook -- please send such a sample to me at bobumlas@hotmail.com, use subject of AllExpertsQ and repeat the issue. Offhand, you may be able to get something like what you want by just filtering the range on...

Excel: Vlookup, vlookup function in excel, rubin rubin

vlookup function in excel, rubin rubin, mathur: Rubin, this is rather involved - but since Ashish Mathur has documented how to do it, let me refer you to his page on this topic http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx...

Excel: vba merging values in cells, 12abc, sheet tab

12abc, sheet tab, target: Dave, why not just use a formula say in C1 =if(And(len(trim(A1)) 0,len(trim(B1)) 0),A1&B1, ) then drag fill down. if you want a macro, right click on the sheet tab and select view code then paste in code like this. Private Sub Worksheet_Change(ByVal...

Excel: Widen Data Validation List Comprised of Merged Cells, pipeline design, screen flicker

pipeline design, screen flicker, data validation: It s a question of limiting what happens, so maybe something like if target.column 7 and target.column 30 and target.row=41 do your thing end if and then a reset test If target.Address $AC$41:$AI$41 and Columns( AC ).ColumnWidth 2 then...

Excel: Workbook object, type mismatch, destination files

type mismatch, destination files, astring: Terry, addendum: Terry, if you think the syntax you posted was correct - then I did a poor job of explaining why you are having a problem. If so, I apologize. I can recommend Bob Umlas on this site who can probably give you a clearer explanation. Again,...

Excel: work scedule in excel, scedule, time cell

scedule, time cell, lunchtime: Don t know what your definition is for lunchtime, so you can subtract 1 from this formula to remove lunch. If the cell in question is A1: =IF(24*IF(ISERROR(FIND( : ,LEFT(A1,FIND( - ,A1)-1))),LEFT(A1,FIND( - ,A1)-1)/24,LEFT(A1,FIND( - ,A1)-1)) 24*IF(ISERROR(FIND(...

Excel: How to write code for multi level commission payout, level referral, spreadsheet program

level referral, spreadsheet program, excel spreadsheet: This is easiest to explain with a sample workbook showing your design, your references, and where you are trying to do these calculations. Please use the email address found at the top of this page: https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/files...

Excel: How to write an Excel function:, excel function, winp

excel function, winp, blank cell: Hi Jesse How are you? Please do the following: 1. Activate Programmer in the Menu Office button Options button Popular Show Programmer 2. Select Programmer - Visual Baic 3. Insert Module 4. Copy the code: Public Function AllConfirmed(R As...

Excel: writing a macro, inverted commas, summary document

inverted commas, summary document, ogilvy: Ralph, If you want to send me a workbook with an explanation of what you want me to advise you on, I can take a look. I am not sure what your expectations are, but just note that I am here to provide technical advice. I don t write work for free writing...

Excel: XL pivots w/ limits, non-calendar quarters, calendar quarters, gross amounts

calendar quarters, gross amounts, comp insurance: Greg, If I was doing it, I would go to my source data and enter an additional column with the quarter. This column would have a formula such as (assume the pay date is in C2, then in some column on row 2): =if(And(C2 =Date(2011,4,4),C2 Date(2011,6,4)),...

Excel: If then?, percentage change, a1

percentage change, a1, spreadsheets: I d calculate the difference by using the ABS function which removes the sign =abs(April - May) replace April and May with the appropriate references Not sure what you had in mind for percentage change, but possibly =(max(april,may)/min(april,may))-1...

Excel: Assign Hyperlink to Newly Created Sheet, cell d5, cell d2

cell d5, cell d2, sheet names: Sub AddLink() Dim strLinkSheetName As String Dim strLinkTextDisplay As String Dim wksWithLink As Worksheet strLinkSheetName = index Set wksWithLink = ThisWorkbook.Sheets(strLinkSheetName) strLinkTextDisplay = ClickThis! ...

Excel: Auto assign serial number every time Excel file is opened, macro commands, excel template

macro commands, excel template, own computer: Put the starting Serial# in a cell on a new sheet and hide the sheet. Let s say that s Sheet2 cell A1. Press Alt/F11 to get to the VBE, double-click on ThisWorkbook in the project window (which gets selected when you press Ctrl/R), then enter this code:...

Excel: Auto assigns serial number every time Excel file is opened., cell a1, developer option

cell a1, developer option, excel objects: Assume you have an Excel 2007 file, and each time it s opened, you want a random number between 0 and 9999 to be automatically entered in cell A1 of sheet1. Does the Developer option appear in your main Excel menu? If not, from the Office menu (click...

Excel: Auto loading data from a code number, data validation, variety type

data validation, variety type, vlookup: Greg, since you have class codes in column A and you want to enter class codes in column A, then I will assume that your 646 rows of data are in a separate worksheet in the same workbook. So assume the header Class is in Sheet1 in cell A1 Sheet1!B1:...

Excel: adding numbers in multiple excel worksheets, adding numbers, j11

adding numbers, j11: John Excel doesn t like the numbers in the sheet name. To get around this create two new sheets, place one before the sheet you want to start at and name it First, the other at the end of the sheets you want to sum and name it last. Use the formula =sum(First:Last!J11)....

Excel: Calculate working hours, excel sheet, richard florida

excel sheet, richard florida, quick response: Deep As I mentioned the format of the cell and the entering of the data is vital to the correct answer. I am not sure what time system you use in Delhi so I can t give you specifics as to what format to use, but when you are calculating time that spans...

Excel: Cash flow chart, cash flow chart, builtin functions

cash flow chart, builtin functions, paypal account: If you send a sample wb to me at bobumlas@hotmail.com I ll show you what needs to be done (a lot easier to show than explain) Use subject of AllExpertsQ and redescribe the issue. Are you interested in online Excel classes led by me? 2 are currently...

Excel: Cash flow chart, cash flow chart, chart expert

cash flow chart, chart expert, richard florida: Mikkel Sorry I am definatly not a chart expert. I seldom create charts unless my children need one for school, and then it takes me three hours to do a simple one. I suggest you reask the question of another allexperts person and I am sure you will...

Excel: In-Cell Editing turns off when I enable my Macros, target cell, target address

target cell, target address, thanks in advance: Emily Sorry it took so long to get back to you. I have been pretty busy the last week or so. I did a little testing and it appears that simply protecting the worksheet causes the ability to edit directly in cells to go away. I did not know that, but, I...

Excel: Checklist & Date Recording Excel spreadsheet, google docs, direct email

google docs, direct email, google: Sorry for the delay in replying - somehow this question didn t appear on my list to answer until now. I m not familiar with Google docs which probably doesn t help, and I m always SLIGHTY wary of spreadsheets with multiple users - I d usually use Access for...

Excel: Clearing of a multi-select list box, right before my eyes, unwanted behavior

right before my eyes, unwanted behavior, vbe: Matt I am not sure how that could be happening. It is possible that there is a Workbook_Before_Close code that runs and clears the listbox. It would have to check the name and not clear the listbox if the filename was as expected. It seems unlikely,...

Excel: COUNTIF Function in EXCEL 2007, monday tuesday wednesday, b25

monday tuesday wednesday, b25, days of the week: Hi, If I correctly understand the situation and the cells A2:A25 and B2:M2 actually contain strings values like Monday and Tuesday then G3: =sumif($A$2:$A$25,G$2,$B$2:$B$25) then drag fill to the right to M3 -- Regards, Tom Ogilvy ...

Excel: Combining macros, source sheet, strikethrough

source sheet, strikethrough, cols: Like this: 1. Adjust your code to: Sub Insertsheet(sSheetName As String) Application.DisplayAlerts = False On Error Resume Next Sheets(sSheetName).Delete Application.DisplayAlerts = True On Error GoTo 0 Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name...

Excel: Combining If statements, k21, h22

k21, h22, ogilvy: Stacy, one problme is that you second formula if(sum(k15:k26)=8, ,8,8-sum(k15:k21) is invalide. It is missing a closing parentheses and it has too many arguments. I will assume you want it to be if(sum(k15:k26)=8, ,8,8-sum(k15:k21) your...

Excel: Combo Box Searches, combo boxes, word application

combo boxes, word application, combo box: * I m sorry, I dont see anything in this code that explicitly refers to the true/false properties even of checkboxes. Maybe the checkboxes are placing a value on a worksheet and then the code is referring to a cell s value on the worksheet, but I can...

Excel: Compare 2 columns to lookup a value in another column, column c, orders of magnitude

column c, orders of magnitude, question thanks: Patrick If you give the sentences a range name, sentences and the words the range name words, the following should work, as best I understand your task Option Explicit Sub test() Dim S As Range Dim W As Range Dim i As Integer Dim j As Integer...

Excel: To Compare two columns and find result, sheet1, hi thanks

sheet1, hi thanks, bbb: Hi Prasad How are you? What about this? Sub prodload() Dim myprange As Range, Lrange Dim myprange1 As Range, Erange Dim i As Integer Set myprange = Sheets(1).Range( D2:D10 ) Set myprange1 = Sheets(2).Range( D2:D10 ) Set Lrange = Sheets(1).Range(...

Excel: Compare Question, b1, match

b1, match: Your 2nd example is a bit misleading, since there is no M123A11 to search for, but they DO match M123A10 according to your 1st example, so I m a bit comfused. However, if what you wanted to do is match the first 7 characters of column A to the first of column...

Excel: Comparing multiple column values, array formula, blank cells

array formula, blank cells, blank cell: This will give you the Mode for the text strings, and if there is more than one matching mode, you ll get the first one in the row. This is an array formula, after entering it into a cell in row 2, you must press CTRL-SHIFT-ENTER to confirm it and activate...

Excel: Conditional Formatting - Multiple Conditions, false answers, alternate color

false answers, alternate color, default table: The trick for designing CF formulas is to work on them in an empty cell first. You are trying to construct formulas that give TRUE/FALSE as result. If the cell to watch is B2, then the formula to test for N/A would be: =$B2= #N/A Another formula...

Excel: Count unique triplicates in column, array formula, countif

array formula, countif, office excel: Dave, I imagine you could add whatever parameters you want in the conditions of the second if statement I tested the below =SUM(IF(IF(LEN($B$2:$B$36)*(YEAR($B$2:$B$36)=2010),1/COUNTIF($B$2:$B$36,$B$2:$B$36)=1/3),1/COUNTIF($B$2:$B$36,$B$2:$B$36))) entered...

Excel: Countif Multiple Criteria, array formula, sumproduct

array formula, sumproduct, status column: Jason, sorry for the delayed response - I teach a 5 hour class on Wed nights. I pasted in your data and tested it with my formula (I removed the that was inadvertently left in when I massaged your original formula) and it worked fine for me with your...

Excel: Countifs, segment c, column c

segment c, column c, text strings: Lena, =COUNTIFS($A:$A, a ,$B:$B, segment ,$C:$C, =0 ,$C:$C, =1000 ) that worked for me. I assume you have numbers stored in column C and they are formatted to show a $. If the dollar sign is produced by formatting, then the above should work fine...

Excel: Counting highlighted cells in column after using conditional formatting, conditional formatting, question pool

conditional formatting, question pool, cpearson: Dale, You didn t ask me this question - I found it in the question pool. Are you using Chip Pearsons CountColor function from here http://www.cpearson.com/Excel/colors.aspx the problem is that there is no built in way to in VBA to determine what...

Excel: Creating Specific numbers for specific cell values, boston beer, cell values

boston beer, cell values, excel formulas: Thanks for the challenge. If your sample list starts in B1, enter these formulas and you ll see the answer you want in column F -- the rest you can hide or cut somewhere else. C1: =MID(B1,FIND( DG_ ,B1),255) D1: =TRIM(MID(C1,FIND( - ,C1)+1,5)) E1: 1 (for...

Excel: Creatting Master Sheet, wrong with copy, excel sheet

wrong with copy, excel sheet, time activity: Make sure the sheet is named Master, then run this macro: Sub CreateMaster() Sheets( Master ).Rows.Delete For Each sh In Sheets If sh.Name Master Then sh.UsedRange.Copy Sheets( Master ).Range( A1 ).Offset(Sheets(...

Excel: Cross Referencing, power point presentations, ofiles

power point presentations, ofiles, fso: Put this in a module, then run Folders Is that what you re looking for? -------------------------------- Option Explicit Dim i As Long Sub Folders() i = 1 Cells.Clear MsgBox Point to top level directory, then click Cancel Application.GetOpenFilename...

Excel: change the data refresh options to optimize file size, external data source, query definition

external data source, query definition, richard florida: Clarkson This may be what you are looking for From excel help, otherwise I don t hae any other ideas. You can refresh an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel,...

Excel: compare, cell interior, column c

cell interior, column c, macro code: Zaina Fatima, Sub comparevalues() Dim r As Range, cell As Range Dim e As Double e = 0.01 Set r = Range( C3 , Cells(Rows.Count, C ).End(xlUp)) For Each cell In r If IsNumeric(cell) And IsNumeric(Cells(cell.Row, E )) Then If Abs(cell - Cells(cell.Row,...

Excel: Comparing two nimbers, spreed sheet, excel formula

spreed sheet, excel formula, colums: Rick I really don t understand the real data change you are trying to make. If the reference number were 12.100 as in your example, what would the adjusted 11.800 be? If you changed it to 11.9 it would be within 2% but so would 11.86 and so would 12.000....

Excel: comparing spreadsheets, spreadsheets, spreadsheet

spreadsheets, spreadsheet: You would need to mockup a sample workbook showing: 1) Your two sheets 2) What you mean visually by I would need to know a 4 had dropped off one and a 5 had been added to the other . There s no way to know what you have in mind unless you show it. ...

Excel: concatenate two columns having date values, dear tom, decimal numbers

dear tom, decimal numbers, navin: Navin, =concatentate(text(c11, hh:mm ), to ,text(d11, hh:mm )) the bad results you were getting are because times are stored as the fractional of a whole day. So you get those funny looking decimal numbers. Using the Text function allows you to...

Excel: consolidating multiple data ranges by using PivotTables, microsoft experts, pivottable

microsoft experts, pivottable, pivot tables: The only way I know to create a pivot table is via the wizard - I do use pivot tables a lot, but don t tend to use them for consolidation so I don t class myself as an expert in that area, therefore it MAY be that another expert has another idea - you might...

Excel: copying selecected values, column c, unique records

column c, unique records, navin: Navin Is this something you will be doing repetitively? If it is a one time thing, you can filter unique values very easily by filtering. In excel 2007, it is Data-Advanced filter, copy to another location, and check unique records only. If you are...

Excel: Data copy in a variable, sh2, acol

sh2, acol, mcol: Masood, I cannot make the text SNo to sno (lower case) as it is pulled up dynamically from external source and I never asked you to. The most robust version of the code, which you didn t use, was designed to handle that situation. I didn t tell...

Excel: Data types for Variables, odds and sods, intel processors

odds and sods, intel processors, memory work: Steve Davis, both integer s and Long s are whole numbers. The long requires twice as much storage as an integer so it seems like you should always use an integer if you can right? No. That was true when the Intel processors used an 8 or 16 bit register....

Excel: Data Validation, validation problem, data validation

validation problem, data validation, array function: Joe, If you want to send me a sample file with your restrictins - what you don t want to do, I am sure I can come up with a suggestion for you. Note, that you only need to send representative data that represents the problem - I don t need a big huge...

Excel: Date Formula, date function, second date

date function, second date, bottom right corner: If you show Excel a pattern , it will duplicate that pattern when you do a drag down. 1) Put your first date in the first cell 2) Put your second date for the next month in the next cell 3) Highlight both cells 4) Now drag the bottom right corner downward...

Excel: Date function, work absence, date function

work absence, date function, correct dates: The DATE function would do it for you =date(year,month,day) I wouldn t necessary have the data entered into cells for each date, probably using functions to bring in the data from a seperate tab, but whatever works is fine! http://www.aidanheritage.byethost3.com/excel/absenceRecorder.xls...

Excel: 1904/1900 Date options automate in a macro?, workbook section, date system

workbook section, date system, datevalue: Gregg, also, I believe I still have some windows help files that explain those macro 4.0 commands. I don t have them here at work, so I will take a look when I get home (if I can find them) and revise this answer to include the documentation of the command....

Excel: Decrease Decimal, 4 digits, value application

4 digits, value application, m10: Rahman, This worked for me as I understand the requirement. Sub Roundto2() Dim r As Range, cell As Range Set r = Range( M10 , Cells(10, M ).End(xlDown)) For Each cell In r If IsNumeric(cell) Then cell.Value = Application.Round(cell, 2) ...

Excel: Displaying a Valid Production Task, e9 e10, wash dishes

e9 e10, wash dishes, clearer view: Now the original file image is missing. Please send both the sample and the valid lists to me at bobumlas@hotmail.com. Use subject of AllEpertsQ , repeat everything from above. ================================== Are you interested in online Excel classes...

Excel: Dsum() to pick criteria on cell value change, cell b1, field criteria

cell b1, field criteria, sumif: Krishna I have to say I do not fully understand wht you are trying to do, but you can use a cell reference as your criteria in the DSUM formula. For example =DSUM(A1:B10,2,E1:E2) Where E1 and E2 are the criteria and they are external to the formula...

Excel: Duplicate name check software, cell b1, cell a1

cell b1, cell a1, check software: Say you have a list that begins in cell A1 of Sheet1 and goes down col A. You want to check if each item on that list also exists on a different list, which is in col A of Sheet2. In cell B1 of Sheet1, enter =IF(ISERROR(MATCH(A1,Sheet2!A:A,0)), Not on...

Excel: Eliminating Empty Lines in a Dropdown, blank cells, chris answer

blank cells, chris answer, data validation: * Oh, right - if the cells have formulas in them, I guess it makes sense they will still show up on the list as not being blank cells. The only other thing I would suggest is vba. You could dump this kind of code behind the worksheet in question,...

Excel: Enable grouping activity on protected sheet, daniel douglas, ogilvy

daniel douglas, ogilvy, support microsoft: Daniel Douglas, read this article Some of the properties of worksheets are not preserved in Excel http://support.microsoft.com/kb/211809 But that really doesn t tell the whole story. 1) the outline/grouping has to already be set up. 2) then...

Excel: Excel 2000, cell a1, excel 2000

cell a1, excel 2000, ogilvy: Joey, Add a sheet to your workbook. Assume the original sheet is named Sheet1 in the new sheet, go to cell A1 and put in the formula =IF(LEN(TRIM(Sheet1!A1)) 0,IF(ISNUMBER(Sheet1!A1),Sheet1!A1,UPPER(Sheet1!A1)), ) then select this cell and...

Excel: Excel 2003 XP Cannot get rid of Shared Worksheet, column data, ogilvy

column data, ogilvy, t line: John, Hopefully you have the original shared workbook. You should close all workbooks and open this workbook by itself. then You can follow these directions to unshare it. http://www.wikihow.com/Unshare-an-Excel-Workbook Make sure you follow...

Excel: Excel 2007, question thanks, interior color

question thanks, interior color, occurance: This routine assumes the values are in column A and column B is empty: Sub Coloring() Range( B1:B2000 ).Value = [row(1:2000)] Range( A1 ).CurrentRegion.Sort key1:=[a1], Header:=xlNo For i = 1 To 2000 Step 5 Cells(i, 1).Resize(5).Interior.Color...

Excel: Excel (2007) disabled items list, left hand corner, bill jelen

left hand corner, bill jelen, upper left hand corner: It looks like in that dialog, you can select the disabled items and click the Enable button and then things should work. I don t know of a sure-fire macro to get a particular workbook added to the list. If you want, you can contact Bill Jelen: pub@mrexcel.co...

Excel: Excel 2007 Match columns, empty cells, formula index

empty cells, formula index, question thanks: Simon, I guess I am not following. If you could send a small sample workbook (a little bit of data on two different sheets - just data like your data, nothing private) and explain what you want to see and where you want to see it, I can try to help - but...

Excel: Excel 2007 Question, number column, consecutive series

number column, consecutive series, richard florida: James The simplest way to find the lowest date is to do two sorts of the data, first a sort of the PO DATE column, which I assume will leave you with a consecutive series of dates in the date/number column. Then a sort of the dates in the date number/column....

Excel: Excel 2007 VBA : Autofit Merged Cells formula result, delimeter, target

delimeter, target, vba: Hi Mark, Unfortunately, the autofit feature does not work on merged cells. You ll have to adjust your code so it figures out the needed height based on the actual content of the cell and calculate it using an algorithm that gets the needed height from the...

Excel: Excel 2007 VBA: Inserting Rows and joining range, row selection, mvps

row selection, mvps, l brown: Mark, only a section of my range down first, the code doesn t shift things down. It inserts 4 cells in columns A:D at the end of your existing range defined as TableQty. (anything below those 4 cells will be shifted down) I don t have it insert...

Excel: Excel 2007 VBA: View Headings on/off, row headings, column headings

row headings, column headings, vba: The only way to permanently remove the row & column headings for that workbook is by using XML code to disable the command to put it back on. As it is, the only 2 options you have are to turn them off when the workbook opens (in the workbook_open event put...

Excel: Excel, fitness class, fitness classes

fitness class, fitness classes, cell a1: You can use a formula such as this =IF(Sheet1!A1= fitness ,1, ) In this example if the cell A1 in sheet1 contains the word fitness then a 1 will be put in the cell that has the formula in it (in this case the formula is in a wokksheet that is within...

Excel: Excel, range target, target address

range target, target address, target range: Hello again Richard, I can only envision one reason the error occurred: that the worksheet DateLastChanged didn t exist. The code creates this sheet automatically if you edit any cell of the active worksheet. But now I realize that the sheet doesn t get...

Excel: Excel, vlookup formula, excel function

vlookup formula, excel function, cell a1: Kenso The VLOOKUP formula will tell you how many you have on hand. I am assuming that your inventory system calculates the number on hand after each sale. So it seems to me that as each sale is processed you will be able see how many are remaining on hand,...

Excel: Excel, inrange, double application

inrange, double application, isnumeric: Dan, Function AverageByBlackFont(InRange As Range) Dim Rng As Range Dim sumByBlackFont as Double Application.Volatile True For Each Rng In InRange.Cells If Rng.Font.ColorIndex = 1 And IsNumeric(Rng.Value) Then SumByBlackFont...

Excel: Excel Address to Word Labels Help, avery labels, google search

avery labels, google search, lastname firstname: Matthew, Word has the built in capability to print address labels. You just walk through the wizard and specify Excel as your data source. Also, if you are using Avery labels, it has a word addin that basically does all the work for you. You can...

Excel: Excel calculations, format cells, word apple

format cells, word apple, k33: Diane, a cell can only have the value it displays for the most part. but here are a couple of ideas that may be what you want - I can t really understand exactly what you want. =countif(K21:K83, Apple )*.25 this will count the number of cells...

Excel: Excel doubt, ldt, richard florida

ldt, richard florida, florida usa: Viji I don t know of a way to put the line in the middle of a cell unless you use the underline feature which will underline both rows of text. This will look almost the same, but not quite. There may very well be a way, maybe someone else will have an...

Excel: Excel drop menus, drop menus, drop boxes

drop menus, drop boxes, data validation: DEPENDENT DROP BOXES These are called Dependent Drop Down lists and they use named ranges and INDIRECT() references for Data Validation lists in the proceeding drop boxes. Here s a sample sheet I ve posted showing how it s done. The sheet CHOICES has...

Excel: Excel Formula - Extract Code from a String, array formula, array formulas

array formula, array formulas, vcode: With a smaller data set, I would use an array formula for this. But with a dataset as large as yours, you would actually notice your spreadsheet getting noticeably slower with that many array formulas active over such a large range. So I would use a macro...

Excel: Excel formula with 4 possible outcomes, dollar signs, excel formula

dollar signs, excel formula, vacation time: Hello Dorothy, Thank you for your question. This should be easy to set up in Excel. First, you will need to create a small table for the formula below to lookup the number of hours and then lookup the amount of vacation earned. This data can be anywhere...

Excel: Excel function, vlookup function, excel function

vlookup function, excel function, leftmost column: Scott How do you know what number corresponds to your selection of 201 or whatever? Is there a block of data somewhere that you can use to look up the value? That would be pretty easy. You could use a VLOOKUP function in the column B to look up the value...

Excel: Excel help, cell b1, cell a2

cell b1, cell a2, builtin functions: in cell B1, ctrl+shift+enter this formula: = A &MATCH(TRUE,ISTEXT(A1:A10),0) -------------- Interested in online Excel classes led by me? 2 are scheduled: May 26, 7-8PM est, Intro to VBA May 26, 10-11PM est, Excel builtin Functions --------------...

Excel: Excel with hyprlinks, service manuals, hyperlink

service manuals, hyperlink, pdf files: * Hi Eddie, sorry I m the same person who already answered your question (among others) on utteraccess.com As we said there, it s not an issue of hyperlinks not working in all shared excel files. It s a matter of a rights access, the hyperlink being...

Excel: Excel locked cells, richard florida, florida usa

richard florida, florida usa, cheers: Jim You can protect the sheet from entry. First, with the workbook open, go to sheet or tab that you want to protect. Then go to toolbar/tools/protection/protect sheet. Put in your password as requested You may want to allow users to select unlocked...

Excel: Excel Macro, column values, excel macro

column values, excel macro, column c: I think I follow this, and I THINK it s probably fairly simple (doing a 3 way sort would seem to be the starting point, then deleting lines as required). I forgot that we are away this weekend (it s a holiday weekend here in the UK) so I haven t time at the...

Excel: Excel math, weighted averages, joe joe

weighted averages, joe joe, weighted average: JOE I don t know of a function for weighted average but it is relatively simple to callculate. All that is neede is to average the weighted amounts not the individual amounts. For example If we are weight averaging the times a series of numbers comes...

Excel: Excel Query, plain english explanation, excel query

plain english explanation, excel query, up arrow: Steve Davis, go to you worksheet in column D and go all the way to the bottom (row 1048576). Now hit the END key and then hit the up arrow. You will end up at the last row that has data in column D that is what Range( D1048576).End(xlup) does when...

Excel: Excel Question, vlookup, indirect address

vlookup, indirect address, cell address: If the dates are in A and the item you want to get is in L, you can use either of these 2 formulas (and there are many others): =OFFSET(INDEX(A:A,MATCH(TODAY(),A:A,0)),0,11) =VLOOKUP(TODAY(),A:L,12,FALSE) So you can see that you CAN use the VLOOKUP...

Excel: Excel - subsampling data, minute intervals, excel code

minute intervals, excel code, random data: Hi Kathy, This is a fairly simplistic way to do it using an additional formula column and filtering. Add a column and try a version of this formula pointing to your column numbered 1 through whatever. =IF(OR(RIGHT(A1)= 5 , RIGHT(A1)= 0 ), cool , no...

Excel: Excel VBScript & Conditional formatting Question, selection worksheet, volleyball club

selection worksheet, volleyball club, level assignment: Mike, the only way to do what you want is to use the Calculate event. Unfortunately, that event fires every time there is a calculate, so you would have to loop through all the cells you want to control and update the color on every calculation (whether...

Excel: Excel vba, priority 1, sex row

priority 1, sex row, sheet1: SHEET1 TO MANY SHEETS Here s a macro I already have published that can get you 95% of the way there, it is used for parsing rows of data from one sheet to many sheets named for the same values in a specific column. https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/parse-functions/sheet1-to-sheets...

Excel: Excell, cell b2, cell b3

cell b2, cell b3, cell b1: Hello Jeff, Thank you for your question. First, you will need to make a list of the numbers you want to increment by. For example, I placed the list of numbers in Column F when I tested this formula, but you can place them anywhere on the sheet, or...

Excel: entering customer mailing info into billing statement, customer mailing, billing statement

customer mailing, billing statement, mailing info: Kelly I am not familiar with that template, but, you can definitely populate the template with customer mailing information from your customer database. You customer database can be a different worksheet in the same file, or another file. The formula...

Excel: excel, sheet1, column c

sheet1, column c, a2: You don t need a macro for this comparison, you can do it in sheet2 with formulas. If you re simply trying to compare the two sheets, which column is the match column, the column with the unique ID or CODE that signifies a match? Assuming you want to...

Excel: excel: age computation, nutritional status, jocelyn

nutritional status, jocelyn, b2: Hello Jocelyn, Thank you for your question. Perhaps I am not understanding your question correctly, but your formula worked perfectly when I inserted it into a spreadsheet. I placed a birthday in B2, and a date several years later in A2, and your formula...

Excel: excel graph/chart, graph chart, line graph

graph chart, line graph, scatter chart: If you re saying 2008 version you must be using a Mac, and I m not that familiar with that. But for a line chart, you really want the values to be the y-axis and the categories to be the x-axis. If you re wanting to switch them, I m envisioning that you are...

Excel: excel question, cell a1, computer spreadsheet

cell a1, computer spreadsheet, 2 divided by 1: Dimas It is clear that your math is incorrect. Without a calculator, computer, spreadsheet, slide rule abacus, or whatever you are using you should see that (100.2 - 100) is .2 and .2 divided by 100.2 is a tiny bit less than .2 divided by 100 which is .002...

Excel: Excell Save if condition is true Macro, exact spelling, copy case

exact spelling, copy case, format date: Something along these lines then, you ll have to very the EXACT spelling of the MyPath variable, it has to be exactly correct with a at the end: Option Explicit Sub SaveConditional() Dim MyVal As String Dim MyPath As String remember the final...

Excel: export chart, filetype, image file

filetype, image file, adobe: * Since you have excel 2007, you can take advantage of the export as PDF feature. Since in most programs, PDFs are actually one of the acceptable picture formats (as a pdf is truly an image file, to everyone except adobe who see it differently), then hopefully...

Excel: Fast or Slow?, vba procedure, exhalted

vba procedure, exhalted, recalculation: Steve, Just because you don t hide a row that contains a formula with a totals doesn t really mean anything. I can t tell you which worksheet functions trigger a sheet wide recalculation when a row is hidden in the sheet. I know that the Subtotal worksheet...

Excel: Find Top 10 Oldest Dates In An Excel 2007 Spreadsheet, source sheet, sheet names

source sheet, sheet names, worksheet functions: * there are probably multiple ways to do this, but i would use vba, since it comes easier to me, actually, than some of the more complex and numerous worksheet functions. As I began writing this, several more questions came to my mind, and I went ahead...

Excel: Find a value and return a result, cell d1, input number

cell d1, input number, vlookup: Hello Andy, Thank you for your question. I hope I am understanding your question correctly. It appears the following formula, when placed in Cell D1 will return the result you are looking for. =VLOOKUP(C1,$A$1:$B$2,2,0) You will need to replace...

Excel: Finding MIN and MAX, array formula, array formulas

array formula, array formulas, curly brackets: Pete both min and max work as array formulas =MIN(IF((MONTH($A$1:$A$500)=3)*($B$1:$B$500 ),$B$1:$B$500)) entered with Ctrl+Shift+enter rather than just enter. If entered correctly it will appear in the formula bar enclosed in Curly Brackets{}...

Excel: Flagging due dates, due dates, g1

due dates, g1, completion date: Hi Helen How are you? I am probably doing this too simple but please have a look at the following 1. Type in G1 =now() 2. Type in A1 the name of the component, in B1 the date of inspection 3. Type in C1 =IF(B1=$G$1, **** , ) Of course you...

Excel: Follow on from last question, last question, ogilvy

last question, ogilvy, step 1: Steve Davis, yes and no for i = cells(1,columns.count).End(xltoLeft).column to 1 step -1 the first 1 says to find the last column in the First (1) row. You can specify any row I like to use the more general cells rather than range and columns.count...

Excel: Formatting Time on imported data, time entries, richard florida

time entries, richard florida, practical solution: Melanie I cannot duplicate the problem but I suspect that the cells are text, not number formating. To convert the cells to numbers you can use the copy paste special multiply-this will convert the cells to numbers. To do this--put the number 1 in...

Excel: Formula to calculate attendance., png format, paint program

png format, paint program, database layout: Your keyboard has a PRINT SCREEN key somewhere. When you press that, it puts a picture of the screen into memory. Now open some sort of paint program and paste into that program, then crop down to just what you need and save as a JPG or something for uploading...

Excel: Formula for Pivot Table, pivot table, cell position

pivot table, cell position, excel formula: Mark You can t use a formula to conditionally insert a pivot table. You can write VBA code to insert a pivot table, triggered by an activity, and conditional on some aspect of your spreadsheet. Alternatively, you could have formulas that behave like a pivot...

Excel: Formulas for Timesheet, hourly appointments, necessary thanks

hourly appointments, necessary thanks, 599cd: I would use a little VLOOKUP table for this. First, here s a tutorial on VLOOKUP you should watch: http://www.599cd.com/tips/excel/vlookup/?key=AllExperts Now what I would do in your case is set up a lookup table that contains all of your different rates....

Excel: Enable grouping activity on protected sheet, vba

Excel: Enable grouping activity on protected sheet, vba

Excel: formula in a column using VB, column c, navin

column c, navin, aidan: difficult to follow from the picture (my email if it helps is aidan.heritage@virgin.net), and I m not clear what you mean by till the values are present - does this actually mean NOT present? Also not clear what is meant by the formula - what did you...

Excel: IF function, destination cell, logic error

destination cell, logic error, cell a2: Ok, in that case I d use a MATCH function to get the number, and then an index function to translate =INDEX($C$1:$O$1,1,MATCH(C5,$C2:$S2,0)) where C1:O1 are the location names, C5 is the header row of the translation table (so your row 1 col3), C2:S2...

Excel: Handling #N/A error when taking AVG( ), sheet names, error values

sheet names, error values, game 1: Sarah, Here is another way to attack the problem (that I forgot to mention) if your sheet names are really Game1, Game2, etc or have any type of naming convention that would lend itself to this solution. =SUM(SUMIF(INDIRECT( Game &{1,2,3,4,5,6}&...

Excel: HELP: SEARCH function using range instead of single value, array formula, countif

array formula, countif, word cat: marcel, this worked for me: =IF(OR(ISNUMBER(SEARCH($O$2:$O$5000,H2))), YES , NO ) this must be committed/entered with Ctrl+Shift+Enter (hold down the ctrl and shift keys, then hit enter) because it is an array formula. Also, you must have words...

Excel: Hide and Unhide Combo Box, oleobj, combo boxes

oleobj, combo boxes, data validation: Jay, well, again, you haven t told me what type of combobox or what version of excel for activeX you can do Sub showNamesActiveX() For Each oleobj In ActiveSheet.OLEObjects If TypeOf oleobj.Object Is MsForms.ComboBox Then MsgBox oleobj.Name...

Excel: Hiding cells based on cell format, blank cells, vba procedure

blank cells, vba procedure, blank cell: Steve Davis, When you make that comparison, the value of the cell is coerced into a number to make the comparison. That coercion makes the cell equivalent to zero. So it isn t a formatting problem since the value property usually ignores formatting. ...

Excel: Hyperlinking in the same workbook in excel, vlookup formula, richard florida

vlookup formula, richard florida, sheet1: Cindy Can t quite tell from your question if you want the entire worksheet to appear or if you want just the contents of the single cell. But the formula for the single cell is =Sheet2!A1 This formula might be in a cell in sheet1, or sheet 3, or...

Excel: hours and rate to get wages., free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, cell c3: 4:23 is 4 hours and 23 minutes which Excel stores internally as a FRACTION of a whole day. So, if you have 4:23 in cell A1, then in cell B1 you can put: =A1*24 This will show you 9:12, but don t worry about that, change the FORMAT of the cell to a NUMBER...

Excel: IFFormula, gain value, column c

gain value, column c, loss column: Trisha I will make some assumptions and give you a formula based on those assumptions Assume column A represents, and is headed, Date. Assume that each of you has three columns Trisha has the first three columns column B,C,D Column B is headed...

Excel: Ignore Zeros in Top 10 Reoccuring Text, vlookup formula, cell d1

vlookup formula, cell d1, text values: Stephen, I just got errors in the formulas in E1, F2, and G2. So I got rid of those and put this in E2 =IF(AND(ISNUMBER(SMALL($C:$C,ROW($A1))),ROW($A1) =$D$1),INDEX($A:$A,MATCH(SMALL($C:$C,ROW($A1)),$C:$C,0),1), ) then drag filled it down for...

Excel: Import selected txt on Macro, filedialog, txt files

filedialog, txt files, recognise: Alvaro, assuming FICHERO contains the information you need, then it would be With ActiveSheet.QueryTables.Add(Connection:= TEXT; & FICHERO, Destination:=Range( $A$2 )) so you have to concatenate the value held in FICHERO -- the way you have it...

Excel: Incorporating pivot tables in VBA Macro Excel (2), pivottables, proper headers

pivottables, proper headers, vba macro: Irma, You show the source of the data for your Pivot Table as LIWD!C1:C5 . That is 5 cells in a single column. But your code is trying to work with at least two Pivot Fields which would not be possible in a one column source. Just to be sure, I created...

Excel: Insert chr(10) every after ever n number of characters in string, array elements, cell a1

array elements, cell a1, string s1: Kevin, basically yes - however, the way my macro works, it is doing everything character by character. So it would probably need some modification to avoid splitting words. Perhaps when it is handling a space character, look ahead and see if there is...

Excel: Invoice Automation, cell a1, invoice template

cell a1, invoice template, question thanks: My coding already increases the number by one - it runs on the OPEN event of the workbook - the SAVEAS coding which you have recorded gives you the syntax, just change the string given to a string variable - so something like C: empInvoice & range(...

Excel: How to ignore blanks, blank cell, time james

blank cell, time james, first test: Hello James, Thank you for your question. Try the formula below, it should leave the cell in the C column blank if the cell in the A column is also blank. For example, if A4 is blank, then C4 will also be blank. I used a nested IF statement to first...

Excel: insert equation into legend, legend text, subscripts

legend text, subscripts, quotes: =SERIES( SERIES NAME GOES HERE, IN QUOTES ,...) Seems like sub/superscripts aren t allowed in the legend. To confirm, I made a superscript text in the sheet and then created a chart and the legend did NOT have the superscript. You could fudge it with a...

Excel: invoicing via email, excel sheet, notebook computer

excel sheet, notebook computer, pertinent data: Since you repeated the same information again, I have no choice but to repeat, based on the information provided, there is no reason you should not be able to open/edit the Excel sheet sent back to you by the technician if it s STILL an Excel file. If he...

Excel: jumping typeing letters, windows xp professional, windows internet

windows xp professional, windows internet, internet explorer: The keyboard is a function of the operating system, and as you have mentioned typing in (I assume) Windows Internet Explorer that would seem to bear that out - there MAY be an issue with the keyboard, or with the driver for the keyboard - but I m not a hardware...

Excel: Lookup Function: Worksheet Selection based on Value, cell a1, worksheet selection

cell a1, worksheet selection, b1: You could make use of the INDIRECT worksheet function to pull the sheet name you want to use from another cell. Say Cell A1 contains the text Sheet1 Cel B1 contains your lookup value and you want to do a look up like this: =VLOOKUP(B1,Sheet1!$A$1:$B$100,2,False)...

Excel: label (form control) to cell without vba, cell b1, object dialog

cell b1, object dialog, index command: Clarkson, ============== start of revision ========= whoops - I misread your post. Disregard all the stuff about a dropdown box below. I thought you were asking about a Forms control in general and I have since reread your posting and now realize you...

Excel: look up, e mail address, lumber type

e mail address, lumber type, radiata pine: Hello Billy, Thanks for the follow-up. I will give you the formula I plan to use. You may be able to get it to work yourself. If not, feel free to e-mail the workbook to me, and I will have a look at it. My e-mail address is below. The formula is:...

Excel: How to look up or match two criteria - one vertical and one horizontal, e mail address, exact layout

e mail address, exact layout, store names: Hello Missy, Thank you for your question. This certainly sounds like something Excel is capable of doing. However, since I am unable to see your data, it is not possible I can provide a solution without knowing more about your data and seeing the exact...

Excel: Macro to make dynamic line graph, line graph, cell addresses

line graph, cell addresses, dynamic charts: A picture of the resulting chart would have been good for final comparison. I ran your existing macro on the data you posted and got an odd looking chart. I then converted the macro into a dynamic macro and ran this, getting the exact same odd-looking...

Excel: Macro Help - Find the cell with the highest value in a column, range selection, column c

range selection, column c, max value: Wayne, assume you have a named cell with the name that holds the number of rows to fill. Assume that if that number were 10 as an example, then the formulas would be filled in A1:P10 (so row 1 is included in the count of the number of rows. Sub AAA()...

Excel: Macro Question, cell d5, target value

cell d5, target value, input cell: * Paste the code behind any worksheet where the input cell is. If it changes, put the code behind that sheet instead of the original sheet. change: Set myrangefrom = Range( R:S ) to be more specific, and in fact really we should always qualify...

Excel: Mandatory Cells (again), question column, allexperts

question column, allexperts, hi bob: the code as is checks for a 1 in column B. If you want it to ensure there s something in X when ANYTHING is in B: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For i = 1 To Range( A65536 ).End(xlUp).Row If Len(Cells(i,...

Excel: Merge Madness, cat sat on the mat, exact situation

cat sat on the mat, exact situation, ogilvy: Paul Sub ABC() Dim bk1 As Workbook, bk As Workbook Dim spath As String, sname As String Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, lastrow As Long, lastcol As Long Set bk1 = Workbooks( three.xls ) Set sh1 = bk1.Worksheets(1) spath =...

Excel: Microsoft Excel, format cells, decimal points

format cells, decimal points, column width: Hello Rohit, I am sorry, but I failed to answer your question properly. To display a number this large, you will have to format the cell as Text, and Excel will display the entry exactly as entered. You will still be able to perform mathematical...

Excel: Missing components error message, microsoft home publishing, encartia

microsoft home publishing, encartia, purchasing a new computer: AllExperts is a good place to come for answers, but at top level you can navigate through different categories to find one that fits your question - thanks for the extra information - this page http://support.microsoft.com/?kbid=245829 MAY give a clue...

Excel: macro for excel to internet based portal, subject report, text boxes

subject report, text boxes, txt files: Excel can certainly do a SaveAs text file - so it could create a file with the data you need. What isn t clear is what the upload process is - if this is as simple as SaveAs then the macro will have no problems -otherwise, I would need to know the upload...

Excel: macros tested and works at home but not at work., repetitive works, array formulas

repetitive works, array formulas, paypal account: Can t help without actually seeing the problem workbook. Send me a copy and tell me what cell(s) you re trying to conditionally format. Send to bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what you re trying to do. ==================================...

Excel: master spreadsheet, master spreadsheet, unnecessary complications

master spreadsheet, unnecessary complications, vba: Leann The best way is to keep your similar data in one place. There are lots of tools e.g. sorting, filtering, subtotaling, pivot tables, formulas, etc to deal with data that is in rows and columns. Once you spread it out on multiple sheets, you usually...

Excel: math formula/calculation, math formula, excel sheet

math formula, excel sheet, escalation: * Do you want to have just one result, in one cell, with one formula, that shows you the total value after a given number of years? (if so, what is the given number of years?) There are probably a few ways to do this, but how about treating as a security...

Excel: microsoft Excel 2007, Sixthsense Excel

Sixthsense Excel: Hi King, I am not sure whether this is what you are looking for. Press ALT to activate the menu items and press P to move on to Page Layout and press I to select Print Titles. Alt+P+I Place the cursor In Rows To Repeat at top: field and select...

Excel: microsoft excel, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: You want to use CONDITIONAL FORMATTING. Here s a tutorial that explains what that is: http://www.599cd.com/tips/excel/conditional-formatting/?key=AllExperts Let me know if you have any other questions or comments. Also, be sure to visit my web site...

Excel: ms excel 2003 conditional formatting with countif function., city state zipcode, conditional formatting

city state zipcode, conditional formatting, array formulas: I need more info. First, you really want the conditional formatting formula to be one which returns true or false, not the result of a COUNTIF (which does work by treating 0 as false and all others as true), so a better formula would be =COUNTIF(UPDATEA,$A2)...

Excel: Need Formula, countif, sheet1

countif, sheet1, error msg: I m assuming you re in R1C1 mode, not A1 mode or this won t be valid. Next, I don t know what you mean by count the maximum date value. You mean how many times does the latest date appear in that range? And how does the 2 given dates fit into that? I...

Excel: Need Formula, array formula, maximum value

array formula, maximum value, max max: Let s say that in cells A1 to A10 you have the values with the corresponding dates in cells B1 to B10. You have another set of values in cells C1 to C10 with the corresponding dates in D1 to D10. Now cells A1 to A10 and cells C1 to C10 are the two columns...

Excel: Need Formula, maximum value, p2

maximum value, p2, cells: Sorry, even with the picture, I don t get it. Max of G & H doesn t help, because in your example G & H were all blank; I don t know what follow between the dates means; If you gave another example with G & H containing some values, AND your expected results...

Excel: Need Help with Dates, isdate, proper solution

isdate, proper solution, column c: Shawn, You just left out an End IF for the test if r is a date. This complied for me. Sub AAAA() Dim I As Long, FinalRow As Long Worksheets( Bug Report ).Activate FinalRow = Cells(Rows.Count, 2).End(xlUp).Row For I = 9 To FinalRow ...

Excel: Number Sorting/Ordering In Excel, street indexes, prefix

street indexes, prefix, yahoo: * This is a very difficult forum (allexperts.com) to ask ? s back and forth. why don t you email me a little sample (or a big sample if you want) to ipisors at yahoo dot com. I m sure there s some way to do it, I m not sure if you will need to change...

Excel: obtaining formula, sumproduct, column c

sumproduct, column c, b100: Manuel Say data is in rows 1 to 100. Lets say I have names in column A and ages in column B and gender in column C (M or F) and I want to count the number of males older than 15 =sumproduct(--(B1:B100 15),--(C1:C100= M )) Say you want the sum...

Excel: PercentRank in VBA (for "selection"), input box, vba

input box, vba, cells: Can you provide some data and sample results you ve moved up manually so I can see the BEFORE/AFTER? You can attach a picture here, or upload a workbook on the internet somewhere an provide a link. You can also use my profile to access my website and...

Excel: Postcode analysis, sumproduct, pivot table

sumproduct, pivot table, postcodes: Wouldn t a pivot table be easier? Use a formula to split the postcode into the bit before the space (if you have a space) =LEFT(G25,SEARCH( ,G25)) where G25 is the postcode field This gives you the town part of the code, you can then use a pivot table...

Excel: Problems with a data sorting macro, validation data, basic header

validation data, basic header, left header: Paul, instead of using a hard coded range in your code, you will have to make the range dynamic. On way is to search for the upper left header using the find command Dim rng as Range, rng1 as Range, rng2 as Range Set rng = columns(1).Find(What:=...

Excel: pivot table value lookup, array formula, formula index

array formula, formula index, maximum formula: mark twain, Assume cells A5 and B5 contain the ID and maximum value combination that you want to return the B value for. So now you would put a formula in say C5 of the Pivot Table sheet. Assume the data is on a sheet named Master and the Pivot table...

Excel: What is practical appl. for HLOOKUP?, hlookup, appl

hlookup, appl, apps: Hello octester, Thank you for your question. HLOOKUP works the same way that VLOOKUP does, but it looks horizontally, rather than vertically. For expample, if your data is laid out horizontally across several columns, HLOOKUP can look up the value...

Excel: Query in excel, google search, circular reference

google search, circular reference, learning excel: You can do a google search on useful circular reference excel for many examples, but here s one: Suppose there s a premium value which is 10% of the total value, but it s included in the total: Gadgets 500 Widgets 600 Premium 10% of Total ...

Excel: Query in excel, Sixthsense Excel

Sixthsense Excel: Hi Talha, It s surprising to me I am not able to use circular reference . No one would like to evaluate a formula with circular reference. Circular reference is a an error. Circular references are a common error in Excel that can cause problems with...

Excel: Question about bill breakdown in Excel 2007, credit card charges, store2

credit card charges, store2, pivot table: Well, I can see why you d want to have that. You can probably pull this off using formulas, especially if you use a couple of helper columns to do it. Unfortunately, I m very busy this week, so I can t help you set something like that up entirely. ...

Excel: Remove Duplicates Based on Date Criteria, greetings from australia, location number

greetings from australia, location number, kind assistance: Yogi, you example shows your rows being sorted so that the last occurance of a LocNum is in the highest numbered row. Using the assumption that it is sorted like that, then this will remove an duplicates in lower numbered rows. Test this on a copy...

Excel: Remove duplicates from combo box, combo boxes, combo box

combo boxes, combo box, aidan: I think you would need an array filter that was used to return only ONE entry rather than the duplicates, and have this set as the source for the lists - not sure if I have talked you through array filters before - happy to show you on a sample file - my email...

Excel: Replace function, vba macro, cells

vba macro, cells: =if(or(a1= B ,a1= C ,A1= D ), X ,a1) would be the function I d use - this would go into a different column - if it is then to replace the existing values you could copy and then paste special, values over the existing data, then delete the helper column...

Excel: Rolling Numbers in Excel 2003-2007, vbyesno, related event

vbyesno, related event, consecutively: * I m not sure if you re in excel 2003 or excel 2007. Right-click on the name of the worksheet (tab). Click View Code. Now you re in the VB project. Double click on the ThisWorkbook object, near upper left. then copy and paste the code I provided in. ...

Excel: removing bulk amount of text, auction listings, answering the question

auction listings, answering the question, trying to find a way: * Sure, I could do that. I am always trying to drum up a little business on my local (Phoenix) craigslist. I would also prefer to work by project . my email is ipisors@yahoo.com If you can give me as detailed a description as possible of what you...

Excel: removing bulk amount of text, auction listings, ecommerce website

auction listings, ecommerce website, trying to find a way: Sorry I was so long in answering... If you re comparing 2 lists, this formula will find the items on the active sheet which are not in the list on the external workbook (named Lists.xlsx here): array-enter (ctrl/shift/enter) this: =INDEX(A:A,SMALL(IF(ISNA(MATCH($A$1:$A$5,[Lists.xlsx]Sheet1!$A$1:$A$7,0)),ROW($1:$5),...

Excel: removing bulk amount of text, cell a1, auction listings

cell a1, auction listings, test strings: Michael, assume tweaked sheet is [Webbook.xls]Data so it is in a workbook named Webbook.xls on a sheet named Data assume similarly the master list is [Master.xls]SKU Now assume both workbooks are open in the same instance of excel. In the tweaked...

Excel: Scatterplot, conditional formatting, score ranges

conditional formatting, score ranges, score value: A scatterchart is a chart, and charts don t have conditional formatting -but that doesn t mean you can t do it - see http://peltiertech.com/Excel/Charts/format.html#CondChart for some examples and http://www.andypope.info/charts.htm for some more! both of...

Excel: Search Functions > 3D VLOOKUP, vlookup, google

vlookup, google, exit function: Easier with VBA. Enter this function into VBA, then use =Sheet( Name A ,row(A1)) and fill down Function Sheet(what As String, which As Integer) As String Dim n As Integer Application.Volatile True On Error Resume Next For Each sh In...

Excel: Search Functions > 3D VLOOKUP, website references, search functions

website references, search functions, google: Oh, there are 1000s of ways to lookup multiple instances of items on multiple sheets. The problem is there 1000s of ways because everyone s workbooks and lookup needs are different every time. You ve already found my website, use the links at the top of...

Excel: Seeking VB code help for reading a column, snap shot, copy contents

snap shot, copy contents, correct reference: Based on your description, you cn just copy ALL of column A into AG and apply the formula you gave. Nothing changes if you want to do it by unique values or filtering, since the formula will apply the correct reference (AG2, AG3, etc) and get the right answer....

Excel: Sheet2 to Sheet1, thero, column c

thero, column c, b7: On Error Resume Next ColRP=Sheet2.Rows(1).Find( RP ,lookat:=xlwhole).Column ColSD=Sheet2.Rows(1).Find( ShippingDiscount ,lookat:=xlwhole).Column ColTD=Sheet2.Rows(1).Find( TotalDiscount ,lookat:=xlwhole).Column ColSIP=Sheet2.Rows(1).Find( SIP ,lookat:=xlwhole).Column...

Excel: Sort the number of occurences by year, earthquake occurrences, builtin functions

earthquake occurrences, builtin functions, survey table: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. -------------- Interested in online Excel classes led by me? 2 are scheduled: May 26, 7-8PM est, Intro to VBA May...

Excel: Sorting data in ascending order across multiple worksheets, queries in access, ecel

queries in access, ecel, pivot table: This macro seems to do the trick for me. Note that it assumes your sheets with data are called Data1, Data2, data3, ... Each sheet is assumed to contain an equal amount of rows, except for the last sheet. The number of rows can be set in the code (MAXROWS)...

Excel: SumIF when adjacent cell contains a particular letter, kendrick, couple of days

kendrick, couple of days, d1: Kendrick That doesn t seem like too bad an approach. It might make sense in this case to add a helper row that handles the checking for s . If say, row two has the formula =IF(ISERROR(FIND( s ,A1)),0,1) then you would be able to use a simple...

Excel: out of spec icon set, yellow exclamation point, array formulas

yellow exclamation point, array formulas, data validation: It seems like you re wanting to use icon sets for column A depending on values in columns B:D, so it d be relative references to the same row. This isn t possible using icon sets. If it s particular values that don t change per row, like B2:D2 only, then you...

Excel: special search and movie in excell, english question, search method

english question, search method, first language: I m afraid this seems to be somewhat out of my area of expertise, though it may be that I still haven t grasped the task that you are trying to accomplish. It s ALMOST sounding as though you simply want to find all matches on all sheets (at which point, select...

Excel: sub total on each page, balace, cell reference

balace, cell reference, page a4: Sandi Without knowing how your invoice is set up, you can create the first sub total on page one then link the subtotal to the second page by using =cell reference for the subtotal. then add the subtotal to the next set of numbers on the second page using...

Excel: Taking Average and getting #N/A error, vlookup, game1

vlookup, game1, return zero: Assuming you re using Excel 2007, use the IFERROR function to return zero in cases where the result is an error. So using iferror(vlookup(a1,2004 game1!A:B,2,FALSE),0) will return the same result as vlookup(a1,2004 game1!A:B,2,FALSE) except...

Excel: Time card in Excel, time card, column c

time card, column c, richard florida: Helen I don t understand why you need to know 7 hours minus the total hours worked, it seems to me that the calculation would yield the total hours in a normal work day that were not worked, which is not a normal feature in a time card. But assuming...

Excel: Top 10 Text Value Functions, blank cells, cell d1

blank cells, cell d1, vlookup: You say that cell D1 is 10, to indicate a top-10 list. But what if there are less than 10 unique non-blank items in col A? So the 10 you currently have in D1 should be entered in D2 instead. In D1, enter =MIN(D2,MAX(C2:C100)) Now, instead of always...

Excel: if/th, cell a1, contingent

cell a1, contingent, ck: ck It is definitely possible, but you really haven t provided enough information to get much help. First of all, do you really want to copy the information, or would it be okay to just have the information show up? If that is the case, then a simple IF...

Excel: Use a function to determine the Range() value, value question, chicken wings

value question, chicken wings, syntax error: OK, it s clearer now - you had mentioned a function, and VBA so I assumed it was a VBA function. As the commands are actually excel worksheet commands you would need to use worksheetfunction within VBA - but much easier would be to have your formula in a...

Excel: Userform Comboboxes Percent Typing, builtin functions, excel formulas

builtin functions, excel formulas, paypal account: can you send me the userform (or workbook) and I ll have a look... send to bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what needs doing. ================================== Are you interested in online Excel classes led by me? 2 are...

Excel: Using Excel to help with my tax returns, veterinary medications, professional fee

veterinary medications, professional fee, zero rate: I am sure the 2/3 rule is not the only VAT calculation hurdle to jump through. What is VAT for $100 orders that break down in other ways? 2/3 service = 13.5% VAT 2/3 non-oral meds = 21% VAT 2/3 oral meds = 0% Now list the other scenarios will occur,...

Excel: Using formula in Excel file, rephrase, drop down list

rephrase, drop down list, f8: 1 - I have no idea what PN is 2 - ...drop down list entered to cell Card P]N =F8... seems gibberish to me -- no idea what you re saying. 3 - Please rephrase in plain English what you re trying to accomplish and yuo can send a sample to me at bobumlas@yahoo.com,...

Excel: Variable data range, sq ft range, tier table

sq ft range, tier table, variable data: Pricing tables work in tiers. Humans think of tiers as 0-3 and 4-10 and 11-20, but Excel only needs to know the START of each tier since the the start of the next tier clearly indicates the previous tier has ended. So, in your workbook you will create...

Excel: VB macro to find cells containing Page and then delete entire row, blank row, column c

blank row, column c, rng: TJ Sub DeleteRows() Dim rng as Range do Set rng = cells.Find(What:= page , _ After:=Range( A1 ), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext,...

Excel: VBA, blank cells, consecutive rows

blank cells, consecutive rows, a20: You could even stop them saving it! This simple routine counts blank cells in a range Sub tester() Dim counter As Long Dim cell For Each cell In Range( A1:A20 ) If Len(cell.Value) = 0 Then counter = counter + 1 Next MsgBox counter End Sub ...

Excel: VBA, vba code, debugging tools

vba code, debugging tools, title survey: J What you show is the declaration for a subroutine. It is designed to be called from another subroutine since it takes an argument. An example might be Sub Main() Dim questionlist as String questionlist = B RunsQuestions questionlist End Sub...

Excel: VBA, vba excel, vba code

vba excel, vba code, copy windows: Ian Fan Workbooks.Open (strInFile) strInfile = Activeworkbook.name prevously, strInFile would hold a fully qualified file name (includes the path) this command strInfile = Activeworkbook.name will change strInfile to hold just the workbook...

Excel: VBA - Conditional Formatting, formula1, ogilvy

formula1, ogilvy, constants: Daniel, Here is a list of the xlformatConditionalOperator constants which you can use. XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual. -- Regards, Tom Ogilvy...

Excel: VBA Directory Search and Save cont'd, sincere apologies, coding sheet

sincere apologies, coding sheet, cell d5: On my website I already had published a function to MakeDirectories. You feed in a full path string and it goes through the entire string making sure all the folders in the string exist, creating them if needed. The original macro and a sample file to test...

Excel: VBA Directory Search and Save, target folder, coding sheet

target folder, coding sheet, cell a1: Item #2 - You didn t specifically respond, but in the long string we will ALWAYS use the second word as the folder?? ALWAYS?? (With text strings holding names, I am wary of it being that simple.) You have lost me on item #3. Please explain it again...

Excel: VBA: Find row containing text, copy and paste into new sheet or workbook, blank row, best guess

blank row, best guess, cstr: TJ, Sub ABC() Dim sh as worksheet Dim lastrow as Long, i as Long set sh = workbooks( workbookname.xls ).Worksheets( Sheetname ) lastrow = sh.cells(sh.rows.count, A ).End(xlup).row for i = lastrow - 1 to 2 step -1 if cells(i, A ).value cells(i...

Excel: VBA: Find word, calculation application, blank row

calculation application, blank row, word question: TJ, Sub abc() Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, A ).End(xlUp).Row For i = lastrow To 2 Step -1 If Len(Trim(Cells(i, 1))) 0 And Len(Trim(Cells(i - 1, 1))) 0 Then Rows(i).EntireRow.Insert End If Next End Sub...

Excel: VBA? Macro or Formula to Group rows and either border it in groups or shade, vba macro, vlookup

vba macro, vlookup, code description: TJ, It sounds like you want to have one row per entry/Cable code. If you do that then you can vlookup the data. If you want to send me a sample file, I can take a look at it. I would propose using low level file IO to parse the information out of...

Excel: VBA read a record, write a new (conditional on content), 6 cells, cell a1

6 cells, cell a1, rw 1: Uffe, This worked for me if the 0 is in cell A1 on sheet named lessons. you have given two names for the destination sheet named lessonswithduplicates lessonswith duplicates adjust the code to reflect the actual names of your sheets. Sub...

Excel: VBA - replace cell value, up arrow, h10

up arrow, h10, dialog: Select column H (Or H1:H1000). Use Ctrl/F to find Call Me . Assuming you have Excel 2007 or 2010, once you get the dialog with the Find results, press Ctrl/A inside that dialog, then close the dialog. All Call Me s should be selected. Type = (no quote),...

Excel: Visibility, cell a1, target

cell a1, target, shapes: Rightclick the tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Range( A1 ).Value = 0 Then ActiveSheet.Shapes(1).Visible = True ActiveSheet.Shapes(2).Visible = False ElseIf Range( A1 ).Value...

Excel: Vlookup formula copy to another colum, Sixthsense Excel

Sixthsense Excel: Hi Reza, Yes it s happening due to the Column Index Number is higher than the Number of Columns selected (i.e.) Table array is limited. For example =VLOOKUP(F1,A:A,2,FALSE) The above formula will result #REF! error because the Table array is A:A only...

Excel: ven diagram, free microsoft excel, ven diagram

free microsoft excel, ven diagram, excel tips tricks: You can use the MATCH and/or INDEX functions to do this. I just covered this exact example in my Excel Expert Level 4 class: http://www.599cd.com/site/courselist/excel2010/expert/x4?key=AllExperts Let me know if you have any other questions or comments....

Excel: vlookup w/ AUTO column index number, vlookup, column index

vlookup, column index, index number: Joe, as I understand it you have a range of data named Table1 as an example. and you want to do a vlookup in the table and return the say the energy use for building5 (the value in the top row of the table is literally building5 then =vlookup(...

Excel: Weighted averages, sumproduct, counta

sumproduct, counta, weighted averages: Nate, assume the first product is in column B from Row 2 to row 26. the weights in column D from row 2 to row 26. =sumproduct(B$2:B$26,$D$2:$D$26)/Sum($D$2:$D$26) should give you the weigted average. You mulitply the values time their weights...

Excel: Weighted percentage, counta, builtin functions

counta, builtin functions, company names: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. -------------- Interested in online Excel classes led by me? 2 are scheduled: May 26, 7-8PM est, Intro to VBA May...

Excel: Working with pivot tables, pivot table, table options

pivot table, table options, pivot tables: You need to use the function GetPivotData which has many parameters, but if you click inside the pivot table after typing the = and DON T get the GETPIVOTDATA formula, you can turn that feature ON by first selecting any cell in the pivot table, then in the...

Excel: weekly data sheet update, high school football, stat sheet

high school football, stat sheet, macro recorder: Hello Joseph, Thank you for your question. There are a couple of things you can do. You could use a Total column to the right of where the data will end (week 12 or whatever), or if the data is on different sheets, you could have a sheet called Total...

Excel: Zero in Excel Formula, array formulas, format cells

array formulas, format cells, conditional formatting: not sure what help you re looking for, but feel free to send me a copy of the wb and indicate which cell is wrong . send to bobumlas@hotmail.com, use subject of AllExpertsQ . ================================== Are you interested in online Excel classes...

Excel: 1 more question on Excel changing date in cell upon reaching a specific date, martha martha, time periods

martha martha, time periods, auto renewal: Martha, the formula was designed to only work with one year time periods. You date is beyond one year. This formula should handle that situation with all subsequent extensions being one year. this assumes that your original expiration date (12/31/12...

Excel: putting √ and x depending file is present or not, notepad files, files names

notepad files, files names, navin: Navin The following procedure and function should do what you want. I have assumed the file list is in a range named list . I have also assumed the files are in a folder named C: emp . Sub test() Dim R As Range Dim cl As Variant ...

Excel: Advanced list management, array formulas, reverse chronological order

array formulas, reverse chronological order, management question: Sandy, =INDEX($Q$8:$Q$68,MATCH(MAX(IF((MONTH($O$8:$O$68)=MONTH(S21))*($Q$8:$Q$68 ),$O$8:$O$68)),IF((MONTH($O$8:$O$68)=MONTH(S21))*($Q$8:$Q$68 ),$O$8:$O$68),0),1) entered with Ctrl+shift+enter (because it is an array formula) worked for me. (or...

Excel: arrays or formulas, blank cells, countif function

blank cells, countif function, blank rows: I d be inclined to NOT use a formula for this but use a pivot table as this would enable you to analyse everything! BUT you COULD use a decreasing countif function - with lets say the workshop being in cells F2 to F200 =countif(F2:$F$200,f2) This formula...

Excel: Auto Populate Data Validation List, data validation, blank cells

data validation, blank cells, abc def: Gigi, I could see doing this two ways. One way would be to construct the list of dates in another location (on the same sheet or another sheet) and have that location referenced by your data validation dropdown. This would be done using formulas based...

Excel: autofill multiple worksheets, master spreadsheet, cell references

master spreadsheet, cell references, richard florida: Chris When removing the = sign, the fill function advanced to the next cell within the sheet, not the same cell in next sheet I am not sure how the text is telling the formula to advance to the next cell if the cell reference isn t changing in the formula,...

Excel: Backfilling Data, question pool, sheet names

question pool, sheet names, text c: Ty, you didn t ask me this question - I found it in the question pool. But I have to wonder why you want to use a macro. In say C5 I would put the formula =INDIRECT( [B.xls] &TEXT(C$4, d-mmm-yy )& !D7 ) assuming your sheet names in workbook...

Excel: Book recommendations, john walkenbach, pam pam

john walkenbach, pam pam, chip pearson: Pam, you can t go wrong with John Walkenbach s books - he has books for Excel VBA (power programming) and for Excel itself. He has separate books for formulas and one for charts and probably one for something else. Then he has the Excel Bible (which covers...

Excel: Two BoundColumn - (VBA question), sheet1, combobox

sheet1, combobox, mauri: Mauri, you can just access the information from the list property and write it in the click event. Here is sample code that worked for me: note: there was a little confusion in your explanation about where the data is to be written - E1 or E2. I am...

Excel: Creating dynamic 2D charts, 2d charts, dynamic chart

2d charts, dynamic chart, graphs: Define a name like Companies which is =OFFSET($H$13,0,0,COUNTA($H:$H),1) and define a name for the series for 2006 (make up a name) as =OFFSET(Companies,,1) and for 2007 as =OFFSET(Companies,,2), etc. Then use these names in the SERIES formula for...

Excel: calculating bonus, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: This is a job for VLOOKUP. Set up a little lookup table just like you have there and VLOOKUP will take care of the rest. Here s a tutorial that will get you started: http://www.599cd.com/tips/excel/vlookup?key=AllExperts Let me know if you have any...

Excel: CALCULATION OF RETIREMENT AGE, cell b3, retirement age

cell b3, retirement age, microsoft xp: JAGANNATH B S Your formula should look like this =IF(DAY(B3)=1,EOMONTH(B3,719),EOMONTH(B3,720)) As you can see the formula is a combination of the day formula and eomonth formula. the formula is referring to cell B3 which in this instance is the...

Excel: Calling a function in a Sub, john wolford, wshome

john wolford, wshome, best guess: John Wolford, One problem is you have rDestination misspelled in the code in your function. wsDestination.rDesitination Another problem is that even if it is spelled correctly, a construct like wsDestination.rDestination would raise an error...

Excel: Calling protection from Workbook_Activate event, string password, ogilvy

string password, ogilvy, scenarios: Mark SetProtection True don t use run. Private Sub Workbook_Activate() SetProtection True End Sub if you want to use run (and that is slower), then it would be run SetProtection , true the reason you can t see SetProtection in...

Excel: Cell Color, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Tawnie, Thanks for sending your question my way, and the same is possible using VBA. But as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim...

Excel: Cell Protection, cell contents, gibberish

cell contents, gibberish, vbe: Hi Vikas, There is no way to make an individual cell invisible (I assume you mean make the cell CONTENTS invisible). However, you can hide the entire row or the entire column containing the cell, and keep users from unhiding it by password locking...

Excel: change event of a cell containing formula, target address, cell a1

target address, cell a1, ogilvy: shravya, everything you say is true. So there is no built in support for what you want to do. If you want to work around this problem, you would have to look to the cell that is actually changing that causes your formula in the cell that has the formula....

Excel: Changing the column letter as I drag row down, cell a2, curly brackets

cell a2, curly brackets, cell a1: Daniel Try using the OFFSET and ROW functions. =OFFSET($E$1,0,ROW()) Alternatively, you could use the TRANSPOSE function (array function). In A1:A6 put {=TRANSPOSE(F1:K1)} This one needs to be array entered (shift-control-enter). It will add...

Excel: Changing Range Selection in a Macro, string s1, question pool

string s1, question pool, range selection: Ted, you didn t ask me this question - I found it in the question pool. If your still looking for an answer, you can get the selected with ActiveCell in VBA. Then you can use a case statement to make a decision on what to select. Dim sh as worksheet...

Excel: Chart Issue, chart elements, relevant steps

chart elements, relevant steps, mary hope: Hi James, On your unstacked column chart, click the data series to which you want to add a trendline or moving average, or do the following to select the data series from a list of chart elements: Please be aware that if you select a chart that has...

Excel: code works in one workbook but not another, vba code, target cell

vba code, target cell, filesearch: My guess is you put the code in the wrong place. Right-click the sheet tab of the sheet which needs to respond to the change event, select view code, and paste the code there. If that still doesn t work, feel free to email be the wb which is not responding...

Excel: Colourformatting cells, due dates, due date

due dates, due date, cells: Select the cells which should change color. Use Conditional formatting/New Rule and for each rule select Use a formula... Enter this formula =A1 =TODAY()+30 and format the cells as yellow do it again, new rule, enter this formula =A1 TODAY() and...

Excel: combine grouped line shapes, line shapes, tetromino

line shapes, tetromino, straight lines: You can certainly group shapes, but this simply creates a group of shapes, not a new shape - so a group of straight lines has no fill option as a straight line has no fill option - to group them, click one at a time and hold down ctrl whilst doing so- then...

Excel: combining conditional, meros, allexperts

meros, allexperts, coexistence: It sounds as though you basically want a lottery type of program - there are 18564 possible combinations of 18 numbers (using the COMBIN function in excel will tell you this) =COMBIN(18,6) and it would be possible to generate all possible combinations...

Excel: Combining data from multiple workbooks into one worksheet, run time error, run time error 1004

run time error, run time error 1004, credit card payments: Anthony, the problem lies in the fact that workbooks can have 1 million rows or 65535 rows. I have run into this problem even trying to do it manually. Hopefully this will fix the problem. In the offending line qualify the rows.count command ...

Excel: Combining two excel formulas, excel formula, grade test

excel formula, grade test, excel formulas: Cheryl It seems like the formula I gave you should work. The VLOOKUP formulas will just need to reference the grade spreadsheet. Is the grade spreadsheet a different worksheet in the same workbook, or a different workbook? It shouldn t really matter...

Excel: Combining Excel Worksheets, hold down ctrl, file names

hold down ctrl, file names, microsoft office: When a shared workbook is updated by other users, and you want to compare the changes that they have made before you update the workbook with those changes, you can use the Compare and Merge Workbooks command. This command is not available with other shared...

Excel: Compare Two Colums, column c, colums

column c, colums, question thanks: Sorry for misunderstanding - more complicated, but still do-able =IF(MAX(IF(ISERROR(SEARCH(B1,$A$1:$A$3)),0,ROW($A$1:$A$3)))=0, Not Found ,INDIRECT( A &MAX(IF(ISERROR(SEARCH(B1,$A$1:$A$3)),0,ROW($A$1:$A$3))))) entered as an ARRAY formula - so press ctrl...

Excel: Compare text and copy another text based on comparision, Sixthsense Excel

Sixthsense Excel: Hi Trond, Are you using Open Office??? If you are using Ms-Excel then your formula should be like the below =VLOOKUP(E40, N3 !D:H,1) Have a look in the below link to know about referring to other worksheets http://www.homeandlearn.co.uk/me/mes12p2.html...

Excel: compare value in two sets of data and return not matched value, column c, straightforward application

column c, straightforward application, casey: Casey I am not sure what you want; the subject says return not matched data but below it looks like you want matched data. Also, where to you want to return the value? Do you want a column E that shows the value in column D of values in column A that...

Excel: Comparing two nimbers, spreed sheet, excel formula

spreed sheet, excel formula, colums: Rick I really don t understand the real data change you are trying to make. If the reference number were 12.100 as in your example, what would the adjusted 11.800 be? If you changed it to 11.9 it would be within 2% but so would 11.86 and so would 12.000....

Excel: convert string date to value, question column, value question

question column, value question, string values: I assumed since I saw a date like 28/04/2011, that you were using a system with dd/mm/yyyy dates instead of mm/dd/yyyy. So those aren t string dates, they re plain old string values (not dates). Change the macro to Sub Cvt() Columns( A:A ).TextToColumns...

Excel: converting a 2007 VBA script into a 2003 compatible, vba commands, vba script

vba commands, vba script, conditional formatting in excel: Ryan, No, that wasn t the only change. All these lines changed Added: Selection.FormatConditions.Delete Recall I said: I have commented out the incompatible commands. Deleted: Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority...

Excel: copy data from multiple workbook in a single sheet, spath, documents and settings

spath, documents and settings, sname: Jain Varun, This should only copy the header from the first sheet where data is copied. It assumes the header is in row 1 of each sheet from which the data is copied. Sub ABC() Dim sPath As String, sName As String Dim bk As Workbook, r As Range ...

Excel: Copy and paste macro, srow, b cells

srow, b cells, scount: Hi again Derrik, You can move the code to the very beginning of the Sub procedure like this: _________________ Sub Genlist2E(sSheetName As String, mastsheet As String) Dim sCol As Variant the search column sCol = InputBox( Enter mastsheet...

Excel: Create a list of Sheets, with data from the top rows in a new sheet, header row, column headers

header row, column headers, cells: 1) Create a sheet in your workbook called Master List 2) Run this macro Option Explicit Sub CreateHeaderList() Dim ws As Worksheet Dim NR As Long On Error Resume Next Application.ScreenUpdating = False With Sheets( Master List ) .UsedRange.Cells.ClearContents...

Excel: Creating a sequential code from multiple cells, alphanumeric code, cell a2

alphanumeric code, cell a2, cell a1: Hello James, Thank you for your question. I am not for sure I completely understand what you want to accomplish. For example, if your data runs from A1 to A10, are you wanting Cell A1 to be 1A and Cell A2 to be 2BC down to Cell A10 which might be...

Excel: Creating Simple Forms for Invoice Tracking, excel formulas, paypal account

excel formulas, paypal account, submission form: I don t know what you mean by like the form to recognize when a particular code is entered. To click a button implies you need a macro. If you re really a beginner, then describing this process in words is a tough task. Send me a sample workbook and I ll...

Excel: Creating Simple Forms for Invoice Tracking, richard florida, submission form

richard florida, submission form, florida usa: Ben What you want to do is not difficult, but--- There are a couple of issues that are slightly problematic. 1 You state I d like the form to recognize when a particular code is entered--- , but I am not sure what you want excel to do when it recognizes...