About Experts Sitemap - Group 76 - Page 5 2012-07-27
Excel: excel, excel spreadsheet, random numbers
excel spreadsheet, random numbers, b1: David: I m pretty sure I do not understand the question. So, you have random numbers in columns B through CW (100 columns) in rows 1 through 100. If you want a SUM of columns 2 through 100 (B through CW), then you would place this formula in cell...
Excel: excel, douglas m smith, cell borders
douglas m smith, cell borders, cell a1: Hi Joe, A formula is programming statement in a cell that calculates or manipulates data in some way. Examples: Add the numbers in cells A1 and B1: =A1+B1 Add up all the values in cells A1 through A20: =SUM(A1:A20) Get the 5 left characters...
Excel: excel, data validation, phase project
data validation, phase project, project names: Hi Sue, My apologies for this disappointing answer. The task is a bit complex, mainly from understanding the requirements point of view. I am currently not in a position to give continued attention to this due to my professional commitments (I am a SAP Project...
Excel: excel, pivot table tutorial, google search
pivot table tutorial, google search, google: It seems to me that you just need to learn a couple of features of Excel. They are: Autofilter: http://www.officearticles.com/excel/using_autofilter_in_microsoft_excel.htm Subtotals: http://www.officearticles.com/excel/getting_subtotals_in_microsoft_excel.htm...
Excel: excel, data validation, cell a1
data validation, cell a1, phase project: Sue Yes it is possible to create a drop down menu that contains a list of projects from which to select. Use the data validation feature. go to toolbar/data/validation, select allow list, create the list and put the reference to the list in the source...
Excel: excel, phase project, project names
phase project, project names, project phase: For the first part, the answer is yes, but i m not sure what it is you want to achieve - I can think of two possible scenarios (1) use the data menu to select filter, autofilter - this would give a list of projects and the user can filter it on a specific...
Excel: excel, data validation, pivottable
data validation, pivottable, phase project: You can create your drop downs using Data Validation - see http://www.excel-it.com/data_validation.htm. If your projects are graded using a points system then you could possibly use a PivotTable, I have an article with examples also on my site. I m not...
Excel: excel, vba code, microsoft website
vba code, microsoft website, bonne chance: Bom jour, Michel, thanks for writing in. Excel doesn t have any kind of dialog box that produces this type of search. It sounds like a great idea, though! I suggest that you go to the Microsoft website and submit this as a feature for a future version!...
Excel: excel, phase project, project names
phase project, project names, project phase: hi let me see if i understand. basically you would have a table with a column with the project name and other columns with information about the project. each line of this table would represent all the information about one project. what you want is...
Excel: excel, master spreadsheet, circular reference
master spreadsheet, circular reference, spreadsheets: Bob It isn t really possible due to circular reference problems, ie, you update one sheet and it updates in another sheet because it is linked to the orignal sheet, if the linked cell is updated you overwrite the link in that cell. Sorry I couldn t...
Excel: excel, row headings, column c
row headings, column c, pivot table: The EASIEST way to do it is with a pivot table (data menu, pivot table and chart report - follow the wizard and drop the NAME area into the row headings area, the numbers in the area next to it) Otherwise, a series of SUMIF statements would do it - but...
Excel: excel, column c, b8
column c, b8, b3: Dennis If you are only doing this once then this formula will do it =sumif(A3:B8, Barry ,B3:B8) where A3:B8 is the range and B3:B8 is the numbers. If you are doing it with a lot of different names then you can change the formula to reference a cell...
Excel: excel, data validation, answer right
data validation, answer right, thanks in advance: Right click the sheet you want to copy and select move or copy, then copy it - this will copy the entire sheet - formatting, contents, header and footer etc. For the dynamic list to work it would need to be in the active workbook, although as long as it s...
Excel: excel, change case, proper case
change case, proper case, sht: There isn t an inbuilt way of doing this in Excel, so options are (1) copy and paste into word, select all, format change case, copy and paste back into Excel (2) use a macro - Sub caser() dim cell, sht for each sht in activeworkbook.worksheets...
Excel: excel, empty column, client names
empty column, client names, secure id: Hello Sue, If you create a lookup table, then you would have client names in column A and the secure ID code in column B in let s say Sheet2. But if you do that, why not just sort the lookup table in the same order you have the client names listed on...
Excel: excel 97 combo box, combo boxes, combo box
combo boxes, combo box, input range: Hi John, How are you? I have Excel 97 no more but it must be pretty the same as Excel XP in that case. Please try the following: 1st. Draw the ComboBox View - Toolbars - Forms 2nd. Right click the mouse on it Format control - control 3rd. Define the...
Excel: excel 97 and xp, eccetera, excel 97
eccetera, excel 97, office 97: 97 and XP are both upward and downward compatible. The location has nothing to do with opening the file. Something else is wrong somewhere perhaps in the code but that is unlikely also. And, FYI - eccetera is spelled etcetera, thus the abbreviation...
Excel: excel, variable formula, sum function
variable formula, sum function, preference: Various ways of doing it - my own preference would be to total an entire column, which would give a fixed formula but ActiveCell.FormulaR1C1 = =SUM(R[- & 11 & ]C:R[-1]C) would give you a variable formula - simply replace the 11 with a count variable...
Excel: excel 98 2000 xp, odd rows, microsoft 98
odd rows, microsoft 98, even rows: Hello, Kris, You can apply alternate shading to rows in Excel by way of conditional formatting. Let s say that the area in question is A1:J100. Follow these steps: 1) Select A1:J100. 2) On the menu bar, select Format/Conditional Formatting. ...
Excel: excel, hlookup, valid dates
hlookup, valid dates, t zero: Nick: I have uploaded a sample sheet here: http://home.swbell.net/nate-sus/excel/newdates.xls I have added two new rows of data in row 2 and 3 - these will assist in calculating the valid values you are looking for... Row 3 - This row determines...
Excel: excel, invoice numbers, invoice number
invoice numbers, invoice number, visual basic for applications: If you don t know how to use Visual Basic for Applications (vba) coding, you can t do this. If you do know a little about vba, here s sample code. This code looks for a list of invoice #s on sheet1, beginning in cell A1, continuing to the first blank cell...
Excel: excel with acrobate, acrobate reader, adobe acrobat
acrobate reader, adobe acrobat, google: You can purchase the full version of Adobe Acrobat, and it will integrate with Excel and allow you to transform Excel files into PDF. You will still need to craft some code to mail it automatically. I haven t tried other options, so I cannot recommend other...
Excel: excel active cell, target address, layout hope
target address, layout hope, cell address: You can use the SelectionChange event on the worksheet to make the change every time you select a different cell, I don t know if this is exactly what you are looking for. In any case, this code may give you some ideas: Private Sub Worksheet_SelectionChange(ByVal...
Excel: excel add-ins, microsoft knowledge base, excel objects
microsoft knowledge base, excel objects, addin: workbooks( Addin1.xla ).worksheets( Sheet1 ).Range( A1:A10 ).copy Destination:=Activesheet.Range( B9 ) For code in the addin itself, you can refer to the addin with ThisWorkbook. so for the above ThisWorkbook.worksheets( Sheet1 ).Range( A1:A10 ).copy...
Excel: excel arrays, array formulas, retreiving data
array formulas, retreiving data, worksheet functions: Hi there, array formulas with worksheet functions are generally faster than any VBA coding equivalent. Some excellent in-depth discussion with examples and tools can be found at http://www.decisionmodels.com/optspeedj.htm. See the SUMIF example. It...
Excel: excel auditing formula, addition formulas, colored border
addition formulas, colored border, pivot tables: This does indeed sound suspicious, Betsy, as the Trace Precedents button on the Auditing toolbar should indeed show *all* of the precedents. Try another way that s even easier: Click on the formula and press F2. F2 activates the formula bar. Once this...
Excel: excel auto fill in, microsoft excel, melinda
microsoft excel, melinda, novice: Hi, Melinda! My tutorials are here: http://www.officearticles.com/tutorials/ The one that goes with that form, and explains how it s made is here: http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm I hope it...
Excel: excel autonumber, visual basic programming, duplicat
visual basic programming, duplicat, programming solution: Hi Luis, This site gives some ways to deal with duplicates. http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates However numbering them would most likely require a Visual Basic programming solution. Something that is beyond me. Thanks...
Excel: excel autosave add-in, security reason, hand pane
security reason, hand pane, open application: Not that I am aware. Even if you saved the file as an Add-in (.xla) then installed it into a worksheet, the add-in would still rely on macros to perform the custom function. If you have your security feature set to disable macros, it will disable all macros,...
Excel: excel; averaging blank columns, blank cells, empty cells
blank cells, empty cells, spread sheet: Randall Ck a couple of things 1--usually the ###### signs indicate an amount that is to large for the cell. Reformat the cell so that it shows only a couple of decimal places and see if that makes it go away. 2---Paint all of the cells that the average...
Excel: excel bell curve, faulty assumptions, bell curve
faulty assumptions, bell curve, monetary amounts: Can you check out this website? http://home.swbell.net/nate-sus/excel/bellcurve.xls I have made the spreadsheet fairly adjustable. You can change the following items: 1) Number of original starting data...you can currently put in up to 20 values,...
Excel: excel and button, homework question, editor menu
homework question, editor menu, excel sheet: Hi whocracks, Although this sounds like a homework question I will help. Go to the Excel menu and select View then Toolbars then Forms . On the Forms toolbar click the Button button then drag anywhere on the worksheet to draw the button. Press...
Excel: excel buttons, active x controls, lesion
active x controls, lesion, spreadsheet: Hi Adam Yes what you wish achieve can be done by VBA. Do what you ask requires requires knowledge of the visual basic editor and adding active X controls to forms and then writing the required code. DO you have any experience with these activities?...
Excel: excel cell content comparison, cell interior, content comparison
cell interior, content comparison, cell content: You need vba to do that. Here s a sample comparing sheet1 & sheetA: Sub Compare() Sheets( Sheet1 ).Activate For Each cell In ActiveSheet.UsedRange If cell.Value Sheet2.Range(cell.Address).Value Then cell.Interior.ColorIndex...
Excel: excel cell content comparison, content comparison, cell content
content comparison, cell content, select statement: Hi, Part I Addition of sheets in the required order- POssible through MACRO. Sheets( Sheet1 ).Select Sheets.Add Sheets( Sheet4 ).Select Sheets( Sheet4 ).Move Before:=Sheets(3) Sheets( Sheet4 ).Select Sheets( Sheet4 ).Name = SheetA...
Excel: excel cell format, professional answer, question the question
professional answer, question the question, office 2000: Other than changing the font size manually each time, the only way is with a macro, which I rarely wtite for people on this site, especially on my own free time. In your case, the macro is fairly complicated and will definitely take more of my free time than...
Excel: excel cell multiple copy word, word text box, copy word
word text box, copy word, excel sheet: Not enough information to provide an easy answer - are you talking about a word document which should link to a block of data in excel (if so, copy that block, then use the edit, paste special command in word to Paste Link). Are you talking about doing this...
Excel: excel cell protection, spreadsheet formulas, format cells
spreadsheet formulas, format cells, excel 2000: By default, all cells are marked as locked. Select the cells you want unlocked and select format= Cells, then go to the protection tab and uncheck the Locked checkbox. Now you would go to Tools= Protection= Protect Worksheet and in Excel 2000, select...
Excel: Do all excel charts have the..., excel charts, aidan
excel charts, aidan, axes: No they don t - they have the x axis as the appropriate scale for the chart you are doing, and you can state that the data is in rows or columns, which in itself would change the X and Y Axes. And the second part of the question is impossible to answer as...
Excel: excel charts, secondary axis, unrelated data
secondary axis, unrelated data, excel charts: Assume you want a shared X-axis and two separate y-axis. Assume your data is in A2:C11 for example with A2:A11 being the x-axis, B2:B11 being series one and C2:C11 being series two. Create you graph normally using the wizard. If it makes three series,...
Excel: excel charts, excel charts, macro recorder
excel charts, macro recorder, data source: Devimeena, it may be most helpful for you to simply use the macro recorder to see how Excel would set those parameters. A simple starting point would look something like this: Range( A1:D15 ).Select Charts.Add ActiveChart.ChartType = xlLine...
Excel: excel colour change by number value, colour change, caveat
colour change, caveat, trial and error: Hi Andy, You are in luck. There is a property called conditional formatting. Highlight the range that you want to change automatically and choose Format/Conditional Formatting. You then are allowed up to 3 different conditions and you could choose Cell...
Excel: excel command buttons, command buttons, command button
command buttons, command button, excel worksheet: Hi Kerry, You can try something like this. Private Sub CommandButton1_Click() Set myRange = Worksheets( Sheet1 ).Range( A1:A7 ) answer = Application.WorksheetFunction.Sum(myRange) Range( B1 ).Value = answer End Sub ...
Excel: excel command scripts, command scripts, detailed explanation
command scripts, detailed explanation, excel 2000: Hey Nick, You can definitely use a macro to do this. I couldn t tell from your question if you are familiar with macros or not? It would probably help if you could email me your spreadsheet so I can write the macro for you. It was hard to judge from...
Excel: excel compare two sheets, cell d1, cell a1
cell d1, cell a1, trues: I m assuming that you want the simplest method, NOT one that requires knowledge of programming (vba)? A simple way to this is to put in cell D1 of Sheet1 the formula: =NOT(ISERROR(MATCH(A1,Sheet2!A:A,0))) This formula will return TRUE if the title...
Excel: excel conditional list, data validation, column updates
data validation, column updates, drop down boxes: Jason: I can only suspect that your Internet browser is not updating with the latest file. As such, I have copied a new file with a different name here (to force your browser to use the new file): http://home.swbell.net/nate-sus/excel/partlist2.xls...
Excel: excel countdown timer, timer value, countdown timer
timer value, countdown timer, new timer: I understand the concept and it seems to me that the sheet calc issue might still be the problem. The time it takes to calc the sheet at the prescribed interval may be throwing off the counter just a little bit. Are there any other formulas involved in all...
Excel: excel countif formula, excel countif, a25
excel countif, a25, good afternoon: Good afternoon! Presuming your dates are actually dates and just just a year (ia, 1/23/2003 and not just 2003 ), then this is the formula you would use: =SUMPRODUCT((YEAR(date)=2003)*(hours=0)*(1)) This is sort of a multiple-sumif function. It looks...
Excel: excel cross reference, sheet1, vlookup
sheet1, vlookup, masterlist: Phew, it was hard absorbing all of that :-). As I read it, you do not have POLICY# on sheet 1 or 2, you only have busines name and address, so how can you look it up? And what do you mean by ... must match in the master list (horizontally and vertically)?...
Excel: excel data coming from company reporting system, product titles, thousand miles
product titles, thousand miles, reporting system: Hi, David! 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... I have an article that explains exactly how to do this at www.officearticles.com/excel....
Excel: excel data manipulation for export, lincoln center for the performing arts, visa petitions
lincoln center for the performing arts, visa petitions, data manipulation: It s a little bit of a setup, but if your data is in cells A1:I501, for example, do this: in J2 enter =A3. Fill this right to cell R2 (9 columns). In S2 enter =A4, fill right to AA2. In AB2 enter =A5, fill to AJ2, and finally in AK2 enter =A6, fill to AS2....
Excel: excel database creation, csv file, employee evaluation
csv file, employee evaluation, database creation: Search for a database? A separate file? A dbf or dbase file? A csv file? Or just a range? Finding if a name exists in a range is pretty easy, but it s not clear yet if that s the case. Create it? Create what? A database? A row containing the name? Please provide...
Excel: excel date formatting, serial nummber, 6 digits
serial nummber, 6 digits, e mail: I haven t been able to duplicate this no matter what I do - however, you have mentioned custom format but haven t said what format you are using - is it possible to see a sample of the data (my email is aidan.heritage@virgin.net) as this may shed some light...
Excel: excel date formula, date function, exact criteria
date function, exact criteria, cell a1: Scott: Well, I can provide an answer, but you will need to tweak it to your situation (since I don t know your exact criteria)... =DATE(IF(A1=1,2007,2008),12,31) The above formula will put 12/31/2007 into a cell if A1 equals 1. If cell A1 equals...
Excel: excel date, excel sheet
excel sheet: ...if the box is 0... .. The box ? What the box are you referring to? And, ... changing all previous entries... What previous entries? Remember, I can not see what you see or know what you are thinking or have designed. ALL I know is ONLY what...
Excel: excel delete columns, column letter, blank records
column letter, blank records, click method: To delete an entire column, right click on the gray box that contains the column letter ( G for instance). A shortcut menu will appear with an option for Delete . Or... You can also left click the gray box with the column letter to select the entire...
Excel: excel deleting rows with a macro, microsoft excel 2002, atributes
microsoft excel 2002, atributes, colums: Oma I see what you are trying to do with the macro. Since the macro is specific in its coding I can t create a macro for you without knowing the specifics of your database, i.e. cell ranges, etc. However you probably can program the macro yourself by...
Excel: excel document cannot be accessed, excel programs, backup copy
excel programs, backup copy, memory stick: It sounds as though the CD has become damaged, which is making it impossible for Excel to read the file - I would try to copy the file to the hard disk, then IF that works, attempt to access it from there - IF it is an important file, ensure you have a backup...
Excel: excel drop down list, steel numbers, excel drop down list
steel numbers, excel drop down list, data validation: First the drop down to see if this is what you want. To create that select any cell and select Data, Validation. Choose List in the Allow drop down. When the Source drop down appears select the range where your steel numbers are and click OK. This will...
Excel: excel drop down menu, data validation, excel sheet
data validation, excel sheet, drop down menu: Ashish - The only way I know how to do this is to use the Forms Toolbar, and insert a ComboBox. But other than circumstances where you re creating a fill-in form where you don t want people to see the real spreadsheet, this is usually more trouble than...
Excel: excel dropdown box, excel dropdown, combobox
excel dropdown, combobox, first selection: Nick, it takes 2 pieces of code. The first resides in the This Workbook object for the workbook. It just needs to look like this: Private Sub Workbook_Open() Clearit End Sub Then you need a module to do the work for you, like this: Sub Clearit()...
Excel: excel dsum(), dsum, lotus 123
dsum, lotus 123, lotus 1 2 3: You are correct, DSUM in Excel requires a criteria range. I did some research on this and found there is no real way to force DSUM to use in-formula criteria. However, might I suggest SUMPRODUCT? It is extremely useful in doing multi-criteria database...
Excel: excel error message from start/documents, long file names, system expert
long file names, system expert, c documents: This is an operating system error, as if you open the file from within Excel it is fine - it sounds like the operating system is having problems with the long file names, but I m not an operating system expert, nor do I know which OS you are using - it is...
Excel: excel error, report sheets, autofit
report sheets, autofit, range selection: Since your code does a lot of selecting you need to select the sheet BEFORE you can then select a range to then paste. Typically, ActiveSheet.Range( a2 ).Select will cause an error and therefore you should ActiveSheet.Select and then Range( a2 ).Select...
Excel: excel to excel data transfer, douglas m smith, brainbench
douglas m smith, brainbench, sheet1: Hi Tarik, Your active directory is probably not the same directory the files are in. (Try to open a file and see what directory the dialog box is looking at before you run you macro.) To fix this issue, add this statement: ChDir ActiveWorkbook.Path...
Excel: excel external links, comp1, togeter
comp1, togeter, documents and settings: 1. Sub Macro1() 2. Range( F12 ).Select 3. comp1 = Range( b1 ) 4. yr1 = Range( a1 ) 5. make1 = comp1 & ua & Right(yr1, 2) 6. ActiveCell.FormulaR1= _ C:Documents and SettingsDesktop[ _ & make1 & ]Sheet1!R2C2 7. Range(...
Excel: excel Function, excel function, seligmann
excel function, seligmann, value1: Jose, The easiest way to accomplish this is to type the function right into the cell. Thus, in cell A3 type =(a2)^2 + a1. However, you can make a custom function if you wish. Here is the custom function for that formula: Function Jose(Value1, Value2)...
Excel: excel filtering?, array formula, array formulas
array formula, array formulas, weird numbers: =SUM(IF((A1:A26= Portland )*(E1:E26= Lights ),1,0)) or =SUM((A1:A26= Portland )*(E1:E26= Lights )) Either Entered with Ctrl+Shift+Enter rather than enter since they are array formulas. or you can use sumproduct for this (entered normally) =Sumproduct(--(A1:A26=...
Excel: excel filtering, excel 2000, spreadsheet
excel 2000, spreadsheet, windows 2000: You can use advanced filter with the right criteria. If your data goes out to column AF, you can enter something like =NOT(ISERROR(MATCH($AH$1,B3:AF3,0))) where AH1 contains the name you want, and row 3 is the first row of possible names. The result will...
Excel: excel forecast question, linear regression, row 24
linear regression, row 24, future step: Hi Tara, Okay, I m going to assume you have a set of data for months past and you now want to forecast the values for the next 18 months into the future. Let s say you currently have past date values in column A (call these X values) and the values you...
Excel: excel format, excel formula, lmco
excel formula, lmco, ostrander: Hi Gwen, It is not clear to me what you want. Could you provide me some more information about what data you have, and what you want as a result? If this is difficult to explain with text and you would like to email me a sample workbook. My email address...
Excel: excel formatting, conditional formatting, format button
conditional formatting, format button, e20: Hi Elaine, You can use Conditional Formatting to cause C4 to fill with black if B4 contains text. To do this select C4, then Format Conditional Formatting. For Condition 1 select Formula Is , and enter =LEN(B4) 0. Then click the Format button and on...
Excel: excel formatting, cell a1, running office
cell a1, running office, fourth column: Jerry If you are using the RANDBETWEEN() function I don t know why you would be getting the two random numbers. As far as I know it should only produce one. If you are using the Random number generator in the data analysis toolpak it can generate 1...
Excel: excel forms, excel forms, spreadsheet
excel forms, spreadsheet, ins: Carol What you do is create the form or format that you want on the spreadsheet, and then save the form. As the form is used I.E. when you fill in the information on the form, you can then save it under a different name, so your template form remains blank...
Excel: excel formula, category award, excel formula
category award, excel formula, start something: there are several ways to do this, including VBA, but without really knowing more details, I don t think I can help. If your problem is that you re running into the 7-nesting limit you can try, as a start, something like this, which has virtually no limit:...
Excel: excel formula, briefcase yahoo, blank cells
briefcase yahoo, blank cells, doing something wrong: Well, the formula looks at row 4 and row 4 has not blanks. If I adjust it to look at row 2, then it should work: =SUMPRODUCT((A2:S2 B2:T2)*(A2:S2 )*(B2:T2= )) It seems this adjustment would be somewhat easy to spot. I didn t memorize your example...
Excel: excel formula, machine groups, excel formula
machine groups, excel formula, numeric data: Hi joseph How are you? I m not sure if I ve understood your question. But please see the functions Countif, Sumif anfd frequency. if the data are numeric the frequency is the best function to count the number of times a category is repeated in the...
Excel: excel formula, application index, golf score
application index, golf score, excel formula: Even though I came up with the correct formula, Excel came up with a bug! The formula is array-entered (ctrl/shift/enter): =AVERAGE(OFFSET(A1,0,LARGE(N(A1:K1 )*COLUMN(A:K),COLUMN(A:C))-1)) This assumes you re entering it in L1 (or later), and the golf...
Excel: Is there an excel formula for this?, simplicity sake, excel formula
simplicity sake, excel formula, a100: Hi, Matt, For simplicity sake, I ll just assume that you re looking for just two numbers to make up the sum. Let s say that your numbers are in A1:A100, and that the sum you re looking for is 123.45. Try this: Enter the following formula in B1: =IF(ISERROR(MATCH(123.45-A1,$A$1:$A$100,0)),...
Excel: excel formula, size column, excel formula
size column, excel formula, index range: This can be done with a variety of lookup formulas such as VLOOKUP, INDEX, etc. I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/vlookups.xls Here is a breakdown of how VLOOKUP works: =VLOOKUP(item you want to index,...
Excel: excel formula, cell b2, tiger wood
cell b2, tiger wood, excel formula: =if($B2= , ,Vlookup($B2,sheet1!$A:$E,2,False)) =IF(B2= Woods ,VLOOKUP(B2,Sheet3!A:E,2,FALSE)) Yours doesn t look like what I suggested. That doesn t mean it isn t what you want. You have to be the judge of that. The Vlookup Portion looks properly formed....
Excel: excel formula, excel formula, column c
excel formula, column c, line x: Hi brenda, I am assuming the following: 1. Sheet 1 has the first table. Row 1 has the header and data is from row 2 onwards (Columns A and B only). New columns start from Col C. 2. Sheet 2 has the second table. Row 1 has the header and data is from 2 onwards...
Excel: excel formula, absolute references, excel formula
absolute references, excel formula, cell a1: Yes, this is the SUMIF function. In your example, the RANGE would be b1:b3. the CRITERIA would be red the Sum_range would be a1:a3. You may want to put absolute references ($B$1:$B$3) around your range and Sum_range if you intend to have one formula...
Excel: excel - formula, excel formula, coverts
excel formula, coverts, excel worksheet: Dear Mamdani, As the number includes a number with two different notations like 47th over and 1 ball is equal to 47.16 i.e it includes over and ball. They are to be segregated. In simple you can work out it by putting 47 in B column and 1 in C Column there...
Excel: excel formula, alphabet letter, absolute function
alphabet letter, absolute function, excel formula: Hi Stephan, That s a great question. Put the $ sign in front of what you want to remain constant. You want the alphabet (or column) to remain constant. So if you have a formula =A1 then you would put the $ sign in front of the A. Such as =$A1 that way...
Excel: excel formula, excel formula, rg2
excel formula, rg2, rg1: Very involved formula if I can come up with it at all, but clearly a simple UDF. Why shy away from UDF s? I would say I wouldn t bother spending the time (probably 1 hour) to simulate the power of the VBA like function, and that s way beyond the AllExperts...
Excel: excel formula, logical test, relative reference
logical test, relative reference, excel formula: Hi Andrew, This is an IF function. The basic IF Function works this way. IF(logical_test,value_if_true,value_if_false) So notice in your IF function where the commas are, they separate the function into three parts. The first part is the logical_test....
Excel: excel formula, excel formula, a170
excel formula, a170, what this means: If the value of P7 is greater than 0.5 then perform this calculation: Subtract the value in P169 from the value in P7. With the results of the subtraction, divide those results with the product of the value in cell P170 times the value in cell A170. ...
Excel: excel formula, excel formula, b1
excel formula, b1, true meaning: Simple example: enter a in B1 and b in b2. In c2, enter: =B2 B1 C2 will display TRUE, meaning that B2 is alphabetically higher than B1. If you chang B1 to c , then C2 will display FALSE. If you want to actually convert letters to numeric values,...
Excel: excel formula, excel formula, what this means
excel formula, what this means, spreadsheet: If the value in P7 is larger than .5, then: subtract the value in P169 from P7, divide that by the value in P170, and multiply that by the value in A170, then add in the value in A169. Otherwise: Put a space in the cell. HT...
Excel: excel formula, array formula, error values
array formula, error values, excel formula: Here s a simple example. Say you have data in range A1:A5, but one or more of the cells may have error values. You want to find the sum of A1:A5, while ignoring any error values (i.e., treating the error values as if they were zeros). The formula is: ...
Excel: excel formula, drop down boxes, format button
drop down boxes, format button, excel formula: Hi Gabriel, It sounds like your worksheet is protected. You can go to the Excel menu and select Tools then Protection then Unprotect then click OK . If you did not create this workbook and someone else entered a password for the protection you would...
Excel: excel formula, target value, cell a1
target value, cell a1, counter application: Hi Dick, This requires a little more than a formula. But you can copy and paste the code at the bottom of the reply and it will work for you. I recieved some help on the code from James at www.mrexcel.com. Here are the instructions to copy and paste...
Excel: excel formula, excel formula, cuser
excel formula, cuser, option name: Dick, there isn t a formula for making that happen. You either have to use VBA code to force the cell selection, or you have to select all of the cells in the form that will be edited, then name the range. Then when you want to enter data into the form, you...
Excel: excel formula, cell b1, column chart
cell b1, column chart, excel formula: syed, If your range values are in column A, copy and paste the formula below into cell B1, and then copy that formula down as far down as your values in column A. i.e. If you have values in A1:A30, copy your formula from B1:B30. =IF(A1 =16,2, 2.45)...
Excel: excel formula, excel formula, zero values
excel formula, zero values, options menu: Easiest way (if zero values shouldn t appear) is to use the tools, options menu, then the view tab and uncheck zero values. Alternatively, you can use =if(d3 0, ,days...) which will put a space in rather than a zero. You also don t need the + sign...
Excel: excel formula, excel vlookup, excel formula
excel vlookup, excel formula, column number: Yes, the formula you need is vlookup - it s probably the single most useful function in Excel, so much so that at one job I used to answer questions about excel by saying It s vlookup, now can you tell me the question I have a page on my website that...
Excel: excel formula, briefcase yahoo, blank cells
briefcase yahoo, blank cells, excel formula: Rather than fighting Excel for this one, I would create a custom function - to do this, go into the VBA editor (Alt F11) and insert a new module into your worksheet Put this in place Function GetBlank(myrar As Range) Dim blankcount As Long Dim blankmark...
Excel: excel formula, briefcase yahoo, blank cells
briefcase yahoo, blank cells, excel formula: Try this and let me know if it works as expected (it works using your small sample size) Place this formula in cell A4: =IF(A3=0,0,1) The above formula is going to be different than all the rest simply because it is the first cell and we cannot compare...
Excel: excel formula...? function?, excel formula, random function
excel formula, random function, mathematical function: Hi Francisco It sounds good, but you we also have to input also some patterns to be checked. For example, odd or even numbers, prime numbers, special series such as Fibonacci or some mathematical function. We can do VBA program for checking the patterns...
Excel: About excel formula with multicolumn, array formula, database column
array formula, database column, excel formula: It looks like the formula you are using works as an array formula, it should entered using Ctrl+Shift+Enter, to get the correct results. In any case, you can use SUMPRODUCT instead, that doesn t require anything special. With your example, the equivalent...
Excel: excel formula needed, target cell, len value
target cell, len value, excel formula: Dutch, I can t think of a way to do it with the built in functions of Excel. However it can be done with a user defined function. To do that do the following: 1. Press Alt+F11 to open the visual basic explorer. 2. Click on the Insert menu and choose...
Excel: excel formula problem, douglas m smith, cell b1
douglas m smith, cell b1, max force: Hi Jack, Sorry about the delay. You can use SUMIF and COUNTIF to do this: =(SUMIF($A$1:$A$20, =26 ,$B$1:$B$20)-SUMIF($A$1:$A$20, 25 ,$B$1:$B$20))/(COUNTIF($A$1:$A$20, =26 )-COUNTIF($A$1:$A$20, 25 )) Column A is the angles and B is the corresponding...
Excel: excel formula problem, excel formula, hi jack
excel formula, hi jack, added advantage: Hi Jack, Because I think your formula would become excessively complicated by incorporating the additionaly pos/neg test, I suggest you do the following: 1. add an additional column containing the following formula =IF(AND(A2 =25,A2 26),IF(B2 0, POS...
Excel: excel formula problem, vlookup, excel formula
vlookup, excel formula, max value: What you need is a lookup-type formula that uses the max as the lookup criteria. Just pick whichever lookup function you d like to use and set the criteria to be the max of the column. For instance, here s an example with a VLOOKUP: =VLOOKUP(MAX(A1:A20),A1:B20,2,0)...
Excel: excel formula, visual basic programming, category award
visual basic programming, category award, excel formula: Hi Elaine, As I mentioned I would get back to you. I believe programming be your best solution. Here is a look at the programming that I came up with. For x = 1 To 100 Step 2 If Range( B3 ).Value = x And Range( C3 ).Value = x + 1 Then ...
Excel: excel formulae, excel formulae
excel formulae: You would need to have a macro - but it s not clear how you would want to fire this - logically you would have EITHER a cell where you are entering something and when this is changed a value is set, or you could have a button to press which would set the cell...
Excel: excel formulas with 2 criteria, array formula, excel formulas
array formula, excel formulas, c13: Hi Roger, One of my favourite topics. You could do this with a SUM(IF array formula, but it is easier to use SUMPRODUCT =SUMPRODUCT(--(C5:C13= ? ),--(B2:B13= ? )) If you want to read more about this function and how to use it, check http://www.xldynamic.com/source/xld.SUMPRODUCT.html...
Excel: excel formulas, excel formulas, autofilter
excel formulas, autofilter, hi chris: Hi Chris, There are a number of ways to accomplish this. Most of the deciding factors will be on your comfort level with the functions and the layout of your data. Please check out the following functions / features: a. =CountIF() function b. AutoFilter...
Excel: excel freeze, e28, d6
e28, d6, b6: By defintion of the freeze pane option, ONLY the data existing both in the column to the left of the freeze and the rows above the freeze will remain frozen. For example, if you freeze B7, then you get the following: A1 through A6 is completely frozen...they...
Excel: excel function, excel function, allexperts
excel function, allexperts, ogilvie: Matt Sorry to say but I haven t come across this one and the research I have done doesn t lead to think it can be done. I would suggest you try Tom Ogilvie at allexperts as he has more knowledge with VBA which may be a way of doing what you want. Richar...
Excel: excel function, logical test, excel function
logical test, excel function, copy functions: huh? Try rephrasing your question with a little more detail. IF I understand what you are trying to ask then: =IF(A1=10, what to do if true , what to do if false ) change the false portion of your IF statement as the example shows below to have the...
Excel: excel function, demo table, illustration purposes
demo table, illustration purposes, excel function: Start your demo table in cell A2 (i.e., the number 1 in cell A2, the letter A in B2, etc). In C2 enter the formula: =IF(A2 A1,B2,C1& , &B2) then copy it down col C as far as your table extends. In col E, starting cell E2, enter the numbers...
Excel: excel function for church offering records, cell b1, cell a1
cell b1, cell a1, excel function: If it s really just for printout, then it d be easiest to keep it on one sheet and simply hide columns! You can check out View/Custom Views to easily access just the left column and the person s column, by name so you aren t stuck with the manual process of...
Excel: excel function for church offering records, excel function, separate pages
excel function, separate pages, excel 2000: Hello, Jeff, While this is possible, it s a little more involved than can be handled for free on this site. Either my partner or I would be happy to set this up for you at a modest fee. Please let me know if we can help you! You can contact me at...
Excel: excel function, a5 e5, excel function
a5 e5, excel function, yair: Hi Yair, How are you? The following is simply but not entirely what you like: 1. Your data ia in A4:E5 2. In A6, type =IF(A5=MAX($A$5:$E$5), OFFSET(A5,-1,0), ) copy to B6:E6 or 1. tools - macro - visual basic editor 2. insert a module 3. copy...
Excel: excel functions, target address, counta
target address, counta, t cells: Hi Bob How are you? Firstly, I am using Excel 2003 ... This likely applies to Excel 97, but not surely. I wrote a macro to solve your problem Sub Worksheet_Change(ByVal Target As Excel.Range) Dim RangeChanged As Range, t As Range Set t = Range(...
Excel: excel-like functions in MSAccess - with VBA?, exact version, msaccess
exact version, msaccess, program software: Four I don t think you are asking the right guy. When I hear the word Access, I hear bloated beast. I would say that if you are able to do it in Excel, do it in Excel. You can always send the data back to Access. I know, that doesn t answer the question,...
Excel: excel functions, different shape, excel functions
different shape, excel functions, negative numbers: Hi Elisabeth How are you? You have to follow the procedure exactly as I told you. But of you need some trainingm please try the following 1. Type 4 in A1 2. Type 2 in B1 3. Type 3 in C1 4. Type in D1 =A1*b1-c1 it must return 5 Cheers Adelaid...
Excel: excel goal seek, rate conversion, conversion functions
rate conversion, conversion functions, functions library: I am not really sure what you want to do or that you have given me enough informaton. My guess is they used the Rate function RATE(nper,pmt,pv,fv,type,guess) it does use an iterative approach to calcualte the answer. You also might look at this...
Excel: excel goal seek, financial formulas, interest calculations
financial formulas, interest calculations, aidan: Possibly seeing the sheet might lend some clues (my email is aidan.heritage@virgin.net) . However, Goal Seek will set a given cell to a given value based on changing one cell, so the formulas should (I would imagine) still be in place - there are a number...
Excel: excel graphics, visual basic programming, excel graphics
visual basic programming, excel graphics, data validation: Hi Michael, I am sorry I do not know the answer to that. I do know that you can conditional format the source data. You can do this by selecting the source data then go to the Excel menu and select Data then Data Validation then select from the drop-down...
Excel: excel help, cell c5, d18
cell c5, d18, d19: I m sure the question was clear in your mind, but it didn t arrive in a very clear fashion at this end! Are you BASICALLY wanting a change in column D to update the details for the person referred to in the name column of that worksheet on the bonus sheet?...
Excel: excel help, spreadsheet template, data validation
spreadsheet template, data validation, client names: Not easily (if at all) I m afraid! A data validation list would show all available choices but wouldn t allow additions, and doesn t scroll the way you want - a VBA user form COULD be designed to work this way, but it s probably a sledgehammer to crack a...
Excel: excel help, text answer, sumif
text answer, sumif, excel help: I have tried the formula, and for 49.99 the formula gives 3.299, so we may need to adjust the thresholds, but it looks correct. One potential mismatch: I have started with 10% on cell L2, going down all the way to 2% on cell L6, maybe that is the reason....
Excel: excel, help me pls!, d2 d3 d4, aidan
d2 d3 d4, aidan, a9: This is going to be tricky, but before trying to work on something I need clarification - is it JUST the room no that should only be counted once, or is it the combination of room number and type of guests (so if we had room 905 with VPS and room 905 with...
Excel: excel help, cell a1, excel help
cell a1, excel help, excel sheet: Create your product list in column E by using Data, Filter, Advanced Filter, Unique. If you don t know how to use Advanced Filter, the Excel help should tell you everything you need. If you have specific problems with advanced filter, you can re-ask here....
Excel: excel hyperlink, folder files, free time
folder files, free time: Sorry, typically I do not write macros for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you first think and therefore take longer to write...
Excel: excel - incrementing a column of dates, arrow pointer, cell a1
arrow pointer, cell a1, leap years: Enter the formula in a column. Copy the column. Use the PASTE SPECIAL feature to paste values instead of the formula. There s also a trick I use from time-to-time... If you select a range and move your cursor to the very edge of the range it will turn...
Excel: excel inputing data, inputed, mouse movements
inputed, mouse movements, excel sheet: hI jAKE What you wish achieve can be done with an activeX control known as a refedit control .These can either be embbeded in the work sheet or within a user form. The ref edit control allows the selction of rectangular ranges by use of mouse movements....
Excel: excel limits, douglas m smith, consolidated document
douglas m smith, consolidated document, brainbench: Hi Lee, Excel s limitations aren t the problem in your situation. I ve created spreadsheets many times the size you are using without problems. Sharing is the problem. My experience with the Excel s sharing feature was a total disaster, not unlike...
Excel: excel line graph, line graph, left hand side
line graph, left hand side, radio button: Stephen, Once you have a basic chart built, you should see a chart that is pretty goofed up, since you have 2 scales for your data, all on one axis. So now, you will need to set the properties of the line that needs to be charted on the secondary y axis....
Excel: excel link to word, radio button beside, row numbers
radio button beside, row numbers, paste link: Hey Girlyn, I found these steps that I believe answer your question: Linking to an Excel Worksheet To insert a link to an Excel file, follow these simple steps: 1. Open both the Word document and the Excel spreadsheet 2. In Excel, copy the range of...
Excel: excel link, vba code, abilty
vba code, abilty, spread sheet: I think you re in the same boat here. I tried to even close Excel while it s playing with vba and it still keeps playing. Unless you can identify the application using vba then your abilty to control it is limited. You can follow heyperlinks with...
Excel: excel linking between sheets, techonthenet, combobox
techonthenet, combobox, xls: Hi Steve, Yes you can. VLOOKUP function is a good way to pull information from a small database. You can use a combobox to make the dropdown box. Here is an example workbook using both. http://www.squish.com/cloverleaf/dropdown.xls here s a site that...
Excel: excel - linkingcells in rows, cell b2, days of the month
cell b2, days of the month, months of the year: For example, say on your summary, you want to sum up cell B2 for each of your monthly sheets. I m assuming the first of the monthly sheets is named jan06 and the last one is named dec06 . The formula would be: =SUM( [outgings06-07.xls]jan06:dec06 !B2)...
Excel: excel links, link references, networked environment
link references, networked environment, server name: Hi, Niall, I don t work in a networked environment, but I d be glad to attempt a guess at what s going on. In a stand-alone environment, the references change depending upon whether or not the source file is open. If it is open, the link merely has the...
Excel: excel links, link references, networked environment
link references, networked environment, link thanks: Hi Niall, I am not an expert on this area. But I believe you can refer to a hyperlink by either going to the server name such as \server namefolderfile name. Or map a drive letter to a certain subfolder of the server. such as F:folderfile name. The...
Excel: excel lists matching (?), data columns, column names
data columns, column names, good answer: Hi Agnes, I am not understanding. If you can send the spreadsheet or a sample spreadsheet. That would help. If you do send a spreadsheet. Just include as much information on it as possible about what you are needing it to do. You can send sample spreadsheet...
Excel: excel lists, centre column, helen walker
centre column, helen walker, thanks for your patience: STEVE Sounds like you have been working with another allexperts person since I don t recall receiving a previous question from you. Maybe you want to readdress the question to whomever you were working with. or if not I ll be happy to try and help....
Excel: excel logical formula, logical formula, year 1
logical formula, year 1, aidan: It SHOULD work as written, if I have understood the problem correctly - I did test it and got one value per case, but in a different column depending upon the age now - which I took to be what you wanted - can you email me your file (aidan.heritage@virgin.net)...
Excel: excel look up formula, staging area, nervewracking
staging area, nervewracking, product tab: If tabs are named retention for bananas , for example, and the word bananas is in cell G3 of the active sheet, for example, you can get to the desired range via: =INDIRECT( Retention for &G3& !A1 ) to get A1 from the retention for bananas sheet. Note...
Excel: excel for Mac, mac expert, snapshot
mac expert, snapshot: hi eddie, It should be similar unless you are making calls to the OS (like getting folder information etc). Unfortunately, I am not a Mac expert so will not be able to help you out on that front. However, if you can send me a snapshot of the code, I might...
Excel: excel macro, false application, excel macro
false application, excel macro, true hope: Ido, I would suggest using the macro recorder to create some of this code to see how that works. It can be very informative. In any case, here s some code: To open a new workbook: Workbooks.Add Turns off the warnings, even fatal ones, saves the...
Excel: excel macro, excel macro, alert message
excel macro, alert message, line application: Ido, Actually, if you would have tried the Application.DisplayAlerts = False command, you would have found it good enough. Try this routine. Sub Ido() Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= c:idoaaa.xls End Sub...
Excel: excel macro, vbscript programmer, macro in excel
vbscript programmer, macro in excel, technical answer: There are many ways to accomplish this. Here is one that is fairly simple. Assign the last row with data to a variable and do the same to the first row that has data if you need to. I did not do the latter in my example for I assumed that the first cell...
Excel: excel macro, excel macro
excel macro: This works - you actually don t need to go to row 36013 because as rows get deleted, the last row moves up, but I left it in so you d see how it works: Sub Skip11thenDelete25() For i = 12 To 36013 Step 11 Rows(i).Resize(25).Delete ...
Excel: excel macro, spreadsheet tools, keyboard shortcut
spreadsheet tools, keyboard shortcut, personal workbook: 1) First you will need to create your macro - You can start by recording a macro to save your spreadsheet (tools- macro- record). Be sure to save the macro to Your Personal Workbook - that way it is available in any spreadsheet. When you are done recording,...
Excel: excel macro, format button, excel macro
format button, excel macro, rept: follow these steps exactly (assumes your data is in A1:E132) 1-in cells F1:F5, enter 1. 2-in F6 enter =F1+1 3-select F6 & fill down to F132 (the last row of data) 4-note the value of what s in the bottom cell (F132). For this example, it s 27. 5-in cell...
Excel: excel macro, keyboard shortcut, excel97
keyboard shortcut, excel97, excel macro: Not 100% sure on this one, but I think if you use the tools menu, macro, macros you can find the macros you use, then use the options button against each one and re-assign the keyboard shortcut - I THINK this should reset the shortcuts to work as you want...
Excel: excel macro error, vdata, macro error
vdata, macro error, xls file: Femi, I think you can use simpler code for that task, as follows: Sheets( Tabelle1 ).Select Filename = Range( D4 ) Range( A1:M50 ).Select Selection.Copy Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs Filename & ...
Excel: excel macro error, xls file, auto exec
xls file, auto exec, paste application: You said you have two issues but you list about 5. First of all, I am not familiar enough with the WINDOWS to know for sure but the only thing it can probably do is simply to open an EXCEL workbook and not run a macro. For the macro to start itself you will...
Excel: excel macro error, macro error, excel macro
macro error, excel macro, production numbers: You say that you are using EXCEL 2003 but it sounds like the macros are still in the ancient XLM version of macros, from EXCEL 4. [2005spring.xls]ScDataWorksheet!A2 is not a valid command or syntax in VBA. Are the macros is a separate sheet in the workbook?...
Excel: excel macro loop a row replace data in another, excel macro, c documents
excel macro, c documents, s5: This Dim LastColOfData as Byte Dim DynamicRow as Range LastColOfData = Range( IV1 ).End(xlToLeft).Column Set DynamicRow = Range( A1 ).Resize(1,LastColOfData) replaces this Set r = .Range(.Cells(25, A ), .Cells(25, IV ).End(xlToLeft)) I...
Excel: excel macro loops, excel macro, inbetween
excel macro, inbetween, spreadsheet: Always difficult to know where to pitch the answers - OK, open the VBA editor, then use the explorer panel to navigate to This Workbook - find the bar at the top that will show GENERAL and change this to Workbook - this should give you the Open event - put...
Excel: excel macro, step mode, excel macro
step mode, excel macro, excell: Try resizing the VBA window and laying it on top of the Excel window before running the code in step mode. With the VBA window shrunk and sitting on top of the Excel window you should be able to see the results of the macro as it runs and also see the cursor...
Excel: excel macros, excel macros, excel spreadsheet
excel macros, excel spreadsheet, memory stick: They can be in the file but not always. They are probably in the Personal.xls file - a hidden file EXCEL opened IF it has been created. Go to the VBE (Alt F11) and you will see the Personal.xls ON TEH MACHINE where you first created the macros. Copy...
Excel: excel macros excel xp /2003, macros excel, excel macros
macros excel, excel macros, best wishes: Suha, I think I can leave the recording to you. Just start the recorder (Tools/Macro/Record New Macro), perform some steps, and go from there. As for a macro that runs upon opening, you simply need to place the code in the This Workbook object, and tell...
Excel: excel macros, excel macros, relative button
excel macros, relative button, directional arrow: Peg, this is also something that you can record into a macro. When you begin recording the steps you want to take, note the recording toolbar that will be displayed. The first button is Stop Recording, the second is Relative References, a toggle button between...
Excel: excel mail merge, zip data, place hope
zip data, place hope, excel spreadsheet: Onalie This should work for you-- Use the text to columns feature to separate the .000000 from The rest of the zip code. First paint the entire column then go to toolbar/data/text to colums select the fixed width or delimited as is apropriate for...
Excel: excel for mailing labels, mailing labels, tool bars
mailing labels, tool bars, info thanks: It just means that field is text. You get it there by typing it. If you enter 123 into a cell you won t see the , but it will be there. (It s called the formula bar ). It has nopthing to do with spacing. And if you re typing a text field (like a name)...
Excel: excel for mailing labels, mailing labels, single quote
mailing labels, single quote, tool bars: It tells excel to treat the cell as text. for example, if you entered a zip code like 02077, excel would see it as a number and display it as 2077, removing the leading zero. If it is entered with 02077, then it will display as 02077 as desired. To enter...
Excel: excel: matrix to column, brouwer, dataset
brouwer, dataset, c3: This macro should work for you - just change the range A1:C3 to whatever range you want and run it - I ve set the output to go to column D, but that could also be amended. Sub mover() Dim counter As Long counter = 0 For Each Cell In Range( A1:c3 )...
Excel: excel numbering, hth, hairs
hth, hairs, several ways: There are several ways. 1 - enter =ROW() in A1, use Edit/Goto, type in A1000, PRESS SHIFT, and click OK, then use either Ctrl/D or Edit/Fill/Down. If you wish, you can then select all of column A and use edit/copy then edit/paste special/Values 2 - enter...
Excel: excel opens 2 or more sheets within same window, menu window, excel sheet
menu window, excel sheet, sp3: Hi SPhilip, How are you? If you mean Workbooks (files), please do the following 1. Open the 2 workbooks 2. In Menu Window, select Arrange and then choose vertical or horizontal If you mean WorkSheets of the same workbook, the only way is to use the...
Excel: excel-- outlook, mail message, de bruin
mail message, de bruin, what additional information: The code had one character missing (a typo): Sub AABB() Dim sSource As String, sDest As String Dim sName As String sSource = L:DDCReportsCOUPONPROCESS sDest = L:DDCReports sName = Dir(sSource & *.idx ) = 1 character was missing Do While...
Excel: excel password, excel password breaker, google search
excel password breaker, google search, google: do a google search on excel password breaker & you may have to purchase a product. If it s password protected for opening, that s the case. If you can open it but from within Excel you need to unprotect the sheet(s), I can do that for you -- if that s the...
Excel: excel performance, memory troubleshooter, new computer system
memory troubleshooter, new computer system, dell p4: Kevin Excel does not use much memory compared with other programs. If you are getting an out of memory message I m guessing that it is not because of excel, but because of the other programs that are running. Before opening excel go to the excel help...
Excel: excel printing mutiple print areas automatically, verbage, price 1
verbage, price 1, signs: Jeff, I m not sure what you re trying to print. You say you want to print signs. What does a sign consist of? You also say that your columns could be several hundred rows, but you want to print just one page. How many of these rows do you want to print?...
Excel: excel printing, knowledge base link, excel worksheet
knowledge base link, excel worksheet, preview screen: I have not been able to find an answer for this in the knowledge base (link is http://support.microsoft.com/search/default.aspx?catalog=LCID%3d2057&spid=2512&query=print%20save%20as&adv=&ast=1%2c2%2c3%2c4%2c7&mode=a&cat=False&kt=ALL&title=false&mdt=&pwt=False&res=20&2057comm=1&range=1-48...
Excel: excel probelm, grey screen, office application
grey screen, office application, click options: Hey Wolfgang, One reason why this could be happening is that you have the Ignore Other Applications box checked in your options. Follow these steps: -On the Tools menu, click Options. -Click the General tab. -Clear the Ignore Other Applications...
Excel: excel problem, excel formula, computer virus
excel formula, computer virus, new computers: Hi Chris, I get 2,687,584 computers. I am interpreting your problem statement to mean that each day the number of computers infected decreases by 1000 but then also increases by a factor of 1.015 (1.5%). I put 250,000 in A1 and used the formula =1.015*(A1-1000)...
Excel: excel problem, c apples, format button
c apples, format button, hi bob: For this example, assume the $5 is in cell B2. Select B2:D3 (or as far as it really goes) and use conditional formatting. Change Value Is to Formula Is, and (assuming B2 is the active cell), enter this: =$B2=MIN($B2:$D2)then click the Format button and...
Excel: excel problem, keyboard problem, computer problems
keyboard problem, computer problems, exel: Hi rahul, I have ran into many computer problems at my work. It sounds more like a keyboard problem than an Excel problem. Especially if you can run the same worksheets on another computer without any problems. Usually what I try first is to restart...
Excel: excel program, monday tuesday wednesday, time card
monday tuesday wednesday, time card, time option: Veronica If you are using the decimal point as shown this doesnt give you time, you need to use the time option : ie 7:51 You formula should then look like this for monday 7:51 11:58 12:55 16:59 Notice the 24 hour clock for the afternoon, if...
Excel: excel programming, excel programming, three solutions
excel programming, three solutions, formula calculations: Satish, I can only give you a very general answer since you don t say how the calculations are performed (formulas, VBA, or a combination of both). Also, it is unclear why the combobox would be used if you want to perform all the calculations anyway. ...
Excel: excel question, irr function, net profits
irr function, net profits, compound annual growth rate: Stephen Sounds like you need to check out the IRR function, I believe it will get you what you want. Look at the help for IRR and look at the example that they give. I believe the example is what you are trying to do. You should be able to build your...
Excel: excel question, lucian, b2
lucian, b2, c2: Hello, Your question is not very clear for me, but I ll try to figure :) First, you could have a column named Erika. Let s say that A1 is called Erika. The values would be below the name on A2, A3 ..... Next, in B2 you could type a formula : =if(A2...
Excel: excel question, cell b2, bob george
cell b2, bob george, column c: Sorry, still confused. You described: Worksheet A: Name Date Bob 1/4 Dan 1/5 Worksheet B: Name Fred Bob George --------- Now, you say What I need to see is if a name from A (in say..column A) matches B (column B),... matches B (Column B)......
Excel: excel question, cell b1, vlookup
cell b1, vlookup, sheet names: Daniel: Give this formula a shot and let me know if it works for you. You will need to change the sheet names (I just used generic names). This formula makes the following assumptions: 1) Names in WorksheetA have identical existing names in WorksheetB...
Excel: excel question, cell c2, cell b1
cell c2, cell b1, guiding principle: Hi Daniel, If you type a number in cell C2 such as 300178541 it should appear in the cell with the formula because it matches the fourth number in column B. So the formula works correctly if that is what you want the formula to do. My thought is the...
Excel: excel question, guiding principle, column c
guiding principle, column c, allexperts: Daniel Sorry--- I thought I had answered your quesiton, but I just saw, on allexperts, that it was still open. Your formula works fine for me but if you are putting a number in C2 and column B is text then it won t find a match. Try this to make sure...
Excel: excel question, conditional formatting, ok copy
conditional formatting, ok copy, dollar signs: Daniel: You are correct; conditional formatting is the way to go. Starting with cell B1, open the conditional formatting box and choose Formula Is for condition 1 and type in the following: =A1= Daniel (note: no dollar signs on the A1) Be sure...
Excel: excel question, budget worksheet, good solution
budget worksheet, good solution, hurry: Maria I don t know of a way to access the same file simultaneously, however I have accomplished the same by haveing two people working on different worksheets and then having the final budget worksheet linked to those two workheets. So the final budget...
Excel: excel question, base numbers, question question
base numbers, question question, specifi: I understand the basics of your question and what you want to do but the specific layout of the data determines the formula that would need to be written and what you want. However, as I said, I would need to see the workbook to really be of any help and...
Excel: if / then excel question, conditional formatting, format menu
conditional formatting, format menu, aidan: You could use a macro, but conditional formatting sounds a better bet - cannot be more specific as your question doesn t give enough information, but (prior to 2007) the format menu gives you the conditional formatting option, here you can set a value OR formula...
Excel: excel question PLEASE HELP, excell spreadsheet, cell a1
excell spreadsheet, cell a1: Alan Sounds like you need a formula that is linked to another sheet and is dependent on the contents of the cell that it is referencing. I.E. =IF(Sheet1!A1= X ,+Sheet1!A1, ) This formula says if cell A1 in sheet 1 contains an X, then put the X in this...
Excel: excel question ., window popup, excel worksheet
window popup, excel worksheet, data input: If you want it done in one window where users enter all the info then press enter, you must use a data form. If you want it done question by question, you d need to use VBA to prompt via an inputbox, something like: Range( F5 ).Value=INPUTBOX( Please enter...
Excel: excel repairing, excelfix, backup copy
excelfix, backup copy, hard disk: IF the file had been deleted for a week, any attempt at recovering it from the hard disk is liable to fail as the space will have been used by other programs - this would account for the lack of data in the file - it s been overwritten with information from...
Excel: excel repairing, excelfix, product software
excelfix, product software, harddrive: Hi None of the programs I use can repair the file, you might want to look at this program http://www.downloadjunction.com/product/software/78391/ as it takes the file directly of the harddrive and repairs it, this may be better than the recovery program....
Excel: excel-retaining special char,formats,etc.,with reference, target cell, welland ontario
target cell, welland ontario, degree symbols: It s NOT something that I ve come across, and I cannot duplicate it on XP - if you could mail me a sample file (aidan.heritage@virgin.net) I may be able to shed more light on the problem - my original thought was that if the cell was formatted as a particular...
Excel: excel scroll bars, horizontal scroll bar, appearance tab
horizontal scroll bar, appearance tab, arrow keys: Hi Allan, You should check if the horizontal scroll bar property has been de-selected. Here are the steps to see if this is the case: -Click on Tools on the top menu, then select Options -Click on the View Tab -Towards the bottom of the window you will...
Excel: excel to send email notification on certain date, email notifications, outlook application
email notifications, outlook application, precise explanation: Visual Basic for Application (vba) code can be launched upon opening the Workbook. In the Visual Basic Editor (VBE), in the Workbook object, the code should be entered in a Private Sub Workbook_Open() sub. You need to have at least a bit of vba understanding...
Excel: excel shading, shading
shading: Todd You need to use the conditional formatting feature Pick the cell you want to format (make it the active cell) Go to toolbar/format/conditional formatting. A window will appear that allows you to pick your options. In the first box pick cell...
Excel: excel shutting down workbook, office xp, taskbar
office xp, taskbar: There is nothing in the settings that will do this, so it MAY be that you have a macro that is doing this - if you get asked if you wish to enable macros, select NO and see if this resolves the problem. Possibly also worth checking is tools options, then...
Excel: excel sort, novell user, absolute values
novell user, absolute values, copy paste: Golden What you need now is an & formula. Assume the text is now in columns A,B,C,D and you want to put it together in column E The formula in column E might be as follows: =A1&B1&C1&D1 This will put the text back together. Since you now...
Excel: excel sort, ascii code, blank rows
ascii code, blank rows, ascending order: Hi Dan, How are you? The problem is that your rows are not empty. They store spaces. If you type in an additional column =Len(a1) and copy down it will return 1 (which means that they store one space) for the rows you suppose to be empty. Those cells are...
Excel: excel spacing, double spacing, double space
double spacing, double space, grid lines: Hi Suzanne, You are correct in noticing the problem with double-spacing and sorting. Here s a work-around. 1. Sort the data as you like. 2. To achieve the effect of a double-space, just select all the rows you want to change and choose Foramt,...
Excel: excel spread sheets, index formula, frams
index formula, frams, sheet1: In order to reflect results from one sheet to the other, the formulas to use depend on the type of aggregations that you want to have. It may range from a simple equal sign, to more complex sums or conditionals, I don t know what would fit in your case. ...
Excel: excel spreadsheet, sumif, exact range
sumif, exact range, logical test: It s probably going to be SUMPRODUCT (if you have two or more criteria) - otherwise it s sumif SUMIF is simply =SUMIF(RangeToCheck,Criteria,RangeToSum) Sumproduct is more complicated in this case - lets say you want to test values as being greater...
Excel: excel spreadsheet help?, excel spreadsheet help, consignment boutique
excel spreadsheet help, consignment boutique, excel template: Yes, I can t create something this involved for free. There are many many things to consider and then for me to do to develop a software package using EXCEL to track inventory, payables, etc. Please contact me if you still want me to help. You can contact...
Excel: excel spreadsheet help?, excel spreadsheet help, consignment boutique
excel spreadsheet help, consignment boutique, consignor: CARA Certainly excel can handle what you need. But the allexperts generally do not create entire pojects for people, we just try to answer specific questions or give you guidance maybe in the design of a project. I will give you some suggestions along...
Excel: excel spreadsheet, cell b1, excel spreadsheet
cell b1, excel spreadsheet, ok time: Hi Carlton, If 3.5 is in cell A1 then the formula in cell B1 would be =A1/24 then with cell B1 selected you would go to the excel menu and select Format then Cells then the Number tab then select Time from the list and then select the format 13:30...
Excel: excel spreadsheets for budgets, microsoft office 2000, using microsoft office 2000
microsoft office 2000, using microsoft office 2000, excel spreadsheets: Latoya I use a format that tracks expenses as they occur by putting the expense in one column and then spreading it or also putting it in another column that is headed with the appropriate name of the expense, for example telephone, utilities, office supplies,...
Excel: excel spreadsheets/time keeping, excel spreadsheets, hundreths
excel spreadsheets, hundreths, decimal point: Steve Time keepng is usually a problem because we tend to not state things properly. For example you have stated that 24.67 is equal to 25.07 hours. When we use the decimal point we are actually saying that it is 24 hours and sixty seven hundreths of...
Excel: excel if statement or conditional formatting, conditional formatting, vlookup
conditional formatting, vlookup, column comments: Assume Book1.xls on sheet1 names is column A comments is column C where you want the data to appear is Book2.xls, sheet1 names in column A you want data to appear in column F So in Book2.xls, Sheet1, in F2 put the formula =if(iserror(vlookup(A2,...
Excel: excel - subtotals, copy paste, sr 1
copy paste, sr 1, office 2000: Susan Yes you can copy/paste special/values the subtotals to another worksheet. The Subtotals will be in the data that you copy, but the formulas to create the subtotals will be eliminated. You can then Sort the data and get the subtotals to be at the...
Excel: excel sum of cells containing formula, sum function, zero values
sum function, zero values, uda: It s not because it s a formula, it s because you re returning text instead of numbers. Change your formula to =IF(H3=1,1,IF(H3=2,3,IF(H3=3,12,IF(H3=4,1.2,IF(H3=5,.75,IF(H3= ,0)))))) (remove the quotes) And if you don t want to see the zeros, you can use...
Excel: excel summing, summation, a1
summation, a1, percentages: Michael I don t know exactly what you are trying to do, but you might consider using a formula that looks like this. =SUM(A1:A100). This formula will sum all amounts included in the range A1 thru A100. Of course if you have values in cells within that...
Excel: excel template error, excel template, ppt file
excel template, ppt file, template error: Yes, get rid of the file - at least, as far as Excel is concerned - you have currently got a powerpoint presentation being loaded at startup, so it should be in tools options then the general tab - ensure nothing is shown in the OPEN ALL FILES IN section (I...
Excel: excel template, personal bills, due bills
personal bills, due bills, purchase details: Send me some more information: What purchase details do you need to track? Give me an idea of what this is for (a small business, personal bills, etc.) Robert ====================================================== Want to learn more? I write and...
Excel: excel templates, douglas m smith, vlookup function
douglas m smith, vlookup function, excel template: Hi, You can use the VLOOKUP function to do this. First, you need to create a table of the codes and the related information. Here s an example for cells A1:D4. Make sure the list is sorted by Code: Code Product Price Supplier A001 Tylenol 2.00...
Excel: excel text boxes, douglas m smith, alignment tab
douglas m smith, alignment tab, short column: Hi Susan, There are two ways to get text to stay within a column. One involves using wrap text . This wraps the text with a cell and is set up by selecting Cells from the Format menu, clicking on the Alignment tab and checking the Wrap Text box. The...
Excel: excel text formatting, zeros, cursor
zeros, cursor, next box: Richard Sounds like a perfect application for the text to columns feature of excel. Select the data you want to convert. Go to toolbar/data/text to columns. If all of your data has three zeros before the numbers and you want to eliminate the first two...
Excel: excel text formatting, format cells, number 0
format cells, number 0, decimal places: Hi Richard, again You can also do: 1. E.g. Your parts numbers are in A2:A10 2. Convert A2:A10 to numbers (Format-cells - number 0 decimal places) 3. Type in B2 = 0 &TEXT(A2, # ) 4. copy B2 to B3:B10 5. Select B2:B10 Edit copy Select B2:B10 Edit...
Excel: excel text import, programmable logic controllers, douglas m smith
programmable logic controllers, douglas m smith, old dos program: Hi Gene, Yes, TRIM should do what you want. If cell A1 contains a s with three spaces between each letter plus three leading and trailing spaces, 19 total characters (I m using the underscore character to represent the spaces because AllExperts editor...
Excel: excel text, horizontal alignment, format cells
horizontal alignment, format cells, split screen: You will only ever be able to see the text in the columns that are on the screen at the moment. You may already be familiar with these, but you may want to check out the following that are useful for ways to format cells and your screen: Cell formatting:...
Excel: excel -tracker to calender, cell a1, calender dates
cell a1, calender dates, sheet1: NOEL If I am understanding correctly what you need, you can do this by linking to a cell on sheet1 where you will change the date. The formula will look something like this. =+Sheet1!A1 (Where the date is in sheet 1, cell A1) and will be placed...
Excel: excel userform for editing individual cells, excel userform, number sheets
excel userform, number sheets, multiple users: Hey Scotty, If you need help getting started creating Userforms, this is a great site: http://www.contextures.com/xlUserForm01.html You would use the following to extract the value from the text box entered by the user: TextBox1.Value Then you would...
Excel: excel userform input box problem, excel userform, input boxes
excel userform, input boxes, background area: First of all the .Show method just shows the form and nothing else. Each control can and does have its own set of code instructions. In the VBE, DOUBLE click your command button and you will be taken the Click Event of this button. The code you need...
Excel: excel userforms, input boxes, userforms
input boxes, userforms, tiny space: As far as I know, you can t use a UserForm during a macro to gather information. The Userform should be displayed and used to gather the information, then write it to the sheet, the userform would contain the code. See the DatabaseForm example here; http://www.excel-it.com/vba_examples.ht...
Excel: excel VB controling Word, excel vb, exceltip
excel vb, exceltip, control word: Hi MArk Yes it is possible to do what you ask. On this occasion I cant help furhter as this is not an area I have needed to stduy. However this link shoul dset you of in the right direction http://www.exceltip.com/st/Control_Word_from_Excel_using_VBA_in_Microsoft_Excel/465.html...
Excel: excel VBA, exit function, whole number
exit function, whole number, ogilvy: Public Function Odd_OR_Even(r As Range) If r.Count 1 Then Odd_OR_Even = Other Exit Function End If If Not IsNumeric(r) Or Len(Trim(r)) = 0 Then Odd_OR_Even = Other ElseIf Int(r) r Then Odd_OR_Even = Other ElseIf r Mod 2 = 0 Then...
Excel: excel -VBA, excel vba, data validation
excel vba, data validation, blank cell: S. MUTHUVEL, The Validation List combobox would be easiest. Let s say the Name Column was in A2:A12 (A1 is the header Name ). With a blank cell selected, click Data Validation. Under Allow choose List, and under Source type =$A$2:$A$12 then OK....
Excel: excel vb, seperated, commas
seperated, commas, cells: Try this code- Sub Try() Dim myString As String myString = Cells(1, 3).Text For i = 2 To 30 If Cells(i, 3) Then myString = myString & , & Cells(i, 3).Text End If Next i Cells(1, 2) = myString myOK = MsgBox( Job Completed! ,...
Excel: excel vba, p40, cells
p40, cells, cheers: Hi Sy, How are you? Please see the following Sub Delete_row() Dim A As Range Set A = Range( a6:P40 ) r = A.Rows.Count Do While r = 1 If A.Cells(r, 3) = And Cells(r, 13) = Then MsgBox A.Cells(r, 3).Address A.Cells(r, 3).EntireRow.Delete...
Excel: excel vba, expert listings, excel expert
expert listings, excel expert, vba macro: Hi, Sy, thanks for writing in. I guess you didn t notice where I say that I m not familiar with VBA. (I mention it both in my expert listing and on the question page.) I am a programmer, but I use the old macro language that came out with an earlier version...
Excel: excel vba, macintosh version, grammar
macintosh version, grammar, macintosh: ...Firstly can you tell me in applications in excel+vba on the PC will run on the Macintosh version of Excel?... Huh? ...If not, can you give me and clues as to how to go about doing this?... Huh, again? These make no sense. Are there typos perhaps?...
Excel: excel vba, vba excel 2000, excel vba
vba excel 2000, excel vba, worksheet functions: Yes, you are wrong. Only the internal worksheet functions found in the object browser (in the VBE) under the WorksheetFunction class are available in VBA. While this is most of the functions, it isn t all of the functions. -- Regards, Tom Ogilvy...
Excel: excel vba,, vba module, cell references
vba module, cell references, time values: You could use a procedure to set the variables, then call this procedure at the start of each of the other procedures. Option Explicit Dim variable1 As Single Dim variable2 As Single Sub init() variable1 = Range( a1 ).Value variable2 = Range(...
Excel: excel vba, target address, target value
target address, target value, excel vba: The button should be created on the worksheet by using the Control Toolbox (View, Toolbar, Control Toolbox) and using the Command Button control. Once it s on your worksheet, name it by setting the Name property to cmdSuspend . I don t understand the...
Excel: excel vba,, max range, volatility
max range, volatility, icount: You have missed the fact that Linest returns and array, not a single value. In your function that works: Sub slinest() Dim sLinestA sLinestA = Application.WorksheetFunction.LinEst(Range(Cells(87, 2), Cells(80, 2)), Range(Cells(87, 5), Cells(80, 5)))...
Excel: excel vba, roy cox, excel vba
roy cox, excel vba, pivottable: I don t see why you would need to maually enter data for the pivottable. Anyway, here s some code to try NOTE: I have answered this question for you on excelforums as well! ---------------------------------------------------------------------------------------...
Excel: excel vba error, cell a1, excel vba
cell a1, excel vba, ogilvy: Hello Scott, I put Sub AA_Tester1() Dim FormulaString As String FormulaString = =B1 + 5 Sheets( A ).Range( A1 ).Formula = FormulaString End Sub in a general module, named a worksheet A and ran the code. It worked fine for me. I can t...
Excel: excel vba userforms, excel vba, print button
excel vba, print button, print dialog: Steven, Two questions. You say you have a print button that brings up a userform. Is any of that written, or is it all needed? I normally throw up a print dialog and let the user choose. Is that okay here? Follow-up: Add a print or OK button...
Excel: excel vba, consecutive rows, increments
consecutive rows, increments, cells: Sy, Let me guess, the problem occurs when there are two or more consecutive rows that meet the blank condition - the second and subsequent rows are not deleted. Here s what s going on. Let s say your routine starts. Rows 6 and 9 are OK, but rows 7 and...
Excel: reduce column width, microsoft articles, column width
microsoft articles, column width, commments: Hi Richard, I am not quite sure what that is but it possibly might have to do with comments. Do you have any commments you are trying to hide? Take a look at these two Microsoft articles. They describe the problem you are having and solutions to them....
Excel: excel or word?, example word, format menu
example word, format menu, line break: Not sure what they all move together is describing, but still - in excel, the enter key isn t accepted as a line break - excel WILL accept line breaks if they are pasted in from (for example) Word. As far as autofitting is concerned, format the entire column...
Excel: excel workbook, excel workbook, random sample
excel workbook, random sample, random numbers: If I needed to do a random sample of 74 accounts, I would use the following formula: =ROUND(RAND()*74,0) RAND() selects a random number between 0 and 1. I then multiply that number by the sample size, 74, and subsequently round it to the nearest whole...
Excel: excel workbook, target address, excel workbook
target address, excel workbook, time robert: You must place this visual basic code in the code page of the first sheet (Tools, Macro, Visual Basic Editor then double-click on Sheet1). This code assumes: The data you are tracking is in the first sheet You have more worksheets than you have rows...
Excel: excel xp, macros, ontime, excel workbook
ontime, excel workbook, infinite loop: First call your onTime sub from within the the WorkBook_Open event which will casue the code to run when the workbook is first opened. Private Sub Workbook_Open() Call onTime End Sub Then, without testing this, you should be able to simply add another...
Excel: about excel, format cells, click tools
format cells, click tools, training materials: Cell protection is a two step process. First you tell Excel which cells you want to be protected or not, then you turn protection on. Cell protection in Excel works in reverse. What I mean by that is that ALL cells will be protected when you click Tools,...
Excel: excel2000, douglas m smith, brainbench
douglas m smith, brainbench, alert message: Hi Sy, The only way to do this would be to write a macro to force close the file with the alerts turned off: Sub CloseFileNoSave Application.DisplayAlerts = False Workbooks( filename ).Close SaveChange: = False End Sub However, I would...
Excel: about excel2003, open button, excel workbook
open button, excel workbook, import data: I also took the question too literally, that was also an interesting challenge :) Regarding the code, you could combine the codes from the two questions to get the final result, something like: Sub browseForTextFile() Dim selectedFile selectedFile...
Excel: I have an excelfile that has..., target number, colomns
target number, colomns, row headers: Jos, The following line of code will provide an input box that requests the number of rows. x = Application.InputBox( Enter number of rows , Type:=1) The answer must be a number (try entering text and see what happens) and the answer will be stored...
Excel: excel, coloum, payroll system
coloum, payroll system, excel user: Hey John, I think I have a solution that may work for you. It s actually a little confusing and I think that emailing an example to you would be best. Can you please provide me with an email address? If not, I can try to cut and paste the sample spreadsheet...
Excel: excell CSV saving, microsoft office 2000, microsoft knowledge base
microsoft office 2000, microsoft knowledge base, web info button: This is listed in the microsoft knowledge base (http://support.microsoft.com) XL2000: Error Message: Document Is Not Completely Saved Article ID : 223810 Last Review : August 5, 2004 Revision : 1.0 This article was previously published under Q223810...
Excel: excell formatting, columb, e2
columb, e2, hannah: Hi Hannah How are you? I am sorry it has taken me a long time to reply to you but I went to Germany for the wedding of a friend. You can do that by using Format - Conditional formatting 1. Highlight E1 2. Format - Conditional formatting 3. in Condition...
Excel: excell forms, straight line, aidan
straight line, aidan, cells: I ve not seen it before, and from the description it s not possible - a column is a column. HOWEVER, you can set indents for individual cells, so that is a possibility (but the columns would still be a straight line) or by use of form controls you could...
Excel: excell formula needed, array formula, incorrect result
array formula, incorrect result, value error: You can do this with a very simple array formula When an array formula is displayed, it is surrounded by braces {}. You do not enter the braces. Instead, when you enter an array formula, you press Ctrl+Shift+Enter, rather than just Enter. Excel will...