usb barcode scanner, scanner interfaces, excel spread sheet: Rommel, You ll need to research how to interface your barcode scanner to your computer to produce a data file, when you scan each barcode. Barcode scanner interfaces are not a part of Excel. Once you find the proper software for your barcode scanner,...

Excel: Excel - Sum Dollars of Two Met Criteria in an Array, cell d2, cell a1

cell d2, cell a1, consecutive rows: Jay What you need is the sumifand formula, unfortunatley this formula won t work in excel, So what I usually do is create one criteria out of the two by merging them with and & formula and then use the sumif formula. In your case assume that your column...

Excel: Excel sheet formulas to determine unduplicated records, d2 d2, excel sheet

d2 d2, excel sheet, pivot table: Adele, Here is one guess of what you want =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2)*($D$2:D2 calls )*($D$2:$D2=D2)) 1,0,1) If the test is any service but Calls is a match to any other service but calls then =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2)*($D$2:D2...

Excel: Excel TIme help, conversion formula, cell a2

conversion formula, cell a2, richard florida: Tom Here is the formula that will convert the time formatted as HH:MM AM/PM to a decimal number. =(A2-INT(A2))*24 So if you have 7:30 AM in cell A2 the result of this formula will be 7.5 You can apply the same formula to the 2:00 PM cell. Assuming...

Excel: Excel and Time addtion, format cells, day zero

format cells, day zero, time values: I pasted your data into my worksheet and put in a sum formula and I got: 0:00 0:00 0:00 0:00 0:00 0:00 3:00 15:30 12:30 5:00 15:30 10:30 5:00 17:30 12:30 3:00 14:45 11:45 47:15 I did format the last cell as [h]:mm I would format...

Excel: Excel use as a searchable database on multiple criteria, medfile, physician name

medfile, physician name, cihost: Dani: Sorry for the late reply. EXCEL can do all of this and depending on much data you will need to store in the file will determine how fast or slow it will run now and eventually later. Without having seen the whole thing I would suggest not using...

Excel: Excel-VBA, excel ontime, chip pearson

excel ontime, chip pearson, excel vba: babu, Chip Pearson has a page that lays out how to do something on a recurring basis using VBA: http://www.cpearson.com/excel/ontime.aspx What you want to do (as I understand it) is have the sheet recalculate. You can put code in Chip s process like...

Excel: Excel VBA functions, vba functions, worksheet cells

vba functions, worksheet cells, excel vba: Function lastName(fullName As Range) As String Dim iChar As Integer, iLen As Integer lastName = iLen = Len(fullName) If iLen 3 Then Exit Function For iChar = iLen To 1 Step -1 If Mid(fullName, iChar, 1) = Then ...

Excel: Excel to Word, aidan, mail

aidan, mail, heritage: Go to word and choose the mail merge option - generate labels based on your data and drop the data fields in where they need to go - I m being VERY general here as you didn t specify version of Office being used and the methods are subtly different in each...

Excel: Excel Workbook and Sheets, sheet1, excel workbook

sheet1, excel workbook, month of the year: Amber, Assume you enter data in sheet1 in sheet2 in A1 put in the formula =Sheet1!A1 Now select Sheet2 A1 and drag fill/copy down, then select that data and drag fill/copy across. these formula will reflect whatever is entered in Sheet1 in the...

Excel: Excel Worksheet Macro - Revised, cell d2, cell c2

cell d2, cell c2, cell references: Stan, I don t have any way to respond to a rating - so best I can do is try to revise this response and hope you get it. r is column H so basecolumn is 8 loff = basecolumn - 4 = 8 - 4 = 4; so loff holds the value 4 (positive 4) r.offset(0,-4)...

Excel: Excell, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: Excell Formulas, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: That s just a matter of simple math. I m assuming you re placing a 1 in each column representing the accept or non-accept values. If so, just count up each column uisng the SUM function, and then add both columns together to get the total number of responses....

Excel: Exel Counting Not to Exceed Question, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: I would use another cell and say: =IF(A1 50,50,A1) Assuming your calculation is in A1, it will display either A1, or 50 if A1 is 50. You could enclose your current function in the IF statement, but that gets messy. You can use the IF function...

Excel: Expenses Between Two Dates, c description, column c

c description, column c, richard florida: Anthony Yes it is possible, here is how you do it. =SUMIF(A2:B100, 01/31/09 ,B2:B100)-SUMIF(A2:B100, 02/28/09 ,B2:B100) This formula is in two parts and sums the expenses between the two dates 1/31/09 and 2/28/09. The formula says--- 1--Sum...

Excel: How to enable delete formula cell, target cells, rng

target cells, rng: If you want to be able to delete formulas, why bother with this routine at all? You CAN unlock the cell, but then that also negates the purpose of protecting the worksheet. You re asking to protect cells with formulas but you want to be able to delete the...

Excel: excel 2000, selection bar, sht

selection bar, sht, excel 2000: Not sure why you need to do this (right clicking the selection bar would show you all the sheets for example) but you could easily use a macro to do this Sub lister() Dim n n = ActiveCell.Row Dim sht For Each sht In ActiveWorkbook.Sheets Cells(n,...

Excel: excel 2007, conditional questions, conditional test

conditional questions, conditional test, excel function: Ahmad, There are many types of conditions. It s impossible to tell you how to use them all! I can get you started, though. In general, most of the Conditional statements are to be found in the LOGICAL functions group. You should use...

Excel: excel 2007 Conditional formatting for dates, conditional formatting

conditional formatting: Hi Dan, You will need to set up three cases to do this. In the Conditional Formatting dialog box, set it up for three cases and select the Formula Is option for all three. Use the following formulas for the three cases. =B3 TODAY()-300 =B3 TODAY()-330...

Excel: excel, 2 names, d3

2 names, d3, text strings: Meg, Everyone is here to answer questions, so if you have them, then ask them. =IF(LEN(D3)-LEN(SUBSTITUTE(D3, , )) =1,D3,LEFT(D3,FIND( ^^ ,SUBSTITUTE(D3, , ^^ ,LEN(D3)-LEN(SUBSTITUTE(D3, , )))))&LEFT(RIGHT(D3,LEN(D3)-FIND( ^^ ,SUBSTITUTE(D3,...

Excel: excel, positive reply, sheet folder

positive reply, sheet folder, mdf files: I m not clear exactly what you want to do here - are you copying a file from a given location and name to another one? Or do you want a listing of all the files. You also didn t specify which version you are using - prior to 2007, the FileSearch command...

Excel: excel, free joe, ebay

free joe, ebay, craigslist: Joe, Excel as such can only be purchased... it is certainly NOT free. It s fairly expensive, $100USD. You might be able to find a deal on Craigslist or eBay. You can also get a similar program called Open Source, which acts like excel, and it is...

Excel: excel, microsoft program, capability

microsoft program, capability, microsoft: You must have a macro that did what you describe since earlier versions did not give you that capability, assuming you are doing this via File, Send To from the menu. You did not say how you did this in the earlier versions so I have no way of knowing how...

Excel: excel shading, excel, conditional format

excel, conditional format: You can use conditional formatting for this. Select all the cells that you want to format in the column, and go to menu Format- Conditional Formatting. Enter a condition there with a formula like: =COUNTIF($A:$A,A1) 1 The A1 reference should be the first...

Excel: excel: time calculated as minutes., manual calculations, true time

manual calculations, true time, ms office: Yes. For example, if 7:04 entered as a TRUE time then use: =A1*60*24 to convert that minutes, which will give you 424 minutes AFTER you format that cell to a General number format. 7 hours is 420 minutes and then add 4 minutes to that. Thus you can subtract...

Excel: excel no's validation, conditional formatting, a12

conditional formatting, a12, thanks in advance: Hi Priyanka, If I am understanding your question correctly, you want to notate which cells have more or less than the required 12 digits in the phone number field. You can do this by using Conditional Formatting. Please follow these steps: 1 - Select...

Excel: excel2003 finding blanks, parent cells, blank cells

parent cells, blank cells, question thanks: in the procedure ProgressChart you loop over the range r For Each cell In r r is defined as Set r = .Range(Cells(9, 11), Cells(lngLastRow, 11)).SpecialCells(xlBlanks) Where end dates are in sheet on sheet Billing. so it should create one...

Excel: Find Nett available quantity from a list of transactions, xyx, aidan

xyx, aidan, a1: Presumably it s a known list of products, so I d build this list first, and then use a SUMIF function (No programming required) which is =sumif(RangeToCheck,Criteria,SumRange) so for example =sumif(sheet1!$A$1:$A$1000,a1,sheet1!$B$1:$B$1000) would...

Excel: Find & Replace Macro, string s2, cell a1

string s2, cell a1, cell reference: Johnathan, Assume cell A1 has the replace text and A2 has the replace with text then Dim s1 as String, s2 as String Sheets( Agents ).Select Columns( B:F ).Select s1 = Range( A1 ).Text s2 = Range( A2 ).Text Selection.Replace What:=s1, Replacement:=s2,...

Excel: Formatting Excel, boggs federal building, hale boggs

boggs federal building, hale boggs, states district court: In the example you give, the commas that you want to replace with carriage returns are NOT followed by a space, whereas the comman that you want to treat differently IS followed by a space. So to accomplish your specifications using search/replace, it takes...

Excel: Formatting Excel, boggs federal building, hale boggs

boggs federal building, hale boggs, states district court: Hi L2, I am not sure how much data you are working with, but here is a quick solution to get it done: 1 - Select the cell(s) that contain the data you want to search and replace 2 - Click on Edit on the top menu, then select Replace 3 - In the Find...

Excel: Formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: Formula for add row, blank line, dependant

blank line, dependant, aidan: Not directly - you can design your sheet in such a way that data appears on a blank line only if conditions are true, OR you can use VBA (perhaps on the change event) to evaluate two cells and dependant upon the results carry out an action such as inserting...

Excel: Formula to find if last 6 characters in a cell are numbers, len function, 6 digits

len function, 6 digits, string 6: James, HAH! I totally revised this answer based on that final thought I had (the one in the previous answer). You only need one equation and its relatively simple. =NOT(ISERROR(VALUE(RIGHT(string,6)))) There may be additional errors...

Excel: Formula Help, modified times, stop time

modified times, stop time, error c: Mealnie, The correct way to do an OR test is like this: =IF(OR(D6 J6,C6 K6), error , ) You d have to create a comprehensive list of all the possibilities, and check for them all, using a set of Nested IF statements. Or perhaps one giant...

Excel: Formula huge, needs simpler solution., logical tests, job1

logical tests, job1, pivot tables: Nico I am not totally sure that I am following how your data is set up but I believe you can use the VLOOKUP() function to get the information you want. i.e. =VLOOKUP($A1,Sheet2!$A$1:$K$2000,11,FALSE) Assuming cell A1 contains the job number This...

Excel: Formula Issue, date room, c 750

date room, c 750, guest names: James, A really quick thought for you: try putting the portion that returns the #NUM inside an IFERROR() statement. I suggest this without looking in detail at your formula. I wanted to shoot you a quick answer... but if my answer isn t possible,...

Excel: IF Function, input number, bob smith

input number, bob smith, bond james: Vish, Okay then! Here s the approach I d use: Use a set of three nested IF statements, where the conditional test for each one is based upon the MATCH() function, embedded within the ISERROR function, which is itself embedded in the NOT function....

Excel: Is Function to change cell format?, cell a2, isna

cell a2, isna, d4: Tania I haven t had the need to do this in the past,and since the conditional formatting feature doesn t allow the percent or numbe formatting--- but it seems to me that you could reference in your formula another cell that would be dependent on the values...

Excel: IF Function using range names, free microsoft excel, hourly salary

free microsoft excel, hourly salary, logical test: The problem is probably just that you have your named cells in quotes. Get rid of the quotes and see if it works. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: Automatic Data population, data links

Excel: Automatic Data population, data links

Excel: convert four-digit number to military time, format cells, digit number

Excel: convert four-digit number to military time, format cells, digit number

Excel: countif not working, logical operators, literal characters

Excel: countif not working, logical operators, literal characters, chars

Excel: Drop down list from another sheet, data validation, ogilvy

Excel: Drop down list from another sheet, data validation, ogilvy, microsoft excel

Excel: file password

Excel: file password

Excel: saving VBA-created objects, c range, b12

Excel: saving VBA-created objects, c range, b12

Excel: If statement cell range, intention

Excel: If statement cell range, intention

Excel: file password, ogilvy, macros

ogilvy, macros, two ways: Brian, there are two ways I can think of, but I can t give you the details of either one, because they are beyond my level of expertise. One is something that would have to be done at the Operating System level, where you would assign a password...

Excel: formatting, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: The easiest solution would be to create a cell that shows the NUMBER OF DAYS this invoice is late (subtract the date from today s date) then apply conditional formatting to that cell. There are many different techniques in Excel you can use to break apart...

Excel: Two formatting questions - workbooks & worksheets, footer text, header footer

footer text, header footer, sheet numbers: Hi Elizabeth, Question 1 - I am assuming that you want the page numbers shown when you print the document. If so, you will just need to select all the sheets each time you print it out. When you do this, Excel knows to page all sheets sequentially. You...

Excel: formula/function for payroll, max function, maximum contribution

max function, maximum contribution, pension contributions: no problem- the MAX function will do it for you =max(1000,yourFormula) NOTE also that I d use the ROUND function to ensure you get a value that fits in with your currency- I imagine 2 dp - just displaying as rounded isn t going to be good enough. ...

Excel: formula help, cell b3, gross profit

cell b3, gross profit, b2: Hey Michael, In order to subtract a percentage from another number, you can use the following formula: =B3*(1-15%) This assumes that selling price is in cell B3. So your formula might look something like this: =(B2-(B3*(1-15%))/B3) I hope that...

Excel: If function, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: That s not too hard: =IF(A3= w ,A1-A2,IF(A3= m ,A1, )) That should do it. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more...

Excel: Generate random numbers in a user-specified range, format xi, cell font

format xi, cell font, thanks in advance: Tim, as you say, the code as written will generate a single random number and assign it to all cells. You will need to loop through those cells and assign a random numbers. As long as your are already looping, you can do it as part of that loop. Private...

Excel: Graph info on different spreadsheets, graph info, cell b2

graph info, cell b2, grap: Say you want to make a graph in which one data series is range A2:A5 in Book1.xls, and the other data series is A2:A5 in Book2.xls. Book2.xls is in C: emp. In Book1, cell B2, enter: = C: emp[Book2.xls]Sheet1 !A2 and copy down thru B2:B5. Then use...

Excel: graphing in Excel 07, types of graphs, column headings

types of graphs, column headings, scatter plot: Twee, There are MANY types of graphs in Excel, especially in the 2007 version! You could make a scatter plot (I call these X-Y plots) where there would be one line for each country.... the X-Axis will automatically fix itself up if the X-values...

Excel: graphing numeric data based on text field, spreadsheet column, line graph

spreadsheet column, line graph, toll free numbers: Mark, I realize it has been a month since you submitted this question. You have probably solved it by now. But in case you haven t, you can resubmit this to me after Sept 29th. I m sorry but events of the past month prevented me from doing much work...

Excel: HELP PLEASE!!, odd rows, cell a1

odd rows, cell a1, excel spreadsheet: Lisa Yes you can do what you want--however how to do it will depend on what your data looks like, what you want it to look like, etc. Some suggestions You may want to use the if and formula to find data that is located within the account number range...

Excel: Help about convert row into columns, platinum gold, platinum silver

platinum gold, platinum silver, gold silver: Satyabrata, This is a terribly complex problem that is beyond my ability to explain with short answer! It would take many hours of complex programming to make this work. I suggest that you direct this question to one of the experts who lists MACROs...

Excel: Help with Excel, b9, ogilvy

b9, ogilvy, len: Naomi, I am not exactly sure what you want, but it you want to know if the number of characters in cell B9 are you can use =len(B9) if you want to display a warning =if(len(B9) 30, Warning, B9 contains &len(b9)& characters , ) This message...

Excel: Help on Macro for copying data from one sheet to another, blank cell, challange

blank cell, challange, cells: Range( A1:E13 ).Copy amend to be the range you want Sheets( Sheet2 ).Select amend the sheet name to whatever your second sheet is called Cells(sheets( Sheet2 ).Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Select ActiveSheet.Paste should be...

Excel: Help on Macro for copying data from one sheet to another, dynamic name, blank cell

dynamic name, blank cell, mayank: Mayank Gupta, Although I usually don t provide Macro advice on this website, I ll give you a brief answer this time. I have successfully done this for several projects I ve developed. All you need is a simple macro that copies the data between...

Excel: Help Please......, sumproduct, colb

sumproduct, colb, excel function: Cosmin, I m sorry for the delay. You can use the advanced form of SUMPRODUCT to count a column based on an input. What you want is something like this: =SUMPRODUCT(--(colA=1/7/09),--(colB 1/1/1900),--(colB 1/1/2030) where colA and colB...

Excel: Help with an IF Statement, cell b2, conditional test

cell b2, conditional test, input value: Sarah, You need a NESTED (sometimes called a COMPOUND) IF statement. These are easy to write once you know how they work; to that end I have included (at the end of this answer) a short tutorial I wrote. When you write this type of statement,...

Excel: Help With Time Function, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Instead of putting 7 AM - 9 AM in one cell, why don t you put 7 AM in one cell, and 9 AM in another cell. Now you ve got actual TIME values, and changing the other times is relatively easy. There are many different techniques in Excel you can use...

Excel: Help w Excel combining duplicate rows and add, meager mind, employee names

meager mind, employee names, pivot table: The EASIEST way is going to be a pivot table - what you didn t tell me was which version of Excel you are using, so I ll guess NOT 2007 - at which point, data, pivot table and chart report, follow the wizard and then drop PCNamd and Name onto the left side,...

Excel: HH:MM:SS, hh mm ss, exact timings

hh mm ss, exact timings, entering time: Hi Key, Unfortunately the shortcut does not provide the seconds. Here are some options that might be helpful to you: 1 - You can create a macro and assign it to your own shortcut key (or even the same on you are currently using for entering time). ...

Excel: Hidden Rows excluding row 1, header row, ogilvy

header row, ogilvy, len: Dave, How are you trying do to it. Sub HideRows() Dim r as Range, cell as Range rows.EntireRow.Hidden = False set r = range(cells(2,18),cells(rows.count,18).End(xlup)) for each cell in r if len(trim(cell)) 0 then if isnumeric(cell) then...

Excel: Hide Formula errors in excel 2003, format font, ref name

format font, ref name, cells: No need to use macro unless you insist, and then you can record these steps: Select ALL cells (ctrl/shift/A twice), use Format/Conditional Formatting, change Cell Value Is to Formula Is , enter: =ISERROR(A1) click Format, Font tab, then pick a white...

Excel: Hide formula error in excel 2003, ref name, zero values

ref name, zero values, click tools: I am not sure what you mean by hide but you can add the ISERROR function to your formulas to check for errors and display whatever you want the cell to display when the formula in that cell raises an error. Example: =IF(ISERROR(A1/B1), ,A1/B1) when...

Excel: help!, free microsoft excel, perspective column

free microsoft excel, perspective column, question sign: Well, which one is the correct price? What criteria should Excel use to determine this? You probably just need to learn the IF function: You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in...

Excel: hide formula in excel 2003., format cells, exact formula

format cells, exact formula, g1: 1 - View/Formula Bar (uncheck it) - but anyone can redisplay it; 2 - Format cells/protection/selcet Hidden , then protect the sheet 3 - Define a name, like SecretFormula, to be the actual formula -- that is, if you have a formula like =VLOOKUP(D3,G1:H22,2,FALSE),...

Excel: how to hide password while typing, free microsoft excel, input mask

free microsoft excel, input mask, question sign: You can t do it with an inputbox, but you can create a password FORM with a single text box and command button on it. For the text box, use the PASSWORD input mask and that will hide the characters. Let me know if you have any other questions or comments....

Excel: hours to mins, manual calculations, example purposes

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

Excel: Importing data from a specific date onwards to a new file, input flow, inputbox

input flow, inputbox, excel sheet: Would the date column always be in a given location, or would the macro have to work out which column to use? - the process is easy enough to write, indeed it s fairly easy to do manually - data, filter, autofilter, select the date column, choose custom and...

Excel: Importing data from a specific date onwards to a new file, input flow, inputbox

input flow, inputbox, sh1: anupreet, I don t know what etc means in terms of the number of columns-- so I set up the macro to copy columns A:C. If you have more columns to copy than that, then change Set r2 = sh.Range(r1, r(r.Count)).Resize(, 3) to reflect the proper number...

Excel: INDEX and MATCH formula in VBA?, formula index, silly question

formula index, silly question, vba: Bill, Read my profile: it says that I don t offer macro or VBA advice on this website. However, I question why you say doing this formula for 20,000 rows is too much . What s too much about it? Does your spreadsheet run slowly, or do you run...

Excel: Increasing Date by One Day in Non-Consecutive Cells, free microsoft excel, cell b1

free microsoft excel, cell b1, cell a1: You just have to select enough cells so that Excel can see a pattern. Then you can just Autofill down. Select A1 thru A20, then Autofill down. Excel should see the pattern and continue with the dates every 10 rows. If you don t know how to Autofill,...

Excel: Index Function, e mail address, index function

e mail address, index function, chef bob: Patrick, Assume names are in column A and January data is in Column B, then I would use =sumif(A:A, Chef Bob ,B:B) I don t see any role for the Index function. if that isn t what you want, then post back. -- Regards, Tom Ogilvy...

Excel: Index/Match Array, room booking system, guest checks

room booking system, guest checks, c 750: James, something along the lines of: if(sum((( Current Bookings !$A$2:$A$750=$B43)*( Current Bookings !$C$2:$C$750 =EZ$1)*( Current Bookings !$D$2:$D$750 =EZ$1)) 1,INDEX( Current Bookings !$B$1:$B$164,Small(if(( Current Bookings !$A$2:$A$750=$B43)*(...

Excel: Input box for entry deletion macro, question thanks, input box

question thanks, input box, step 1: Sub DeleteCodes() Dim ToDelete As String, i As Integer, NumDeleted as Integer ToDelete = InputBox( Enter Code to delete ) If ToDelete = Then Exit Sub NumDeleted=0 For i = Range( B65536 ).End(xlUp).Row To 1 Step -1 If Left(Cells(i,...

Excel: Insert, vlookup formula, rng

vlookup formula, rng, input box: This will work except that I can t finish the VLOOKUP formula because I don t know where you re looking col A up! So you ll have to change the formula. Sub Insert() Dim Rng As Range, TheCol As Range, LastRow As Integer Set TheCol = Application.InputBox(...

Excel: import text VBA, array array, simulation model

array array, simulation model, spath: Alexis, The error would be because you don t have a workbook open named Book1 You need to put a legitimate name for the workbook where you want to move the sheet. Suggest you use the proper extenstion as well Workbooks( Master.xls ).Sheets(1) ...

Excel: LArge excel file, slow calculation inefficient

slow calculation inefficient: Dave, There can be many reasons why it is slow. First, two major classes of reasons: 1) Something else is using your computer resources. For example, you are simultaneously solving pi to 1 billion decimal places or running a weather simulation...

Excel: Large function, relative cell value, array formula, curly brackets

array formula, curly brackets, relative cell: Kev, I put random numbers between 1 and 5 inclusive in the range A1:A20. Then I went to B1 and entered the following formula =SMALL(IF($A$1:$A$20=LARGE($A$1:$A$20,1),ROW($A$1:$A$20)),ROW()) I entered the formula with Ctrl+shift+enter rather than...

Excel: Large function, relative value, top salespeople, value question

top salespeople, value question, vlookup: Need a few helper columns, say G & H. In G1: =COUNTIF($D$1:D1,D1)-1 Fill down to G5. in H1: =MATCH(D1,OFFSET($A$1,IF(G1=0,0,MATCH(D1,A:A,0)),0,1000,1),0) in H2: =MATCH(D2,OFFSET($A$1,IF(G2=0,0,MATCH(D2,A:A,0)),0,1000,1),0)+IF(D1=D2,H1) Fill down to...

Excel: Linking data to combo boxes, source sheet, combo boxes

source sheet, combo boxes, linking data: Rodney, I would need to see what you have to suggest a solution. Any solution would be specific to how you have the data laid out on your source sheet. If you want to send me a sample file, I will try to show you a solution/approach. send a sample...

Excel: Linking information from one cell on one sheet, to another cell on another sheet, cell b11, sheet1

cell b11, sheet1, a12: You have two sheets. On the sheet named Sam, you want cell A12 to display the value that s in cell B11 of the sheet named Joe. Select A12 on Sam. Hit the = key. Click the Joe tab. On Joe, select cell B11. Hit the Enter key. Cell A12 of Sam will now have...

Excel: Linking Workbooks, excel workbooks, first instance

excel workbooks, first instance, instances: Lisa Do you have two instances of Excel open? ie do you open one workbook then start another instance of Excel and open the other workbook? If so open both workbooks from the first instance of Excel. If that isn t the problem get back to me. Richar...

Excel: Listing all external file references, path workbook, sheet tab

path workbook, sheet tab, cell reference: I m assuming you have an open file A.xls, which has a reference to a closed file B.xls. Select all worksheets within A.xls (unhide all sheets, click on the first sheet tab, hold the shift key, then click on the last sheet tab). Hit Ctrl-F, and do a Find...

Excel: Listing new items, array formula, curly brackets

array formula, curly brackets, colomn: StuartJohnWood, I tested with constants in column A of worksheet2 and overlooked that the formula would produce the null string so here is the revised formula be B1: =IF(ROW() =SUM(1*(LEN(TRIM($A$1:$A$1000)) 0)),INDEX($A$1:$A$1000,SMALL(IF(LEN(TRIM($A$1:$A$1000))...

Excel: Locating Cells, matching number, different numbers

matching number, different numbers, visual representation: Luis, A very tricky arrangement! I would never have guessed from your first question! I see how to do it! It s not too difficult. You need to put a FORMULA in the conditional formatting [CF] dialog box which checks the value in that...

Excel: Logical/Nesting functions, hey sandy, d7

hey sandy, d7, e7: Hey Sandy, I believe the following formula is what you are looking for: =IF(D7 60,2.45,IF(AND(D7 =60,D7 =119),2.27,IF(AND(D7 =120,D7 =239),2.12,IF(D7 =240,2.00)))) I hope this answers your question. Please let me know if it does not. Thanks! Jessic...

Excel: Look up formulas, blank cells, array reference

blank cells, array reference, absolute references: you need to use absolute references here – A2 is a relative reference, in that as the formula moves around the sheet, so does the cell it is referencing. $A$2 is an absolute reference – it refers to A2 no matter where you put the formula. In the formula...

Excel: Lookup Date Range, report wizard, pivottable

report wizard, pivottable, index formula: 1. Add a column to your data labeled Month 2. type in this column in each row the following starting with row two =month(a2) This will list the month in each row of this column 3. Click somewhere on the data 4. Select the Data drop down menu...

Excel: Lookup a given cell ref in another workbook and return another cell's value based upon the reference, cell references, colb

cell references, colb, proper location: John, You can use the indirect function for this. Both workbooks must be open in the same instance of excel. then you can do for example, in A2 you could put a formula like: =Indirect( [Other bookname.xls]Sheet1 ! &B2) You would need to adjust...

Excel: Lookup Multiple Rows., cell references, lookup functions

cell references, lookup functions, vlookup: Nabam, This is an EXTREMELY difficult problem, made difficult by several factors: 1) your data is not consistently spaced. In some cases, one set of data spans THREE rows; in other cases, TWO. There may be other cases that span 1 or 4 rows,...

Excel: Lookup question, column c, question question

column c, question question, shooting from the hip: Yes and there needs be another change as well, for the OFFSET function, which I have done below =IF(ISERROR(OFFSET(C1,MATCH(50,C1:C5,1),-1)), ,OFFSET(C1,MATCH(50,C1:C5,1),-1)) this means that column C will have the 50% value you are looking for and...

Excel: ledger, receivables, b2

receivables, b2, collections: You can use a helper column to see if the date is older than 30 days, something like this (assuming B has the dates): =IF(TODAY()-B2 =30,2,1) and fill down, then sort by this column and all the 2 s will be at the bottom. You can cut/paste these to the other...

Excel: lookup and return a value from a table, sheet1, vishwanath

sheet1, vishwanath, ogilvy: Vishwanath, Assume the table is in sheet1 so for an entry in B2 of the other sheet then in A2 of the other sheet =IF($B2= , ,Vlookup($B2,Sheet1!$A:$B,2,False)) I assume you will enter a date that is an exact match to one of the dates in the...

Excel: MAtch text, dharmender singh, email addresses

dharmender singh, email addresses, correspondences: bhavana, You didn t give me any examples of what the corresponding email addresses look like, so this is difficult to answer. Probably 10 examples showing the different variations would be helpful. It s unlikely that this will be easy, since...

Excel: Macro, test xls, valid expression

test xls, valid expression, wkb: Example: you want to know if the workbook named Test.xls is open. You want the variable blnOpen to be TRUE if Test.xls is open, and FALSE otherwise. Dim wkb As Workbook Dim blnOpen as Boolean On Error Resume Next Set wkb = Workbooks( Book1.xls ) blnOpen...

Excel: Macro to extract specific values from a list, vba code, mitali

vba code, mitali, ogilvy: Mitali, Although my profile says that I don t offer macro advice on this website, I wonder if you are using the word Marco properly. Macro is VBA code, which runs and performs a function. I am not sure you need a macro to do what you want. But,...

Excel: Macro for finding data, isnumber, sheeta

isnumber, sheeta, worksheet functions: Philip, You have asked at least 3 questions since the one you are following up on. I answered all three in one way or another in less than 30 minutes after they were asked. I asked you to send me your workbook and gave you my email address - according...

Excel: Macro to pick value and copy row, excel spreadsheet, reference numbers

excel spreadsheet, reference numbers, ogilvy: Jay, This worked for me. Test it on a copy of your data. Sub CopyData() Dim r As Range, cell As Range Dim r1 As Range, res As Variant Dim r2 As Range Set r1 = Range(Cells(1, A ), Cells(Rows.Count, A ).End(xlUp)) Set r = Range(Cells(1, AC...

Excel: Macros, layout location, sheet1

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

Excel: Mail/Hyperlinks, hyperlinked, a10

hyperlinked, a10, aidan: two options - one is via a macro, the other uses word to do a mail merge - see http://www.rondebruin.nl/tips.htm for macro instructions on emailing, for mail merge, use the wizard in Word to set this up and set the mailing recipients. Sorry to be so brief...

Excel: Maintaining Cell References in a Formula During A Sort, source cells, cell moves

source cells, cell moves, cell references: Bill, You can t maintain the references to cells when they sort, as you have observed. Formulas external to the sorted data will refer to the same cells; the cell references don t change with the sort order, as you have seen. Its the VALUES in the...

Excel: Manipulating Characters WIthin a Cell, 4 digits, macro in excel

4 digits, macro in excel, jones consulting: There are many ways to do this. Here is one way that might help you. Change the range as needed. Note that your data will have to be formatted consistently for this to work every time correctly and on the right cells since it looks for space dash space...

Excel: Master sheet, positive reply, detail sheets

positive reply, detail sheets, excel sheet: There s no such thing as automatically updated in the master sheet -- I d need to know a lot more information, like how the detail sheets are laid out, how the master sheet looks, if you want data copied/pasted somewhere onto the master or it it should pick...

Excel: Matching Two Columns, logical formula, logical function

logical formula, logical function, career industry: Kevin, This is perfectly clear, and there are several ways to do it. If it were me, I d use Conditional Formatting [CF] to change the color of all rows where the before and after don t match. You could also add a column with a logical function...

Excel: Merging cell contents, free microsoft excel, paste values

free microsoft excel, paste values, excel tips tricks: Make your concatenation formula in column C, then copy the whole thing and paste it into column D but only paste the values: Edit Paste Special Paste VALUES Let me know if you have any other questions or comments. Also, be sure to visit my web...

Excel: MODE function, pivot table, numeric column

pivot table, numeric column, matching numbers: Ramya, The MODE function only works with numbers so you can t use if for determining the most frequently occurring piece of text. Do to so, you must: A) create a list of each piece of text (a unique list). You can use a single-column pivot...

Excel: Move data from cell without moving formula, input table, true expert

input table, true expert, sort keys: Jonas, To make the rows automatically change order when data is entered (without writing a macro) is not easy, in fact it may be impossible. But you can do something like what you want. My first suggestion is to find one column that you can...

Excel: macro for sorting like entires, jamie price, dummy data

jamie price, dummy data, couple rows: Jamie Price, Send me a sample workbook with some dummy data in it or real data - your choice. If dummy data make sure it accurately represents you data. Tell me what version of Excel you are using. The first step will be to sort the data so all...

Excel: mm/dd/yy hh:mm:ss AM/PM format in excel, hh mm ss, format cells

hh mm ss, format cells, cell a1: Open a new Excel workbook. Select cell A1. Type in a sample date, e.g.: 7/18/1959 3:33:10 AM With A1 still selected, choose from Excel menu: Format, Cells, Number, Category: Custom, Type: mm/dd/yy hh:mm:ss AM/PM (you re typing this into the entry box...

Excel: modified workdays function, free microsoft excel, custom vba function

free microsoft excel, custom vba function, question sign: Excel makes it easy to count the number of WORK DAYS between two dates. You can use NETWORKDAYS if you only need to worry about excluding Saturdays, Sundays, and a list of holidays. However, if you have a custom requirement, you ll need to write your...

Excel: moving data, cell a1, receivable ledger

cell a1, receivable ledger, sheet1: Edgar You can use an IFTHEN formula to determine that the account is over thirty days and then move it to collections. This formula will do it =IF((NOW()-A1) 30,+B1, ) Assuming the date of the invoice is in cell A1 The formula says, if the...

Excel: Need help with Combo Box and Exporting Data, table values, concrete example

table values, concrete example, combo box: It s generally beyond the scope of this free service for me to create an entire vba solution for you from scratch. Many paid services are available in which someone will do all the work for you. If you d do some of the work yourself, and ask a question here...

Excel: I need help with solving this problem I've tried a pivot table but can't quitem make it work., commission percentage, pivot table

commission percentage, pivot table, gross margin: Adam, A pivot table might work, but you probably are having trouble getting it to be in the table-format you want. What would be better is one of the single-or-multiple criterion record-selector and summarizing functions. There s a whole...

Excel: nested sumif?, b2, c2

b2, c2: Not clear what you mean by seperate formula, but for your sumif try =SUMPRODUCT(--(A2:A10=B2:B10),C2:C10) amend my A2:a10 etc to the actual range you want for the certain percentage something like =SUMPRODUCT(--(B2:B10/A2:A10 1),C2:C10) will do...

Excel: Obtain values in column M based on alphanumeric match in column C, countif, agent names

countif, agent names, corresponding service: Glenn Names in C, values in B given a name, sum the corresponding values in B and also get a count of the rows that contain the name =sumif(C:C, AgentQ ,B:B)/Countif(C:C, AgentQ ) if AgentQ is in cell Z5 it could be =sumif(C:C,Z5,B:B)/Countif(C:C,Z5)...

Excel: Offset function to multiple rows, bj2, result row

bj2, result row, right combination: You didn t tell me what the formula does, so I m not sure if offset is actually the way to go, but =offset($a$1,1,2) would return the cell one row DOWN from A1 and 2 columns across - but possibly using the right combination of absolute, relative or mixed...

Excel: Page number refencing, double quotation mark, cell references

double quotation mark, cell references, text values: Page numbers in excel don t actually exist - they do on printed output, but on the sheet itself it has no meaning - changing the margins or zoom would affect the page. You can reference other cells, and you can use the CELL worksheet function to return information...

Excel: To Paste Jpg Images form Folder, employee id number, string s2

employee id number, string s2, myfolder: The Application.FileSearch has gone in office 2007 - though heaven knows why - this code With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & .Title = Please select a folder to list...

Excel: Percentage, format a3, format cells

format a3, format cells, percentage formula: Stephen =24/42 if you go to format cells, then select percentage and pick one of the percentage formats I get 57.143% or just 57% depending on how many decimal places you want to show. A1: 42 A2: 24 A3: =A2/A1 if you have your values...

Excel: Pivot Table, excel 2007 pivot table, best regard

excel 2007 pivot table, best regard, pop up menu: Khurram, I think you can accomplish this by using INSERT CALCULATED FIELD. This is an option which appears in the (Excel 2007) Pivot Table Ribbon, which becomes available when you click anywhere in the existing pivot table. The option (which is on...

Excel: Pivot Table, best regard, pivot table

best regard, pivot table, borders: You can t do this automatically - you either need a macro to do both the pivot table and this extra column or the extra column itself, OR You can VERY easily do it manually: Select the entire Credit column, copy it, select the Balance column, Paste special...

Excel: Printing nonadjacent cells in Excel 2003, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, paste link: I don t believe that s possible the way you re doing it. You could create a summary sheet with the two regions you want, and then print that. Just copy and paste-LINK the regions you want into a new sheet, and then print that summary sheet. Let me...

Excel: How do I preserve the '0's in text-number conversion?, cells, conversion

cells, conversion, conclusion: Brent, An example of one case, without a general problem statement, isn t clear enough for me to draw the general conclusion of what you want. If you want to make 0004.00 into 5350004.00, add 5350000 to 4.00 and you ll get 5350004.00. I m sure...

Excel: question on ranges, strikethrough, outl

strikethrough, outl, start 1: Manny, You haven t told me where to look to find the last row, so I will assume it is column M (which column will always have a value in the last row you want to fill to?). Then Sub GetPM() Dim lastrow as Long lastrow = cells(row.count, M ).end(xlup).row...

Excel: Random Selection of cells and values averaged., 6 cells, index function

6 cells, index function, ethen: Rahul I have only needed to do this once, and as far as I know there is no function in excel that automatically will choose randomly a range of consecutive cells. As I recall what I did was something like this. First I created a grid that represented...

Excel: Ranking formulae, excel formulae, format cells

excel formulae, format cells, relevant names: Using my original answer, change the D2 entry to =IF(CELL( row ,D2)=2,1,D1+IF(SMALL($C$2:$C$6,CELL( row ,D2)-1)=SMALL($C$2:$C$6,CELL( row ,D2)-2),0,1)) and the E2 entry to =VLOOKUP(SMALL($A$2:$A$6,CELL( row ,D2)-1),$A$2:$B$6,2,0) and copy down...

Excel: Recognising data fitting a mask, many different things, cell a1

many different things, cell a1, ogilvy: Claire, try this formula: =IF(LEN(A1=7),IF(ISNUMBER(RIGHT(A1,4)*1),IF(SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6,7},1)*1),{1,2,3,4,5,6,7}),1))=4, Match Mask , No ), No ), No ) lightly tested, but worked for me. -- Regards, Tom Ogilvy...

Excel: Referencing a cell beneath the vlookup function, vlookup function, specific solution

vlookup function, specific solution, request number: Steven, the solution is reasonably complicated, so let me refer you to a link which explains it with some examples and illustrations that should make it easy to implement - a lot more details than I could provide in this medium. Article by Ashish Mathur...

Excel: Reg: VBA Code, employment address, education column

employment address, education column, database column: Avinash, I would see adding code like this: Cells(Target.Row, 12).Value = Holdvar - this is already in your code, add these lines after Dim v1 as Variant, j as Long, i as Long v1 = Array( Education , Employment , Criminal , Database , Address ,...

Excel: Reg: VBA Code, target cells, column c

target cells, column c, tsd: Using the visual basic editor, enter the following code in the worksheet object: Private Sub Worksheet_Change(ByVal Target As Range) Dim rngColA As Range, rngColB As Range If Target.Cells.Count = 1 Then If Target.Column 3 Then ...

Excel: Removing zero values from columns to only show non-zero values in a different worksheet, ms excel 2000, zero values

ms excel 2000, zero values, maintenance cost: Josh, John Walkenbach documents a way to do this although in his example, he has one column of labels on the left (you have Name and User#). See if this method will work for you. He also has a link to a macro that will do it. I doubt it would work...

Excel: Reset a validation list that is in a protected worksheet, data validation, proper password

data validation, proper password, error msg: Yve, Try unprotecting the sheet in code and then protecting it Dim r As Range Dim r1 As Range Activesheet.Unprotect Password:= ABC Set r = Range( C10 ).EntireColumn On Error Resume Next Set r2 = r.SpecialCells(xlCellTypeAllValidation) On Error...

Excel: Retain Original Cell Value in Macro, loff, cc3

loff, cc3, rg: Stan, Just guessing here, but perhaps this is what you want. (you want to capture the values in the last row in F and G at the beginning of the macro and at the end of the macro you want to put those values back in the same cells - that is how I understand...

Excel: Retrieving data based on cell match, cell b1, sheet1

cell b1, sheet1, column c: James, I believe this is what you want: In D1 of sheet1 put in the formula =if(iserror(match($B1,Sheet2!$B$1:$B$265,0)), ,Vlookup($B1,Sheet2!$B$1:$C$265,2,False)) Now select D1 and drag fill the formula down column D for as far as you have data...

Excel: cannot reinstall Excel 2003, ms office professional, word attachments

ms office professional, word attachments, computer friend: Ivan, I cannot tell what might be wrong from the little information you have. This is a case where you might want to read the log file and see what it actually says. or, you can send this to me and I will take a look (but you must read it also). ...

Excel: Save importing file, vba procedure, shell application

vba procedure, shell application, excel2003: Insert the following code into your vba procedure: Dim selectedFolder As String Dim ShellApp As Object selectedFolder = Set ShellApp = CreateObject( Shell.Application ).BrowseForFolder(0, Please choose a folder , 0) On Error...

Excel: Search Across Worksheets, type in a keyword, that would allow users

type in a keyword, that would allow users, aidan: It s certainly possible, though I d need slightly more information to give you a working macro - are you searching for a sheet name, or the contents of a cell within the sheet? If the latter, is it all cells, or just a given column or set of columns. Are...

Excel: Shading Cells in Exccel, format painter, column format

format painter, column format, excel 97: You didn t specify version but as I m sure it s at least excel 97 that shouldn t matter too much - this can be done with format, conditional formatting - it would need a formula option - you say it should already be set up, so if so, you should see the formulas....

Excel: Skip a cell in a For Each Cell action, cell c5, cell c3

cell c5, cell c3, range selection: Mark For each next works as you state, so you can t use for each next. You could use a for next construct and make a decision on each cell, but I think a Do Loop is a better construct here. Sub abc() Dim r As Range, i As Long Dim xcell As Range,...

Excel: Sort a digit column by last 2 numbers, 6 digits, hidden answer

6 digits, hidden answer, a1: Say your list is in col A, beginning A1. In cell B1, enter =RIGHT(A1,2)+0 and copy down col B as far down as your data goes. Then use a sort of A:B, using B as your primary sort, to accomplish your tax. If you put the col B formulas in white font, then...

Excel: Sorting Groups of Rows in Excel, bob dole, sort key

bob dole, sort key, holiday hours: Mahala, The only way I can see to do this is to add two columns to the spreadsheet and use them to sort with. The first column to add is a column which repeats the employee name five times, thus creating a group which will stay together when...

Excel: StartBlink, excel sheet, excel worksheet

excel sheet, excel worksheet, animation: If you looked at the answer I think you are referring to (http://en.allexperts.com/q/Excel-1059/Animation-Excel-Worksheet.htm) you would see I mentioned using an event - I suggested using the open event - if you can clarify what the issue is I can possibly...

Excel: IF Then Statement with a total sum, sumif, richard florida

sumif, richard florida, n100: Sara You didn t give me your formula so I can t tell exactly how you have written it, but based on what you are saying I think your formula should look something like this =SUMIF(K1:N100, premier ,N1:N100) Check your formula and see if it does look...

Excel: If Statements, conditional test, cell references

conditional test, cell references, cell values: Barbara, Your question isn t clear. You made a nice little table of a & b, but then never referred to the b-part. I assume that what you really meant is this: How do you ask if a = x and b is less than 15, and a = x and b is greater than 15...

Excel: Strip numbers from text, isnumber, cell b1

isnumber, cell b1, cell a1: Kathy, =1*MID(A1,SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6}),1)),FIND( + ,A1)-SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6}),1)))+(1*MID(A1,FIND( + ,A1)+1,5)) in B1, then drag fill down column B. ...

Excel: Strip numbers from text, cell b2, isnumber

cell b2, isnumber, cell b1: Kathy, I accidently hit something that marked this a rejected, but here is the answer that worked for me. I put this in B1 =1*MID(A1,SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6}),1)),FIND( + ,A1)-SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6}),1)))+(1*MID(A1,FIND(...

Excel: SUMIF (Array), pivot table service, works microsoft

pivot table service, works microsoft, cell references: Hi Ravi, In order to categorize the times, please follow these steps: 1 - Insert a column in front of column U. This will move the current column U to column V. 2 - Starting in the first cell in column U, enter the following information (This adds the...

Excel: Sub Procedure question, rs1, sh2

rs1, sh2, table reference: The first problem I see is that you see rs1 to Nothing and then try to loop thru each cell in that NOTHING, which can t and wont work. Secondly, you don t tell the code that rs1 should reference the range name ISYES. Thirdly, you can t type a variable as...

Excel: Sum of Lookups, array formula, column names

array formula, column names, intermediate steps: Ctrl/shift/enter: =SUM(N(OFFSET(A2,0,MATCH(lookupnames,$B$1:$D$1,0))) * $B$27:$C$27) INDEX only returns one value; OFFSET returns multiple values. Oddity is that the N-function, which here does nothing, makes it work (without it you get #VALUE!) If...

Excel: Sum Question, array formula, curly brackets

array formula, curly brackets, blank cell: John You can use a Sum an If statement to get what you need. Type in the following assuming that C column starts at 2 and goes to 80 =SUM(IF(C2:C80 0,1,)*D2:D80) then press the Ctrl+Shift+Enter key to complete the formula (this is called an array...

Excel: saving VBA-created objects, command button, macros

command button, macros, boxes: Since I can not and have not seen your code and thus do not know how specifically you are doing this and/or WHEN they are being populated then it is real hard for me to answer. Clicking the command button does not permanently populate those controls. If...

Excel: search and load user form, vba code, combo boxes

vba code, combo boxes, firstchoice: You may ask multiple questions in separate posts, but please limit each post here to one specific question. I ll answer your first question here. Suppose that your data is on Sheet1, with Advanced, Advanced, Caterpillar etc in A1:A4, and Hazelhurst, Memphis,...

Excel: shading between two polynomial fits, pam pam, type appearance

pam pam, type appearance, signal amplitude: Pam, When you say you want to draw a shade area between THESE two lines , you can ONLY mean between the two data lines, NOT between the polynomial fit lines. Hmmm, I seem to be assuming that your chart consists of FOUR lines; the original two...

Excel: skipping rows of data, free microsoft excel, paleoclimate data

free microsoft excel, paleoclimate data, excel tips tricks: Kerry, this would be MUCH easier to do in a database program like Access. Excel is just not designed for this kind of thing. When you say you need to extract 11 rows what do you need to do with them? Copy them to another sheet? If so, I m sure a macro...

Excel: sorting out e- mails, e mail adresses, dropdown arrow

e mail adresses, dropdown arrow, pivottable: There are several options: A. Use the sort option (Each Column of Data must have a header) 1. Click on the database 2. Click on the Data drop down menu 3. Click on Sort This will bring up the Sort popup menu 4. In the Sort By section, click...

Excel: sorting letter-by-letter, test words, abac

test words, abac, word by word: Enter your test words in range A1:A4 like this: AB CD AB EF ABAC ABAF In cell B1, enter: =SUBSTITUTE(A1, , ) then copy this formula down through range B1:B4. Then sort A1:B4, using col B as your primary sort col (no headers). Col A will end...

Excel: If statement cell range, array formula, cell reference

array formula, cell reference, cell b1: Don, Your formula has an error, the second paren around the range should not be there. It should read: =IF(B1:B8= DOG , , X ) You also state the result inverted... you re thinking that a BLANK should appear (according to the formula...

Excel: if then statements from a drop down list that comes from range., free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Link your drop-down box to a specific cell, then use an IF statement to set the value of the second cell. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial...

Excel: stuck on excel formula :(, vlookup, excel formula

vlookup, excel formula, excel spreadsheet: If I read this right, it s not Vlookup, but HLookup that you want - that would look ACROSS the data until it comes to a date that you want, then return a value x rows down from that. today() is the function to return todays date so something like =hlookup(today(),YourHorizontalTable,2,false)...

Excel: stuck on excel forumula :(, vlookup formula, excel spreadsheet

vlookup formula, excel spreadsheet, last time: When you say ...put that data... , is that data in one cell? I other words, will you have just one cell in the other sheet that corresponds to today s date that you want to pull over into the other sheet? If so, then a VLOOKUP formula will work IF...

Excel: sum bill of material by assembly level, spreadsheet design, cumulative sum

spreadsheet design, cumulative sum, cumulative value: James, Ass a column that takes the cumulative value of all the rows where there is a 1 in the level column, and which puts in a zero when it encounters a zero in the Level. Write a simple formula that says: If there is a 1 in the Level column...

Excel: sumif with partial keyword, SUMIF, wildcard

SUMIF, wildcard: Hi Setiadi, If the first line is on row 1, the formula for cell D1 would be: =SUMIF($A$1:$A$5, * &C1& * ,$B$1:$B$5) The asterisk (*) character is a wildcard and in this formula, it looks for the column C value anywhere in the column A value, and it...

Excel: Time calculations, time calculations, time range

time calculations, time range, time hope: Hi Ravi, Here is more of an explanation on how to use the formula I wrote for the VLOOKUP. This will definiely work best because IF statements will become very confusing, especially with the limitations to nested IFs. =VLOOKUP(T1,$U$1:$V$7,2) What...

Excel: Time/conditional formatting, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: There are MANY ways to do this, but assuming you have your date/time value in A1, then set B1 to: =INT(A1) Now format that as a DATE field. That will give you the whole date (only) portion. Now what you have left over (the fraction) is the time. So...

Excel: Time difference calculation, cell a1, time column

cell a1, time column, train time: William, You need to re-create the times so this doesn t happen. make a new table according to the concept set forth below, and use the new times to perform your calculations. What you need to do is detect this condition. if the end-time is...

Excel: TRIM function problem, ascii character code, cell a2

ascii character code, cell a2, colums: If I read this correctly, it s simply a question of paste special, values - but possibly doing a search and replace on the original column would be a better way to go, or use the substitute worksheet function within your exact to remove these returns - eg...

Excel: Trouble with IF statement, data changes, aidan

data changes, aidan, c4: I don t know what the IF statement is doing, but if a macro is generating results, it should be easy enough- the counta worksheet function would give a number of rows, or test for a cell being blank and carry out the process whilst that condition is not true?...

Excel: Trying to copy a cell contents once validated, sheet1, cell contents

sheet1, cell contents, cells: A formula won t really do it, you need VBA code -- Assuming the sheet with all the data is on Sheet1 and the other sheet is Sheet2, then this macro will do what I think you want: Sub MoveRemove() Dim n As Integer On Error Resume Next With Sheets(...

Excel: Trying to insert a row and then manipulate data, worksheet selection, blank rows

worksheet selection, blank rows, blank row: Tejal, Test this on a copy of your worksheet. It did what I expected it to do, but I don t know if that is what you want it to do. It inserts the row and copies the data all in the same step so you only run the Insert_Blank_rows macro. Again,...

Excel: timesheet calculations, free microsoft excel, timesheet calculations

free microsoft excel, timesheet calculations, excel tips tricks: It s not just ONE formula, it s knowing how to set up the timesheet. I cover timesheets specifically in my Excel 232 course: http://www.599cd.com/site/courselist/excel/excel232?key=AllExperts However, the following tutorials might help you as well:...

Excel: Use VBA to copy sheets between workbooks - "Subscript out of range", source cells, source copy

source cells, source copy, range error: Set xlw_source = Excel.Workbooks(ResultSheet) is basically fine, SO the contents of ResultSheet cannot equate to a currently opened workbook at the runtime, hence the error - I d try recording a macro to do something similar and that would give you an...

Excel: Using Excel interface to execute an external program, shell command, vba code

shell command, vba code, excel sheet: Depends on how you want EXCEL to launch the program but you can use the SHELL command in VBA to launch an external program. WHERE you put this code will be what you need to decide. You can use the Workbook_Open even to store the code, which means the code...

Excel: Using VBA to replace names, left hand column, english muffins

left hand column, english muffins, number of names: Aaron, I would put this table in one sheet, call it sheet Table with data in A:B in your data where the names need to be replaced, I would insert a column next to those names. Assume the names are in column C so I would insert a new D. In D I would...

Excel: How to update column, cell b1, iteration count

cell b1, iteration count, cell a1: Stephen, It s very difficult to do this, since it requires that Excel have a memory of the previous values that led up to the number. What you re asking is for Excel to remember that you typed in a 5, and to retain the value of 105 when you take the...

Excel: using excel to download files from sharepoint/ websites, analysis spreadsheet, mybook

analysis spreadsheet, mybook, vba macro: downloading files is the same as opening them, so the VB command to open a file would be the way to go - and referencing sheets is easy enough dim mysh as worksheet set mysh=activeworksheet dim mybook as workbook set mybook=workbooks.open( YourFilePath...

Excel: VB blanks and such, c cells, copy worksheets

c cells, copy worksheets, blank rows: Jason, This is what I understood you to want. Test it on a copy of your data. Sub Update() Update Macro Macro recorded 8/18/2009 by Administrator Sheets( Domains_about_to_expire ).Select Cells.Select Selection.Sort Key1:=Range(...

Excel: VB copy/ transfer, gtrd, c cells

gtrd, c cells, srow: Ray, I understand you want to go to each of 4 different sheets and then run the macro to have the data on that sheet copied to mastersheet in the next available row. then you just need to change how drow is initialized. Instead of drow = 1 use...

Excel: VBA, copy worksheets, header row

copy worksheets, header row, ogilvy: Jason, Sub ABC() Dim r As Range, r1 As Range Dim cell As Range With ActiveSheet Set r = .Range(.Cells(2, A ), .Cells(Rows.Count, A ).End(xlUp)) End With For Each cell In r If Len(Trim(cell)) 0 Then If cell.Text = 1/1/1900 Then ...

Excel: VBA Code Help, spath, excel template

spath, excel template, filesystemobject: If I understand your question correctly, what you re trying to learn is to use vba to determine the file within a folder that s been most recently saved. Here s some sample code, which you should be able to customize to your needs. This sample code stores...

Excel: VBA code to capture data from Windows Explorer to Excel, excel template, hi bob

excel template, hi bob, sheet1: Actually, it doesn t clarify it for me. What s your original template ? What s its name and how is it relevant? How is it known what new file(s) are in the folder? How is that relevant? Can you be a lot more specific? Why isn t what I gave you going to work...

Excel: VBA code to copy data from a workbook that has password protection., command button, pastes

command button, pastes, wb: Jill If you are selecting in that worksheet and those cells are locked and protection doesn t allow selection of locked cells that could be a problem. I believe you said the sheet was/is hidden, so you can t select a hidden sheet. The solution might be...

Excel: VBA Excel Interior, target cells, cell a2

target cells, cell a2, vba excel: Below is some sample code. If entered to the Worksheet object, using the Visual Basic Editor, then it ll be triggered whenever user makes a change to a single cell in col A of that wks. It will turn cols A:W in that row to Red if the col A entry is AA; Yellow...

Excel: VBA/Macro help, array array, old fashion way

array array, old fashion way, computer locations: Why not just use a formula? =MID(A1,FIND( ,A1,1)+1,FIND( ,A1,FIND( ,A1,1)+1)-FIND( ,A1,1)-1) The formula finds the position of first and then the position of the second . It then uses that data to extract or pull out whatever is between...

Excel: VBA match and copy cells., wb1, wb2

wb1, wb2, sheet1: Sub Untested() dim N as integer, WB1 as Workbook, WB2 as workbook Set wb2 = Workbooks( 2ndWB.xls ) change accordingly Set wb1 = Workbooks( 1stWB.xls ) also. On Error Resume Next for i=1 to wb2.sheets(1).range( A1 ).end(xldown).Row n=0...

Excel: VBA - Replace Cell Value Each Month, valus, number changes

valus, number changes, cell address: Dave, Here is the revision: =IF(A1 IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)) 6,13-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),6-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)))+14+DATE(YEAR(A1),MONTH(A1),1)-2,MONTH(A1),MONTH(DATE(YEAR(A1),MONTH(A1)+1,1))) -- Regards,...

Excel: VBA ranges & Pivot Tables, pivot tables, labels

pivot tables, labels: Well, it wouldn t be a button because that would also reside on the workbook which contains the macro. Probably the easiest is to assign a shortcut key, like ctrl/Shift/G or something. You can press Alt/F8, Select the macro, click Options, put in the assignment...

Excel: VBA Script, stm 1, paris 1

stm 1, paris 1, script thanks: Still confusing. How do you go from PARIS.1, LineWest-A Slot 302 STM-1 1 1-2-6-1 - Trib3 Slot 306 2 Mb/s 16 2M (active) to PARIS.1, Trib3 Slot 306 2 Mb/s 16 ?? Why does this section: STM-1 1 1-2-6-1 - Trib3 Slot 306 disappear?? You...

Excel: VBA script, stm 1, 2m

stm 1, 2m, a2: Solay, If you meant the original data is on sheet2 and you want to process it in place there then: Sub ABC() Dim r As Range, cell As Range, s As String Dim s1 As String, v As Variant, v1 As Variant Dim iloc1 As Long, iloc2 As Long, iloc As Long With...

Excel: VBA for sorting excel with color, yellow cells, rng

yellow cells, rng, excel macro: Tanya, In Excel 2007, they added the ability to sort on color. They also added a SORT object. In the code you first show, that is the sort method of the range object and it doesn t support the new functionality - you have to use the sort object. This...

Excel: VLOCK in excel, vlookup formula, vlock

vlookup formula, vlock, good answer: Alex I have not done this before and I have not been able to figure it out very quickly. The Vlookup formula should work, but pictures are treated differently in excel and I can t get it to work for me either. Rather than waiting for me to figure it out...

Excel: Vlookup Formul, follow ups, formul

follow ups, formul, aidan: I m not 100% sure I have enough information here - how is the data to be imported? Is it being imported to the one master sheet already? Are we to use macros or formulas (especially confused as you mentioned vlookup but it doesn t sound a lookup question?)...

Excel: vba code find cell value, lmax, max value

lmax, max value, column c: C, This code will select the max value in the specified column. In the example, that is column C (the 3 in columns(3)) - change to suit. Sub FindMaxinaSingleColumn() Dim r As Range, res As Variant Dim r1 As Range, lmax As Double Set r = Columns(3).Cells...

Excel: vba conditionally delete rows, absolute reference, b2

absolute reference, b2: Set currentCell = Range( B2 ) Do While Not IsEmpty(currentCell) Set nextCell = currentCell.Offset(1, 0) If InStr(cell.Formula, $F ) + InStr(cell.Formula, $G ) + InStr(cell.Formula, $H ) + InStr(cell.Formula, $J ) + InStr(cell.Formula, $M...

Excel: vba script, ubound, ogilvy

ubound, ogilvy, instr: The second script fails It runs fine for me. It may not give you exactly what you want, but it didn t fail for me using all the test data you have provided. Here is a revision that may help. It produces the output you say you want except it removes...

Excel: vba script, ph5, ocb

ph5, ocb, rh: Solay, This worked for me with your test data. Sub ABC() Dim r As Range, rw As Long Dim s As String, s1 As String, v As Variant Dim l As Long Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) rw = 1 For Each cell In r If InStr(1, cell.Value,...

Excel: vba search for numbers, vbyesno, digit numbers

vbyesno, digit numbers, nbr: Sub SearchNbr() Dim Nbr As String, FirstResult As String Dim FindNext As Integer, i As Integer Dim sheet As Worksheet Dim looped As Boolean Dim result As Range On Error Resume Next Nbr = InputBox( Please enter a 2-digit Number ) If...

Excel: Week no./procedure matrix, cell b2, column headings

cell b2, column headings, rev level: Richard, I still don t understand the nature of your table. You have three ROWS per procedure? And they are called Procedure name , revision level , and hours taken? It sounds as though only one of the three rows will have data across...

Excel: Wirting a Macro, macro book, dde link

macro book, dde link, sheet tab: Bud Horrell, How are you running your flashing program now? You would just run the wav playing program there as well. If you used one of my suggestions then (since you say it is streaming and that doesn t really tell me much, but I will assume it is DDE...

Excel: Workday if function, holiday range, g18

holiday range, g18, bank holidays: I would suggest the easiest way is to test your end of month date against the holiday list =IF(COUNTIF(G10:G18,eomonth(B9-DAY(B9),1)) 0,workday(eomonth(B9-DAY(B9),1),1,G10:G18),eomonth(B9-DAY(B9),1)) where G10:G18 is the holiday range and B9 is the...

Excel: 3 dimensional constant values, relative reference, absolute reference

relative reference, absolute reference, anchor: I don t follow what it is you are trying to do - unless you simply mean you want to select a cell on another worksheet - if so, the EASIEST way is to do it by clicking rather than typing - after entering the = excel knows that you are entering a formula, so...

Excel: Adding cells in different Workbooks, switch windows, task bar

switch windows, task bar, ribbon: Sorry, should have been clearer - I was referring to the one on the task bar, but I forgot that I m now using winows 7 - possibly a better way is to use the ribbon and click on the view ribbon,then switch windows - this gives a list of open workbooks...

Excel: Adding rows and automatic merging, spreadsheet, cells

spreadsheet, cells: I m not sure I follow this, especially as you say you want to sort and filter - having a note in a row BELOW data would surely mean that when sorting or filtering your note could end up out of position? It would be more logical to have a comments column at...

Excel: Adding a sroll bar in a chart in Excel 2007, scroll bar, axis

scroll bar, axis, good luck: Hi Sami, So sorry - I accidentally clicked on the automatic Thank you note option instead of emailing you back. I wanted to tell you that I am unable to answer this question. I did find this website though that has some useful information on adding...

Excel: Adding Time to a Date & Time, analysis toolpak, cell b1

analysis toolpak, cell b1, adding time: In Excel menu, choose Tools, Adds-In, and make sure Analysis ToolPak is checked. Without this add-in Excel does not recognize the function WORKDAY(). In H4:H17, enter holiday dates. In A1, enter 11-Sept-09 8:30 (request date/time). In cell B1, enter...

Excel: Advance Filter, combo box, aidan

combo box, aidan, search terms: If it s a macro, I d probably get it to set the row height to standard for matched cases and zero for non matched ones - at which point the process is easy - though I d want to have some known value seperate the search terms (or ideally have them in a list)....

Excel: Alphabetizing, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: That s easy. Just select all of your data (so if you have 3 columns and 10 rows, make sure it s all selected). Now click on Data Sort and choose which column you want to sort by. Let me know if you have any other questions or comments. Also, be sure...

Excel: Analyzing a column in a large data set, onecell, quick reply

onecell, quick reply, text strings: Sub cleanUp() Dim wksFrom As Worksheet, wksTo As Worksheet Dim rngFrom As Range, rngTo As Range, oneCell As Range Set wksFrom = ThisWorkbook.Sheets( Sheet1 ) Set wksTo = ThisWorkbook.Sheets( Sheet2 ) wksTo.Cells.ClearContents ...

Excel: Array Formula, array formula, precise answer

array formula, precise answer, pivot table: Difficult to give you a precise answer here as I don t know the ranges, AND for three countries I might be inclined to do something like =sumif(FirstSet)+sumif(SecondSet)+Sumif(ThirdSet) (I ve simplified the structure to give you the idea - this isn...

Excel: Array might be needed?, column c, 2 names

column c, 2 names, many names: Press Alt/F11, use Insert/Module, copy this in: option compare text Function Countx(Rg As Range, ColAVal As String, ColCVal As String) As Integer Dim n As Integer, Found As Boolean For i = 1 To Rg.Columns(1).Cells.Count If Rg.Columns(1).Cells(i)...

Excel: Attach particular sheet and mail through lotus notes 8.5 / gmail, open mail, owb

open mail, owb, smsg: Modify your routine like this: Sub SaveSheets() Dim oSh As Worksheet Dim oWb As Workbook Dim vFilename As Variant Dim sPath As String sPath = c:data For Each oSh In ActiveWorkbook.Worksheets oSh.Copy Set oWb = ActiveWorkbook...

Excel: Autofilling dynamic data in a column, correct sequence, question thanks

correct sequence, question thanks, dynamic data: 2 separate macros: Sub EnterFormulas() For Each x In Sheets x.Activate Columns(4).Resize(, 3).Insert lrow = Range( A65000 ).End(xlUp).Row Range( D1 ).Resize(lrow, 3).FormulaR1C1 = =rc[-3]/10000 Next...

Excel: Automatic data extraction?, intersection cells, empty worksheets

intersection cells, empty worksheets, cell a2: ---(Sheet1!B:B) - Why B:B and not $B:$B or B2:B100? Could have been B:B or $B:$B, referring to the whole column, either works since you re filling DOWN, not RIGHT. However, B2:B100 would fill down and change to B3:B101, then B4:B102, etc. so if you DID...

Excel: Automatic Highlight, cell c1, conditional formatting

cell c1, conditional formatting, format feature: Leanne You can use the conditional formating feature to do wht you want. It is found on Toolbar/format/conditional formatting. In the pop up box select formula is and put a formula in that will do the calculation indicating whether your date is beyond...

Excel: activate/deactivate macros, keyboard shortcut, custom toolbar

keyboard shortcut, custom toolbar, aidan: both are possible, and both would need ANOTHER macro to achieve (although it would be the same macro)- what I don t know is what CURRENTLY activates it - I m GUESSING the open event of the workbook - effectively you would need to move the code from that location...

Excel: adding columns in a pivot table, row labels, column labels

row labels, column labels, region managers: Not sure i can help if Excel says too many calcs, but if you can send me the (large) wb that illustrates the issue + your detailed instructions how to replicate the message, I ll see if I can come up with a workaround. Send to me (maybe zip it) at bobumlas@yahoo.com,...

Excel: assigning values to letters in excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: Sure. You could make another column to change Y into 1: =IF(A1= Y ,1,0) And then do the same thing for N. Or, you could just count up all of the Ys and Ns as they are using COUNTIF. See: http://www.599cd.com/tips/excel/sumif-countif/?key=AllExperts...

Excel: auto increment by 1, auto increment, invoice number

auto increment, invoice number, cell a1: I see you already tried to do the increment using this line: r.Value=r.Value+1 But you do not tell VBA what r is. To assign a cell to a variable, you must use this syntax: Set r=Range( A1 ) So if the invoice number is in cell A1, you can either:...

Excel: Backup of an excel file, options window, trying to find a way

options window, trying to find a way, xls: First of all I am not Bob but regardless, yes, this will work in 2007. However, just saying ...but I can t seem to get it to work... does not help me a lot. This is like only telling a doctor I am sick and then expect him to make you better from only...

Excel: Basic Excel Vba Help, excel vba help, layout location

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

Excel: ByRef type argument mismatch when trying to generate new array, spreadsheet index, depth column

spreadsheet index, depth column, irow: Nick, ============================== You can t upload a file to allexperts. You can contact me direclty at twogilvy@msn.com Sep 21, 2009 (Monday) ============================== This worked for me: Function depArray(reportDate As Date, lithArray...

Excel: Calculate horizontally to end, j5, c5

j5, c5, intellect: Excel luckily doesn t go to infinity so the easiest fix would be to enter the last possible column value - prior to 2007 this would be =sum(k5:IV5)-c5 Blank (non created) cells would not be summed. If you insert entries, you don t need to have the formula...

Excel: Calculating cells with a specific colour and text, colorindex, cell interior

colorindex, cell interior, color index: Dunc, I can add a argument for the text you want to count and then it will return the count for the combination of color and text: Function CountColorText(R As Range, ColorIndex As Integer,s as String) As Long Returns a count of the number of cells...

Excel: Calculating number of days in a date range, date function, month of january

date function, month of january, ogilvy: Danielle, Assume 3/31/09 is in cell J6 then if you want to know the total number of days in that month and the two previous months you can use: =j6-Date(year(j6),Month(j6)-3,0) to explain: Date(year(j6),Month(j6)-2,0) Month(j6)-2 would...

Excel: Calculating unknown future date from a given date excluding weekends and holidays, free microsoft excel, custom vba function

free microsoft excel, custom vba function, question sign: There is no function that will automatically ignore holidays. You will need to write your own function. Here s a tutorial that will help you: Excel makes it easy to count the number of WORK DAYS between two dates. You can use NETWORKDAYS if you only...

Excel: Calculating variables across columns, array formula, sumif

array formula, sumif, statuses: I think I understand. You can use the Conditional Sum Wizard to help you build the formula to sum these items and then change the SUMIF to COUNTIF. The Conditional Sum Wizard is an add-in that helps you build the array formula you will need, IF I understand...

Excel: Calendar, mr abc, column c

mr abc, column c, c lab: Your way of working isn t very effective from Excel s point of view :-) Excel has no built-in formula or function to determine if you have merged cells. I would not merge cells to indicate when a trainer is scheduled. Instead I would put 1 s or x s in each...

Excel: Calendar, mr abc, column c

mr abc, column c, three sheets: Bhavanh Sorry I am having difficulty wading through the information to determine the questions/answer. Having said that--one answer might be to use linking formulas to the worksheets maintained by the others to add the changed data to your worksheet. ...

Excel: Cell Colour Query, colored cells, blue cells

colored cells, blue cells, cell entries: Have cell B1 be the active cell. That is, select cell B1. Then, Excel 2003: Insert/Name/Define Use any name, but I suggested: TheColor in the Refers-To box, type =Get.Cell(24,A1) Excel 2007: Formulas Tab/Define Name Use any name, but I suggested:...

Excel: Cell copy, last question, b8

last question, b8, len: The answer to your question is to enter in cell B8: = S & RIGHT(B7,LEN(B7)-2) Also, you might consider reworking your sheet so that F is in one cell and 10:36 is in a different cell. That way, Excel could recognize 10:36 as a time; Excel won...

Excel: Cell Option, data validation, drop down list

data validation, drop down list, c2: Rahul, The first thing to do is make a Y-detector. Then, apply its result to cause some action to occur. To make Y-detector, you need an OR of all 9 cells, like so: =OR(B1= Y ,B2= Y ,B3= Y ,.... etc) this will be TRUE is any of...

Excel: Cell Referencing, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: If you insert rows or columns INSIDE of the range Excel generally increases the references to that range for other sheets IN THAT WORKBOOK. Of course, the best way to find out for sure is to TRY IT and see. Let me know if you have any other questions...

Excel: Cell ref autofilter on protected sheet, target value, target address

target value, target address, autofilter: James, Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Address = $F$3 Then Set r = Me.AutoFilter.Range me.Unprotect Password:= whatever it is If Len(Trim(Target.Value)) 0 Then r.AutoFilter Field:=1, Criteria1:=Range(...

Excel: Cell references - THANKS RICHARD, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use the OFFSET function. =(B1,35,0) This says go 35 rows down and 0 columns across from B1. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL....

Excel: Change colour of cell when expiry date is greater than, credit card expiration, conditional formatting

credit card expiration, conditional formatting, mm yy: You have a column of credit card expiration dates that are TEXT in the format MM/YY. If they re all exactly MM/YY you can do this: Assuming the EXP date is in A1, put this in B1: =LEFT(A2,2) That gives you the MONTH. Now put this in C1: =RIGHT(A2,2)...

Excel: Check if a cell is blank - before checking if another cell has an error, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Sure. Just use the ISBLANK function and nest the IF functions. =IF(ISBLANK(A1),0,IF(ISERROR(A1), Oops ,A1)) Easy enough? Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: Clear duplicates below cell, lrw, advanced auto

lrw, advanced auto, rw 2: Stephen Douglas Test this on a copy of your data. It works from the Active Cell down to the last filled cell in the column in the column of the ActiveCell when you run it. So if you want to process the whole column, select a cell in the first row. If...

Excel: IF CONDITION, format cells, coloum

format cells, coloum, dear mr: In A2 enter YLY In B2 enter 15-Jul-03 Select col D. From menu, choose Format, Cells, Number, Category: Date, Type: Mar-01, OK. In D2, enter =DATE(YEAR(B2),MONTH(B2)+IF(A2= YLY ,12,IF(A2= QLY ,3,IF(A2= HLY ,6,0))),DAY(B2)) The formula will...

Excel: COPY FORMULAS BETWEEN WORKBOOKS - FILTERED, auto filters, row numbers

auto filters, row numbers, exact sequence: Roger, Before you copy or filter the data, put in =row() in the next available column. Then copy the formula down. Select the column and do Edit= Copy, then Edit= Paste Special and select values. How you have the original row number as a constant....

Excel: COUNTIFS function, criterias, formula one

criterias, formula one: Pelambre You can do it with a static number such as your =10 but not with the sign as excel doesn t recognize that in a cell, it turns the value to text. Have a look at this link http://www.mrexcel.com/archive/Formulas/16665.html Richar...

Excel: Code needed, free microsoft excel, blank cells

free microsoft excel, blank cells, question sign: You can LOCK cells and turn on SHEET PROTECTION, but you would have to manually lock the cells yourself between users. Personally, I would probably do this in an Access database. Let me know if you have any other questions or comments. Also, be sure...

Excel: Combine 2 rows in excel, cell1, customer numbers

cell1, customer numbers, customer number: Chris, You did say A3 didn t you. I forgot that when I went off to develop the code. Here is the revision to start at A3: Sub fixdata() Dim r As Range, cell As Range Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range Dim cell1 As Range, res...

Excel: Combine Multiple Rows of Data, free microsoft excel, allexpert

free microsoft excel, allexpert, question sign: And spreadsheet with more than a few hundred rows should be moved to a database. I would recommend using the SUMIF function in a summary table. Just make another table that says: =SUMIF(B1:B100, =12 ) Do this for each of your data items and it will...

Excel: Combobox (activeX control), c values, time robert

c values, time robert, space character: ROBERT, I ran this code and had no problem: Private Sub ComboBox2_Change() ComboBox2.List = Array( A , B , C ) If ComboBox2.Text = A Then [63:76].EntireRow.Hidden = False Range( b63:b76 ).Value = Range( h63:h76 ).Value ...

Excel: Comparing cells and moving data between sheets, cell1, blank cell

cell1, blank cell, sheet1: Kyle, Set r1 = .Range(.Cells(10, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With With Worksheets( sheet2 ) Set r2 = .Range(.Cells(7, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With Both column references in a particular sheet should be the same - so...

Excel: Complex concatenations using intermediary tables, array formulas, presentation table

array formulas, presentation table, entry tables: Assume that the dates are in col J (the first col of addArray) and additives are in col K (2nd col of addArray): Function strAdd(startDate As Date, endDate As Date, addArray As Range) as String dim iRow as Integer dim rngDate as Range, rngAdditive...

Excel: Complicated Sum, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Here s what I would do: replace your tick with a 1, and leave the cell blank (or 0) for no tick . Now, just SUM up that column and you re all set. If you really want to keep your tick marks, then use the COUNTIF function. You can use the SUMIF...

Excel: Conditional Formula in a VLookup, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: How about: IF(X1=0, ,X1) In this example, X1 is your existing VLOOKUP statement. In other words, if the cell contains a zero, replace it with a blank . Let me know if you have any other questions or comments. Also, be sure to visit my web site...

Excel: Conditional IF statement over data range, array formula, tim ward

array formula, tim ward, luis ramirez: Your formula would do it IF you entered it over 16 rows(ie select 16 rows FIRST then start entering the formula, but instead of using the tick or pressing enter, press Ctrl Shift enter to make it any array formula) - BUT you would get 16 results returned which...

Excel: Conditionally Delete First Characters, cell entries, excel microsoft

cell entries, excel microsoft, typical entry: I would PROBABLY do this with vba - a sample of that is here http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.newusers/2008-09/msg00508.html I m happy to create a custom function for you that does something similar - my email if it helps...

Excel: Connecting a diagram to a table, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can refer to values on another sheet as: =SheetName!A1 Where SheetName is the name of the sheet, and A1 is the value cell (or range). In your case, when you create the diagram/chart/whatever just tell the Wizard where the data is, then select a different...

Excel: Consolidation formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You could say: =IF(A1=0, ,A1) This would display a BLANK if A1 is zero. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Copy cell from many sheets to one sheet, case sensative, false application

case sensative, false application, data sheets: This error appears when sh is a worksheet with a name that does NOT appear at all (case sensative) on the weekly votes sheet. For instance, when sh.name is weekly votes itself, if there isn t a cell on the sheet weekly votes that has precisely the value...

Excel: Copy paste Worksheets with conditions., vba code, tab sheet

vba code, tab sheet, sheet1: Hi Jack, I believe the following code does what you describe: Private Sub Workbook_Open() Copies MPR and Form64 sheets to MPR 2009 and Form64 2009 workbooks the first time this workbook opens in a given month. Dim LastOpened ...

Excel: Copy similar data (specific columns) from multiple excel files into one, u50, u10

u50, u10: I don t understand. What do you mean by ...a different excel. A new workbook? Is there any matching of data between TicketDetails and RawData? How? Are you wanting a new workbook with 2 sheets (TicketDetails and RawData) which contains ALL the other workbooks...

Excel: Copying cells after a filter, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Once you apply a filter, you can t copy and paste it, but you can select the cells and then Autofill to the right one column. That should copy over JUST the records in the filter. You should, however, SERIOUSLY consider using a DATABASE like MS Access any...

Excel: Copying cells after a filter, left arrow, paste command

left arrow, paste command, alpha bravo: easiest way to do this is to use a blank column - in which you enter =if(a2= Bravo ,a2,b2) (this assumes row 2 as I m sure you realised) Drag this down, then copy the results and click on B2 - then paste special, values. You can now delete the formula...

Excel: Copying columns but admitting certain values., blank worksheet, randoms

blank worksheet, randoms, random number: a couple of options - firstly, data, filter, autofilter - then choose custom and select does not equal 0 - this will give you a list which you can copy but the data won t be continuous and you d have to reapply the filter each time you wanted a list. secondly,...

Excel: Copying by dragging down formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You need to use ABSOLUTE REFERENCES in your formulas. A1:B1 should be $A$1:$B$1 Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Copying Order of a Formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Don t use copy and paste. Use AutoFill and give it enough to see the pattern. For example, if you type in the following into cells A1, A2, and A3: 3, 5, 7 Now select all three cells and AutoFill them across the sheet, Excel will see the pattern and...

Excel: Copying & Pasting (top down format to left to right format), sheet names, z1

sheet names, z1, z2: You can use a formula like this -- first select A1:Z1, for example, in the summary sheet, enter =TRANSPOSE(Sheet1!A1:A26), then enter it with Ctrl+shift+enter Note the 26 is because you selected 26 columns. Adjust as necessary. For the 2nd row you d first...

Excel: Countif Formula - I think, excel, countif

excel, countif: You are right, you can use COUNTIF. For example: Greater than 0 but less than 5: =COUNTIF(A1:A50, 5 )-COUNTIF(A1:A50, 0 ) Greater than 0 but less than 10: =COUNTIF(A1:A50, 10 )-COUNTIF(A1:A50, 0 ) Greater than 10: =COUNTIF(A1:A50, =10 ) Hope this...

Excel: Counting Blank Cells..., blank cells, sumproduct

blank cells, sumproduct, peson: If I follow this correctly, it s basically a count IF requirement - you are counting IF it s the right person AND its blank - you can do this using SumProduct - =SUMPRODUCT(--(A1:A4= a ),--(ISBLANK(B1:B4))) where A1:A4 relates to your name data, a...

Excel: Counting in multiple columns, array formulas, excel user

array formulas, excel user, score: =sumproduct(--($A$2:$A$10= A ),--($B$2:$B$10=80)) for the first part and =sumproduct(--($A$2:$A$10= A ),--($B$2:$B$10 =70))+sumproduct(--($A$2:$A$10= B ),--($B$2:$B$10 =70)) for the second part - NOTE that as this stands it includes the first part....

Excel: Counting number of times a combination appears in different rows, sumproduct, ogilvy

sumproduct, ogilvy, b1: Luke list of values in column A value 1 (in B1), Value 1 (in C1), Formula (in D1) this is the formula =IF(B1=C1,SUMPRODUCT(((LEN($A$1:$A$30)-LEN(SUBSTITUTE($A$1:$A$30,B1, ))) =2)*1),SUMPRODUCT(ISNUMBER(FIND(B1,$A$1:$A$30))*ISNUMBER(FIND(C1,$A$1:$A$30))))...

Excel: Creating a VBA script to change the drop down selection of a pivot table, target text, target value

target text, target value, set target: Cornell, that is what this code does. You right click on sheet2 and select view code. then put in this code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub MsgBox...

Excel: Cross referencing occurances., SUMPRODUCT

SUMPRODUCT: Dominika: This can be done with the SUMPRODUCT formula. Based on your example I suggest the following formula: =SUMPRODUCT((A1:A10= attack )*(E1:E10= right )*(1)) Of course the above formula assumes only 10 rows of data - thus, just expand the formula...

Excel: calulating overdue and underdue dates, cell references, column c

cell references, column c, magic number: Gareth, You can perform easy arithmetic on dates. Dates are stored as special serial numbers , as Excel calls them. But don t be afraid! This magic number is simply the number of days since an initial (and fictitious) date: Jan 0, 1900. So as of...

Excel: If cell A1=A then delete data in cell B2 and repeat, cell b2, cell a1

cell b2, cell a1: Tyr this: Sub Delete_Data() Dim X As Long Dim Counter As Long On Error GoTo Err X = 1 Do Until Range( A1 ).Offset(X - 1, 0) = If UCase(Range( A1 ).Offset(X - 1, 0)) = A Then Range( A1 ).Offset(X - 1, 1).ClearContents ...

Excel: cell reference in VBA, relative cell reference, point thanks

relative cell reference, point thanks, target: John, If you are using the change event (and it sounds like you are - if not, you should be using change, not selectionchange), then Target holds a reference to the cell that triggered the event. So you want to use arget instead of Activecell --...

Excel: change cell color when, conditional formatting, a12

conditional formatting, a12, a5: You are on the right lines - under conditional formatting, choose the FORMULA option - this then allows you to use a formula to dictate the condition =$A$12 $A$5 This condition will evaluate to TRUE if a12 is greater than A5 and therefore the formatting...

Excel: checking conditional formatting, conditional formatting in excel, sanity check

conditional formatting in excel, sanity check, allexperts: I m sorry, but I know of know utility which will list all the conditional formatting settings in a workbook. I can probably write such a routine but that d be beyond the scope of AllExperts and I simply don t have the time to dedicate to writing it -- it would...

Excel: comapring columns and output out of that in a same cell, visual basic for applications, unique records

visual basic for applications, unique records, t1 t3: Sub makeOutput() Dim rngFrom As Range, rngTo As Range, rngCheck As Range Dim strOutput As String Set rngFrom = Range( a2 ) Set rngTo = Range( d2 ) rngTo.Range( a1:b1 ).EntireColumn.ClearContents Do Until rngFrom = If IsError(Application.Match(rngFrom,...

Excel: combining a group of rows into one cell, apple orange, alice jane

apple orange, alice jane, bval: Here s a macro to do it: Sub Combiner() n = 1 BVal = Range( B1 ).Value Range( c1 ).Value = Range( A1 ).Value & For i = 2 To Range( A1 ).End(xlDown).Row If Cells(i, 2).Value = BVal Then NextOne: Cells(n, 3).Value...

Excel: combo box list, variant array, lct

variant array, lct, combobox: Hi Chris, Yes you can do that, but you would need two controls: a textbox and a listbox or combobox. You use the change event of the textbox to add the items to the list. I have a form with TextBox1 and ListBox1 and use this code to handle things: ...

Excel: comparing two worksheets and merging when matches are found, asking this question, aidan

asking this question, aidan, email: The COUNTIF worksheet function would enable you to determine if one value exists in another location, and vlookup would bring the data across - once done, you can replace the formulas with values. =if(countif(SecondSheetRange,YourCell) 0,vlookup(YourCell,SecondSheetRange,2,false),...

Excel: complicated lookup, logical tests, shipping invoice

logical tests, shipping invoice, shipping zone: You can do this with a trick using SumProduct which allows a multiple condition sumif(which if there is only one possible match becomes a multiple condition lookup) =sumproduct(--(ShippingService=type),--(shippingzone=zone),--(packageWeight=weight),rate)...

Excel: conditional pop-up messages, target value, data validation

target value, data validation, sheet tab: Sandy, I don t see where data validation could do this isnce you would have to have an invalid value to raise the message and then it wouldn t let you enter the value. You could do it with a macro right click on the sheet tab and select view code...

Excel: connection between workbooks with VBA, bk1, vba

bk1, vba, ogilvy: Kuba, If there are only two workbooks, then I guess one workbook contains the code. For that workbook, you can get the name and path with Thisworkbook.Name thisworkbook.path or to get both together thisworkbook.Fullname ThisWorkbook is a predefined...

Excel: converting letters to numbers, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You wouldn t have to alphabetize it if you use the fourth VLOOKUP parameter. If you set it to FALSE, then VLOOKUP will find an exact match and the list doesn t have to be alphabetical. Let me know if you have any other questions or comments. Also, be...

Excel: converting numbers in Excel, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Absolute Value =ABS(A1) Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts Cordially, Richard...

Excel: copying sheets/formulas, self reference, a10

self reference, a10, thanks in advance: probably the easiest fix is to do what you are doing, then edit, links and change the source - to the destination file, so it becomes a self reference (and therefore ceases to be a link) Sorry for the delay in replying - somehow your question got lost in...

Excel: Data merge, ogilvy, data sheets

ogilvy, data sheets, data sheet: Hello Marty, You can use vlookup. Say you want the post code to appear in column AC in Sheet2 (it can be any column, but I will use AC for example) in Sheet2 cell AC2 put in this formula =IF(ISNUMBER(MATCH($B2,Sheet1!$B:$B,0)),VLOOKUP($B2,Sheet1!$B:$AC,28,FALSE),...

Excel: Data validation for multiple criteria, cell b2, data validation

cell b2, data validation, revenue assurance: Please send me a sample wb so I can show AND explain rather than just explain which is more difficult -- send to me at bobumlas@yahoo.com, use subject of AllExpertsQ and I ll send back your solution with explanation. Please repeat what needs doing...

Excel: Decimal places based on integer in cell, conditional formatting, format button

conditional formatting, format button, hermanson: Ed, I don t know what Bill Hermanson provided in his answer, so I can t comment on his solution. I applied conditional formatting to cell H6 and made the number of decimal places dependent on the value in I6. You can only do this in Excel 2007 as I...

Excel: Default file location in Excel, application programming interface, microsoft knowledge base

application programming interface, microsoft knowledge base, location changes: The MAPI folder is supposed to be their for mail processes (messaging application programming interface) but that doesn t explain why Excel would be looking their - possibly a setting in the startup location, or possibly an add-in misbehaving? Other than...

Excel: Delete rows where their Column B entry = 00000, false selection, rowcount

false selection, rowcount, fileformat: I am curious as to why you did not get IT Services, which is who recorded this macro for you, to fix what they did. Regardless, based on the info you provided: Dim R As Range Dim X As Integer Dim RowCount As Integer Dim FirstC As Range Set R =...

Excel: DOLLAR puts the dollar sign to the right of the amount, target cell, currency symbol

target cell, currency symbol, time susan: Susan, I would go into the control panel found under Settings in the start menu in most versions of windows. Once in the control panel, look for regional settings. Look at the settings for currency and see if there is anything unusual there - like the...

Excel: Document not saved, query document, lcid

query document, lcid, excel workbook: There are so many possible reasons for this. The most common: You re saving to a directory which is protected You (or someone) have the file open elsewhere (separate instance of excel) Out of disk space for other reasons, check: http://support.microsoft.com/search/default.aspx?qid=10165&query=document+not+saved+excel&catalog=LCID%3D1033&mode=...

Excel: DSUM - swtuching from 123 to Excel, column headings, cell a2

column headings, cell a2, employee numbers: EXCEL also has a DSUM function. The primary difference is that you can not embed the criteria argument in the function as you have done and as you could do in 1-2-3. You have to create a range, with appropriate column headings and, of course, the criteria...

Excel: Duplicate, excel, conditional format

excel, conditional format: You can use the menu Format- Conditional Formatting for this. Select your range of data, and enter a formula like this as a Formula Is condition: =COUNTIF(A:A,A1) 1 I am assuming that the values are on column A, and that the first cell of the range is...

Excel: Duplicating Cells, cell b1, cell a1

cell b1, cell a1, richard florida: Alex I am not sure how your data is being input, but assuming it is being input into column A, then a formula in column B row 1 such as +A1 will bring over the data into cell B1 as you are inputting into cell A1. Of course this formula can be copied down...

Excel: Dynamic Copy Ranges, data cells, copy worksheets

data cells, copy worksheets, column c: Kevin, Dim lastrow as Long with Worksheets( Daily ) Lastrow = .cells(rows.count, C ).End(xlup).row .Range( AA4:AK & lastrow).Copy _ .Cells(Rows.Count, AM ).End(xlUp)(2) End With If it will always be pasted to AM4 (AA4 ??) then Dim lastrow...

Excel: date plus time plus hours worked equals, numeric representation, company holidays

numeric representation, company holidays, workdays: Brett: I m not 100% certain, but it sounds like you are adding dates/times, but want to ignore weekends. There is a function called WORKDAY that will add a specific number of workdays (ignoring Sat and Sun) to a starting date. The WORKDAY function also...

Excel: difference between dates Excel 07, calendar months, decimal value

calendar months, decimal value, a1: I d tend to use YEARFRAC to handle this - this returns a decimal value indicating the exact fraction of time - I m not aware from your question how this data is to be used, so either go with yearfrac s result as returned IF you need the period of time, or...

Excel: How to direct Excel to select from the intersection in a table, cell a1, index formula

cell a1, index formula, column number: Dorell The formula to look up the value at the intersection of the row and column is-- =INDEX(C9:G15,+MATCH(B22,B9:B15),+MATCH(B21,C8:G8)) It looks a little complicated but-- The reference in the formula to C9:C15 is the range of the rows and columns...

Excel: Email list cleanup, free microsoft excel, question sign

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

Excel: Embed an "OR" into a "COUNTIF", free microsoft excel, countif

free microsoft excel, countif, question sign: Try: OR(CountIf(a:a, x ),CountIf(a:a, y )) Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts Cordially,...

Excel: Error message in Excel 2003, microsoft knowledge base, data validation

microsoft knowledge base, data validation, exit design: Bri, I didn t want to leave you hanging, so I did some research on your question. It appears that a lot of people have had this error, but I don t find any specific solutions. The only documentation of it I can find on the Microsoft Knowledge Base site...

Excel: EXCEL 2007 formula evaluation question followup, evaluation question, formula evaluation

evaluation question, formula evaluation, correct evaluation: Jim, Wow, that is really odd. I took your spreadsheet and wrote SUM(D45:E45) in one cell and =F44-A45+B45 in another and compared them.... they are NOT the same. I expanded both of these to ELEVEN decimal places. Amazingly, the =F44-A45+B45 equation...

Excel: EXCEL 2007 formula question, free microsoft excel, cell a1

free microsoft excel, cell a1, question sign: Depending on how many items you have, you could do this with an IF statement. Anything else would require a macro. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: EXCEL Compare, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use the MATCH function. If the value exists, it will return the index (location) of the value, otherwise it will return an NA. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: EXCELL TIME FORMAT, excell format, valid entries

excell format, valid entries, time format: not sure if the brackets are part of this but I ll assume they are - probably the easiest method is to do three search and replaces replace a) with [space]am (just type a space where I ve put [space] replace p) with [space]pm replace ( with nothing ...

Excel: Excel 07 Conditional Formatting, formula g1, conditional formatting

formula g1, conditional formatting, hth: First, color the range red (default) Next, with the range selected, and assuming G1 is the ACTIVE cell, use Home/Conditional Formatting, New Rule, Use a formula: =AND(G1 =TODAY(),G1 TODAY()+60) then use the patterns for Yellow, then go back to New Rule,...

Excel: Excel 2000 formulas, free microsoft excel, excel 2000 formulas

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

Excel: Excel 2003, using ms office, computer issue

using ms office, computer issue, excel expert: Amol, This is probably a computer issue and has nothing to do with Excel specifically. I suggest you try a computer expert instead of an Excel expert. The only thing that could cause slow operation in Excel only, is if you have a rather...

Excel: Excel 2003 Formula to calculate annual due dates, analysis toolpak, logical test

analysis toolpak, logical test, functions in excel: I m not sure I ve got enough to go on here - you mention annuals, and also 4 years indicating that this may be significant. It might be worth taking a look at the xldic01.xls file stored at http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls as this...

Excel: Excel 2003 - Mandatory Fields, excel spreadsheet, mandatory fields

excel spreadsheet, mandatory fields, msgbox: Press Alt/F11, double-click the ThisWorkbook you ll see in the project window, put in this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For i = 1 To Range( A65536 ).End(xlUp).Row If Application.CountA(Cells(i,...

Excel: Excel 2003 remove cells containing text, index column, menu toolbar

index column, menu toolbar, sort key: Nancy, You cannot remove cells with a formula. TO do such a thing, a formula would have to be able to delete itself. A better answer to your question depends on what you mean by remove . This is not an Excel term... there is no remove...

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

free microsoft excel, excel tips tricks, looking for a new job: No, but you can change the default file location by clicking on Office Excel Options Save. Change the Default File Location setting there. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my...

Excel: Excel 2007, negative signs, column c

negative signs, column c, richard florida: Ruth The minus behind the number indicates that the number is being recognized as text by excel. What we need to do is convert it to number format. The positive numbers or those that do not have a minus behind them may also be text format if so they will...

Excel: Excel 2007 version - backup workbook, backup file, select tools

backup file, select tools, drop down menu: Wynneth, How to make a backup file. 1. Open the file you want to have a backup of. 2. Select File 3. Select Save As This will bring up the Save As popup menu. 4. In the upper right hand corner of the popup menu select Tools 5. Select...

Excel: Excel, canada column, b1 and b2

canada column, b1 and b2, word time: John, Your basic question here seems to be about circular references [CR]. You REALLY don t want to use a circular reference here. But, there IS a way to make Excel allow CR s. What you have to do is set the calculation method so...

Excel: Excel, hh mm ss, cell d1

hh mm ss, cell d1, minute time: Rachel, In order to multiply a time by an hourly rate, you must convert the minutes portion of the time to a fractional hour, then add it back to the hours. Then multiply the result by your rate. =(HOUR(time)+ MINUTE(time)/60)* hourly-rate ...

Excel: Excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, hidden columns: I can t think of an EASY way to do this in Excel. In Microsoft Access (a database program) you could create a combo box with hidden columns, but I m not sure if Excel would support that. Let me know if you have any other questions or comments. Also,...

Excel: Excel addition corresponds to date, excel, sumif

excel, sumif: You could use the SUMIF formula for this, something like: =SUMIF(A1:A100, &C2,B1:B100)-SUMIF(A1:A100, &C1,B1:B100) I am assuming that the dates are on column A, the values or column B, and the start and end date on cells C1 and C2. Hope this helps,...

Excel: Excel attachment & Lotus notes123, mail, owb

mail, owb, smsg: Maybe you can combine the code below (last sub does the sending of the active sheet) with the code I already sent: Function SendMail(vEmailSendTo As Variant, sEmailSubject As String, sBody As String, sFileToAttach As String) Dim objNotesSession As...

Excel: Excel auto numbering, invoice number, xls

invoice number, xls, novice: Not sure this works well for a novice as it would need a macro - but see http://www.aidanheritage.byethost3.com/excel/invoice.xls which is already programmed - this might help! let me know if you need further help and I ll do my best for you. NOTE...

Excel: Excel bill spreadsheet I created, cell b2, format painter

cell b2, format painter, seperately: Select cell B2 and then define a name, like yellow, to be =GET.CELL(38,Sheet3!A2)=6 In cell B2 enter: =OR(yellow,NOW()=0) For the total (in A9?), enter: =SUMIF(B2:B8,TRUE,A2:A8) Now the first time you change a cell to yellow nothing will happen unless...

Excel: Excel COUNT formula, letter reply, full time job

letter reply, full time job, excel count: This is in response to your comment: The answer was not helpful at all. I already knew what Excel function to use, I needed help writing the specific wording . Thank you for letting me wait two and a half days to receive what was as informative and useful...

Excel: Excel - Conditional Formatting, excel conditional formatting, maximum value

excel conditional formatting, maximum value, spreadsheet: As you guessed it s something else - conditional formatting would set the FORMAT of the cell based on certain conditions being true - so you could have the maximum value in a-c set to a different colour for example. In your case,it s simply a formula as you...

Excel: Excel chart range with vba, spreadsheet template, variable assignment

spreadsheet template, variable assignment, somtimes: You can use the RANGE command or the cells command - I m not sure what you are doing with the range, but myVar=1000 in reality, would be your variable assignment range( A1:A & MyVar).select selects a1:A1000 which is the result of concatenating the...

Excel: Excel - color cell based on increase/decrease in data, conditional formatting, necessary code

conditional formatting, necessary code, two colors: It is certainly possible - the only issue is that this would ALWAYS kick in Public oldval, myval Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then oldval = myval If Target.Value oldval Then Range( A & Target.Row).Interior.ColorIndex...

Excel: Excel data sorting, solution area, macro code

solution area, macro code, whole lot: Is this dropdown from a filter? If so, clearing the filter is equivalent to having ALL show up -- if it s really a filter, selecting ALL would show all the rows which contained the text ALL . Is this a real dropdown which runs a macro? What s your macro...

Excel: Excel data sorting, solution area, vlookup formula

solution area, vlookup formula, richard florida: Sathya It is difficult to read into your questions exactly how you are arranging your data. etc. but it appears that you could use a vlookup formula to look up the selection EG ALL function and thereby return the correspnding data from the rows you need....

Excel: Excel Formula, blank cell, excel formula

blank cell, excel formula, g24: These two lines of code: Range( F9 ) = Range( F15 ) = put a blank character, like pressing the space bar and then pressing ENTER, in those two cells and your formula looks for a blank. Try changing the code to: Range( F9 ) = Range(...

Excel: Excel Formula - Grabbing data from multiple worksheets by copying formula, excel formula, trying to find a way

excel formula, trying to find a way, a5: If your sheets are named Sheet1, Sheet2, etc, then you can do it with dragging the fill handle by setting up the reference this way: =INDIRECT( Sheet & ROW(A1) & !A5 ) The ROW(A1) is just to get the 1 so the formula in the first cell is effectively =Sheet1!A5...

Excel: Excel - Formula for Looking up max and min values from specific groupings, excel, large

excel, large: If the line is on column B and the data in columns C and D, you could use some array formulas like: Earliest: =LARGE(C2:C8*(B2:B8= LINE 2 ),COUNTIF(B2:B8, LINE 2 )) Final: =MAX(D2:D8*(B2:B8= LINE 2 )) Enter them with Ctrl+Shift+Enter. Change the ranges...

Excel: Excel Formulas, a3 b3, excel formulas

a3 b3, excel formulas, d3: Amy, Use the SUMPRODUCT function for this. It is designed to take the sum of products, just like you want. Read how to use this in the Function Reference (which appears when you type the equal-sign in any cell). It explains the syntax much...

Excel: Excel Formulas, excel formulas, ogilvy

excel formulas, ogilvy, brief description: Taryn, The only one I am aware of is one done by Peter Nonely. It says it was updated in Dec 2008, so it may contain some of the new functions from Excel 2007 - I can t say for sure. I also can t say it includes every function, but it includes a lot....

Excel: Excel formula, excel, consolidate

excel, consolidate: To consolidate information, I have found that the articles on this page explain it better that I can do on this box: http://www.rondebruin.nl/tips.htm Go to the Copy/Paste/Merge examples, and you have a couple of articles on how to do it with macros, or...

Excel: Excel formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You re on the right track, but what do you do if it DOES put O 6900 ?? =IF(O 6900,P-N,X) X = whatever to do otherwise Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: Excel formula fire only once, worksheet changes, best bet

worksheet changes, best bet, page answer: You didn t say which cell becomes true for this to fire If you can identify that cell, you can use this macro when that cell changes, but I also don t know the cell which should contain the result, so I m making a few assumptions here: 1 - the firing cell...

Excel: Excel Help, fldr, stemp

fldr, stemp, vaibhav: This code will do what you want - it stores the data in A2 downwards on the active worksheet. Sub List_it() dim counter counter=2 myvar = FileList( C:Test ) amend the above to whatever path you want to check If TypeName(myvar)...

Excel: Excel Help, a8, file log

a8, file log: I m not 100% clear what needs to happen here - you could use =log!a7 & log!a8 & log!a9 to pull in ALL the data, which would give you just the typed data. You could use an IF statement to pick up which cell is non blank. Or you could use VBA to populate...

Excel: Excel Links, excel, links

excel, links, protection: I am not sure on what the error is, sorry. In the menu Edit- Links, you should be able to modify the links at your convenience; changing the source or breaking the link. If the boxes are grayed out, check that the worksheet is not protected, as try again....

Excel: Excel, MAXA Value, single entries, maxa

single entries, maxa, text values: I think I d do this with a custom function as you want to combine text values which match the entry you have returned, AND this text (presumably) could be multiple entries or single entries. Public Function MaxReturn(rar As Range, offvar As Long) Dim...

Excel: Excel-Macro, excel macro, spread sheet

excel macro, spread sheet, ogilvy: Y Based on that I will make the assumption that the headers appear in column A Sub addrow() dim lastrow as long, i as Long lastrow = cells(rows.count, A ).End(xlup).row for i = lastrow to 1 step - 1 if cells(i + 1, A ).Font.Bold and len(trim(cells(i...

Excel: Excel Macro - Change Color of referenced cell, cell a1, cell references

cell a1, cell references, loop macro: You did explain it, and the macro I gave you SHOULD do that - at least, it did when I ran it range(cells(looper,1).value).interior.colorindex=10 is the key- this version is set to run on column A, which is column number 1, and uses the looper to pick...

Excel: Excel Macro Data Consolidation, variant array, suyen

variant array, suyen, data consolidation: There are too many things wrong with this code which would make it not work; I ll name a few. •You refer to DeptArray(j) yet there s no definition for it •Your code: For i = 1 To .FoundFiles.Count PathInfo = .FoundFiles(i) Next...

Excel: Excel - Mandatory Fields, data validation, drop down boxes

data validation, drop down boxes, excel spreadsheet: The vba procedure that you display in your question uses the following logic: It checks each row in your active sheet, beginning with Row 1, and continuing through the LAST row in that sheet that has a non-blank cell in col A. For each of these rows, it...

Excel: Excel Multiplying Criteria, vlookup function, data layout

vlookup function, data layout, combination of the two: Pictures don t come across very well, but this sounds like EITHER a sumproduct function or a vlookup function. I m GUESSING that it s basically vlookup =vlookup(d2,OtherSheetRange,Column,False)*g3 OtherSheetRange relates to the data with dates in -...

Excel: Excel macro to (un)protect select sheets, macro book, follow ups

macro book, follow ups, question book: OK the Book macro is fine, BUT I had overlooked the protect/unprotect comment -if a worksheet is protected, you probably can t set the page setup - but if it s only protected WITHOUT a password, include activesheet.unprotect and activesheet.protect at the...

Excel: Excel macros, vba procedure, vba procedures

vba procedure, vba procedures, vba code: An excellent resource for using vba to add/delete/modify vba procedures & modules themselves is here: http://www.cpearson.com/excel/vbe.aspx Before you run any such vba procedure (aka macro ), you must do the following: First: from the main Excel...

Excel: Excel macros/VBA, excel macros, parent cells

excel macros, parent cells, sheet1: Randy, Yes. The code can be written to do that, but even knowing you have a lot of column headings, doesn t help me know where and what data you will have in Sheet1 and how to determine what data you want from sheet2. If you want to send me a workbook...

Excel: Excel math functions are not working, dash line, math functions

dash line, math functions, k27: Assuming your formula is: =R7*K27 Try confirming the values in the cells to see what the formula is using. Edit the formula in the title bar and double click one of the cell references in the formula. That will select R7, for example. Then press F9...

Excel: Excel nested if's, vlookup, wb

vlookup, wb, yahoo: That formula, as written, makes no sense. Just the first part, =IF(Credit_Only, yes ,Grading_Criteria) is complete, so what s supposed to be happening with the VLOOKUP as part of the formula? It d help if you send me a sample wb & redescribe the issue....

Excel: Excel Question on (show only), excel, filter

excel, filter, format: Depending on what you need to do, you may use different approaches. For example, if highlighting the cells would work for you, you can use conditional formatting with a formula like: =LEN(A1) 30 And then use a pattern to highlight those cells. Other...

Excel: Excel Referencing a Cell, free microsoft excel, cell b2

free microsoft excel, cell b2, cell a1: Sure. You could use the OFFSET formula to move over and down a number of rows/columns in your sheet. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL....

Excel: Excel Spreadsheet, left hand column, cell b2

left hand column, cell b2, data validation: You d create a great deal of difficulty for yourself by using so many checkboxes. The right direction is to use Data Validation to make In-cell dropdowns that allow user to choose between an x and a blank in the cells where you have checkmarks. In creating...

Excel: Excel spreadsheet addresses into printed letters, london se1, lauren harris

london se1, lauren harris, excel spreadsheet: What you didn t say was how you are getting the letters - IF this was a mail merge via word, then these fields should automatically be skipped. IF it is and they aren t then the fields are not blank (so perhaps contain a number of spaces). Knowing the how...

Excel: Excel and Text Files, aidan, copy and paste

aidan, copy and paste, newbie: Prior to excel 2007, there was a limit to file size of 65536 and you cannot exceed this. SO options are 1) upgrade to excel 2007 where the limit is a lot bigger 2) use Word or Access to manipulate the data 3) use VBA to open the file for programmatic...

Excel: Excel transferring data, automatic insertion, ogilvy

automatic insertion, ogilvy, thanks in advance: David, This is not possible using equations. There is no function that detects the presence of highlighting, except possibly the CELL function. But the operation you desire of automatic insertion and removal isn t possible. You MIGHT be able...

Excel: Excel User Identification Prompt, excel user, excel spreadsheet

excel user, excel spreadsheet, excel workbook: You can create a sheet to hold the names and hide it. Say you name it XYZ . Use Alt/F11, double-click the thisWorkbook in the project window, enter this: Private Sub Workbook_Open() Sheets( XYZ ).Range( A65536 ).End(xlUp).Offset(1) = Environ( UserName...

Excel: Excel VBA: Copy and Paste 2, c cells, excel vba

c cells, excel vba, srow: Hi VaC, I assume you mean that you want to loop through all the region sheets, performing the same operation with each sheet, appending the data to the destination sheet. I have added such a loop to the code below. Note that I used a For Each loop and...

Excel: Excel VBA macro help, vba macro, excel vba

vba macro, excel vba, aidan: A good way to learn how to write macros is to record them, and in this case that would give you exactly what you need Sub Macro4() Macro4 Macro Macro recorded 16/09/2009 by Aidan Heritage Rows( 100:120 ).Select Selection.Copy...

Excel: Excel - vlookups, excel, sumproduct

excel, sumproduct, lookup: In this case, as the data that you want to bring is numeric, we could use the SUMPRODUCT formula here, something like: =SUMPRODUCT(--(Sheet1!$A$2:$A$11=A2),--(Sheet1!$B$2:$B$11=B2),Sheet1!$C$2:$C$11) Change the ranges as appropriate. If you have more than...

Excel: Excel2003-VBA, manual button, ontime

manual button, ontime, excel application: I don t know what kind of variable txtFilePath is. But suppose it is a userform called ufFilePath: Declare variable Dim txtFilePath as ufFilePath Other code Now instantiate the txtFilePath object Set txtFilePath = New ufFilePath Now you can set...

Excel: Excell Time Format, free microsoft excel, excell format

free microsoft excel, excell format, format cells: Right-click, Format Cells, TIME, look for the 24-hour time format. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Extract data from one worksheet to many worksheets, bennu, colin wilson

bennu, colin wilson, data extraction: Not sure I understand -- in the above example, you d want 4 sheets created, each looking like this: Serial number Pers.No. Name Project name CAL561930053D71 77 Guy Roberts R- Bennu Serial number Pers.No. Name Project name CAL561930053D84 78 Colin Wilson...

Excel: With.... end with, for each.. or multiple ifs?, customer details, mobile telephone

customer details, mobile telephone, home telephone: Private Sub SimpleExampleForYou() Dim i As Integer Dim chk As Control, txt As Control Dim str As String For i = 1 To 3 str = Choose(i, CCN , CEN , CKR ) Set chk = Me.Controls( chk & str & 1 ) Set txt = Me.Controls(...

Excel: excel 2007, fifa under 20 world cup, world cup 2006

fifa under 20 world cup, world cup 2006, fifa under 20: Sorry, I don t seem to be able to download the file at the website you provided. Do you have the file? Feel free to send it to me and I ll either explain how to edit it or explain why you can t -- send to me at bobumlas@yahoo.com, use subject of AllexpertsQ...

Excel: excel, target address, advance automation

target address, advance automation, cell a1: Automation in Excel is achieved using Visual Basic for Applications (vba) procedures, sometimes called macros. If you re not familiar with vba, it s impossible to automate like this. If you do know some vba, then you can accomplish this with code like the...

Excel: Can excel do this?, richard florida, time line

richard florida, time line, florida usa: Gary The easy part of your question is can excel to it. The answer is yes excel can do almost anything a lot of which I do not necessarily know how to do. I think you can get a better and quicker answer by reasking the question of another allexperts person....

Excel: excel 3 columns, excel, sumproduct

excel, sumproduct: You can use the SUMPRODUCT formula, something like: =SUMPRODUCT(--(A1:A100= Contact 1 ),--(B1:B100= Skilled ),C1:C100) This should give you the result you are looking for, and you can change the conditions to check other combinations. Hope this helps,...

Excel: excel & chronological organization, free microsoft excel, question sign

free microsoft excel, question sign, chronological organization: Sure, as long as you have the dates in the same column, you can just sort by that column. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: excel column identifier, spreadsheet column, arrow symbol

spreadsheet column, arrow symbol, column identifiers: Quite a short answer - and as you didnt mention the version to me, it might be difficult for me too - I don t have a mac but the BASIC answer is right - at the MOMENT you are viewing what is termed R1C1 style reference. IF you have a tools menu, do you have...

Excel: excel fomulae, excel, vlookup

excel, vlookup: It looks like an scenario for VLOOKUP. In this case, I will assume that you have a table on a separate sheet that has the countries on column A and the different values that you are looking for on columns B and D. So, if the cell with the country to input...

Excel: excel formula, type cow, cell a1

type cow, cell a1, excel formula: Example: in sheet1 cell A1, type: cow In sheet2 cell A1, type: =Sheet1!A1 You can then copy this formula from sheet2 A1 to your entire range of cells on Sheet2, Sheet3, etc. Now, when e.g. you change the cow in cell A1 of Sheet1 to pig, ...

Excel: excel formula, excel, reference

excel, reference: In this case, you can use a formula on the separate sheets that you want to have linked, something like: =Sheet1!A1 This way, every time that you change the value on Sheet1:A1, it will be changed on the other cells that refer to it. Hope this helps, ...

Excel: excel formula, excel, countif

excel, countif, autofilter: You could use a helper column with a formula like this: =COUNTIF(A:A,A2) This could be on B2, and then you can drag the formula along your values. The next step could be an autofilter on the column (menu Filter- Autofilter) for values greater than 1; that...

Excel: excel formula, Absolute Value

Absolute Value: solayman.inty: I will presume the above values are in cells A1:B5. Place this formula in cell C1: =ABS(A1-B1) Copy the formula down from C1 through C5. ABS is the Excel function for absolute value (which appears to be what you need since it appears...

Excel: excel formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Without recreating your sheet, I couldn t tell you what s wrong with your function, however, your values like 1 and 3 shouldn t be in quotes. For example: =IF(C15 E15,3,1) This will set the cell to 3 if C15 is greater than E15, otherwise 1. Here s...

Excel: excel formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Add up the times as normal, and divide by the number of sessions. There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two...

Excel: excel spreadsheets, excel spreadsheets, indirect address

excel spreadsheets, indirect address, sheet names: Mark, It sounds like what you want to do is collate information from non-adjacent cells, located on many sheets, onto one sheet. The method I always use to do this is the =INDIRECT(ADDRESS(parameters)) equation. This will...

Excel: excel vba help urgent, axis rotation, rotation value

axis rotation, rotation value, 3d surface: difficult to see from the picture - my charting source tends to be www.peltiertech.com and this search http://www.google.com/cse?cx=partner-pub-1975943359936598%3A3rfphr06tf2&ie=ISO-8859-1&q=vba+rotation may reveal answers from that site that would...

Excel: IF and Filter Macro, false selection, copy windows

false selection, copy windows, copy application: Lots of parts to the question -I would recommend http://www.rondebruin.nl/tips.htm for lots of information relating to email and excel. I d use the COUNTIF worksheet function (via VBA) to find out if there are any entries. I d PROBABLY generate a text...

Excel: Filtering, cell reference, autofilter

cell reference, autofilter, countif function: I d use a COUNTIF function to count the entry against the other list =countif(MainList,YourData) any zero returns are not in main list - replace MainList with the range that contains the full list, and YourData with the cell reference you want to check...

Excel: Find next emptry cell/s in a range and return data, empty cells, client details

empty cells, client details, macro recorder: Nico, If you insist on the structure you ve outlined, then there no choice except for a macro. As my profile says, I don t offer macro advice here, so you ll have to choose another expert who does, like Tom Ogilvy. What forces a macro is the...

Excel: Find & Replace Number within a formula, cell a1, vba code

cell a1, vba code, len: Assume the formula to change is in cell A1. Sub add150() Dim rngToChange As Range Dim iChar As Integer, iCount As Integer Dim strOld As String, strNew As String, strNumb As String Dim strChar As String Set rngToChange = Range( a1...

Excel: Find & replace, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Not easily, no… but you can create a custom toolbar and put the FIND button on it. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Find and replace by part of value, vlookup function, right solution

vlookup function, right solution, direct contact: Not clear what the rules are here, but do you have a table of old and new? If so, use a blank column to do a vlookup function - in which you look up the old value and return the new - copy the results then paste, special, values OVER the original data - your...

Excel: Finding and Deleting empty spaces before and after characters in Excel, input error, empty spaces

input error, empty spaces, worksheet function: Yes, there is a way. Assuming you meant to ask HOW to do that then have you tried the TRIM worksheet function? IF this is user input error and your import to Access is being done via Access (you don t give me any info as HOW you are doing this) then you...

Excel: Finding and referencing a range based on date, SUMPRODUCT

SUMPRODUCT: Cari: Hi Cari! The short answer is you need to use SUMPRODUCT. This is a very powerful function and it is extremely useful for situations like yours. I believe this specific function will work for your situation (or at least the sample you provided):...

Excel: Follow on, chk, ckr

chk, ckr, loop through: On a new Excel sheet, go to the Visual Basic Editor and create a UserForm. Place on it 3 checkboxes named chkCCN1, chkCEN1, chkCKR1. Also 3 textboxes, named dCCN1Date, dCEN1Date, dCKR1Date. Place a button on the userform that will run the code that I ve twice...

Excel: Formatting Time, format cells, clock time

format cells, clock time, column format: Josh The problems with calculating time differences usually are the result of formatting and data entry. In your example this is what should work for you. In the duty in, duty out column format your cells like this: h:mm AM/PM In the calculation...

Excel: Formula, table b2, pivottable

table b2, pivottable, excel formula: Joseph, The best way to handle such data is using a PivotTable. To use this do the following: 1. Click somewhere on your raw data 2. Select Data 3. Select PivotTable and PivotChart Report... This will bring up the PivotTable and PivotChart...

Excel: Formula, cell a1, f1

cell a1, f1: Chris: Suppose your first price is in cell F1 and it shows 100.00. Place this formula in cell A1 (or any cell really): =F1*(1-0.45)* 0.18+(64.9*2.4) The result of the above formula is 165.66. From that point forward you can copy the formula down...

Excel: Formula for date, excel spreadsheet, d note

excel spreadsheet, d note: =MIN(30,DATEDIF(G6,TODAY(), d )) will stop at 30. Select the cell, use format/coditional formatting, change cell value is to formula is , and assuming the cell is G7, enter this: =G7+7 today() then click Format, click patterns tab, select the color...

Excel: "IF" Formula in Excel, free microsoft excel, microsoft office xp professional

free microsoft excel, microsoft office xp professional, microsoft office xp: You ve just got your quotes in the wrong places. It should be: =IF(B10= Small ,29.89,… You want the word Small not a cell named Small. Likewise, your dollar amount should not be text. You might want to consider using VLOOKUP for this instead: ...

Excel: Formula Problems, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can do this with a couple of string functions. Let s say: A1= 1234xxxx5678 And you want to get rid of that xxxx in there. B1= xxxx C1=FIND(B1,A1) D1==REPLACE(A1,C1,LEN(B1), ) This will FIND where xxxx appears in A1 and then replace...

Excel: Formula problem, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: This is just going to be a compex, nested IF function. =IF(A1 =100,A1*3.25/23,IF(A1 =200,A1*2.75/23,IF(A1 =300,A1*2.10/23,X))) X represents everything else (what value to use if the miles are over 300). You can use the IF function to have Excel place...

Excel: Formulas as text, cell reference, cell a1

cell reference, cell a1, a3 b3: Mickey, Indirect can built a cell reference, but it can t build a more complex expression. So you can dynamically build a formula string and then evaluate it - at least not with just the built in functions. In your case, you can design the formula...

Excel: auto increment by 1, d3, tiny bit

Excel: auto increment by 1, d3, tiny bit, invoices

Excel: Document not saved

Excel: Document not saved

Excel: Excel VBA: Copy and Paste 2

Excel: Excel VBA: Copy and Paste 2

Excel: Excel - vlookups, vlookup

Excel: Excel - vlookups, vlookup

Excel: Finding and Deleting empty spaces before and after characters in Excel, cells, hidden characters

Excel: Finding and Deleting empty spaces before and after characters in Excel, cells, hidden characters

Excel: Student Address Excel Database

Excel: Student Address Excel Database

Excel: Text to speech in Excel, boumediene, excel 5

Excel: Text to speech in Excel, boumediene, excel 5, text to speech

Excel: Travelling allowance, travels

Excel: Travelling allowance, travels

Excel: forms / databases, free microsoft excel, microsoft access database

free microsoft excel, microsoft access database, question sign: I honestly would not do this as an Excel form. I would set up a Microsoft Access database or a Web-based HTML form that stores the data in a table. Doing this in Excel would be like using a hammer when you should have a screwdriver. Let me know if you have...

Excel: formula, ribbon bar, cell reference

ribbon bar, cell reference, integer value: Make X another cell reference in the spreadsheet and then use Goal Seek to solve it - in this instance, I m using A1 as my test which has this formula =IF((E1-(A4-3%)*1.25%*31/365) =I40,1,0) goal seek tries to set the value of A1 to 1 by changing E1...

Excel: formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: If your value is in A1 you should be able to say: =(((A1*.45)*.18)*2.4) Or thereabouts. It s just simple math. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: formula, string manipulation functions, free microsoft excel

string manipulation functions, free microsoft excel, string concatenation: It adds S to the right X characters of whatever s in B7 where X is the length of B7 - 2 characters. I cover the various string manipulation functions in my EXCEL 232 tutorial: http://599cd.com/site/courselist/excel/excel232?key=AllExperts In this...

Excel: IF formulas, ot hours, project numbers

ot hours, project numbers, sumif: Tracey, Yes, you have to type in the unique list of project numbers in B53:B75. Another way is to add a blank sheet and copy the two sets of project numbers all into a single column such as column A on the blank sheet. In A1 put in the word Numbers and...

Excel: formulas/copying sheets, richard florida, a10

richard florida, a10, florida usa: Jeff Obvious answer and I am guessing one that you have thought about would be to copy the entire workbooks/worksheets that are involved--since everything is the same. If that won t work then-- I believe if you copy your formulas to the new worksheets...

Excel: If formulas for reminders., follow ups, column c

follow ups, column c, richard florida: Ram I am a bit confused as to whether you have three columns for the reminders or two because in your question you mention three reminders but you need your formula in column E with A being the first column used and with D being the second reminder, which...

Excel: frequency table, array formula, vertical array

array formula, vertical array, array formulas: This answer comes from the help file Show All FREQUENCY See Also Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall...

Excel: now() function result, template sheet, macro code

template sheet, macro code, wasting your time: What footer code are you referring to? Is there a macro (code) being run? To prevent the date from updating you will have to type a hadrd date in the footer or write a macro that puts today s date as a hard date and not a dynamic date, which is what Now()...

Excel: Generate automatic Invoice no and P.O, invoice format, invoice numbers

invoice format, invoice numbers, ini file: I m not clear what you want to happen here - there is no automatic process to update a sheet name, but VBA could be used to create a sheet with a name based on the current name - however, if the sheets are all in the one workbook, then it s easy enough to...

Excel: Generating an Array in with a for-each loop in VBA, irow, new element

irow, new element, shooting from the hip: try SET depArray(depArCount) = rngDepth since the DepArray function is a range. And, depArray(depArCount) must be a range or this will still not work. And finally, yes, you can only preserve a one dimensional array - I missed the fact that you were...

Excel: Generating an Array in VBA, irow, array

irow, array, cells: A much easier way to accomplish this is by using built-in Excel functionality: filter the table of cells on the proper date. Then copy the filtered table to another location. Like this: Sub SortNumbers() Dim oResultRange As Range Set oResultRange...

Excel: Grabbing values from multiple workbooks, index function, sheet1

index function, sheet1, shared server: easiest way is simply =[filename1.xls]sheet1!B10 +[filename2.xls]sheet1!B10 etc simply open all the files in excel then after typing = use the window to switch between the workbooks and click on the cell you want, then type the + and continue until...

Excel: Help, code index, upc code

code index, upc code, unistrut: Steve: The formula you have chosen is one of the best choices to use. You ve done the hard work already - now it is just a matter of copying the formula to all of the existing cells. I m not 100% certain, but it sounds like the problem is you don t...

Excel: Hide rows without a particular word, hideme, word question

hideme, word question, header row: If you could state that a full word was followed or preceeded by a space, they you could search for price or price . Alternatively, you could search for the variants (if they are known) and IF found reset the boolean value - hope this makes sense...

Excel: Highlight Duplicates, conditional formatting, yuo

conditional formatting, yuo, drop down list: There s no way to do it for the non-contiguous ranges, but yuo may be able to get away with this. Select ALL the cells in the ranges you listed, and use this formula in the conditional formatting (assuming B2 is the ACTIVE cell): =COUNTIF($B$2:$T$15,B2) 1...

Excel: Horizontal Sorting and counting, vba code, combinations

vba code, combinations, cells: Hi Norman, Not sure how the columns need to be sorted, in order? or each column by its own? This does the former: Sub SortNumbers() Dim oSortRange As Range For Each oSortRange In ActiveCell.CurrentRegion.rows oSortRange.Sort oSortRange.Cells(1,...

Excel: help, code index, upc code

code index, upc code, unistrut: First, select the cells you want to apply the formula to (i.e., select cell E1, then hold down the shift key as you extend the selection to E1:E10000). Then type: =VLOOKUP(B1,I:L,4,0) but after you type it, instead of hitting the Enter key, hit Ctrl-Enter....

Excel: hourly/daily averages, creating pivot tables, minute intervals

creating pivot tables, minute intervals, pivot table: Easiest way would be to create a new column that shows the hour and a new column for the day =hour(a2) would give the hour of A2 and =int(a2) would give the date only with no time. Then use the data menu to create a pivot table and get that to...

Excel: Importing CSV files into excel, csv files, csv file

csv files, csv file, code excel: Change: Copia los colores ActiveSheet.UsedRange.Copy ThisWorkbook.Worksheets( Hoja2 ).Cells(UltimaFila, 1) to Copia los colores Set NewWorksheet = Thisworkbook.Worksheets.Add ActiveSheet.UsedRange.Copy NewWorksheet.Range( A1 ) NewWorksheet.Rows(1).Font.Bold...

Excel: Importing data point (cell) from one excel file to another, sheet tab, cell reference

sheet tab, cell reference, documents and settings: If they re open, type = , then click on the cell you want to get the value from. If they re not open, you need to include the entire path to the workbook, the workbook name, the sheet/tab name and the cell reference, like = D:Documents and Settings umlasMy...

Excel: Improving on Do Loop macro efficiency, loop macro, c7

loop macro, c7, aidan: I would probably do this without a macro - you create the subtotals, then use data, filter, autofilter, custom to select those lines that contain total - select and copy the result and paste to a blank worksheet - which gives you the data you need to work...

Excel: Inconsistent formula, macth, d15

macth, d15, snap shot: Ravi, I noticed one problem with the formulas you provided. The example you give for the equation in D21 is using for input the value in a different row! (A18) Though this value AB is present, isn t it confusing to look for input values...

Excel: Inserting Comma's into values, string manipulation functions, free microsoft excel

string manipulation functions, free microsoft excel, state abbreviations: The big question is this: are all the states EXACTLY 2 characters at the end of the cell? If so, you could get away with this to get the CITY: B1 =LEFT(A1,LEN(A1)-3) The state would just be: C1 =RIGHT(A1,2) Now put them together D1 ...

Excel: Interest rates, cell c1, cell b2

cell c1, cell b2, maturity value: In cell A2 enter =A1+3 In cell B1 enter 100000 Format cell C1 as Percentage with 2 decimal places. In cell C1 enter 4.00% In cell B2, enter =B1*(1+$C$1/4) Copy A2:B2 down through A2:B8. From Excel menu, choose Tools, Goal Seek, Set...

Excel: ISBLANK formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: How about: =IF(ISNUMBER(S4),S4,IF(ISNUMBER(R4),R4, closed )) You just need to nest your IF statements. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial...

Excel: isblank, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: The NOW() function is going to continuously update that cell to the current time. You would need a macro to run when your cell is updated to manually set that cell equal to the current time. Let me know if you have any other questions or comments. Also,...

Excel: if iserror, srow, instr

srow, instr, formula 1: You don t, though you can, use the ISERROR function in code. Instead, just to check to see if the cell has valid data, something like If IsNumeric(Range(scol & 168 )) And Trim(Range(scol & 168 )) then do the stuff you need to do because Range(scol...

Excel: isna and vlookup, isna, null string

isna, null string, e9: Dan, If no match is found, rather than returning N/A, it returns a null string, i.e. the cell with teh formula appears blank. Otherwise it does what you describe. =IF(ISNA(VLOOKUP(A9,Sheet2!$A$2:$G$97,7,FALSE)), ,if(VLOOKUP(A9,Sheet2!$A$2:$G$97,7,FALSE)=0,E9*C9,VLOOKUP(A9,Sheet2!$A$2:$G$97,7,FALSE)))...

Excel: Linkage issues, financial models, source file

financial models, source file, microsoft excel: Tristan One of the things I use is to turn off the automatic calculation and use the manual calc (F9). That way nothing gets updated until you want it to. (toolbar/tools/options/check the manual calc box) Also review the following it is from the excel...

Excel: Linked Cells, proper sort, cell reference

proper sort, cell reference, sort function: Hi Kath, In the current structure you are correct: no sorting allowed. Without seeing your sheet it is hard to advise what to do next, but I expect a redesign of your setup is needed. I strongly suspect what you are doing here would best be done using...

Excel: Linking excel files to be used in a Macro, excel spreadsheet file, vba code

excel spreadsheet file, vba code, directory folder: I m not sure I m clear what you want the code to do - it sounds as though it would need to open and reference one or more books, whilst retaining a reference to the active one - so something like dim CurBook as workbook set CurBook=activeworkbook you...

Excel: Linking excel formulas to macro buttons, array formula, multidimensional array

array formula, multidimensional array, dimensional array: Nick, on a blank sheet, in A1 put in this formula =CHAR(COLUMN()+64)&ROW() Drag fill it to the right for a couple of columns and then drag the first row down for 10 or so rows. This should put the cell names in each cell. Now go to cell M22...

Excel: Linking multiple excel sheets and retaining the values, rajaram, richard florida

rajaram, richard florida, copy paste: Rajaram I believe the easiest way for you to handle this is to use a copy/paste special/values in the child sheet, thereby eliminating the parent changing values due to links. You can use another sheet to copy to or another part of the same child sheet...

Excel: Linking worksheets, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You could create a HYPERLINK from one cell to another. That s about all I can think of without programming. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO...

Excel: Look up functions, microsoft site, mathur

microsoft site, mathur, office microsoft: Danielle, Yes, there is no difference with respect to this. If you want to send me a sample workbook and tell me exactly what you want and where you want it, I can show you how to do it. twogilvy@msn.com Otherwise, I would just be repeating...

Excel: Lookup the closest match, vlookup formula, different time

vlookup formula, different time, occuring: The problem is that IF you are using vlookup it will look in the FIRST column for a match and then return values from that or a subsequent column - so table B needs to have the time entered AFTER the entry number - which you can do by adding an =a2 or equivalent...

Excel: linking references, cell c2, cell references

cell c2, cell references, window menu: Cannot be totally specific as I don t know the cell references involved, and indeed I m not sure what you wanted to do with the link, but any formula can reference any cell in any workbook - if the worksheets are in different workbooks, have them both open,...

Excel: as listed, format cells, damons

format cells, damons, date stamp: Dave, Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect([G2:G12], Target) Is Nothing Then Application.EnableEvents = False Cells(Target.Row, 6) = Now Application.EnableEvents = True End If End Sub -- Regards,...

Excel: lookup, core units, isna

core units, isna, ogilvy: ermias, In xl2003 and earlier: =if(isna(VLOOKUP(A3, P:Core UnitsCost Centres[ES3011.xls]ES3011 !$A:$G,3,FALSE)),0,VLOOKUP(A3, P:Core UnitsCost Centres[ES3011.xls]ES3011 !$A:$G,3,FALSE)) If you have Excel 2007, you can use the new IFERROR function...

Excel: lots of ifs, value entries, brainbench

value entries, brainbench, doug smith: Hi Hilde, The easiest solution would be to select all the date cells and use the Replace command (Edit menu) to replace all the #VALUE! entries with nothing. Then your formula should work as-is. Otherwise, the formula would need to look something like...

Excel: Macro, blank cell, loop condition

blank cell, loop condition, open range: It s easier to put data at the bottom of a worksheet, but it s not impossible to do it the other way round - I d tend to put at the bottom and then sort the data. To put the data in a given location, use Selection.Insert Shift:=xlDown instead of paste...

Excel: Macro to Correct Data Entry Errors, minute values, summary activity

minute values, summary activity, iloc: Stan, This worked for me. It works on column B. Text it on a copy of your worksheet It assumes that column B is formatted as hh:mm It assumes that row 1 has headers, so it starts processing in B2 of the active sheet. Sub AdjustTime() Dim...

Excel: Macro to copy rows, datar, c statement

datar, c statement, rerun: Politely, this is why I don t write macros for people who need one, as my intro says, because it can be an almost never ending stream of changes and modifications. I made an exception in your case because this was as simple and short macro. How easy would...

Excel: Macro efficiency, r1 value, sheets field

r1 value, sheets field, dim button: Pete, Private Sub CommandButton1_Click() Dim Button As Control Dim r as Range, r1 as Range, r2 as Range Dim res as Variant set a reference to the list of names in Playlist with Worksheets( Playlist ) set r = .Range(.cells(1, A ),.Cells(1,...

Excel: IF THEN Macro Help, c cells, b cells

c cells, b cells, scount: Your ENTIRE macro can be: Sub Vendor() Dim DestSheet As Worksheet Dim sRow As Long row index on source worksheet Dim dRow As Long row index on destination worksheet For sRow = 1 To Range( G65536 ).End(xlUp).Row If Cells(sRow,...

Excel: Macro Help, cell b1, column c

cell b1, column c, richard florida: JOHN I think we can do what you want with an IFTHEN formula, something like this-- = IF(B1= A,B,C , X , ) The formula says If the cell B1 has the text A,B,C in it, then put an X in the formula cell. If it doesn t then put a blank in the formula...

Excel: Macro Help, size column, john doe

size column, john doe, johndoe: Why not just use a formula, like this in C1: =IF(B1= John , Doe , ) and fill down? But if you insist on a macro: Sub JohnDoe() for i=1 to range( B65536 ).end(xlup).Row if cells(i,2).value= John then cells(i,3).value= Doe Next End Su...

Excel: Macro to run through autofilter options & print each, pivot table, pivot tables

pivot table, pivot tables, table toolbar: Sure you can, but before I start cooking code, let me point you to a handy option Pivot tables have. If you add the corresponding field of the table as a page filter for the pivot table, then you can use the Show pages option from the Pivot table dropdown...

Excel: Code that displays the Date at which a cell/cells Changed., vlookup function, code string

vlookup function, code string, char c: Sujan, Based on what you have said, I would do this: Private Sub Worksheet_Change(ByVal Target As Range) if more than one cell is triggering, then exit sub If Target.Count 1 Then Exit Sub check if triggering cell is empty If Len(Trim(Target))...

Excel: Macros and pivot tables, excel, vba

excel, vba, pivot tables: You can use the worksheet events for this. This code can give you some ideas: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $A$1 Then Sheets( Sheet1 ).PivotTables( PivotTable ).PivotFields( Country ).CurrentPage...

Excel: Macros and pivot tables, pivot table, pivot tables

pivot table, pivot tables, accurate answer: James Sorry I don t fully understand the what you need, and if I did I might not be a good one to answer because I seldom use pivot tables therefore I am defiantely not an expert in them. To get a quicker and probably more accurate answer I suggest you...

Excel: Mandatory fields, column ranges, registration dept, patient tracker

registration dept, patient tracker, vba help: I can probably help, though I m not sure I ve got enough to go on to be specific at the moment - however, my suggestion would be to use a USER FORM to get the data - this would decide which stage we were at in the process and present the questions. Only a...

Excel: If and Match Function Combination, store numbers, bb3

store numbers, bb3, aidan: The formula looks fine, so SHOULD be returning the right store, though it would always return the first entry if there are multiple entries - I m not sure that the lookup formula is right though as it would look in column A of the WM Store file and return...

Excel: Merging columns, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Well, you could convert the date to TEXT first: =TEXT(A1, mm/dd/yy ) Excel stores DATES internally as a long integer. That s why it s showing up as 40167. Let me know if you have any other questions or comments. Also, be sure to visit my web site...

Excel: MODE for text values, array formula, vba solutions

array formula, vba solutions, text values: I ve found some VBA solutions online, but you can do it with an array formula - I assume your list is in cells - so I ll assume A1:A8 =INDEX(A1:A8,MAX(IF(COUNTIF($A$1:$A$8,A1:A8)=LARGE(COUNTIF($A$1:$A$8,A1:A8),1),ROW(A1:A8),0)),1) This needs to be entered...

Excel: Mouse over pop-up, pointless message, mouseover event

pointless message, mouseover event, mousemove event: There isn t a mouseover event for the cell, but you can get one to work by inserting a control object - eg a label - this has the MouseMove event which you can program - you would need a control in each cell that you are interested in - the code is along the...

Excel: Moving data using a check box, run time error, sheet1

run time error, sheet1, design mode: Sabrina, Assume you can use column A to check for the next blank row. You can use set r = Worksheets( Sheet2 ).Cells(rows.count, A ).End(xlup).Offset(1,0) now r refers to column A of the next blank row. This assumes you have header data in row1...

Excel: MS Excel 2007, visual basic for applications, familiarity

visual basic for applications, familiarity, visual basic editor: Customizing Excel like this requires the use of Visual Basic for Applications coding (vba, sometimes called Macros ). So you first need some basic familiarity with vba. Once you have that, go to the Visual Basic Editor. In the workbook object, you can...

Excel: Multiple Criteria, product conversions, array formula

product conversions, array formula, logical tests: not sure your summary helped, but I assume you are summing if multiple conditions apply - at which point, you can do it without an array formula but using a trick of sumproduct which allows you to turn true/false values into 1 s and 0 s =sumproduct(--(left(I25:I101,8)=...

Excel: Multiple IF Formulas, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: =IF(A1 =.25,0,IF(A1 =.75,2,IF(A1 =1,1,-1)))) Try that. A value outside your range will give you -1. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial...

Excel: macro, tool users, filing system

tool users, filing system, aidan: Not sure I follow the question - but if each file equates to a sheet in the workbook then the macro can simply copy these to a new book (record this action by right clicking on a sheet and selecting move or copy, create a copy destination to new book). Can...

Excel: merging the columns of a matrix into a single long vector, basic principle, excel spreadsheet

basic principle, excel spreadsheet, acceptable solution: Ah, OK, sorry for the misunderstanding - obviously you can do it manually by copy and paste, but that s not ideal (although I have to admit for one off jobs it s something I do do as it s quicker than some alternatives). The BEST solution would be to have...

Excel: modfied sorting, sort columns, persian words

sort columns, persian words, sort command: Dave, My first suggestion is to give custom lists another try. Create a list with the first character followed by the wildcard *. I can t say for sure if this will work or not, but it is worth a try. Another thought is to create a set...

Excel: Need ISTEXT Formula Assistance, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Use the =ISNA() function to determine whether or not the cell is #N/A or not. It’s not text, it s a special code. =IF(ISNA(AG5), LSFV Null , LSFV 3yrs ) Let me know if you have any other questions or comments. Also, be sure to visit my web site...

Excel: Negative time and conditional formatting, conditional formatting, negative time

conditional formatting, negative time, diffence: I m not clear what you want to do here as there are (it seems) two contradictory statements - you want the text to be red, but you don t want to see the text? Format, conditional formatting is the basic setting you need and it should be easy enough to achieve...

Excel: Nested IF Statements, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can still use VLOOKUP. There is a fourth optional parameter that does an EXACT lookup. Say: =VLOOKUP(Cell,Range,Column,FALSE) Now VLOOKUP will work, and you won t have a monstrously large IF statement. You can use the VLOOKUP or HLOOKUP function....

Excel: Numbers w/o .0, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Just select your data and click on the DECREASE DECIMAL PLACES button on the toolbar. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Opening Files via macro's, excel, open file

excel, open file, vbs: Yes, it is possible. As this is not a workbook, but a .tif file, my recommendation would be to place an hyperlink on the cell, and then following it with code, something like: Range( A1 ).Hyperlinks(1).Follow If it were Excel workbooks, you could use the...

Excel: Option buttons, optionbutton, control toolbox

optionbutton, control toolbox, option buttons: Gracie, The option buttons from the forms toolbar (which use a groupbox) operate the way you describe. OptionButtons from the control toolbox toolbar should each be assigned to its own cell. then if you group them using the groupname property, the...

Excel: Option buttons, control toolbox, option buttons

control toolbox, option buttons, click properties: Check the properties for each (Right click the control and then left click Properties). When the Groupname property is the same name for each then they will be dependent on each other, which means when you click one the other gets unclicked. Does that help...

Excel: How do I obtain the average of letter grades?, letter grades, numerical value

letter grades, numerical value, suggestion: Not sure you can do this in the way you are trying without making a real complicated formula. As an alternate suggestion, create a small lookup table and assign values to each grade. Then use VLOOKUP, or MATCH, to get the numerical value for each grade...

Excel: Personal workbook, personal workbook, xls

personal workbook, xls: It shouldn t. It refers, of course, just to personal.xls -- are you certain it s not referring to another workbook? Have you turned events off? I have the same code in my personal.xls and it doesn t ask, so it s hard to know what s happening in your case...

Excel: Pivot Table, general ledger account, gl account

general ledger account, gl account, business credit card: Martin, It would be easier to spit it up manually and create an equation in the allocated total column to ad it up, than to enter the number in the total column and have equations detect how to split it up. Its unclear but it seems that a given...

Excel: PO spreadsteet, free microsoft excel, microsoft office online

free microsoft excel, microsoft office online, question sign: Have you looked in the Microsoft Office Online templates? Perhaps someone else has made one. If not, create your own. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: Populating cells, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use an IF function in Sheet2!B1 =IF(A1=Sheet1!A1,Sheet1!B1, ) You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more information...

Excel: Preventing data entry errors in Excel, target value, data validation

target value, data validation, sheet tab: Vickie, You could do the first part with data validation if the letters are listed say in the first row in their respective columns. If you want to do the second part, you would have to write a macro using the change event. If you are going to...

Excel: Print options, continuous area, landscape

continuous area, landscape, aim: Not clear what you mean by virtual pages, but IF excel views two items as being two pages, it will print to two pages - you therefore need to find a way to get excel to view this as being one page. assuming your two virtual pages are (for example) two worksheets,...

Excel: Prob in Vlookup, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Make sure your list is sorted ascending for VLOOKUP to work - if not you have to use EXACT MATCH. You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet...

Excel: Problem with end.(xlup)?, validation routines, details button

validation routines, details button, marketing information: Lee, In the Previous Customer sheet, you do an end(xlup) and then add a row to the end of the list. I suspect at that time, the active cell is the new row. In the Marketing List sheet, you do an end(xlup) and start writing over the last customer. ...

Excel: Profit And Loss Graph, xy scatter chart, negative colors

xy scatter chart, negative colors, profit back: Dom: This is not something that can be done with Excel using the standard chart options. I have listed two websites that offer Visual Basic alternatives; however, they don t really exactly work perfectly (I ll let you give them a shot and see if they are...

Excel: Progressive Invoice Numbers / protection, excel invoice template, invoice numbers

excel invoice template, invoice numbers, invoice number: you probably know that a worksheet can be hidden or unhidden by using the menu choices - within VBA there is a setting called VeryHidden which makes it impossible to see the sheet to unhide it - so you could set up your sheet such that ONLY if the macros are...

Excel: Pulling Report?, free microsoft excel, ms access database

free microsoft excel, ms access database, question sign: This really would be better accomplished with an MS Access database, but you could do this with VLOOKUP on a second sheet. Here s a tutorial for you: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that...

Excel: program a simple equation using excel, target value, cell b2

target value, cell b2, macro in excel: Feras, right click on the sheet tab and select view code. then paste in this code in the resulting module: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Count 1 Then Exit Sub If Len(Trim(Target)) = 0 Then Exit Sub...

Excel: qouestion on exchangin data in excel, cell a1, a10

cell a1, a10, a11: Example: say you want to replace the data in Col A and Col B with each other. First, select col A and copy it to col C. Then select col B and copy it to Col A. Then copy col C to col B. Then select col C and hit the Delete key. Example: Say on sheet1 you...

Excel: question on maintaining inventory excel sheet, closing stock, stock tracker

closing stock, stock tracker, first language: I appreciate that english is probably not your first language, but it is hard to follow what it is you want to achieve - if it helps to send me a sample file, my email is aidan.heritage@virgin.net - please note that I am on vacation at present so may not be...

Excel: Range objects in Excel VBA, free microsoft excel, excel vba

free microsoft excel, excel vba, question sign: A1 refers to just that cell, relative to the current sheet. If you want to specify a sheet, you need to say: SheetName!A1 Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: RE:, target value, cell b2

target value, cell b2, target address: Example: you have 2 documents in folder C: emp. The document filenames are A.xls and B.xls. You need a dropdown menu, such that the user can choose one of these documents and will automatically follow the hyperlink to it. Everything below refers to Sheet1....

Excel: RE: Deletin unwanted rows from multiple worksheets, rng

rng: Try this Sub Find_UNKNOWN() Dim sh as Worksheet Dim rng As Range Dim what As String what = UNKNOWN For Each sh in Activeworkbook sh.select Do Set rng = ActiveSheet.UsedRange.Find(what) ...

Excel: RE: Deleting unwanted rows from multiple worksheets, blank cell, rng

blank cell, rng, aidan: Sub DeleteRows() dim currentcell,nextcell,looper dim DeleteVar as boolean for each sh in worksheets sh.activate Set currentCell = sh.Range( A1 ) Do While Not IsEmpty(currentCell) you may wish to amend this test as it will stop if there is a blank cell...

Excel: RE: Retrieving data from multiple worksheets, sheet names, retrieving data

sheet names, retrieving data, a13: after this line: ActiveCell.Offset(iSheet - 1, 0) = & Worksheets(iSheet).Name put this code: Dim N as integer On error resume next N=0 N=Application.Match( Methane ,Sheets(iSheet).Columns(2),0) if N 0 then Activecell.offset(isheet - 1,1).Value =...

Excel: REVERSE CONCATENATION IN EXCEL, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use the RIGHT function to get the right X characters, and use LEN to see how long it is: =RIGHT(A1,LEN(A1)-1) Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: Rectangles, format cells, thick border

format cells, thick border, text boxes: Unfortunately, you ve found a (nasty) bug, and I m afraid I don t know of a workaround. Simply creating a no-fill rectangle in 03, open in 07, save, reopen in 03 and it s filled, even tho it says the property is no fill. All I can do is report it to Microsoft...