Excel: sorting range names

Excel: Left space, pertinent details, space character

Excel: Left space, pertinent details, space character, added advantage

Excel: spalsh screen , password validation and animation, open application

Excel: spalsh screen , password validation and animation, open application

Excel: split cell values, cells

Excel: split cell values, cells

Excel: How to split an excell field

Excel: How to split an excell field

Excel: spreadsheet comparison, spreadsheet tool, uk software

Excel: spreadsheet comparison, spreadsheet tool, uk software, spreadsheets

Excel: IF AND statement

Excel: IF AND statement

Excel: stop excel assuming I have a header row, header rows, header row

Excel: stop excel assuming I have a header row, header rows, header row, blank fields

Excel: How to sum using macros, simple solution

Excel: How to sum using macros, simple solution

Excel: TAB = NAME, formatting, spreadsheet

Excel: TAB = NAME, formatting, spreadsheet, tabs

Excel: Table with two columns and the formula "COUNTIF", b1

Excel: Table with two columns and the formula "COUNTIF", b1

Excel: Telephone dialer

Excel: Telephone dialer

Excel: Text box, office

Excel: Text box, office

Excel: Tiered Formula for Excel, zapp, widget

Excel: Tiered Formula for Excel, zapp, widget, salesperson

Excel: Time calculation, b1, a1

Excel: Time calculation, b1, a1, clock

Excel: Time formulas, format a1, mm format

Excel: Time formulas, format a1, mm format, cell references

Excel: Time stamp in Excel, range target, target value

Excel: Time stamp in Excel, range target, target value, time stamp

Excel: Timesheets, free timesheet, free trials

Excel: Timesheets, free timesheet, free trials

Excel: Totals by Week, conjunction

Excel: Totals by Week, conjunction

Excel: Transpose, centuries

Excel: Transpose, centuries

Excel: Turning a column of postive numbers into negatives, negative values, special feature

Excel: Turning a column of postive numbers into negatives, negative values, special feature, blank field

Excel: Typing numbers in Excel

Excel: Typing numbers in Excel

Excel: time calculation and shift work, b2, a2

Excel: time calculation and shift work, b2, a2, max 1

Excel: transfer data from excel to word document using macro

Excel: transfer data from excel to word document using macro

Excel: transparent pictuers in excel, openoffice, foreground

Excel: transparent pictuers in excel, openoffice, foreground, cells

Excel: transparent pictuers in excel, openoffice, foreground

Excel: transparent pictuers in excel, openoffice, foreground, system document

Excel: turn off validation input messages, run time error, data validation

Excel: turn off validation input messages, run time error, data validation, input message

Excel: Unchecking readio boxes in Excel, button names, option buttons

Excel: Unchecking readio boxes in Excel, button names, option buttons, cell values

Excel: Unhiding Columns, simple answer, million times

Excel: Unhiding Columns, simple answer, million times

Excel: Unique data in a column, countif, a2

Excel: Unique data in a column, countif, a2

Excel: Unique records count from multiple cells, array formula, unique names

Excel: Unique records count from multiple cells, array formula, unique names, occurrence

Excel: Use of "nested" SUMIF statements, sumif, a23

Excel: Use of "nested" SUMIF statements, sumif, a23, column c

Excel: Use a serial number in Excel, vba project, cell a1

Excel: Use a serial number in Excel, vba project, cell a1, initial value

Excel: User Form: Autofill text boxes based on Combo Box selection, listindex

Excel: User Form: Autofill text boxes based on Combo Box selection, listindex

Excel: User name on Excel Documents, offcourse, unfortunate thing

Excel: User name on Excel Documents, offcourse, unfortunate thing, network environment

Excel: Userform1 combobox to userform2 textbox

Excel: Userform1 combobox to userform2 textbox

Excel: Using OFFSET to refer to external worklbooks, spreadsheet application, workaround

Excel: Using OFFSET to refer to external worklbooks, spreadsheet application, workaround, calc

Excel: Using text in formula result, quotation marks, format menu

Excel: Using text in formula result, quotation marks, format menu, decimal places

Excel: Using Vlookup to find more than 1 criteria, inaccurate results, bacon

Excel: Using Vlookup to find more than 1 criteria, inaccurate results, bacon, egg

Excel: how can we unprotect a workbook password.

Excel: how can we unprotect a workbook password.

Excel: unprotecting password protected sheet, vba, password recovery tool

Excel: unprotecting password protected sheet, vba, password recovery tool, attack brute force

Excel: using workbook.open without starting macros

Excel: using workbook.open without starting macros

Excel: #VALUE! error

Excel: #VALUE! error

Excel: VB Error, generic error message, database security

Excel: VB Error, generic error message, database security, security settings

Excel: VBA Code to Delete Excel File, vbproject, clines

Excel: VBA Code to Delete Excel File, vbproject, clines

Excel: VBA Code - Security Settings, system32 file

Excel: VBA Code - Security Settings, system32 file

Excel: VBA Create Range Names Automatically, sht, time periods

Excel: VBA Create Range Names Automatically, sht, time periods, different time

Excel: VBA Insert formula instead of value, len

Excel: VBA Insert formula instead of value, len

Excel: VBA MAcro Based Upon Cell Click, target range, message box

Excel: VBA MAcro Based Upon Cell Click, target range, message box

Excel: VBA Macro Help. Moving specific data from Sheet1 to Sheet2, vba code

Excel: VBA Macro Help. Moving specific data from Sheet1 to Sheet2, vba code

Excel: VBA/Macro programming, cell b2, code snippet

Excel: VBA/Macro programming, cell b2, code snippet, blank cells

Excel: VBA Problem, xls extension, using tools

Excel: VBA Problem, xls extension, using tools

Excel: VBA runtime error 1004, type conversion, type integer

Excel: VBA runtime error 1004, type conversion, type integer, variables

Excel: VBA Select Directory, answer god, glory to god

Excel: VBA Select Directory, answer god, glory to god

Excel: VBA Select Next Visible Cell, empty cells, initial answer

Excel: VBA Select Next Visible Cell, empty cells, initial answer, rg

Excel: VBA - tie a macro to the click of a cell, snobs, shift key

Excel: VBA - tie a macro to the click of a cell, snobs, shift key, share knowledge

Excel: View image within spreadsheet

Excel: View image within spreadsheet

Excel: Views in excel, print screen button, popup window

Excel: Views in excel, print screen button, popup window

Excel: VLOOKUP with 2 conditions

Excel: VLOOKUP with 2 conditions

Excel: Vlookup

Excel: Vlookup

Excel: Vlookup Function, sheet1, customer name

Excel: Vlookup Function, sheet1, customer name, customer id

Excel: Vlookup References, printing characters, annoying problem

Excel: Vlookup References, printing characters, annoying problem

Excel: #value! error, imtiaz, problem thanks

Excel: #value! error, imtiaz, problem thanks, white spaces

Excel: value in $Millions, parenthesis, format string

Excel: value in $Millions, parenthesis, format string

Excel: #value!

Excel: #value!

Excel: vb code for simple input in Excel, ogilvy

Excel: vb code for simple input in Excel, ogilvy

Excel: vba excel: usage of screenupdating & freezepanes, hth, error message

Excel: vba excel: usage of screenupdating & freezepanes, hth, error message

Excel: vlookup alphabetical order, possiblity, match

Excel: vlookup alphabetical order, possiblity, match

Excel: vlookup and if condition, array example, j8

Excel: vlookup and if condition, array example, j8, j7

Excel: vlookup and na#, p 600

Excel: vlookup and na#, p 600

Excel: vlookup use, video tutorial, illustration

Excel: vlookup use, video tutorial, illustration

Excel: vlookup not working, cheers

Excel: vlookup not working, cheers

Excel: Want to make mandatory cells filled by user, datas, reminder

Excel: Want to make mandatory cells filled by user, datas, reminder

Excel: WEEK NUMBER, sunday to saturday

Excel: WEEK NUMBER, sunday to saturday

Excel: Weekly History, cell changes

Excel: Weekly History, cell changes

Excel: WORKSHEET NAME, cell a1, file extension

Excel: WORKSHEET NAME, cell a1, file extension, len

Excel: Word Wrap, word wrap

Excel: Word Wrap, word wrap

Excel: Work Sheet Tab Size, tabs, contracts

Excel: Work Sheet Tab Size, tabs, contracts, district offices

Excel: Workbook reference, macro book, object integration

Excel: Workbook reference, macro book, object integration

Excel: Workbook_Open macro, vba code

Excel: Workbook_Open macro, vba code

Excel: Working with Formulas, decimal points, options button

Excel: Working with Formulas, decimal points, options button, bottom right corner

Excel: Worksheet Arrangement, asap utilities

Excel: Worksheet Arrangement, asap utilities

Excel: Worksheet Protection, hyperlink, cells

Excel: Worksheet Protection, hyperlink, cells, hyperlinks

Excel: Writing a recursive formula in Excel, address line, line 1

Excel: Writing a recursive formula in Excel, address line, line 1

Excel: i want to restrict to some value in cell of the excel., restriction, ms excel

Excel: i want to restrict to some value in cell of the excel., restriction, ms excel, cells

Excel: watermark an excel worksheet, select properties, drop down list

Excel: watermark an excel worksheet, select properties, drop down list, watermark

Excel: IF, THEN wildcard in Excel, wildcard, b3

Excel: IF, THEN wildcard in Excel, wildcard, b3, b1

Excel: worksheet name in formula, cell a1, cells

Excel: worksheet name in formula, cell a1, cells, a1

Excel: too many worksheets in single workbook, how to organize the worksheet., office microsoft, support microsoft

Excel: too many worksheets in single workbook, how to organize the worksheet., office microsoft, support microsoft, sheet tabs

Excel: how to write square meter, right click menu, word type

Excel: how to write square meter, right click menu, word type, superscript

Excel: XML to VBA conversion, good answer

Excel: XML to VBA conversion, good answer

Excel: IF AND OR, ogilvy, b1

Excel: IF AND OR, ogilvy, b1, c1

Excel: if/then, a3

Excel: if/then, a3

Excel: Zero fill a cell, format cells, decimal point

Excel: Zero fill a cell, format cells, decimal point, zeros

Excel: Zip Codes Starting with Zero, cell reference, zipcodes

Excel: Zip Codes Starting with Zero, cell reference, zipcodes, zips

Excel: Zoom function

Excel: Zoom function

Excel: zero values in pivot tables, blank cell, custom filter

Excel: zero values in pivot tables, blank cell, custom filter, pivot table

Excel: fax merge, excel spreadsheet, interest statements

excel spreadsheet, interest statements, email faxing: Well... Yes, I could help. I could just build the application for you. But that would be several hours of my time. In a 5 min response, I can tell you it will involve coding with VBA. You ll need some code to detect the print section break points and...

Excel: few short questions about excel shortcuts, accounting spreadsheet, excel shortcuts

accounting spreadsheet, excel shortcuts, sheet tabs: 1 - you can t - feature was introduced in 2002 2 - any of: shift right arrow; edit/goto a1:h1; F5/A1:H1... 3 - depends on the length of the tab names. To the right of the last visible tab is a little vertical indicator which can be slid left/right. 4 -...

Excel: How to figure precentage, cell a2, cell a1

cell a2, cell a1, initial value: Let s go first with the paper. We want to know what % it has been applied to our money, we name it as x%. The formula that we need to solve is: $121.23 * x% = $1.32 Isolating the x, it results: x = ($1.32 / $121.23) * 100 Now with Excel, suppose that...

Excel: figurs in words, salary slip, asap utilities

salary slip, asap utilities, fifty dollars: Mandar, There is no easy way for this. Option 1 Right a complex function yourself. Option 2 You could download and install ASAP Utilities (www.asaputilities.com) and you will then find that function under Numbers, Convert Numbers to Words. However,...

Excel: file assocoation, wks files, program option

wks files, program option, floppies: I don t have windows XP knowledge, and this is more a windows question than an Excel question - I CAN tell you that Excel may not be able to open the works files correctly, but you can TRY by simply loading Excel and using File Open to open the files - change...

Excel: If a file is exists then do this, fileexists, newsearch

fileexists, newsearch, many thanks: you said, If the file exists then do this if not do NOT this That translates to using my macro something like: IF Fileexists then doRoutineA Else doRoutineB End If Not sure what else you re asking. The FileExists routine will return True...

Excel: file size - Excel, paste values, mailing addresses

paste values, mailing addresses, copy paste: Guessing here -- would be nice to see it! Maybe you can send the 1Mb file to me at bobumlas@yahoo.com and I ll see what I can discover (use subject of AllExpertsQ). are there any links? Do you have tools/options/calculation/Save external link value selected?...

Excel: file size reduiction, charlie charlie, dalgleish

charlie charlie, dalgleish, source database: Charlie, That is one of the worst ratings I have ever received or any of the experts in the Excel groups has received. Mind sending me an email an letting me know what the problem with the answer was. It certainly seems inconsistent to shaft someone like...

Excel: file can't open, poor netiquette, reply thanks

poor netiquette, reply thanks, network neighborhood: Sorry, I can t help. There are many things that could cause this and wihtout being there it is difficult to say. And, in the future, please refrain from using ALL CAPS. It is difficult to read. It is widely deemed to be poor netiquette since it is considered...

Excel: back up files, vbyesno, hey dave

vbyesno, hey dave, fname: Hey Dave, Try this code if it is ok for you to have the date added before the filename. If this is not ok, let me know and I could write some code trim the xls and add it to the end: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Msg As String...

Excel: filing system, visual basic for applications, macro code

visual basic for applications, macro code, archived copy: Brian, This kind of macro primer information has already been documented on the web and is more thorough than any brief explanation I could write. Here are links to two locations that should speed up your learning: http://www.mvps.org/dmcritchie/excel/getstarted.htm...

Excel: filling in descriptions of drop down menu items, empty cells, macro button

empty cells, macro button, vlookup: Here is what I want to do. After a user selects a menu item in one of the cells in the range Sheet1!A118:142, I want the corresponding descriptor to be placed in the cell next to it. That is, when menu item A is selected in cell A118, descriptor A is...

Excel: filter cells by text font color, filter cells, text font

filter cells, text font, free time: ...how suppose to be macro for that... made no sense based on the way you phrased it. In any event, generally I do not write macros for people on this site who request such a thing. If I did then that s all I would be doing and I don t have enough time,...

Excel: filter count xl 2003, counta, dim statement

counta, dim statement, question thanks: just dim it as a variant - dim Cell For Each cell In v If cell.Height 0 Then SumUnhidden = SumUnhidden + cell.Value Next This SHOULD fix the macro, however, it s a filtered SUM not a filtered count For Each cell In v If cell.Height ...

Excel: filter data with 2 criteria, case calculation, calculation mode

case calculation, calculation mode, filter data: listen moron, I don t think for one minute that you would be brave enouth to talk like this face to face. I don t hate you because you are insignificant. What I object to is your unfair rating of my knowledge when you cannot even solve your own little problem...

Excel: filter data with 2 criteria, case calculation, calculation mode

case calculation, calculation mode, filter data: Hello again Erwin, Yes, you can email me your workbook. My email address is VBAexpert-AT-myway.com (replace -AT- with @ ). Damo...

Excel: filter data with 2 criteria, case calculation, calculation mode

case calculation, calculation mode, filter data: Erwin, I have having difficulty visualising this one. What is in C3 that is being monitored by this code, the employee to be filtered on? Do you have the criteria on the same sheet as the data to be filtered? You want to copy the data to the activesheet?...

Excel: filter data with 2 criteria, case calculation, calculation mode

case calculation, calculation mode, filter data: If you want to filter by month, it s simplest to add a field to your database. For instance, if Col E is a date, you could add a calcuated column F with a formula like: =MONTH(E2) and then in your advanced filter, you d have a 2-column criteria range...

Excel: filter and data protection, business intranet, excel spreadsheet

business intranet, excel spreadsheet, autofilter: Yup...I m using 2002. So, beyond upgrading, here is what I found on the net for earlier versions of Excel; This article indiciates what other features for cell protection are available in Excel 2002+ that are not in previous versions: http://j-walk.com/ss/excel/faqs/protectionFAQ.htm...

Excel: How to filter information from an excel spread sheet, wholesaler product, product id number

wholesaler product, product id number, excel spread sheet: Assuming your master list is in A1:A30000 and your other list is in B1:B2000, enter this formula in D2 (D1 is empty): =NOT(ISNA(MATCH(B2,$A$2:$A$30000,0))) Then select B1:B2000 and use Data/Filter/Advanced filter. Make sure the list range is B1:B2000 and...

Excel: filter and send chose one email, operator name, ship details

operator name, ship details, filter option: Shah I don t think you can do that and have it automatically sent to the email, you would need to use a program like access and run a query. You could use the filter option and copy the cells to a new sheet and then send that to the email printer. Richar...

Excel: with filter on, autofilter, cells

autofilter, cells: Hello Asif Please try selecting the filtered cells Then goto the edit menu and select goto , press the special button on the popup and then select visible cells only. Now if you copy and paste your selection you will only paste the visible filtered...

Excel: filtering data within the date range and 1 criteria, control toolbox, data validation

control toolbox, data validation, commandbutton: Hi Erwin, Here s commandbutton code you can use to do this. Private Sub FilterBtn_Click() Clear results range on Sheet2 (sheet containing button) Range([C8], [F65536].End(xlUp)).ClearContents Dim Crit1 As String From date filter...

Excel: filtering lists, ogilvy, salesperson

ogilvy, salesperson, alias3: Johan, It sounds like you would need a macro to do that. You certainly haven t provided enough information to suggest anything specific. If you want to send me a small sample workbook explaining your problem and what kind of results you want, I could...

Excel: filtering in a protected worksheet, filter function, checkbox

filter function, checkbox: Yes. Use Excel 2003. No prior to that version. If you ARE using 2003, then when you protect the worksheet there s a checkbox indicating you can allow autofilter. If prior to 2003 you can write a macro which will unprotect the worksheet, show the filter,...

Excel: filters in Excel 2003, autofilter

autofilter: Deepak You can use the autofilter, make sure that you select the entire table then activate autofilter, you will see drop down lists on all the columns, select the first criteria in the starting column and then the next criteria in the other column/s. ...

Excel: filters, cell interior, a1

cell interior, a1, cells: Mike, The only way I know to do that is with a macro (aka VBA). The first routine will hide all rows that are not colored red in the A column. The second routine unhides the hidden rows. Let me know if you need help placing the code in the right place....

Excel: find coloured text, font colors, colorindex

font colors, colorindex, coloured text: Example: code below looks at each cell in activesheet range A1:C2, and will select a cell and stop when it finds any character within that cell that has blue font, even if other characters in the same cell have different font colors. Sub findBlueCharacter()...

Excel: find current user in the PC, getusername, type username

getusername, type username, excel formula: I did not send everything last time in error. Here is everything: Private Declare Function GetComputerName Lib kernel32 _ Alias GetComputerNameA (ByVal lpBuffer As String, nSize As Long) _ As Long Private Declare Function GetUserName Lib...

Excel: How to find the diff in time using macros, elapsedtime, endtime

elapsedtime, endtime, difference in time: This doesn t look at all familiar, but you DO have a few errors, mainly the location of your code. The code from the userform should be: Dim StartTime As Date Dim EndTime As Date Dim ElapsedTime As Date Private Sub TextBox1_AfterUpdate() StartTime...

Excel: How to find the diff in time using macros, elapsedtime, endtime

elapsedtime, endtime, difference in time: Dim StartTIme as Date Dim EndTime as Date Dim ElapsedTime as Date Then on the AfterUpdate event of TextBox2 use this code StartTIme = CDate(MyForm.Textbox1) EndTime = CDate(MyForm.Textbox2) MyForm.TextBox3 = Format(EndTime-StartTime, h:mm AM/PM;@...

Excel: find an element in a big array, sstr, hash table

sstr, hash table, mouse type: This code is usually very fast. Sub SearchforText() Dim sStr As String, rng As Range Dim c As Range, rng1 As Range Dim firstAddress As String sStr = InputBox( Enter Text to search for: ) If Len(Trim(sStr)) = 0 Then Exit Sub On Error Resume Next ...

Excel: find a name corresponding to a top mark, vlookup, student names

vlookup, student names, alphabetical order: You can use a function that will show the highest mark and get the corresponding student. As long as the students names are in alphabetical order you can use a vlookup. have a look at this link http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=44...

Excel: find a number, sheel, a7

sheel, a7, match: Having to search in the whole sheet makes it difficult to use built in functions, so I have created a macro to do the task: Sub FindValue() Dim wksData as Worksheet Dim rngValue As Range Dim myValue As Variant Set wksData = Sheets(...

Excel: find and replace, joe smith, first names

joe smith, first names, delimited: Excel has a feature that can help in this case, select the column with the data, go to menu Data- Text to columns, and it will launch a wizard. On the first step, select Delimited; on the second step activate the check box on comma, and click finish on...

Excel: use a number in a cell in lookup from another workbook, xls, ogilvy

xls, ogilvy, a3: Hello Kevin, Remember I said: This is possible as long as the source workbook (Workbook2.xls) remains open. (your original example was written in the style to refer to an open workbook) this = W:A - Clients and ProspectsReportsSecurity[AccountBalances...

Excel: find and replace, set of numbers, multiple times

set of numbers, multiple times, cells: I don t quite follow -- you have, say, 30 cells with 123 in them and you want to change all 123 s to another number? LIke 456? Simply Edit/Replace 123 with 456. Or are you saying you have 30 123 s and you want to replace each one with s unique number so you...

Excel: find second space, middle space, cosgrove

middle space, cosgrove, first position: =MID(A1,FIND(CHAR(22),SUBSTITUTE(A1, ,CHAR(22),LEN(A1)-LEN(SUBSTITUTE(A1, , ))))+1,255) This will return the last word in a phrase separated by blanks, so will work on 2, 3, or more part names. The LEN(A1)-LEN(SUBSTITUTE(A1, , )) temporarily...

Excel: find sheet2 copy find sheet1 paste, string s1, value ranges

string s1, value ranges, string s2: ...For example if the value is in col G in sheet2 it goes in col G in Sheet1 and it should go in col F.... Is this pattern always the same? If so then add .Offset(0,-1) to the end of your code that is now calculating where to paste the data found...

Excel: find a specific cell, cell reference, two different views

cell reference, two different views, drop down list: Hi Ivan, There are several things you can do to be able to see what is on another sheet when you are working on a different sheet. I will explain all just in case you haven t done these before. 1. You can simply type in the formula =Sheet32!F17 wherever...

Excel: find the values that make a sum, allexpert, long ranges

allexpert, long ranges, many thanks: Korneel I m sure that a formula can be contrived to calculate what two numbers, or more, combined with other numbers in an array equal a fixed number, but I cna t seem to come up with the formula. I suggest you reask the question of another allexpert...

Excel: How to find width of MSForms Dropdown list items, combo boxes, character width

combo boxes, character width, vertical scroll: If your using Additem instead of getting the data from the worksheet as I assumed in my earlier response, then I think you will have to experiment and come up with an average character width for each font type and size. I don t know why you would let this...

Excel: How to find width of MSForms dropdown list items, combobox, dropdown list

combobox, dropdown list, guesswork: That depends on the font you are using and the # of characters. You may have to come up with a fudging routine which you can do by trial and error -- using the font you want (I d suggest a momospaced one) see what width is required for the combobox given various...

Excel: find a word and convert it in hyperlink using excel macro, working in germany, excel macro

working in germany, excel macro, occurence: HI Send me the sheet and code you have and I wil;l see what I can do for you. Please be aware that I will be working in Germany until Saturday. So I would have to look at this over the weekend for you. Regards Des limbic_lesion@hotmail.com ...

Excel: finding combination in the selected cells, visual basic for applications, address index

visual basic for applications, address index, conventional method: Your numbers are in range A1:D3. In cell F1 enter a 1. In G1, enter 2. In H1, enter the formula: =CELL( address ,INDEX($A$1:$D$3,INT((F1-1)/4)+1,MOD(F1-1,4)+1)) copy this formula frim H1 to I1. In J1, enter the formula: =IF(INDIRECT(H1)+INDIRECT(I1)=13000,...

Excel: finding combination in the selected cells, target cell, conditional formatting

target cell, conditional formatting, cell reference: Mervin Olivera, If by select you mean to physically select the cells in some way, then no a formula does not support that. If you mean have the cells which satisfy the sum change color, again, that might be possible for a 3 or 4 cells using conditional...

Excel: finding combination in the selected cells, goto tools, sumproduct

goto tools, sumproduct, conventional method: Mervin, Interesting question. There is an elegant solution that might be of help to you. Assumptions: A1 - D3 has the data A5 - has the value that you want to solve for Steps 1. Goto Tools, Options. Under Calculation, check Iteration, maximum...

Excel: finding correct end?, ogilvy, marshall

ogilvy, marshall, formula bar: The trick is to delete entire unused rows and columns and then save the workbook. Assume you data ends on row 100 and excel thinks the last cell is on row 1000. An easy way to do this is to go to the name box in the left of the formula bar and enter ...

Excel: finding data, cell b2, hi thanks

cell b2, hi thanks, number 6: For instance, let s say the number 6 is somewhere on row 1, and you want to know which column it s in. In cell B2, use the formula: =MATCH(6,1:1,0) If the formula returns a 10, it means that the 6 was found in column #10 (i.e., J). If it returns #N/A,...

Excel: finding degrees, atan2, arctangent

atan2, arctangent, d16: Hi Dave, If the goal is simply to get the arctangent of C16/G16 without regard to quadrant, probably the easiest way is =DEGREES(ATAN(C16/G16)) which is almost exactly what you are doing. But if for example C16 represents the sine of an angle and...

Excel: finding duplicate rows and deleting both in a worksheet, colorindex, loop through

colorindex, loop through, basic knowledge: Just replace your comparison with another loop Change the 23 to as many columns as you need to check Sub comparesheets() Dim bRowsMatch as Boolean x = 1 y = x + 1 Do While Cells(x, 1).Value Do While Cells(y, 1).Value Start off...

Excel: About finding duplicates in row B77-B132, parent cells, base font

parent cells, base font, rng: Jawad, The reason is that the first part of the code that you have added leads to subroutine being exited when there is nothing in D77-D132. You should do the following. Move your code Dim rng As Range, cell As Range On Error Resume Next Set rng...

Excel: finding first, last non-zero cells in a list, array formula, array formulas

array formula, array formulas, zero values: Try these =INDIRECT( A & MIN(IF(A1:A20 0,ROW(A1:A20)))) gives the first non 0 number. This is Array formula entered with Ctrl+Shft+Enter, not just Enter. The A in quoation marks is the column of original numbers, change as required. The A1:A20 is...

Excel: finding+listing numbers in arnage of numbers, autofilter, excel spreadsheet

autofilter, excel spreadsheet, arnage: select the column with the list of numbers and do Data= Filter= Autofilter in the dropdown that appears, select custom then put is Greater than or eqaul to 70 click AND is Less than or equal to 71 No only the rows that contain the numbers between...

Excel: finding and moving same numbers in two rows, master inventory, isnumber

master inventory, isnumber, inventory count: Shannon: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/inventory.xls Columns A and B have the master inventory records you are working on updating. Columns F through G is where you enter the inventory item number...

Excel: finding text in a cell, run time error, subscript out of range

run time error, subscript out of range, run time error 91: I corrected the code for you - sorry for the typo. I guess if I hadn t taken so much time trying to explain the situation to you, I might not have made it. Sorry you couldn t understand it. As long as the TestNumber is found, your code should not cause...

Excel: finding the value based on, cell b2, punctuation characters

cell b2, punctuation characters, sheet names: Hi Manisha, To limit the column A cells to specific values, you need to first create a list of those values. List the project types in (for example) cells M1:M9. Now select the column A cells to be used and then select Validation from the Data menu. ...

Excel: Getting first cell address after custom filter, vba macros, custom filter

vba macros, custom filter, row numbers: Hello Jim, I assume you mean an Autofilter (Data= Filter= Autofilter) Sub FillABCD() Dim rng As Range, rng1 As Range Set rng = ActiveSheet.AutoFilter.Range Set rng = Intersect(rng.EntireRow, Columns(13)) Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count...

Excel: first and last nonzero values, nonzero values, array formula

nonzero values, array formula, array formulas: =index(A1:A16,Small(if(B1:B16 0,row(B1:B16)),1),1) =index(A1:A16,Large(if(B1:B16 0,row(B1:B16)),1),1) Each formula should be entered with Ctrl+Shift+Enter (hold down Ctrl and Shift, then hit Enter while holding them down) rather than just using...

Excel: How to fix a clock .exe in the form., excel ontime, karl peterson

excel ontime, karl peterson, previous question: I doubt there is any way to do that. If you want a clock to tick, you will have to get a third party control that is compatible with a useform, or you will need to write the code yourself. Karl Peterson has some sample code for doing timers: http://vb.mvps.org/samples/project.asp?id=TimerObj...

Excel: Getting fixed field text out of excel, lotus 123, payroll software

lotus 123, payroll software, dell desktop: Hi, Doug, There is more than one possible answer to this, and they depend upon the layout of the data. Can you give me a more detailed example of what the data looks like now, and how you d like it to end up? I wasn t sure if the example you gave in...

Excel: flickering of command button, mousemove event, blank worksheet

mousemove event, blank worksheet, command button: Hi Arnie, This seems to be an elusive problem. I have resolved several issues with computers and it always comes down to the process of elimination. So I would do things to test and eliminate to start narrowing it down to the actual problem. So here...

Excel: flipping columns of numbers, sort data, excel 2002

sort data, excel 2002: EASIEST way I can think of is to create a temporary column which contains (if say you have 100 numbers) 100 at the top and 99 in the next cell etc - you can auto-fill down from here. Now simply do a Data Sort (data menu, then sort) and sort on that new column...

Excel: flipping columns and rows, horizontal axis, vertical axis

horizontal axis, vertical axis, ivalue: Brian, here you go from David s site: ReversI() can be used to reverse the order of items in a row, column, or range. It can be used to reverse itself. Applied to a SINGLE ROW, the macro will flip about a vertical axis, or a horizontal axis for...

Excel: follow up question on file naming, window pop, ogilvy

window pop, ogilvy, job: Jim, based solely on limited information provided: If the workbook already has that name, then there is no reason to us Saveas ActiveWorkbook.Save does the job Otherwise On Error Resume Next kill xxx On error goto 0 Activeworkbook.SaveAs...

Excel: follow-up question re: multiple ranges & criteria in formulas, east aurora ny, douglas m smith

east aurora ny, douglas m smith, array formula: Hi Joe, In some cases, formulas like that will work without the zeroes. However, without the zeroes, the result returned is FALSE and that can have unintended consequences. Example: If you are averaging the results in the array, and all tests result in...

Excel: too many follow ups, cell c2, cell a2

cell c2, cell a2, follow ups: No, for that one you can use the formula on the first post, but instead of using B2, using C2. =(C2-$A2)/$A2 On the cell C2 you have the value 19, on cell A2 the value 4, and the formula will give you the % increase between the two values. If you want to...

Excel: IF fomulas, column c, summation

column c, summation, spreadsheet: Rows are shown/hidden either manually, VBA, or Filtering. In your case, I recommend filtering, but you haven t given me enough information. You want only row 12 to be hidden if something is in C12? IF anything is in C12? Or if anything is in column C you want...

Excel: forced formatting, value application, blank worksheet

value application, blank worksheet, true range: Hi Rodney, I m glad to hear I made the correct assumption on the borders problem. The way your code is right now (assuming you added my code in place of the old in the CheckBox3_Click procedure), it runs in its entirety whenever you click on the checkbox,...

Excel: forecast function, axis values, leap year

axis values, leap year, scatter chart: Hi Clare, Since your X values are months AND must be numerical, you have a number of choices: 1. you could use integer month numbers, starting at a certain date. For example month 1 could be Jan 2004, month 2 Feb 2004, ... month 12 Dec 2004, month 13...

Excel: form stops execution, developing computer, excel macro

developing computer, excel macro, excel 2002: Hi Marcos, I ran your code under WinXP and Excel 2002 without problem. Without being able to duplicate your problem it is very difficult for me to debug. So I will ask a few questions that may help you in debugging at your end. 1. you mentioned two commandbuttons...

Excel: format change, macro virus, return key

macro virus, return key, cheers: Hi Mike how are you? I am sorry but I do not know what is going on in your computer. You can, for instance, check the type of file you are opening when you select File - Open. Or you can check the tools-options. But it is difficult to advise you remotely...

Excel: format change, pdf file, conversion

pdf file, conversion, search engine: Assuming you are able to use the text select tool in Acrobat reader, you select the text and copy and paste it into an Excel document. There MAY be other tools out on the web to do the conversion - a search at any search engine would help here possibly, but...

Excel: format excel column, fancy programming, ebay

fancy programming, ebay, coin catalog: I understand the problem...used to sell coins on eBay. :) As soon as you enter a letter, Excel sees it as text, and it always sorts text below numbers. If it were me, I would simply put the letter in another column. Without some fancy programming, I...

Excel: format masks, proper case, access databases

proper case, access databases, variable length: I m in the UK so know nothing about zip and phone code formats, BUT in the UK we would run into problems if we tried to prescribe either of these as dialing codes can be 4 or five digits long, and the number can be of variable length - as can postal codes....

Excel: format problems, chemical equations, i beam

chemical equations, i beam, subscripts: At face value, it sounds as though it s the TOOLS menu, then options, then the EDIT tab - this needs to be set to having a check against edit directly in cell. Hopefully choosing that option will fix the problem, but do let me know if I can help further...

Excel: format tablature, exact sizes, aidan

exact sizes, aidan, boxes: Difficult to answer - and it s also not clear why this has been done in Excel (from the information given, I would have laid out this form using tables in word, set to exact sizes and probably using form fields). I m not sure HOW you have laid out this document...

Excel: format text in MsgBox, vba commands, error indicators

vba commands, error indicators, msgbox: Wayne, You can t format a string in a msgbox using built in VBA commands. I am not aware of anyway to ignore errors en masse. However, this may be a user level setting - in other words, it may only pertain to your environment and not affect other...

Excel: How to do this format, hh mm ss, chip pearson

hh mm ss, chip pearson, math formula: Jothiswaran, If you want to work with time values, then you have to live with the way Excel stores time. See Chip Pearson s page on how time/dates are stored. http://www.cpearson.com/excel/datetime.htm Times are just an integral part of dates. There...

Excel: formating data into 2 columns on one page, consecutive rows, page question

consecutive rows, page question, column c: In this case, I would take the approach of a separate sheet, with formulas bringing the data. Supposing the worksheet name is Sheet1, the formulas to bring the first column are: Column A: =INDIRECT( Sheet1!A &ROW()+INT((ROW()-1)/64)*64) Column B: =INDIRECT(...

Excel: formatting, bold lettering, format button

bold lettering, format button, b7: Conditional formatting only acts on the contents of the entire cell - so if the Name, Count, and Current year are substrings within cell B7, conditional formatting would not work (plus, you can t put a fill behind substring anyway) - so I assume you mean the...

Excel: formatting, bold lettering, b7

bold lettering, b7, cells: First, I m not sure if name, county and current year in cell b7 is ALL that exists in B7. You can t conditionally format SOME of the text in a cell. Anyway...I d have to know what the data in the cells looks like to be sure about the formula and why yours...

Excel: formatting, ogilvy, security settings

ogilvy, security settings, vba: Matthew To the best of my knowledge, this is not possible using the built in functions of Excel in versions xl2003 and earlier. There are not built in functions that will read the color of a cell. You could build a function with VBA and get it to do...

Excel: Why is my formatting not affecting all rows, excel spreadsheet, allexperts

excel spreadsheet, allexperts, formatting: Esther, I can t really diagnose this without seeing the file. If you like, you can send it to me at kawai54@excite.com. Once you ve sent it, write me back here at AllExperts to tell me so. You should know that I have no experience at all with queries and...

Excel: Why is my formatting not affecting all selected rows, douglas m smith, excel spreadsheet

douglas m smith, excel spreadsheet, brainbench: Hi Esther, Numbers coming in as text is a common issue with external data plus dates can come in all kinds of unusual formats. You mentioned the Text to Column Wizard. That s what I normally use to clean up foreign date formats and numbers as text....

Excel: formatting cell borders, run time error, cell borders

run time error, cell borders, selection range: You don t really need to select A19 at all. I commented out the lines you dont need. Try copy-paste from below: Range( A19 ).Select If ActiveCell.Value = Then Range( a1015 ).Select Selection.End(xlUp).Select ActiveCell.Offset(0,...

Excel: formatting a cell/cells, alignment tab, format cells

alignment tab, format cells, cell menu: Unless you have specified that the cell is to WRAP TEXT from the format cell menu you cannot do it - except POSSIBLY by VBA. However, if you want this to apply to all cells, why not click in the square above row 1 and to the left of column A,which selects...

Excel: formatting cell comments, windows control panel, default color

windows control panel, default color, joe joe: Joe, The comments aren t exactly user friendly either. However, if you change one comment, you can then simply select each one after that and press F4 to repeat the formatting. Even VBA code isn t very helpful in this instance. You can certainly access...

Excel: formatting a cell, conditional formatting, changing colors

conditional formatting, changing colors, sheet tab: The segmetns of code, from the previous answer, must exist in all spreadsheets that use the flashing text. My first guess is that the code isn t quite where it is supposed to be in the spreadsheet where it isn t working (one must be in a sheet module...

Excel: formatting cells to be a certain length, cell c3, format cells

cell c3, format cells, value case: Phyllis, I hope you can handle some VBA code, cause that s by far the simplest way to do this. From the top: With the file open, press Alt F11. That will open the Visual Basic Editor. Right click the name of the project that corresponds to the file you...

Excel: formatting cells, running office, step 3

running office, step 3, large scale: Hey Jercel, Are the dates all in one column? If so, follow these steps: -Select the entire column by clicking on the letter at the top -On the top menu, select Data, then Text to Columns -Press Next twice, until you are at Step 3 -Under Column Date...

Excel: formatting a chart, exact placement, tick

exact placement, tick, axis: You should be able to simply select each axis in turn and use Format/Axis. The patterns tab has choices for major tick mark type, Minor tick mark type, and tick mark labels. If this doesn t help, please email me a sample chart to bobumlas@yahoo.co...

Excel: formatting comments, time joe, default font

time joe, default font, readability: As far as I know, it isn t possible to change the default look and feel of the comment itself - from my research, it doesn t look as though you can even do this with a macro - however, I am currently unable to check this on XP (only earlier versions due to...

Excel: formatting for currency change, currency symbol, currency change

currency symbol, currency change, drop down list: I don t know to change the format of a cell based on the value entered in another cell. If this is for display purposes only I have two suggestions: 1) simply have your currency symbol pull-down box in the cell before the sum, and you can put a single...

Excel: formatting currency change, currency symbol, currency change

currency symbol, currency change, conditional formatting: Shannon You may consider the condiional formatting feature, whereby the format of the cell will change based on the selected currency. Go to toolbar/format/conditional formatting after poutting in one condition, you can click the Add box at the bottom...

Excel: formatting for currency changes, currency changes, currency symbol

currency changes, currency symbol, concatenate: I have uploaded a sample spreadsheet to the following location: http://home.swbell.net/nate-sus/excel/currency.xls I used a combination of INDEX() to look up the appropriate symbol, and CONCATENATE() to tack it on to the end of the total in column A....

Excel: formatting in Excell cells in time, billing program, decimal number

billing program, decimal number, decimals: As long as 7:45 ACTUALLY needs to be 7.75 I m fine!!! Excel stores dates and times as numbers, and time is the DECIMAL part of a day - so 12 noon is 0.5 - SO to convert to a decimal, simply multiply the time by 24 - obviously format the cells to the correct...

Excel: formatting in excel., comma separated list, vba code

comma separated list, vba code, ogilvy: Jimmy, Every cell would be a huge number of cells -- perhaps you could be more specific. If you could send me a sample workbook, I would be able to write some specific VBA code for you (unless you were asking how to do it by hand). Right now I would be...

Excel: formatting and finding the last cell, dear bill, active file

dear bill, active file, thanks for your help: Yes. The code I provided assumes that the last row would come from column A from the active sheet and then from the active workbook. You did not tell me otherwise. So, if the quote file is the active file BEFORE the code I provided is run then last row...

Excel: formatting issue, format cells, ascii format

format cells, ascii format, column c: Tyler, Hope you are doing well. There are two issues a) Why does formatting the cell as yyyymmdd not work? The reason is that formatting does not change the data, only how it shows up. The way Excel stores dates (when shown as a number) is the difference...

Excel: formatting subtotal cells, detail level, own time

detail level, own time, macros: Sorry. I don t write macros for people on here. If I did then that is all I would be doing. And, as you know, I and the other volunteers, do this on our free time. On the surface, the macro seems fairly simple but it is a bit more involved than what is...

Excel: formatting and toolbox, douglas m smith, editor tools

douglas m smith, editor tools, brainbench: Hi Cindy, Hmmm, that s very strange. The only thing I can think of off the top of my head is that it s a font aspect ratio quirk. This might run a character or two over but would likely print correctly. This could also be affected by the view magnification...

Excel: formatting, sheet tab, target

sheet tab, target: Nothing builtin - right-click the sheet tab, select view code, enter this: Dim oldrow As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Rows(oldrow.Row).Interior.ColorIndex = 0 Rows(Target.Row).Interior.ColorIndex...

Excel: forms, run time error, run time error 1004

run time error, run time error 1004, excel2003: Ken, Without knowing which line of code caused the error, it would be hard to be specific. I would say that error means one of the object(s) you referenced in your code does not exist. I would recommend putting your Update_New code in a general module...

Excel: forms, userforms

userforms: Ken, It sounds like the first form is being Unloaded when opening the new form. If you can hide the form rather than unloading it, that should keep the data. For example, I created two userforms. On the first userform there is a button that hides the...

Excel: forms, caption property, control toolbox

caption property, control toolbox, properties button: Hi Rodney, No, this question was not duplicated, but I still owe you an apology. Please forgive me for my slow response. I have been out of town on vacation all week, and found today when I returned that I had mistakenly entered the month for my vacation...

Excel: formula, f1000, d1000

f1000, d1000, text values: 1) =SUMPRODUCT((D7:D1000 0)*(E7:E1000)) 2) (This assumes the leading zeroes are actually there, not just formatted to be there -- that is, they are text values; the 00 shows in the formula bar: =SUMPRODUCT((LEFT(A7:A1000,2)= 00 )*(F7:F1000= comp )*(E7:E1000))...

Excel: formula, detailed explanation, ogilvy

detailed explanation, ogilvy, percentages: Why would a lower percentage receive a larger portion. This sounds like a disincentive. You haven t said how many winners or what the rules are to divide up the pot. assume the winners had percentages A 20% B 30% C 50% How would...

Excel: formula, douglas m smith, font colors

douglas m smith, font colors, brainbench: Hi Philip, No, you can t read font properties directly in Excel. It would require a macro to read font colors. However, if there is some numerical criteria for coloring the fonts green, then you can read this with the COUNTIF function. Example: numbers...

Excel: formula, countif, green hope

countif, green hope, critera: P In the count functionf excel cannot discern the font color as a criteria, however excel can use the same criteria that are being used to make the font green as a critera for the count. In other words if all numbers from 0 to 100 are green, you...

Excel: formula, bmis, xf

bmis, xf, bmi: John, Say your BMIs were in column a starting in A1. Somewhere on the sheet, say in D1:E7, type in the following table: 0 Underweight 18.5 Underweight 18.6 Normal 24.9 Normal 25 Overweight 30 Overweight 30.1 Obese ...

Excel: formula, h5, bmi

h5, bmi, h2: Assuming cell F2 is the value you want to evaluate: =LOOKUP(F2,{0,18.5,24.9,30},{ under , normal , over , obese }) Of course, range references could also be substituted for the hardcoded arrays: =LOOKUP(F2,G2:G5,H2:H5) Regards, Aaron ...

Excel: formula, cell reference, bmi

cell reference, bmi: There are various ways of doing this - I would PROBABLY use a lookup table, but to use your method you need =IF(AND(BMI 24.9,BMI 30), Overweight ,IF(AND(BMI =18.5,BMI =24.9), Normal ,IF(BMI 29.9, Obese , Underweight ))) replace BMI with the cell reference...

Excel: If/or formula, 6 cells, concatenate

6 cells, concatenate, excel 97: Hi Linda, You could use this function in the cell, where you did like to put the result =IF(ISERR(FIND( n ,CONCATENATE(F4,E4,D4,C4),1)),SUM(C4:F4), N ), where the cells F4, E4, D4 and C4 are those cells that contains the values. Regards, Amal Raj ...

Excel: formula??, c15, grand total

c15, grand total, macleod: Kathryn, I can t make the formula for you, but I can walk you though it. In the cell you want the Grand Total, just type the = sign, then click on the first sheet with a total, select the cell and then type a + sign, keep selecting each sheet and cell...

Excel: formula, tim tim, c1

tim tim, c1, best wishes: Tim, this will take 2 formulas for each row. It doesn t matter where you put them - any worksheet will do. The formula currently uses Sheet1 and Sheet2, so if you have other names, then replace those. Here ya go: =IF(AND(Sheet2!A1=Sheet1!A1,Sheet1!B1=Sheet2!B1,Sheet2!C1=Sheet1!C1),Sheet1!L1-Sheet2!L1,...

Excel: formula, cell d2, cell c2

cell d2, cell c2, ruth ruth: Ruth I m not sure what the training requirement are but here are a couple of formulas that may work for you. +IF(B2= A ,+C2+365,+IF(B2= B ,+C2+365,+IF(B2= C ,+C2+1095))) This formula is in cell D2 where column D is the date due column. It says if B2...

Excel: formula, douglas m smith, slit widths

douglas m smith, slit widths, solver tools: Hi Jane, You can use Solver to come up with solutions like this. I haven t used it much though. To get instructions on Solver (Tools menu), search for Solver in Excel Help. I got solutions of 4, 3, 2 for exactly 52 inches and 6, 6, 0 for exactly 60 inches....

Excel: formula, ruth ruth, 3 years

ruth ruth, 3 years, cells: Ruth This can be done with an if statement in the needed cells, the only problem I can forsee is that of forcing the if statement to look at either a or b and not involve c as you can answer that with the a false answer, unless you have other values in...

Excel: formula, personal computer tutor, cell b1

personal computer tutor, cell b1, cell a1: Hi Karen, You can us an IF statement such as. =IF(A1=60,30, ) the formula would need to be placed in cell B1. the first part of the IF statement is the condition IF A1=60 after the comma the second part is the what you want placed in cell B1 if...

Excel: formula, cell a1, exact spot

cell a1, exact spot, excel 2000: Presuming your number with the * is in cell A1, you would use this formula: =SUBSTITUTE(A1, * , DG ) Adjust the A1 reference as needed for your situation. Please let me know if this doesn t take care of your question! NOTES: How the formula above...

Excel: formula, microsoft excel 97, number signs

microsoft excel 97, number signs, huge list: Linda, You can do this a couple of different ways. The easiest way is to use the find replace function find all # and replace with or essentially nothing [CRTL+H]. Or you can use this formula where A1 is the cell that your trying to strip out the...

Excel: formula, array formula, seperate cells

array formula, seperate cells, excel microsoft: Setup: A1 = 8 (normal day) B1 = 10 (overtime day) C1 = 10 (overtime day) D1 = 6 (short day) E1 = 8 (normal day) I wasn t sure how your overtime logic would work, so I present two scenarios: === LOGIC ALTERNATIVE #1 === If you only want the...

Excel: formula, cell a2, a3

cell a2, a3, a5: Not sure what you want to do if someone enters something in A2 when there s something in A3 or vice versa. Nor how is this related to A5? You could do something like this in A5: =IF(COUNT(A2:A3) 1, PLEASE ENTER A2 OR A3 NOT BOTH ,SUM(A1:A4)) but I m not...

Excel: IF formula, tax bands, missing something

tax bands, missing something, many thanks: Karen When creating an If formula like this one it is best to work your way up through the values as down as you have done here. Have a look at this version and you will see what I mean. I have tested this one and it should be what you need. (I used...

Excel: formula, array formula, shift keys

array formula, shift keys, a10: You need an array formula. Assuming your data is in A1:A10 then type the formula below and DO NOT PRESS ENTER. Instead press and hold down the CONTROL and SHIFT keys and then press ENTER. EXCEL will add { } around the formula =AVERAGE(IF(A1:A10=0,...

Excel: formula, array formula, period subject

array formula, period subject, f6: Rodney: Would the following work on your formula that takes an average of the 6 periods?: =AVERAGE(IF(periodtotals 0,periodtotals)) * The above is also an array formula This is essentially an AVERAGEIF function...it only includes a number in an...

Excel: formula, array formula, formula index

array formula, formula index, blank entries: Hi, Dane, Let s say that Date is in column A and Main is in column B. The formula that you gave me above (when entered as an array formula) will look up the bottom-most date in column A. To recap, here s the logic in the formula, going from the inside...

Excel: formula, array formula, array formulas

array formula, array formulas, cell c1: Hi John, See if this helps you. In cells A1:A5 type in the cooresponding numbers 2,5,2,8,2 repectively. In cells B1:B5 type in the cooresponding words Jan,Feb,Jan,Mar,Apr In cell E1 type in the following formula =SUM((A1:A5=C1)*(B1:B5=D1)) ...

Excel: formula, douglas m smith, minus signs

douglas m smith, minus signs, formula usa: Hi Nancy, If I read your question correctly, the third column reult would be: 50% under. Correct? The formula would be: =TEXT(ABS((A1-B1)/A1), 0% )&IF(A1 B1, under , over ) ((A1-B1)/A1) calculates the percentage. ABS gets rid of any minus signs....

Excel: formula, blank cell, log database

blank cell, log database, denver los angeles: John: It sounds like you want the following: IF value in column F = City Name Specified AND value in same row, colum M is not a blank cell THEN Include that row in a count As such, if I had the following data in F and M: New York 59 ...

Excel: formula, blank cells, m1000

blank cells, m1000, e mail: John: The formula, =SUMPRODUCT((E1:E1000= New York )*(M1:M1000 )) Should work even if there are blank cells. Typically, this formula will return #N/A if the arrays are not the same size. For example, array1 is E1:E1000...or 1000 rows. array2...

Excel: formula, left mouse button, open parenthesis

left mouse button, open parenthesis, sheet tab: Hi Nellise, Start on the worksheet with the employee totals where you would like the formula to reside. Select the cell you would like the formual in and start by typing =SUM( Once you get to the open parenthesis displayed above you want to use your...

Excel: formula, douglas m smith, cell c1

douglas m smith, cell c1, brainbench: Hi Antonio, To get the name cells to change with Conditional Formatting, change Cell Value Is to Formula Is . In the edit box, enter a formula that will evaluate as true for the same condition in the days cell. Example: if the days result cell is B1,...

Excel: formula, spread sheet, vacation days

spread sheet, vacation days, 10s: To sum an entire row, you can use a formula such as this: =SUM(10:10) The above will sum all numbers on row 10. If you want a formula to sum all row 10s on multiple sheets, you could use the following: =SUM(Sheet1!10:10,Sheet2!10:10,Sheet3!10:10)...

Excel: formula, spreadsheet, cells

spreadsheet, cells, yahoo: Hi Dede, I am not sure what you are trying to count. Locations of where the spreadsheet is saved? or location of cells? Not sure? Can you send me an example or explain further? You can send an example workbook to cloverken@yahoo.com Thanks Ke...

Excel: =IF formula, dcount, exceltip

dcount, exceltip, days between two dates: Hi Heather, You can do this with a user-defined function. Select and copy the following function. Function GetWorkDays(StartDate As Long, EndDate As Long) As Long returns the count of days between StartDate - EndDate minus Saturdays and Sundays...

Excel: Is there a formula for this?, simplicity sake, e numbers

simplicity sake, e numbers, question question: Matt If your numbers that may add up to 123.45 are randomly interspersed within your data then it will be difficult to write a formula that will encompass all possibilities. I.E. any combination of numbers in a long list of numbers may leave millions of...

Excel: formula, supplier deliveries, supplier names

supplier deliveries, supplier names, countif: One way is to do a Data, Filter, Advanced Filter, and copy all unique combinations of Supplier and Order Numbers to a different location, then just count the occurances of each supplier. If you want to do it formulaically, say for example your supplier...

Excel: IF formula

Hi Betsy, Sorry it took a while. I finally had to go to www.mrexcel.com to get help myself. The following formula will detect if any cell in the range C3:C18 has a number between .014 and .025 and will put the word Continue in the cell if it does, otherwise...

Excel: How to do this formula, cell a2, b1

cell a2, b1, b3: There are many ways to do it, but I think the easiest is to put in Cell A2 =vlookup(a1,b1:c4,2,false) which would do a lookup for you - it will return an error if no match, but as you didn t give me an ELSE condition it is difficult to work out what...

Excel: formula, douglas m smith, component values

douglas m smith, component values, sum function: Hi Debbie, Yes, this can be done, sort of. Select the A3:A8 reference in the formula and press the F9 function key. The formula will change to: =SUM({456;123;789;654;97;818}) The F9 key press evaluated the selected range and returned the component...

Excel: formula, conditional formatting, formatting toolbar

conditional formatting, formatting toolbar, current date: Claudia You can use the combination of conditional formatting and the NOW formula. First--in the cell you want highlighted put in the +NOW() This will calculate current date every time you open the worksheet, or when you ask excel to recalculate....

Excel: formula, b1, multiplication

b1, multiplication, cells: Fahad, The answer would depend on what context you would want to apply the multiplication. If you wanted to multiply A1 by B1, then by C1, for instance 2, 4 and 3 to get 24, then it would be =A1*B1*C1 or =PRODUCT(A1,B1,C1) If you wanted to...

Excel: formula, douglas m smith, currency code

douglas m smith, currency code, cell a1: Hi Sudha, If the currency code is in cell A1, the exchange rate is in B1 and the sales value is in C1, the formula would be: =C1*IF(A1= SGD ,B1,1) The IF statement checks the currency code. If it is SGD, then the exchange rate amount is retrieved...

Excel: formula, array formula, curly brackets

array formula, curly brackets, thanks in advance: If I understand the question corectly, then you could use =SUM(ROW(INDIRECT( 1: &V1)))*X1 which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets),...

Excel: formula, cell a1, natural numbers

cell a1, natural numbers, thanks in advance: Ricardo: According to Algebra, the sum of all natural numbers leading up to a specific number is as follows: =V*(V+1)/2 Thus, if V is in cell A1...and X is in cell B1, then this formula will provide the answer you are looking for: =A1*(A1+1)/2*B1...

Excel: formula, greatest common denominator, cells

greatest common denominator, cells: Ricardo: I do not understand the question. Can you give me a few examples of before and after? Just guessing...I might start with this in cells A1:D1... 2 2 4 8 I presume you want to find the greatest common denominator among these numbers. =GCD(A1:D1)...

Excel: formula, lumber mill, allexperts

lumber mill, allexperts, maximum value: I don t think this is going to be possible to do JUST with a formula, but VBA could certainly be used to produce the results - I m pretty sure I ve seen a very similar question on AllExperts before, but of course I cannot track it down now - can I just ask...

Excel: "If" formula, chip pearson, e41

chip pearson, e41, d38: Jonah, Yes there is a limit. You can only nest 7 levels deep. the solution here is to split the alternatives into two groups. Make the initial decision if it is AIG or Zurich. Then have separate nesting under each condition: =IF(LEFT(G38,3)= AIG...

Excel: formula, input message, c10

input message, c10, aidan: If you wish to restrict input in a particular cell, you will need to use the data menu, then the validation option - in this instance you can use the decimal option - it should be self evident once you get this far what you need to enter in the boxes. You...

Excel: formula, b1 and b2, vlookup function

b1 and b2, vlookup function, currency code: I think I understand the concept of what you want but I did not quite follow the question and how your worksheet is set up. Regardless, it seems you need to use a VLOOKUP function. Assuming the codes, USD and SGD are in A1 and A2 and their respective rates...

Excel: formula, destination cell, combo box

destination cell, combo box, index formula: Hi I know the number vs text is initially confusing. The number represents the nth item in the list. In the following example, the text list is in A1:A4 and the cell link value of 3 is in cell C1. This index formula would then return the text Large...

Excel: formula, conditional formatting, formula g1

conditional formatting, formula g1, j1: I m not quite sure what you want to do, but I ll give two options for what I think you are getting at 1 - use data filter autofilter - this will allow you to show ONLY those cases that have a 1 in the cell 2 - use conditional formatting to highlight...

Excel: If then formula - How can this be accomplished??, array formula, array formulas

array formula, array formulas, logical number: Please refer to this spreadsheet to see the formulas in action. http://home.swbell.net/nate-sus/excel/prov.xls The astericks are there ONLY to highlight where my offset is for in case you want to add additional header rows. Currently my sapmple spreadsheet...

Excel: formula so you can get the answer matching 2 different citeria from different columns within a spreadsheet, citeria, pivot table

citeria, pivot table, asnwer: Hi Ian, I hope you are well. The easiest way to do this is by using pivot tables. 0th Let s assume that your data is in A1:B13 1st Select A1:B13 2nd DATA - PIVOT TABLE 3rd Select MICROSOFT EXCEL LIST OR DATABASE and PIVOT TABLE Press NEXT 4th Confirm...

Excel: formula bar, left mouse button, three dots

left mouse button, three dots, curser: Shirley - Probably happened with a flick of the mouse. First, check the sides to see if it has been moved there. And check to see if it has been moved into a different position on top. If you find it in any of these places, you want to get hold of it...

Excel: formula for calculating a date, datedif, daily basis

datedif, daily basis, dionne: Dionne, Basically, you just subtract the smaller from the larger or the earlier from the later and format your answer as [d] using a custom number format. Take a look at this page http://www.cpearson.com/Excel/datetime.htm#AddingDates it should tell...

Excel: formula from two cells, eldon, c2

eldon, c2, kilograms: Hi! From your question it is not very clear as to what kind of function you want in cell C3. However, try using the OR() function in C3 to take either value from C1 or C2. Something like this... =OR(C1,C2) Let me know if you need any more assistance......

Excel: formula from two cells, newtons, d1

newtons, d1, kilograms: Hello again Sy, I recommend using a formula like: =IF(D1= ,C1*0.4535924,D1) which always yields a value in kg. This formula yields the kg value in D1 unless D1 is empty, in which case it uses the value in C1 and converts it to kg. If you want...

Excel: formula from two cells, cell c2, cell c3

cell c2, cell c3, cell c1: Hi Sy, Yes you can. You can use the IF Function for this. I am not sure if you have a calculation for converting the lbs or kgs, but you can do that also if you need to. Here is a formula that says if cell C1 has a number greater than zero then place...

Excel: formula from two cells, excel help, corporate decision

excel help, corporate decision, exact solution: Yes you can. The answer is to in corporate decision making in Excel. To accoplish this, you use the IF statement. Just type IF in Excel help and it will give you the syntax for IF. In your case, it might be something in the following lines: You have...

Excel: one formula for a column of random dates, douglas m smith, random dates

douglas m smith, random dates, integer numbers: Hi Jim, There are multiple ways of doing this. Dates are really just integer numbers (example: today, August 1, 2006 is 38930) so you can perform operations on them like any other number. To add a year to a date, type 365 in a cell. Now copy that cell...

Excel: formula to compare and enter new data, target copy, target value

target copy, target value, master cells: This should get you started. Assumes master sheet is named Master , and the emp#s are in col A and the relevant data is 10 columns wide: Right-click the Excel LOGO near the file menu, select view code, enter this: Private Sub Workbook_SheetChange(ByVal...

Excel: formula for conditional format, conditional formatting, a9

conditional formatting, a9, weekday: no problem =OR(A9= Tuesday ,A9= Wednesday ,A9= Thursday ) UNLESS you mean that the weekday of A9 is Tuesday etc - in which case =OR(weekday(A9)=3,weekday(A9)=4,weekday(A9)=5) will do it - in either case, use format, conditional formatting, then...

Excel: formula's in conditional formatting, conditional formatting, cell a2

conditional formatting, cell a2, data validation: I can t see why you need VBA to do this, why not simply use Conditional Formatting? If you use VBA then you don t need to select any cells. Sub markCells() Dim rCl As Range Dim rCheck As Range With Sheets(1) Set rCheck = .Range(.Cells(2,...

Excel: formula to convert number to text, excel spreadsheet, currency

excel spreadsheet, currency, checks: Hey Carl, You can add a Custom Function to Excel to convert currency to text without using a lengthy IF statement. I found this site that details the steps: http://www.ozgrid.com/VBA/CurrencyToWords.htm Please let me know if you need any help setting...

Excel: formula to convert time to decimal time, cell b1, cell c1

cell b1, cell c1, cell a1: I m not sure I exactly understand your question. Based on the Subject, I m going to answer what I think your question is and also refer you to some helpful websites. You need to be sure you set the format in the cell that you want the total time shown...

Excel: formula for counting numbers repeated in a range of cells, array formula, countif

array formula, countif, formula index: In that case, I am afraid that it cannot be done in one cell. If possible, what I would suggest is to create a matrix with the potential values that you can encounter in the range vs. the sets of data that you are using (1-16, 2-17, etc), and there set a...

Excel: formula for counting numbers repeated in a range of cells, counting numbers, b50

counting numbers, b50, b17: Sahil I believe what you need is the COUNTIF function, with aformula such as =COUNTIF(A$1:A$16,A1) This formula asks excel to count the number of times the value in A1 occurs in the range A1 to A16. You question asks to count which number as...

Excel: formula for counting numbers repeated in a range of cells, counting numbers, b50

counting numbers, b50, b17: Example: if you want to display how many cells in A1:A16 have the value of 4, the formula is: =COUNTIF(A1:A16,4) If this doesn t solve your problem, then please reask the question being very clear and specific. It might help to create a simpler example...

Excel: Can this formula be created?, microsoft excel 2002, contract time

microsoft excel 2002, contract time, time cell: All of what you want is possible since EXCLE can do just about anything but to make it possible depends on how much time you want to invest to make it possible. I don t really understand what you want or are trying to do but I am fairly confident that a...

Excel: formula creation for repeated numbers., target cells, cell a1

target cells, cell a1, case holder: Hi Sahil Easiest done with a custom Function, and I am assuming from your example that input data is all integer. Go to VBA (Alt F11), Insert/Module, and paste the following into the code space: Public Function FindRepeats(Target As Range) Dim Holder(100)...

Excel: formula creation for repeated numbers., array formula, cell a1

array formula, cell a1, c17: a little bit complex. use this formula is C17 =CONCATENATE( 5 occurs , SUM(LEN(A1:A16)-LEN(SUBSTITUTE(A1:A16, 5 , ))), times ) this is an array formula. to enter this, press Ctrl+Shift+Enter. Drag and drop till you want it. you should be...

Excel: formula creation for repeated numbers., douglas m smith, cell a1

douglas m smith, cell a1, brainbench: Hi Sahil, The formula in cell C17 would be: =$C$16& occurs &COUNTIF(A1:A16,$C$16)& times where cell C16 holds the value you want to count. As you copy the formula down, the range reference will change to A2:A17, etc., but the $C$16 reference...

Excel: formula with criteria based on text or font color, inrange, isnumeric

inrange, isnumeric, rng: Hi Runna, You can only sum by text color with a user-defined function. There are two here. One to sum by Green Font. One to sum by Black Font. Select and copy both Functions. Function SumByBlackFont(InRange As Range) Dim Rng As Range ...

Excel: formula errors alert, option error, option window

option error, option window, vba code: The only way I can think of to prevent that behaviour in other people s computers is to add some VBA code that disables that option while opening the file. For example, this will use the Workbook Open event to disable the adjacent cells error: Private Sub...

Excel: formula in excel, stock list, melissa

stock list, melissa, cells: Melissa You could use an if statement such as =if(A1=1, 2 x 4inch fittings , ) this will only work if you have less than 7 options as a nested if statement can only have 7 ifs in it. If you need more options then I would suggest a database of some...

Excel: formula in excel?, cell d1, cell b2

cell d1, cell b2, letter of the alphabet: You ll need to set up a table somewhere that associates each letter with a number. Example: you could enter A in cell A1, B in cell A2, C in A3, etc through Z in cell A26. Then next to each letter put the numerical value you want it to have, e.g., 1 in cell...

Excel: formula excel 2003, excel count, spread sheet

excel count, spread sheet, keystroke: I don t need your file, Theo...or at least I don t think so. But, I will say, I d be changing those symbols to numbers or letters if I were you. Also, symbols are just formats. What happens if you change that particular column to, say, Times New Roman? If...

Excel: formula in excel, filter number, address number

filter number, address number, elaine: Elaine, I think this would require code to make it any faster than you are doing it right now. You could use code like this: Sub Elaine() Range( A65536 ).Select Selection.End(xlUp).Select Selection.Copy Range( C5 ).Select ActiveSheet.Paste...

Excel: The formula you gave me, digits, amount of time

digits, amount of time, wheel: I didn t keep the original. Your formula contains errors (can t enter it): =IF(ROW() 937,CHAR(INT((ROW()-1)/36) 97),CHAR(INT((ROW()-1)/36) 22))&CHAR(IF(IF(MOD(ROW(),36)=0,36,MOD(ROW(),36)) 96 122,IF(MOD(ROW(),36)=0,36,MOD(ROW(),36)) 21,IF(MOD(ROW(),36)=0,36,MOD(ROW(),36))...

Excel: formula help, open parenthesis, column tables

open parenthesis, column tables, steel column: Hi Rich, Yes the Open Parenthesis ( before the word offset and the close Parenthesis ) after the word false is what is throwing it off. So revise the formula to. =IF($C$38 =OFFSET( Steel Column Tables (2) !$F47, Steel (2) !$B$45,0), Steel Column...

Excel: formula help, douglas m smith, right parenthesis

douglas m smith, right parenthesis, column tables: Hi Rich, It looks like you have a right parenthesis in the wrong place. It s grabbing the true/false results and putting them inside the IF condition test, hence the error. This should fix it: =IF($C$38 =(offset( Steel Column Tables (2) !$F47, Steel...

Excel: Make a formula hyperlink into a permanent hyperlink, hyperlink address, yellow arrow

hyperlink address, yellow arrow, link option: Without the actual error message it s difficult to debug - I ran it here with no problems, but POSSIBLY including DIM CELL as the first line might resolve it? If it helps, my email is aidan.heritage@virgin.net and I m happy to check the file for you...

Excel: formula to increment cell values, douglas m smith, customer shipping

douglas m smith, customer shipping, quantity column: Hi Ashley, Writing a macro to this would be pretty tricky but I understand the situation correctly, there are simpler ways to warn you. Conditional Formatting can be used to change a cell s properties under certain conditions. Example: select cells...

Excel: formula: knowing tha data of a cell, douglas m smith, array formula

douglas m smith, array formula, index formula: Hi Patrick, Try this, if your dates are in cells C2:P2 and the entered numbers are in C3:P3: =INDEX($2:$2,1,MAX(IF(ISBLANK($C$3:$P$3),0,COLUMN($C$3:$P$3)))) However, instead of hitting Enter, hit Control-Shift-Enter to make it an array formula. If...

Excel: formula ? macro?, unknown number, k8

unknown number, k8, lars: To find the last row of data, using I8, as a reference, then the code below will find the last row of data in that column and use that to create a range of data from I8 to the bottom of the data and extend to columns to the right to account for J and K ...

Excel: formula needed, h7, d7

h7, d7: Leon Unfortunately, I may need some more info. Do you mean that if there is any single cell within the range H7:H3000 that is equal to comp shop and if any cell in the range B7:B3000 is equal to the value of B8, then you want the formula to return...

Excel: formula problem in excel, douglas m smith, cell a1

douglas m smith, cell a1, brainbench: Hi Kelly, Time values are actually values between zero and one. If you subtract a larger value (11pm = 0.95833) from a smaller value (7am = 0.29167), you will get a negative amount, in this case -0.66667 or -16 hours. To fix this, you need to test the...

Excel: formula query, alternative solution, page break

alternative solution, page break, autofil: Why do you need the blank lines? My GUESS is to force a page break? Are macros OK, and do you need to print EVERYONE or a selection of people? I m asking the questions as this question sounds like you have a solution that isn t working and want me to fix...

Excel: formula question, cell d1, cell c1

cell d1, cell c1, column c: I believe SUMIF is what you are looking for. The following is a sample formula that you may need to modify slightly (because you will likely have different spreadsheet names): =SUMIF(WorksheetA!E1:E1000,WorksheetB!C1,WorksheetA!I1:I1000) This formula...

Excel: formula question, array formula, n3

array formula, n3, zeros: Hi Dede, This is not an array formula and it will work for noncontiguous cells when you want to ignore the zeros in the averaging. It will only work for up to 30 different cells just add to the first and second part of the equation to add more cells. ...

Excel: formula question, array formula, example spreadsheet

array formula, example spreadsheet, social security number: This is a very interesting question. We need to use an array formula, but the MAX() function will not work in an array formula. Thus, I will have to use a variety of IF() functions to determine the maximum value (thus, the formula will appear very messy...

Excel: formula question, cell d2, daniel daniel

cell d2, daniel daniel, column title: Daniel: Please place this formula in cell D2: =INDEX($A$1:$C$1,MATCH(MAX(A2:C2),A2:C2,FALSE)) This formula would go in cell D3: =INDEX($A$1:$C$1,MATCH(MAX(A3:C3),A3:C3,FALSE)) Here is an explanation for the first row if you are curious: 1) MAX(A2:C2)...

Excel: formula question, cell a1, employee names

cell a1, employee names, sheet1: Example: On Sheet2, you have employee names in Col A, and Employee # in Col B. On Sheet1, you want to enter the employee # in cell A1 and have a formula in a different cell display the corresponding name. The formula to do this is: =INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0))...

Excel: formula question for excel, cell c5, filter number

cell c5, filter number, address number: u can see a count of the number of filtered rows in the COUNT option at bottom left of screen. as to getting this number in cell C5, ul need to write some VBA looping on the rows that are visible... something like this: for i = 1 to 1000 or whatever...

Excel: formula question, douglas m smith, zero result

douglas m smith, zero result, window options: Hi Joe, Yes, the are a couple of ways to do this, globally and locally. The global method will hide ALL zero values in the workbook. To set this, go to Tools / Options / View and then uncheck the Zero values box in Window Options section. You...

Excel: formula refering to another worksheet, vlookup function, mundane task

vlookup function, mundane task, sceme: VLOOKUP needs a column from the range referened from which the data being looked gets returned, like 1, or 2, or 3. It is the third argument. Check help for the syntax for the VLOOKUP function. Typically the referenced range (the 2nd argument) is at least...

Excel: formula - i can send an excel file, tim tim, occured

tim tim, occured, cells: Tim I m not clear on what your match needs to be Do you want to match a cell in column A with a cell in column B, or match Cells in A, B, and C (same row)with Cells in A, B, and C (same row) in another worksheet (I.E. a match only occurs when the...

Excel: formula to shade cell without conditional format, cell interior, target

cell interior, target, weekday: Hi Leon, You can use a macro in order to solve this problem. -Unlike standard macros which are installed in standard modules, Worksheet Events are installed with the worksheet by right-clicking on the sheettab, choose view code , and then paste in...

Excel: formula to sort items, boatload, sucess

boatload, sucess, yahoo: Hi Loren, Is there a chance you can send me an example workbook? I was a little confused by your question. If you can send me a workbook with some questions directly on there and then a line showing the result you would like to have that would really help...

Excel: formula for sum, odd numbers, a2

odd numbers, a2, e1: Santosh Rearrange this formula to suit your setup Assuming the cells are in range A1:E1... In cell A2, enter the formula: =IF(A1/2=INT(A1/2), EVEN , ODD ) then copy it to the range A2:E2. Then the formula: =COUNTIF(A2:E2, EVEN ) counts...

Excel: formula test for text within text/wildcard test, array formula, search operator

array formula, search operator, character position: Hello Jeff, =SUM(ISNUMBER(SEARCH( mars ,$O$31:$O$3007))*($H$31:$H$3007)) Array entered as you show should do the trick. Both SEARCH and FIND perform this function, but SEARCH is case INSENSITIVE while FIND is case SENSITIVE Each would return...

Excel: formula with text and numbers, agency names, pivot table

agency names, pivot table, column c: Terry The easist solution would be to use either a Pivot Table or use the Data Filter options to select your list. The pivot table would give you the better looking report though. Have a look at these links to see if either option is viable for your...

Excel: How to do this formula using macros, exact code, isempty

exact code, isempty, ogilvy: This is the code taken right out of a module Sub ABC() Dim v As Double v = 0 On Error Resume Next v = Range( Q1 ).Value / Range( R1 ).Value * 2 On Error GoTo 0 If v 0 Then ActiveCell.Value = v End If End Sub It worked fine for me no matter...

Excel: formula using if, yvette, b1

yvette, b1, a1: Yvette - It s almost an If statement, it s a SumIf statement. It looks like: =SUMIF(range of cells to evaluate,criteria,range to sum) So lets say you have 2 columns, column A has letters and column B has numbers. To sum only the numbers that are across...

Excel: formula values, cell references, programers

cell references, programers, dollar sign: Butch One of the nice things about excel is its ability to change the cell references as a formula is moved. This is called relative referencing. There are many many times that this is very useful. But there are also times, such as in your instance,...

Excel: formula for whole column in other columns cell, vlookup formula, cell b1

vlookup formula, cell b1, column c: [I corrected this response at noon PST 4/3/07] If you want values to appear to column B, then you need to enter formulas in column B to return the values you want. It seems that you want to put a formula in C1 that will make a value appear somewhere else;...

Excel: IF formula not working, allexpert, carneal

allexpert, carneal, value error: Hi Robert, correct formula =IF(AE3= ???? ,0,IF(AND(VALUE(AE3) =1000,VALUE(AE3) =YEAR(TODAY())),1,IF(VALUE(AE3) YEAR(TODAY()),2,9))) incorrect formula =IF(AE3= ???? ,0,IF(AND(VALUE(AE3) =1000),(VALUE(AE3) =YEAR(TODAY()),1,IF(VALUE(AE3)) YEAR(TODAY)),2,9)))...

Excel: "=" formula between worksheets, microsoft office 2000, using microsoft office 2000

microsoft office 2000, using microsoft office 2000, using microsoft office: Yes. This happens because the result of the calculation is zero. You can either select Tools, Options, View and uncheck Zero values. Or, you can modify the formula and have it check to see if data is there. So, =IF(Trim(Sheet2!A1)= , ,Sheet2!A1)....

Excel: formula, ascending order, excel 2000

ascending order, excel 2000, c2: DAN The formula looks like this: +VLOOKUP(A1,Sheet2!C2:D6,2) Where the part number you are looking up is in A1, and the range where you are looking up the part number is in sheet2 cells C2 thru D6 and the column the description is in is column 2 of...

Excel: formular for worksheet name or workbook name, book1, documents and settings

book1, documents and settings, c documents: The CELL formula is the way to go, but you just have to use some other formulas with it in order to single out just the filename or just the worsheet name: =CELL( filename ,A1) C:Documents and Settings headDesktop[Book1.xls]Sheet1 =MID(CELL( filename...

Excel: formulas, month today, jan jan

month today, jan jan, birth dates: Hi, Linda, Whenever you want a formula to return a blank if the dependent cell is blank, use the following structure. Let s say that you want to multiply A1 by 2 if it has an entry: =IF(A1= , ,A1*2) In other words, If A1 is blank, return blank....

Excel: formulas, conditional formatting, vlookup formula

conditional formatting, vlookup formula, vlookup function: Hi Theresa, The #N/A error message is generated by the VLOOKUP function under the following conditions (from Excel Help): 1) If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value....

Excel: formulas, cell a1, excel 2000

cell a1, excel 2000, toolbars: Hello, Dick, Make sure that you click in the cell before you try to enter any information. Whenever you type anything, your entry goes into the active cell. You can tell which cell is active in two ways: 1) It has a darker border around it. 2)...

Excel: formulas, cell c2, ogilvy

cell c2, ogilvy, d2: Hello Ruth, assume 1 Jan 05 is stored as a date in cell C2. In D2 put in =IF(AND(C2 ,B2 ),DATE(YEAR(C2)+IF(OR(B2= A ,B2= B ),1,IF(B2= C ,3,0)),MONTH(C2),DAY(C2)), ) Make sure D2 is formatted as Date =if(D2 ,if(today() =D2, Current...

Excel: "If - Then" formulas, cell b1, cell c1

cell b1, cell c1, c values: Good morning John - lets see if we can get you some formulas... For your first question, you will want to use the SUMIF function: =SUMIF($A:$A, A ,$B:$B) This formula says: every time there is an A in column A to add the value appearing immediately...

Excel: formulas, array formula, excel 2000

array formula, excel 2000, thanks for your help: Kristen, this should be interesting . the person is using Array formula - a rather advanced topic. I really think any decent Excel book should be helpful I think the reason the person has used array formula is because they need to use them - i doubt...

Excel: formulas - blinking or flashing the results, cell b10, colorindex

cell b10, colorindex, cell interior: Right-click the sheet tab, select View Code, paste this in: Private Sub Worksheet_Calculate() If Range( B8 ).Value = Then Blink B10 Else Range( B10 ).Interior.ColorIndex = 0 End If End Sub Private Sub Worksheet_Change(ByVal...

Excel: formulas for changing colors, changing colors, format button

changing colors, format button, numerical value: To do something like this you will want to use conditional formatting: Select the cell you want to format green and then click on format- conditional formatting. Input the options you want, in this case, Cell value is , greater than , 50 . Click...

Excel: formulas in excel, math error, formulas in excel

math error, formulas in excel, black arrow: Hi Christian, You can send me any questions at any time to cloverken@yahoo.com I just like answering questions.The problem with the formula is the basic math error that we cannot divide a number by zero. So Excel returns the #DIV/0 error. To get around...

Excel: formulas in excel, formulas in excel, black arrow

formulas in excel, black arrow, sum formula: The error #DIV/0! occurs when Excel find a division by zero. In order to avoid the error, one way is to check first if the part of the denominator is zero, and avoid the calculation if so. The formula to do so can be: =IF(A2=0, ,A1/A2*50+50) Use this...

Excel: formulas involving dates, january first, mark 1

january first, mark 1, january 1st: Try this: =SUMPRODUCT(--(DATE(YEAR($A$1:$A$10),MONTH($A$1:$A$10),1)=DATE(2007,10,1))) for Oct 2007. Change: =DATE(2007,10,1) for the month you need. You will need a separate formula for each month. The formula assumes your dates are in A1:A10....

Excel: Why are my formulas no longer adjusting the value in cells?, microsoft office professional, iname

microsoft office professional, iname, spreadsheet: In order for me to solve your problem I will need you to email the spreadsheet to me at rmadsen@iname.com. Robert ====================================================== Want to learn more? I write and sell training materials for Word, Excel and many other...

Excel: formulas using miltary time, format cells, time question

format cells, time question, timeclock: Deb You can still type in 27:00:00 and it will change to 3:00AM and still work out the hours between the starting hour and ending hour. The only problem with working more than 24 hours is that whoever does the payroll has to know or be shown on the sheet...

Excel: formulas, excel 2000, c5

excel 2000, c5, syntax: Hi Dan, Suppose you have the data in worksheet2. Name it as Db and sort it on the first column which contains the values on which u r going to do the search. In the second column of the first worksheet put this formula =VLOOKUP(C5,db,5) where c5 is the...

Excel: fornulas, chemical applicator, format cells

chemical applicator, format cells, a1 mix: Hi Don! How are you? I would do the following A1: Mix rate (100) B1: Water for the Mix rate (1.5) C1: Mix needed (10) D1: = B1*C1/A1 If you want to format the number of D1, please Select D1 Format - cells - number and choose the format you want....

Excel: free exercises from the net on Excel, free exercises, office2000

free exercises, office2000, fgcu: Gosh, that s a great question, Srikanth. Since I m a professional who s worked with Excel for many years, I haven t had the need myself to search out such a site. But it certainly would be a handy thing for me to recommend to others. Just right now, I went...

Excel: freezepanes excel 2007, freeze panes, single step

freeze panes, single step, first person: Congratulations on being the first person to ask an Excel 2007 question- luckily one day after I took delivery of this version! In earlier versions of Excel, you needed to split the window first, then freeze panes - this has been changed to a single step,...

Excel: freezing rows and columns, internet explorer, d6

internet explorer, d6, b6: Hi Rebecca, Excel cannot do that unfortunately. However when you need something like that you can use multiple views of one window. To do this you select the worksheet that needs multiple views and then go to the Excel menu and select Window then ...

Excel: frequency formula with criteria, frequency formula, columm

frequency formula, columm, b cells: Hi Sean Hopw are you? The following seems complicated and may be there are simpler ways to do it but, please try 1. Tools - macro - visual basic editor 2. insert - module 3. copy Option Base 1 Function CountingUni(A As Range, B As Range) Dim C(100),...

Excel: fromatting, shading, benefit

shading, benefit: Excel has a way to automatically shade rows based upon a changing cell value, but unfortunately, you can t shade rows based upon the location of the active cell. The best work-around I can come up with is the Shift/space shortcut. Pressing Shift/space automatically...

Excel: frustrating excel formula!, brick wall, excel formula

brick wall, excel formula, 366 days: Your info was conflicting regarding the 365 and 366 days for a one year stay: Z30 = between 365 & 731 then i d like AA31 to show 1-2 years and if Z30 showed a figure between 1 & 366 id want AA30 to show 1 year and so on. Therefore adjust the formula...

Excel: IF function, country japan, thng

country japan, thng, saids: Hi Dina, How are you? A question like that require the followinf thought: If country = japan then taxrate=17/100 condi else taxrate=0 endif Converting to Excel notation 1) Where is the country? Suppose the name of the...

Excel: IF function, vlookup function, cell d1

vlookup function, cell d1, cell c1: AN IF WOULD work but there is a better approach IMHO. Create a 2 column table. Put the ins. co names in the first column and their respective percentages in the adjacent column. I will use A1:B5 as an example. In D1, use this formula: =IF(ISERROR(VLOOKUP(C1,$A$1:$B$5,2,FALSE)),C1&...

Excel: IF function, m58, m81

m58, m81, m22: It would be best to put those lists in separate places. So, for example, if the first 22 values are in M1:M22 and the next 17 values are in M23:M39, etc, you can use smething like: =IF(NOT(ISNA(MATCH(J2,M1:M22,0))), Pac10 , )&IF(NOT(ISNA(MATCH(J2,M23:M39,0))),...

Excel: IF function, logical test, cell a1

logical test, cell a1, test value: If works like the following: =IF(logical_test,value_if_true,value_if_false) This means, if you had the value of 95 in cell A1, you could do the following: =IF(A1 =90, A , Not an A ) For the purposes of calculating grades, you can nest IFs together...

Excel: function, masterlist, cols

masterlist, cols, a1: In Master list cell B1 enter: =IF(ISNA(MATCH($A1,Sheet1!A:A,0)),IF(ISNA(MATCH($A1,Sheet2!A:A,0)),IF(ISNA(MATCH($A1,Sheet3!A:A,0)),NA(),INDEX(Sheet3!B:B,MATCH($A1,Sheet3!A:A,0))),INDEX(Sheet2!B:B,MATCH($A1,Sheet2!A:A,0))),INDEX(Sheet1!B:B,MATCH($A1,Sheet1!A:A,0)))...

Excel: IF function, f15, cell contents

f15, cell contents, cells: Tom: I believe this may be what you are looking for...if I understand your intentions correctly: =IF(COUNTIF(F15:F21, Done )=ROWS(F15:F21), Done , IF(COUNTIF(F15:F21, In progress ) 0, In progress , Not Yet )) If all 7 cells = Done , then DONE is...

Excel: IF function, cell d2, cell c5

cell d2, cell c5, shipping amounts: Hey Maria, In the blank cell in the total column that should contain the shipping amount, put this formula: =IF(C5= standard , $9 ,IF(C5= express , $15 , )) This is assuming that the shipping type is listed in cell C5...you will need to change this...

Excel: OR function, input cells, data validation

input cells, data validation, false message: Hey Sean, You can use Data Validation to solve this problem. Here are the steps: -Select the range of cells that you want to check. You can click the top of the column to select the whole thing if it is the whole column that has the data. -On the...

Excel: if function, conditional formatting, cell b3

conditional formatting, cell b3, empty strings: Functions can t hide columns. Your approach of using If formulas is about a close as you will get using just functions for instance in D3 =if($B$3 =2, my value , ) for E3 =if($B$3 =3, My other value, ) another approach would be to use conditional...

Excel: IF function, cell d1, cell c1

cell d1, cell c1, vlookup: Gary If you are only using 5 insurance companies then the if is what would be easier if you go above 7 then if statements don t work and you would need something like a vlookup. Here is the if statement that you would need. If you are working on the cell...

Excel: IF function, cell c1, cell d1

cell c1, cell d1, company names: Hi Gary, We can accomplish this several ways. (I m going to shorten your company names to A, B, C, D, E.) Here s one just using an IF statement: =IF(C1= A ,0.5,IF(C1= B ,0.5,IF(C1= C ,0.5,IF(C1= D ,0.55,IF(C1= E ,0.45,0),0),0),0),0) That formula...

Excel: IF function, cell d1, cell c1

cell d1, cell c1, vertical data: For C1, use the DATA menu, then VALIDATION - choose LIST and either select a cell range (on the same sheet) OR type the list (eg Acme,Beta,Charlie - enter it as the names with commas seperating them). This gives you a drop down list on the worksheet. NOTE...

Excel: IF function, cell d1, cell c1

cell d1, cell c1, vlookup formula: What you describe would be better served by putting your companies and their corresponding percents in a data table and then refer to them using a VLOOKUP formula. Assume your company names are in the range A1:A5, the corresponding percents for each are...

Excel: IF function, cell d1, cell c1

cell d1, cell c1, southern indiana: in D1: =CHOOSE(MATCH(C1,{ Acme Ins. , Beta Ins. , Charlie Ins. , Delta Ins. , Egads Ins. },0),0.5,0.5,0.5,0.55,0.45) but notice that C1 must match EXACTLY (including the ending period) the list). or =IF(OR(C1= Acme Ins. ,C1= Beta Ins. ,C1= Charlie...

Excel: NOW() function auto update, target value, sheet tab

target value, sheet tab, combo box: You can t do it with formulas, as you found out. You d need an event macro which would fire with the worksheetChange event. It would need to test which cell caused the change, and if it were A11 or B11 or C11, etc, then if it is pass , then you can put the...

Excel: Is there a function to compare..., vlookup formula, cell a1

vlookup formula, cell a1, millie: Millie Yes you can use the VLOOKUP in an IF formula to accomplish what you want with a formula as follows. =IF(ISERROR(VLOOKUP(A1,B$1:B$10,1,FALSE)),+A1, ) If there is no match the VLOOKUP formula will return an error message, so where there is...

Excel: IF function in excel, double digits, a7

double digits, a7, assumptions: Marke Ok here is the formula =+IF(ISBLANK(A7), ,IF(AND(ISODD(A7),A7 10),1,0)) I m not totally sure about what you meant by pair I assumed you meant double digits, I.E. 10 as opposed to 9. Also What did you want to appear if the number was not...

Excel: if function: finding cells that contain part of the text in another cell, extra digits, cells

extra digits, cells: Kristy I think I understand-- Wht I believe will work for you is a formula that looks something like this =IF(ISERROR(FIND(E$1,B1)), ,FIND(E$1,B1)) Ignore the iserror part of the formula that is just there so that you wont get a #VALUE sign as a...

Excel: IF function help, pension calculation, column c

pension calculation, column c, return result: Hi, Eileen! That s my daughter s middle name. She s 24. As you can imagine, she hates the name. :) I love it! Okay. In Column C s cell (assuming row 2): =if(B2 =43700,B2*.0699, ) In column D s cell: =if(B2 43700,B2*.0849, ) :) Happy Holidays...

Excel: function or macro in excel that insert rows and fill missing numbers in a series, macro in excel, gap

macro in excel, gap, b4: More details would be useful. However, this may help: You can insert rows with Insert/Rows, then, let s say the gap in column B is between 2 values, like 123 and 174. Select from the 123 to 174 (maybe that s B4 to B17, for example), then use Edit/Fill/Series...

Excel: If function Does not work after 8 Ifs, nestings, vlookup

nestings, vlookup, cap 10: The IF function is limited to 8 nestings by Excel. There is no workaround with the IF function. You will need to instead go a different route. Based on the data presented, an index might be the way to go. Option A) =MATCH(H2,{ Ari , Tau , Gem , Can...

Excel: IF function, cell b1, cell d1

cell b1, cell d1, case range: Hi Dylan, Who said you can t do more than 7 if s? Try this. Cell B1 formula =IF(C1 0,C1,IF(A1=1,25,IF(A1=2,24,IF(A1=3,23,IF(A1=4,22,IF(A1=5,21,IF(A1=6,20,0))))))) Cell C1 formula =IF(D1 0,D1,IF(A1=7,19,IF(A1=8,18,IF(A1=9,17,IF(A1=10,16,IF(A1=11,15,IF(A1=12,14,0)))))))...

Excel: functions, excel 5, buit

excel 5, buit, cheers: Hi Mike, How are you? I do not know any buitl-in function that does that. But you can try the follwing function I wrote 1. Tools - macro - visual basic editor 2. insert - module 3. Copy the following Function recent(A) m = 2 For J = 2 To A.Columns.Count...

Excel: functions, format cells, cell a2

format cells, cell a2, cell a1: Hello, Carlton, In cell A3, enter the following formula: =(A2-A1)*24 Go into Format/Cells, and on the Number tab, choose the Category General. (That is, remove the Date/Time formatting from the cell.) In your example, the result will be 7. Hope...

Excel: functions, b cells, countif

b cells, countif, usa regions: Hi Afton again How are you? You can also do it simple and hard working 1. Assuming your data is in A1:B10 2. in C1 type =IF(AND(A1= USA ,B1=20),TRUE,FALSE) copy c1 to c2:c10 3. in D11 =COUNTIF(C1:C10, true ) or with a VBA function 1. Tools...

Excel: functions, douglas m smith, array formula

douglas m smith, array formula, usa regions: Hi Afton, The following formula will do what you want: =SUM(IF($A$1:$A$5= USA ,IF($B$1:$B$5=20,1,0),0)) but hit Control-Shift-Enter instead of Enter. This makes it an array formula instead of a regular formula. If you do it correctly, {} brackets...

Excel: functions, array formula, column c

array formula, column c, usa regions: Afton Have you looked at Pivot Tables? These can be quite easy to use for the exact options you need. You will have to sort the column alphabetically but otherwise should be easy. Have a look at this link and if you have any other questions please get...

Excel: functions=IF+Concatenate=text string?, cheese milk, array function

cheese milk, array function, c29: Assuming the data is ONLY in the cells 23 to 29, then you can use a simple formula like this (I never use the concatenate function as the & sign does the same thing and is easier to type!) - this is a normal, not array, function =IF(B23= X ,C23, )&IF(B24=...

Excel: IF functions and drop down boxes, cell a1, box c

cell a1, box c, variables: =if(or(and(a1= a ,c14 10000),and(a1= b ,c14 15000),and(a1= c ,c14 20000),and(a1= d ,c14 30000)), Stop , ) should do it for you It would be tidier with a lookup table:- =IF(C14 VLOOKUP(A1,F1:G4,2,FALSE), stop , ) where F1:G4 in this example have...

Excel: IF functions/formulas in excel, overstocked books, formulas in excel

overstocked books, formulas in excel, single box: Hi Karen, This is an interesting problem and I don t have an answer off the top of my head, but I would like to work on it. Unfortunately, I will be out the next several days, but if I come up with a solution, I ll try to respond early next week. -Crai...

Excel: the IS functions ISTEXT and ISBLANK, little key, true response

little key, true response, aldan: There is a belief amongst some people that the very big key at the bottom of the keyboard is the same as the little key to the right of the large enter button - or, a space is the same as delete - but of course it isn t. A cell containing only one or more...

Excel: Two functions in a single spreadsheet, douglas m smith, city of calgary

douglas m smith, city of calgary, couple options: Hi David, This cannot be done directly as the totals can t remember what was erased. However, there are a couple options. If you copy the numbers from each transaction into the same cells using Paste Special using the Values and Add options, each...

Excel: If functions, group numbers, negative numbers

group numbers, negative numbers, a20: #######################FOLLOW UP################ I had a thought after posting the answer that you MAY want to handle zeros and negative numbers - which the original answer doesn t do - so.... =MIN(5,INT((MAX(3,A20+2))/3)) gives you the answer you...

Excel: Gathering data from many Excel (forms) surveys, outlook object model, path expression

outlook object model, path expression, excel forms: Theoretically, you can do this with a macro - I would set up a rule in Outlook to ensure that the replies all end up in the same folder. You would then need to go through each item in the folder, opening the attachments and saving the data to the storage...

Excel: GETPIVOTDATA, getpivotdata function, pivottable report

getpivotdata function, pivottable report, quotation marks: Each GETPIVOTDATA returns one value from a pivot table. From help: GETPIVOTDATA See Also Returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in...

Excel: Generating a list from Vlookup, index array, search parameter

index array, search parameter, column number: Antoine: The Short: Vlookup won t work very well for this situation. I suggest a combination of INDEX, MATCH, and OFFSET. Please refer to the following example: http://home.swbell.net/nate-sus/excel/multiplefinds.xls The Long: =INDEX(array,row_num,column_num)...

Excel: Generating new numbers, microsoft office 2003, myid

microsoft office 2003, myid, mark clayton: This code uses a named range to store the ID. I have coded it so that when the book is opened it checks what the recorded number is and adds one. This will of course fail if macros are not enabled or the user does not use the master workbook. Option Explicit...

Excel: Generating a Random Sample, selection index, random samples

selection index, random samples, random sample: Lesley: I think you need to see Tushar Mehta s RANDOM page: http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html I am thinking you would use the RAND to randomly apply values to your 500 rows, and then RANK to pick the lowest 115 or highest...

Excel: Ghost Toolbar, custom tool, backgroung

custom tool, backgroung, tool bars: None that I know of, aside from this idea: you might try to take all your custom toolbars & attach them to a new workbook (customize/Toolbars tab/Attach button). Save this workbook. exit Excel, Delete the xlb file, open the workbook with all the toolbars,...

Excel: Give excel sheet a FORM look, excel sheet, vba

excel sheet, vba, layman: There are things that you want the worksheet to do that are not possible without using VBA. You could create a workbook with two sheets, one to act as a form and the other as the database, but in order to keep it functional you will need macros for the following:...

Excel: Giving a Place Value based on percentages, format button, column c

format button, column c, different color: If your percentages are in C1:C13, then enter this in D1 and fill down to D13: =RANK(C1,$C$1:$C$13) To get the colors you want, select C1:C13, then use Data/Conditional Formatting, change Cell Value Is to Formula Is, then enter this formula: =C1 =SMALL($C$1:$C$13,4)...

Excel: GOTO a conditionnal cell adress, max function, max range

max function, max range, events dates: Stephane, For calcuation purposes, Excel treats dates as numbers. Today s date (7/29/2004) is really the number 38197 to Excel. Thus, if you were to use the MAX function on column B, Excel would return the highest value in that list which would naturally...

Excel: Goal Seek, excel tools, spread sheet

excel tools, spread sheet, last question: Hannah, I don t know of a way to make this happen for you. I do know that VBA has a goalseek function, but I haven t ever used it. It s possible that you could select the cells you want the result in, and let the code walk it through the process. Unfotunately,...

Excel: Goal Seek, ahmed ahmed, frontline systems

ahmed ahmed, frontline systems, ogilvy: Ahmed, No, I believe goal seek is restricted to the choices in the dialog. It sounds like you can do more what you want using the Solver Addin. the Solver addin is shipped with Excel and is provided by Frontline systems. Here is a link to their...

Excel: Goal Seek Formula, keyboard shortcut, cell position

keyboard shortcut, cell position, position input: Ok, here is a formula that will work, and the explanations about why: Sub Macro3() Selection.GoalSeek Goal:=0.25, ChangingCell:=Range( S6 ) End Sub A few remarks: - The goal seek tool only accept one cell to be changed, and can only be applied to one...

Excel: Goal Seek Formula, sintaxis, cells

sintaxis, cells: You can do it with VBA. I have recorded a macro while doing a simple goal seek and this is the result: Sub Macro1() Range( C1 ).GoalSeek Goal:=9, ChangingCell:=Range( A1 ) End Sub The sintaxis is simple, you can use it with Selection and offset...

Excel: Goal Seek, cell a2, excel tools

cell a2, excel tools, spread sheet: Hannah, I only know of a way to do what you are asking with VBA code. The below routine will goal seek four cells starting with C15 and going over to F15. You can add more cells to the range if you have more than four cells in the row you are evaluating....

Excel: Golf Handicap Calculation, golf handicap calculation, excel spreadsheet

golf handicap calculation, excel spreadsheet, b30: That was FUN! My SUGGESTED solution is to have a vlookup table which consists of three columns The first column has the numbers 5,7,9,11 etc (the lower of the values you are interested in) The second column has the number of differentials you will take...

Excel: Golf Handicap Calculation, golf handicap calculation, a21

golf handicap calculation, a21, b11: =SMALL($A$2:$A$21,ROW()-2) You left out the minus. Row() returns the row number where this formula is. So to get the first smallest number AND your formulas start on row 4 then. =SMALL($A$2:$A$21,ROW()-3) ROW()-3 will calculate to be 1, the first...

Excel: Golf Handicap Calculation, golf handicap calculation, vlookup

golf handicap calculation, vlookup, excel spreadsheet: Darren, I don t have a copy of the original. I emailed it from work, so I don t have it here at home. It just said I assumed the data was in D2:D21 (20 cells) since you didn t say where it was actually located. the first formula can be entered normally....

Excel: Golf Handicap formula question, golf handicap calculation, douglas m smith

golf handicap calculation, douglas m smith, array formula: Hi Darrin, Your question just snuck in after I went on AllExperts vacation for the week. I don t have time to answer it today, but it s a very interesting problem and I did something similar with bowling scores a few years back. I ll track down that spreadsheet...

Excel: Golf handicap, array formula, golf handicap

array formula, golf handicap, vlookup: Darren, I don t recall the question or the answer, but looking at the formulas, It appears to me the second is a much shorter version of the first (probably I was suggesting a more compact approach), so you would only need one. The second would have to be...

Excel: Grafs, axes, axis

axes, axis, graph: If the dates in question are far apart, Excel will fill in uniformly the scale data on the axes. To change this (not actual dates but evenly placed dates), right click on the axes in question, Click on Format Axis , go to scale tab and give appropriate values...

Excel: Grand Totals for Rows in a Pivot Table, pivot table, table options

pivot table, table options, options window: Never had that problem. Here is the way my options are set up Checked Grand totals for columns Grand totals for rows Subtotal hidden page items Preserve formatting Repeat labels on each printed page Save data with table layout Enable drilldown ...

Excel: Graph Color, graph color, info charts

graph color, info charts, different colors: Sorry, I was trying to figure out the layout, but I am not completely sure of what it is. Can you please send an example to my email address (zapicm [at] gmail.com)? In the meantime, I can recommend a nice site with Excel chart tricks: http://andypope.info/charts.htm...

Excel: Graph data from the MSF test, stage fitness, graph data

stage fitness, graph data, manual input: Casper, Yes, you can use the Offset function to return the desired value from your Matrix, as follows: Put the following in each cell as indicated: A1: Level A2: Shuttle B1: 4 B2: 1 A3: MatrixLookup B3: =Offset(A5,B1,B2) A5: 0 B5 through...

Excel: Graph of distance in Excel, person lives, alec

person lives, alec, thanks in advance: Count of those within 20 miles: =SUMPRODUCT(--($A$2:$D$2 =20)) Count of those greater than 20 and up to 50 miles: =SUMPRODUCT(--($A$2:$D$2 20),--($A$2:$D$2 =50)) Count of those greater than 50 and up to 70 miles: =SUMPRODUCT(--($A$2:$D$2 50),--($A$2:$D$2...

Excel: Graph formula, linear trend, trend line

linear trend, trend line, vertical lines: Tim I am not sure if this will work or is even what you need as I don t do much in the way of analysis with charts but from memory you can right click on the line and select options and then display equation on chart but as I said I haven t used it. ...

Excel: Graph labels, example spreadsheet, category axis

example spreadsheet, category axis, bottom axis: Jaco: This should work for most charts: 1) Right-click on the chart and choose source data. 2) Select the series tab. 3) Select the series which represents the years. 4) In the box that says category axis labels select the Excel range which contains...

Excel: Graph Math, graph wizard, inch increments

graph wizard, inch increments, wizard menu: In fact, what the trendlines do is regression, given a set of points the system tries to find the equation of the suggested type that best fit the distribution. As far as I know, Excel needs the type of equation to give results, there is not a built in formula...

Excel: Graph Printing Problem, axis labels, graph window

axis labels, graph window, logarithmic scales: I have three ideas at the immediate moment; however, this might be one of those situations where I could view the spreadsheet and find an answer. If you are willing, my email is Lotus@swbell.net. If not, here are the three ideas off the top of my head: ...

Excel: Graph with stacked column and line, graph types, line graph

graph types, line graph, programing: Hello again Nele Ok Ill assume you have your data plotted as a stacked graph with a common x axis. Place the on one of one one of the stacked data points on the graph right click the mouse and select chart type , when the menu opens select line graph....

Excel: Graphic Motion in Excel, shape objects, graphic animation

shape objects, graphic animation, view of the earth: Hi Daniel, There are several ways to achieve graphic animation in Excel. But I should qualify first that Excel is not a great application for doing dynamic graphics since it does not contain any built-in (and therefore efficient) code to support this. For...

Excel: Graphing, xy scatter chart, result chart

xy scatter chart, result chart, semilog: After you make the normal XY-Scatter, can t you right-click the Y-Axis, choose Format Axis, then click the Logarithmic scale checkbox? (Not sure what a semi-log form is, as opposed to a log scale). If this doesn t work, I can t help you, mainly because...

Excel: Graphing average values, smooth graph, fortnightly basis

smooth graph, fortnightly basis, friend andy: I think the only way that you could do this would be to actually calculate it within your data. I m no expert on charts, but have a look at my friend Andy pope s site - www.andypope.info. maybe ask your question at http://www.excel-it.com/Forum/index.php -...

Excel: Graphing Geographical data on Maps, google maps, microsoft mappoint

google maps, microsoft mappoint, geographical data: You could use Google Maps. It is a free service from Google and enable you to embed maps into Excel - see examples at www.automateexcel.com. You will need to get a registration key from Google (it s free and not a hassle) and you will be limited to something...

Excel: Graphing, straight line graphs, chart wizard

straight line graphs, chart wizard, tiny column: Hi, Kay, The first thing you should know is that Excel calls them charts. Lotus 1-2-3 called them graphs, and Excel calls them charts. I don t have the slightest idea why! In order to create a chart, here are the steps you follow: 1) Click any single...

Excel: Graphs, graph lines, plot graph

graph lines, plot graph, chart wizard: Hi Ed & grandSon How are you? It is nice to see grand father and grand son working together. Congratulations 1st question Assuming you are using the Chart Wizard (Insert-chart) After choosing the type of chart: - press next - define the data Range...

Excel: Graphs, linear regression line, slope of a line

linear regression line, slope of a line, creating a graph: Hi Ed, Would it be possible to send me the spreadsheet so I can take a look? My email address is bluecat74@yahoo.com There is a function in Excel to find slope that it seems like you could use: SLOPE: Returns the slope of the linear regression line...

Excel: Graphs, chart wizard, paste values

chart wizard, paste values, dynamic chart: In order to get the values, one simple approach can be generating a column of due dates (for example in column M) and using a formula like this on the data table: =IF(NOW() M1,J1, ) Supposing that J1 contains the value that we are looking for. If the...

Excel: Graphs, secondary axis, dynamic graphs

secondary axis, dynamic graphs, info charts: Hi James, When you have two series, you can use the secondary axis to plot one of them, so they can have different scales. You can find the option in the properties of the Data Series, on the Axis tab. Additionally, if you are interested in charts,...

Excel: Graphs, individual times, scatter plots

individual times, scatter plots, custom charts: Lee: Absolutely. The XY chart will actually allow you to add several sets of data. I have uploaded an additional sample here: http://home.swbell.net/nate-sus/excel/timechart2.xls The key is to modify the chart range to where each set of values is...

Excel: Graphs - Excel, water level data, blank cells

water level data, blank cells, empty cells: change you formula to something like =if(condition,na(),calculate result) so instead of appearing blank, it either shows a number or #N/A This causes the cell to be ignored. Hope that will give you the results you desire. -- Regards,...

Excel: Graphs from Pivot Tables, line graph, bar graph

line graph, bar graph, visual separation: Hi Greg, I m not sure why the graph functionality is reduced, but I too find it annoying. As for solutions: 1. You could try using a bar graph. 2. Presuming you don t change the layout of your pivot table often, only the width or length, you could copy...

Excel: Graphs, columes, plot graph

columes, plot graph, graph line: Ed I see this questions poped up again on my list, I m sorry I thought I d answered it, or rather suggested that you reask another allexperts person, because I m definatley not an expert in graphs and charts. I m sure you can get a good answer from another...

Excel: Hi again,These are names..., street suffix, address city state

street suffix, address city state, initial column: You re going to have to rearrange your data, obviously. I suggest these steps, which is assuming you only need to have columns A, F, and N to produce the mailing labels. This will put the info into 3 rows per address. You may then have to adjust the row heights...

Excel: Greetings,I am trying..., city st zip, excel spreadsheet

city st zip, excel spreadsheet, source data: I would suggest using WORD and its mail merge feature. When prompted for the source data in the mail merge process, reference the EXCEL workbook. This may not work if your EXCEL workbook is not arranged in the right and compatible way for WORD to figure...

Excel: Gross Profit Formula, gross profit formula, douglas m smith

gross profit formula, douglas m smith, time andrea: Hi Andrea, If you put a minus after the equal sign, that may solve the problem in the formula. Another alternative would be to enter -1 in a cell, copy it, select all your negative GP cells, select Paste Special from the Edit menu, select the Values...

Excel: Group selection, excel2003, excel97

excel2003, excel97, group selection: If column M is unlocked on all sheets, and if you select them as a group, they will change as a group - at least, they do when I try it! Can I see the file to see if I can spot anything that would explain this behaviour? My email is aidan.heritage@virgin.ne...

Excel: Group sheets based on name, canada11, sheet names

canada11, sheet names, group sheets: Sub SeparateCountries() Dim FirstInGroup As Boolean Application.DisplayAlerts = False FirstInGroup = True For Each x In Sheets If FirstInGroup Then 1: x.Select FirstInGroup FirstInGroup = False ...

Excel: [Group], visual cues, sheet tabs

visual cues, sheet tabs, group mode: Group mode is when you are editing several worksheets at once. Be careful! Any edit that you make to a single sheet, is simultaneously being made to every single sheet in the group. Because Group mode is so powerful, there are two different visual cues...

Excel: Grouped radial buttons, speadsheet, option buttons

speadsheet, option buttons, protection error: Hi Kyle, Probably the easiest way for me to help is to see a copy of this. Because there are so many variables to protection and objects. You can remove any information, but let me see the radial buttons and formulas if it is possible and send a sample...

Excel: Grouping of cells and Protection of sheet (XLS97), grouping symbol, ogilvy

grouping symbol, ogilvy, cells: I hope you know VBA code. In the workbook open event you need code like this Private Sub Workbook_Open() with Worksheets( Sheet1 ) .Activate .EnableOutlining = True .Protect Contents:=True, UserInterFaceOnly:=True End With End Sub --...

Excel: Grouping data, engineer skill, grouping data

engineer skill, grouping data, darren smith: Pivot table should do the trick. If the Reward values are truly numbers, you can use the pivot table s grouping to group them into these ranges. If you are unfamiliar with PT s, feel free to email me a sample workbook at bobumlas@yahoo.com and I ll see if...

Excel: Grouping by Quarter, pivot table excel, quarter thanks

pivot table excel, quarter thanks, welcome hope: You re welcome. Hope it works. Seeing the name Bombardier completely out of context is what confused me, meant nothing to me, had nothing to do with the real issue, and did not tell me whatsoever what it was. Remember, I can not see what you see and...

Excel: Growth Formula, growth ratio, percentage increase

growth ratio, percentage increase, e john: Usually you will need 24 months of data to measure a growth over a 12 month period. With that, you compare the same of the last 12 periods, with the sum of the same 12 periods on the previous year, with a simple formula like: =(SUM(12 months)/SUM(12 months...

Excel: gathering data, csv files, csv file

csv files, csv file, comma delimited: Yes, you can call the data form in VBA. If your data is on Sheet1: Worksheets( Sheet1 ).ShowDataForm will work. I don t know much about web/email forms. Try some other expert on that one. Maybe even a different allexpert group. HTH, Gyul...

Excel: How can I generate a new column..., substraction, question thanks

substraction, question thanks: Hi James, I m not sure I understand your question. Do you mean just having a column that just does simple substraction as follows? A B C 2 4 2 3 1 2 4 1 3 If so, you would simply put this formula in the third column, making...

Excel: generating the date in a cell after a change in value in another cell, target cell, application description

target cell, application description, completion date: IF I understand correctly what you need then you will have to write a macro and sorry, as politely as possible, I don t write macros for people on this site who need one, want one, and/or seem to think I am expected to write one unless they are very short,...

Excel: generating a formula, string s2, isnumber

string s2, isnumber, ltrim: Hi Yasser How are you? It easier to write a user defined function for that. Please do the following: 1 Tools - Macro - Visual Basic Editor 2 Insert - Module 3. Copy Function splitt(R As Range) Dim S1 As String, S2 As String, S3 As String Dim I As...

Excel: generating work orders, common sense solution, dispatching jobs

common sense solution, dispatching jobs, weekly schedules: I don t really understand your question and/or situation. But, without writing a macro, then you might want to explore Data, Filter, Advanced Filter or Data, Filter, Auto Filter. These are the only two features I can think of that will help based on what...

Excel: given several conditions, provide a result, dell optiplex gx270, slo blo

dell optiplex gx270, slo blo, several descriptive terms: Gary, There is no outright easy way to make a search engine in Excel - it would require some coding with VBA to make a decent one. One thing you can do is to use the filter function as a sort of search engine. Highlight the cell with ITEM in it. Click...

Excel: goal seek, douglas m smith, vba solutions

douglas m smith, vba solutions, brainbench: Hi, You can do this by adding a loop counter and using it with Offset. Before the loop, add the line: i = 0 This sets the loop counter variable. Before the Next cell line add this line: i = i + 1 This increments the variable for the next...

Excel: goalseek, target value, cell reference

target value, cell reference, ommission: Andy I don t believe goal seek will allow a cell reference in the to value box, so I don t think it can be done with goal seek. However you probly can create a macro that will work without goal seek that will give you the result you want. I can t tell...

Excel: golf handicap, course handicap, golf handicap

course handicap, golf handicap, handicaps: Fay, You said last 10 scores. I assume the average is of the 7 scores between the lowest and two highest and not the average of the lowest and two highest. Assume this is calculated over a period of time so scores will be recored over time. For purposes...

Excel: goto method, vague recollection, silver spring md

vague recollection, silver spring md, f5: Annoyingly, I cannot find a way of doing this with a macro - keypresses are Ok - ctrl G (or F5 or whatever else you fancy of the available shortcuts) should be followed by scroll lock, home, scroll lock - this will do what you want, but recording this records...

Excel: grade averages, quiz average, quiz grade

quiz average, quiz grade, grade averages: Hi, Rosa, The average of the quizzes can be found like so: =(SUM(C3:I3)-MIN(C3:I3))/(COUNTA(C3:I3)-1) The project and test averages can be done normally. Let s say that the quiz average is in C4, the project average is in C5, and the test average...

Excel: grading, pe teacher, occurence

pe teacher, occurence, aidan: Yes. Not sure how you have total possible points shown, BUT a COUNTIF function would count the number of a given occurence - which you could then multiply by 10 and deduct from the total Let me know if I can help further - if it helps to send a file,...

Excel: grading spreadsheets in Excel, quiz example, student emails

quiz example, student emails, quiz format: Use Alt/F11, then Insert/Module, then put the code there. You can run it by Alt/F8 (or tools/macro/macros) and select Grades . Or, you can put a button on the worksheet & right-click it & assign macro to it, assigning Grades to it and it ll then run when...

Excel: grading, pe teacher, a20

pe teacher, a20, point values: Hi, Eric, Let s say that A2:A20 is a list of either point values or AB s. You want to add up all the points, and subtract 10 for every AB in the list. This formula should do the trick: =SUM(A2:A20)-COUNTIF(A2:A20, AB )*10 In other words, sum A2:A20,...

Excel: graph, macro in excel, how to create a graph

macro in excel, how to create a graph, excel sheet: I really don t know VB. VBA, yes, not VB. This sub, done in Excel, creates a chart (graph) from the data selected. If you can get the data into the cells, say starting in A1, then select A1 (or run this small changed code): Sub CreateChart() Range( A1 ).CurrentRegion.Select...

Excel: graph, graph wizard, column graph

graph wizard, column graph, element names: It is a type of column graph, that represent each element in a single column, composed by the sum all the different values of that element in the series that comprise the graph. Each one of the contributions is drawn in a different color, so you can see how...

Excel: graph colors, graph colors, chart wizard

graph colors, chart wizard, column chart: Kathye, this is a fabulous idea for an enhancement to Excel! I heartily recommend you go to the Microsoft website and suggest it for a future version of the program. I m sure that a lot of users would love to have it. In the meantime, however.... I think...

Excel: A graph with empty values., title cells, cell b6

title cells, cell b6, graph line: Hi Roi, Yes, If you leave B5 blank the chart works, leaving the blank uncharted. However if you input a formula, Excel considers it a value. To get around this use and IF function such as. =IF(ISBLANK( B6),#N/A, B6) Cell B6 has to empty for it to...

Excel: graph on excel , X and Y two variables, info charts, excel charts

info charts, excel charts, tushar: Not sure I follow, but my email is aidan.heritage@virgin.net You might also want to check out these pages http://processtrends.com/ http://peltiertech.com/Excel/Charts/index.html http://www.andypope.info/charts.htm http://www.stfx.ca/people/bliengme/ExcelTips.htm...

Excel: graph plotting, chart wizard, axis

chart wizard, axis, cheers: Hi Terry How are you? Please do the following 1 type data for example in A1:B10 2 insert - chart 3 Select line 4. select A1:B10 5. tab SERIES SELECT SERIES1 PRESS REMOVE 6. IN CATEGORY (X) SELECT a1:A10 7. CONTINUE THE CHART WIZARD ...

Excel: graph question, dynamic array, input range

dynamic array, input range, daily basis: James, Basically I understand your generalized question to be can you build a chart that has a dynamically defined input range (both x and y axes). The answer is yes: http://support.microsoft.com/kb/183446/en-us How to use defined names to automatically...

Excel: graph, clipart index, last selection

clipart index, last selection, google: Stephan, Type the number 1 in a cell. Highlight the cell and click Insert Chart. Under chart type, select Pyramid (on my list it s the last selection). This should plot a single pyramid with a magnitude of 1. One by one, click on the elements of...

Excel: make a graphic part of an Excel record, ogilvy, blob

ogilvy, blob, cells: If the picture is saved as a file on the computer, you can go to the menu and do Insert= Picture and select File. Then nagivate to your file and select it. Hit OK. This puts the picture in a layer above the cells - it doesn t put it in a cell although...

Excel: graphing problem with Excel, xy scatter chart, axis point

xy scatter chart, axis point, data labels: Bill: Please let me know if you have any more questions...this was a fun question for me...I ve never tried to do anything quite like this before :). I have uploaded an example here: http://home.swbell.net/nate-sus/excel/kind-of-like-a-timeline.xls...

Excel: grid lines in excel, memory issue, gridlines

memory issue, gridlines, grid lines: I might be wrong, but I don t think that the gridlines are missing - to get gridlines back, use the tools menu, then options, then view and check gridlines - BUT I suspect that you have borders turned on, and that some of these are set to either nothing, or...

Excel: grid lines, grid lines, sheet tab

grid lines, sheet tab, menu tools: There are two places to remove grid lines. If you don t want to see them on the screen, go to menu Tools / Options, and in the view tab, in the Windows options section, you have a check box called Gridlines. Unmark it and the grid lines will disappear. ...

Excel: gridlines, format cells, box office 2000

format cells, box office 2000, menu option: either right click a cell and choose format, or just use the format... cells menu option - this gives you the borders tab and you can see (hopefully) what has been set here - I would GUESS that white borders would cause the problem you have described...

Excel: grids, left mouse button, row labels

left mouse button, row labels, word sorts: Diane Based on the question, I have to assume that you don t use Excel very often, so I will try to give as many details as possible. First, you need to click on the menu item File. In that menu, select Page Setup, where you will see several pages of...

Excel: grids, word sorts, page landscape

word sorts, page landscape, orientation 3: Simple, Diane. All you have to do is orient the page landscape, and then make the rows tall and the columns wide. Here s how to do so: 1) On the menu bar, go to File Page Setup. 2) On the Page tab, choose Landscape under Orientation. 3) Click OK. 4)...

Excel: group/define protected cells, outline function, control toolbox

outline function, control toolbox, pivottables: The only way I am familiar with is this method that has been around since xl97: http://support.microsoft.com/kb/169106/en-us XL97: Some Worksheet Properties not Preserved You can manually set this property on an unprotected worksheet by making the control...

Excel: What is [group], sheet tabs, chart icon

sheet tabs, chart icon, group mode: Hey Steve, Group is actually a mode in Excel that allows you to edit several worksheets at once. It seems you have accidentally entered this mode. It s important that you get out of it because any changes you are able to make in Group mode are made to...

Excel: grouping dates by month, cell b2, moving in the right direction

cell b2, moving in the right direction, sep oct nov: Laurie: Will these functions work? =MONTH() and =YEAR() For example, presuming your date, 4/1/07, was located in cell B2. These formulas would return the following: =MONTH(B2) = 4 =YEAR(B2) = 2007 If you need a text month instead of a numerical...

Excel: growth formula, cell a2, cell a1

cell a2, cell a1, misleading results: The second formula (=-FV(A1,A3,0,A2)-A2) just does that. It shows the future value based on the number of years stored in A3, if you subtract the initial value, stored on A2, you will have the 10 years of earnings together. If you want to check it, you can...

Excel: Hai, default location, thanks in advance

default location, thanks in advance: You can create a macro with the following code: Sub SaveWithWeek() ActiveWorkbook.SaveAs week & Format(Now(), ww ) End Sub This will save the current workbook to the default location, using the week of the current day. You can change the path...

Excel: Handicap Calculation, golf handicap calculation, excel spreadsheet

golf handicap calculation, excel spreadsheet, differentials: Hi, Darrin, Thanks so much for including that website. I wish more people did that. It helped give me a clear picture of what you need. I can give you a formula to accomplish this, but it has one handicap. ;-) That is, the differentials must be sorted...

Excel: HEEEELP!!, grey boxes, option buttons

grey boxes, option buttons, excel spreadsheet: Good morning Nadia! 1) Normally I don t answer questions about Macros (as per my profile), but I ll make an exception here :). You will just need to record a macro to print the desired pages (tools- macro- record new macro). Then you insert a forms button...

Excel: HELP!!, pivot table, cells

pivot table, cells, good luck: Hard to tell. Are the cells formulas? If so, do they reference other cells which contain #REF!s? Are they formulas like INDIRECT referencing a closed file? If the answers are all NO, then I m afraid your only option is to hopefully do UNDO -- else I can t...

Excel: HELP!!, douglas m smith, cell c2

douglas m smith, cell c2, cell b3: Hi again, I figured you were in England because of the date format. The following formula format can be used to take care of the error messages: =IF(ISBLANK(Sheet1!C2), ,original formula) Example: =IF(ISBLANK(Sheet1!C2), ,DATEVALUE(LEFT(Sheet1!C2,8)))...

Excel: Header Text, header text, ogilvy

header text, ogilvy, formatting: hello a, Unfortunately, excel does not provide a property or method to return the information you describe. Rightheader (or left or center) is pretty much what you have to work with. So you would need to write code to parse out the information you...

Excel: Headers, page document, workaround

page document, workaround, remainder: Unfortunately, Excel doesn t work in the same way as Word in this matter. The workaround that I usually follow is to create and additional worksheet and refer all the information on the first page there. Because the page setup is different for each sheet,...

Excel: Headers, preview areas, title header

preview areas, title header, headers footers: Joan As far as I know you can t view headers from within the workspace as excel can t show you how it will print and allow you to edit the cells at the same time. This is because the headers are not actually part of the spreadsheet but part of the printed...

Excel: Held down the Shift Key too Long, selection area, sure what version

selection area, sure what version, shift key: Hey Jeff, I m not sure what version you are using, but I found the following solutions to your problem. Try them out and let me know if they help :o) Please note that there may be no indication from windows that the Filter keys were turned off, just check...

Excel: Hellish Goal Seek Scenario., r2 value, day trades

r2 value, day trades, price column: ...i would like to be able to make this code skip over rows that already have a manually input value already.... Just use a simple IF statement in your For loop that checks to see if that cell has a value. For Each cell In Range( V2:V100 ) If cell...

Excel: Hello Bob,hope you can help..., excel 2002, bob hope

excel 2002, bob hope, thanks in advance: This works for me (first shot!) Sub redistribute() n = 1 m = 1 For i = 1 To Range( A65536 ).End(xlUp).Row For j = 1 To Cells(i, 256).End(xlToLeft).Column Sheets(2).Cells(n, m).Value = Cells(i, j).Value m...

Excel: Hello DouglasWhat I am..., douglas m smith, mark langley

douglas m smith, mark langley, cell b1: Hi Mark, The best way to handle this is with a lookup table and to read it with VLOOKUP. Example: put the 0 through 28 value in column E and the corresponding values in column F. If the column F values are the same over a range of column E values, you...

Excel: Hello JanI'm Salvino Sammut..., blank cells, preferred one

blank cells, preferred one, a100: Hello again, Salvino, Please pardon me for not answering you more completely before, as I was in a hurry. The truth is, the two alternatives are not exactly the same. Your formula is the preferred one if the data in column A is contiguous, that is...

Excel: Hello JanI have a program..., a11, a10

a11, a10, sheet1: Hello, Mark, On each cell of page 2, you can put the following formulas: A10: =IF(Sheet!A10 0,Sheet!A10, ) B10: =IF(Sheet!A10 0,Sheet!B10, ) C10: =IF(Sheet!A10 0,Sheet!C10, ) A11: =IF(Sheet!A11 0,Sheet!A11, ) B11: =IF(Sheet!A11 0,Sheet!B11,...

Excel: Hello Jessica -I am working..., aids foundation, grant awards

aids foundation, grant awards, ssn: Hi Celeste, You can absolutely do this with Excel. I will be glad to help. You can use a column for SSN and then next column for Names and additional columns for more data you may have. You can sort the columns in alphabetical or numerical order after...

Excel: Hello!I have a query result..., query result, field names

query result, field names, access database: You will need to write a formula, in an adjacent cell, that calculates the 4 s, the =4 and =6 s, etc and then use that clumn to graph. Look into the COUNTIF function and the Condidtional Sun Wizard, which is an add-in and may not be loaded. The Condidtional...

Excel: Hello!I have a query result..., query result, access database

query result, access database, aidan: Firstly, you will need to get your data summarised as you only want three values charted - so you will need a formula that counts records that are in the age ranges concerned. Without more information, I cannot easily provide this formula but if you are happy...

Excel: Hello RichardI have an..., quantity number, excel worksheet

quantity number, excel worksheet, jose jose: Jose I m not sure what is meant by the 10 top SysID that spent more parts and the 10 top used parts . How do you determine what is top, number of orders, number of units sold (or ordered), dollar value of units sold (or ordered), etc. etc. I don t see...

Excel: Bill,I'm sorry if you..., blank rows, column names

blank rows, column names, thanks in advance: Don t meant to be rude but it IS quite a bit due to so many unknowns and/or assumptions that the macro would have to handle. And, depending on how much you know about wriitng macros will determine if an example I send you will make sense and you will be able...

Excel: Hello, I am using Excel..., border styles, color border

border styles, color border, days of the week: What you want to do can t be done without either using copy/paste special values (and then clear the original cell), or a macro. I have an example of how such a macro would work, but it d be a lot easier to explain if you sent me your email address for me...

Excel: Hello, douglas m smith, brainbench

douglas m smith, brainbench, mvp: Hi Anthony, There are a couple of way to chop data. One involves the functions LEFT, RIGHT and MID. The formulas to get the above results would be: =LEFT(A1,3) =MID(A1,4,2) =RIGHT(A1,2) In the LEFT formula, it gets the three left characters. ...

Excel: Help, resuls, countif

resuls, countif, value error: It s difficult to answer without seeing your workbook layout.Your formula appears to be looking at a range of cells, whereas afaik LEFT and RIGHT should refer to single cells. If you are trying to determine how many cells start with W then I would use a COUNTIF...

Excel: Help, cells, first book

cells, first book: Balwinder You can link the cells from one workbook to another by either copying and pasting as link or typing in = in the second book and clicking on the corresponding cell in the first book. For the second book to update though it will need to be opened...

Excel: Help!!, logical value, e105

logical value, e105, ogilvy: Masego, It works fine for me if the cell actually contains the logical value TRUE. If it contains a string TRUE , then it doesn t work. If your formula in column F to return true or false is =if(condition, TRUE , FALSE ) then change it to =if(condition,TRUE,FALSE)...

Excel: Help!!, office experts, e105

office experts, e105, correct answer: Hi, Masego. Sorry. I think I m going to need to see the formulas in F column. If your formula is using TRUE , then you need to put quotes around it in your Countif. Otherwise, I d have to see your file. You could send it to anne at office experts dot com...

Excel: Help to adapt code to send outlook tasks, outlook application, outlook tasks

outlook application, outlook tasks, c body: You code shows the data being gotten from 4 rows in column A, but you say it is in 4 columns on a single row - I will assume the latter Sub Test() declare and instanciate Outlook Dim objOutlook As New Outlook.Application declare a task item Dim...

Excel: Help to Make Chart Object Free of Cell Ref., macro code, mouse movements

macro code, mouse movements, b85: It s easier to do it manually - as these will have links in them, use the Edit Links menu choice, and then choose Break Links which converts the linked values to the current values of those links. I guess a macro could be written to do this, but as it s so...

Excel: Help With Charting Macro, Please, vba code, sheet1

vba code, sheet1, series names: To refer completely to a range including the sheet where the range is, use this syntax: Sheets( Sheet1 ).Range( A1 ) In your example the code is looking for a range name of Sheet1!A1, not a reference to A1 on Sheet1. Secondly, I suspect some of your...

Excel: Help with Conditional Formatting, conditional formatting, logical formulas

conditional formatting, logical formulas, menu format: You can only have three conditional formatting applied to a cell, so in order to code the fourth condition we will have to use some logical formulas. For the sample, I would suppose that the text with the answer will be on column A, the entered date on column...

Excel: Help :D, quotation template, product information sheet

quotation template, product information sheet, vlookup formula: Using a range of A2:A12865 allows ONLY 1 column. If you use A2:Z12865, then the ...4,FALSE will return the value from column D, and ...26,FALSE will return the value from column Z, and ...27,FALSE will return #REF!. all of these will look up the value in column...

Excel: Help for data validation and lookup, data validation, dear bill

data validation, dear bill, column c: There is no validation rule you can write that will do what you want done. Secondly, you will have to alter and/or re-create your table to make a formula work for you. To re-create your table: In an adjacent column, E for example, write this formula:=A5&...

Excel: Help with dropdown list functions, cell d2, cell a1

cell d2, cell a1, data validation: James: I have uploaded a sample spreadsheet here: http://www.snapdrive.net/files/487599/3lists.xls You can use the data- validation dropdown box in cell A1 to select either HK, KL, or NT. Once that is selected the list to the right is generated dynamically...

Excel: Help with Excel combination Function, freebie software, optimization problem

freebie software, optimization problem, optimization tool: What you re asking for is the very common Cutting Stock optimization problem. It s not all as simple as putting a few cells in a spreadsheet. There are dedicated software packages for that sort of thing. There are also different ways of looking at...

Excel: Help with Excel Function, excel function, allexperts

excel function, allexperts, iterations: Michael, I solved a similar issue for someone else. The URL is attached. http://en.allexperts.com/q/Excel-1059/finding-combination-selected-cells.htm You should be able to tweak the formula to make it work for you. only change is in Step 1, maximum...

Excel: Help with Excel formula, vba macro, excel formula

vba macro, excel formula, invoices: It would need a VBA macro, and I ve answered similar questions before - as you say, you MAY get too many combinations, but of course you would get FEWER combinations if you looked for the number that HADN T been paid (if you know the total due, and the total...

Excel: Help with Excel formula, excel count, array formula

excel count, array formula, using microsoft office: You ll have to change the referenced cells, but try this: =SUM((A2:A10= F )*(LEFT(B2:B10,1)= 5 )) You must enter this as an ARRAY formula--sometimes called a CSE formula. That means type it in, then hit Ctrl+Shift+Enter (not just Enter!). The LEFT...

Excel: Help with an Excel function, valid combination, excel function

valid combination, excel function, invoice numbers: yes, this would create so many different possible combinations that EXCEL would probably freeze before it found a valid combination. I assume you have unique invoice NUMBERS for each of 20 different invoices. When the cust pays he does not specify which...

Excel: Help on Formula, c 280, blank cell

c 280, blank cell, a15: Then that says the VLOOKUP IS returning an error since the formula says if the long VLOOKUP returns an error then display nothing. TO help confirm, replace the with a zero. IF it returns a zero then your VLOOKUP is wrong somewhere and you will have...

Excel: Help In Freeing Up Excel System Resources, hard disk space, excel system

hard disk space, excel system, red herring: The hard disk space is a red herring, except that windows will use some of it for virtual memory. The resource message is a windows message, and apart from the fact that Excel can be memory hungry I m afraid my windows knowledge is not up to this question...

Excel: Help with Interpolation, straight line segments, linear regression

straight line segments, linear regression, line sections: Your data fit well in to a linear model. To see the pattern of your data, you just chart known values of X and Y and see the trend of the graph. That means you can use linear regression, i.e your line is of the form y = mx + b. Excel can calculate for you...

Excel: Help with incrementing Columns in Excel, range selection, task selection

range selection, task selection, selection range: So to clarify William Column A can be of any lenght and may or may not have characters in the first cell of each row. If this is so and also the conditions of your subroutine are lets say True then you wish to check each cell in the current row ( excluding...

Excel: Help with indentifying formula, vlookup, math calculations

vlookup, math calculations, current sheet: Hi, John, I assume that the main part of the formula that you don t understand is the VLOOKUP -- that s the only function that appears. Let me explain how that works first. VLOOKUP searches the first column of a range for a given value. It then returns...

Excel: Help with list box in VBA, data validation, vendor names

data validation, vendor names, combobox: Putting it on a worksheet helps but as I had said before, there are two ways of putting it on a worksheet and you have not said and did not say which of the two ways you would do so. Knowing which way you do so determines what I need to write to answer...

Excel: Help with Macro, richard nash, excel macro

richard nash, excel macro, correct position: Raj The simplest way is to create a name for the last cell on sheet two then create a macro like this one. This macro goes to the cell named end, inserts the copied text from sheet 1 at the correct position. This macro will only work if the copied text...

Excel: Help with a macro in Excel 2003, macro in excel, input number

macro in excel, input number, code option: You asked for comments on your code, have you tested it. I don t think it will work. I have added some comments within the code Option Explicit Sub CopyRange() Dim selctedarea As Range On Error Resume Next Application.DisplayAlerts = False...

Excel: Help on macro (Next row), false selection, excel object model

false selection, excel object model, blank row: on your first question: comment out the Selection.Insert Shift:=xlDown on your second question: Sheets( Audit sheet ).Range( E43:CH43 ).Copy Sheets( Data Dump ).Cells(last + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ ...

Excel: Help on macros, plant numbers, sheet numbers

plant numbers, sheet numbers, thanks in advance: Sub Answer() Set answersheet = Worksheets.Add [a1] = Materials [b1] = Plant Set tocopy = Sheets( Sheet1 ).Range( A2:A & Sheets( Sheet1 ).Range( A60000 ).End(xlUp).Row) numrows = tocopy.Rows.Count For i = 1 To Application.Count(Sheets(...

Excel: Help with making excel budget quicker, excel budget, budget info

excel budget, budget info, vertical columns: Blane, I would expect it could be done with a macro. If you want to send me a sample workbook, I can have a better idea of what you want. twogilvy@msn.com If you want to try to do it yourself, I think this page a Ron deBruin s site has code that...

Excel: Help needed, cell a1, sheet1

cell a1, sheet1, headings: Hi Ankush, Hope you are doing well. Assumptions 1. Sheet1 - A1 - start date. A2 - end date 2. sheet 2 - you need the headings from cell a1 onwards 3. looks like you need at max 13 columns (12 months + 1 total) as you do not want to show the year...

Excel: Help with organizing lists, wedding tulle, name badges

wedding tulle, name badges, macro in excel: Hi, Crystal! I am so sorry for the delay in my response. I was over a thousand miles away from my PC. :) I hope that you have gotten an answer by now, but just in case... First of all, it definitely sounds like you should be using ACCESS. However......