answer change, excel application, question thanks: * What specifically do you mean doesn t stop . Do you mean the code keeps running, your Excel application is frozen, and nothing happens, (for how long, a few minutes or what?) ?? OR - Do you mean that it continues putting this formula in cells...

Excel: VBA Macro to do VLOOKUP (Excel 2007), failure value, vba macro

failure value, vba macro, serial numbers: Mich, group of spreadsheets and each one is a specific part number that is pretty ambiguous. In excel you have workbooks. A workbook can have 1 to many worksheets. A worksheet can have rows and columns. You talk about a serial number being in column...

Excel: VBA if then macro for conditional NUMBER format, target, bk

target, bk, response time: Cassaundra, Note that I have edited these macros for the following reason. I thought your variable was c1 - that was the way it looked to me. Now I see it was cl (see ell). I just copied some of your code and edited the C to create r1, but it created...

Excel: VBA questions: copying from a row to a column & "suspending" a macro, template sheet, jane doe

template sheet, jane doe, copy database: Bojan I don t understand what step 1 is doing. As far as continuing the macro after you do some calculations, it seems like it would be just as good to start a new macro, rather than try to suspend and restart your macro. You are going to have to do...

Excel: VBA Sort, example aa, symbol column

example aa, symbol column, number column: Dave, assume the data is in A1:D100 and there are headers in column 1 Range( A1:D100 ).Sort Key1:=Range( A1 ), Order1:=xlAscending, _ Key2:=Range( B1 ), Order2:=xlAscending, Header:=xlYes, _ MatchCase:=False Begin by sorting Column...

Excel: Vlookup & ?, example aa, letter combinations

example aa, letter combinations, sumif: Shaun, assume sheet2 is where you are looking up your data. assume on sheet2 you have your two letter combinations in column A and your numbers in column B then on sheet1 a2: AA b2: =Sumif(Sheet2!A:A,A2,Sheet2!B:B) drag fill down column...

Excel: Vlookup???, wont move, fri

wont move, fri, aidan: I would suggest a redesign of the sheet would be in order – when you enter hard coded values, those values won’t move. You COULD use VBA to shuffle things around, but it seems to me that it would be EASIER to have a master sheet storing the expense items,...

Excel: Vlookup for a range of dates, cell a1, countif

cell a1, countif, day of the year: Since the dates are listed sequentially, you can do the hard work in a prior count cell. In an empty cell on Sheet2, I m using D1, put this formula: =COUNTIF(Sheet1!A:A, = & A1) - COUNTIF(Sheet1!A:A, & A2) Then in A4 put this formula and copy...

Excel: visual basic6.0, excel xls, visual basic6

excel xls, visual basic6, excel application: I wouldn t usually use SHELL for anything, but if you MUST use Shell you will need the full path - I would PREFER to create an object and then interface with that object On Error GoTo notloaded Set myxl = GetObject(, Excel.Application ) notloaded: If...

Excel: Worksheet_Change macro for formula, isnumber, sumproduct

isnumber, sumproduct, b100: Nabam, You say less than 100 and greater than 100, but not what happens when it is equal to 100. I will assume exactly 100 is the same as greater than 100 Private Sub Worksheet_Change(ByVal Target As Range) Dim sformH As String, sformE As String, sH...

Excel: if, blank cells, trail balance

blank cells, trail balance, true range: aezzat, You haven t said where the expenses are located that you want to sum. So I will assume they are in column D. For purposes of example, I will assume you data is in rows 2 to 500. Adjust the formula to reflect the true range where you data is...

Excel: 2 Questions: Need to block a cell & Copy text from other cell., format cells, protection options

format cells, protection options, dear tom: Jamil, each cell has the option of being locked or unlocked. By default, all cells are locked. You can select all the cells you want the user to have access to and right click on one of them, then select format cells. In the protection tab, then select...

Excel: Adding changing value to a time, hr clock, time question

hr clock, time question, hi thanks: 1 - the use of SUM in your example is superfluous -- =F2*1440+I3 is the same as =SUM(F2*1440)+I3. 2 - 47 minutes as a decimal.. what s that... .47? 47/60? precisely what it is F2? 3 - 12 vs 24-hr clock is solely a matter of formatting, not of values, so...

Excel: Adding a sum to an adjacent cell, a3 b3, circular reference

a3 b3, circular reference, referance: Hello Dewi, Sorry I failed to fully understand your question. It sounds like the formula for the running total needs to be in H182. What range is it adding? I will need to know that before I can provide a formula. If you have one number in G182 and...

Excel: Addition of numbers with Text, Excel Sixthsense

Excel Sixthsense: Hi Ali, Always please give more description about your query and your expected result with more examples which will help us in providing exact solution without having more interpretations in our mind. Your example data 3D 4W 12N What is your next...

Excel: Array formula: Daily Index Returns, array formula, index returns

array formula, index returns, vertical format: Steve, how about sending a sample workbook with your data and showing where (what cell) and how you will specify the month you want to process. Also include the formula I gave you for reference. send it to twogilvy@msn.com I have to teach a five...

Excel: Auto Insert Delete Rows in Worksheet, row button, active selection

row button, active selection, command button: 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. It s currently unclear still. -------------- Check out my book, This isn t Excel, it s Magic! on tips & tricks at:...

Excel: Auto sum giving 0 answer, circular reference, richard florida

circular reference, richard florida, few suggestions: Marile I do not know what might be causing this other than the things you have already checked, but I have had the same experience. I have a few suggestions. 1.Check to see that the format for decimal places has not changed i.e. .0001111 formatted as...

Excel: Autofilter Trigger Event, filter criteria, ogilvy

filter criteria, ogilvy, aa: Steve, I tested to be sure, but I didn t find any event that triggered when the criteria was changed in a normal autofilter (as long as the user didn t select the cell with the filter dropdown before trying to change the criteria.) I tested in Excel 2007....

Excel: Automating Data Placement, location column, excel spreadsheet

location column, excel spreadsheet, queston: Dawn I am prolly not totally understanding the queston, but it sounds like you may be able to use a combination IF and FIND or SEARCH formulas to do what you need. For example a formula like this. =IF(ISERROR(FIND( parking lot ,B1,1)), , parking lot...

Excel: Average values of various headings, ivol, aapl

ivol, aapl, intc: Matt, Sounds like you just need to create a pivot table form your data. One of the summary options is average. Then you could create a pivot chart as well or even use a conventional chart from the results. If you don t know how to use a pivot table,...

Excel: average, cell b2, cell b3

cell b2, cell b3, relative address: Bill, in B1 put in the formula =Average($A$1:A1) then select B1 again and there should be a little black square on the lower right of the highlight. double click on that with the mouse and the formula should fill down to the end of your data. Or...

Excel: average if with multiple conditions, column c, c2

column c, c2, automobiles: Right-click the red cell, select Filter by selected cell s color right-click one cell containing Truck from col B, select Filter by selected cell s Value In column C at the bottom (under the last value in C), (let s assume that s C63), enter: =SUBTOTAL(101,C2:C62)...

Excel: averageif with multiple criteria, question column, blank answer

question column, blank answer, column c: John my answer was =Iferror(averageifs(C:C,A:A, Red ,B:B, Truck), ) that is all you need. I don t know why you have combined it with other functions. for those specific ranges it would just be =IFERROR(AVERAGEIFS(c1:c20,a1:a20, red ,b1:b20,...

Excel: barcode scanning for inventory control, barcode scanner, sheet tab

barcode scanner, sheet tab, perpetual inventory: Jaden, I am not familiar with the templates you describe. Nonetheless, perhaps this will work. It expects the value to look up to be in cell A17. That means A17 must be the top left corner of the merged cells. Given that, this code worked for me. Right...

Excel: Calculate school year group based on age, vlookup, year group

vlookup, year group, current date: Hi Gill How are you? Please try the following 0. Create the vertical table in G3:H7 Type 0, 4, 5, 6, 7 ... the Lower limits of each school year s age in G3:G7 Type 0, 1,2,3,4 in H3:H7 1. Type the current date in A1 =Today() 2. Type the birth date...

Excel: Calculating time interval to include blank rows, array formula, blank rows

array formula, blank rows, time interval: Steve, You lost me. there would not be an entry in any of the cells in row 11. (A11 and D11 would be blank) I don t know how you can calculate your rest time if you don t have a start time. You would still be resting. R11: =A11+D11-(A11-1+Lookup(1E+100,$E$1:E10))...

Excel: Capturing data by week, material inventory, pivot table

material inventory, pivot table, best bet: Hi Jack, If you mean summarise the data by week, then a pivot table is your best bet. Click anywhere in your data and (depending on Excel version): 2003: Data, Pivot table and Pivot chart report 2007: Insert, Pivot table (on the far left) Drag...

Excel: Cell Colour Changing, conditional formatting, colour changes

conditional formatting, colour changes, 1 feb: Jill If you only have 3 possible formats, that means you probably are using Excel 2003. Excel 2007 has many more, and lots of new possibilities for conditional formatting. An upgrade will solve that problem; but, be aware that Excel 2007 is very different...

Excel: Co-ordinates- Nearest 3 matches, array formula, closest airports

array formula, closest airports, array formulas: Mark, for the same setup as listed in the example you found, I had data in A1:C20. I put values from one of those rows in E1 (from column B) and F1 (from column C) in G1 I put in this formula =INDEX($A$1:$A$20,MATCH(SMALL(SQRT(($B$1:$B$20-$E1)^2+($C$1:$C$20-$F1)^2),COLUMN(B1)),SQRT(($B$1:$B$20-$E1)^2+($C$1:$C$20-$F1)^2),0),1)...

Excel: Combining rows based on identical columns, jill jack, jack jill

jill jack, jack jill, google: It s always better to start with the actual end goal. Your process is far more steps than needed. I can accomplish this reformat with a few worksheet formulas. This macro will created the reorganized table off to the right of the original data using...

Excel: Compare cells, r2 value, sh2

r2 value, sh2, cell address: KLN, Sub ABC1() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, cell As Range, r2 As Range Set sh1 = Worksheets(1) Set sh2 = Worksheets(2) Set r1 = sh1.UsedRange For Each cell In r1 Set r2 = sh2.Range(cell.Address) If r1.Value r2.Value...

Excel: Compare similar and difference formula, column c, ip range

column c, ip range, ip numbers: You need the inexact formula, based on the 1st 6 characters I mentioned above -- repeated here: =IF(ISNA(MATCH(LEFT(A1,6),LEFT(B$1:B$1000,6),0)), No , Yes ) ctrl+shift+enter or more precise for 1st 7 characters: =IF(ISNA(MATCH(LEFT(A1,7),LEFT(B$1:B$1000,7),0)),...

Excel: And/or Compute, communication question, row numbers

communication question, row numbers, minor adjustments: Monte Leaving out the row numbers for clarity, I believe you need something like this (you may need to make some minor adjustments, but this should show you the syntax =if(and(and(a =b,a =C),or(d=100,e= hundred ))) You have two embedded and conditions...

Excel: Conditional Counting of Colored Cells, colored cells, vba

colored cells, vba, spreadsheet: You are overcomplicating it - at least, I assume you are as you state that the cells are conditionally formatted, and therefore have the value in them - so simply =countif(yourrange, 1 ) will do it - replace yourRange with the range you want to sum,...

Excel: Conditional Formating, format button, home tab

format button, home tab, formating: Trang, select column B with B1 as the active cell in the selection xl2007 and later: on the home tab, select conditional formatting then select New Rule then select Use a formula to determine which cells to format in the textbox that results...

Excel: Conditional Formatting color, hyperlink, blue cells, red jump

blue cells, red jump, cell a2: Hello Andy, Thank you for your question. As I understand your question, I believe you will have to manually create the hyperlinks from each cell to the corresponding cell with the price in it. I do not know of a way to do it with Conditional Formatting....

Excel: Conditional formatting, conditional formatting, what additional information

conditional formatting, what additional information, ogilvy: Alan, I assume you know how to use conditional formatting and you are just looking for a formula to use to test the cell They all have (f)in the text at the end of there name If the last letter in the cell is an F can be tested with =Right($A1,1)=...

Excel: Convert multiple XML files to XLS, office versions, direct email

office versions, direct email, allexperts: that macro SHOULD work correctly in office versions prior to 2007, but in 2007 and later the methods used have been taken out of the program - see http://www.ozgrid.com/forum/showthread.php?t=71409&page=1 for a possible alternative if it helps, my direct...

Excel: Copy to bottom of variable # of rows, formul, variable number

formul, variable number, ogilvy: Norene, Dim lastrowD as Long, lastrowA as long lastrowD = cells(rows.count, D ).end(xlup).row lastrowA = cells(rows.count, A ).end(xlup).row cells(lastrowD, D ).copy Range(cells(lastrowD + 1, D ), Cells(lastrowA, D )) This assumes that in say...

Excel: Copying tabs that reference rows, cell a2, cell a1

cell a2, cell a1, change reference: Yes - requires a bit of code. In the Thisworkbook code, enter this: Private Sub Workbook_SheetActivate(ByVal Sh As Object) Sh.Calculate End Sub In a regular module, enter this: Function ix() As Integer Application.Volatile True ix = ActiveSheet.Index...

Excel: Count of non-specific strings, pivot table, column c

pivot table, column c, autofilter: Martin, It doesn t sound like the count is required - only to know if some combination of attributes is unique. But even if you do want count, I would do this with a pivot table. Select all your data and insert a pivot table. Put your attributes in the...

Excel: Counting data within a date range, exact formula, google

exact formula, google, thanks in advance: Hello Claire, Thank you for your question. To count how many times you have entered Sys Eng in column E and how many times a date, which falls within a range, is in column J, you would need to use the Sumproduct Function. I cannot give you the exact...

Excel: Create Sheet, employee names, excel sheet

employee names, excel sheet, code numbers: Any I you give the range where all your codes are nums and run the code below it should do what you want Sub test() For Each cl In Range( nums ) Worksheets.Add ActiveSheet.Name = cl.Value Next cl End Sub You may prefer another way of...

Excel: Cut & Paste filtered lists, vlookup function, relative reference

vlookup function, relative reference, dollar signs: If I m following this, the actual data is on the same rows in each sheet, but you need to pick out the changed data - if so, easiest way is via a formula =[Book1]Sheet1!A1 where Book1 is the sheet you want to copy from - it s actually easier than this...

Excel: If cell values match then display value from a different cell, cell values, sheet1

cell values, sheet1, c15: Brent You can t show 40 different values in one cell. So your formula doesn t really convey to me what you are trying to do. Here is what I think: Possibly =IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$40,0)), ,INDEX(Sheet2!$C$2:$C$40,MATCH(A1,Sheet2!$A$1:$A$40,0),1))...

Excel: coloring duplicates, conditional formatting, different colors

conditional formatting, different colors, ogilvy: Mercy I can t think of any easy way to do that - at least not by coloring in the cells with different colors (there certainly is no build in support for it). Even if you came up with a way to put unique tags on duplicate values, you would have to enter...

Excel: compare cells, sequential numbering, position number

sequential numbering, position number, b20: For this I would use MATCH(). In B5 put this formula, then copy the cell downward through B20 to get all the answers. =MATCH(A5,$C$2:$N$2,1) What you ve created is called a lookup table. They are very common. Since your table consists of sequential...

Excel: compare cells, n4, dear sir

n4, dear sir, d5: Hi Sahil How are you? The easiest way is to type in 1. the lower bound of each range in C5:N5 2. the numbers 1,2, 3,... 12 in C6:N6 3. Type in B5 =hlookup(A5;$C$5:$N$5;2) But you can try something like int(A5/3)+1 or other function. Cheers...

Excel: compare cells, n4, dear sir

n4, dear sir, d5: sahil This seems like a pretty complicated problem; but, maybe you are not making it as simple as it could be. I don t really understand the pattern, and I don t know what is in cell D4. If you could actually solve the problem by simply comparing the...

Excel: to compare multiple columns in different worksheet, Sixthsense Excel

Sixthsense Excel: Hi KLN, 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: comparing columns on different sheets of workbook in Excel 2007, cell b3, golf questions

cell b3, golf questions, golf teams: Ron Being a golfer myself I always like to help out with golf questions. If I am correctly understanding what you need I believe you can accomplish it with a link to the column in each of the worksheets. For example in a sheet I shall call summary, you...

Excel: comparing two colums, cell b2, colums

cell b2, colums, richard florida: Sasa You can use the countif formula, something like this =COUNTIF(A$1:A$6000,B1) Where B1 holds the number you are looking for and A1 thru A6000 is the range you want to look in. The formula can be copied down and of course will change with relative...

Excel: compound return calculation, exact need, compound return

exact need, compound return, financial calculations: I do not understand EXACT need. There are various formulae in Excel for financial calculations of this type. You will need to pick the right one. Have a look at these links - http://www.vertex42.com/ExcelArticles/financial-functions.html http://www.exceltip.com/st/Formulas_to_calculate_Loan_payments_in_Microsoft_Excel/301.htm...

Excel: conditional formatting, conditional formatting, error indicators

conditional formatting, error indicators, error values: Chris You can add conditional formatting to existing conditions and this should keep your colors or other conditions. Without seeing what other conditions you have though it may take trial and error. Have a look at this link http://office.microsoft.com/en-us/excel-help/hide-error-values-and-error-indicators-in-cells-HP003056121.aspx...

Excel: conditional formatting of color cells in a row, color cells, conditional formatting

color cells, conditional formatting, colored sections: the formula option sounds like the way to go, but it s not too clear from the question how this data is stored. At face value it sounds as though condition 1 is =and(myDate =StartDate1,myDate =EndDate1) condition 2 being the same,but with startDate2...

Excel: conversion of Excel formula to VBA, rc 45, excel formula

rc 45, excel formula, conversion: Excel 2003 doesn t support nested IF s over 7 deep, as you have here, whether it s done directly or through VBA. You can use this user-defined function which will do the same thing. After putting in this code, you can enter this in the appropriate cell: =MyIF()...

Excel: conversion of Excel formula to VBA, Sixthsense Excel

Sixthsense Excel: Hi Nadeem, 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: conversion of Excel formula to VBA, rc 45, excel formula

rc 45, excel formula, conversion: Function sample(targetCell As Range) As String Application.Volatile If targetCell.Offset(0, -60) = Then sample = 01- No SOW received date ElseIf targetCell.Offset(0, -56) = Then sample = 02- MOP not yet submitted ...

Excel: convert decimal number into minutes, cell b1, substitute text

cell b1, substitute text, cell a1: TRY THIS ONE: 1) IN CELL A1 YOU TYPE 38.75 2) IN CELL B1 YOU TYPE =INT(A1)&SUBSTITUTE(TEXT((A1-INT(B1))/100*60, .00 ), . , : ) You will get the result 38:45 I hope this will solve your issue. If you still have any problem then dont hesitate to ask me...

Excel: copy cells automatic, blank cells, cell b1

blank cells, cell b1, a1 index: Sahil in B1 put in the formula =IF(COUNTA($A:$A) =13,IF($A1= , ,$A1),INDEX($A:$A,COUNTA($A:$A)-13+ROW($A1),1)) then drag fill it down to cell B13 It should show the values in the last 13 rows in column A that have data. If you add more data,...

Excel: copy diff range in diff sheets based on cell value, value question, copy source

value question, copy source, z9: Right-click the sheet tab, select View code, put this in: Dim OldValue Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column 3 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If OldValue V1 And Target.Value = ...

Excel: copy diff range in diff sheets based on cell value, copy source, richard florida

copy source, richard florida, source destination: Vilas Sorry to be slow with my reply, I did not see your question. What you want can be done with a macro, I am not expert in macros so I can t help with that, however I believe it can also be done with If formulas which are pretty easy to work with....

Excel: copy diff range in diff sheets based on cell value, Sixthsense Excel

Sixthsense Excel: Hi Vilas Desai, 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...

Excel: copy function not working, calculation options, relative reference

calculation options, relative reference, correct result: Hello Ossi, Glad you got it figured out. The calculation option was not something I even thought about. I have run into situations where that was the problem before, but it is rare. So rare that I don t even think of it being a problem in most cases....

Excel: To copy text from combo boxes to List Box and specified cells, adnan khan, hris software

adnan khan, hris software, software in excel: Sub ADD Listbox.additem combobox1.value & - & textbox1.value & - End sub Is the basic method for adding to a listbox (though this would only exist at runtime, is that what you wanted?) Not clear if the employee qualification worksheet would be...

Excel: copying values from multiple columns and pasting it to single column, a3, a2

a3, a2, cells: Sub CopyCols() Untested for i=1 to range( IV1 ).end(xltoleft).column j=cells(60000,i).end(xlup).row cells(1,i).resize(j).copy Sheet2.Range( A65000 ).end(xlup).offset(1).Pastespecial Next Sheet2.rows(1).Delete Sheet2.Activate nex...

Excel: count multiple occurences, countif, quick reply

countif, quick reply, c application: Sasa so assuming the first number is in A1, then the only modification to my macro would be Sub ABC() Dim rw As Long, r As Range, cell As Range rw = 1 Set r = Range( A1 , Cells(Rows.Count, A ).End(xlUp)) For Each cell In r If Application.CountIf(r,...

Excel: count of shared occurrences within a table, array formula, graveyard shift

array formula, graveyard shift, example names: Patrick in you example worksheet, enter this formula in cell E3 =IF($D3=E$2,0,SUM(IF(ISNUMBER(MATCH(IF($A$2:$A$14=$D3,$B$2:$B$14,MAX($B$2:$B$13)+1),IF($A$2:$A$14=E$2,$B$2:$B$14, ),0)),1,0))) enter it with Ctr+Shift+Enter rather than just enter since...

Excel: count text in multiple sheets, sheet tab, employee id

sheet tab, employee id, absenteeism: Shell, the general form of the equation is =COUNTIF(OFFSET($B$3,MATCH($D20,$B$3:$B$7,0)-1,2,1,7), A ) for a specific month =COUNTIF(OFFSET(Jan11!$B$3,MATCH($D20,Jan11!$B$3:$B$7,0)-1,2,1,7), A ) assuming the sheet tab name is Jan11 I duplicated...

Excel: counting, cell reference, session minutes

cell reference, session minutes, minute session: Grace, this part of the solution =IF(AND(COUNTIFS(B3:$B$45000, 0 ,C3:$C$45000,C2,A3:$A$45000,A2)=0,B2 0),1,0) would be changed to =IF(AND(sumproduct(--($B3:$B$45000 0),--($C3:$C$45000=$C2),--(A3:$A$45000=$A2))=0,$B2 0),1,0) then this part of...

Excel: creat separate sheet, target cells, value application

target cells, value application, ecodes: Anything s possible with VBA :-) Rightclick the sheet tab with the ECode list, select View Code and paste this code (code assumes you are writing the ECodes in column A): Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim...

Excel: creat sheet, vba code, employee names

vba code, employee names, excel sheet: Hi Rup How are you? Please start by trying the following vba code Sub Insertnewsheets() Dim R As Range, s As Worksheet, t As Integer Set R = Sheets(1).Range( A2:A7 ) Set s = Sheets(1) t = 2 For I = 2 To R.Rows.Count + 1 On Error Resume Next...

Excel: create icon on toolbar to launch my macro, custom button, left pane

custom button, left pane, new menu: 1) Right-click the blank part of the menu bar and select CUSTOMIZE 2) Select NEW MENU from the left pane Categories 3) Grab NEW MENU from the right pane Commands and drag it onto the menu bar 4) Right-click the NEW MENU you added and rename it 5) Click...

Excel: Data Validation - month, data validation, richard florida

data validation, richard florida, florida usa: clarkson To set up this data validation make the A1 cell active go to toolbar/data/validation/settings. In the allow box pick custom In the formula box put =Month(A1)=B1 You can then pick your error message etc. Hope this helps Richard Florida...

Excel: Data validation for an evolving list of items, apple orange, blank cells

apple orange, blank cells, data validation: David, You can use data validation using the list option. I have looked at options such as using a pull-down menu while allowing the user to dynamically add to the list tied to the pull-down menu. so yes, that is the approach you should pursue...

Excel: Date dependent color coding, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, generic rules: The conditional formatting rules for dates are not very customizable. You have options like last week or next month but that s about it. If you want to go beyond what the standard conditional formatting rules can do, you ll need to write yourself some...

Excel: Dates in VBA, spreadsheet formulae, type mismatch error

spreadsheet formulae, type mismatch error, mm yy: Terry, open a new workbook and run this code on that workbook Sub ABC() Dim i As Long, dt As Date Range( A:A ).ClearContents For i = 1 To 30 With Cells(i, A ) .Value = Date - 30 + i .NumberFormat = dd/mm/yy End With Next dt...

Excel: Decending order, array formula, error values

array formula, error values, d10: It doesn t come over very clearly in the text, but it does look as though you have errors in the data - if so, it may be difficult to sort in descending order - IF the errors can be eliminated, then the LARGE worksheet function would do what you want =large(DataRange,1)...

Excel: Define folder parth in VBA, whttp, cell a1

whttp, cell a1, desktop sales: Dim strFileLocation as String Dim strFolder as String, strCharacter as String Dim iCharacter as Integer strFileLocation = thisWorkbook.Sheets( Sheet1 ).Range( A1 ) strFolder = strFileLocation Do Until Right strFolder = strFolder = left(strFolder,...

Excel: Defining Listbox, x cells, horizontal range

x cells, horizontal range, z9: Zuzzu I don t believe you can use a horizontal range as a list rowsource for a listbox. I think you will have to fill the listbox dynamically through a loop that runs when the file is opened. I don t really understand where your list box is located, but,...

Excel: Delete the rows containing L in Column O, ogilvy, o1

ogilvy, o1, headings: JN test this on a copy of your worksheet until you are sure it works as you want. Sub deleterows() Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, O ).End(xlUp).Row For i = lastrow To 2 Step -1 If Len(Trim(Cells(i, O ).Value))...

Excel: Delete using VB, row button, row selection

row button, row selection, auto insert: This macro should do the trick: Private Sub CommandButton1_Click() Dim lCt As Long Dim oRng As Range ActiveSheet.Unprotect Set oRng = Selection For lCt = oRng.Rows.Count To 1 Step -1 If oRng.Rows(lCt).EntireRow.Cells(1,...

Excel: Deleting rows in the end of worksheet, collumns, lookat

collumns, lookat, cells: There are two methods - the first is quick and dirty, which is to create a new worksheet, copy the data you want onto it, delete the original sheet and rename the new one. The other method I m aware of is via a macro Sub OptimizeWorkbook() Dim ws As Worksheet...

Excel: Display a value based on data validation list selection, currency conversion, currency type

currency conversion, currency type, euro conversion: MJ C3: =if(B21= , ,HLOOKUP(B21,$C$7:$K$9,3,FALSE)) see the picture to see it in action: I filled my list box with the data in C7:K7 so I know there will be an exact match and I won t have any problems. Othewise, clean up your data so you know you...

Excel: Dollars and Cents, format cells, manual selection

format cells, manual selection, home tab: Jacqueline I am sorry I don t remember how to help you step by step through Excel 2002. I don t have access to anything earlier than 2007. The menus are so much different now and I don t remember how they looked. I think you need a basic book on Excel,...

Excel: data manipulation, data manipulation, sorry david

data manipulation, sorry david, cell a1: Hello Art, Thank you for the additional explanation in the follow-up. This is a difficult task to accomplish. It can be done without VBA, but a VBA solution will be easier. You can put a formula in C1 that will concatenate the cells with the 1 ...

Excel: date formatting, problem occuring, mm yy

problem occuring, mm yy, data validation: Setting the cell format to mm/dd/yyyy will give you what you want, but I come back to my original point - I would have the cells already formatted in the format you want AND would use data validation to ONLY allow a date to be entered - users would then not...

Excel: deleting a portion of all cells, rng, instr

rng, instr, delimiter: Assuming the column to evaluate is column A, this macro will put the last value in the cell using the | delimiter to split the data. Any cells without a | in it will not be touched. ========== Option Explicit Sub KeepLastValue() Dim RNG As Range...

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: Efficient manipulation of hyperlinks while updating a Master spreadsheet, master spreadsheet, ogilvy

master spreadsheet, ogilvy, hyperlink: David, you should use workbooks open. then you have control of the workbook. If you are using workbook open you are not executing the hyperlink - so your statement concerning that is incorrect. FollowHyperlink executes the hyperlink. Using workbook.open...

Excel: EXCEL FORMULA - NEED HELP!!, cell a1, result column

cell a1, result column, excel formula: Tom A formula such as this will work. =IF(A1 0, CR , DR ) The formula says If cell A1 is greater than zero, then put CR in the formula cell, if it is not greater than zero then put DR in the cell. Of course this assumes that column A is your balance...

Excel: EXEL FORMULAS, exel formulas, language version

exel formulas, language version, avg: Hi Jessica, Please do not type in all caps, it is hard to read and on the internet, writing in all caps is considered as shouting. What language version of Excel do you have? Since the formula syntax is correct, all I can think of is that you are not...

Excel: Excel 2003 vba code re: start date and 3 yr alert, vba code, conditional formatting

vba code, conditional formatting, initial check: You mention your initial VBA code but you didn t show me any code. Conditional formatting doesn t Alert , per se, but it can highlight. Also not clear on your conditions -- if a person has a date of 1/1/11, say, you want it to highlight ONLY on 1/1/14,...

Excel: Excel 2007 calculation bug

AQUA, let me answer your question with a question? If you divide 1 by 3 on your calculator, is the answer exactly 1/3. No it isn t but you don t want to take your calculator back to the store. all decimal/fractional numbers can not be represented...

Excel: Excel 2007 - How to compare differents details easy way, variance, airplane

variance, airplane: I need more details -- better would be for you to email me the workbook -- send to bobumlas@hotmail.com, use subject of AllExpertsQ and redescribe the issue. But I won t be able to get you an answer for at least 2 days -- away at a conference and will be...

Excel: Excel 2007 IF statement help, ogilvy, multiplier

ogilvy, multiplier, n2: Ken, I assume you want to use quantity as a multiplier of the payout amount. M2: =if(AND(D2 =64005150,D2 =64005154),5*J1,0) N2: =if(AND(D2 =64005155,D2 =64005157),10*J1,0) O2: =if(AND(D2 =64005160,D2 =64005162),15*J1,0) Then drag fill down the...

Excel: Excel, cheers, cliente

cheers, cliente: Hi Annette How are you? Yes, you can do this, but in order tio help you I need to knw which data tells a old cliente from a new one. Based on this data we can slipt the 2 lists programmatically or we can use the filters from Excel to display the recors...

Excel: Excel, database program, barcodes

database program, barcodes, 599cd: You could just scan it once into column A, and then copy and paste it down. I can t really think of a good NON-PROGRAMMING way to do this in Excel. You could easily do it in a database program (like Access) by using a form and subform. Cordially, ...

Excel: Excel, question values, null value

question values, null value, whole numbers: Barry, Do you want to treat fl as equivalent to Null? the rules are the same except fl is equivalent to NULL then =IF(AND(L1 ,L1 fl ),L1,IF(AND(R1 ,R1 fl ),R1, fl &TEXT(TRUNC(RAND()*999999+1), 000000 ))) -- Regards, Tom Ogilvy ...

Excel: Excel 7 ignore empty cell, excel 7, ogilvy

excel 7, ogilvy, b4: Bill, your table is all jumbled together so it shows me nothing. So what do you want to show if B4 is empty or blank? I will assume you want the formula cell to appear blank: =if(B4= , ,32-B4) would be my suggestion. Change the to whatever...

Excel: Excel Conditional Formatting (Date Format), cell b1, richard florida

cell b1, richard florida, microsoft excel: Tovey You can do the conditional formatting in cell B1 with the condition being Cell value is Greater than A1+10. Excel will compare the date in B1 to the date in A1 plus 10 days, and if the date in B1 is greater than A1 plus 10 days, then your selected...

Excel: Excel Corruption, stranger characters, ascii characters

stranger characters, ascii characters, quick reply: Hello Deepu, It sounds like the data imported into Excel was corrupted. You may send the file to me at dwcofer@gmail.com. If it is password protected, please provide the password, or remove the password protection. I will look at it and see if I can...

Excel: Excel Date help, b2, a2

b2, a2, d2: Hi Vishalkumar How are you? Please try the following 1. Type date 1 in A2 and date 2 in B2 2. Type =A2-B2 in C2 3. Type in D2 (number of days) =INT(C2) 4. Type in E2 (number of hours) =INT((C2-INT(C2))*24) 5. Type in F2 (number of minutes) =(C2*24*60)-(D2*24+E2)*60...

Excel: Excel Date help, datedif function

datedif function: Hello Vishalkumar, Thank you for your question. Unfortunately, I am not aware of a way to do this with a formula. I use the DATEDIF function a lot, so I am familiar with it. But to have the result displayed in days and minutes will require a VBA solution....

Excel: Excel Formula, excel formulae, array formula

excel formulae, array formula, excel formula: Hi Usman, 1. It does not look like a array formula. Array formula starts with {. May be you did not put it here correctly. The { comes automatically when you enter array formula with SHIFT+CTRL+ENTER. 2. Wild characters like * do not work in excel formulae....

Excel: Excel Formula, excel sumproduct, excel formula

excel sumproduct, excel formula, conduct performance: Gill, in Excel 2007 and later =COUNTIFS($B$1:$B$50, red ,$C$1:$C$50, Attendance ) (note this is COUNTIFS with an S, not Countif) I assume column C contains the classification (you said B, but you also said B held the rating) in any version of...

Excel: Excel formula, code column, excel formula

code column, excel formula, vs1: Hello Annie, Thank you for your question and the opportunity to answer it. You should be able to use the SUMIF Function to summarize the profit for a given Code and month. I copied and pasted your data into a spreadsheet and Column B was the Code,...

Excel: Excel formula syntax using AND and OR in the logical, cell b1, cell d1

cell b1, cell d1, correct syntax: Sherra, You really haven t told me the conditions you are trying to check and what you want to do if and if not the conditions are met, but here is a guess =if(And(or(B1= A ,B1= P ),D1= ), X , ) so if (B1 = A or B1 = P) and D1= then display...

Excel: Excel help, workbook sheets, drep

workbook sheets, drep, question sir: The master workbook should have one sheet for each of the other workbooks sheets. For each sheet in the master workbook, you need a formula which points to the corresponding sheet s same cell. Remove the $ if any. Sample: = [Cash Book]Sheet1 !A1 This...

Excel: Excel Lookup: Find Specific Column with Multiple Row matches, excel lookup, option1

excel lookup, option1, text import: Kristen, the offset command has these parameters Offset(base cell,offset n rows, offset n columns, number of rows, number of columns) in the sample formula, since I didn t know the number of Option columns I chose 10 if the correct number is 2,...

Excel: Excel Mail Merge, data source, word document

data source, word document, january 1: The format of the date in excel actually has no bearing on the mail merge – being in the UK this can often be a real problem as Word will TRY to format the date as US format if it possibly can – the solution is to use a formatting switch- easiest to use tools,...

Excel: Excel Number Display, custom option, regional settings

custom option, regional settings, negative numbers: The options come (I think) from regional settings BUT this is not important - simply use the CUSTOM option to enter the format you want - for example £#,##0;(£#,##0) would display values as whole pounds without decimals, with commas and with negative...

Excel: Excel question, legible format, relative reference

legible format, relative reference, column numbers: the picture doesn t come through in a very legible format - probably my eyesight - MIGHT work if you change =[Book1]Sheet1!$A$4 to =[Book1]Sheet1!A4 which is a relative reference so SHOULD change as you move around - but if this isn t enough information...

Excel: Excel requirement, consecutive columns, data columns

consecutive columns, data columns, manager x: Arthur, this assumes columns D:F are available to build the split data. Columns A:C are then deleted putting the results in columns A:C as you show. There are other ways to do it if that is a problem. Assumes the data to be processed starts in cell A1....

Excel: Excel usage for end users, simple answer, work thanks

simple answer, work thanks, angel resendiz: Angel Resendiz, the simple answer is no. There is no way to just take a workbook or the code in a workbook and compile it to an exe. That said, you can rewrite your code into a language that can be compiled and learn how you can write that code to manipulate...

Excel: Excel VBA - Counting Worksheets, vba module, many thanks

vba module, many thanks, boolean: Mairead I you put this in the BeforePrint event Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet.PageSetup .CenterFooter = ActiveSheet.Index & of & Sheets.Count End With End Sub it should do what...

Excel: Expenses workbooks summarize, cell a2, sum formula

cell a2, sum formula, sheet1: Roberto, use a linking formula like these = C:myfoldersMy Reports2010[Jan.xls]Sheet1 !E22+ C:myfoldersMy Reports2010[Feb.xls]Sheet1 !E22+ C:myfoldersMy Reports2010[Mar.xls]Sheet1 !E22 and continue until you get to December. change...

Excel: Extract data from a text string, aafl, finance application

aafl, finance application, best bet: simon I hope you can get the export from you finance application modified to give you more usable data. If not, you appear to have a mess. There might be some rules we can apply, but, I need to know more about the task and the options. Can we get rid...

Excel: How to eclude Sundays and holidays from calender, custom vba function, year calender

custom vba function, year calender, 599cd: First, see this tutorial: http://www.599cd.com/tips/excel/workdays/?key=AllExperts Now, ignore the section in that tutorial about the custom VBA function. There is a new function called NETWORKDAYS.INTL in Excel 2010 which has an option in it to specify...

Excel: excel 2007, label bookmark, e56

label bookmark, e56, adva: pravin makwana, =HYPERLINK( C:Datacox_adva_profitablity.xls#Sheet1!F9 , go to other book) or right click on the cell and choose hyperlink choose existing file or we link. Navigate to your workbook. select the workbook. Now click the button with...

Excel: excel 2007 concatenate within a vlookup?, blank field, concatenate

blank field, concatenate, usage stats: Simon, You can take advantage of the Subtotal worksheet function. It can detect visible data in a worksheet. I have attached a picture to show my approach. I have a table of 23 rows and a header rows starting in cell A1 of a worksheet. the table is 3...

Excel: excel, consecutive columns, lmax

consecutive columns, lmax, lct: This is hard to do without some VBA, so: - open your file - Open the VBA editor (alt+f11) - insert a module (insert, module) - paste this code: Function LongestSet(Range) Dim oCell As Range Dim lCt As Long Dim lMax As Long For...

Excel: excel, range f1, columnar data

range f1, columnar data, google: At the error, click on debug then hover the mouse over the line of code highlighted and tell me what the value of Rw is. Also, press CTRL-G to open the Immediate Window and put in this command, tell me the result: ? delRNG.Address If you couldn t...

Excel: excel drop down list, excel drop down list, data validation

excel drop down list, data validation, format menu: drop down list would be data, validation, list - but you already have that. For auto-complete, see the article at http://www.ozgrid.com/Excel/autocomplete-validation.htm. As to red and green boxes, the easiest way is with conditional formatting (format menu...

Excel: excel issue, excel sheet, tabs

excel sheet, tabs: Hello Rachel, Thank you for your question and the opportunity to answer it. It sounds like you have the worksheets Grouped together. When Grouped, data entered into a cell on one sheet will automatically be entered into the corresponding cell on the...

Excel: excel macro, blank cells, excel macro

blank cells, excel macro, cell values: Sub RoundCells() Dim thisCell As Range Dim oldString As String For Each thisCell In Selection.Cells oldString = thisCell.Formula If (oldString ) And IsNumeric(thisCell) Then If Left(oldString, 1) = = Then oldString...

Excel: excel macro, cell a2, empty cells

cell a2, empty cells, cell b1: Open a new workbook in Excel 2007. In cell A1, enter DATE. In B1, enter VALUE. Starting in cell A2 and down, enter dates. In the corresponding cells in col B, either enter a value, or leave blank. Select a cell within this table. From the Data ribbon, click...

Excel: excel problem, office icon, exact problem

office icon, exact problem, excel sheet: Click on Office Button (Round Button with Office Icon - On your left Top Corner) Click on Excel Options button towards bottom right. Choose Advanced option on left half. In Editing Options second option from top is Automatically Insert a decimal...

Excel: excel problem, data links, cheers

data links, cheers, a1: Hi Sharad How are you? This is just for starting 1. Open the two workbooks (First and Second) 2. Type in A1 of First =[Second.xlsx]Folha1!$E$8 3. Save both workbooks 4. Select First Select Data - Links in First and activate the properties you want...

Excel: Putting expiration date in spreadsheet without using Windows Rights Management, sheet tab, time usage

sheet tab, time usage, msgbox: Right-click a Sheet tab, select View Code. Double-click the ThisWorkbook that you ll see in the project window. and put this code in -- this is set to expire on April 5th: Private Sub Workbook_Open() If Date #4/5/2011# Then MsgBox Trial...

Excel: Filter based on cell value, filter criteria, header value

filter criteria, header value, a10: Jim, I guess I don t understand the question. select your data (starting with the header in A10) and apply a filter. specify the criteria of less than the date you want to filter on. (I read filter out to mean don t show dates greater than Today() +...

Excel: Filter text in Excel - Fariya Enterprises, fariya, filter text

fariya, filter text, space tab: Hello Fariya Enterprises, Thank you for your question and the opportunity to answer it. Yes, you can import text from notepad. Click on the Data tab, then either click on Get External Data or From Text, depending upon which version you are using. An...

Excel: Filtering buttons on only certain columns, filter buttons, autofilter

filter buttons, autofilter, filter data: Scott, and that is what I told you how to do. However, when using an autofilter with data on a sheet, you can t filter just B through U. You can filter A:IV or A through whatever the last column is in Excel 2007 and later - but you CAN do that with just...

Excel: How to Find, Copy, & Paste, c cells, srow

c cells, srow, b cells: Here s some sample code. It goes down each row of data sheet (from row 1 to the first blank row). It copies A:K of the row to the DestSheet multiple times, determined by the value in col M of the row on data sheet. Understand this code, and you ll know...

Excel: Find and Replace, asterics, consecutive rows

asterics, consecutive rows, richard florida: Kaiwalya Here is a simple way that I think will work for you. First use the text to columns feature to separate the sentence into separate cells. To do this select the sentence then go to toolbar/data/text to columns. Select delimited and put an asterisk...

Excel: Finding cell content in another workbook, cell1, bk2

cell1, bk2, blank cell: Vikram, Sub ABC() Dim sh1 as worksheet, bk2 as workbook, sh as Worksheet Dim cell1 as Range, r1 as Range Set sh1 = workbooks( Book1.xls ).Worksheets( Sheet1 ) set bk2 = Workbooks( Book2.xls ) set r1 = sh1.Range( A1 ,sh1.cells(sh1.rows.count, A ).End(xlup))...

Excel: Finding all records using a name of a person in excel, juan dela cruz, note style

juan dela cruz, note style, custom filter: It s unclear exactly what you re asking for. The format of the data itself is critical to not be note style data, it must be clear names, categories, one date in a cell, amounts...no text strings where dates or amounts should be. To see ONLY a specific...

Excel: Finding more than One Value, array formula, value question

array formula, value question, home tab: Rajiv, Assume you data is in A2:B11 in another location =INDEX($B:$B,SMALL(IF($A$2:$A$21= A ,ROW($A$2:$A$11)),ROW(A1)),1) enter this with Ctrl+shift+enter rather than just hitting the enter key since this is an array formula. Once entered, drag...

Excel: Follow up to VBA Search, search criterion, search question

search criterion, search question, ubound: Brian, the test search is mostly just code copied from the original macro. I have moved a couple of commands in the original so it mimics the test routine Sub myFind() Standard module code, like: Module1. Find my data and list found rows in report!...

Excel: How are "Too Many Formats" Determined, horizontal alignment, service pack 3

horizontal alignment, service pack 3, background explanation: I don t get to see your email address - though if it helps, mine is aidan.heritage@virgin.net The Microsoft KB article is http://support.microsoft.com/kb/213904 which explains the limitations - I would NORMALLY not expect to run up against this issue (Indeed,...

Excel: Formula, sheet tab, richard florida

sheet tab, richard florida, data sheet: JENNIFER I cannot read the image that you included with your question so I can t be specific with the answer. But in general it sounds like you can use a simple if formula to bring over the data using the criteria yes . something like this =IF(Sheet2!$A1=...

Excel: Formula HELP!!!!!, reference column, code column

reference column, code column, column c: Jessica I think you left out some information. What do you mean by reference column A? How do you know which ones need to be ordered? What is column G that needs to be referenced of something needs to be ordered? It seems like what you are probably...

Excel: If Formula Problem, pat answer, professional survey

pat answer, professional survey, question survey: Pat, My interpretation is that if C193 and C194 both equal zero, then make the cell C195 appear blank. =IF(AND(C193=0,C194=0), ,IF(C199 ,IF(ISNUMBER(C199),IF(AND($G$4 C199,$G$4 0,($G$4+C199) 0),(C193+C194)/($G$4-C199), ), ),IF(AND(ISNUMBER($G$4),$G$4...

Excel: convert decimal number into minutes, purpose text, mm ss

Excel: convert decimal number into minutes, purpose text, mm ss

Excel: date formatting, voila

Excel: date formatting, voila

Excel: Image links in Excel, left pic, expert advice

Excel: Image links in Excel, left pic, expert advice, shapes

Excel: Large(IF()) = #NUM! Result - Excel '07, array formula

Excel: Large(IF()) = #NUM! Result - Excel '07, array formula

Excel: Macro to scan string, cell a1, first instance

Excel: Macro to scan string, cell a1, first instance, text search

Excel: Nested IF Formula, brackets

Excel: Nested IF Formula, brackets

Excel: Overtime calculation

Excel: Overtime calculation

Excel: Protecting Formulas in Excel 2010

Excel: Protecting Formulas in Excel 2010

Excel: finding change in a price list, array formula, precise instructions

array formula, precise instructions, pivot table: the minimum price is easy enough - if you want to see all of them, I would use a pivot table, with the output column being set to show minimum value. You didn t specify version of excel, so difficult to give precise instructions for creating the table. I...

Excel: formula, sumproduct, e35

sumproduct, e35, formula 3: Formula # 1 should have answer of 1 from cell D8 Row containing (1) and =160000 =SUMPRODUCT(--($A$5:$A$35= *(1)* ), --($E$5:$E$35 =16000)) Formula # 2 should have answer of 1 from cell D3 Row containing (1) and between range of 160000 =180000...

Excel: formulas, array formulas, index function

array formulas, index function, row numbers: Sabrina, the information is very general so I can t give you a specific example, but I can explain how you can approach the problem can write the formula specific to your actual setup and locations. what you would need to use to do this would be array...

Excel: further to earlier question "deleting rows with Vb", many thanks, vb

many thanks, vb: Private Sub CommandButton1_Click() Activesheet.Unprotect If activecell.offset(1).Value= TOTAL: then If Activecell.Value= then Rows(Activecell.Row).Delete EndIf ActiveSheet.Protect AllowFiltering:=True, AllowFormattingColumns:=True,...

Excel: Generating dates using Excel 2003, header row, column c

header row, column c, feb 2: Helen Make a table like this: 3 5 8 10 1 6 8 13 15 2 6 8 13 15 3 6 8 13 15 4 6 8 13 15 5 4 8 11 15 6 5 8 11 15 7 6 8 12 15 The numbers 1-7 down the left side are the weekday numbers; the numbers across the top are the number of days earlier...

Excel: Generating dates using Excel 2003, weekdays, spreadsheet

weekdays, spreadsheet, l1: Not trivial. You probably need a table of the dates in a year (or 2) which consist only of weekdays. in cell L1, for example, put 1/1/2011, right-click the fill handle and drag down to L261. When you let go, you ll be presented with a menu to fill weekdays....

Excel: Group Boxes for Option Buttons, group boxes, option buttons

group boxes, option buttons, excel forms: Ron, the lines of a group box on not configurable. If you don t want to have a square around your option buttons, use the option buttons from the control toolbox toolbar (xl97 to xl2003). In Excel 2007 and later, these are call activeX controls. ...

Excel: grouping info from several files to one file, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: First, I would suggest you switch over to a database program like Microsoft Access which is MUCH better at tracking inventory. However, if you want to use Excel then just put each item in a separate sheet. I would recommend keeping your running total in...

Excel: Hello, Sixthsense

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

Excel: Help with Excel Formula, cell d5, spreadsheet system

cell d5, spreadsheet system, bonus payment: Julie h5: =if(D5 ,vlookup($D5,$J$5:$J$58,2,False),0) then drag fill H5 down to the last filled cell in column D. I guessed that column K holds an amount of bonus to be added. If it is a multiplier/rate, you would change the 0 in the formula to...

Excel: Help!! Excel Worksheets - Extracting Data from Several Worksheets to 1 New Worksheet, cell c1, example sheets

cell c1, example sheets, cell d1: The formula expects the list on the left with your agenda items to be in the exact same order as the columns are on each of the Group sheets. You can add as many columns as you want on those sheets, but make sure you match that column A list on the first...

Excel: Help!! Excel Worksheets - Extracting Data from Several Worksheets to 1 New Worksheet, cell c1, example sheets

cell c1, example sheets, cell d1: Mock all of this up, then send me workbook so I can see some good sample data and a sample SUMMARY based on that sample data. That should make it easy to help. You send to me through this forum/my profile, or use the contact info found on my Excel code...

Excel: Help please!, number comments, excel spreadsheet

number comments, excel spreadsheet, invoice number: If you know how to create a user-defined function with vba, then create this function: Function unhiddenSum(withinRange As Range) sums all cells in withinRange, except excludes any cells in hidden rows Application.Volatile Dim sumCell As Range...

Excel: Hide Rows with cell value of -0-, sheet1, initial question

sheet1, initial question, absolute value: Jim Initial question: I would like to hide all rows in sheet2 (Sheet2 is a duplicate of Sheet 1, but is used for printing) followup question: the duplicate sheet is Sheet9. I didn t have any problem with the macro. It is working fine for...

Excel: Hyperlink in Data Validation, data validation, vlookup

data validation, vlookup, link details: I m not that good on hyperlinks, but I think that IF the dropdown list itself is to be a hyperlink it would need to have VBA to set the hyperlink - if the cell NEXT to it could be a hyperlink instead then it could use the HYPERLINK worksheet function together...

Excel: How to put two horizontal splits in Excel 2003, Sixthsense Excel

Sixthsense Excel: Hi Jennifer, Sorry for providing the suggestion without testing the same. Now i have given a solution which you may find it useful or may not. But have a look in the created file. I have created a sample file for you, Click or Copy and paste the below...

Excel: Ignoring Zeros-Excel XNPV, blank cells, real estate lease

blank cells, real estate lease, blank cell: Greg, (I have zeros set to be blank instead of $0) since zero doesn t cause a #Num, assume that the cells either have a number or they are blank. The numbers are contiguous and stop when a blank cell is encountered. =XNPV(8%,OFFSET($B$7,0,0,COUNT($B$7:$B$366),1),OFFSET($A$7,0,0,COUNT($B$7:$B$366),1))...

Excel: Image links in Excel, image names, file names

image names, file names, spreadsheet: Filenames in column A starting at A2 Path in B1 ============== Option Explicit Sub ImportPics() Dim Pic As Range Dim Pics As Range Dim picPath As String With ActiveSheet .Shapes.SelectAll Selection.Delete picPath = .[B1] ...

Excel: Import files, sday, spath

sday, spath, sname: Jn, this assumes that all the files in the specified directory are processed. put in the correct path and change the name of the sheet you want to process. It puts the information in the sheet that is active when you run the macro. Sub ConsolidateFiles()...

Excel: Insert Data into differet cells, blank cell, macro code

blank cell, macro code, do loop: The layout looks rather difficult as you appear to have a series of blocks of data going both across and down the sheet - so if the macro needs to determine the first blank cell in the block it would need to check a whole series of cells - this could be done...

Excel: ISERROR, conditional formatting, cell a1

conditional formatting, cell a1, format font: Conditional formatting would work. Highlight all the cells. Let s assume cell A1 is the primary selected cell and more cells are selected going down and to the right. Open the conditional formatting window and use this: Formula Is: =ISERROR(A1) Format...Font...select...

Excel: inserting images in excel 2007, Microsoft Excel 2007, sizing graphics

Microsoft Excel 2007, sizing graphics, sizing cells: Hi Silu, Microsoft Excel does efficiently perform this task. In order to have the picture cell correctly sized, all other cells in the row will need to be the same height. Don t know if that will be a problem. If not, the easiest way to do this: 1. insert...

Excel: a Kind of double v look up, jnb, nbo

jnb, nbo, m system: Rakesh Typo in the original formula corrected below M2: =IF(OR($K2= ,$L2= ), ,SUMPRODUCT(--($A$1:$A$7500=$K2),--($B$1:$B$7500=$L2),$C$1:$C$7500)) you can then drag fill the formula down column M if you will have more entries in columns K and L....

Excel: Large(IF()) = #NUM! Result - Excel '07, dear tom, a21

dear tom, a21, ogilvy: Steven, I get #Num if I2 contains a number larger than the total quantity of numbers returned by the IF statement. I get #Num if E1:E21 contains all non-numeric content for rows in column A that have a value that matches the value in cell H2. I2 needs...

Excel: LEN & SUBSTITUTE function, d4, question thanks

d4, question thanks, d8: =IF(LEN(D4,1)= - ),D4+D5,D4-D5)& Nos and =C4-C3)& Nos both give me errors because you have unmatched parentheses Len(D4,2) give an error because length doesn t take to arguments so your statement that they work fine is a bit confusing to...

Excel: Limit number of Words in a form, data validation, question thanks

data validation, question thanks, allexperts: It s always difficult on AllExperts to work out what level of knowledge the questioner has, and clearly I guessed wrong here -for which my apologies! Under the data validation, it is indeed custom. Assuming for explanation s sake that the cell this applies...

Excel: Link listbox selection to checkboxes?, cell c3, e mail

cell c3, e mail, checkbox: Barbara, You can do it without a macro. You would use the Linked Cell property to tie each control to a cell. In your case, that cell would be underneath your controls so they would not be visible. You could further make the font white. (I placed them...

Excel: Look up value, preview results, column address

preview results, column address, a340: it can be done using a macro, it s basically Sub output() Dim outvar As Long outvar = 2 For Each cell In Range( A:A ) If cell.Value = Range( a340 ).Value Then Sheets( Sheet2 ).Cells(outvar, 1).Value = cell.Value Sheets( Sheet2 ).Cells(outvar...

Excel: Looking an option in Data Validation to highlight if wrong code typed., conditional formatting, data validation

conditional formatting, data validation, format cells: Jamil A.Z. this would be done using conditional formatting - not data validation. You can have your list in a different sheet if you create a defined name/named range to refer to it. You didn t say what version of excel you have and the location...

Excel: Looping and copying, q3 q4, q2 q3

q3 q4, q2 q3, q1 q2: I don t think it needs to be as complicated as that - if the purpose of getting the quarter and year is simply to ensure the data is in the right order then I d store data in a blank column taking the right hand edge of the file name split as required. ...

Excel: Looping and copying, q3 q4, q2 q3

q3 q4, q2 q3, q1 q2: The macro uses the DIR() method to cycle through the filenames in the given folder. Each time a value is entered into the fName variable using the DIR command, you can test that filename for whatever characteristics you want before proceeding. Also, the...

Excel: How to link access table to excel

I would do this by creating a data query – - you didn’t specify version of Office, so I’ll give instructions for versions PRE 2007 - data, import external data, database query – follow the wizard to connect to your table. To refresh the data at any point...

Excel: linking multiple sheet totals to one cell, alternate approach, ogilvy

alternate approach, ogilvy, answer the question: Marie, Not really clear what you have already done - but you can link to other sheets in the same way you do to one sheet =Sheet1!M9 =Sheet2!M9 =Sheet3!M9 =Sheet4!M9 You can also select all your cells and copy them, then do a paste special...

Excel: links in excel sheets, cell references, cell reference

cell references, cell reference, u27: There is a HYPERLINK() formula, too. You can use it to create hyperlinks with text strings to specific places on the sheet and if the cell references will move appropriately since it s a formula hyperlink. Here s a link to my site where I show many various...

Excel: lookup value, cell a2, cell a1

cell a2, cell a1, janb: Hi Bill, Suppose you have the following entries in Jan sheet Name Age City JanA 11 JanAcity JanB 12 JanBcity and the following in Feb sheet Name Age City JanA 21 FebAcity JanB 22 FebBcity And summary sheet has the following layout. ...

Excel: lookup value final, bxx, time sarah

bxx, time sarah, hi thanks: For Each cell In Range( D:D ) this line specifies to run for every cell in column D - the word cell here is just a variable, but as it is actually referencing cells, I named it accordingly! If that was changed to For Each cell In Range( B2:B2000 )...

Excel: loop macro, loop macro, bxx

loop macro, bxx, x 2000: Sarah I can t really tell what you are trying to do, but, I think you will be better off if you set up your loops as follows: sheet1.activate for i=1 to 337 for j=1 to 2000 if cells(i,j).value=cells(338,j) then do what you want if the...

Excel: looping through files and copying, q3 q4, q2 q3

q3 q4, q2 q3, q1 q2: Dave Do you want to open all the files in the folder? If that is the case you won t need to build all your filenames in strings, nor store the data anywhere. You would just loop through the folder, using DIR, until it was nothing. For each opened file...

Excel: Macro, exit function, len

exit function, len, cells: Sub changeToNumbersOnly() Dim changeThis As Range For Each changeThis In Selection.Cells changeThis.Value = numbersOnly(changeThis.Value) Next changeThis End Sub Function numbersOnly(originalString As String) As String Dim...

Excel: Macro, Sixthsense

Sixthsense: Hi Dhananjay, 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...

Excel: Macro, Sixthsense

Sixthsense: Hi Sung, 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: Macro To Find Max Values Within Column, array formula, array formulas

array formula, array formulas, information column: Paul, the macro runs one time. Formulas recalculate everytime the sheet recalculates. So regardless of how slow the macro runs, if your data doesn t change very much, then the macro should relieve some of the burden of array formulas Sub FindMin()...

Excel: Macro Issue, target range, sheet tab

target range, sheet tab, manual changes: First, the answer to your macro question. There is another class of macros called sheet events that trigger themselves automatically as manual changes occur on the sheet. These macros go into the Sheet Module. Right-click the sheet tab and select VIEW CODE...

Excel: Macro Issue - Emailing From Excel, email signature, sfile

email signature, sfile, outlook application: james You have a function embedded in your sub. That would cause an error similar to what you are getting but I can t be sure that is the only problem. Try moving the GetBoiler function our of your subroutine. It is called by the subroutine, but, to call...

Excel: Macro -not a macro, empty column, macros

empty column, macros, a1: Sorry, I completely misunderstood the question. To get the 5th value in the string, use this: =MID(A1,FIND( ~ ,SUBSTITUTE(A1, ; , ~ ,4))+1, FIND( ~ ,SUBSTITUTE(A1, ; , ~ ,5)) - FIND( ~ ,SUBSTITUTE(A1, ; , ~ ,4)) - 1 ) ....copy down to get the value...

Excel: Macro modification, cell b1, string s2

cell b1, string s2, cell a1: Rakesh, OK, so I would see it being this way: Sub BuildTable() Dim v(1 To 8), v1(1 To 8), vv1, vv2 Dim sh1 As Worksheet, sh2 As Worksheet Dim cst As Long, idex1 As Long, idex2 As Long, i As Long, j As Long Dim s1 As String, s2 As String, cst_d as...

Excel: Macro to paste a selected excel worksheet range into PowerPoint, slide button, column widths

slide button, column widths, powerpoint slide: Barbara, Most of this is the powerpoint object module - not my area of expertise, but I will try to work something up for you and and post it as a revision of this answer. It may take a while as I have some meetings to attend. go into the VBE and...

Excel: Macro to scan string, national challenge, number 0

national challenge, number 0, simon simon: simon What do you want to do? Your answer to my question doesn t seem to be much like the original question. Finding the first instance of a number (0,1,2,3...) turned into once I find the numbers... . Does everything after the first instance of a number...

Excel: Manipulating Time Formats, array formula, data validation

array formula, data validation, time int: I would have the cell formatted as time, and have data validation on the cell that only accepted a time value - with help text explaining how to enter time. If all values ARE time then the problems should be automatically resolved - I m not clear why you...

Excel: Matching the first Row Names with Other excel sheet and extract data, index array, mail id

index array, mail id, index function: The MATCH worksheet function will match a given entry so could be used with an INDEX function to get data Match needs to be =match(YourValue,YourList,0) which will allow for the list not being in order - this will return a numeric value INDEX...

Excel: Merge cells and copy column to another sheet, destination cell, rowcount

destination cell, rowcount, xls file: Sub copyToSheet2() Dim rowCount As Long Dim wksht1 As Worksheet, wksht2 As Worksheet Dim rngTarget As Range, rngSource As Range Set wksht1 = ThisWorkbook.Sheets( Sheet1 ) Set wksht2 = ThisWorkbook.Sheets( Sheet2 ) Set rngSource = wksht1.Range(...

Excel: Microsoft Excel 2003 VBA, home servers, microsoft excel

home servers, microsoft excel, vba: j if I assume that you want to copy no data when findpatch is not found in the second workbook then Sub Test() Dim strName As Variant Dim varVal As Variant Dim FindPatch As Variant Dim strDt As Variant Dim r as Range strDt...

Excel: Microsoft Excel, concrete example, microsoft excel

concrete example, microsoft excel, cells: Please ask only one question in each post. If you have multiple questions, ask them in separate posts. When you ask a question, never do so in a general way, always use a concrete example. Don t say some cells to be highlighted or after a specified date....

Excel: Microsoft Map Add-In, type libary, excel 98

type libary, excel 98, macintosh version: Carrie, Excel 98 was a macintosh version of Excel. I don t have any experience with that. I will take a look at Excel 2000. But as the article says, it isn t supposed to work with later versions. ----------------------------- I loaded Excel 2000...

Excel: Modify Search Macro, whatfor, inputbox

whatfor, inputbox, cell address: Try this: ============= Option Explicit Option Compare Text ignore case Sub SearchSheets() Dim FirstAddress As String, WhatFor As String Dim Cell As Range StartAgain: Application.ScreenUpdating = True WhatFor = Application.InputBox( What...

Excel: Mulitply letters and numbers + sum totals, personal vacation, 5p

personal vacation, 5p, letters and numbers: Ork AA1: =countif($A1:$Z1, v )+.5*countif($A1:$Z1, .5v ) AB1: =countif($A1:$Z1, s )+.5*countif($A1:$Z1, .5s ) AC1: =countif($A1:$Z1, p )+.5*countif($A1:$Z1, .5p ) select AA1:AC1 and drag fill down the column -- Regards, Tom Ogilvy ...

Excel: Multiple Sheets Data Summary, absolute novice, line explanation

absolute novice, line explanation, paste data: Hi Tom, Suppose you want the combined information to go to is called Summary and you have another worksheet called Report which uses the summary sheet to report results. This macro copies all other worksheets into the Summary sheet: Sub CopyAll2Summary()...

Excel: macro to copyand paste valid data range, sh2, error data

sh2, error data, error values: Hari, change Set r = sh1.Range( A5:G29 ) to identify the source range change Set r2 = sh2.Range( C5 ) to identify the upper left corner of the destination range Of course the Sh1 should point to the source sheet and sh2 should point to the destination...

Excel: macro help, run time error, size sheets

run time error, size sheets, ogilvy: Alison, Yes, you can not offset(1,0) if you are on the last row. I don t understand why you are on the last row - but if that is where you want to be you can remove it. But I thought you were adding data to the end of the last data you added. In...

Excel: making macros immune from sheet name changes, sheet names, ogilvy

sheet names, ogilvy, clarity: Bill, If you go to the Visual basic editor and look at the project manager, for each sheet you will see it has two names. Usually they are the same when you start a new workbook. For example Sheet1 (Sheet1) If I renamed Sheet1 to Data, then it...

Excel: How to match values in column A with column B, return matches in C, array formula, column c

array formula, column c, serial numbers: Add a helper array formula in E1: =SUM(--ISNUMBER(MATCH(A1:A100,B:B,0))) ...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. This formula will tell you how many matches there are between columns A and B. You must use a reasonable...

Excel: Need an option to disable and enable the cell depend on code, cell b1, data validation

cell b1, data validation, cell a1: Jamil A.Z. You can t really disable a cell just using formulas. That would take VBA code and you don t mention any desire for VBA code. So your alternative is to use data validation. So you can use a formula in you data validation that look up the...

Excel: Need Steps to solve, example id, thanks in advance

example id, thanks in advance, wb: for the details you re asking for I d need to know which version of Excel you re using, as the location of commands varies. Perhaps you could send me a sample wb at bobumlas@hotmail.com - use subject of AllExpertsQ and redescribe what you want to do...

Excel: Nested IF Formula, delivery charge

delivery charge: Hello James. Type in the below mentioned formula in the cell where you want the amount to be displayed =IF(B22=0,0,IF((AND(B22 =1,B22 5)),15,(IF((OR(B22 =6,B22 10)),30,(IF((B22 =11),50,(IF(B22=0,,)))))))) What I have done here is simple added an IF command...

Excel: Nested if function, ogilvy, vba

ogilvy, vba, a1: Mohammad, If you have excel 2007, the nesting restriction is something like 56 levels - so you don t have a 7 condition limit. If you have xl2003 and older, you do have a 7 nesting level limitation but if you can branch in two directions at the top...

Excel: Noob Excel question, cell b3, relative reference

cell b3, relative reference, column chart: If I follow this it s simply a bit of normal maths =B8*(1+$B$3) gives you the share price increase - this can drag across to autofill. Similar formulas for the rental and fixed deposit - note that B8 is a RELATIVE reference so will change relative to...

Excel: nesting IF statements, tim simmons, allexperts

tim simmons, allexperts, 599cd: To nest IF functions, you have to place them inside each other: =IF(C8=F8, ,IF(C8=F9,F9,IF(C8=F10,G10, ??? ))) Remember, it s: =IF(Condition, ValueIfTrue, ValueIfFalse) See this tutorial for more help: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts...

Excel: new to excel and data, ad234, vlookup function

ad234, vlookup function, colb: olga, If you want to send me a sample file with a clear explanation of what you want to do, you can send it to twogilvy@msn.com that is my email as well if you have a specific question. I will be tied up for portions of Monday and Tuesday, but will...

Excel: Overtime calculation, overtime calculation, suppositions

overtime calculation, suppositions, aidan: based on your suppositions alone =B1-A1-(8/24) would give the result - BUT your examples don t agree with this - the first one is fine, but the rest show different results - start at 10:00 and finish at 19:30 is 8 hours plus 1.5 hours, which is 1:30...

Excel: offset formula, max cell, blank cell

max cell, blank cell, max value: Hi Jon - I ve read your question a few times but am having problems visualising what it is you need to do - I think this is partly as your formula refers to data I cannot see! It might be easier to do this via email (that way I could see a sample file) -...

Excel: To open a book with some rows and sheets hidden, adnan khan, documents and settings

adnan khan, documents and settings, hidden question: Private Sub Set_Calendar_Click() Application.screenupdating = False Workbooks.Open C:Documents and SettingsAdnan Khanmy.xlsx For each Sh in Sheets If sh.name Setting and Sh.name Employee Data then Sh.Visible=xlSheetVisible else sh.visible=xlSheetHidden...

Excel: To open a book with some rows and sheets hidden, adnan khan, documents and settings

adnan khan, documents and settings, sheet c: Private Sub Set_Calendar_Click() Dim mywrkbk As Workbook application.screenupdating=false Set mywrkbk = Workbooks.Open( C:Documents and SettingsAdnan Khanmy.xlsx ) Dim sht As Worksheet For Each sht In mywrkbk.Worksheets If sht.Name Setting And sht.Name...

Excel: Pivot Chart/Tables clearing existing queries, pivot table tutorial, output graph

pivot table tutorial, output graph, xls file: Define what you mean by clear the pivot table -- to me, that means doing something like this: Sub clearPT() For i = 1 To ActiveSheet.PivotTables.Count For j = 1 To ActiveSheet.PivotTables(i).PivotFields.Count ActiveSheet.PivotTables(i).PivotFields(j).Orientation...

Excel: To Print data of all sheets of a workbook, false test, sht

false test, sht, default printer: pplication.ScreenUpdating = False Dim NewSht As Worksheet Dim LogicTest As Boolean Dim lastcell As Long, curcell As Long Dim testvar lastcell = 1 curcell = 1 Set NewSht = ActiveWorkbook.Worksheets.Add NewSht.Name = Merged Dim sht For Each sht In...

Excel: To Print data of all sheets of a workbook, count cells, datedif

count cells, datedif, vlookup: 1 - it CAN T work in a regular module unless possibly YOU run it. In the thisworkbook, it runs automatically whenever you print. 2 - you MUST keep Cancel = True in order to print the Merged sheet automatically - otherwise it would print whatever the active...

Excel: Protecting Formulas in Excel 2010, format cells, contiguous selections

format cells, contiguous selections, formulas in excel: Lori, each cell is individually marked as locked or not. If you lock a cell, then when you protect the sheet, all the locked cells are protected. The cells marked as unlocked can be edited. By default, all cells are marked as locked. to have...

Excel: pass text strings longer than 255 characters, cell a1, text strings

cell a1, text strings, mytxt: Try this (untested) Change ActiveSheet.Shapes( TextBox 1 ).Select On Error Resume Next With Selection to With Sheets( Sheet2 ).Shapes( TextBox 1 ) and remove the line: ActiveWindow.RangeSelection.Select If that doesn t work try ...

Excel: payroll hours calcuations, payroll hours, decimal fraction

payroll hours, decimal fraction, time entries: OK, two parts to this (it would only be one if they entered the time AS time eg 16:00 and 2:00) firstly, 1600 is 1000 times too high for hours, and 24 times too high for a time value, so =time/2400 gives you a true time value (time is stored as a...

Excel: periodic sampling, vba, cheers

vba, cheers, sampling: Hi Shahzad! How are you? Please try the following vba program Sub samplingrecord() Dim R As Range, D As Range Set R = ActiveSheet.Range( A1:F5000 ) Set D = Sheets(2).Range( A1:F5000 ) D.Clear K = 1 For I = 1 To R.Rows.Count Step 5 For j = 1...

Excel: personal financial sheet, home budget worksheet, creit cards

home budget worksheet, creit cards, credit card debt: Patsy Check out this offering I believe it does what you want to do. http://office.microsoft.com/en-us/marketplace/monthly-home-budget-worksheet-EM001180062.aspx?redir=0 You can also use quicken, which is relatively cheap, like $30 or so, and has a...

Excel: To populate combobox, hris software, adnan khan

hris software, adnan khan, software in excel: You may have to either have that workbook open, or use a reference in the workbook with the form which points to that same range and then use a rowsource pointing to that range. In order to get it from the other workbook without putting the same data in the...

Excel: pulling data, lcase, sh2

lcase, sh2, sh1: Cody, This is about what I can offer based on your description and picture. Sub ABC() Dim sh1 as worksheet, sh2 as worksheet Dim r1 as Range, cell as Range Dim sFName as String, sLName as String sFname = lcase( Mike ) sLname = lcase( Top ) set...

Excel: Quiz Macro, valid numeric expression, random number generator

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: I’m not clear where the data comes from but it sounds as though it’s 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...