sirajuddin, color index, range c: Hi Sirajuddin How are you? Please do the following 1. Run the Visual Basic Editor 2. Insert - Module 3. Copy the following code Sub countingyellow() Dim R As Range, c As Integer, cel As Range Set R = ActiveSheet.Range( A3:K20 ) For Each cel...

Excel: Column checking, list of names, aidan

list of names, aidan, gaps: If you don t mind NOT having them aligned, then you could use conditonal formatting to highlight the matches using a countif worksheet function - otherwise, you d need VBA to shuffle them around - answer being done quickly as my daughter has been unwell (nothing...

Excel: Combinations, letter combinations, concatenation operation

letter combinations, concatenation operation, excel function: David, There isn t any Excel function which can generate these specific combinatorial sets. At best, you could use Excel to create these more-or-less by hand. You could use the concatenate operators, along with drop-downs for each character...

Excel: Combine if statment with date, product x, excel sheet

product x, excel sheet, 4 months: Firas, What you need is to extract the MONTH number from the date, and subtract it from the month number for June. I don t understand your example, because from April (month 4) to June (month 6) is only THREE months, if you include April itself....

Excel: Combining Data, vlookup function, excel formulas

vlookup function, excel formulas, a26: No problem, and you ve almost used the right function name in your question - it would be a vlookup function - the only proviso is that the names need to be unique, otherwise you will get the first matching record each time you try to look up that data. ...

Excel: Combobox referencing external range in VBA, blank rows, source sheet

blank rows, source sheet, d19: JPierce, try Dim r as Range Dim s as String s = Range( A2 ).Text set sh = ActiveSheet or set it to the sheet with the combobox With sh set r = .Worksheets(s).Range( D12:D19 ) .ListFillrange = r.Address(0,0,xla1,True) End With...

Excel: Compare Rows wise excel data, cell1, reply thanks

cell1, reply thanks, bcopy: Solaianand, Sub comparedata() Dim sh1 As Worksheet, sh2 As Worksheet Dim bk1 As Workbook, sh3 As Worksheet Dim cell As Range, cell1 As Range Dim r1 As Range, r2 As Range, r3 As Range Dim r4 as Range, s as String Dim rw As Long, bCopy As Boolean Set...

Excel: Compare Workbooks, xls file, cell a2

xls file, cell a2, cell a1: Mohd, That corection doesn t make any sense to me, since you just wanted to check data in column A. You must be using excel 2007 since earlier versions only have 256 columns. I could put a counter in the code, and a message box but I can t say...

Excel: Comparing data from two worksheets and deleting, a28, csv

a28, csv, r1: Bobby, I opened a new workbook Named sheet1 Index Named sheet2 Compsheet in A8 I put in the formula = A &row() and drag filled it down to row 28 I then copy and pasted special values to make these values. I added 6 additional sheet and in...

Excel: Completion Date, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Nope. You ll have to actually type the date in. The NOW function always shows NOW. 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: Complex VLOOKUP formula, vlookup formula, cell references

vlookup formula, cell references, column c: the screenshot doesn t show cell references, so it s hard to know where F8 and B4:D8 is. However, the breakdown is this: =VLOOKUP(F8,B4:D8,3) looks up the value in F8 down column B (B4:B8) and returns the corresponding value from D4:D8. So, for example, if...

Excel: Conditional Formatting, blank cells, eddress

blank cells, eddress, followup question: Angelique, If your version of Excel is THAT old, I may not be able to see the problem either. But, it is unlikely to be age-related. You can send to my professional eddress, EXPERTATEXCEL@AOL.COM. However, you MUST also send a followup question...

Excel: Conditional Formatting, conditional formatting, red cells

conditional formatting, red cells, advertising campaigns: Anna, I guess you asking about the conditional formatting formula. Select G3:I10 with G3 as the active cell do format= conditional formatting in the dialog, in the first dropdown change Cell value is to formula is put in =G3 0 make the format...

Excel: Conditional formatting, conditional formatting, step 6

conditional formatting, step 6, drop down menu: First the cells in A1 B1 and C1 must be formatted. To do this take the following steps: 1. Highlight all three cells 2. Select the Format drop down menu 3. Select Conditional Formatting... This will bring up the Conditional Formatting popup...

Excel: Conditional formatting for reminders., conditional formatting, reminders

conditional formatting, reminders, reminder: Seems to me, IF I understand, that all you need to do is add a higher level to check for the cell that shows the COMPLETED status that you might type =IF(H:H= COMPLETED , COMPLETED ,IF(G:G= , ,IF(G:G TODAY(), Overdue ,IF(G:G=TODAY(), CRITICAL ,IF(G:G...

Excel: Conditional tab color change, question question, script editor

question question, script editor, z8: Eric, I have xl2007 and seem to be having problems running this code = however, I don t want to change it because I know Chip has thoroughly tested his code with earlier versions. I would put all this code in the thisworkbook module and see if it works....

Excel: Conditional tab color change, run time error, chip pearson

run time error, chip pearson, exit function: Eric, No we don t have to quit - it is just because there are too many characters in the original discussion. You can always start a fresh question - you just lose the history. I had problems as well in xl2007 - but I believe I said that. I don t have...

Excel: Conditional tab color change, z8, script editor

z8, script editor, ogilvy: Eric, I can t figure out from your code what your condition is for coloring the tab. You have the loop I put in which is for each Cell in r where r is the range B3:Z48 then you pass A1:ZZ200 to the ColorIndexOFCF function While there is...

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

free microsoft excel, excel tips tricks, 599cd: This tutorial might help you: http://599cd.com/tips/excel/extend-conditional-formatting/?key=AllExperts 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: Consolidate data from several worksheets, question thanks, database format

question thanks, database format, ogilvy: Elvira, sorting isn t required to do that, but I have included sorting in the macro and returned the file to you. It worked for me with your sample workbooks. Also, the master workbook should have nothing past row 5. No checkboxes and no formatting....

Excel: Consolidate data from several worksheets, blank rows, spath

blank rows, spath, lcase: Elvira, Assume the data will be consolidated in the workbook that contains the code in the 1st sheet in the tab order. Further assume that that sheet has headers in row 1 but is otherwise blank. Also, assume the extent of the data can be determined by...

Excel: Control the sum of random numbers using VBA, employee test, random numbers

employee test, random numbers, drug test: Sub tests() Dim randRow As Range For Each randRow In Range( B2:G3 ).Rows randRow.Formula = =ROUND(RAND(),0) Do Until Application.Sum(randRow) = 3 randRow.Calculate Loop randRow.Copy randRow.PasteSpecial...

Excel: Controlling the format of a cell, data validation, custom selection

data validation, custom selection, consistent format: Dan, I assume you are using data validation. If so, I used the custom selection in the data validation dialog and this formula to validate cell E4 (as an example) =AND(LEN($E$4)=6,ISNUMBER(MID($E$4,1,1)*1),ISERROR(MID($E$4,2,1)*1),ISNUMBER(MID($E$4,3,3)*1),ISERROR(MID($E$4,6,1)*1))...

Excel: Convert Text in Formula, conversion table, number 9

conversion table, number 9, final result: Leandro, I don t know how to convert text into a formula, without a very complex conversion table which would be really hard to write. I suggest that there is a much easier way than what you re trying, but I don t understand the end result you...

Excel: Convert a text formula into a computable formula, cell a1, ced

cell a1, ced, aidan: I think my brain was fried when I replied before - my daughter has been slightly unwell over the last few evenings, so although nothing serious I ve not been getting the sleep I needed! The method isn t going to work, although it s a nice idea - I m hoping...

Excel: Converting time to text, hh mm ss, ss format

hh mm ss, ss format, question post: Robert, If you want to keep it as a number (stored as a number), the best you can do is format the column with a custom number format of 0000 then the leading zero will be displayed (not retained - stored numbers don t retain leading or trailing zeros)....

Excel: To Copy Down Formula Until Last Row, data cells, w2

data cells, w2, adnan: Sub CopyFormula() Dim LastRow As Long With Worksheets( Data ) LastRow = Worksheets( Data ).Cells(Rows.Count, A ).End(xlUp).Row .Range( H2 ).AutoFill Destination:=.Range( H2:H & LastRow) _ , Type:=xlFillDefault .Range( V2 ).AutoFill Destination:=.Range(...

Excel: To Copy Multiple Cell Values in One Cell, department names, cell values

department names, cell values, namee: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: Copy macro problem, c4 c5, input sheet

c4 c5, input sheet, sh2: Matt, see the modified code below. I now use a copy and separately as Pastespecial for values and formats. Private Sub CommandButton2_Click() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range Dim v1 As Variant, v2 As Variant ...

Excel: To Copy multiple cell values in one cell, department names, cell values

department names, cell values, namee: This line If Len(Sheets( sheet2 ).Cells(OutVar, 4).Value) 0 Then Sheets( sheet2 ).Cells(OutVar, is the firsts line that updates column D - D is the fourth column, hence the value 4 - so just amend the 4 in all parts of this line. To do the same with...

Excel: Copy multiple worksheets (value-only) to new workbook, false application, dows

false application, dows, shs: Kathy, I would do it this way. sub copy6() Dim bk as workbook, sh as Worksheet worksheets(Array( Sheet1 , Sheet3 , Sheet5 , Sheet10 , Sheet15 , Sheet20 )).copy set bk = Activeworkbook for each sh in bk.Worksheets sh.Cells.copy Sh.Cells.PasteSpecial...

Excel: Copy and Paste Macro, cell a2, sh1

cell a2, sh1, sh2: Louis It works perfectly for me. this is where it does the copy and paste: r3.Copy sh2.Range( A2 ) It should copy to sheet2 with the upper left corner of the data in cell A2. If should either copy and paste or is should give you a message...

Excel: Copy and Paste Macro, autofilter, rw 1

autofilter, rw 1, pastes: LeBreeze, assumes the sheet with the autofilter is the activesheet when you run the macro and the data is already filtered for the criteria you want copied. Sub CopyFilteredData() Dim r As Range, r1 As Range, cell As Range Dim rw As Long, sh As...

Excel: Copy and Paste Macro, listindex, combo boxes

listindex, combo boxes, box2: Sev, this would be one interpretation of what you want. Combobox1 code Private Sub Combobox1_Click() Application.ScreenUpdating = False On Error Resume Next If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If If ComboBox1.ListIndex...

Excel: Copy and Paste Macro with Merged Cells, data values, ogilvy

data values, ogilvy, macros: Ginelle, sending me the macros that don t work won t help much (I write those all the time g ). If you want to send the workbook and tell me what you want to copy where and what you must have as a result (do you need the merged cells as a result - should...

Excel: Copying data from varying rows from one workbook to another, input cells, report sheets

input cells, report sheets, input sheet: Tammy, Your explanation is pretty confusing, but this is what I came up with. I doubt it is what you want. Sub ABCD() set bk1 = workbooks( Fiberline Morning Report.xls ) set bk2 = workbooks( Bleach Room - 2009.xls ) set sh1 = bk1.worksheets( Input...

Excel: Copying Text, fundraising event, university campuses

fundraising event, university campuses, paste operation: Natalie, There are several functions which can be used to look up information from one sheet and make it appear on the sheet where the function sits. The information that is looked-up may be textual as well as numerical. So the brief answer to your...

Excel: Count cells with certain criteria, pivot table, follow ups

pivot table, follow ups, reference number: This sounds like a job for a pivot table - which will do it easily without formulas - you didn t specify version, so I ll assume NOT 2007 - data menu, pivot table and chart report, follow the wizard and drop the details you need into the appropriate areas...

Excel: Count Ifs, criterion

criterion: Dwayne, You ll simply have to use TWO COUNTIFS, each one counting the names in one of the columns B and C, according to the criterion in A and D. Then add the two COUNTIFS together. If you need additional help on making the individual COUNTIFS...

Excel: Countdown in Excel, hh mm ss, chip pearson

hh mm ss, chip pearson, excel ontime: Dena, put this in A1 (for example) Put in the date and time of the race Nov 1, 2009 (their site [http://www.nycmarathon.org/] said offical start times are coming and the clock there is counting down to midnight going into Nov 1. My formula produced...

Excel: Countif with dates, excel, text

excel, text, countif: It looks like the format of the cells have changed from text to date. You can either change the format back, for example using a formula like: =TEXT(A1, dd mmmm yyyy ) Or you could count using the date format with an array formula like: =SUM(--(MONTH(A1:A30)=7))...

Excel: Countif with a range, irow, search address

irow, search address, search range: If you are trying to have your code write a formula, as it seems, then you need to use Cells(irow, 6).FormulaR1C1 Try this: Cells(irow, 6).Value = =IF(COUNTIF( & Search.Address & ,bl) 1, Duplicate , Single ) Since your code is building the...

Excel: Countif unique and if function, cell c1, abc company

cell c1, abc company, citeria: Ed The countif doesnt really work with criteria, I.E. countifand, so what I usually do is combine the citeria and then count the unique criteria that I am looking for. For example You can use the & formula to combine the data The formula might look...

Excel: Counting consecutive numbers, consecutive numbers, final answer

consecutive numbers, final answer, consecutive weeks: Jill, ------------------------------ this version of the formula will work with zero stored as text or number =IF(OR(F2=0,F2= 0 ),0,IF(OR(E2=0,E2= 0 ),1,IF(OR(D2=0,D2= 0 ),2,IF(OR(C2=0,C2= 0 ),3,IF(OR(B2=0,B2= 0 ),4,5))))) Maybe that will help....

Excel: Counting the number of occurances, based on 2 criteria, numerical format, column c

numerical format, column c, occurances: Albert, First you need to add two columns to your spreadsheet. Label one Month and the other DAY OF WEEK . In the month column you are going to list the month in numerical format. For example January is 1, February is 2, and so on. The DAY OF WEEK...

Excel: Create a warning dialog box if text already exists, chip pearson, fuller explanation

chip pearson, fuller explanation, ogilvy: Joyce, Chip Pearson has laid out a complete page on discussion how to do this, so you can get a much fuller explanation there, with illustrations: http://www.cpearson.com/excel/duplicates.aspx If you don t think that is what you need, then you can...

Excel: Creating a conditional list with no blank rows, blank rows, autofilter

blank rows, autofilter, project titles: I am not sure how the formula returns several rows of data since formulas can not do that so that part confuses me. You should be able to use Data Filter, AutoFilter but not having seen your data then I can t say for sure and I don t WHERE you want your...

Excel: Creating Menus for Macros, data validation, drop down menus

data validation, drop down menus, cell a2: Joff Drop down menus can most easily be created by using the data validation feature. First create your drop down list somewhere on your worksheet. for example cell A1=ZZ, cell A2=BB, cell A3=TT, etc. then go to toolbar/data/validation In the...

Excel: Creating a submit button macro in an excel sheet., column headings, control toolbox

column headings, control toolbox, sheet1: Merged cells are always a problem. I assume there are no merged cells in the destination. so I would alter the code as Private Sub Submit_Button_Click() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range Dim v1 As Variant, v2 As Variant...

Excel: Creating a Time sheet for my company, dddd, monday to sunday

dddd, monday to sunday, time sheet: Ryan Blehar, B11: =NOW()-WEEKDAY(NOW())+2+COLUMN()-2 I put in the Column()-2 because in column B I want to produce a zero and Column(B11) produces a 2. Now the 2 s in the above formula cancel out, but I wanted you to understand the formula in case...

Excel: Creating a Toolbar Addon button, page word document, rcell

page word document, rcell, change case: James, Here is some good information: http://support.microsoft.com/kb/830502 How to customize menus and menu bars in Excel http://support.microsoft.com/kb/166755 XL97: WE1183: Customizing Menu Bars, Menus, and Menu Items This is for excel 97...

Excel: Creating a time sheet for my company, time sheet, monday to sunday

time sheet, monday to sunday, excel sheet: I assumed that your company time sheet was just an EXCEL file. Based on that assumption then I thought you would recognize this as just another formula you would need. Therefore, type the formula I provided in the cell where you want Monday, June 29 to...

Excel: Cross Reference Lookups, sumproduct, excel functions

sumproduct, excel functions, unary: Milly, If there is only ONE combination of each pair of Alpha & Num, you can use DGET() to find the matching Value. You could also use DSUM. You can also use the advanced form of SUMPRODUCT(), which I actually prefer in this case [because it...

Excel: Cumilative, tonnage, microsoft excel

tonnage, microsoft excel, formulae: Ravi, A Cumulative equation is simply the sum of the number above it, plus the new number to its left. The first equation in the cumulative column has to be different, because there is no number above it... so the first equation is simply a reference...

Excel: Curiosity on behalf of an Excel novice, mathematical algorithm, excel spreadsheet

mathematical algorithm, excel spreadsheet, different numbers: James, If I may re-phrase your question, it sounds like you have a column of numbers and you want Excel to pick out the ones which add up to a given result. Actually, this is quite difficult to do because there are so many possible combinations....

Excel: Current Time Display in Cell in Excell Spreadsheet, excel ontime, chip pearson

excel ontime, chip pearson, cell a1: Rod, There is not built in support for flashing. There is not built in support for displaying a clock. You can use VBA to start and event and to periodically perform an action. Chip Pearson documents these techniques at his site: http://www.cpearson.com/excel/ontime.aspx...

Excel: Custom function, macro sheet, index range

macro sheet, index range, c range: easiest fix surely is to have a third argument, being the OFFSET value (though you say offset was crossed off your list, which I m not sure about) OR the cell reference you want to use - so =skipby( cards ,3,a2) if you wanted to use the value in A2...

Excel: How to calculate a total for the week from my daily totals?, column c, question thanks

column c, question thanks, time type: The custom format I gave you was intended only for column C which was the calculation of time, which I imagine wouldn t need pm/am. For the rest, two possible fixes - one, keep the data as you have it, but go back to the original format for these...

Excel: calculating percentages in excel, calculating percentages in excel, free microsoft excel

calculating percentages in excel, free microsoft excel, percentages in excel: Without programming a macro, the easiest thing would be to just use the AUTOFILL feature to copy the formula down all of those rows. I cover AUTOFILL in my very first Excel 101 course: http://www.599cd.com?GOXL101&key=AllExperts Let me know if you...

Excel: calendar, street dates, key date

street dates, key date, peltier: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: cell from one workbook to another, clever method, m24

clever method, m24, text strings: Teresa, You can use some clever method to CALCULATE the address [BANKRECONCILIATION]JUNE 09!$M$24 Although you probably don t need the $-signs here, so [BANKRECONCILIATION]JUNE 09!M24 will be adequate. ...

Excel: How to change a date in a cell reference, cell reference, reference question

cell reference, reference question, cycle time: PAUL If I understand the question you are wanting to change the date in the link to the workbook so there is a workbook titled Office Cycle Time Mar09_final.xls and you want to change it to Office Cycle Time Apr09_final.xls. You arent just changing the...

Excel: charts, desirable results, source data

desirable results, source data, two rows: Yehuda, Read my answer again. It tells you just what to do. The 12 values MUST be in adjacent cells, B1, B2, B3, B4, etc. Otherwise there are gaps in the data and your chart looks funny. My pervious answer tells you EXACTLY how to create this. Please...

Excel: circular reference problem, iteration count, invisible formula

iteration count, invisible formula, time profits: Dave, I m afraid to say that nothing is obvious about your question. What. for example, is the systems execution section of Excel? To my knowledge, everything either calculates, or it doesn t. There aren t sections that will calculate while...

Excel: Why does my code skip 2 lines?, header footer, ogilvy

header footer, ogilvy, m4: DD, vbNewline puts in the end of line indicator for whatever system excel is running on. In Windows, it puts in two characters - same as VBCrLr (it puts in a character 10 and a character 13). Apparently header interprets both of these characters as Line...

Excel: column alignment, free microsoft excel, column alignment

free microsoft excel, column alignment, format cells: Right-click on the cells (or column) select FORMAT CELLS and then pick the ACCOUNTING 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...

Excel: compare two columns in Excel, countif, final result

countif, final result, c10: Anna, You can use a variety of methods. I would use COUNTIF, which would not only tell you if the number exists in Col 1, but HOW MANY TIMES it exists. Just add =COUNTIF($A$10:$A$1510,B10) to cell C10, and copy it down 500 times. The resulting...

Excel: complex numerical sort for mixed part numbers, excel help file, numeric digits

excel help file, numeric digits, order of precedence: Steve, Does The alpha 0 shall be considered as numeric zero mean that the LETTER O, should be considered as numeric zero? If so, you ll need to use the SUBSTITUTE function to change all occurrences of the letter O to the numeric 0. Then sort...

Excel: Can control toolbox check box data be manipulated into a pie chart?, check boxes, pie chart

check boxes, pie chart, trues: Answer is yes. Check box results are linked to a cell. If you have not done it do it by maintaining properties of check boxes. then, you need to create a count of TRUEs. Based on this count, you can create a pie chart. Based on more data (with excel version...

Excel: How can I convert two by two table into an equation, cell a1, example purposes

cell a1, example purposes, column c: Nahed, Make a table like this grp Male Female gp1 4443605 4349290 gp2 1553674 1633777 gp3 600161 618546 gp4 373028 386441 gp5 264980 241311 gp6 191201 135555 gp7 119801 85936 then you can return a number with Assume...

Excel: convert text to time in excel, allexperts answers, numberformat

allexperts answers, numberformat, left c: allexperts answers tend to be addressed to the specific questioner, so there is nearly always a need to adapt - the answer in question wasn t one of mine but is easy enough to adapt For Each C In Selection.Cells Text string must end with A or...

Excel: go to copied cell reference, lotus macros, macros excel

lotus macros, macros excel, applicable code: Laurence: Are you trying to put the all of the code in a worksheet, as 1-2-3 required and as EXCEL 4.0 required? IF so then I d suggest you abandon that approach and use VBA, the programming language for EXCEL since version 5.0. Typically the 1-2-3 macros...

Excel: copy the existing rows format and formulas (exclude the values), ogilvy, r1

ogilvy, r1, cells: Piju, I would use something like this: Sub AAA() Dim r As Range, r1 As Range Set r1 = Range( b9 ).EntireRow Set r = r1.SpecialCells(xlFormulas) For Each cell In r cell.Copy cell.Offset(1, 0) Next End Sub if you want to copy formatting...

Excel: copy paste format, destination format, copy paste

destination format, copy paste, maxine: Maxine, I cannot come up with an explanation for this. I doubt its a virus! Were the original dates entered without a year? If so, that s a possible explanation... somehow the new workbook s year default is set to 2005. But really, I have never...

Excel: How to count if two cells contains similar text, free microsoft excel, single sided deafness

free microsoft excel, single sided deafness, excel tips tricks: 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: count the specific data in multiple sheets, customer names, customer name

customer names, customer name, sales reps: Arsa You can use th countif formula. Your references to range just have to include the sheet name. The formula might look like this. =COUNTIF(C1:C20, a )+COUNTIF(Sheet2!C1:C20, a )+COUNTIF(Sheet3!C1:C20, a ) Where the range of customer names in...

Excel: countif formulas, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: You re going to have to make a third column with an IF function to see if BOTH of your conditions are met. In essence, IF it s tech 3, AND hours logged is greater than 3, THEN put a 1 here, else put a 0 here. Then just count up all of those 1 values. =IF(AND(A1=3,B1...

Excel: countif statement, excel, countif

excel, countif, sumproduct: You can use the SUMPRODUCT formula for that, something like: =SUMPRODUCT(--(A1:A100= B ),--(D1:D100 0)) This will return the number of records that fulfill both conditions. The ranges must have the same number of rows, or the formula will return an error....

Excel: Can you create a pie chart from data in control toolbox checkboxes?, check boxes, pie chart

check boxes, pie chart, abbie: Hi Abbie, Yes, you should be able to do that. First you need to set up each checkbox to place its result in a cell (use the linkedcell property). After that you can use countif functions to count the number of true s and falses with which you can build...

Excel: creating a formula, ogilvy, character string

ogilvy, character string, answer the question: Maria if you have the men s names in column A of the summary sheet starting in A2 and you have the Name of the sheet in row 1 starting in column B and the sum for each man is in column S on each of the monthly sheets, then in B2 of the summary sheet...

Excel: creating a timesheet covering the past 2 years, paystub, column headings

paystub, column headings, blank rows: Ruth, You have it already figured out well. Your ideas for the columns you need are right on. I d also add a Day-Of-The Week column which shows what Sun, Mon, Tue, etc, depending on the date. You can add a lot of features to a spreadsheet...

Excel: csv's with more than 256 rows, csv file, software application

csv file, software application, variables: 2500 data variables? That is a lot of variables to have to keep up with and manage. I suspect you mean you 2500 different columns of data. And don t you mean convert the file to be ROW based? Regardless, to answer your question, then yes there is a way...

Excel: .csv data source for Pivot Table, excel pivot table, csv data

excel pivot table, csv data, csv file: If there s a $12.00 format in the csv file, then it can t be a number, because csv files can t hold formatting per se. So it IS text and you d need some procedure perhaps VBA to open the csv file, change that money column to real numbers, save it as an excel...

Excel: Daily Worksheet (macro?), cash flow worksheet, cashflows

cash flow worksheet, cashflows, ogilvy: Matt, It sounds like you need a sheet that is used as a template. It has all the headings and formulas, but the actual values to be entered are blank. Then each day you can have the macro copy this sheet and name it for the appropriate date when it will...

Excel: Data Charts, team members name, conditional formatting

team members name, conditional formatting, tab team: J Madden, If you still need an answer to this, I have some thoughts. Your question is, however, vague. You state the problem as: I want to put a complete line throughout the chart showing the players average which is on the far right of that...

Excel: Data Sorting, cell d2, cell a1

cell d2, cell a1, jane 5: When asking a question, please make very clear exactly where all the data is located. In this case, I ll assume e.g. that the label Name is in cell A1, # of Surveys is in B1, John is in B2, etc. Row 5 is completely blank, and cell C6 is the group percentage...

Excel: Data Sorting, nature of the beast, jane 5

nature of the beast, jane 5, data sheet: I m a bit confused. sort the whole row ?? Don t you mean column? If the data being sorted has formulas which reference anything outside that row (if row 3 has a formula referencing something in row 5, for example), you can t be certain of the result. It s...

Excel: Data transfer between two worksheets, blank row, imput

blank row, imput, data input: Very confusing description: 2 sets of data input into one column. I don t understand. Save that in 2 rows in another sheet. I don t understand that either. Also you want columns transposed into rows? How does 1 column take up 2 rows? What do you mean by 2...

Excel: Data Validation Dependent List, data validation, word selections

data validation, word selections, word range: Lisa, The method you are using, INDIRECT(D15), makes the Data Validation refer to a NAMED RANGE, e.g. there is a named range ACCOMMODATION somewhere in your workbook. The reason the other two-word indirect references don t work, is that named ranges...

Excel: Date // Q, format cells, popup menu

format cells, popup menu, thanks in advance: 1. Select the cells you want to format 2. Select the Format drop down menu 3. Select Cells This will make a popup menu titled Format Cells appear 4. Select the Number tab 5. Select Date in the Catagory: section 6. In the Type: section...

Excel: Date Range Specifications, autofilter, knowledgable

autofilter, knowledgable, desperate need: Teej, You can probably accomplish this, but the complexity involved depends on how your main database is set up. If every employees has ONLY one row of data, then it is [relatively] easy to find all items related to that employee. If there are multiple...

Excel: Date related formula, png file, number of days between two dates

png file, number of days between two dates, days between two dates: Dave, I corrected the formula for 2009 and put it in I4. I put all the combinations in G and H 1)completely in 2009 2) start in 2009, end in 2010 3)Start in 2008, end in 2009 4) Start in 2008, end in 2010 the formula calculated all of those correctly...

Excel: Date time extrapolations, time, weekday

time, weekday: Say your dates start in cell E2. Make sure that col D is formatted as numbers; then in D2, enter the formula: =HOUR(E2)*100+MINUTE(E2) and copy down col D. This translates the time in col E to a number; if E2 has the time 6:53am, then D2 will be 653....

Excel: Dates - Today minus date column, ogilvy, current date

ogilvy, current date, answer the question: Laura, =today() will put today s date in that cell. It requires that calculation be either set to automatic or you cause the sheet to calculate. If that didn t answer the question, please followup with what else you need to know. -- Regards,...

Excel: Default text in a input box, default property, default text

default property, default text, input box: Joseph, Certainly. See the example below: ans = InputBox(prompt:= Enter Your Phone Number , _ title:= Phone Number Entry , _ default:= Enter Value Here ) So you just put the text you want as the value assigned to the default...

Excel: Delete Duplicate Blanks rows, blank row, addin

blank row, addin, looper: Sub aidan20() Set currentcell = Worksheets( Sheet1 ).Range( A2 ) For looper = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row Set nextCell = currentcell.Offset(1, 0) If Len(currentcell.Offset(-1, 0).Value) = 0 And Len(currentcell.Value) = 0 Then...

Excel: Deleting columns, visual basic programming, accum

visual basic programming, accum, macros: Hunter, Sub deletecolumns() Dim r As Range, l As Long, i As Long Set r = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) l = r.Column For i = l To 1 Step -1 If Application.CountIf(Columns(i), *Accum* ) 0 Or _ Application.CountIf(Columns(i),...

Excel: #DIV/0 error, sum function, best route

sum function, best route, zero value: I see you like the sum function! As I mentioned before, it s not necessary - =sum(a1:A100) is quicker than typing all the cells, but =sum(DoASum) which is what you have written means you are simply asking excel to evaluate the formula twice when...

Excel: Difference in Dates, column c, ogilvy

column c, ogilvy, cpearson: Tom, http://www.cpearson.com/Excel/datedif.aspx documents the datedif formula. (I believe it is in Excel help only in version xl2002, but the function has existed in all versions). so if the data was in row 2 =datedif(B2,A2, y ) would...

Excel: Display the last non blank cell most to the right., blank cell, right question

blank cell, right question, d11: Daisy, In s9 =IF(COUNTBLANK($D9:$R9)=15, ,INDEX($D9:$R9,1,MATCH(LARGE(IF(ISNUMBER($D9:$R9),$D9:$R9+200,IF(ISTEXT($D$9:$R9),CODE($D9:$R9),0)),1),IF(ISNUMBER($D9:$R9),$D9:$R9+200,IF(ISTEXT($D9:$R9),CODE($D9:$R9),0)),0))) Enter with Ctrl+shift+enter...

Excel: Donor data, donor data, campaign number

donor data, campaign number, donor name: Sarah, The answer to this depends on exactly how your data is organized. Do you have a table with four columns: DONOR NAME, CAMPAIGN, DATE, and AMOUNT? or is it something else? It would also be helpful to know which version of Excel you re...

Excel: Drop Box depending on selection from Previous Drop Box, free microsoft excel, combo boxes

free microsoft excel, combo boxes, excel tips tricks: I do not know how to code this in Excel. I can show you how to do it in Microsoft Access though: I get asked this question all the time. You ve got two or more combo boxes and you want the first one to filter the second one. I get asked this so much that...

Excel: Drop Down List Help, resource types, wits end

resource types, wits end, resource type: Stephen, Yes, this is possible. Apply the ADVANCED FILTER to your database. The ADVANCED FILTER needs a criterion range to work... so create the range it needs, and feed the values from your drop down selections into the appropriate cells of the Criterion...

Excel: Drop down lists / data validation lists, data validation, excel sheet

data validation, excel sheet, dropdown list: not sure what your OR statement is doing - I d try =if(a2= Beginner ,Table1,if(a2=Intermediate ,Table2,if(a2= Advanced ,Table3, ))) for a simpler formula - I ve not managed to generate your error message, but I imagine at some point it had a list...

Excel: DropDownList Macro, field headings, personnel number

field headings, personnel number, autofilter: 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: Dsum question and Copy from closed workbooks macro, running log, column header

running log, column header, loop through: I don t know what column the CI values might be in, but if they re in column B, for example, and the Route is in volumn A, these formulas will do what you want: =SUMPRODUCT(N(A9:A16= V9 ),B9:B16) =SUMIF(A9:A16, V9 ,B9:B16) or use A2 for the location of...

Excel: Dynamic Charting - Odd Chart type, causes problems, custom tab, correct values

custom tab, correct values, dynamic range: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. I assume all the code will be in the workbook! And what is the issure you re having with the code?? Mark your calendars...

Excel: Dynamic Copy & Paste, e5 f5, header columns

e5 f5, header columns, response button: Matthew, Dim rng As Range Dim cell As Range Dim v As Variant v = Split( B3,C3,D3,E3,F3,G3,B28,C28,D28,E28, & _ F28,G28,G29,F29,E29,D29,C29,B29,G4, & _ F4,E4,D4,C4,B4,B5,C5,D5,E5,F5,G5,B30, & _ C30,D30,E30,F30,G30,G31,F31,E31,D31,C31,...

Excel: Dynamic range in VLookup Function in VBA, target value, selection range

target value, selection range, column 8: Blair, Looking at the code, you are looking at a single row - is that what you want to do? Dim r as Range, m as Long, valve as string valve = something for m = 3 to 20 with worksheets( Valves Selection ) set r = .Range(.Cells(m,8),.cells(m,17))...

Excel: Dynamically adding rows to a table, cell c5, pivot tables

cell c5, pivot tables, pivot table: You don t use it as a formula, you use it as a defined name, then you can branch to it, etc. If you entered this formula in a cell, you d also get #VALUE! =OFFSET(ptRMA!$A$5,0,0,20,1) Maybe you should send me a sample of the workbook (bobumlas@yahoo.com,...

Excel: Dynamically populate rows based on a string in a field above, cell b2, company abc

cell b2, company abc, string s1: Ashleigh this code loops through column A of the activesheet looking for entries that have a semi-colon in them. When it finds them it builds multiple records as you show in your example Column A: single and multiple segments Column B: Company name...

Excel: Dynamically populate rows based on a string in a field above, cell b2, company abc

cell b2, company abc, cell b1: Ashleigh, This is a difficult problem for equations only . A macro/VBA solution would be much easier. However, I don t offer Macro/VBA advice on this website. Try asking Tom Ogilvy this question and see what he says. Some initial thoughts...

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

drop down list in excel, data validation, column headings: Sarah, Quite Easily Done! {QED) I get this question so often that I have prepared a canned answer for it. Drop-down lists are done with data Validation, hence my use of the word validation throughout. I assume you already know how to make a single-layer...

Excel: EXCEL FORMULA, attendance number, bad boy bill

attendance number, bad boy bill, conditional formatting: Christine, This is quite easy. On the Master sheet, add a column that sums the attendances per row. Then you can lookup the attendance number from any other sheet based solely on the person s name. You d use VLOOKUP to do this. Put one VLOOKUP...

Excel: EXCEL split 1 row into multiple rows, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: WITHOUT programming, I don t think it could be done unless you made a REAL complex string maniupulation function with LEFT, RIGHT, MID functions. I wouldn t want to try it that way though. Another alternative would be to import the data into MS ACCESS which...

Excel: EXEL if statement, excel function, catalog data

excel function, catalog data, k26: Gary, What you re asking is to look up a price from a catalog-like data table, using multiple criteria. There are several ways to do this. All of them hinge on the fact that there will only be ONE ITEM in the catalog/data table which meets the...

Excel: Excel 2000, question pool, blank cell

question pool, blank cell, cell contents: Gary, You didn t ask me this question - I found it in the question pool. When you do edit= Find or in xl2007 use the Find and Replace button on the right end of the Home tab, there should be an options button (click that) or there should be options offered....

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

free microsoft excel, excel tips tricks, blank cell: You can use the ISBLANK function, and enclose that in a NOT function if you want to find the next non-blank cell. If your data is numeric, Excel only stores the VALUE of the data (1 instead of 1.0). If you care about that . then you need to format your...

Excel: Excel 2003 - automatically transferring data from 1 sheet to another, column headings, macros

column headings, macros, many thanks: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: Excel 2003 box displays an X, vb script, exact size

vb script, exact size, spreadsheet: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: Excel 2003 formula, search portland, ogilvy

search portland, ogilvy, c2: Melanie, =if(C2= Portland , yes , no ) if Portland will be a substring of a longer string such as Portland, Oregon in cell C2 Then: =IF(ISERROR(SEARCH( Portland ,C2,1)), no , yes ) (Note that this is NOT case sensitive). This will...

Excel: Excel 2003 - lock a cell automatically when data has been inserted into it?, many thanks, spreadsheet

many thanks, spreadsheet, cells: ANY cell? What do you mean by repeated in the corresponding cells beneath it? Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour...

Excel: Excel 2003 table, pivottable field, header rows

pivottable field, header rows, instructor average: This can be done with a PivotTable as follows: 1. Each column must has a unique header name. Right now you have two header rows in rows 2 and 3. I would recommend you combine them into one. For example 1.1 Course Content Description and so on for the...

Excel: Excel 2007, microsoft help, exclamation marks

microsoft help, exclamation marks, exclamation mark: it s a warning rather than anything else, but if you are sure you don t want it, click on one of the exclamation marks to the left of them, then go to error checking options and remove the checks from the error checking options that you don t want excel to...

Excel: Excel 2007, homework problem, odd problem

homework problem, odd problem, group sheets: Kim, I suspect you are in school and this is part of a homework problem. Its an odd problem though, because grouping sheets is very rarely used in practice. Ask your teacher why you ve been asked to do this. It is very easy to group sheets...

Excel: Excel 2007, format button, 19 june

format button, 19 june, a1: No macro needed. Select all the cells for which this is to happen, use Home/Styles/Conditional Formatting. Click New Rule, click Use a formula to determine which cells to format , Enter this formula (substituting the real active cell for A1): =AND(A1 ...

Excel: Excel 2007, master matrix, table solution

master matrix, table solution, excel function: Chris, This sounds like an application for a pivot table. It can summarize your whole database in one table, instead of on separate sheets. have you tried a pivot table solution? Your idea of updating the secondary sheets and having the master...

Excel: Excel 2007 - how to add up only specific fields in an excel spreadsheet, currency fields, pivottable

currency fields, pivottable, excel spreadsheet: Sally, 1. Add a column labeled TYPE 2. In this column for each row that has pounds in it type Pound and for all others type in Hours It should look like this: TYPE AMOUNT Pounds £2,333 Hours 10 Hours 10 Pounds £4,432 Pounds £6,589...

Excel: Excel 2007 Change background colors, change background colors, format button

change background colors, format button, excel worksheet: Select all the cells whose colors change according to your criteria, then use Home/Conditional Formatting, select New Rule, select use a Formula to determine which cells to format, then enter this formula: (Assumes A1 is the ACTIVE cell): =TODAY()+30 A1...

Excel: Excel 2007 Data Table Lookup, cell f15, variable column

cell f15, variable column, drop down boxes: Paul, Your table looks like its already working! But I ll assume that you just typed in the correct answer in H42 instead of having an equation do it. What you want is to use two MATCH functions, one for each input F14 & F15, and feed the...

Excel: Excel 2007 dropdown rules, vlookup function, input value

vlookup function, input value, dropdown menu: Chris, Use a VLOOKUP() with the dropdown selection as the input-value. Build a small table (using your list on sheet 2 as the first column) that gives the price for each machine. Then declare this table to be the lookup-array in the VLOOKUP function....

Excel: Excel 2007 Formula, workdays, yy

workdays, yy, first thursday: Tari, You can begin to approach this problem by asking if the WEEKDAY(date,1) of 1/1/YY is 6 [that is, if it is not Friday or Saturday] For 2009, WEEKDAY(01/01/09) = 5, a Thursday. IF 1/1/YY is 6 is TRUE, then the number of workdays in...

Excel: Excel, dumb question, gross profit

dumb question, gross profit, original cost: Amanda Sounds like the issue is definitional, what does markup mean. Generally the term markup means the amount or percentage that an item will be increased from the original cost. And gross profit is the difference between cost and retail as a percentage...

Excel: Excel, course websites, f1 key

course websites, f1 key, challenges: everyone learns differently, but my own method is to find challenges and then work out ways of solving them - which is partly why I ask questions on here - you could read the various newsgroups or past answers on this site for ideas. Other than that, the...

Excel: Excel, excel, error

excel, error: Can you post the lookup formula that you are using? It will help me determine how Excel considers data as a cell reference, as that is not usually the case. Are you using the flight numbers directly in the formula or references to cells where the flight...

Excel: Excel, format cells, control key

format cells, control key, data input: At face value it sounds as though there is already protection on the cells - this would be what I would generally do to allow data input in areas I want - at which point the process is when tabbing or entering the cursor will move to the next available cell....

Excel: Excel, selection move, vba

selection move, vba, 1234: Aleksander, Create a column next to the column you want to change. Then fill it with the =PROPER(text) function. Refer to the cells in the original column where I have written text, such as this: =PROPER(A5) When you re done, you ll...

Excel: Excel, answering questions, aidan

answering questions, aidan, xls: Word is easy, as any changes to the normal template will do this - so open the normal template and update the footer. Excel is trickier to make this automatic - I would PROBABLY have a macro on the personal.xls Sorry for the delay in replying - my daughter...

Excel: Excel, consecutive numbers, number sequence

consecutive numbers, number sequence, nums: Russell, First, let me know if I have understood your problem correctly. Then, if my restatement of the problem is correct, read my answer again. Use the SIGN function to generate the +1, -1. (although yout IF stat ement is just as good)Then use...

Excel: Excel, meaningful answer, spreadsheet

meaningful answer, spreadsheet, clarity: Sue, There isn t enough detail for me to provide a meaningful answer. I don t know what INCORRECT means. I suspect you mean have values that don t match , or perhaps, the total payments made don t add up to the total owed . If you can provide...

Excel: Excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: Use NETWORKDAYS. See this tutorial: http://www.599cd.com/tips/excel/workdays?key=AllExperts Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go...

Excel: Excel, variable inputs, chart excel

variable inputs, chart excel, logical functions: Russell, It s taken me a while to come up with the basic idea which will make this work, but perhaps (at last) I have done so. What you ll need to do is learn a lot about the LOGICAL functions, so start studying! The hard part is to create...

Excel: Excel 3D Chart wall picture, excel 3d, 3d chart

excel 3d, 3d chart, 3d charts: I tend not to work with 3d charts as they can be quite difficult to get to look right, and using other charts can present data equally (or more) effectively - my charting guru site is http://peltiertech.com/Excel/Charts/ which has more ideas than I could...

Excel: Excel 97 macros, dell dimension 4600, excel 97 macros

dell dimension 4600, excel 97 macros, dell 4600: Charles, Sorry to say, I have no idea what could be causing this problem But anything involving Vista is suspect in my book. I d suggest that you try using a non-vista computer for that second computer and see if you get the same result. Hopefully...

Excel: Excel Aging, magic, yahoo

magic, yahoo: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: Excel Auto-Population, will find dozens, vlookup

will find dozens, vlookup, number 17: Barry, Certainly you can. What you need is to use one VLOOKUP equation for every field (manufacturer, model, serial number,etc ) you want to populate. Hopefully you have a DATABASE of all this equipment, on ONE sheet. Each piece of equipment...

Excel: Excel Chart, dual bar, custom labels

dual bar, custom labels, example charts: You are very kind - in this instance, I think I ll refer you to http://peltiertech.com/WordPress/example-charts/ where about a third of the way down the page it talks abut this Comment from Jon Peltier Time: Tuesday, August 19, 2008, 10:29 pm Patrick...

Excel: Excel Chart Properties, data values, mouse events

data values, mouse events, good answer: Jim I am pretty sure that you can do what you want to, but I am not an exepert in charting and can t really give you a good answer. I suggest you reask the question of another allexperts person and ck the resumes for someone who deals in this area. I m...

Excel: Excel Chart problem, category axis, line graph

category axis, line graph, ogilvy: Kathleen, If you want to send the file, I will take a look twogilvy@msn.com Usually in a line graph the X axis is a category axis - so I would really need to look at the data before I can say what you might need to do. (also tell me what version...

Excel: Excel in a Citrix Environment, annoying behavior, excel expert

annoying behavior, excel expert, computer specialist: Tanya, I m sorry but I have no idea why you d have this problem. This question is more properly directed to an network or computer specialist, than to an Excel expert such as myself. Good luck and I hope you can figure out this annoying behavior. ...

Excel: Excel Complex Logic, computer logic design, logic problems

computer logic design, logic problems, logical functions: Joe, By this kind of material , I assume you mean the Logical Functions. I m not able to recommend a book to you, because I know all the logical functions inside and out and don t really need a book. My background includes extensive experience in...

Excel: Excel Conditional Formatting, cell c5, logical statement

cell c5, logical statement, time values: Alan, Yes, you can check for more than one condition. You must use the FORMULA IS mode (that s Excel 2003 terminology), OR, create TWO separate conditional formats, each of which checks its own VALUE-IS. Formatting has absolutely nothing to...

Excel: Excel: Counting number of occurnces of a word which is offset to a word (to be searched), critieria, column c

critieria, column c, richard florida: Kunal The countif formula will not work with more than one critieria, so you can t do a countifand formula. What I do to work around it is to combine the criteria that are in the columns and then count the resulting combined criteria that are the same...

Excel: Excel - comparing date fields, b1, pam

b1, pam, array: Pam You can use the =SMALL() formula which might look like this--- Assume the dates are in the cells A1, B1 and C1 =SMALL(A1:C1,1) The formula says look in the array from A1 to C1 and return the 1st smallest number of the array. Remember...

Excel: Excel Data manipulation, data manipulation, battalion

data manipulation, battalion: Marty, People always seem to want to make duplicate copies of their data! I d ask WHY you want to do this. if it is simply to print the lists, you can use the FILTER (either AUTO or ADVANCED) to HIDE the rows of everyone not in A. Co, then print the...

Excel: Excel - Date based on cell change, target cells, column c

target cells, column c, hi bob: The worksheet_Change is triggered, but your code kicks it out because of Target.Column = 3... You can change that to If Target.Column 4 and it should be OK. If not, add all this to the sheet code to run whenever the worksheet calculates. Clicking on a...

Excel: Excel Date conversion issue using Text to column, free microsoft excel, conversion issue

free microsoft excel, conversion issue, excel tips tricks: Why not just use the YEAR, MONTH, and DAY functions? =YEAR(A1) =MONTH(A1) =DAY(A1) 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...

Excel: Excel Date Entry, date entry, copy paste

date entry, copy paste, e mail: Jack The concept is to use either now() or today() to date stamp the entry, but as you know the formula is volatile and changes any time it is opened or a new entry is made. The way to prevent this is to use copy paste special values and copy only the...

Excel: Excel Date Format - follow up, data validation, novice users

data validation, novice users, date entry: Michelle, The empty date cell you are trying to create can t be done the way you wish... at least, I don t know how to. POSSIBLY by creating some exquisitely complex CUSTOM NUMBER FORMAT, you may be able to make this work, but I doubt it. If you...

Excel: Excel Drop Down Lists, value budget, creating a budget

value budget, creating a budget, january february march april: Colt, As I say often, with Excel, ANYTHING is possible. Your concept for the monthly tracking sheet seems fine. This would also lend itself to crating charts in various ways, so you could chart each type of expense as its own line, VS month...

Excel: Excel date formula, conditional formatting, colour change

conditional formatting, colour change, jan 29: I do, but it s pretty much impossible to use one month after today s date without detailed description of what that means. For example, if today is March 30, then 1 month after is April 30. But if today is March 31, then what s 1 month after? There is no April...

Excel: Excel dropdown list ..., list of usernames, excel dropdown

list of usernames, excel dropdown, database column: Robin, If you select a name from a dropdown list, and want it to be part of another cell, then that other cell has to refer to the cell where the dropdown is. If the name is PART of the contents of a cell (whether the cell is merged or not is...

Excel: Excel equivalent of 1-2-3 RangeExtract, lotus data, lotus 1 2 3

lotus data, lotus 1 2 3, equivalent function: It has been a few years (at least 10) since I have used 1-2-3 but I was well versed in it. Having said that then, if I recall what Range Extract does, Data, Filter, Advanced Filter is the Range Extract equivalent. Advanced Filter is like a mini-data...

Excel: Excel Files Comparison, left hand corner, conditional formatting

left hand corner, conditional formatting, index column: Erica, I d ask why you can t sort them? You can always get them back the way they were before the sort, if you first add what I call and INDEX column. This is simply a column filled with numbers from 1 to whatever, so each row has a unique number....

Excel: Excel Find VBA, followup question, g1

followup question, g1, previous question: Charlie, No, the offset is in exactly the right place. Sub ABC() Dim r As Range, r1 As Range, r2 As Range Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Worksheets( Entry ) Set sh2 = Worksheets( dBase ) Set r = sh2.Cells.Find(What:=sh1.Range(...

Excel: Excel Formula, INDIRECT

INDIRECT: Hi John, You can use the INDIRECT function to convert a text string into a reference. Something like this: =INDIRECT( K:WashingtonWeekly Expenses & TEXT(A1, mmddyyyy )& [Expenses.xls]A !$K$14 ) Notes: It can be a little quirky with external references,...

Excel: Excel Formula, vlookup, excel formula

vlookup, excel formula, three sheets: Lois, There is a function (or two) designed exactly for this. You can use either VLOOKUP() or MATCH(). In either case, if the number is NOT found, the formula will return the #NA error. If the number IS found, MATCH() will return the relative...

Excel: Excel Formula, math problem, excel formula

math problem, excel formula, contradiction: A random # between your 2 values would be =556982+INT(RAND()*997865) so fill A1:A5185 with that formula. But to force 80% if them to be nearer the 556983 is a contradiction, I think -- then it s not random. And if that is possible, it s more a math problem...

Excel: Excel Formula, entry cells, excel formula

entry cells, excel formula, hi mike: Hi Mike, These are my favorite kinds of problems, Hopefully this does what you want: =IF(OR(D4=0,E4=0,F4=0),SUM(D4:F4),SUM(D4:F4)-MAX(D4:F4)) If any of the values is zero, add them all up, otherwise, add them all up and subtract the highest value....

Excel: Excel Formula Error #Value, excel, multiple if

excel, multiple if, vlookup: The problem with the formula is that, due to the location of the parenthesis, they are several formulas separated by commas, not a single formula that Excel can understand. You may try with something like: =IF((V54)= PP75 ,(V52*75%)+V54,IF((V54)= PP25 ,(V52*25%)+V50,IF((V54)=...

Excel: Excel Formulas reading left over data from a filter., array formula, excel formulas

array formula, excel formulas, visible data: Jason: Early versions of Excel do not have AVERAGEIF, so I am going to assume you are using Excel 2007 or later. I am familiar with filtering difficulties and having formulas only work on the visible data. Most functions will not work under this situation;...

Excel: Excel Function Error - #value, excel function, absolute references

excel function, absolute references, cell references: Mona, I did a little bit of experimentation with this. It appears as though formulas of this type are POSITION SENSITIVE. They work in some cells but not in others! The formula MUST be adjacent to the column it is checking the values in.... it cannot...

Excel: Excel filling a column, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: If you need to do this on a regular basis, just record a MACRO that puts them all in place for you, assign that macro to a button or keystroke, and then you re all set. Let me know if you have any other questions or comments. Also, be sure to visit...

Excel: Excel find and Replace, double spaces, spacebar

double spaces, spacebar, drop down menu: Andy, 1. Highlight the cells you want to change 2. Select the Edit drop down menu 3. Select Replace This will bring up the Find and Replace popup menu. 4. Select the Find What: box and hit the spacebar twice 5. Select the Replace With:...

Excel: Excel - finding the right formula, q2 q3, test q1

q2 q3, test q1, logic problem: Thomas, This is an interesting logic problem. I can see the start of several possible solutions. One attempt would be to convert the scores themselves into RANKS, using the RANK function. This, however, will not eliminate the problem with...

Excel: Excel formuals for timesheets, chip pearson, strange number

chip pearson, strange number, cpearson: Margie, Dates and times are stored as a single number. You can strip off the date or you can strip off the time in your formula if you need to, but normally you don t If you want the difference between two dates/times, just subtract the smaller from the...

Excel: Excel formula, warranty costs, warranty claim

warranty costs, warranty claim, max data: Michael Assume dates are in column D and teh warranty costs column is Column E. Assume the sheet name is DATA in another sheet =Sumproduct(--(Month(Data!$D$2:$D$1000)=Month(Max(Data!$D$2:$D$1000))),--(Year(Data!$D$2:$D$1000)=Year(Max(Data!$D$2:$D$1000))),Data!$E$2:$E$1000)...

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

free microsoft excel, excel tips tricks, countif: You could use SUMIF function. You can use the SUMIF and COUNTIF functions to add up or count values based on specific criteria, respectively. Here is a free tutorial that explains how to use both: http://www.599cd.com/tips/excel/sumif-countif?key=AllExperts...

Excel: Excel IF formula, free microsoft excel, validation tool

free microsoft excel, validation tool, excel tips tricks: To see if a value is within a range like that, you ll need to use the AND function inside your IF statement. Something like: IF(AND(B1 (A1*.9),A1 (A1*1.1),…) This will return TRUE if B1 is between 90% and 110% of A1. Let me know if you have any other...

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

free microsoft excel, excel tips tricks, excel formula: Days Old is simply the difference between two dates. For example, if you have Jan-1 in A1 and Jan-2 in B1, then B1-A1 is going to give you 1 which is the difference. See this tutorial: There are many different techniques in Excel you can use to break...

Excel: Excel formular, sheet1, sumproduct

sheet1, sumproduct, countif: Arika, Assume the first table is on Sheet1 and the second is on sheet2. You show a 1 next to Arika in the second sheet, but I would assume b1: dev C1: sit D1: tansfer/cancel A2: Arika A3: Yati in B2 put in the formula =Sumproduct(--(Sheet1!$A$2:$A$200=$A2),--(Sheet1!$B$2:$B$200=B$1))...

Excel: Excel functions, excel functions, columb

excel functions, columb, customer name: 1. Highlight the data 2. Select the Data drop down menu 3. Select DataTable and DataChart Report... This will open the DataTable and PivotChart Wizard Popup Menu. 4. In the Drop Data Items Here put Balance Amount 5. In the Drop Row Fields...

Excel: Excel "Group" setting for sharing, excel group, sheet tab

excel group, sheet tab, ogilvy: Maureen, I hope you haven t done any editing in the workbook Group means more than one sheet is selected (you should see more than one sheet tab highlighted). If you make a change in any of the selected sheets, the change is made in all selected sheets....

Excel: Excel Help, two bars, axes

two bars, axes, axis: I would probably just have the data show the month, with a legend indicating which colour represented which year - however, see http://peltiertech.com/Excel/Charts/axes.html for axis tricks and tips, and explore the rest of the very useful site for other...

Excel: Excel handling of time and days., duration time, time column

duration time, time column, colums: Alan, I don t know what information is calculated and what information is hand entered. I also an mot sure what the last two time colums are as the headers are not lining up properly. I assume you want some formula to come up with the value of 1 or...

Excel: Excel INDEX & MATCH, compatibility mode, optional parameter

compatibility mode, optional parameter, c13: without the file I cannot be certain, but I m confused with the match function - at least, as written - it SHOULD be match(Value,Range,OptionalParameter) but your optional parameter is a range of cells? If it helps, I have excel 2002, 2003 and 2007 installed...

Excel: Excel - inserting decimals, cell a1, decimal place

cell a1, decimal place, tool options: JUNE By the time you get this answer I bet you will have already thought of this. You can divide each number by 100 and if the cell is formatted with two decimal places it will read the way you want it to. So if Cell A1 contains 11150 and you use...

Excel: Excel issue, vlookup function, issue question

vlookup function, issue question, detials: Ryan, The principle should work across different workbooks. You ll have to tell Excel the workbook path name for the table-array, of course. To tell the truth, I have no direct experience using VLOOKUP across workbooks, but I believe that it...

Excel: Excel Lookup & return value?, excel lookup, eye and brain

excel lookup, eye and brain, value question: Jessica, Thanks for answering some of my questions, but you missed the most important ones! I ll repeat them and await your answers: A] The complexity (or not) of the answer hinges on this: will any given supervisor s name appear more than once...

Excel: Excel for Mac Help, vlookup function, manufacturer price

vlookup function, manufacturer price, break points: Garry, This is a common use for Excel. Its pretty easy to create and you can add lots of user friendly sophistication. I have made many of these for clients. What you need is two sheets, a DATABASE, and the RECIPE, or INVOICE. The DATABASE...

Excel: Excel Macro - Chart location, text frequency, blah blah

text frequency, blah blah, colorindex: Andy, Activesheet is a reference to the currnt active sheet. so ActiveChart.SeriesCollection(1).XValues = =Sheet1!R27C& p+4& :R35C11 becomes ActiveChart.SeriesCollection(1).XValues = _ = & ActiveSheet.Name & !R27C & p+4 & :R35C11...

Excel: Excel Macro and Linking, rank position, title column

rank position, title column, c experience: I think I follow this - I ll paraphrase to ensure we ve got that right though - you have a list of items which are ranked, and you need the ranking to remain in order - having paraphrased that, is there any reason why excel isn t working out the ranking itself...

Excel: Excel Multiple file opening, filefilter, ubound

filefilter, ubound, multiple file: Where you have the ???? you only have ONE file open. Work on it (it s already active) and when you re done you can use Activeworkbook.Close True and when you get to the 2nd loop you ll have the 2nd workboko active, work on it, etc. You re accessing the array...

Excel: Excel macro, word application, application question

word application, application question, sorry bill: In theory this is an OK idea at best if I may offer my opinion. If I were reviewing your resume I think I would have somewhat of a difficult time navigating it. But more importantly, in my opinion, your resume may never even get read because it will require...

Excel: Excel macro to copy and compare worksheets, excel macro, comparison data

excel macro, comparison data, csv: I m not absolutely sure what we are comparing but something like dim CurSht as worksheet set cursht=activesheet dim MyCheck as boolean Mycheck=true dim OffVar for offvar=8 to 28 if sheets( compsheet ).cells(offvar,1).value cursht.cells(offvar,1).value...

Excel: Excel macros, custom button, tool bar

custom button, tool bar: 1. Create a button and assign Macro. Click the button to run. 2. If you are using Excel 2003 or before, add a custom button to tool bar ,assign macro to it, click to run. 3. Tools, Macros, select macro and run (upto Excel 2003) 4. Developer Menu, Macros,...

Excel: Excel to notify user, countif, vacation requests

countif, vacation requests, robust solution: Elena, Just remember, that I can do this much faster than you, possibly. And while I m doing it, you can get on with the rest of your job and not have to worry about how to make this work. Once its done it will save you tons of time.... so you can...

Excel: Excel Protection Question, format cells, check boxes

format cells, check boxes, excel worksheet: Madeline If the sheet is protected, unprotect it. At the intersection of the row and column headers (to the left of A and above 1) is a box/chicklett. Click on that and every cell on the sheet should be selected. The do format= Cells in the menu...

Excel: Excel problem, basic excel, richard florida

basic excel, richard florida, drag and drop: Nesta I haven t seen the second problem and I can t seem to duplicate it. But it seems as though your options might have been changed. Go to toolbar/tools/options/calculation and see if the automatic button is checked. If it is not then the autosum...

Excel: Excel programming - copy, paste + time to another sheet, blank row, excel programming

blank row, excel programming, press button: Not sure if you mean copy different data on sheet1, or to a different area on the target sheet? The macro would need some way of knowing where it had reached - if it is copying to the next blank row on the target worksheet, then it can use the counta...

Excel: Excel Question, excel, concatenation

excel, concatenation: You could use a helper column with a formula like: =IF(A1 2000,A1& /K ,A1& /S ) This will add either /K or /S depending on the value. If you need to have values instead of a formula, you can select the whole column, copy it and paste values (menu Edit-...

Excel: Excel Scrol bar, scrol, dalgleish

scrol, dalgleish, scroll bar: abe, go to this URL and read the Used Range reset FAQ. http://www.contextures.com/xlfaqApp.html#Unused You are correct that excel thinks you are using the whole worksheet rather than the small area that actually has data. It could cause some...

Excel: Excel Solver: Maximizing the minimum value from a selected set, target cell, pairwise distances

target cell, pairwise distances, psuedocode: Steven, I m sorry that I haven t answered this question earlier. Hopefully by now you have asked someone else, or found a solution to your problem. I m afraid that I am not familiar with the Solver add-on. I thought I might have had some time...

Excel: Excel Spreadsheet, excel spreadsheet, peltier

excel spreadsheet, peltier, atlantic city new jersey: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Excel - Macro Run On Cell Value, cell d2, reply thanks

cell d2, reply thanks, value question: It does make sense, but what is triggering the change - is it (I hope) a manual update? If so, you are going to need a global variable which will store the value before update and after update so that it can compare the two and only if the old one is below...

Excel: Excel User Forms, irow, task scheduler

irow, task scheduler, excel user: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Excel UserForms, excel ontime, chip pearson

excel ontime, chip pearson, operational knowledge: Molly, I would really require much more operational knowledge on how the form was being used - what actions could be keyed on and so forth to give you any king of a usable answer. but to answer you question of how to use a timer, see Chip Pearson s site...

Excel: Excel VB Log file, target address, excel vb

target address, excel vb, sure what your question: Not sure what your question is since you did not ask one but I assume you meant to ask something like, what is the code needed to specify this path as the place that the log file is saved rather than the default saving location ? So, with that assumption...

Excel: Excel VBA, reed jacobson, john walkenbach

reed jacobson, john walkenbach, excel vba: Jim, I learned with the Excel 95 version of this book by Reed jacobson: Microsoft Office Excel 2007 Visual Basic for Applications Step by Step It gives you a good foundation. http://tinyurl.com/kopykk the one review is pretty negative,...

Excel: Excel VBA - Dynamic Worksheet Adressing, contract variations, excel vba

contract variations, excel vba, lmax: Dave, If NewCVRNo had the name of the sheet, I don t see why that wouldn t work. In any event: If you want to select the last worksheet in the tab order? worksheets(worksheets.count).Select You want to loop through all the sheets and find the...

Excel: Excel VBA Form to Sheet, excel vba form, text cells

excel vba form, text cells, cell location: This section: Cells(lRowNum, 4).Value = OptionButton1 Cells(lRowNum, 4).Value = OptionButton2 Cells(lRowNum, 4).Value = OptionButton3 will only store OptionButton3. And you should use OptionButton3.Value It s not clear what you really want...

Excel: Excel value compare, answer mark, james daniels

answer mark, james daniels, norry: Mark, What I would do to correct this mess is to create a temporary sheet that combines both of the messed up databases together. COPY & PASTE them onto one sheet. Then you can SORT by name and/or ID and find the problems. This will be MUCH easier...

Excel: Excel Worksheet Question, excel, group sheets

excel, group sheets: It can be done, but you will have to group the sheets manually. To do that, select one worksheet, then hold the Ctrl key and select the other worksheet. Excel will highlight both tabs, and any change that you do in terms of format, data and structure will...

Excel: Excell Time Format, mm ss, time formats

mm ss, time formats, time values: Andres, I suspect your times are not stored as time values. You can select the column of time and do Edit= Replace. Replace a with AM (no double quotes - I just put in the double quote to show it is one space and then AM. Repeat replacing p with ...

Excel: Excell Times Format, format cells, time formats

format cells, time formats, time option: Andres Select the cell/s with the times and then right click on the cells and select Format Cells and go to the Time option, on the right select either 13:30 for hours and minutes or 13:30:55 for hours minutes and seconds. Hope this helps. Richar...

Excel: Excell time format, format cells, alert button

format cells, alert button, time formats: as long as this column ONLY has time in it, select the column, then format cells as time hh:mm. Then do a search and replace for a replacing with am and p replacing with pm - this should fix it. IF it doesn t, select the time block of cells, you should then...

Excel: Excell if/vlookup formulas, john doe 2, sap report

john doe 2, sap report, attendance hours: Sylvie, I don t seem to understand your problem. You say you have entered 3 formulas, each in ONE CELL (E3, F3, & G3), but then you say these work on 12 out of 13. You MUST have entered your formulas in an entire column, yes? And which of the 13...

Excel: Exit Sub Error and format check, false selection, default text

false selection, default text, input box: Mark, I made a few changes - I don t know if I addressed everything you asked, so if you have further questions, follow up. Sub ReName() If ActiveSheet.Name = Template Then msg = MsgBox( Cannot Rename Template - Select another Sheet...

Excel: Expiry of Spreadsheet after # of uses, vba code, spreadsheet

vba code, spreadsheet, expiry: You can make it still unusable by hiding all the sheets except one which says something like You can t use this wb without macros . The workbook structure is protected and the VBA code is password protected. In the Workbook_open event, the code unprotects...

Excel: Extra Columns, home tab, excel worksheet

home tab, excel worksheet, ogilvy: Lisa, in the name box type in K:XFD and hit enter to select all these columns. then delete (home tab, delete, entire column) Then save the workbook (possibly close and reopen it as well). That should do it - if not, then I would need to look...

Excel: Extra Columns, excel worksheet, book copy

excel worksheet, book copy: ctrl end takes you to the end of the last EVER used range - so at some point, someone would have (perhaps) selected an entire row and formatted it - or pasted in error. There are various fixes - I have a macro on a machine somewhere that does it, but the...

Excel: Extract data from table according to a time interval, gg hh, interval values

gg hh, interval values, minute interval: EliaJozaif, Create a new table with a column that lists the 20-minute intervals for which you want to know the values. Then use VLOOKUP [or MATCH & INDEX] to look up the values from your main table which correspond to the 20-minute intervals in your...

Excel: Extracting Data from Database Sheet Based On Date Periods, functions in excel, sumproduct

functions in excel, sumproduct, excel function: Matt, It would seem that you want to SUM some field in a number of records which fall between a range of dates, or perhaps COUNT the number of records which have some criteria and which fall between a range of dates. However, the second part...

Excel: Extracting Detailed Data, pivot table in excel, pivot tables

pivot table in excel, pivot tables, pivottables: TJ, this sounds tailor made for a pivot table, In Excel 2007, it is found in the insert tab in the first button. In Excel 2003 and prior versions, it is found under the Data menu down at the bottom of the submenus. Debra Dalgleish has written several...

Excel: Extracting data into another column, ogilvy, parenthesis

ogilvy, parenthesis, test data: Hello Jackie, Assume Manufacturing (SR12) is in A1, then in B1 =MID(TRIM(A1),FIND( ( ,TRIM(A1))+1,LEN(TRIM(A1))-FIND( ( ,TRIM(A1))-1) then drag fill this down column B for as far as you have data in column A. This worked for me with all your...

Excel: eliminate date format from excel forever, xlstart directory, apostrophe

xlstart directory, apostrophe, dashes: Best way would be to select ALL cells, format as Text. But that would be only for the current workbook/sheet. If you really want to do this for all workbooks (new ones), manually do it for all sheets in a new blank workbook, save it as a template (.xlt) in...

Excel: entering multiple lines in a textbox, cell a2, little boxes

cell a2, little boxes, control source: Terrell, Are you using the control source property of the textbox? I just tested it (as it has been a while since I last played with this problem) and I found if you format the linked cell to wrap text, you don t get the little boxes - the text is on...

Excel: error handling, time thanks, ogilvy

time thanks, ogilvy, run time: David, Not every prompt can be suppressed - but for the ones that can be application.DisplayAlerts = False code that might cause a warning/prompt Application.DisplayAlerts = True if it is actually an error and you know you can ignore it, you...

Excel: excel 2002 Auto recover, worst case scenario, document recovery

worst case scenario, document recovery, time stamps: I NEVER trust document recovery -- always has a problem for me. Your best bet is to set autosave for every 10 minutes or so, so the worst case scenario is you ve lose 10 minutes work. And for sensitive stuff, also create a backup file. I m afraid there s...

Excel: excel 2003 formula question, employee names, persons age

employee names, persons age, eligibility requirements: Holly, Sure its possible. All you need to do is be able to detect each condition, and then put them inside the AND() function. If you have columns that have the age and years of service, you can say =AND(AGE =55,SERVICE 25) where...

Excel: excel, multiple times, aidan

multiple times, aidan, heritage: selecting all sheets would be the way to go, the only other way would be to use VBA - but I m not clear why you want the information on multiple sheets - when designing a sheet, then yes, but in use it shouldn t be necessary to repeat things multiple times?...

Excel: excel, pivot table, pivot tables

pivot table, pivot tables, staff staff: alyssa Are you familiar with pivot tables found under the data menu in xl2003 and earlier and on the insert tab in xl2007. the easiest way to do this would be to make a pivot table and it would list all the unique combinations and give you the count...

Excel: excel, aidan, arrow

aidan, arrow, massage: its not very clear from the question what the problem is - if it s a forgotten password then I may be able to give you a working password (my email is aidan.heritage@virgin.net) if it s a corrupted document then the arrow to the right of the open command gives...

Excel: excel, scaffold, hi thanks

scaffold, hi thanks, ogilvy: Suba, You can add conditions to that formula =sumproduct(--(Month(J2:J500)=4),--(K2:K500= scaffold )) =sumproduct(--(Month(J2:J500)=4),--(K2:K500= temp )) =sumproduct(--(Month(J2:J500)=4),--(K2:K500= material )) -- Regards, Tom Ogilvy ...

Excel: excel, keystroke sequence, type recorder

keystroke sequence, type recorder, chance thanks: Andy, There is a similar type recorder called the Macro Recorder. It will copy every keystroke you make, and play it back on command. You can also edit the keystroke sequence you ve recorded, which is called a Macro, and which is written in a language...

Excel: excel Chart/Graph question, graph type, pink ltd

graph type, pink ltd, primitive knowledge: Dave, It seems to me as though a bar chart isn t the best choice. You want to plot too many variables for a bar chart to handle. There are other more advanced charts (don t be afraid, they aren t that bad) that can display multiple variables in an...

Excel: excel compounding growth, www google co uk, financial functions

www google co uk, financial functions, percentage growth: There are a large number of financial functions, although this isn t an area I m familiar with - see http://www.google.co.uk/search?sourceid=navclient&aq=0&oq=excel+compound+&ie=UTF-8&rlz=1T4RNWN_enGB318GB318&q=excel+compound+interest for some links...

Excel: excel date formula, traffic light system, conditional formatting

traffic light system, conditional formatting, format button: Angela, then you would just adjust the formulas I gave you: This would be the first condition =($B$9-30) =today() so if I subtract 30 days from B9 and it is less than today, that mean that today is within the 30 day window. This would be the...

Excel: excel formula, d4 d5, c4 c5

d4 d5, c4 c5, cell d1: Parth, This is a perfect application for the OFFSET function! The OFFSET function creates a range (a reference) which can be used in place of an actual range inside any equation where a range is required. Please read about the OFFSET function in the...

Excel: excel formula, excel formula, excel sheet

excel formula, excel sheet, pivot table: Jeromy, You want a list of Unique Names! If you are using excel 2007, there is a menu command (in the Data ribbon, I think) which generates a unique list. If using Excel 2003, you can create a one-column Pivot table with no column or data area, which...

Excel: excel formula, excel formula, walking program

excel formula, walking program, spreadsheet: I don t quite follow. Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel...

Excel: excel lists - excell 2003, better solution, aidan

better solution, aidan, alphabetical order: It would probably be possible but I don t understand the requirement to have the list in two places - why not simply arrange for the sheet to sort before it closes so that the data is only in one place. I m also SLIGHTLY concerned that this may be a multi-user...

Excel: excel lookup, excel lookup, wheat thins

excel lookup, wheat thins, potato chips: Chris, Do you still need an answer to this? I have a rather simple way to do it, but I don t know if its what you really want. Add a new column to your database that is filled with the FIND() function embedded inside a NOT(ISERROR()) function....

Excel: excel macro, automatic answer, rng

automatic answer, rng, excel macro: Jon, that is because the cells are not empty since they contain formulas. for that situation you would need Sub PrintReports() Dim rng as Range Dim cell as Range with worksheets( Data1 ) if len(trim(.Range( A2 ).Value)) = 0 then set...

Excel: excel macro, excel macro, differe

excel macro, differe, ogilvy: ryan, Change Sheet2 to reflect the tab name of the second sheet. Sub ABC() Dim rr As Range, r As Range Dim r1 As Range Set rr = ActiveCell Set r = Worksheets( Sheet2 ).Cells Set r1 = r.Find(What:=rr.Value, _ After:=r(1), _ ...

Excel: excel macro help, r2a, r1a

r2a, r1a, bcopy: Manjari, OK. As I understand it you want to consolidate the data from all duplicate entries and copy the data from unique entries If duplicates are found, (based on the combined data from columns C & D) a single row is created in sheet4 with the...

Excel: excel problem, debits and credits, extra digits

debits and credits, extra digits, credit columns: Most likely the issue is precision. Put your formulas inside a ROUND function, like =IF(ROUND(A1,5)=ROUND(B1,5),...) I can t see the image you sent (way too small), so feel free to send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ...

Excel: excel if statement, free microsoft excel, blank cells

free microsoft excel, blank cells, counta: I m sorry, but I don t understand what you mean. If you re trying to count blank cells, why not use the ISBLANK function? 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 tables, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Columns. :) I don t know another name for them. 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: Field concatenation, pic thanks, variable data

pic thanks, variable data, b1: Try: =IF(AND(LEFT(SUBSTITUTE(B1& - &C1& - &D1, -- , - ),1)= - ,RIGHT(SUBSTITUTE(B1& - &C1& - &D1, -- , - ),1)= - ),MID(SUBSTITUTE(B1& - &C1& - &D1, -- , - ),2,LEN(SUBSTITUTE(B1& - &C1& - &D1, -- , - ))-2),IF(LEFT(SUBSTITUTE(B1& - &C1& - &D1, -- , - ),1)=...

Excel: Find Bold Cells, sh2, cell font

sh2, cell font, sh1: abe, Sub findbold() Dim rw As Long, sh1 As Worksheet Dim sh2 As Worksheet, r As Range Dim cell As Range rw = 1 Set sh2 = Worksheets( Sheet2 ) Set sh1 = ActiveSheet If sh1.Name = sh2.Name Then MsgBox Select the sheet to process, & _ vbNewLine...

Excel: Find Button, excel, find

excel, find: If you are using the built-in find function, you don t need to insert any type of object. You can use the menu option under Edit, the keyboard shortcut Ctrl+F, or even customize the toolbars to show a button with the find function there. For this last...

Excel: Find misplaced digits or decimal points, excel function, mathematical rules

excel function, mathematical rules, decimal points: Hi Ramiz How are you? This depends on how far you want to automate the checking process. E.g. You can detect values too big or too small as follows: Public Function OutofBounds(R As Range) Const Maximo = 900 Const Minimo = 1.7 If R.Cells(1,...

Excel: Find next coloured cell in a range, background colours, colorindex

background colours, colorindex, coloured background: ActiveCell.Interior.ColorIndex (or any other cell reference) will return the fill of a cell, white is -4142 - so testing the cell for it NOT being -4142 would be the way to go - so something like for each Cl in activesheet.selection if cl.interior.colorindex...

Excel: Find nth value in range, msgbox, command button

msgbox, command button, b20: yes it is - my match function has the range specified as a1:a20, so change that to X, and change the ,2 to ,25 as y is the 25 column on the spreadsheet. It would also be possible to program it to work on any column if required, though this would need different...

Excel: Find a Text String, array formula, curly brackets

array formula, curly brackets, vba macro: Kee, put this in D1 =IF(ROW() =COUNTIF(A:A, *is medium* ),INDEX($A$1:$A$4,SMALL(IF(ISNUMBER(FIND( is medium ,$A$1:$A$4)),ROW($A$1:$A$4)),ROW()),1), ) enter it with Ctrl+Shift+enter rather than just enter. This is done since this is an ARRAY formula....

Excel: Find Variable Macro, cell c2, stock database

cell c2, stock database, sh1: Evan, Sub FindStockNumber() Dim r As String, rng As Range, r1 As Range Dim bk As Workbook, bk1 As Workbook Dim sh As Worksheet, sh1 As Worksheet Set bk = Workbooks( Invoice.xls ) Set sh = bk.Worksheets( Invoice ) s = InputBox( Enter the Target )...

Excel: Finding average, cell d2, column c

cell d2, column c, final result: Ehsan: I hope I understand the question - if not, please provide additional information. I am going to presume your data is in cells $A$2:$C$15 I am going to presume you want an average to show in column D for every row. This means every row with...

Excel: FORMULA, block capitals, blank cell

block capitals, blank cell, g24: It is not clear what you want to do here - you say formula for Cell H24 to be blank but you don t say where or what this is to apply to - could you clarify (and also turn off the caps lock key as BLOCK CAPITALS IS VERY HARD TO READ) - if it helps, you can...

Excel: FORMULA, g24, blank cell

g24, blank cell, f24: Dan, the formula I posted worked fine for me. It is possible you are hitting the spacebar to make the cells look blank and they are not actually blank. when blank the formula worked as I expected. If you only want the average when the both F24 and...

Excel: FORMULA, blank cell, g24

blank cell, g24, f24: Dan, I thnk what you must mean to say is that you want cell H24 to behave as follows: 1) it shall be blank if either or both F24 or G24 are blank 2) it shall be the average of f24 & G24 if those two cells contain numeric values. If...

Excel: Follow-up to my macro question., debug error, macro recorder

debug error, macro recorder, excel sheet: Kenneal, change abc to your password Sub ENTRY_BUTTON() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range Dim v1 As Variant, v2 As Variant Set sh1 = Worksheets( Sheet1 ) Set sh2 = Worksheets( Sheet2 ) sh1.activate sh1.unprotect...

Excel: Forecast, linear average, linear relationship

linear average, linear relationship, linear range: James, It seems to me that if everything is linear, you can use a simple Y=MX+B function to calculate Y given any X, as long as that X is in the linear range of your data set. You should be able to calculate M and B from the known data values you have......

Excel: Forgot the Security Code Of Spread Sheet, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: You would need to look into a 3rd party program to bypass the security. I don t know of any techniques. 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: If Formula, vba procedure, vlookup function

vba procedure, vlookup function, sitation: Hi Mahmoud How are you? I think that there only is two solutions: Either you write a VBA procedure to compute this conditional structure or you convert it into a VLookup function. In both ways, I need more details to help you. Cheers Adelaid...

Excel: IF Formula

No, sorry I can t because you did not tell WHAT the conditions are. And EXCEL 2003 and earlier will only allow for 7 IF s in one single formula. Having 20 conditions in one formula makes for a very difficult formula to write and have to edit if the need...

Excel: Formula Error #value, VALUE error

VALUE error: Hi Mona, Most Excel error messages tell you what is wrong, like DIV/0 or REF, but a VALUE error means it wants a value and isn t finding it. Most likely, you have text (or a space, or numbers entered as text) in a cell that your formula is trying to do...

Excel: Formula or Maco for hiding rows based on cell, range a11, target address

range a11, target address, false case: Jeanie, the only detail you gave me was C3, so I will assume C3 can hold any of 5 entries: Item1 Item2 Item3 Item4 Item5 you would right click on the sheet tab of that sheet and selet view code in the resulting module, at the top are two dropdowns....

Excel: Formula problem, dear sirs, sumproduct

dear sirs, sumproduct, test1: it sounds like it needs a multiple condition sumif formula, which can be done with sumproduct - it would be easier to show you on an actual attachment (for which my email is aidan.heritage@virgin.net) but it s basically =sumproduct(--(range1=test1),--(range2=test2),Range3)...

Excel: FormulaArray in a loop, run time error, correl

run time error, correl, variable values: Bastian, It just looked to me like you have several errors in your formula involving parentheses and also, you have dat1 and dat2 entered as if they are defined names in the worksheet rather than variable values you are trying to concatenate into the formula...

Excel: FUN TIMES remove certain text, semi colons, cell b2

semi colons, cell b2, james benson: Russ, If you still need an answer to this, I ll need to know: How is a non-board member identified? My GUESS is the use of the word PRIOR somewhere between the semi-colons which separate the names. How is a current exec identified?...

Excel: IF Function, sheet1, d10

sheet1, d10, account numbers: Misty, Use the SUMIF function instead. Refer to 123 in an external cell; that way you can add up every account number (or whatever the first column represents) with the same formula. Lets say you have 123 in cell D10, and your data table is in...

Excel: Age in Months, b3, len

Excel: Age in Months, b3, len, ym

Excel: Automatic expansion of lists, pivot table

Excel: Automatic expansion of lists, pivot table

Excel: Average Array Formula - multiple criteria, array formula, h1000

Excel: Average Array Formula - multiple criteria, array formula, h1000, k1000

Excel: adding # plus cell contents to reference a cell, o11, fly

Excel: adding # plus cell contents to reference a cell, o11, fly

Excel: Combinations, formula index, random combination

Excel: Combinations, formula index, random combination, ascending order

Excel: Creating a submit button macro in an excel sheet.

Excel: Creating a submit button macro in an excel sheet.

Excel: Creating a time sheet for my company, text formating, format cells

Excel: Creating a time sheet for my company, text formating, format cells, weekday

Excel: Formula Error #value, array formula, curly braces

Excel: Formula Error #value, array formula, curly braces, detail c

Excel: Inq, typo, target

Excel: Inq, typo, target

Excel: Macro Runtime 13 Mismatch Error, type mismatch error

Excel: Macro Runtime 13 Mismatch Error, type mismatch error

Excel: Medical Caluclation on Excel, cockcroft gault, excel formula

Excel: Medical Caluclation on Excel, cockcroft gault, excel formula, cr

Excel: #N/A of Vlookup, vlookup, parenthesis

Excel: #N/A of Vlookup, vlookup, parenthesis, formula one

Excel: Pivot table source in excel 2003, pivottable wizard, query wizard

Excel: Pivot table source in excel 2003, pivottable wizard, query wizard, microsoft query

Excel: Pop up message

Excel: Pop up message

Excel: Is It Possible To Extract Data From Networked Hubs Into One Worksheet?

Excel: Is It Possible To Extract Data From Networked Hubs Into One Worksheet?

Excel: Remove Special characters, disaster management, a2

Excel: Remove Special characters, disaster management, a2, catastrophe

Excel: SELECT MULTIPLE LARGE RANGES OF CELLS, h1000, t1000

Excel: SELECT MULTIPLE LARGE RANGES OF CELLS, h1000, t1000, p10

Excel: shading entire row based on cell value, happy camper, canp

Excel: shading entire row based on cell value, happy camper, canp, subject number

Excel: splitting text

Excel: splitting text

Excel: vlookup doubt, rev number, column c

Excel: vlookup doubt, rev number, column c, best of luck

Excel: find command, sheet tabs, number id

sheet tabs, number id, string value: Chuck, I assume this is a VBA question. Say a sheet s tab name is 111. The name is really 111 which is a text/string value. If you try to do worksheets(111).Select it will look for the 111th sheet in the tab order. You need to convert...

Excel: fix sized charts, tiny icon, box move

tiny icon, box move, size group: I do apologize - because it was already so un-intuitive I foolishly assumed Microsoft couldn t have made it harder, so didn t check on office 2007 when replying - I was trying to get through a LOT of questions - it took some searching and I m going to take...

Excel: formating with if clause, conditional formatting, aidan

conditional formatting, aidan, email: There isn t anything directly in excel that can pick up if a value is in bold, but is the bold itself done with conditional formatting? You didn t mention version of excel, nor if a VBA solution would be acceptable - my email (if it helps) is aidan.heritage@virgin.ne...

Excel: formula, mp 50, answer column

mp 50, answer column, richard florida: Roxana I believe you can use the countif formula. The formula looks like this Assume the answers are in column A from row 1 thru row 100. =countif(A1:A100, yes ) This counts the number of yes answers in the answer column of course the formula...

Excel: formula that will break a number down into smaller groups, time tom, golf tournaments

time tom, golf tournaments, ogilvy: Tom, do you just want to know what combinations are possible? so say you put 13 in A1, then in B1:D1 you have 3 4 5 and you want formulas in B2:D2 and on down showing all possible combinations of groups (and quantities of those groups)...

Excel: formula challenge, addressing modes, formula challenge

addressing modes, formula challenge, fundamental skill: Kay, Yes, this is basic! In fact this is a fundamental skill, which a lot of people just don t know and which causes endless difficulties. Its called Absolute, Relative, and Mixed addressing modes. You can read more about this using those keywords...

Excel: formulas on excell professional, cheque accounts, seperate sheet

cheque accounts, seperate sheet, crdit card: Jacques, I have an excel workbook from you that apparently relates to this question. I m GUESSING that each row in the sheet named Clients Rank represents ONE client. Furthermore, each client may appear ONLY once in this table... it would appear...

Excel: forumula to delete multiple rows of data, pivot table, duplicate records

pivot table, duplicate records, multiple records: Nay, If you actually want to delete the data, then the best you can do is mark the rows using a formula, then delete them manually (which can be done with one command if marked appropriately). However, the above will just identify the rows to delete...

Excel: IF function, multiple variables, b1, c1

b1, c1, a1: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: Generated Worksheet List as a Named List, data validation, macro script

data validation, macro script, lct: Hi Joe, I d do it like this: Sub SheetNames2() Dim oSh As Object Dim lCt As Long For Each oSh In Sheets lCt = lCt + 1 Sheets( DataCapture ).Cells(lCt, 1).Value = oSh.Name Next With Sheets( Datacapture...

Excel: Giving Different Values to Numbers, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: You just want to transpose a 1 to a 5, and that s it? You could just do this with a little math. Since: 1=5 2=4 3=3 4=2 5=1 Then just say that if A1 is your value, then the rank would be: =6-A1 Now just add up that column for the total points....

Excel: GoTo ...another macro, sort property, sort feature

sort property, sort feature, followup question: Joe, ------------------------------ Had a chance to go back and look at the pseudo code I offered. the Key should have been Key1, but it wasn t turnkey code. Assuming the data starts with a header row in row 1 starting in A1 Sub abc() With ActiveWorkbook.Worksheets(...

Excel: grab every fourth cell's value, indirect address, cell d1

indirect address, cell d1, value question: Dave, Try this: =IF(INDIRECT(ADDRESS(C1,1))=0, ,INDIRECT(ADDRESS(C1,1))) I assume that the beginning of your question means that the Indirect Address method is working, up until the point that column A runs out of data, correct? ...

Excel: graphing grades, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Use the COUNTIF function. Let s say in column A you have your values, and you want to see how many 3 values you have. =COUNTIF(A1:A100,3) Just do a separate value for each one: 1,2,3. Then graph that. Let me know if you have any other questions...

Excel: Handling missing data - Excel 2007, blank cells, measurement data

blank cells, measurement data, cell position: Hi Mary, I m not sure what basic statistics you are peforming, but typically, if you left these cells completely blank, they will not show up when you do a count, or average, etc. You could also put in a text value, like null and try using the Count...

Excel: Handling missing data in time series - Excel 2007, basic statistics, decimal numbers

basic statistics, decimal numbers, summary statistics: Mary, replace the Null with the formula =na() so a #N/A error is displayed in the cells. The graph should ignore these cells. In xl2003 and earlier, have the graph selected, then go into tools= options and look at the Chart tab to insure the settings...

Excel: Hide Empty Columns, empty columns, ogilvy

empty columns, ogilvy, spreadsheet: Sev, I assume no data means empty (no formulas, nothing in the cells) given that assumption, this worked for me. Sub ABC1() Dim firstrow As Long, cell As Range firstrow = Cells(Rows.Count, 1).End(xlUp).Row For Each cell In Range( A2:AM2 ) Set...

Excel: Hide rows, cell a1, digit number

cell a1, digit number, previous versions: I don t fully understand all of your question. It s true that formulas cannot change row heights (or hide them), so to hide them you either need a macro or do it manually. What I don t understand is the conditions under which you d want the row(s) hidden/shown...

Excel: Hiding columns VBA, accum, header row

accum, header row, ogilvy: Hunter, My fault - I missed the hide/unhide part and completely misinterpreted what you were saying. No the original macro only needs to be slightly altered: Sub Hidecolumns() Dim r As Range, l As Long, i As Long Set r = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)...

Excel: Ho can I do merge a cell by keyboard in MS Excel, alignment tab, keyboard shortcut

alignment tab, keyboard shortcut, arrow keys: There is no keyboard shortcut already assigned that I m aware of, but you could assign a keyboard shortcut to the command by recording a macro if required - otherwise, select the cells (use the shift key and the arrow keys to extend selection), then ctrl 1,...

Excel: help in writing macro in excel., match 2, macro in excel

match 2, macro in excel, mail: I m confused. In one place tou say to match A,B,C,d, and later you mention A,B,F,G -- so which ahould be matching? You also say ...rest of all entries from column #E,M,O,P to N,O,P,Q... -- I don t understand this either, especially rest of all entries...

Excel: Identify possible source of errors for out-of-balance amount, excel function, mathematical rules

excel function, mathematical rules, richard florida: RAMIZ How about this formula =IF(D6/9 +ROUND(D6/9,0), , TRANSPOSITION ) Where D6 is the cell that has your difference in it. The formula says If the difference (D6) divided by 9 is not equal to the difference divided by nine rounded then...

Excel: Import data from various workbooks, excel vlookup function, cell f15

excel vlookup function, cell f15, import data: Justin I am definately not an expert in VBA- I suggest you reask the question of another allexperts person and search the resumes for someone who is VBA profficient==I am sure you can find someone who can help you. Sorry I couldn t be of more help....

Excel: Import hyperlink into Oracle database, absolute hyperlink, relative hyperlink

absolute hyperlink, relative hyperlink, oracle table: Dale, I am sorry to have neglected your question for so long, and then to come back like this without an answer! If you still need help with this, I MIGHT be able to help you - so resubmit your question. BUT, my stumbling point on answering...

Excel: Importing data from .txt file, iloc, input boxes

iloc, input boxes, getopenfilename: Albert, I suspect you have multiple lines associated with 4500 and you want to bring back values from both lines (or multiple lines) So assuming that then So no, you can t do that with Vlookup, but you could do it with Index and match =Index(Transfer!$B:$B,Match(4500,Transfer!$A:$A,0)+1,1)...

Excel: Importing file using wildcard function, mm ss, filesearch

mm ss, filesearch, fxrates: I would probably do this using the following method Set fs = Application.FileSearch With fs .LookIn = C:My Documents .FileName = cmd* If .Execute(SortBy:=msoSortbyFileName, _ SortOrder:=msoSortOrderAscending) 0 Then ...

Excel: Importing file using wildcard function, sfile, mm ss

sfile, mm ss, spath: Hi Emmanuel, You could do something like this: Sub Test() Dim sPath As String Dim sFile As String Dim sFileSpec As String sFileSpec = UCase(Format(Now, DDMMMYY ) & *.csv ) sPath = c:YourPath sFile = Dir(sPath & sFileSpec)...

Excel: Importing updated sheets with moving cell ref, stock ticker symbols, stock ticker symbol

stock ticker symbols, stock ticker symbol, social security number: Keith, It is probably better to look up the information from Sheet 2 onto sheet 1 using one of the LOOKUP & REFERENCE functions, instead of a hard-coded link to a specific cell. As you have observed, the links don t move with the data. The key...

Excel: Importing XML files using a macro, direct email, xml file

direct email, xml file, multiple users: Not sure how much you have in VBA already, so I ll assume you are OK apart from getting the file name - if so:- FileToOpen = Application.GetOpenFilename( Please select the file you want to import (*.xml), *.txt ) If FileToOpen False Then...

Excel: INDIRECT function, question post, c9

question post, c9, c7: Hello Quentyn, row() give the row number of the cell containing the formula so if you use that in C7, it returns 7 and if you use it in C8, then it returns 8. Now we need it to refer to row 9 in C7 and 15 in C8 and 21 in C9 C7: (row()-7) ...

Excel: Incidence calculation, gp5, column c

gp5, column c, incidence rate: Nahed Batarfi, I put your data in a worksheet in the proper columns and put your formula in C2. It worked fine. I put every group name in column C and drag filled down the column and it appeared to work fine. the only problem I could see is if instead...

Excel: Index Match Excel 07, general pax, ogilvy

general pax, ogilvy, 0000: Sarah, If you want to send me a sample workbook showing the table, some data you are looking up and the results you expect to see, I can probably help - but I don t get a clear picture from your example. send it to twogilvy@msn.com -- Regards,...

Excel: Index and Match not recognizing any value under 1 and Indexing multiple tables, mileage tables, mileage reimbursement

mileage tables, mileage reimbursement, index function: Bill, I took your formula apart to analyze it, see below. It looks okay to me, and as you say, it works. What I don t understand is your exact question..... you presented me with a fine formula which has actual cell references, but then your question...

Excel: InputBox Worked, project scope, access vba

project scope, access vba, pword: Lena, I copied it to demonstrate the difference, but I guess I forgot to edit the upper copy. It should have been: Instead of something like : Private Sub CmdQuit_Click() Unload Userform1 End Sub do Private Sub CmdQuit_Click() Userform1.Hide...

Excel: Inputbox dates, john walkenbach, contextures

john walkenbach, contextures, dialogbox: John, a message box can not produce a dropdown list, but this can be done using a userform or the older dialogbox capability in excel. John Walkenbach has a procedure to fill a listbox or combobox with a list of unique items. http://spreadsheetpage.com/index.php/site/tip/filling_a_listbox_with_unique_items/...

Excel: Inq, staff name, fomular

staff name, fomular, inq: You didn t say where the staff name was located, but I ll assume column Q =sumproduct(--( Svg - WIP !P10:P11= Evalution ),--( Svg - WIP !P10:P11= staff name )) this is based on the data I can see in the question, so hopefully is what you need. ...

Excel: Insert Image into Userform, lcase, mypics

lcase, mypics, elseif: Calyaron, Assume you have an image control on the userform named Image1 Private Sub Textbox1_Exit() if lcase(me.Textbox1.Value) = yes and _ lcase(me.Textbox2.Value) = yes then me.Image1.Picture = LoadPicture( C:MyPicscheckmark.jpg...

Excel: Insert Row, hh mm ss, string s1

hh mm ss, string s1, isdate: JW, I assume the times are sequential. Also assume that you want to insert one row and quit. Sub InsRow() Dim LR As Long, i As Long, j As Long Dim s As String, s1 As String, j1 As Long LR = Range( F & Rows.Count).End(xlUp).Row For i = 2 To LR...

Excel: Inserting data into many work sheets, run time error, run time error 1004

run time error, run time error 1004, xls extension: Ray, I checked some of the code in the immediate window: set bk = ActiveWorkbook set sh = bk.worksheets(2) partno = LBR-FAB sword = LBR-FAB sh.Range( A5 ).Value = partno ? sh.Range( A5 ).Value LBR-FAB sh.Range( B5 ).Formula = =Vlookup( ...

Excel: Intentional Circular Referance without VBA, excel macros, stock feed

excel macros, stock feed, circular references: sorry for the delay in replying - for some reason I didn t see this post at the top of my list. My email is aidan.heritage@virgin.net - we can probably best take it forwards via that route. I do have a spreadsheet I downloaded that already does live prices...

Excel: Isolate differeing data from two spreadsheets, spreadsheet layout, location changes

spreadsheet layout, location changes, key family: Martin, Yes, its possible. The thing that makes it possible is the Unique Key for each employee. However, its not going to be exactly easy to do, perhaps. There is also the question of what happens next week ... do you want to only compare...

Excel: how to insert dynamic time, dynamic time, cell a1

dynamic time, cell a1, hotkey: Hi Mark, You can update the NOW() formula using the F9 function key to recalculate. What I would do though is record a short macro that when entered using a hotkey, like CTRL T or something that enters in the active cell the =NOW() and then goes back...

Excel: insert signature, excel spreadsheet, aidan

excel spreadsheet, aidan, signature: Somehow I managed to miss the fact that this was Excel - I think you have a further problem in that PRESUMABLY you only want the picture inserted if the IF condition is true? I think you simply need to have the picture set to properties, move and resize...

Excel: integers, cell a1, format cells

cell a1, format cells, format numbers: I assume that you re asking how to make a number display with zero decimal places. Say in cell A1 is the number: 1.32 and you want it to appear as 1 You select A1 and then choose from menu Format, Cells, Number, Category: Number, Decimal places:...

Excel: intersect of two lines, point of intersection, logical conditions

point of intersection, logical conditions, logical functions: Hoy Yen, There isn t an INTERSECT function, which is too bad. You ll have to create your own, and it may not be easy. Or possible! I ve never tried to do this, so all I can make are a few suggestions. It s an interesting problem. ...

Excel: Keeping only first and last entry in a column, column c, home tab

column c, home tab, ogilvy: Ron, Assume the first 302 is in in A2, then in C2 put in this formula =IF(OR(COUNTIF($A$2:A2,A2)=1,A2 A3), ,NA()) then drag fill it down column C until you reach the end of your data. Now Select Column C and hit F5 (to get the Goto dialog),...

Excel: Label Caption to Equal Cell Value, initialize event, control source

initialize event, control source, doevents: Manuel-Squares, You could use the calculate event to test if the userform is loaded and it it is, then have it update the textboxes. You might have to throw in a DoEvents after writing to the textboxes to get the userform to update or, since you...

Excel: Lapsed Time in Excel, free microsoft excel, sas program

free microsoft excel, sas program, excel tips tricks: I don t know what format your times are in. They MIGHT work. I d have to see a sample of the date to tell you for sure. Sorry I missed your deadline, but I get HUNDREDS of questions a week and can t get back to everyone right away. If you still need...

Excel: Limit cells, data validation, number combinations

data validation, number combinations, 6 digits: Dave, I would choose custom in the data validation and use a formula like this =and(len($A1)=6,isnumber(1*$A1),countif($A:$A,$A1)=1) I selected A1:A15 with A1 as the active cell, did data validation, then custom and put in that formula and it worked...

Excel: Linking and Conditional Formatting., conditional formatting, excel spreadsheet

conditional formatting, excel spreadsheet, basic excel: Paul, What you want might be possible but I don t understand IT because you use too many referring words like THIS and IT, and I cannot tell what you mean. My comments are inside [] s I followed you fine till you said this: Now lets say there...

Excel: Linking to Data and not Cell, source sheet, cell location

source sheet, cell location, cell a2: Matthew, I understand what you describe and the short answer it no - not with a hard coded link. That puts you into the zone of seeing if there is a workaround that works. That requires specific knowledge of what you are trying to accomplish with the...

Excel: List of objects on a worksheet, blah blah, code vb

blah blah, code vb, rw 2: John, I am using xl2007 - don t know what you are using, but the behavior might be slightly different. If I select a chart and then go to the VBE and the immediate window and do: ? typename(selection) ChartArea that has a thick white border around...

Excel: Listbox, excel, listbox

excel, listbox: The easiest option to create and maintain is to use the menu Data- Validation on the cell. If you select List on the Allow menu it will create a drop down list on the cell, with the values that are entered in the source (this can be a list of values or a...

Excel: .ListFillRange Problems, cell a2, acell

cell a2, acell, source sheet: JPierce, I took your code and added my code to it in the appropriate places - named a sheet 60809 and put some data in that sheet in D12:D21. had a sheet9 and then ran the code -- this worked fine for me: Sub AddComboBoxes1() Dim cb As Object ...

Excel: Listing array data:, array data, blank rows

array data, blank rows, relative name: Eric, Question 1: There are two methods: 1) Use SORT to sort the data table so all the blank rows come together, then you can either delete then or hide them 2) use the AUTOFILTER (the FILTER in 2007) to HIDE only the blank rows. Question 2:...

Excel: Lookup? Match? Offset?, time management specialist, spreadsheet formula

time management specialist, spreadsheet formula, priority values: Renzo, It is possible, but it s COMPLEX. The answer involves the MATCH(), INDEX(), and OFFSET() functions. There are other simpler functions required also, like COUNTIF(). If you could SORT your task-table, it might be easier, but...... ...

Excel: Lookup question, dear sirs, pivot table

dear sirs, pivot table, field settings: Fk Wan, It seems to me as though this is a PERFECT application for a pivot table. Put the ITEM in the row area, the CLIENT in the column area,and everything else into the DATA area. If you don t like the results, once you have created the pivot...

Excel: Lookup question, menu operations, logical functions

menu operations, logical functions, autofilter: Matt, I suspect that you re not telling me the whole story, so my answer may not be what you want. Its actually very difficult to do what you want (though it can be done). The equations are complex logical functions and it takes about ten columns...

Excel: Lookup from Separate Sheet, failure number, excel functions

failure number, excel functions, aidan: Best one is vlookup - this would need the part number to be in a column before the supplier data - doesn t have to be the first column - see http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls for explanations of this and many other excel functions....

Excel: Lookup from separate sheet, mr mrs ms, failure number

mr mrs ms, failure number, excel function: Mr./Ms/Mrs Mysterious X, The lookup table specified in the VLOOKUP must be more than one column wide. You re asking to retrieve from column 16, but you have only specified a one-column lookup array. So Excel gets confused. =VLOOKUP(A2,BOM!$A$5:$A$4347,16)...

Excel: Lookups VBA, cell1, sh1

cell1, sh1, sh2: Brian, this worked for me. Test it on a copy of your workbook. Assumes data in Sheet2 is in columns A:C. Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1Date As Range, r2Date As Range Dim r1Name As Range, r2Name As Range Dim cell As Range,...

Excel: not letting data move after creating sheet, location column, number column

location column, number column, colums: question not exactly clear, and I personally wouldn t insert data into the list, just add it to the bottom and re-sort later, but if you click on the row number and then press ctrl + you will insert an entire row - which I THINK is the issue you are describing...

Excel: linking sheets, vlookup function, material names

vlookup function, material names, nipun: Nipun, lets call the first sheet the DATABASE and the second sheet the INVOICE. You can use the VLOOKUP() function on the INVOICE sheet to lookup the corresponding rates from the DATABASE. I ve included my tutorial on the VLOOKUP function. ...

Excel: Macro, spreadsheets, a5

spreadsheets, a5: Are you placing this sub in its own Module? That is, are you using Insert/Module and putting it there? Or are you right-clicking the sheet and using View Code & putting it there? The first way is correct, the 2nd is NOT correct. The location of macros is important....

Excel: Macro, code selection, curser

code selection, curser, cell a1: Jackie, If your data has no gaps you can add the following steps to your macro. 1. Select the column you want to start entering data into. This can be done with the following code: Range( A1 ).Select This will move the curser to cell A1 ...

Excel: Macro to compare and delete worksheet, comparison data, csv

comparison data, csv, match: ...column A:8 thru A:28... I don t understand that reference. Columns, as you know, are not referenced by or with numbers, rows are. Regardless, one way is create a string variable for both sheets s data and then compare the two variables, something...

Excel: Macro for data entry when opening excel, chip pearson, project workbook

chip pearson, project workbook, caption bar: Alison, are you familiar with events. If not see Chip Pearson s page on events: http://www.cpearson.com/excel/events.htm you would want to use the Workbook_Open event. It is found in the Thisworkbook module for your project/workbook and can be reached...

Excel: Macro For Formating, target value, vba project

target value, vba project, grey shade: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: Macro to Generate User Form, checkboxes

checkboxes: It s possible, but I never do it, so have no expertise there. -- perhaps you can create a userform which has the maximum # of checkboxes you d ever need and simply adjust the height of the form to show only the appropriate ones, or make some visible/invisible...

Excel: Macro for hyperlinking files, c text, error return

c text, error return, ogilvy: John, you can test the existence of the file Sub hyperlinks() Dim c For Each c In Selection s - XXX & YYYY & c.text & .PDF if dir(s) then ActiveSheet.hyperlinks.Add Anchor:=c, Address:=s end if Next c End Sub -- Regards,...

Excel: Macro on imported external data., worksheet changes, macro name

worksheet changes, macro name, sheet tab: You might try this -- run your macro automatically when the worksheet changes -- but then it d run every time you make a change, so I don t think it s a good idea. However: right-click the sheet tab on the sheet onto which the import goes, select View Code,...

Excel: Macro Leaving Apostrophes in Formula, apostrophes, apostrophe

apostrophes, apostrophe, ogilvy: Katie, this is a common mistake. (done it myself on occasion). You are mixing R1C1 notation in A1 notation and the FormulaR1C1 is expecting just R1C1. It sees the B3 as a constant. You should use: ActiveCell.FormulaR1C1 = =Average(R3C2:R[-1]C) ...

Excel: Macro for Pivot Table, sheet names, pivot table

sheet names, pivot table, pivot tables: Jackie, You didn t include the recorded macro, so I can t give you specifics, but where it specifies a specific location or sheet, you would need to put in variables. Instead of sheet1! . . . you would have & activesheet.Name & ! . . . ...

Excel: Macro to paste unique entries of a list into another list, true range, pastes

true range, pastes, list of names: JoSe, I will assume there is a header/label in B1 and you don t want this header/label in AD3: Sub MakeUniqueList() Dim r as Range, r1 as Range Set r = Range( B2:B500 ) Range( AD3:AD501 ).ClearContents r.AdvancedFilter _ Action:=xlFilterCopy,...

Excel: Macro Runtime 13 Mismatch Error, data columns, gothic medium

data columns, gothic medium, mismatch: Since the macro is doing two loops then it is real hard for me to tell WHEN within the loop the error is occurring. It could at the beginning, at the end, or at some point in the mid stream. Have you tried going back to the person who wrote it to fix this?...

Excel: Macro to refresh all pivot tables, pivottable reports, external tables

pivottable reports, external tables, pivot tables: ~Kathy, I assume the code is in the workbook that contains the PivotTables. then I would think: Sub RefreshPivot() Thisworkbook.RefreshAll End Sub from excel VBA help for the Refreshall method: ----------- Refreshes all external data ranges...

Excel: Macro To Sort Data, mar dec, jan feb mar

mar dec, jan feb mar, adnan: In the code behind ThisWorkbook : Private Sub Workbook_BeforeClose(Cancel As Boolean) SortIt End Sub In the code behind the sheet you want to sort (say it s sheet Sheet3 ): Private Sub Worksheet_Deactivate() SortIt End Sub In a regular...

Excel: Macro to sort, copy worksheets, sumif

copy worksheets, sumif, rw 2: Anand that is what it should be doing now. If you changed the sort to sort on column B first, then that would be problematic, but the way I sent it, it should do exactly what you asked. the code was tested and it did work that way for me. --...

Excel: Macro / VBA Question, sheet1, ogilvy

sheet1, ogilvy, spreadsheet: Jennifer Yes, it should be possible. If you are looking for help on what such a macro would look like, you have not defined what determines if a row is duplicate or not. Is there a single column on each sheet where the values can be compared or does...

Excel: Macros, target characters, target text

target characters, target text, text coloring: Sarah, Right click on the sheet tab where you want this behavior. Select view code. In the resulting module paste in this procedure/event: Private Sub Worksheet_Change(ByVal Target As Range) If InStr(1, Target.Text, 2nd DD Complete , vbTextCompare)...

Excel: Macros to circumvent character limitations in Excel 2003, character limitations, character limitation

character limitations, character limitation, microsoft support: Seems odd to me that the code can t grab the entire contents but regardless try this: - untested Dim FirstHalf as String Dim SecondHalf as String FirstHalf = Left(Len(Worksheets(tabName).Range( B18 )/2,2) SecondHalf = Right(Len(Worksheets(tabName).Range(...

Excel: Makcro to auto sort, random formulae, jon dee

random formulae, jon dee, excel spread sheet: IF you are asking me to write the code for you then 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...

Excel: Making changes to chart data using a macro, template sheet, spread sheets

template sheet, spread sheets, spread sheet: It s certainly possible, and indeed I regularly do the same thing. What I m not clear on though is why this process actually NEEDS to take place - if the data can be exported to a file with a known file name (or even without) and your template sheet is saved...

Excel: Masking Data, sensitive data, data sheet

sensitive data, data sheet, cells: You can have a style which has a font of white and you can protect these cells and include Locked and Hidden (hidden means it won t show in the formula bar when clicked), and for further protection you can not allow locked cells to be selected when you...

Excel: Medical Caluclation on Excel, serum creatinine, creatinine clearance

serum creatinine, creatinine clearance, weight males: Renee, Sorry about that. There are a couple of problems in that formula Got my MINs and MAXs reversed. Used 70 inches for 5 ft instead of 60 inches Anyway, I think this is fixed: =IF(C2= , ,IF(C2= M ,50+2.3*MAX(0,F2-60),45.5+2.3*MAX(0,F2-60)))...

Excel: Microsoft Excel- Referencing the contents of a cell by row and column, bad eyesight, data validation

bad eyesight, data validation, microsoft excel: I have problems seeing most of the pictures that get onto this site- probably my bad eyesight - but my email is aidan.heritage@virgin.net - that way you can send me the file - I m pretty sure it s similar to http://www.aidanheritage.byethost3.com/excel/UseOfMatchAndOffset.xl...

Excel: Microsoft Excel Spreadsheet, microsoft excel spreadsheet, cell b2

microsoft excel spreadsheet, cell b2, cell c2: Mike, What I ll do is suggest several methods by which you can create address of the type you need, and let you work out the details of applying them. But rest assured, what you want can be done when you learn these techniques. There are THREE...

Excel: Microsoft Office Excel Functions, office excel, due tomorrow

office excel, due tomorrow, microsoft office: Andrew, There is a function which can calculate the NUMBER of combinations, but there isn t an easy way to LIST them all. Look at the PERMUT() function for the NUMBER of combinations. Otherwise, you will have to design a set of formulas...

Excel: Move data from multiple excel worksheets to one worksheet, cell b1, sheet names

cell b1, sheet names, indirect address: Bob, You can use the INDIRECT(ADDRESS(parameters)) function combination to make this happen quickly. When you read about ADDRESS(), you ll see it can accept a sheet-name as one of the parameters. So you ll need to type a column full of 80 sheet...

Excel: MS Office 2003, autosafe, xla

autosafe, xla, opslag: Hallo Henk, Als je nog ergens een oude CD va de vorige Office hebt, dan kan je daar de oude file autosave.xla afhalen, die werkt gewoon. Maar kijk ook eens hier naar: www.jkp-ads.com/downloadnl.asp#autosafe Over Word weet ik helaas niet genoeg om de...

Excel: Multi Criteria Lookup with Wildcard, tom mccann, situation index

tom mccann, situation index, result column: Tom, Wildcards don t work in an equality situation. {=(INDEX($E$4:$E$636,MATCH(1,(Left($D$4:$D$636,len($I30))=$I30)*($B$4:$B$636= Final Pass ),0)))} should work as a general approach and without seeing your data. -- Regards, Tom Ogilvy...

Excel: Multiple Conditions - How?, math 2, learning areas

math 2, learning areas, logical functions: Ivan, The solution to this is entirely in the realm of Logical Functions. What you need to do is write an English sentence which says exactly which conditions must be met. The sentence should be in terms of AND and OR, possibly also NOT. Once...

Excel: Multiple Rows to Single Row with Multiple Columns, mail code, sumproduct

mail code, sumproduct, store2: Brian, Here s what I d do (if you still need a solution after all this time): Create a grid with mail-code in a column, and each possible store name across the top. At the intersection of mail-code and store name, put an advanced SUMPRODUCT()...

Excel: Multiple value’s in one cell., excel sheet, action id

excel sheet, action id, peltier: Offhand, I d say this does require a function macro -- what s your hesitation? But I ll take another look if you send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark...

Excel: If-Then macro, range c, isempty

range c, isempty, d20: Hi Nick How are you? Please have a look at Sub deleteRows() Dim R As Range, C As Range Set R = ActiveSheet.Range( D1:D20 ) For Each C In R If IsEmpty(C) = True Then C.EntireRow.Delete End If Next C End Sub Cheers Adelaid...

Excel: macro help with correction, silly mistake, sh2

silly mistake, sh2, sh1: M. Misra, Sub matchdata() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range, rr As Range, cell As Range Dim sAddr As String Set sh1 = Worksheets( Sheet1 ) Set sh2 = Worksheets( Sheet2 ) Set r1 = sh1.Range(sh1.Cells(2, B ), sh1.Cells(2,...

Excel: match and look up problem, c1 40, table a1

c1 40, table a1, richard florida: fkwan I do not see a specific question, but I will assime it is how do I write a formula in C3, that will accomplish this If C2=A1,the result is C1*50%, if C2=A2, the result is C1*40%, if C2 = A3, the result is 100) The formula in C3 would look...

Excel: multiple reference, layout section, pivot table

layout section, pivot table, catagory: This can be done with a Data Table: 1. Select the Data drop down menu 2. Select PivotTable and PivotChart Report... Follow the directions given on the pivot table popup menu go into the Layout section and do the following: The Row should be the...

Excel: multiple tables, time product, four tables

time product, four tables, unice: This is simple but involves many steps. 1. Add a column called PRODUCTS then change the name of the ICE CREAM SOLD to NO SOLD 2. Merge all four of the tables into one 3. Add another column called RATING In this column you will put your ratings...

Excel: #N/A of Vlookup, ISNA, VLOOKUP

ISNA, VLOOKUP: Hi, Yes, the ISNA function can make this happen for you. It tests for an #NA error result and can be used like this: =IF(ISNA(your VLOOKUP formula),0,your VLOOKUP formula) An example: =IF(ISNA(VLOOKUP(A1,C1:C50,2)),0,VLOOKUP(A1,C1:C50,2)) If...

Excel: NEED Help with a formula to delete multiple rows of data, index column, relative reference

index column, relative reference, pivot table: Noami, You can t delete rows with a formula, and I don t see how a pivot table would help either. If you know some clever technique for deleting rows based upon a pivot table, go ahead and use it. What I would do is sort your table by part...

Excel: NEED Help with a formula to delete multiple rows of data, countif function, unique records

countif function, unique records, aidan: Not clear if there is other data, but data, filter, advanced filter, unique records might be a way to go, otherwise a decreasing countif function would do it =countif(a1:$A$100,a1) where the formula is being entered in row one, testing column a, and...

Excel: Need help with a macro, text import wizard, blank worksheet

text import wizard, blank worksheet, import data: Hi Irfan, Try: On a blank worksheet, select Data, Get External data, Import data. Select the file type from the bottom dropdwon and point to the file you need imported. The text import wizard opens so you can set the import specs. Next time you need...

Excel: Need help removing 64K rows at the bottom of a worksheet, vba macros, blank rows

vba macros, blank rows, ogilvy: chris, First I will assume you don t have any merged cells on the sheet. If that is correct, then select the first cell or row you want delete and then run this macro. sub ClearRows() Dim r as Range set r = range(activeCell,cells(rows.count,1)).EntireRow...

Excel: Need help with rental charge marco/formula, rental charges, scaffold

rental charges, scaffold, d5: Kevin, If you still need an answer to this, then here are my thoughts. First of all, the rental cost per day should not be date dependant, or else the title of this column is misleading. It should simply be the rental cost per day, independent...

Excel: Nested And Statements, award contracts, invisible space

award contracts, invisible space, capitalizations: Ruben, Your equation looks correct, except that you have lots of extra () pairs that you don t really need. I don t see that those extra pairs would cause your equation to fail. I made a short-form of your equation by taking out most of the text...

Excel: Nested IF Statements, negative answers, h11

negative answers, h11, percentage change: Because of the way QuickBooks works on the if then order, the best solution I can give you is to put the formula you have above of: =IF(ISNUMBER((H11-I11)/I11), (H11-I11)/ABS(I11), NA ) into cell J11 Then in cell K11 you can put in the following...

Excel: Do I need a macro for this?, cell c5, validation scheme

cell c5, validation scheme, cell a2: No, a macro is not needed but in my opinion it would help and would be the preferable way to really accomplish what you want. You can write a few IF statements next to C5 and C6 that check for the payment type X or payment type Y and make those cells...

Excel: Offset, sh2, sh1

sh2, sh1, question thanks: Charlie: Here is the revised code with that line included. Sub RetrieveCommentsBox() Dim r As Range, r1 As Range, r2 As Range Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Worksheets( Entry ) Set sh2 = Worksheets( Dbase ) Set r = sh2.Cells.Find(What:=sh1.Range(...

Excel: Overtime Calculation Formula, overtime calculation, cell a1

overtime calculation, cell a1, cell reference: Ravi, You need TWO equations, one to calculate regular time and one to calculate over time. If the Time Out (TO) is in the early morning, while the Time In (TI) is in the evening, TimeOut (TO) minus TimeIn (TI) will be a negative number! To...

Excel: optimization using integer linear programming, optimization problem, suitable value

optimization problem, suitable value, objective function: Harpreet This is not the type of problem that I work with, therefore I don t really have the knowledge you need. I suggest you reask the question of another allexperts person. Check the resumes for someone who might have experience in this specific area....

Excel: Password prompt macro, entry password, case entry

entry password, case entry, true user: Dave, I saw you were having trouble understanding Bill s answer. (Please don t rate me since you didn t ask me this question - if you don t understand what I offer, then just disregard it). Your main problem is that you unload the userform in the...

Excel: Paste Values, paste values, anomally

paste values, anomally, questin: Abe, My mistake. I used LEFT and I should have been using RIGHT. I also put in a safety so it quits if it doesn t fine the end in around 5 rows. Also, I am just putting up ideas here - not necessarily turnkey systems. Sub abc() Dim bFound...

Excel: Pivot table (blanks), blank cells, giant region

blank cells, giant region, pivot table: Caroline, The Excel default is to write the word BLANK so you can clearly identify what is being counted, summed, etc. I don t know of any way to change this behavior. If it really bugs you, you could make a cell-by cell copy of the pivot table...

Excel: Pivot table source in excel 2003, external data source, pivot table in excel

external data source, pivot table in excel, xl2000: Nick, I don t have any version but xl2007 where I am right now. This isn t something I have needed to do personally but have dabbled in it to help someone else. I do it using VBA and it turns out Microsoft has an article telling how to do it using VBA....

Excel: Pop up message, xls, fn

xls, fn, wb: Yes. Why not just save the file yourself when the wb opens and not aggravate the user with a msgbox ever? Just do (untested) Dim FN_Date as String FN_Date = Cstr(Format(Now(), DDMMYYYY )) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=FN_Date...

Excel: Pop up message, harleen, ogilvy

harleen, ogilvy, xls: Harleen, If the original name won t include Allocation Template, then you can Private Sub Workbook_Open() If instr(1,thisworkbook.name, allocation template ,vbTextcompare) = 0 then MsgBox Please resave this workbook as DDMMMYY Allocation Template.xls...

Excel: Populate Data in Multiple Worksheets, paste data, retention plan

paste data, retention plan, information thanks: Sounds like an array filter, although I m not 100% sure why we need to have the data repeated multiple times - however, I m sure there is a reason. Array filters are best seen rather than described - see http://www.aidanheritage.byethost3.com/excel/Array_Filter.xls...

Excel: Populate matrix from column data, r2 r3, record selection

r2 r3, record selection, selection function: Ron, If you actually have the text R1 , R2 ... etc and C1 , C2 .. etc, in columns A & B, then you can use one of the multiple-criterion record selection functions to retrieve the number associated with those row & column names . If there is...

Excel: Populating cells on a conditional formula, move rows, absolute references

move rows, absolute references, relative reference: Sorry, I made a guess that you knew about the different types of references - you need to use what are called ABSOLUTE references - to explain e1:e2 is a relative reference, so will change (as you ve seen) as you drag it down - the row numbers get bigger...

Excel: Possible Combinations from a list, science problem, homework problem

science problem, homework problem, excel macro: Michael, I ve gotten this question many times before, and haven t been able to find a reasonable answer for it. There are SO many possible combinations of numbers that thousands and thousands of equations are required to even begin to approach the solution....

Excel: Possible combinations from a list, rng, ubound

rng, ubound, random numbers: Michael Drayton, Assume the random numbers start in A1 and go across row 1. So, for example if you had 6 numbers, they would be in cells A1:F1 You didn t say you wanted a specific set of numbers in the combination, so I assume you want to count any...

Excel: Is It Possible To Extract Data From Networked Hubs Into One Worksheet?, input cell, correct values

input cell, correct values, hermanson: Alex, It is possible to extract data from closed workbooks which are located on other servers in your company intranet. If these workbooks have sheets, or possibly ranged names, which are identified by the consultant s initials, you can extract data...

Excel: Predictive Sum, forecast revenue, 5 months

forecast revenue, 5 months, 10 months: Sorry - this is more a math/simulation question than an Excel question. If you know the formulas or how you would do it using pencil & paper and can explain that, I can help. Or, if you have a few examples of values for A,B,C and what the result might look...

Excel: Prepare for a toughie, floating point numbers, john walkenbach

floating point numbers, john walkenbach, number signs: Jan, Extract from Excel VBA help on the date variable: Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59. Any recognizable literal...

Excel: Pulling information from one worksheet into another, autofilter, ogilvy

autofilter, ogilvy, spreadsheet: Stephanie, If, as you say, The idea is to simply be able to update one spreadsheet , then why make more than one? I don t follow your reasoning. What I would do is stick with one, the one that you have. if you want to create reports for the...

Excel: pasting into an autohotkey file, file excel, excel programming

file excel, excel programming, text application: Steve, I know nothing about an autohotkey file, but it sounds like it is just a text file. Excel can write text files either using SaveAs (and then change the extention) or write it out with VBA. All that said, if you are doing it manually, select...

Excel: percentage formulas in sxcel, office excel, sp2

office excel, sp2, using office: Joe, If you have the question, then it isn t too simple. Write an equation in Column F that multiplies the number to its left (in column E) by 0.6000. That will result in column F having 60% of the number in column E, which is the same as 40%...

Excel: pivot table, pivot table, amts

pivot table, amts, occuring: Sarah, Ok, How about this. 1. Go the the tab with all of the data 2. Make sure you have a title for each column. 3. Click somewhere in the table 4. Select the Data drop down menu 5. Select Filter 6. Select Autofilter 7. Click on the drop...

Excel: Having a problem using a variable name for opening multiple files., explicit function, variable string

explicit function, variable string, experience programming: Jenna, You said your code was successfully opening one file then errored I just need get this to open more than one file. You said the cell contained a filename and that was held in the File_Name variable I ve set my spreadsheet up that in the...

Excel: pv table, left hand corner, menu organization

left hand corner, menu organization, column headings: Sunny, there are a couple of ways you could do it. You could proceed as you do now and get the second pivot table, then select the second pivot table (I usually select all the cells on the sheet by clicking in the upper left hand corner at the intersection...

Excel: Question in Excel, frequency function, question thanks

frequency function, question thanks, excel sheet: Hi Ashutosh How are you? Please do the following 1. Tools - Macro - Visual Basic Editor (for Excel 2003). In Excel 2007, you have to display the Programmer on menu Bar 2. Insert - Module 3. Copy the code I have sent to you to the empty window that...

Excel: Questions for Excel..., cell b2, table of values

cell b2, table of values, b200: A table with all the values needs to already exist somewhere so you could look up the values. If that table were in Sheet2!A1:B200, then in Sheet1, cell B2 could have this formula: =IF(A2= , ,VLOOKUP(A2,Sheet2!$A$1:$B$200,2,FALSE)) and fill down. To not...

Excel: Quicker way to delete out the single lines, data tools, single line

data tools, single line, subtotal: Jeremy, You labelled Location as being in column D. I only know what you tell me. Sub DeleteSingles() Dim r as range, cell as Range Dim r1 as Range Dim r2 as Range set r = range(cells(2, L ),cells(rows.count, L )) set r1 = r.specialcells(xlconstants)...

Excel: question related to excel., microsoft excel, cells

microsoft excel, cells, fonts: sapna Assume that in cells A1 thru C14 you have your text. In another part of the worksheet create the formula +UPPER(A1), then copy this formula to a range that will allow enough room for the text in A1 thru A14. The formula will copy the text...

Excel: One last question on the submit button macro., format cells, blank row

format cells, blank row, macro code: Kenneal Harrigan when you copy and paste, you paste formatting and protection is part of formatting. If you don t need the formatting, try just pasting values. r.copy r1.PasteSpecial xlValues instead of r.copy r1 (above code is notional. r and...

Excel: Random Number Function, random number function, paste values

random number function, paste values, paste button: Jeff, In xl2003 and earlier: Select the cells with the formula and do Edit= Copy, then without changing the selection, do Edit= Paste Special and select values. This replaces the formulas with the values they returned. in xl2007, to do the same...

Excel: Range problem for graph, element changes, second element

element changes, second element, tricky question: You need to use the INDIRECT worksheet function =indirect(EVModel!$E$12:CONCATENATE( EVModel! ,ADDRESS(13,Dashboard 2!R2))) which converts text that looks like a reference into the reference itself. should it be useful here or at any point, my email...

Excel: Range.Value increase by 1, parent cells, followup question

parent cells, followup question, combobox: JPierce, Well, you picture isn t readable, so perhaps that would have been clearer if it had been. No big deal. Sub AddComboBoxes1() Dim cb As Object Dim aCell As Range Dim r As Range Dim s As Range this line just to show you what is placed...

Excel: Reading data from one excel to aanother througha macro, reading data

reading data: 1. Have both file A and file B open 2. Select file B 3. Start a new macro as follows: - Data - Macro - Record New Macro... 4. Select file A 5. Go through the steps necessary on file A 6. Once finished end the macro...

Excel: Reference another worksheet in VBA, vba, rg

vba, rg, a1: change ActiveSheet to Sheets( A ) or whatever the name of the sheet is. To make it more generic, use it inside the function and pass a range: Function LastRow(Rg as Range) As Long Dim ix As Long ix = rg.parent.UsedRange.Row - 1 + rg.parent.UsedRange.Rows.Count...

Excel: Referencing a sheet index in a formula, countif, sheet index

countif, sheet index, index number: To my knowledge, a formula can not do that - a common complaint from many users. You can try this link but IF I understand what you are wanting to do then I think this link will not help. But, take a look anyhow: http://www.contextures.com/xlFunctions05.html#RefShee...

Excel: Referring to "last row" in a formula., array formula, curly brackets

array formula, curly brackets, cell a1: John, The easiest way is to have =Subtotal(3,B3:B5814) where 5813 is the last row with data. then do your insertions somewhere above 5814 and the formula will expand. another way is to use an array formula =SUBTOTAL(3,INDIRECT( B3:B &LARGE(IF($B$1:$B$10000...

Excel: Regarding Macros, blank rows, c15

blank rows, c15, cheers: Hi Hankit How arte you? What about the following Option Explicit Sub InsertingRows() Dim R As Range, enc As Boolean, I As Integer Set R = ActiveSheet.Range( A1:C15 ) I = R.Rows.Count enc = False Do While I = 1 And enc = False If R.Cells(I,...

Excel: To Remove Hyperlinks, excel sheet, adnan

excel sheet, adnan, l3: Hi Adnan How are you? This is not the fully solution to your problem. But I hope it helps you. Sub removehyper() Dim R As Range, h As Range Set R = ActiveSheet.Range( E3 ) Set h = ActiveSheet.Range( L3 ) If R = Now() Then h = Na h.Hyperlinks.Delete...

Excel: Remove Special characters, question thanks, quick reply

question thanks, quick reply, customer database: Sub cleanAllText() Dim rngUsed As Range, rngCheck As Range Set rngUsed = Range( a1 ) Set rngUsed = Range(rngUsed, _ rngUsed.SpecialCells(xlLastCell)) Application.Calculation = xlCalculationManual For Each rngCheck In rngUsed.Cells...

Excel: Repetitive entries, excel, advanced filter

excel, advanced filter, unique list: In this case I would suggest to use the Advanced Filter (menu Data- Filter- Advanced Filter). You can select the option Copy to another location , enter the destination on the Copy to field (same worksheet, but you can move it later), and select the check...

Excel: Report Format macro, keyboard shortcut, hot key

keyboard shortcut, hot key, co worker: The easiest way to run the macro in my opinion is use the hot key that has been already established to run it, Ctrl+Shift+F. And not sure what you mean by to get the macro to work alone or with a button. Try this link: http://www.ozgrid.com/Excel/assign-macros.htm...

Excel: Request for a marco to copy data from different workbooks into an excelsheet, sheet names, spath

sheet names, spath, sname: Sris, Sub copydata() Dim sh As Worksheet, sh1 As Worksheet Dim sPath As String, sName As String Dim bk1 As Workbook Dim col As Long col = 1 ThisWorkbook.Activate Set sh = ActiveSheet sh.Cells.ClearContents sPath = C:Myfolder sName = Dir(sPath...

Excel: To Restrict Scroll Area For Users, sheet1, error method

sheet1, error method, secon: I don t understand how YOU specify the last row -- where/when do you do this? Without knowing the above, this is what I ve come up with for your other requests: In code for ThisWorkbook: Private Sub Workbook_Open() Sheet1.Activate ActiveSheet.ScrollArea...

Excel: Returning value with text, skilled person, h5

skilled person, h5, c5: Firstly congratulations on being the first person who s left a picture that came out large enough for me to see what the question was! Try =INDEX($C$4:$H$4,1,MATCH(MAX(C5:H5),C5:H5,0)) NOTE that this will return the FIRST entry that matches, so if you...

Excel: Rounding numbers, rounding numbers, math teacher

rounding numbers, math teacher, accurate answer: Nada, Unless the student enters the number as a text value rather than a number, what the cell displays in terms of decimal places is determined by formatting. If the cell is formatted as general, then entering 447.0 displays 447 by itself. If I format...

Excel: re: is this sumproduct:sorry! here is my corrected question!, double criteria, c values

double criteria, c values, column c: The formula itself looks fine, so I would IMAGINE that one of the cells in the range c2:c401 or J2:J401 is also evaluating to an error - which gives an error to the entire formula If I could see the workbook I could be more specific - my email is aidan.heritage@virgin.net...

Excel: reformat a spreadsheet, fulfillment company, csv files

fulfillment company, csv files, sort command: You can more easily do it by sorting the columns into the sequence you want and then deleting the right-most columns you don t. For example, suppose you want only columns C,D,J,G,M,E in that order and don t want the others. Once the data is in Excel, insert...

Excel: regarding grid lines in excel Sheet, mail id, grid lines

mail id, grid lines, excel sheet: Vinod, (1) Firstly, there is no cell A in excel. A is a column. The cells are specified as A1, B5 etc. So you need to give exact requirements. (2) Please let me know the exact Excel Version you are using as since Excel2007, entire scheme of commands has...

Excel: Saving form to a specific drive and folder, false application, true application

false application, true application, uline: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True Application.EnableEvents = False Application.ScreenUpdating = False ChDrive G ChDir G:Purchase order log ThisWorkbook.SaveAs PO No & Format(Sheets(1).Range(...

Excel: Saving a tab-delimited file within a macro, spreadsheet changes, excel spreadsheet

spreadsheet changes, excel spreadsheet, individual records: Since I have never seen your code then it is very difficult for me to tell you why the code is not working or offer any changes to make it work, wouldn t you agree? Regardless, ONE way is to make a copy of just the sheet itself that has the data and then...

Excel: SELECT MULTIPLE LARGE RANGES OF CELLS, h1000, l1000

h1000, l1000, control key: 1. Move to the first cell you want to highlight and click on it. 2. Keep your mouse button held down and push the Control key ( CTRL ) down and keep it down. 3. Now release the mouse button, go to the next section and use the mouse button to select it as...

Excel: Search, filter function, work library

filter function, work library, autofilter: Matt, Look into the FILTER function, or possibly the ADVANCED FILTER. Reading about this function should give some ideas how to start. Your question is somewhat vague, by the way, so I am sorry that I cannot offer a more specific answer. ...

Excel: Search then copy based on matching results, firstname lastname, bob phillips

firstname lastname, bob phillips, initial column: Cassandra Bilyeu, Assume the first sheet has a tab name of Data1 column A: First name Column B: Last Name Column C: Middle Initial Column D: State ID Assume data has headers in row 1 and data starts in A2 and goes to A1500. Further assume...

Excel: Search Macro, cell b6, cell address

cell b6, cell address, ogilvy: John Sub ABC() Dim cell As Range For Each cell In ActiveSheet.UsedRange If cell.Value = Range( B6 ).Value And cell.Address $B$6 Then cell.Select Exit Sub End If Next If ActiveCell.Value Range( B6 ).Value Then MsgBox Range(...

Excel: Search & Replace '=, formulas excel, absolute references

formulas excel, absolute references, tim tim: Tim, I am so embarrassed by this, I don t know what to say. I m sure that I have used this technique before (perhaps it worked in Excel 2003 and now doesn t work in Excel 2007?). I also wrestled with converting the = back to just =, and couldn t...

Excel: To Send Mail, mail question, b cells

mail question, b cells, counta: Sub Send_Files() Working in 2000-2007 Dim OutApp As Object Dim OutMail As Object Dim sh As Worksheet Dim cell As Range, FileCell As Range, rng As Range With Application .EnableEvents = False .ScreenUpdating =...

Excel: Sequence List, structions, dalgleish

structions, dalgleish, ogilvy: Mr. Millán, the method has been documented in great detail at this site of Debra Dalgleish s. Better to see a well crafted solution than try to cobble together terse in structions in this limited forum. Two separate methods are presented: Method1:...

Excel: Simple chart in Excel - not so simple for me, proper values, left column

proper values, left column, peltier: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. Mark your calendars for a 2-day advanced Excel conference June 17-18 09 in Atlantic City, New Jersey - 4 sessions of...

Excel: Simple encryption, digit phone number, ascii code

digit phone number, ascii code, ascii codes: Diana, Sub ABC() Dim i As Long, s As String, sChar As String Dim oset As Long, key As Long, s1 As String Dim k As Long, j As Long Dim v As Variant, ub As Long Dim Num(1 To 10) As String Dim Alpha(1 To 26) As String Num(1) = 0 For i = 2 To 10...

Excel: Simple VBA Procedure, lowercase characters, vba procedure

lowercase characters, vba procedure, sheet1: John, Names have to start with a letter (or and underscore or backslash) Here are the rules: Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (). Remaining characters in the name can be...

Excel: Sort by Font, calibri, vba code

calibri, vba code, font times: Sub SortFont() Dim Col As Range, toSort As Range Set toSort = Application.InputBox( Click on an column to be sorted... , Type:=8) tsc = toSort.Column find helper column: For i = tsc To 256 If Application.CountA(Columns(i)) =...

Excel: Sorting Data, screws, cheers

screws, cheers, surveys: Hi Paul How are you? There should be no problem with that. 1. Select the range - all columns and rows - you want to sort. 2. Data - Sort 3. Define the column you want to be the sorting key 4. Define the order That is all. The formulas do not mess...

Excel: Sorting Data, dollar sign, copy paste

dollar sign, copy paste, screws: Paul I answered this question several days ago but apparently didn t push the right button to get it to you, Sorry. Apparently you have a formula whose reference is changing as the result of the sort. I suggest you look at the formulas and you may...

Excel: Sorting Data, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: You have to select ALL of the data. If you sort by only 1 column, only THAT column is sorted. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go...

Excel: Sorting data, absolute address, jane 5

absolute address, jane 5, cell reference: Paul, Sorting a table with equations in it is done all the time and should work perfectly. Your problems tell me you re doing something incorrectly, but they don t tell me what it is. As long as the equations do not refer to rows above or below...

Excel: Sorting By Section, excel spreadsheet, style numbers

excel spreadsheet, style numbers, storeroom: Kyle, Neither sorting nor filtering is an automatic operation... these must be done from the menus or ribbon, or possibly from a macro. I suspect, from your wording, that the spreadsheet you get from headquarters doesn t contain this new information...

Excel: Split Cells, split cells, drop down menu

split cells, drop down menu, spreadsheet: 1. Select the Data drop down menu 2. Select Text to Columns... This will bring up the Convert Text to Column Wizard popup menu. 3. Follow the steps in the Wizard and once done select Finish Once done Excel will split the cell. Remember that...

Excel: Spreadsheet Moving Average, medication dose, blank cells

medication dose, blank cells, mouse down: Hi Arthur, This depends on how you calculate your moving average. The AVERAGE worksheet function of Excel ignores text entries and blank cells. But it will be an average of just 9 entries if one of them is empty or is text. Why not put the remarks...

Excel: Spreadsheet question!, free microsoft excel, excel tips tricks

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

Excel: Spreadsheet into Report, sheet names, followup question

sheet names, followup question, question title: Racine, You can send me the actual spreadsheet, but be aware that I will NOT write the equations for you... I haven t got the time (unless you choose to hire me professionally). I ll advise you what functions to use and how-to use them, but will also...

Excel: If/Then Statement, sumif, necessary thanks

sumif, necessary thanks, b 101: Brett, assume you data is in rows 2 to 101 with headers in row1 assume category is in column B and the value you want to sum up is in column D =Sumif($B$2:$B$101, Appliance ,$D$2:$D$101) will give you the sum of values in column D where column...

Excel: If Statements, table a1, spreadsheet database

table a1, spreadsheet database, ogilvy: Jason, In sheet3 put in your look up table A1: IT User 2 B1: MFZ2TQ0015 A2: Admin 2 B2: MQZ2TQ0015 A3: Team Leader 2 B3: MQZ2TQ0031 A4: Admin 3 B4: MQZ3TQ0019 A5: Admin 4 B5: MQZ4TQ0064 and so forth. then say in B9 you enter...

Excel: IF & AND Statements in excel, question thanks, j17

question thanks, j17, boundary conditions: Mo, I had some typos in the formula - it should be: =if(A2= Small ,index(G2:G18,Match(B2,I2:I18,1)+1,1),if(A2= large ,Index(G2:G18,Match(B2,J2:J18,1)+1,1), )) the 1 in the third argument of Match indicates that the data is sorted and match should...

Excel: IF & AND Statements in excel, text phrase, blank cell

text phrase, blank cell, question thanks: Mo, Ummm, I m Bill, not Tom. Are you sure you are talking to the correct Expert? Given my example of how to use AND & OR in an IF statement, you should be able to do this yourself. I can t write every IF statement you come up with for you.......

Excel: Still more..., time series data, group data

time series data, group data, ineptitude: Hunter, I know what locking is, but I wouldn t associate it with what you have described. Tell me how you would do this in Excel 2007 and if I can understand what you are referring too, I will tell you if it is possible in Excel 2003. As far as Inserting...

Excel: Stripping Characters from a Cell, 4 digits, basic principle

4 digits, basic principle, business names: Steven, I took a second look at what I thought was a touch question, and solved it in less than a minute! What you do is use 11 nested SUBSTITUTE functions, each of which finds one digit and changes it into a blank ( which is a pair of double quotes...

Excel: SUMIF with 2 columns, array formula, request number

array formula, request number, sumif: Kowshick M Subramaniam, Since you didn t provide actual ranges, I had to make them up. I see I had several typo s when I entered the formula - it was past 1 AM when I typed it - guess I should have waited till this moring. Here is a correction: Asume...

Excel: SUMPRODUCT Formula, xls spreadsheet, test sheet

xls spreadsheet, test sheet, wakeup: Luke, I should have caught the mistake you d made with the date-criterion.... you had = 4/27/2009 IN QUOTES as the criterion value! that would mean the date had to be TEXT. Now, however, I am confused as to what s text, what s not, and what...

Excel: SUPER FAST AUTOFILL IN EXCELL, drag method, autofill

drag method, autofill, excell: sure - type your value in the first cell, then press F5 (the goto key) and type the full range you want - eg a1:A1000 - OK that (NOTE - the first cell in this range needs to be the same first cell you just entered data into) - finally, press Ctrl D. Job done...

Excel: Sum data from a certain column and row, financial spreadsheet, sumproduct

financial spreadsheet, sumproduct, sumif: Catherine Crump, Are you familiar with pivot tables. This seems tailored made for a pivot table. If you are not, send me a sample workbook and I will set one up to illustrated. twogilvy@msn.com if you are familiar with them and you don t want...

Excel: Sum rows with common data, pie bill, excel function

pie bill, excel function, column c: Roshan, You will be surprised at how easy this is! Just use SUMIF() in column C. Use column A as the criterion range, and column B as the summing range. The criteria values will be references to each cell value in column A. Read about SUMIF...

Excel: Sum Last X number of bars variable, excel functions, lookback

excel functions, lookback, optimal results: Dave, This is easy to do using an advanced function called OFFSET. OFFSET defines a range based on 5 parameters. The parameters can be numbers in cells, which specify the size (height and width) and position (up/down, left/right) of a range....

Excel: Summary Report, colorindex, range c

colorindex, range c, excel sheet: Hi Sirajuddin How are you? Please have a look at Public Function findchanges() Dim R As Range, c As Integer, cel As Range Set R = ActiveSheet.Range( A1:AA100 ) For Each cel In R If cel.Interior.ColorIndex = 4 Then c = c + 1 End If Next cel findchanges...

Excel: Summary Report, colorindex, range c

colorindex, range c, excel sheet: I would use a pivot table to summarise the column A data - that would seem to be the easiest way to do that - otherwise it would have to count if the entry already existed in the list Function find_changes(Val as string,R as range) Dim R As Range, c As...

Excel: Summary Report, r2 value, colorindex

r2 value, colorindex, range c: Sirajuddin, If I understood the question/situation correctly, this worked for me Sub find_changes() Dim R As Range, c As Integer, cel As Range Dim sh1 As Worksheet, r1 As Range, r2 As Range Dim res As Variant Set R = ActiveSheet.Range( A1:AA100 )...

Excel: saving a file in excel from a cell and creating a folder based on that cell name, unc paths, public customers

unc paths, public customers, mypath: I don t know about UNC paths -- never used them. But in general you can see if a folder exists by this: Function FolderExists(ThePath as string) as Boolean on Error Resume Next Err.Clear Chdrive ThePath Chdir ThePath FolderExists = (err.number=0) End...

Excel: search for cell values among multiple .xls files

Olivier, -------------- Glad you got it working. I am not sure why you thought it was important or appropriate to disrespect me for trying to help you, but if you have anymore problems, make sure you ask someone else. -- Regards, Tom Ogilvy ...

Excel: select large nonadjacent ranges of cells, excel, selection

excel, selection, go to: Sorry for the late, I was out for the weekend. Regarding your question, you could use the menu Edit- Go to, and in the reference field type: H10:H1000,L10:L1000,P10:P1000,T10:T1000 This should select the full region in a single step. Hope this helps,...

Excel: select large nonadjacent ranges of cells, down arrow, h1000

down arrow, h1000, l1000: DAN, at the left side of the formula bar is the name box - if you select a cell, the name box will show the address of the cell (or cells) copy the below from this note by highlighting it and doing Ctrl+c H10:H1000,L10:L1000,P10:P1000,T10:T1000 ...

Excel: sequence of numbers, left mouse button, mouse arrow

left mouse button, mouse arrow, mouse down: Jessica Put 1 in the first cell. Say it is A1. then in A2 put in =A1+1 now drag fill that down the column or you can use Excel intelligent help Put a 1 in A1. Put a 2 in A2. Select A1:A2 and drag fill down the column. I used A1 just...

Excel: sequential numbering, job card, sequential numbering

job card, sequential numbering, b100: Hi Tom How are you? I think that you should start it simple. For example Whenever you write a name in A2, a number turns up in B2. Just try the following: 1. Type the start number in B1 2. Type a name in A1 3. Type in B2 =if(IsEmpty(A2);...

Excel: shading entire row based on cell value, conditional formatting, subject numbers

conditional formatting, subject numbers, value question: Tina, Careful, you might hit someone with a flying computer! There are better ways to wreak your vengeance, try a satisfying baseball bat instead! The reason that your formula works is that 1 and 0 (the only possible results of your equation)...

Excel: short and quick?, bls gov, excel ftp

bls gov, excel ftp, gov pub: William: I guess you will need to define short and quick. Is it a real involved macro? No. Is it a one or two liner? No Having said that then I would need to see the txt file opened in EXCEL to see how it looks. Just seeing it on and via the web...

Excel: simple date calculation exclude weekends, accurate calendar, column c

accurate calendar, column c, due dates: Jennifer, If you are using Excel 2007, there are two functions built in: NETWORKDAYS () is the one you want. It also will exclude holidays if you set up a holidays table. If you don t have Excel 2007, the problem is much more difficult. You...

Excel: A simple problem that i can not figure out relating to IF., memory cells, score chart

memory cells, score chart, circular reference: Richard, You re right; it s simple on paper but difficult to implement. The process you ve described requires a MEMORY CELL, which can remember the past value (without the info as to how it got that value) and then increment it by some amount...

Excel: sorting 3 columns of cells, satellite tv programs, channel numbers

satellite tv programs, channel numbers, description column: Mark, There is a sorting routine on the Excel 2007 Home Ribbon, on the right, or under DATA -- SORT in Excel 2003. How to use these will be obvious once you find them. HINT: select your entire data table FIRST, including the column heading (e.g....

Excel: sorting in pivot tables with more than one row section, table manipulation, table wizard

table manipulation, table wizard, pivot tables: VOLKAN I am not an expert in pivot table manipulation since I seldom use them--I suggest you reask the question of another allexperts person and I am sure you can get a good answer. Sorry I couldn t be of more help Richard Florida United States ...

Excel: sorting and sub sorting macros in excel, macros in excel, excel sheet

macros in excel, excel sheet, pivot table: Do you mean groups them so it say red dog and shows 5 as the result - if so, no need for a macro - simply use the pivot table option from the data menu (or insert ribbon in 2007) and it will do it all for you - having to rush slightly with my answers as my...

Excel: splitting text, string length, first position

string length, first position, error detection: Venkat, You ll need a series of equations in separate cells. Figure out what is the largest possible number of ZIP codes in one line of text to determine how many extraction equations you ll need. Use the FIND function to locate the positions...

Excel: if statement in excel, problem thanks, ogilvy

problem thanks, ogilvy, checks: the limitation is caused nested levels of a function in Excel 2007 it is 64 in earlier versions it is 7 levels (top level plus 7 nested functions) =IF(A1 1,1,if(A1 2,2,if(A1 3,3,if(A1 4,4,if(A1 5,5,if(A1 6,6,if(A1 7,7,if(A1 8,8,9)))))))) that...

Excel: sum of the dollar sign format only, format cells, currency values

format cells, currency values, cell reference: Bahaa, I don t think there is a way to do this. But you might try this experiment: Use the information function =CELL(reference, format ) on the column of values. If this function returns a unique result for the cells which have the $-format,...