novell network, experiencing technical difficulties, excel 97: Hi Fran, Sorry about the delay in responding. The service was experiencing technical difficulties . It s been a while since I dealt with a Novell network. Could the problem be with the long file name issue? Is the file over 8 characters and/or does...

Excel: Error with Pivot Cache, wolfram schneider, lcol

wolfram schneider, lcol, table source: Seems to me that the simple solution is to write code that either replaces [1] with the desired text or removes it all together from Prww2a-EXCELacf21669baaa40d[1].xls . You can use error checking when the wrong file causes an error and then use the...

Excel: Error starting new Excel file, stop gap, error message

stop gap, error message, gap: A note of the error message MIGHT help shed some light on this - it s not a standard problem I know about, although the action of creating a sheet is different to that of opening one. A stop-gap fix is to open a sheet that exists, delete everything on it...

Excel: Error starting new Excel file, kb articles, best wishes

kb articles, best wishes, knowledge base: Roger, I would suggest going to support.microsoft.com and searching the knowledge base for the error you are receiving. The error could be caused be several things. Upgrading from one version of Office to another can be a cause if it wasn t done properly,...

Excel: Error trapping in VBA macro, run time error 13 type mismatch, run time error

run time error 13 type mismatch, run time error, run time error 13: Is this what you want? If an error is caused by DIV?0 it skips the cell. Option Explicit Sub FormatCells() Dim cl As Range Dim rng As Range On Error Resume Next an error will be caused if no Special Cells exist dynamic...

Excel: Error trapping, overflow error, memory error

overflow error, memory error, number 0: Hi Alan, How are you? Do you think you can use the following to find a solution to your error: Sub gg() On Error Resume Next a = 12 c = a / b If an error occurs, construct an error message Err.Clear Err.Raise 11 Overflow error....

Excel: Errors in VBA, run time error, subscript out of range

run time error, subscript out of range, rcl: Exactly where is the error? Is it the line rCl.CurrentRegion.AutoFilter field:=6, Criteria1:=i ? If so, when you reach this line, what is the range represented by rCl.currentregion ? Because your code specifies field:=6 , the range represented...

Excel: Evaluate Dates between what season?, happy holidays, allexperts

happy holidays, allexperts, 4 seasons: Try this, Courtney: http://www.officearticles.com/allexperts/courtney.xls Unfortunately, Excel DOES care what year it is, so your lookup table must contain a list for each year you ll need seasons for. That s easy enough, though. While my lookup table...

Excel: Evaluate filename from cell, link source, cell a2

link source, cell a2, doke: I really do not see why you don t simply use a linked cell. You have the basic Formula already. Simply open each workbook and use = then select the cell from the other workbook. This is an example = [Profit & Loss Planner_Demo.xls]P&L Detail !$D$7 [Profit...

Excel: Evaluating time in Excel, logical values, correct result

logical values, correct result, ogilvy: Stephanie, Glad you found an answer to your real question rather than the one you asked. Wouldn t =Or(A2 =TIME(21,0,0),A2 =TIME(7,0,0)) do as well and be simpler. (this is 9PM to 7AM as your formula pretty much shows) If you want up to 6 AM as...

Excel: Event for change of a sheet tab name?, sheet tab, illustrative example

sheet tab, illustrative example, database connectivity: Jared, You are correct, there is no Worksheet Rename Event. You can do Tools= Protect= Protect Workbook and protect the STRUCTURE of the workbook. This should prevent the user from changing the sheet name. Another approach is to use the codename property...

Excel: Event Log, 60 minutes 2, mm ss

60 minutes 2, mm ss, incr: I don t understand your question exactly, and it might well be too big a job for me to explain here. So I ll give you some basic idea. Here s code that will put a time stamp down column A. It will do the stamp every 30 seconds for 3 minutes. Hopefully, you...

Excel: Event Processing, target address, excel tips tricks

target address, excel tips tricks, cell c3: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 1 Then Target.Offset(2, 2).Formula = Target.Formula ElseIf Target.Column = 3 And Target.Row 2 Then Target.Offset(-2, -2).Formula...

Excel: EXAMPLE OF USING (IF) AND (AND) AT A TIME, cell a1, term loop

cell a1, term loop, mouse pointer: Hi Dipankar, I assume you are asking for examples. IF AND Example: Open new workbook then copy and paste this formula in cell A1. =IF(AND(B1 =C1,B1 =D1),E1, Your last name is Chandra ) Place the number 10 in cell B1. Place the number 8...

Excel: EXCEL, b5, current date

b5, current date, target: Yes. OK, I ll try and answer it properly (although I m GUESSING that you meant A rather than B) the macro as it stands put s the date into A so changing the =int(now()) which puts the current date to (say) = This has been done on : & format(int(now()),...

Excel: EXCEL, vlookup function

vlookup function: VLOOKUP is used to look up a value on a table that exists on the sheet. If col A has names and col B has addresses, and John is somewhere in col A, then =VLOOKUP( john ,A:B,2,0) is john s address. IF returns one of two values based on a condition....

Excel: EXCEL, screen name, spreadsheet

screen name, spreadsheet: Susan Haven t heard of that one, but I assume that by tried deleting you mean that you have highlighted the clicked on the screen name at the bottom of the workbook and right clicked and then shosen delete. If not then try that. If that doesn t work...

Excel: EXCEL, sheet tab, shift tab

sheet tab, shift tab, ogilvy: In the old worksheet select the first sheet tab. Hold down the shift tab while you select the last sheet tab. Do edit= move or copy Sheet, click the make a copy checkbox on the lower left and select the location in the destination workbook (the destination...

Excel: EXCEL, row numbers, paste function

row numbers, paste function, cell a1: Joseph Yes you can copy the contents of one worksheet to a new one, a couple of ways. Use the copy paste function. Open the new worksheet. Go to the old worksheet and clic on the box at the intersection of the row numbers and the column letters. Right...

Excel: EXCEL, excel spreadsheets, custom functions

excel spreadsheets, custom functions, click tools: The hidden programs are not programs, they are files EXCEL loads automatically. These files typically have custom functions that don t come with EXCEL. Click Tools, Add-ins and see if the files you downloaded are listed and checked. If not click browse...

Excel: EXCEL, analysis toolpak, excel spreadsheets

analysis toolpak, excel spreadsheets, documents folder: Joseph - The Add-Ins section of Excel is for small programs you might want to add to your Excel catalog. For instance, the Analysis Toolpak for help with covariances, etc. Add-Ins will not recognize spreadsheets. If you downloaded things that belong...

Excel: EXCEL, finance data, investements

finance data, investements, cell contents: Hi Steven, Since this is quite easy to do manually via a copy and paste, I assume you are referring to how to do this with VBA code. It is actually just like transferring the data within a single workbook except that both workbooks must be open at the...

Excel: EXCEL, a8, a5

a8, a5, a4: Sub top5() Dim LatestDates(1 To 5), Where(1 To 52) As Integer dates = Range( A4:AZ4 ).Value vals = Range( A5:AZ5 ).Value For i = 1 To 52 Where(i) = i Next Sort them: For i = 1 To 51 For j = i + 1 To 52 ...

Excel: EXCEL, numeric portion, format cells

numeric portion, format cells, numeric value: Yes I understand Anuj. But what you can do if it always pcs is that use formula- =A1*LEFT(B1,LEN(B1)-3) The trouble is if your string pcs is not constant or not of constant length. (In my example A1 is 0.69 and B1 is 10pcs) Try this out and let me know....

Excel: EXCEL 2000 doubt !, microsoft office 2000, using microsoft office 2000

microsoft office 2000, using microsoft office 2000, using microsoft office: In simple terms, you can copy the symbol to the clipboard, then use Ctrl V to paste it into the replace dialog box. HOWEVER, the Lambda character doesn t exist in the standard character set of most fonts - it IS in the symbol font, and is the same character...

Excel: EXCEL 2000 use sumif to get data that did not exactly match the criterion, douglas m smith, array formula

douglas m smith, array formula, sumif: Hi Louisa, SUMIF only works with one criteria, but you have two, so an array formula would be needed. Something like: =SUM(IF(LEFT($A$1:$A$7,5)= Luzon ,IF($B$1:$B$7= Jan ,$C$1:$C$7,0),0)) To make this an array formula, you will need to use Control-Shift-Enter...

Excel: EXCEL 2000, spreadsheet test, fuller explanation

spreadsheet test, fuller explanation, numerical value: Question doesn t quite provide enough information, but IF the requirement is that if text is in C1, then D1 has 1, otherwise E1 has 2 for example then in D1 =if(istext(c1),1, ) in E1 =if(istext(c1),2, ) will do it IF however it is more complicated...

Excel: EXCEL 2002 CUMULATIVE TOTAL, cumulative total, rng

cumulative total, rng, excel 2002: Hi Carolyn, From what you are describing, it still seems to me that my first response with the macro would be the answer. If you would like, you can email the spreadsheet to me and I can make the fix directly on it and email it back to you. If you want...

Excel: EXCEL 2002 formatting question, conversion issue, input numbers

conversion issue, input numbers, minutes format: Dean Working with hours and minutes can be very tricky since part of what we are dealing with is in fact decimal and part is analog. I can t give you a very good answer without knowing more about what your input looks like and what type of caclulations...

Excel: EXCEL 2002, cell d2, excel 2002

cell d2, excel 2002, brief summary: in cell D2 put your formula in cell A3 put =d2 you can then automatically fill this down the spreadsheet. IF you want it to do this without filling down the sheet, you will need to use some VBA so that whenever amounts are entered in column B, columns...

Excel: EXCEL 2003, free excel formula, john walkenbach

free excel formula, john walkenbach, exceltip: Hi Patrick, I really haven t found one database of information on Excel formulas or macros. But here are several locations that are very helpful. For free Excel formula help http://www.techonthenet.com/excel/formulas/ http://office.microsoft.com/en-us/excel/HP052001271033.aspx...

Excel: EXCEL 2003 Chart Text Box Problem, text boxes, false application

text boxes, false application, vba code: Art, I do not know of any problems with text boxes on charts. That doesn t mean there aren t any though. I Haven t had a lot of exposure with your situation. To me it sounds like it could be a layering (order) issue or screen refresh issue. There...

Excel: EXCEL 2003, poor netiquette, professional edition

poor netiquette, professional edition, microsoft office: ...CAN EXCEL SORT BY COLOR? ... No ...I DON T KNOW IF I WILL HAVE TO: ALPHA OR NUMBER EVERY LINE TO CORRESPOND TO THE DIFFERENT PEOPLE THEN SORT. ... Yes, you will need some type of key to do the sort. And, in the future please don t SHOUT. FYI...

Excel: EXCEL 2007, conversion method, formatted files

conversion method, formatted files, office excel: Hello Rizwan, You need to download and install the compatibility Pack found here: http://office.microsoft.com/en-us/products/HA101686761033.aspx It allows you to work with office/Excel 2007 formatted files. Once it is installed, you just open the...

Excel: EXCEL, jan jan, concrete examples

jan jan, concrete examples, sums: Hello, Mike, (I m not sure why you put close to 300 numbers in your question, but thank heaven for scroll bars!) :-) Let s say that the column number is in J10, the beginning row is in J11, and the ending row is in J12. You can use the following formula...

Excel: EXCEL, 4 steps, 5 steps

4 steps, 5 steps, sum total: One more try, then maybe I d need to call... Fill these formulas WAY down - as far as you would ever type in the values. So if you enter values in column B and expect some day to go to cell B750, then fill the formula in C1 down to C750. It will only show...

Excel: EXCEL, vcr type controls, blank spreadsheet

vcr type controls, blank spreadsheet, blank worksheet: =Tabname(row()) is exactly as shown - nothing inside (). But this should be entered somewhere on a blank worksheet (your table of contents sheet) and the first one should be in row 1. If you re still having a problem send me what you did to bobumlas@yahoo.com...

Excel: EXCEL - Auto insert date, question thanks, auto insert

question thanks, auto insert, a9: Geoff Okay you want the answer to the hard question--how do we keep the date the same once it has been posted. I usually write a macro to accomplish this along with a couple of formulas that are bit different than the one that I gave you. However you...

Excel: EXCEL - arbitrary fill in, cell c2, blank spaces

cell c2, blank spaces, blank space: Adeline, Did you copy the formula and paste it down the column? It should work. For example I type the following values into column B, deliberately leaving a few rows blank then type =IF(ISBLANK(B2),C3,B2) into cell C2 and copy the formula down the column....

Excel: EXCEL BARCODE, douglas m smith, groton ct

douglas m smith, groton ct, barcode scanners: Hi Gus, I have some experience with barcode scanners and the ones I worked with had settings that would cause the various things to happen (like a line feed) to occur after a code was read, although it seemed to be a bit quirky. As for tabbing to other...

Excel: EXCEL Combo boxes, separator character, nth element

separator character, nth element, txt1: Eric, I would suggest using the list box instead. The rest is dependant on the data setup. Are the words seperated by a space or anything else that is consistent? You can use the following function to extract specific elements from the text, if they...

Excel: EXCEL DATE-FORMAT HELL, format cells, column format

format cells, column format, digit format: OK... Here are ways to tackle rest of the problems. (1) This is not exactly EXCEL setting. Go to START -- SETTINGS -- CONTROL PANEL -- REGIONAL OPTIONS -- DATE. At the top under calendar, you will find :Interpret 2 digit year as.... Please enter appropriate...

Excel: EXCEL FORMULA, excel formula, cell a1

excel formula, cell a1, e1: For this you need an IF statement, Dennis. Try this: =IF(D1= , - ,LOOKUP(B1,D1:D30,E1:E30)) In other words, if D1 is blank, return a hyphen. Otherwise, perform the LOOKUP. You can replace the hyphen, of course, with whatever you like! Regards, Jan...

Excel: EXCEL FORMULA, excel formula, time format

excel formula, time format, date time: Hi, Dennis, Let s say you have 51:23:00 in cell J10. All you need to do is multiply by 24 to get hours, and then multiply by 60 to get minutes. This formula should do the trick: =J10*24*60 Once you ve done so, the cell will turn into date/time...

Excel: EXCEL FORMULA, excel formula, column c

excel formula, column c, charter: Dennis If it works for text then the problem could be that the numbers are mixed, ie text or numerical differences, I would check both columns to make sure they are both the same formats. if they are then can you send the file to littlewingtoo@charter.net...

Excel: EXCEL FORMULA, douglas m smith, target range

douglas m smith, target range, blank cells: Hi John, Try this formula on sheet 1: =ROWS(Sheet2!$C$1:$C$30)-COUNTBLANK(Sheet2!$C$1:$C$30) Change Sheet2 in the formula to whatever the sheet name is and the range to whatever it is on that sheet. If the sheet name contains a space character,...

Excel: EXCEL FORMULA, excel formula, future value

excel formula, future value, present value: Joseph - Good news, if you know how to do this on a calculator then you re 90% there! In fact, you don t really need to use formulas at all, you could type in what you would do on the calculator, just preceed the formula with an = . However, the formulas...

Excel: EXCEL FORMULA, maturity date, settlement date

maturity date, settlement date, excel formula: I m afraid this type of calculation isn t one I m familiar with - it sounds as though it is along the lines of one of the financial functions, the full list of which is Financial functions Some of the content in this topic may not be applicable to some...

Excel: EXCEL FORMULA, douglas m smith, text cells

douglas m smith, text cells, cell b1: Hi Dennis, I took a shot at this and I couldn t come up with a formula for it. FIND and SEARCH will both return the first occurance of a character, but only the first. This could be done with a macro. It would need to loop through each character in the...

Excel: EXCEL FORMULAS, 4 digits, store numbers

4 digits, store numbers, circular reference: Hi Jodi, I believe this formula will work for you. Just insert a column next to the one you would like to work on. Say your store numbers are in column A. Insert a column to the right of that and insert this formula in the first cell: =RIGHT(A1,LEN(A1)-1)...

Excel: EXCEL Formula, excel formula, cell a1

excel formula, cell a1, column format: Depending on what your desired output should look like, there are a variety of ways to handle highlighting dates. Example - Date is located in cell A1. 1) =+TODAY()-A1 This formula will calculate how many days old an invoice is. 2) =+IF(TODAY()-A1...

Excel: EXCEL HELP !!!!!!!, douglas m smith, colored cells

douglas m smith, colored cells, brainbench: Hi Chris, There s no way to directly count cells based on their color. It would require a macro or a macro-based user defined function. However, the basis you used to determine why a cell was yellow instead of red may be xountable. Examples: cell is...

Excel: EXCEL MACRO, conditional formatting, data validation

conditional formatting, data validation, excel macro: You can set up a reminder as to course of action, but I can t give you much help since I don t know what your data looks like or what you want to do with your reminder. You probably do not need a macro, but maybe can use conditional formatting or data validation...

Excel: EXCEL MACRO, row sums, column sums

row sums, column sums, col1: this should capture the basic functionality you describe: Option Explicit Sub CalcSums() Dim rw As Range, rw1 As Range Dim col As Range, col1 As Range For Each rw In Selection.Rows Set rw1 = rw.Cells With rw.Offset(0, rw1.Count).Resize(1, 1) ...

Excel: EXCEL PROBLEM, hyperlinked files, excel sheet

hyperlinked files, excel sheet, hyperlinks: I have no problems opening a pdf using a Hyperlink.Presumably you have Acrobat reader installed. Firstly, select the cell then right click & choose Hyperlink. In the form that opens browse to find the file and in the Text to Display box type 2007 ...

Excel: EXCEL print area set up, douglas m smith, split screens

douglas m smith, split screens, feature functions: Hi Siva, First select all of row 1. Now hold down the Control key and select of column A. Under the Insert menu, select Name and then Define from the submenu. In the text box, enter the following: Print_Titles and click OK. (This is a special...

Excel: EXCEL QUESTION, page setup options, column headings

page setup options, column headings, column width: Well you could hide the December column by setting the column width to zero. Or you could change your print range to only use the January column. In whic case you d have to use the page setup options to specify rows to repeat at top and columns to repeat...

Excel: EXCEL QUESTION, location codes, upc number

location codes, upc number, gist: Eli - I think I get the gist of how you want your spreadsheet to work, but I m not understanding the details. At one point it seems you want B1 to change to the current location. At another, it seems you want to keep a running record in C1, D1, E1 etc....

Excel: EXCEL QUESTION, column c, option button

column c, option button, little knowledge: If you already have the data in A&B and you want to add what s in A to B, you can select all of column A (Click no the letter A at the top of the column), then use Edit/Copy, then select all of column B, then use Edit/Paste Special, and select the Add option...

Excel: EXCEL ROTA WORK SHEET, rota 2, type b2

rota 2, type b2, rapid reply: Hi Philip, Would it be possible to send me the worksheet so I can get a better understanding of the layout you are describing? My email address is bluecat74@yahoo.com That way I could try to give you exact formulas to achieve your desired effect. Thanks!...

Excel: EXCEL SPREADSHEET, chris hansen, lee snyder

chris hansen, lee snyder, prize lists: This does it for me in my tests: Option Base 1 Sub ReSort() Assumes lists are already named Series and Higame in the worksheet (without the header rows) both of these ranges are 2 columns -- name and score Dim n As Integer On Error...

Excel: EXCEL SPREADSHEET, chris hansen, lee snyder

chris hansen, lee snyder, prize lists: Anything I suggested would depend on where the data is located and where you want the answers. Do you want to use macros, or do you want to do it manually with the assist of formulas. Send me the answers to those questions and a sample file and I will...

Excel: EXCEL SPREADSHEET, excel spreadsheet, good answer

excel spreadsheet, good answer, compound interest: Joseph Sorry I m super busy right now and won t be able to answer your question for a couple of days. I suggest you reask of another allexperts person and I m sure you can get a good answer. If not please let me know and I ll be able to answer you in...

Excel: EXCEL SPREADSHEET, annuity investment, investment calculator

annuity investment, investment calculator, excel spreadsheet: There are many templates available on the Microsoft Office website, here are the results for compound interest: http://office.microsoft.com/en-us/templates/results.aspx?qu=compound+interest&av=TPL000 The first one that appears, the Annuity Investment Calculator,...

Excel: EXCEL SPREADSHEET, excel spreadsheet, intrest rate

excel spreadsheet, intrest rate, answer format: 6% per month is 72% per year, which is unbelievable, no one outside the Mafia would pay that type of interest. But if I suspend my disbelief... If A1 is your principle, and the intrest rate was 6% per month, the formula for your principle plus interest...

Excel: EXCEL SPREADSHEET, excel spreadsheet, compounding interest

excel spreadsheet, compounding interest, g 500: Hi Joseph, I believe this formula would give you the result you are looking for. I think the expert might have just mistakenly typed -L1 in their formula. But not only that the formula they gave does not have the interest rate they listed as being in cell...

Excel: EXCEL SPREADSHEET, chris hansen, lee snyder

chris hansen, lee snyder, prize lists: I suspect (as always with Excel) that there are lots of ways of doing what you want - but MY method would be to remove the duplicates in one hit - the method would be to use VLOOKUP - which would return #N/A errors for non duplicates, and would show you the...

Excel: EXCEL SUMIF CONDITIONAL FORMULA, excel sumif, sumif formula

excel sumif, sumif formula, cell reference: That formula has no variable cell, so wherever you enter is (ctrl/shift/enter) it will have the same value. Maybe you want the 518 to really be a cell reference? And the formula you have needs to be array-entered (ctrl/shift/enter). But there s a formula...

Excel: EXCEL TEST, excel test, word test

excel test, word test, level test: Latasha I m sorry I have no idea what type of questions will be asked. Excel can be very advanced with complicated macro formulas or very simple with 2+2 type of formulas. It s unlikely that it would all be covered in one test, but I have no idea what...

Excel: EXCEL (Transfer cell data from book1 (worksheet1) to book2 (worksheet1), excel application, spreadsheet

excel application, spreadsheet, a1: As far as I know, you need to open the workbook in order to modify it. What you don t need is to show it, in order to do so. The following macro creates a new instance of Excel, hides it, opens a password protected workbook, writes the value of range A1...

Excel: EXCEL VB DATE QUESTIONS, daylight saving time, vb date

daylight saving time, vb date, excel vb: Mr. LaBounty It appears form the information you are giving me that you can solve the problem with a series of IF(AND() formulas. You can construct the criteria within the formula to direct excel to do waht you want. For example If a date is between...

Excel: EXCEL VBA - FIND COPY PASTE, c cells, srow

c cells, srow, b cells: Hi again Paul, The code you show never changes the worksheet that is active, so there should be no need to go back to sheet Questions . A VLOOKUP in a table that resides in another sheet does not make that sheet active. So after looking up Results the...

Excel: EXCEL - VBA - NAMED RANGE, track changes, cells

track changes, cells: I m not aware of any way of doing this through VBA - although you COULD use the tools track changes option to acomplish what you are trying to do. The other alternative is to have the sheet protected so that the users couldn t do what you want them to NOT...

Excel: EXCEL VBA, katherine katherine, outlook application

katherine katherine, outlook application, blank file: Katherine, The code will need to be played with a bit. This code needs to be run before you start altering Outlook. What I have done for this process is create a blank file (empty.xls) with a sheet named empty. Then I use this code: Sheets( name...

Excel: EXCEL VLOOKUP FORMULA, excel vlookup formula, cell a1

excel vlookup formula, cell a1, fri: Ah - clearer now, but less straightforward as I don t think you can do it with JUST a formula - are you happy to have a macro on this sheet? IF SO, and assuming that E1 contains a header row, this code will do it for you Sub UpdateMaster() Dim holdvar...

Excel: EXCEL WITHDRAWAL FORMULA, circular reference, excel formula

circular reference, excel formula, present value: The only way you d get a circular reference error is if you re entering this formula (=K1-I3) in either cell K1 or I3. Enter it somewhere else! However, it seems you d want to take the present value and subtract the withdrawal from THAT. So you should use...

Excel: EXCEL WITHDRAWAL FORMULA, circular reference, excel formula

circular reference, excel formula, financial background: I don t have a financial background, so I am not sure if I have understood correctly. What I suppose is that you want to withdraw every month $200, apply the monthly interest rate (annual/12?) and that is the starting figure for the next month. I have put...

Excel: EXCEL WORD SERCH?, progress press, format dialog

progress press, format dialog, asterik: Hi James, Yes, you can search and replace words in Excel 2000. These are the steps you would take: Replace text or numbers: Select the range of cells you want to search. If you want to search the entire worksheet, click any cell in the worksheet....

Excel: EXCEL worksheet menu bar, worksheet menu bar, command bars

worksheet menu bar, command bars, chart menu: Using Private Sub Workbook_Open() On Error Resume Next Application.CommandBars( _ Worksheet Menu Bar ).Enabled = False On Error goto 0 End Sub worked fine for me opening the file with excel already open or double clicking on it in windows explorer....

Excel: EXCEL, circular references, sheet tab

circular references, sheet tab, michel michel: Michel Ctrl+semicolon puts a non-changing date in a cell. If you want it automatically, you would need to preenter formulas that intentionally use circular references or use a macro. Right click on the sheet tab and select view code. Put in code...

Excel: EXCEL2000, new location, spreadsheet

new location, spreadsheet, mistake: Hi, Sy, Excel can indeed transpose ranges. But I suggest that you practice first on small sections, just to get a feel for how it works. Before anything else, save your file under a different name. Since transposing a large area is a major change, you...

Excel: EXCELL, microsoft excel 2000, budget spreadsheet

microsoft excel 2000, budget spreadsheet, cell a1: It often has to do with comments in the cells which would not be able to be seen if the rows were inserted. Try either clearing all the comments (if that s ok) and then inserting, or resetting the last cell (the real last cell and what Excel thinks is the...

Excel: EXCELL SPREADSHEET, g 500, compound interest

g 500, compound interest, begining: i Joseph How are you? Please do the following 1. Type in H1 the monthly rate, e.g =6%/12 2. Type in I1 the constant withdrawal, e.g. 500 3. Type in J1 the number of months, e.g. 10 4. Type in K1 the initial amount, e.g. 100000 5. Type in M1 1 if...

Excel: EXCELL, microsoft excel 2000, douglas m smith

microsoft excel 2000, douglas m smith, budget spreadsheet: Hi Paul, That error message means that you have data that will be shoved off the bottom of the worksheet. Something is on row 65536. Hit the key combination Control-End and you will jump to the last active cell. My guess is that is way beyond the actual...

Excel: EXCEl VBA, cell b1, excel vba

cell b1, excel vba, cell a1: Here is an example that does what you describe, it may give you some ideas: Sub IncrementWeek() Dim rngOrigin As Range Set rngOrigin = ActiveSheet.Range( A1 ) rngOrigin.Offset(0, 1).Value = rngOrigin.Value + 7 End Sub It works on...

Excel: EXcel, run time error, run time error 1004

run time error, run time error 1004, sqlsetconnectattr: Jay, Not really my area of expertise, but I ll take a shot at it. Check to make sure that the new machine has the ODBC addin activated in Excel and VBA. In Excel, click Tools Add Ins... and make sure ODBC Add In is selected. You may need the Office...

Excel: EXcell logical test, logical test, numeric value

logical test, numeric value, many thanks: Talat Your first formula works fine for me. It should not matter if the cell K44 coantains a formula as long as the formula results in a numeric value. Make sure tha formatting of the cell is correct, and make sure that you used a zero integer in the...

Excel: EXTRACTING AND ADDING UP DATA FROM MULTIPLE COL AND ROW, calderoni, transaction description

calderoni, transaction description, outo: Unfortunately, Peter, when you type in a question here, the spaces are all condensed and things don t line up. So I can t see how your data falls into columns. But for the sake of simplicity, I ll have to assume that the code letter is in a column by itself....

Excel: Ex cel Formula, vlookup formula, concatenate

vlookup formula, concatenate, final result: I think the best way to demonstrate the other options is with an example. As such, I have uploaded a sample spreadsheet to the following URL: http://home.swbell.net/nate-sus/excel/lookups.xls On the third example I used an array formula (you will probably...

Excel: Excel, regression models, perl programmer

regression models, perl programmer, multiple regression: To my knowledge, the only way to create a file that would contain both data AND formulas would be to actually create the binary file in the Excel file format. I don t know if there is information on this file format or where it might be found. You might...

Excel: Excel, pivot tables, excel microsoft

pivot tables, excel microsoft, pivot table: Alex, one of the best ways to do this is to create pivot tables that summarize the data in specific fashion to create your secondary lists. Any changes made within the originally specified range will be updated in the pivot tables when they are refreshed....

Excel: Excel, xls file, search tool

xls file, search tool: Alt F11 will bring up the VBA editor and you can search from here, or you can use the search tool from the windows start menu - use this to find files containing text where the text is something you remember from the macro - if these fail, I would guess that...

Excel: Excel, colums, spreadsheet

colums, spreadsheet, view 1: Laura: Yes, specific colors (or rows) along with entire pages/sheets can be hidden from view. 1) To hide a row or column select the row or column you wish to hide and click format- column (or row)- and select hide. To unhide the same item select the...

Excel: Excel, width measure, formula index

width measure, formula index, sheet1: Exactly. If you only list prices for 24, 36 and 48, then I would expect it to return the price for 36 (anything from 36 to 48). So it is working exactly as designed. If I add a separate price for 37 and put the number 37 in sequence between 36 and 48,...

Excel: Excel, pagedown key, cell a1

pagedown key, cell a1, arrow key: Absolutely - might be worth using a custom form to replace the multiple input boxes you might need though - but the simple version of the macro would be Sub FillDown() Dim Filler dim FillValue as long FillValue=inputbox( What Value ) I ve used the...

Excel: Can Excel do it?, data validation, down arrow

data validation, down arrow, excel 2002: BJ Data validation is the only way I know of doing this, excel doesn t allow you to insert text or numbers into cell and then have a formula in that cell to change it. The Autocorrect option in Excel uses the same one as in Word, so if you were to autocorrect...

Excel: Excel, target value, target address

target value, target address, cell a1: Hi Jerome, I m interpreting your question to mean that if the user enters 1, cell G6 is activated, 2 - cell G10, 3 - G14, 4 - G18, etc. You don t have to use Select Case for this (although you could) because the constant 4-cell offset can be computed. Here...

Excel: Excel, target value, target address

target value, target address, cell a1: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $A$1 Then Select Case Target.Value Case 1 Range( G6 ).Select Case 2 Range( G10 ).Select Case 3...

Excel: Excel, bowlers, ogilvy

bowlers, ogilvy, target: I assume you know how to use the Change event as that sounds like what you are describing Private Sub Worksheet_Change(ByVal Target As Range) If Target.count 1 then exit sub If Target.Row = 3 And Target.Row = 133 And _ Target.Column = 7 And...

Excel: Excel, douglas m smith, brainbench

douglas m smith, brainbench, sheet1: Hi Gwen, The easist way to set up a link to or from a spreadsheet is to put an equal sign in a cell and click on the cell in the other spreadsheet. The link will effectively write itself. A link within a worksheet will look like this: =A1 A link...

Excel: Excel, conditional formatting, spread sheet

conditional formatting, spread sheet, units of measure: Well, you need to ask how if that is what you wanted to really know. I can t assume what your real question is? Regardless, unless you use a macro then you can t do this as accurately and as completely as you d like but you can get close. The non-macro...

Excel: Excel, douglas m smith, brainbench

douglas m smith, brainbench, thanks in advance: Hi Charles, The list of files is the most recent ones that you ve opened, regardless of what s happened to them since they were open. Old file names will eventually clear off after you ve opened enough other files. You can however clear all the file...

Excel: Excel, vlookup, column c

vlookup, column c: As long as the specific unit appears only once and the previous date you need is on the same row as this unigue previous date in some table somewhere then a lookup should work. If you have the previous date listed more than once in the table then this won...

Excel: Excel, paste values, excel spreadsheet

paste values, excel spreadsheet, necessary number: You can do it with a macro, but it s probably easier just to use a formula - put this in cell B1 =OFFSET($A$1,MOD(ROW(A1)-1,20)+((COLUMN(A1)-1)*20),0) fill this down to B20, then fill those 20 cells across the necessary number of columns. Once done,...

Excel: Excel, douglas m smith, array formula

douglas m smith, array formula, number values: Hi Dimitris, The problem with using VLOOKUP is that is only pulls the first result it finds. There is a function called WEEKDAY which will return number values for the days of the week. There are three variations of it but for your situation, we...

Excel: Excel, data validation, b cell

data validation, b cell, spread sheet: Hi Im presuming you know how to place a button on your spread sheet and access the click event. if you do put this code in the click event and it perform the function you have asked for. CODE FOLLOWS ; Range( a3:iv3 ).Select Dim a, b As Long...

Excel: Excel, excel spreadsheet, excel sheet

excel spreadsheet, excel sheet, blank paper: This is a good question, and I have to confess that I have no experience at it. I will try to keep it simple, an Excel spreadsheet is a tool that allows you to play with numbers. It is a big sheet of blank paper, organized in rows and columns, where you...

Excel: Excel, letter designation, spreadsheet

letter designation, spreadsheet, nate: Don: VLOOKUP is the way to go...I suspect you are getting a #REF because some of your lookup values may have leading or trailing spaces...or perhaps the formula is not being entered correctly. Here is the sample formula I used: =VLOOKUP(K3,$M$3:$N$9,2,FALSE)...

Excel: Excel, winword exe, conditional formatting

winword exe, conditional formatting, cell a1: Sherlyn: Give this a shot... 1) In Excel select a cell and click insert- hyperlink 2) On the left choose Existing File or Web Page 3) Browse to the word processor you want to open...you will want to find the .EXE file (for example, WINWORD.EXE). You...

Excel: Excel, douglas m smith, brainbench

douglas m smith, brainbench, page books: Hi Eduardo, Can you be a little more specific on what you are trying to do with Excel? List management, importing data, charting, reporting, statistical analysis? There are 1000 page books on Excel tips and techniques. Narrow your question down to specific...

Excel: Excel, cells, yahoo

cells, yahoo, formula works: Hi Claire, Normally when you drag a date down the page it will continue the dates day after day. I think I don t fully understand how your formula works. Can you send me a copy of it? I will take a look at what you are trying to accomplish. You can email...

Excel: Excel, xlstart directory, wkbk

xlstart directory, wkbk, excel 97: Put them in the XLSTART directory of where you have Excel installed. They should work just fine. Re templates, you can find the right directory by simply starting the process of saving a workbook as a template and see where Excel points. Note that directory...

Excel: Excel, brain failure, cell references

brain failure, cell references, nursing station: If Discharges... begins in Col A, and where you re looking the pair of values up is in Cols A & B on Sheet2, then enter this in C2 (it d have been easier if I had the exact cell references, but...) This formula MUST be entered via ctrl/shift/enter: =INDEX(Sheet2!C:C,MATCH(A2&B2,Sheet2!A2:A1000&Sheet2!B2&B1000,0))...

Excel: Excel, geeky friends, data layout

geeky friends, data layout, fourteen times: Sorry to take so long. GOT IT!! It s VERY cool. Open your workbook, save a copy. Hit Alt+F11 to open the VBA area (the VB Editor). On the left, choose your workbook (or the copy). From the menu, choose Insert-- Module. In the right-hand window, paste...

Excel: Excel, warm regards, weekdays

warm regards, weekdays, weekday: Dear Dimitris, Try to add an extra column with a formula- =WEEKDAY(A2,2) 6 You will notice that all weekdays are marked as TRUE in this column. Now use SUMIF with criteria as TRUE Please let me know if this worked the way you needed it to.... With warm...

Excel: Excel, microsoft works spreadsheet, www microsoft com downloads

microsoft works spreadsheet, www microsoft com downloads, microsoft com downloads: How do I do this? You must purchase the program. Microsoft Excel is not free. You can download the Viewer for free from Microsoft: http://www.microsoft.com/downloads/details.aspx?FamilyID=c8378bf4-996c-4569-b547-75edbd03aaf0&displaylang=EN Follow...

Excel: Excel, microsoft works spreadsheet, microsoft office 2000

microsoft works spreadsheet, microsoft office 2000, microsoft office 2000 standard: Hi Jackie, I apologize for the delay, I went on vacation and forgot to answer my emails. You can download a free Excel Viewer, if you only want to view the file. It has all the download and install instructions. You can do that by copying and pasting...

Excel: Excel, cell a1, cell reference

cell a1, cell reference, optional input: It does indeed - you need vlookup =vlookup(What,Where,Which,Logical) WHAT is the value you want to look up (Joe, Frank etc, but usually entered as the cell reference) WHERE is where it will find the data - with the item being searched being in the...

Excel: Excel, joe frank, database database

joe frank, database database, list of names: Hi Christopher, Imagine you have data in Columns A & B of Sheet1. NAME VALUE Joe aa Ted bb Jim cc Frank dd Sally ee Kim ff Wendy gg Tim hh In Sheet 2, Column A you have - Joe Frank ...

Excel: Excel, format cells, select tools

format cells, select tools, spreadsheet: Let me explain how to protect your worksheet, locking certain areas and leaving others open for editing. When you protect a worksheet, every cell that is formatted as locked becomes unavailable. By default, all cells on the worksheet have this locked...

Excel: Excel, database database, wendys

database database, wendys, list of names: Chris You can use a variety of options but I would need to know if any of the names are duplicates ie two wendys or if they can be sorted alphabetically. Are the sheets set up the same? Are they in two workbooks or the same workbook different sheets...

Excel: Excel, roy cox, dao

roy cox, dao, spreadsheet: Why not store the data within the the workbook containing the Form? That is much easier. See the Contacts Form example here: http://www.excel-it.com/vba_examples.htm You can write to another closed workbook but the code is much more complicated,...

Excel: Excel, category range, pivottable

category range, pivottable, excel spreadsheet: I can t tell what is wrong without seeing the workbook. However, it is much more efficient to store all the data on one sheet with a Dates Column. Then you can use a PivotTable. With a PivotTable you can Group data by Months, Quarters and Years. Have a...

Excel: Excel, douglas m smith, brainbench

douglas m smith, brainbench, mischief: Hi Steven, I don t believe this is possible, but check with other experts too. Someone may have a technique to pull this off. My guess is you are trying to keep the actual database safely out of reach of the data entry people. Book2 would need to open...

Excel: Excel, precaution, spreadsheet

precaution, spreadsheet, suggestion: Steve, What you need to do through your macro, is remove protection of Book 2, update data and again protect with password. See sample below- ActiveWorkbook.Unprotect Password:= trial ---copy data commands to go here ActiveWorkbook.Protect Password:=...

Excel: Excel, skill level, macros

skill level, macros, free time: Hmm. 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 do so unless they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros...

Excel: Excel:, auxiliary data, consolidated data

auxiliary data, consolidated data, vlookup: You can adapt the solution to your needs, by moving the control information to a new sheet, and referring the formulas to that range. Using names for the ranges can me the final formulas look simpler. Regarding extending the formula for other columns,...

Excel: Excel, cell reference, caveat

cell reference, caveat, clue: It s the floor worksheet function that should do this for you =FLOOR(CellRef*1.05,100) would give you a working formula (replace my CellRef with the Cell Reference!) Only slight caveat is you said rounded down AND to the nearest 100, which I m assuming...

Excel: Excel, correct column, column names

correct column, column names, blank rows: Katrina, the code for this isn t too difficult to write, so I m going to let you record the second half or so. The first part, getting rid of the blank rows, would look like this: Sub DeleteEmptyRows() SheetCount = ActiveWorkbook.Sheets.Count For i...

Excel: Excel, cell a1, clicking tools

cell a1, clicking tools, installation cd: Heather: Presuming your value is in Cell A1 and you have Excel 2007, then you can use this formula: =ROUNDDOWN(A1*1.05,-2) The above formula multiplies cell A1 by 1.05, and then rounds down by negative 2...which is hundreds. Thus, 785698 would round...

Excel: Excel, minites, accurate calculation

minites, accurate calculation, intermediate values: I cannot replicate your problem, the formula works for me even with values over 36 hours. There are a few things you can check: 1.- Evaluate the formula, using the menu Tools- Formula Auditing- Evaluate formula. This will give you the intermediate values,...

Excel: Excel, document answer, a45

document answer, a45, cell a1: NOTE - File sent to your email address: Hi Dawn: Ahh…that helped immensely! Index and Match are going to be the way to go – I have enclosed a revised spreadsheet with the formulas already in place. Now, the formulas are going to look very lengthy…unfortunately...

Excel: Excel, indirect address, true example

indirect address, true example, shot 1: Ivan: Give this a shot: 1) In the same sheet with your data insert this formula into cell E1: =+INDIRECT(ADDRESS(ROUNDUP(ROW()/4,0),IF(MOD(ROW(),4)=0,4,MOD(ROW(),4)))) 2) Copy the above formula down until you have all of the data you need - I speculate...

Excel: Excel, copy paste, b2

copy paste, b2, c1: IVAN You can use the Copy/paste special/transpose feature to do what you need. First paint tne entire area that you want to display on the second sheet. Right clic and select copy. Go to the second sheet and put the cursor where you want the data to...

Excel: Excel, vlookup, menu bar

vlookup, menu bar, a1: Alex, Hope you are doing well. Lets assume that A1 is the cell that you want to change the color if the cell is less than todays date 1. Click on A1 2. go to menu bar - FORMAT - Conditional Formatting 3. A window will pop up 4. Cell Value Is will...

Excel: Excel, sum formula, sumif

sum formula, sumif, aidan: I don t quite follow what you want to do, but I m pretty sure we can do it WITHOUT a macro - sounds a bit like a sumif, but you may be right that a macro would be needed - can you mail me a sample file (aidan.heritage@virgin.net) and I ll see what I can come...

Excel: Excel, salary proposal, spreadsheet module

salary proposal, spreadsheet module, scenario details: Hi Nisa! How are you? Tpo perform a what-if analysis you can: use TOOLS - SCENARIOS (for analysing variables) or TOOLS-Goal seek or Data-table (for analysing 0ne or 2 variables) E.g. A1: 2 B1: 3 B3: =A1*B1 C3: 5 D3: 6 B4:7 Select...

Excel: Excel, cell c1, cell b1

cell c1, cell b1, currency field: Hi Amanullah, If I understand your question correctly, you want to be able to change either the dollar or dinar value and have the other value adjust accordingly. Doing this directly with formulas won t work because if they each have formulas that are...

Excel: Excel, cell manipulations, excel worksheet

cell manipulations, excel worksheet, strange way: You can record your steps on one worksheet. Then you can use a macro like this: Sub AllWorksheets() For each sh in Worksheets sh.activate your recorded macro goes here, or call your recorded macro, like MyMacro (without the...

Excel: Excel, excel spreadsheets, aidan

excel spreadsheets, aidan, f5: Keep asking - I m happy to answer! Yes, you can do this quite quickly Put the formula in the first cell. Ensure the cursor is in the first cell. Now do Edit goto (f5) and (assuming we are doing column A, row 1 onwards) type a1:a65000 (You should be...

Excel: Excel, cell d1, multipling

cell d1, multipling, cell c1: Hi Amanullah, If you enter the Dinar in cell C1 such as 35 then enter the rate in cell D1 such as 29.5 (I assume the rate changes periodically). Then in cell E1 the formula would be =D1/E1 So in other words instead of multipling dollars 100 times the rate...

Excel: Excel, something simple, office xp

something simple, office xp, alma: Alma, you have 2 choices here. The first is to use your mouse after you have typed the = sign to start your formula, and select the sheet and cell you want in the formula. The formula will be created as you select cells. The other option is to name the...

Excel: Excel 2000, sara gilbert, a41

sara gilbert, a41, a44: Sara: Sorry, I just can t call you. I do work full time and volunteer my time for free to this service. So, you can imagine that everyone who sends me questions would want me to do this. Of course it would be nice but not very practical for me. Once...

Excel: Excel 2000, array formula, cell b3

array formula, cell b3, hold down ctrl: Hi, Adrienne, This involves what is called an array formula. In case you re not familiar with it, it s a handy way of processing a single formula over an entire range of cells, all in one step. Since you want to look at a whole range of cells, and only...

Excel: Excel 2000, douglas m smith, graphic object

douglas m smith, graphic object, brainbench: Hi Adrienne, The most likely cause is something in column AE that is overrunning into and over the next two columns. It could be text or a graphic object. If you can t find anything doing this, you can use printing options to exclude those column. ...

Excel: Excel 2000, column headings, memory 1

column headings, memory 1, shortcut menu: Hi, Adrienne, Excel is still holding those two columns in memory because you must have had something in them at one time. Here s how you can delete them from memory: 1) On the column headings at the top, drag over both the headings AF and AG. 2) Do...

Excel: Excel 2000, excel 2000, cakes

excel 2000, cakes, algebra: 1 - this is algebra, not excel. 2 - I m pretty good in algebra 3 - if your sales increased by 100%, you would have sold 2*494, or 988. This is (988-494)/494, or 1, expressed as %. 4 - so, the % increase is (11936-494)/494, or 2316% increase. Or if these...

Excel: Excel 2000, spreadsheet tasks, layman terms

spreadsheet tasks, layman terms, conditional formatting: I work for Norwich Union here in the UK, so know about Insurance! I would PROBABLY do this project in Access if it was down to me, but lets stick with Excel (1) you can use conditional formatting to change the colour of cells dependent on certain conditions...

Excel: Excel 2000, random number generator, column headings

random number generator, column headings, separate list: Hi, Adrienne, (Just wanted to let you know that it looks like you clicked the Submit button twice on this question. Be careful--you only need to hit it once. Since I have to answer every question that comes in, I ll flag the second one as a duplicate.)...

Excel: Excel 2000, list of names, excel 2000

list of names, excel 2000: a few ways: Next to your list, enter =RAND() and fill down, then sort both columns by the RAND column & take the top 10 items in the list of names. You can enter thie formula: =RAND() .1 in a cell (not row 1), say it s in F2 (F1 empty). then use data/filter/advanced...

Excel: Excel 2000, correct result, a31

correct result, a31, excel 2000: Not sure what a roll is -- do you mean row ? If you have data in A1:A31 and some are blank, they won t be counted in =AVERAGE(A1:A31) -- that is, the formula will return the correct result. Put 1 in A1, 2 in A3, leave A2 blank, then take =AVERAGE(A1:A3)...

Excel: Excel 2000, column c, calendar system

column c, calendar system, days per year: Adrienne, I would say yes, as long as you are willing to accept the fact that there is an odd number of days per year, so the data is going to change slightly over time. Anyway, since there are 365 days per year, you can simply add 183 to any cell to...

Excel: Excel 2000, sara gilbert, a43

sara gilbert, a43, excel 2000: Sara That was the only option I had for you, I can t think of another reason it isn t working unless you are concatenating from another workbook. Does the F9 key work when you press it (it should recalculate the entire sheet). If it does then try try...

Excel: Excel 2000, column c, excel 2000

column c, excel 2000, 12 months: When you say 6 months what do you mean exactly? For example, if you have 1/10/04 in A, do you need/want B to have 7/10/04 or do you want 180 days from 1/10/04 (6/29/04)or 182.5 days from 1/10/04? Give me the specific dates you need calculated with examples...

Excel: Excel 2000, xlstart folder, hi bob

xlstart folder, hi bob, target: 1 - If you ve never used a Personal Macro workbook, it doesn t exist. Put the macro into a NEW workbook, and save it with the name personal.xls in the xlstart folder. Before saving it, use window/hide, then exit excel. save it at the prompt to do so. 2 -...

Excel: Excel 2000, shortcut menu, menu system

shortcut menu, menu system, excel 2000: Adrienne, I don t believe you can edit that menu system. You can create a macro that does most of the work for you. For example, you can have the user enter the data they want to find in a specific cell, then press a button that searches for that data....

Excel: Excel 2000, page margins, page hope

page margins, page hope, page break: Hi, Adrienne, I can think of two other ways to accomplish this: 1) You could create margins so large that only one row will fit on a page. Margins are set in File/Page Setup. 2) You could create a multiple selection, making each row a different area....

Excel: Excel 2000, excel 2000, aidan

excel 2000, aidan, amount of time: Various fixes - my own would be to put the totals at the top, which then becomes an easy fix. Another possibility is to put the totals at the same cell on each page, being the MAXIMUM distance away. Finally, you can use the OFFSET function and the COUNTA...

Excel: Excel 2000, sum function, pastes

sum function, pastes, excel 2000: Yes. You need to use a loop that cycles thru all 60 sheets and writes the formula and then copies and pastes it. After you determine the last row of each sheet s data, then use that to determine the address of the sum function. You can use something like...

Excel: Excel 2000, conditional formatting, format option

conditional formatting, format option, format menu: Mark Have a look at conditional formatting in the format menu, you can do exactly what you want using an if function for one or all cells. The formula would be like =if(cell you are currently in 0,) use the format option to change either the text...

Excel: Excel 2000, c drive, excel 2000

c drive, excel 2000, what this means: Adrienne, I would say there are a couple of ways, though none are as simple as it is in Excel 2003. Anyway, you can search for the string that forms the first part of the link (= C:) assuming that it is in the C drive. Finding the cell(s) that contain...

Excel: Excel 2000, pivot table, excel 2000

pivot table, excel 2000, arrows: I would IMAGINE that she has an earlier version of Excel (eg 97) where the pivot table was different - as indeed it is in XP - I think the question should be posed differently though, as the arrows are simply a means to accomplishing a task - I am GUESSING...

Excel: Excel 2000, right arrow, max number

right arrow, max number, maximum number: Hi Matt, How are you? Yes, apparently the maximum numbwer of cells is maximum number of rows x maximum number of columns 65536 x 256 =16777216 cells 1. Maximum number of rows press The Key end and then the down arrow 2. Last columns press The...

Excel: Excel 2000, douglas m smith, blank cell

douglas m smith, blank cell, brainbench: Hi Adrienne, You can use COUNT in an IF statement to check those cells before calculating their average: =IF(COUNT(C5,F5,H5)=0, ,AVERAGE(C5,F5,H5)) If COUNT returns a zero, there are no numbers in the cells and the result will be , a blank cell....

Excel: Excel 2000, b cells, flat dollar

b cells, flat dollar, number 80: Hi Cindi How are you? Could something like the following do? 1. Tools-Macro-Visual Basic Editor 2. insert module 3. copy Function MultiOrCopy(B) If B.Cells(1, 1) = $ Then t = B.Cells(1, 1).Offset(0, -1) Else t = B.Cells(1, 1).Offset(0,...

Excel: Excel 2000, element array, conversion options

element array, conversion options, second element: Hello Gene, If you do text to column manually, you recall that in the third dialog, you can set specific instructions for each column. I your case, you would want to treat these as Text so they won t be converted to date. You can look at the help on Text...

Excel: Excel 2000, macro security, inputbox

macro security, inputbox, command button: Hi Zoe How are you? I can give you only san example. Please try the following: 1. Tools - Macro - security - security level - low Tools - Macro - security - trusted publishers- tick the two boxes at left-lower corner 2. Tools - macro - Visual Basic...

Excel: Excel 2000, vcr type controls, booking system

vcr type controls, booking system, excel 2000: First, let me remind you that there s a builtin feature, sort of, without buttons -- right-click on the VCR-type controls at the bottom left of the workbook to see a list of worksheets and you can then left-click one to access that sheet. That being said,...

Excel: Excel 2000, filter data, excel 2000

filter data, excel 2000: I encounter issue with advance filtering. It won t allow me to copy the filter data to another location and displays only filtered data can be copy to another location . please advise. Be on the sheet where you want the data be copied (assuming you are...

Excel: Excel 2000, dialog box, excel 2000

dialog box, excel 2000, thanks in advance: I assume that by file listing you mean the last used file list - you can reset this by EITHER opening a lot of files, or by using the tools menu, then options to remove the tick from the recently used file list and then putting it back again close the dialog...

Excel: Excel 2000, excel 2000, manipulation

excel 2000, manipulation, cells: Not 100% sure why you need to define the range as a named range within a macro, but still! ActiveWindow.RangeSelection.Address will give you a string containing the current selection address and should be able to be used within the macro to set the named...

Excel: Excel 2000, conditional formatting, vlookup formula

conditional formatting, vlookup formula, default background: Adrienne Try this conditional format For all the cells that *could* show N/A set the font color to be the same as the background (the default background is white). Next, set the conditional format for the same cells so that if the cell value does not...

Excel: Excel 2000, selection object, input box

selection object, input box, excel 2000: You can use the Selection object with the ReferTo property when adding a name, something like: Sub NameSelection() ActiveWorkbook.Names.Add Name:= Test , RefersTo:=Selection End Sub This sub names the selected cells with the name Test once the...

Excel: Excel 2000, vba procedure, actc

vba procedure, actc, excel 2000: Hi Vern How are you? Please edit your macro and change it accotding to the following VBA procedure Sub namingactivecell() Dim ActC As Range Set ActC = Selection ActiveWorkbook.Names.Add Name:= test , RefersToR1C1:=ActC End Sub Cheers Adelaid...

Excel: Excel 2000, excel 2000, manipulation

excel 2000, manipulation, cells: The code below will create a range named test for the range of cells currently selected at the time it is run. It deletes the range named test before it re-creates it. See if this helps Dim R As Range On Error Resume Next ActiveWorkbook.Names(...

Excel: Excel 2000, range selection, book2

range selection, book2, ogilvy: Vern, To create a named range that refers to the selected cells selection.Name = MyRange will create a named range MyRange Let me illustrate from the immediate window: Select B9 to E18 Range( B9 ).Resize(10,4).Select demonstrate what is...

Excel: Excel 2000, excel vba help, code application

excel vba help, code application, strange behavior: Hello Vern Application.ScreenUpdating = False for each sh in thisworkbook.Worksheets sh.Activate sh.Unprotect Password:= ABC process this sheet sh.Protect Password:= ABC Next Application.ScreenUpdating = True For a lot of things, you...

Excel: Excel 2000, douglas m smith, brainbench

douglas m smith, brainbench, loop through: Hi Vern, Here s a routine that will loop through all the sheets in the active workbook and unprotect them, assuming they all have the same password: Sub Unprotector() For Each x In Sheets x.Unprotect whateveryourpasswordis Next x End Sub ...

Excel: Excel 2000, password protection, excel 2000

password protection, excel 2000, macros: You can use two macros like these: Sub ProtectSheets() Dim mySheet As Worksheet For Each mySheet In ThisWorkbook.Worksheets mySheet.Protect Pass Next End Sub Sub UnprotectSheets() Dim mySheet As Worksheet ...

Excel: Excel 2000, visual basic menu, select tools

visual basic menu, select tools, password protection: Hi Vern, Open your workbook with password protection that you want to add the Macro to and press Alt+F11 to open the Visual Basic Editor. Then from the Visual Basic menu select Insert then Module then in the right-hand blank pane paste the following...

Excel: Excel 2000, password protection, excel 2000

password protection, excel 2000: Sub Protect() Dim Sh As Worksheet For Each Sh In ThisWorkbook.Sheets On Error Resume Next Sh.Protect Password:= Test Next Sh End Sub Sub UnProtect() Dim Sh As Worksheet For Each Sh In ThisWorkbook.Sheets On Error Resume Next Sh.UnProtect...

Excel: Excel 2000, macro code, password protection

macro code, password protection, excel 2000: You don t need code for that, just go into the visual basic editor then the tools menu, project properties and use the protection tab - lock project for viewing and set a password - job done! When you close the workbook and then reload it, you will not be...

Excel: Excel 2000, conditional formatting, vlookup formula

conditional formatting, vlookup formula, blank cell: Adrienne, It can be done by nesting the function in an IF statement. For example: =IF(ISNA( Your Formula ), , Your Formula ) Will display a blank cell or, =IF(ISNA( Your Formula ), - , Your Formula ) will display a dash. Obviously, copy and paste...

Excel: Excel 2000, password protection, excel 2000

password protection, excel 2000, pw: Sub Answer() Dim pw as string, Sh as Worksheet pw= ThePasswordGoesHere For each sh in Sheets sh.activate not really nec. but not sure what else you want to do sh.Unprotect pw DO YOUR MANIPULATION HERE sh.Protect pw ...

Excel: Excel 2000, single board, excel 2000

single board, excel 2000, plywood: Do you mean you calculate the total area required the need to divide that by the area of a single board/ Perhaps =(100*15)/(10*4) Where the first bracket calculates the total required & the second calculate the board area, then divides total by boar...

Excel: Excel 2000, left hand corner, excel expert

left hand corner, excel expert, upper left hand corner: I hate to choose the beyond my expertise... for a question like this, but it IS. However, I like to tell you that it CAN be done using VBA. You can ask another Excel expert who states that they have VBA programming experience, or you can ask at www.vbaexpress.com...

Excel: Excel 2000, ogilvy, excel 2000

ogilvy, excel 2000, vern: Vern, JE McGimpsey has written most of the code for you already. See his page on this topic: http://www.mcgimpsey.com/excel/lookuppics.html You will probably need to make a few modifications to get exactly what you want. -- Regards, Tom Ogilv...

Excel: Excel 2000, camera object, dynamic ranges

camera object, dynamic ranges, current sheet: In this case, you may want to use macros. The following code will remove the latest picture added on the active sheet, and include the one that is passed as parameter, appending its name to a fixed path: Sub BringImage(strImage As String) Const gPath...

Excel: Excel 2000, excel 2000, vern

excel 2000, vern, filenames: Hello Vern I did send an answer to this question yesterday, unfortunately you appear not to have received it. I can help achieve what you want . Can you tell me what format the the graphics would be in and how and where you would want them displayed....

Excel: Excel 2000, vbe, excel 2000

vbe, excel 2000, filenames: Press Alt F11 to get to the VBE, where macros live. Double click the sheet name where you will type the file name in the Project Explorer window. On the right side look for General in the top left wide drop down and then click Worksheet. In the top right...

Excel: Excel 2000, cell a1, picviewer

cell a1, picviewer, cell b1: Hi Vern, You might try a couple of different ways. First is to use Hyperlinks. If you use cell A1 to type in your code to select a graphic. Then if you have a table with codes in column C and corresponding hyperlinks in column D then in cell B1 you...

Excel: Excel 2000, conditional formatting, vlookup formula

conditional formatting, vlookup formula, excel 2000: Conditional formatting should work fine - you need to choose Formula Is , then, using the active cell as a reference, enter =ISERROR(D1) [if D1 is the active cell]. Then click Patterns, and on the font tab, select the color which matches the background (probably...

Excel: Excel 2000, default filename, folder path

default filename, folder path, documents and settings: Hi Vern, I recommend having the macro change the current working directory so that the path doesn t have to be entered or browsed to. You can do this two ways: 1. Use the ChDir() VBA function to change the working folder. If you want to change it to...

Excel: Excel 2000, fileexists, filesystemobject

fileexists, filesystemobject, fso: I am not sure if this will fit with what you would like to do later, but at least this code may give you some ideas. It will prompt the user for a file name, and it will check if it exists on a given directory, fixed in the code: Sub TestFile() Const...

Excel: Excel 2000, change directory command, file open dialog box

change directory command, file open dialog box, excel 2000: Sorry for not being clearer the FileToOpen = Application.GetOpenFilename( Text Files (*.txt), *.txt ) will store in the variable FileToOpen the name of the file the user selected. The filter I ve used here (*.txt) should be changed to jpg or gif,...

Excel: Excel 2000, fname, chdir

fname, chdir, graphics files: Wow, methinks you misinterpreted me -- I wasn t critical at all (but that doesn t help matters if you did think I was being so) -- I was actually trying to help. I am always in good spirits, but you can t tell that, I guess! Sorry if you misread what I typed...

Excel: Excel 2000, excel 2000, macros

excel 2000, macros, free time: 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 do so unless they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros are...

Excel: Excel 2000, example routine, spath

example routine, spath, c data: This example routine will prompt for a file name and subsequently save the active workbook using that name (and location): Sub GetSaveAsFileNameExample() Dim vFilename As Variant Dim sPath As String sPath = c:data emp vFilename...

Excel: Excel 2000, conditional formatting, vlookup formula

conditional formatting, vlookup formula, error indicators: Adrienne, there are two ways to take care of this. Let s say that the VLOOKUP formula is in J10. 1) Using conditional formatting Conditional formatting actually is one way to handle this. In the dialog box, you can use the formula =ISERROR(J10) and...

Excel: Excel 2000, download open office, sony vaio

download open office, sony vaio, virtual memory: Peter I have tried a variety of different op systems but it opens on all of them here. I would suggest trying one thing, go to openoffice.org and download open office for vista on the laptop, then try opening it, this will give you a better idea as to...

Excel: Excel 2000, cell b3, cell b1

cell b3, cell b1, word pie: not with a vlookup. You need to array-enter* this formula: =INDEX(C:C,MATCH( CherryPie ,A1:A3&B1:B3,0)) or, if the keyword Cherry is in D1 & Pie is in D2, then =INDEX(C:C,MATCH(D1&D2,A1:A3&B1:B3,0)) *array-enter means to press ctrl+shift before pressing...

Excel: Excel 2000, cell b3, cell b1

cell b3, cell b1, word pie: Hi Adrienne, As far as I know VLOOKUP will not lookup two Items. I believe part of the reason is that VLOOKUP is made to find unique items in a database. What you have explained is more of a filtering of the data. I think my question would be what would...

Excel: Excel 2000 Autofilter and Status Bar message, pet peeves, status bar message

pet peeves, status bar message, autofilter: Oh, Jan, this is one my pet peeves with Excel. It has frustrated many people, so you re in good company. The problem is that you have automatic calculation turned on. Though Excel does indeed flash the number of filtered records in the status bar, it then...

Excel: Excel 2000, Complicated Lookup formula, cell b2, cell c3

cell b2, cell c3, cell c2: Hi Greg, How are you? What about the following for E3 cell of Concad sheet =HLOOKUP(C3,Ac2:Ak202,MATCH(B3,Sheet2!B3:B202,)+1) it looks up C3 in the percentage table and retrieves the value that fits this column and the row that B3 occupies in the...

Excel: Excel 2000 Complicated VB for Combo box, blank cells, blank cell

blank cells, blank cell, column count: To find the first blank cell in the range G31:G43 Dim rng as Range, rng1 as Range, rng2 as Range set rng = Worksheets( Sheet1 ).Range( G31:G43 ) if application.CountA(rng) = rng.count then msgbox No blank cells in range exit sub End if set rng1...

Excel: Excel 2000-Copy and Past Row to Column, array formula, fb 3

array formula, fb 3, eb: Excellant description of the problem! I love it when people put before and after examples into their question. Okay, I know this works in the later versions of Excel, so bear with me while we find out if it works in version 2000. 1) Select cells A1...

Excel: Excel 2000 & Copying, vba code, button work

vba code, button work, wb1: OK, try this 1 - Tools/Addins -- if anything is there, deselect it. 2 - Window/Unhide - if anything is there, unhide it and close it 3 - Alt/F11, then F4 - if anything is there aside from the workbook(s) you opened, close them. Now, try copy from 1 workbook...

Excel: Excel 2000 - change column left to right, array formula, change column

array formula, change column, fb 3: Jaz: Excel does not provide a way to transpose in that manner, but you can do a Sort to reorder the data. First you will need to number all of the rows, or columns, depending on which type of switch you are attempting. Lets use this as an example...

Excel: Excel 2000 - change column left to right, change column, selection area

change column, selection area, mr nathan: I have uploaded a sample spreadsheet to the following location: http://home.swbell.net/nate-sus/excel/sortinfo.xls 1) B1 through D4 contains the data we want to sort. 2) In column A – I added a numerical sequence; an index or key. This provides a basis...

Excel: Excel 2000 - change column left to right, eb, change column

eb, change column, fb 3: here is a simple code loop to reverse any number of columns starting with Column A: Option Explicit Sub ReverseColumns() Dim numCols As Integer Number of Columns to roll Dim i As Integer Set the number of Columns to Roll numCols...

Excel: Excel 2000 - Excel 2003 and Vista, xl2000, excel 2000

xl2000, excel 2000, macros: None that I know of (Vista specific) -- I use that combination all the time. One xl2003 issue which has nothing to do with Vista is that this works in xl2000 and not in xl2003: Range( A1:B5 ).Calculate That is, range(...).Calculate no longer works in xl2003,...

Excel: Excel 2000 & Excel 97, activex controls, xl2000

activex controls, xl2000, demod: Private Sub Combobox1_Click() ActiveCell.Activate your code End Sub for commandbuttons, change the takefocusonclick property to false. for all other activeX controls, use the activecell.activate as the first command in the event. (demod above in the...

Excel: Excel 2000 - filename and path in the footer, dialog boxes, custom button

dialog boxes, custom button, copy button: Jan, there are a couple of ways to do this. The most versatile, I think, is to place some code in VBA and create a toolbar button to insert the Path into the active cell. So let s walk this through: First thing, we want to make this available to all workbooks...

Excel: Excel 2000 If/Then formula, cell b1, cell a1

cell b1, cell a1, cell c1: Hi Dorothy, Yes you can. Let us assume that cell A1 contains the amount of hours worked. cell B1 contains the regular 40 hour rate. cell C1 contains the overtime rate. then in cell D1 place the following formula. =IF(A1 =40,A1*B1,IF(A1 40,40*B1+(A1-40)*C1,...

Excel: Excel 2000 help, ogilvy, excel 2000

ogilvy, excel 2000, csv: Modify one of the code sets on this page to do this: http://www.rondebruin.nl/copy5.htm You could put in your column to calculate the workbook name and page, then filter on this column with the code for each unique location and copy all the records for...

Excel: Excel 2000, index & Match on two cells, array dimensions, spreadsheet programs

array dimensions, spreadsheet programs, hlookup: Greg, You need to use one of the LOOKUP functions if you want to match items. Here s a quote from help: =========================================================== The LOOKUP function has two syntax forms: vector and array. The array form of LOOKUP...

Excel: Excel 2000 Linking Problem, excel 2000, hth

excel 2000, hth, windows explorer: I tried dummying up the situation in excel 2000 but I m not getting the condition you describe. Maybe the version of 2000 wasn t with all the service packs installed? But you say that if you do this in 2003 you don t get the problem. So the solution would...

Excel: Excel 2000 linkage problem, linkage problem, excel 2000

linkage problem, excel 2000, new location: (1) when you open the sheet you should be prompted to update the links - select NO to this (although you MAY have set the document up to not prompt you at which point you are stuck) (2) use the edit menu and choose the edit links option to set them to the...

Excel: Excel 2000 linkage problem, linkage problem, excel 2000

linkage problem, excel 2000, simple search: 1. Try Tools, Options. Under the Edit tab uncheck Ask to update automatic links . Since you did not say what version of EXCEL you are using this will vary from version to version. 2. Just a simple search and replace searching for old path and replacing...

Excel: Excel 2000, linkage problem, linkage problem, change source

linkage problem, change source, excel 2000: Hi Sy, When you open a linked workbook it should give you the option to update links. You can press Don t update button to cancel links for that session of Excel. Once open you can go to the Excel menu and select Edit then Links you can press Change...

Excel: Excel 2000 lists, dave edwards, vlookup

dave edwards, vlookup, dave walker: Send the spreadsheet or a sample to rmadsen@iname.com and I can tailor the answer for you. I assume you have a table somewhere of people s names and phone numbers. Normally I would suggest using vlookup, but this function only works if the key information...

Excel: Excel 2000 Macro, macro in excel, left hand side

macro in excel, left hand side, excel 2000: Hi Connie, How are you? Please do the following: Run Excel Open the file where the macro was stored Tools - Macro - Visual Basic Editor Select Module1 ( the Project explorer is on the left-hand side of the screen) File - Remove - Module1 No ...

Excel: Excel 2000/Merging data from 2 worksheets, musical genres, vlookup

musical genres, vlookup, product numbers: vlookup will do this for you - it s =vlookup(WHAT,WHERE,WHICH,LOGICAL) WHAT is the value you want to look up - so the product number WHERE is the data you are looking up - the product number needs to be in the first column of the range WHICH is...

Excel: Excel 2000/Merging worksheet info, vlookup formula, musical genres

vlookup formula, musical genres, product numbers: If I correctly understand the scenario you describe, this can be done with a vlookup formula from the smaller list to the larger list. See excel help on Vlookup for details. You would probably want a 4th argument of false for an exact match. -- ...

Excel: Excel 2000/merging data from 2 worksheets, musical genres, product numbers

musical genres, product numbers, time copy: Les Sorry it took so long, been a little busy up here, anyway without looking at the layout a bit more I would be hesitant to advise one option over another, however, if you would like to send a copy to richard@littlewingweb.com I will look at it and get...

Excel: Excel 2000 Password Protection, douglas m smith, third party software

douglas m smith, third party software, sheet cells: Hi Robert, You can try copying the sheet cells to an unprotected sheet. This may or may not get you what you want depending on the links, macros and other sheet complexities that may be involved. There is third party software available for Excel password...

Excel: Excel 2000 Prompt save every sheet close, excel sheet, excel 2000

excel sheet, excel 2000, yahoo: I suspect you didn t place the code in the appropriate place. It MUST go in the Thisworkbook events: Right-click the Excel LOGO near the File Menu, select View Code, put it there. If you DID this, then the only way it wouldn t work is if your application.enableevents...

Excel: Excel 2000 problem - custom toolbar and macros, custom toolbar, initial question

custom toolbar, initial question, excel 2000: Typically, the *.XLB filename is somthing like USERNAME.XLB where the username depends on your network or the users PC settings. If you search for *.XLB files on the users PC and delete every one of them (typically there s only one) Excel will default to...

Excel: Excel 2000 - Trendline in chart, linegraph, trendline

linegraph, trendline, quarterly data: Richard As far as I can tell there isn t a way to do this using the existing data for the current chart, you can however create another table to create two data tables and combine them in one chart using a linegraph, you would have to create a trendline...

Excel: Excel 2000 - some thing like Conditional Formatting, conditional formatting, mr nathan

conditional formatting, mr nathan, max 2: Good morning JAZ! As you suspected, this can be done with conditional formatting. I have placed a sample spreadsheet with the conditional formats here: http://home.swbell.net/nate-sus/excel/conditional.xls Basically, you want to add the two conditions...

Excel: Excel 2000 time problem, cell b2, cell a1

cell b2, cell a1, flight log: Eric I was a little surprised to find that it didn t work as expected. When I put a value in cell a1 like .07291, and then format the cell b2 to show time, and run this code ARR1 = Range( A1 ).Text Range( B2 ).Value = ARR1 it displays this time: 1:44:59...

Excel: Excel 2000 used in Windows XP, dell xps, office 2000 premium

dell xps, office 2000 premium, older computer: The older versions of Adobe created files by PRINTING to them (I m remembering back to get this) so this MIGHT be the issue - HOWEVER, key to this problem is the fact that it ISN T an Excel issue - it s an adobe one - although I do notice from the query that...

Excel: Excel 2000 VBA, box gutter, victorian conservatory

box gutter, victorian conservatory, a74: I isolated the code you said wasn t working Sub AA() If Lflag = 0 Then If Worksheets( quote ).Range( G13 ).Value = a3 Then Lsupport = MsgBox( Is a Lean Too Support system Required , vbYesNo, Lean Too Support Question ) End If End If...

Excel: Excel 2000 vba code lookup problem, matrix report, vba code

matrix report, vba code, input boxes: It s not clear from the code example where the choices are being stored and I ve never looked in detail at these but I m GUESSING that the values don t update on the change event of the control, but probably the exit event - however, you can always set the...

Excel: Excel 2000 & WordArt, hp laserjet printers, hp printers

hp laserjet printers, hp printers, hp website: If the version of Office remains the same, then the issue is with the Operating System - my GUESS would be the printer driver in use - it would be worth checking on the HP website for updated drivers (OR possibly going back to an OLDER driver - we had a similar...

Excel: Excel 2000 Workbook Links, change source, source button

change source, source button, excel workbook: Hi Bethanne, In Excel 2002 they have a break links button to get rid of the links. This has been a problem for Excel for a long time, how to get rid of old links. Before the Excel 2002 version what I always did was open the Links menu and select the link...

Excel: Excel 2000, external reference, click tools

external reference, click tools, excel 2000: One way is to click Tools, Options and select the Edit tab. Uncheck Ask to update automatic links Or, you can reference the current workbook instead of the original workbook when you edit the link. Once the link is to the current workbook, the prompt...

Excel: Excel 2002, frequency formula, manual additions

frequency formula, manual additions, age bands: Ruth I do very little work with staistical functions which the FREQUENCY formula is, so I m not sure that I m a good on e to answer your question. However--Not knowing exactly what your data looks like --If you simply want to count the number of respondents...

Excel: Excel 2002, table wizard, pivot table

table wizard, pivot table, excel 2002: A pivot cache can hold more the 65536 records, so you could move the data to an access database and combine it there. then link a pivot table to the access database. When you start the pivot table wizard, you can specify that you want Multiple Consolidation...

Excel: Excel 2002, selection type, type option

selection type, type option, excel 2002: Sorry, I was thinking of multicolumn that is not supported. Here is code for getting the selected items. Note that these listboxes start with 1 rather than 0. Assign this to the listbox (or use in other code). Sub listbox1_click() j = 1 With ActiveSheet.ListBoxes(...

Excel: Excel 2002, microsoft excel solver, solver users

microsoft excel solver, solver users, excel 2002: Never tried it, but I would assume you could do it using VBA. If nothing else, you could set screenupdating to off and unhide the sheet, run the macro, hide the sheet and turn screenupdating back on. Here is a source of information on working with Solver...

Excel: Excel 2002, excel 2002, bolding

excel 2002, bolding, ogilvy: This is called rich text formatting, but it only works with text that is hard coded - it doesn t work with formulas. The only alternative would be to use a calculate or change event to run code that builds the string, places it in the cell and bolds the...

Excel: Excel 2002, calendar control, control icon

calendar control, control icon, caption property: Hi Laurie, In Excel 2002 there is no built-in way to do this. I think the Excel developers definitely should have provided this feature. Fortunately, there is a way to add this capability using a simple userform with a Calendar control. Here s how: ...

Excel: Excel 2002, input values, pre filter

input values, pre filter, target: Hi Steven Yes, but if there are more than a few input values the numbers become astronomic. If I wanted to check 16 numbers for all combinations that sum to x, I would have to look at 65536 different combinations (2^16). You will need a Macro or Function...

Excel: Excel 2002, chip pearson, conditional formatting

chip pearson, conditional formatting, waste of my time: You don t talk about any criteria in terms of which rows would be colored or how updated would affect this or what the updates would be. In any event, Chip Pearson has presented some ideas on how you could do this using formulas and conditional formatting...

Excel: Excel 2002, format button, hi bob

format button, hi bob, waste of my time: Select everything (Ctrl/A) Use Format/Conditional Formatting Change Cell Value Is to Formula Is in the formula, enter: =MOD(ROW(),2)=0 then click the format button, then the Patterns Tab. Click on a color for every EVEN row. Click OK, then Click...

Excel: Excel 2002 - 4 day work week, analysis toolpak, man days

analysis toolpak, man days, excel 2002: The nice to have is actually a clue to the best way to do this - you will need to install the Analysis toolpak (tools, addins, check the analysis toolpak option) which will then allow you to use the NetWorkDays function - this calculates the working days between...

Excel: Excel 2002, flag changes, waste of my time

flag changes, waste of my time, excel 2002: Not really I m afraid - you can use tools track changes to flag changes, but it doesn t put the stripes on that you want - you could POSSIBLY use the change event of the workbook to change the background of cells when changed, but this would give you more...

Excel: Excel 2002, problem occured, odd rows

problem occured, odd rows, format button: select all the cells use Format/Conditional Format change Cell value is to Formula Is enter =MOD(ROW(),2)=0, then click FOrmat button, then Patterns tab, and pick a color for even # d rows Click OK Click Add, enter =MOD(ROW(),2)=1 and repeat the process...

Excel: Excel 2002, grave accent, history worksheet

grave accent, history worksheet, menu point: Directly from Help when hide formulas and show formulas was typed in the search box Display or hide formulas Switch between displaying formulas and their values on a worksheet Press CTRL + (grave accent). Prevent a formula from displaying in...

Excel: Excel 2002, john walkenbach, support dates

john walkenbach, support dates, single quote: Excel doesn t support dates before 1900. You can enter such a date (preceed it with a single quote), but must be treated as a string. John Walkenbach wrote an addin that works with earlier dates. It might do what you want. http://j-walk.com/ss/excel/files/xdate.htm...

Excel: Excel 2002, floating point arithmetic, floating point numbers

floating point arithmetic, floating point numbers, math packages: No, it isn t really a bug. Excel complies with IEEE standard for floating point numbers (#754 I believe). You are well aware, I am sure that numbers in a computer are stored in binary. Just like we can t represent all numbers exactly in decimal (take...

Excel: Excel 2002, 3d graph, graph chart

3d graph, graph chart, chart wizard: A 3D graph is one of the choices in the graph/chart wizard. You have to lay out your data x and y values on the left and top of a table in the worksheet and z-values at the intersection. Both the x and Y axes are category axes (like a line chart) rather...

Excel: Excel 2002 - Formula Audit, douglas m smith, brainbench

douglas m smith, brainbench, excel 2002: Hi Judy, If you just want to see the formulas themselves, hold down the Control key and hit the key (it s on the left side of the 1 key). To toggle back to the normal mode, do it again. The instructions you describe will draw arrows between all the...

Excel: I have Excel 2002 installed..., keyboard method, excel 2002

keyboard method, excel 2002, spreadsheet: Sorry, not familiar with a fast save feature thats is suspose to be included . Is that your name for it or EXCEL s name for the feature you are looking for? What does fast save do that the diskette tool on the toolbar not do? Using the diskette tool...

Excel: Excel 2002 SP3 or ??, data labels, text boxes

data labels, text boxes, quality indicators: I don t know of another program you should use, but drawings (including text boxes) are notoriously bad for staying where you put them. You can actually use a data point label to contain the entire text of what you want to show in the text box instead. Depends...

Excel: Excel 2002, manual additions, layout window

manual additions, layout window, age bands: Ruth, I would say that you have two options. The first is to use multiple pivot tables to keep track of the data. To do that, you would select the data, and that can just be the age column (the whole column would be best) - make sure it has a title in the...

Excel: Excel 2003, cell1, sh2

cell1, sh2, sheet1: Hello David, Sub CopyData() Dim sh1 As Worksheet, rng1 As Range, cell1 As Range Dim sh2 As Worksheet, cell2 As Range Dim i As Long Set sh1 = Worksheets( Sheet1 ) Set sh2 = Worksheets( Sheet2 ) i = 1 Set rng1 = sh1.Range( A6,C5,E11,G10 ) Set cell2...

Excel: Excel 2003 (11.5612.5703), colums, column number

colums, column number, different numbers: Sounds very much like VLOOKUP to me - this is a very useful function (so useful I ve explained it in full at my own website - www.aidanheritage.co.uk/Excel ) - however, to recap briefly it s =vlookup(what,where,which,logical) WHAT is the value you want...

Excel: Excel 2003, bessy, apostle

bessy, apostle, sorts: austin You cant unless you remove them, Excel sorts on the first character in each cell. You can parse the column into individual columns, then sort them on the main column but that will only work if you have the same amount of characters in all the columns....

Excel: Excel 2003, swbell, spreadsheet

swbell, spreadsheet, lotus: Kathi: I speculate you are running into some issues with how Excel handles time. Please send your spreadsheet to Lotus@swbell.net and I will take a look. Notes on how Excel handles time: 1 = 1 Day 1/24 = 0.041666667 = 1 Hour 1/24/60 = 0.000694444...

Excel: Excel 2003, cell c5, time bank

cell c5, time bank, lunch time: Fernando I just looked up Castro Verde on my world map--isn t the internet amazin we can talk all over the world to each other, and its also amazing to me that so many people speak or at least type english, I know that would be very difficult for me. ...

Excel: Excel 2003, cell b2, data validation

cell b2, data validation, microsoft excel: What other cells? Suppose you have a dropdown in cell B2. When you choose from that, you d like the price to show in C2 and the part number to show in D2? Here s a tutorial on that: http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm...

Excel: Excel 2003, control toolbox, calendar object

control toolbox, calendar object, date selection: Lisa, Yes it is possible. Click Insert Object and select Calendar Control 11.0 (may be slightly different depending on your version of Excel and the options you loaded with it). This will insert the calendar object in a spreadsheet - move or resize...

Excel: Excel 2003, cell b2, array array

cell b2, array array, counta: Dave, I regret that my answer did not satisfy your needs. The manner in which you asked the question left me with the impression that VBA might have been out of the scope of your knowledge base and therefore not worth my time to develop. I mean no offsense...

Excel: Excel 2003, alternative school, quarterly reports

alternative school, quarterly reports, cells: Phylis I tried to answer your question earlier but couldn t get on the allexperts web site. In any event here it is. I believe the following formula will work for you. =+WEEKNUM(B8,2)-WEEKNUM(A8,2)+1 Where B8 is the ending date and A8 is the beginning...

Excel: Excel 2003, numeric portion, hi bob

numeric portion, hi bob, k1: You need to set up 3 criteria ranges - one for each type of item. I ll use item title as being in A7. in K1 (outside your range) enter =$A7 in K2 enter onw of the items, like Item1 fill K1:K2 to M1:M2 - change the values in L2 and M2 to the other item...

Excel: Excel 2003, ppmt, ipmt

ppmt, ipmt, mathematical ability: I m afraid this function is outside my area of mathematical ability - the help file in excel explains this function, but as it uses mathematical formula that means I cannot copy it across here (it also explains why I cannot help as as soon as the symbols go...

Excel: Excel 2003, vlookup function, student names

vlookup function, student names, student numbers: Jeanne In the Second sheet you can use the Vlookup function, which means that the Students need to be sorted in ascending order and those with the same name will make the vlookup non functional. The formula will look like this =VLOOKUP(B8,[vlookuplink.xls]Sheet1!$A$1:$B$10,2)...

Excel: Excel 2003, company materials, control tab

company materials, control tab, link option: Display the FORMS toolbar and add a checkbox. Assign any cell to it by using the cell link property of the checkbox. Right click the checkbox and left click Format control. Under the Control tab, in EXCEL 2003, you will find the cell link option. Assuming...

Excel: Excel 2003, target cells, target value

target cells, target value, novist: Versions of Excel prior to 2007 only allow 3 Conditional Formats, so the only option would be VBA. The usual way is to use the WorkSheet_Change event. Quick Instructions On How to Add Code to a Sheet Object Open the VBE (Alt+F11) and then double click...

Excel: Excel 2003 bell curve, bell curve, excel 2002

bell curve, excel 2002, mistake: Iain: It really does not matter to me if you rate me poorly or not. But, I d suggest that in the future you learn to ask better questions and/or phrase your questions better. If you ask a question that would only require a Yes/No answer then you should...

Excel: Excel 2003 Cell Population, prayer leader, group attendance

prayer leader, group attendance, prayer leaders: It can be done without macros, the following solution only requires an additional column to be used on the Fall PG attendance worksheet. For the case, I have filled up the first 30 rows of the Dorm Information sheet with data, so all the formulas have...

Excel: Excel 2003 - Changing Case, case option, change case

case option, change case, cells: Vicki There isn t a real simple way to do the conversion as there is in word, but you can use the UPPER function and it will do the job for you. For example-- Assume that in cells A1 thru C14 you have your text. In another part of the worksheet create...

Excel: Excel 2003 - Compound Interest, analysis toolpak, compound interest

analysis toolpak, compound interest, excel 2000: A1: Blank A2: 1 B1: 1000 B2: =Round(B1*1.02,2) C1: =B2-B1 drag C1 down to C2 select A2:C2 and drag down to 300 cells. I don t understand this comment ( if possible, Rounding off 1041. 21.00) but perhaps you can adjust the rounding formula...

Excel: Excel 2003 - Compound Interest, cell b2, principle and interest

cell b2, principle and interest, ipmt: Terry There are a couple of ways that you can do what you want. I assume you need a schedule that tells you what the value is at the end of each day, so I d approach it as follows. In column A starting with row 2 place the number 1. then copy down...

Excel: Excel 2003: copy sheet to another workbook, updating links, apples and bananas, macro menu

apples and bananas, macro menu, entire department: One way of doing it is to use the menu Edit- Replace, and replace the name of the workbook ([Bananas.xls]) with the empty string. This way the references will change to the current workbook.If you want to automate it, you can record a macro (menu Tools- Macros-...

Excel: Excel 2003 data series problem, speadsheet, default user

speadsheet, default user, microsoft excel: The only thing I can suggest is that you run Excel troubleshooting while logged in as the user. See: http://www.officearticles.com/excel/troubleshooting_microsoft_excel.htm Perhaps it s the XLB file? Not sure. Do the steps in the order given. See if it...

Excel: Excel 2003 Error, proper parameters, oracle client

proper parameters, oracle client, oracle db: It doesn t look like the error is on Excel or XP, but on the ODBC configuration to Oracle on the computer. It looks like it is properly configured in the XP computer, but needs to be set up on the other computer. Without entering in much detail, you will...

Excel: Excel 2003 or earlier VBA, libararies, object browser

libararies, object browser, extensibility: Gregory, You can do it in xl97 or later. Here is an article that tells how: http://support.microsoft.com/default.aspx?scid=kb;en-us;160647&Product=xlw97 XL97: How to Programmatically Create a Reference The reference has a remove method as well....

Excel: Excel 2003 email links, e mail address, mail link

e mail address, mail link, draft message: Two solutions. 1. HOLD your mouse pointer down after clicking to keep it from following the hyperlink. 2. Use a macro to remove the hyperlinks in the entire worksheet. Run it as often as you like, or as often as necessary. See: http://vbaexpress.com/kb/getarticle.php?kb_id=73...

Excel: Excel 2003 formulas, excel 2003 formulas, anwer

excel 2003 formulas, anwer, merry christmas: The only reason it would raise an error that I can think of would be if the item you wanted to exclude was not a value. The AVERAGE formula you proposed is fine. It s great that you were able to come up with something that works on your own. Merry Christmas,...

Excel: Excel 2003 - Highlight cells in a column that are not values x,y,z, cell a2, cell values

cell a2, cell values, months of the year: Hi Sam, You should be able to use conditional formatting using OR in your formula should let you do an example with the 12 months. In that example, highlight the column (except for the title) so that you pick up existing as well as potential new entries....

Excel: Excel 2003, level of expertise - 5, vlookup formula, nonzero values

vlookup formula, nonzero values, countif: Hi Joann, If I understand your question, your VLOOKUP is returning zeros for the portion of the month that other areas of sheet haven t been filled in for yet. So you want the graph to just plot the portion of the month for which the VLOOKUP is returning...

Excel: Excel 2003 - lookup, currency rate of exchange, cell b6

currency rate of exchange, cell b6, currency conversion: CDP I m not real clear on how your data is set up, etc. but try this. Use the VLOOKUP function to look up the exchange rate of the value in B7 to US dollars. I assume you have this exchange rate in your table D4 thru F17. Then multiply or divide (depending...

Excel: Excel 2003 Macro, row header, col1

row header, col1, data columns: If the cells will be blank after the to date entry, then Sub ABC() Dim col1 As Long, col2 As Long ActiveSheet.Columns.Hidden = False col = Cells(2, IV ).End(xlToLeft).Column col1 = Cells(2, col).Offset(0, -13).Column If col1 6 Then Cells(2, 7).Resize(1,...

Excel: Excel 2003 mulitple worksheets, brick wall, final score

brick wall, final score, f 90: Hi CDP How are you? Let s assume that you have - the exams types and weights in Sheet1, range A1:B4 - the overall mark and grade in Sheet1, range F3:G8 ( in col F the lower limits of the intervals and grades in col G) - The exams marks of the 1st student...

Excel: Excel 2003 - noncontiguous data in array formulas, array formulas, array function

array formulas, array function, multiple regression: ay Greg well, as you probably already know, linest is an array function. x values has to be in an array, so they have to be in one worksheet, unless i understand arrays wrong. that does not mean that you havre to redo the whole thing though, destroying...

Excel: Excel 2003 - noncontiguous data in array formulas, array formulas, going in the right direction

array formulas, going in the right direction, multiple regression: Good afternoon Greg! Okay, you got me - I have never used LINEST before. As such, I ll play around and see what happens: Test#1 - Two columns of data in same workbook - A1:B2 contains the values: A B 5 6 6 8 =LINEST(A1:A2,B1:B2) Answer = 0.5...

Excel: Excel 2003 Plotting, real time data, blank cells

real time data, blank cells, conditional formatting: You can use conditional formatting to hide the errors, using a formula similar to this in the menu Format- Conditional formatting area: =ISNA(A1) And then set the ink to white, or whatever your cell color is. Regarding dynamic ranges, that can work if...

Excel: Excel 2003 Question, format menu, add button

format menu, add button, a3: Steve You can use the Conditional Formatting option from the Format Menu. Select your cells that you want the formatting to apply to and go to Format Conditional Formatting Select the Drop Down option for Condition 1 and click on Formula is, A3...

Excel: Excel 2003 Question, douglas m smith, cell reference

douglas m smith, cell reference, personal bills: I have a saying, an ounce of structure prevents a ton of programming . If that cell is going to move around, it s going to be far more difficult to find it, including the possibility of a macro being necessary. You might trym naming the balance cells...

Excel: Excel 2003 (SP3 installed) Graph Axis Tic Marks, minor axis, chart updates

minor axis, chart updates, ticks: Hi Charles, I do not have Excel 2003 available (I m still at Excel 2000) so cannot easily check to see if a new chart feature may have been added that would give more flexibility in the way tick marks are drawn, but I suspect that the demand for this capability...

Excel: Excel 2003-Speedy Vlookup Function, vlookup function, daily basis

vlookup function, daily basis, bpo: First of all I have no idea what a BPO is and really don t know how that affects the question and/or my answer. Secondly, there is no way any function can really return its value any faster. The fact that you have so much data is what is causing the...

Excel: Excel 2003, SUMIF, b100, xyz

b100, xyz, cells: I d prefer a more exact example -- are you looking for xyz in any PART of any cell in rows x to y and in any columns? can you supply me with exact ranges and text (making it easier for me to understand what you re trying to do, instead of x & y) so for example...

Excel: Excel 2003 on XP-code fails for ADO connection, microsoft active x, jet oledb

microsoft active x, jet oledb, open table: You need to reference the ADODB library. To do so, in the Visual Basic window of your book, go to Tools- References, locate Microsoft Active X Data Objects 2.8 Library and activate the checkbox beside it. If you don t have that version you can select a...

Excel: Excel 2003 on XP-compile error with ADO connection, activex data objects, jet oledb

activex data objects, jet oledb, microsoft activex: This means that one of your variable declarations declares a varibale type that EXCEL does not recognize. Scroll thru your libraries and look for one that has a question mark next to it or something different like that. I forget what VBA shows when there...

Excel: Excel 2003, hh mm ss, target value

hh mm ss, target value, autofit: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Count 1 Then Exit Sub If Target.Value = Then Exit Sub If Target.Column = 1 Then Application.EnableEvents = False Target.Offset(0, 1).Value = Now Target.Offset(0, 1).NumberFormat...

Excel: Excel 2004, cell cursor, g4 powerbook

cell cursor, g4 powerbook, windows keyboard: Sounds like you have scroll locking on? On my Windows keyboard, it s a key much like NumLock or CapsLock... Otherwise, check under Tools-- Options (Is that preferences on the Mac?) and make sure that the Transition settings are all for Excel, and not...

Excel: Excel 2004 for Macintosh, array formula, adelaide australia

array formula, adelaide australia, four digits: Hi Graeme, I m a little confused about your specific set up but I can throw out general information about date math that may be of help to you. Excel has a number useful date-related functions. If you have a date in cell A1, you can read it s month...

Excel: Excel 2004 operating slooooooow!, ms windows operating system, excel 1997

ms windows operating system, excel 1997, mac powerbook: Michelle I don t have the 04 version of excel nor do I have a Mac so my experiences may not be the same as yours. In the past with my versions of excel (1997 and 2003) and with a PC, MS windows operating system either 98, ME, or XP, this type of problem...

Excel: Excel 2007, empty cells, creating a survey

empty cells, creating a survey, survey questionnaire: Nena - From your question, I am not sure if you want to delete the columns to the left, or just make them appear as a blank section with no gridlines. So I will answer both questions. To delete columns: Click on the column head, where the letter...

Excel: Excel 2007, color target, target address

color target, target address, macro editor: Hi - I realised that you only wanted to change the active cell, unfortunately, the only way I could think of to do it was with VBA -AND it only applies to the worksheet it is set on, so it s not ideal. The selectionchange event is in the macro editor - press...

Excel: Excel 2007 color palette, backgrounds and borders, theme colors

backgrounds and borders, theme colors, color palette: 1 - If you MUST use the old colors, you should use the format painter to copy them wherever. For any new colors, use the themes. 2 - Themes can t contain 40 colors, so that s not an option. 3 - to use the old colors you d need a file probably created in...

Excel: Excel 2007 data entry, format cells, menu format

format cells, menu format, menu tools: You can use the worksheet protection to achieve this. First, select the columns B and C, go to menu Format- Cells, and on the Protection tab clear the check box Locked. Then, go to menu Tools- Protection- Protect sheet, and accept the default by clicking...

Excel: Excel 2007 AND Excel 2003, regserver, click this icon

regserver, click this icon, excel exe: Find the .exe file for Excel 2003, right-click it and use Send To/Create Shortcut. Then right-click THIS file, select properties, put in /regserver at the end, something like this (your path may be a little different): C:Program FilesMicrosoft OfficeOFFICE11EXCEL.EXE...

Excel: Excel 2007- If Statements, bonus bonus, initial money

bonus bonus, initial money, bonus money: Ginger I don t think your telling me all the conditions. There is no need for an if statement in your description a1: 60 b1: 10 c1: =A1+B1 Let s say that if Sean earns more than 50 dollars, then he gets a bonus. If he earns 50 or less, then...

Excel: Excel 2007, xls file, pivot table

xls file, pivot table, vbe: That s why I said you really should do the work in the environment you ll be using it in. There is no ActiveWorkbook.PivotCaches.Create in Excel 2003. If you re-record the macro from xl2003 you d see ActiveWorkbook.PivotCaches.Add and there are probable other...

Excel: Excel, text import wizard, csv file

text import wizard, csv file, ogilvy: Ian, With the minimal information provided, I will assume this is a .CSV file. If so, change the extension to .txt, then open it in excel and use the text import wizard. Select Fixed width rather than delimited. This will allow you to make the decisions...

Excel: Excel, analysis toolpak, cell a1

analysis toolpak, cell a1, workday: From the main Excel menu, choose Tools, Add-Ins, make sure that Analysis ToolPak is checked, then click OK. Try this example. Select Col A and format it as dates. In cell A1, type in today s date. In cell A2, enter: =WORKDAY(A1,5) to return the date...

Excel: Excel, current sheet, unitil

current sheet, unitil, command button: If I understand the question the easiest way is to have the macro insert the formula, calculate the result and then set the value to the resultant value - record the macro to make it easiest to set up, but basically you are going to get something like ...

Excel: Excel, target value, vba programmer

target value, vba programmer, dc discharge: Private Sub Worksheet_Change(ByVal Target As Range) If target.count 1 then exit sub If Not Application.Intersect(Range( A1 ), _ Target) Is Nothing Then If Target.Value = CL Then YourMacroName End If end if End Sub...

Excel: Excel, sum function, apostrophe

sum function, apostrophe, cells: Hi Vickie, Yes, I believe that its purpose is to show text of what is entered in the cell. Another good use for it is if you have a list of numbers and you want to exclude one of them from the sum function, but you want to retain the number in the list...

Excel: Excel, b6, a6

b6, a6, f2: First, get a unique list of what s in A (Data/Filter/Advanced Filter, use only col A, click Unique values, click copy to another location). Let s assume that location is F1. Since F1 is likely a heading of some sort, put this in G2: =COUNTIF($A$1:$A$100,F2)...

Excel: Excel, cell reference, data areas

cell reference, data areas, pivot table: Easiest way to do it is via a pivot table (on sheet1, use the data menu, then pivot table and chart report - then follow the wizard - drop the row, column and data areas you want onto the chart and it will be created. To refresh, right click). Alternatively,...

Excel: Excel, cell a1, spreadsheets

cell a1, spreadsheets, spreadsheet: This is really strange, as the formula you are using should bring the value that is shown in the cell directly, and I cannot think on a better formula that the present one. If you want, you can send me a copy of the spreadsheet to m4zapic [at] yahoo [dot]...

Excel: Excel, indoor bowls club, format painter

indoor bowls club, format painter, format menu: I m chairman of Stevenage Leisure Centre Indoor Bowls Club! what you need to do is to use the FORMAT menu, then conditional formatting - this will allow you to use formulas, or just values to test against, and set various formats - should be reasonably clear...

Excel: Excel, mp 3 players, mp 3 music

mp 3 players, mp 3 music, type mp: Hi Sean Put your category data in columns elsewhere (like Sheet2): A B MP-3 Music CD Music DVD Film RC Car Toys ... and use the VLookup function. You type MP-3 into a cell in column K of sheet1 and a VLookup equation...

Excel: Excel, cmnt, visual basics

cmnt, visual basics, code space: Hi Carol First one is easy. Enter Design Mode (Select the toolbar icon with the ruler, set-square and pencil). Right-click on the control and select Properties. A pop-up window appears; select Font and set the desired size. Close the pop-up and deselect...

Excel: Excel, macros excel, xls file

macros excel, xls file, networked machine: The easiest way to share both of these is to give the people the spreadsheet that contains the chart and the macros. Excel stores user-defined custom chart types in XLUSRGAL.XLS on an individuals machine, but the chart is ALSO stored on the spreadsheet concerned....

Excel: Excel, countif, sumif

countif, sumif, repair time: Leo You can use the SUMIF and COUNTIF formulas to get you averages as follows. assuming the data you gave me is in rows 1 thru 5 to get the average of W repair time =SUMIF(C1:K5, w ,K1:K5)/COUNTIF(C1:C5, w ) To get the average of S repair time...

Excel: Excel, stock price, current value

stock price, current value, thanks in advance: Not clear how the data is refreshed, which would govern the nature of the reply - BASICALLY, there is no before update event, so if a macro can be used to refresh the data, it can move the existing data to another place - IF it is an automatic process, POSSIBLY...

Excel: Excel, customer account number, credit card numbers

customer account number, credit card numbers, vba macro: Hi, Dianne. I presume you have a list of names, credit card numbers, and account numbers. I also presume you want to enter, perhaps somewhere ELSE their credit card number and have it show their name and account number or something...so...are you looking to...

Excel: Excel, data validation, exact data

data validation, exact data, option button: OK, starts to get clear now. If you always select just ONE of these three options, you could use either: 1. An option button - rightclick the menu and select the Forms toolbar - Click the option button button and draw three of them on your worksheet...

Excel: Excel, task time, decimal representation

task time, decimal representation, time on task: It s not a problem, except that presumably a piece of work could feature in both day and night (eg started at 5pm, finished at 7pm). The calculation would therefore need to use the start/end times and check these against the times for day and night - I m...

Excel: Excel -, data validation, collumn

data validation, collumn, target: There is no click event for the worksheet, so the macro is never called - however, why not use Data Validation for the column which would remove the need for the macro? If you want to use a macro, then try the selectionchange event of the worksheet Private...

Excel: Excel, reasion, hello tom

reasion, hello tom, blank cell: I don t see an issue. Even more reason to follow that approach. once you enter the formula in C2, then select the cell and double click on the little square in the lower right corner of the cell outline and it will fill down the column. After you filter,...

Excel: Excel, data validation, reasion

data validation, reasion, emi: Here are two links that detail how to do what you want to do. They describe how to use Data Validation to prevent a duplicate entry from being typed in a range, which is in essence what you want, in a different way. I did not want to have to type/explain...

Excel: Excel, target value, reasion

target value, reasion, sheet tab: Right-click the sheet tab, select view code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then current column is B Application.EnableEvents = False Set exists = Range( A:A ).Find(Target.Value)...

Excel: Excel, reasion, unique records

reasion, unique records, emi: I can think of various ways of doing it - one method is to copy both sets of data into one column (probably on another sheet), then use the data, filter, advanced filter to filter on unique records only - copy this result back to the first column. Using...

Excel: Excel, relevent data, goal sheet

relevent data, goal sheet, f7 key: you ve given me a little conflicting information. The original question says ...on the next months goal sheet and the followup says ...another workbook for each month... So are they separate workSHEETS or separate workBOOKS? Big difference. Feel free...

Excel: Excel, s1 s2, string s1

s1 s2, string s1, sstr: You said import a text file, so this macro allows you to browse to and select the file AND open it on the current sheet starting AA12. (handles a file such as you show) Sub GetAndReadTextFile() Dim LineofText As String Dim rw As Long, bSpace As Boolean...

Excel: Excel, format cells, best bet

format cells, best bet, drop down list: Hi Tony, I think your best bet is to password protect the sheet, but if you have cells that you want to be edited, you can select those (highlight or choose individually) format cells/ protection and uncheck the locked selection prior to protecting the...

Excel: Excel, tab sheet, macro button

tab sheet, macro button, sheet tab: It is hard for me to suggest anything specific since you have only shown a few lines of code and none that would indicate how you added a sheet and named it Dec. So best I can assume is you now haf a sheet named Dec and you want to change the heading in C1...

Excel: Excel, digit number, excel 2000

digit number, excel 2000, glitch: Heather Excel s number precision is limited to 15 digits, which basically means that any digits over 15 will show as a number to a power ie 1.1111E+16 and drop the last numbers to 0. If you aren t using the numbers in a calculation change them to text,...

Excel: Excel, data2, data1

data2, data1, sucess: Hi Daniel, How are you? Please try the following 1st TOOLS - MACRO - VISUAL BASIC EDITOR 2nd insert-module 3rd copy the following Public Function AvR(A As Range) Dim data1 As Date, data2 As Date data1 = 01/01/2003 : data2 = 01/01/2004 c = A.Rows.Count...

Excel: Excel, cell a1, string place

cell a1, string place, cell b1: Dear Amanullah, There is another macro at the end of this email. You will need to copy the macro then open Excel and press Alt+F11 (or hover your mouse pointer over sheetname and right-click and select View Code )then select Insert from the menu then...

Excel: Excel, target value, target address

target value, target address, value application: Hi Bruce How are you? I know that this is not what you want, but maybe you can work out the solution from that Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim Keepvalue As Range Set Keepvalue = Range( A22 ) A = Keepvalue.Cells(1, 1)...

Excel: Excel, b4, free time

b4, free time, a1: Sorry I do not write code for people on this site. Since I do this in my free time then that is all I would be doing. Moreover, requests like this are always more involved than what they appear to be and always take longer to write. I am sure you can understand...

Excel: Excel, c cells, b cells

c cells, b cells, column c: Hi Daniel, How are you? Please try the following 1. TOOLS - MACRO - VISUAL BASIC EDITOR 2. INSERT - MODULE 3. COPY Sub inAbutnotB() Dim A As Range, B As Range, c As Range Set A = Range( a1:A20 ) Set B = Range( b1:B10 ) Set c = Range( c1:C20...

Excel: Excel, different colors, critical situation

different colors, critical situation, rng: Hi Aman, Go to the sheet where you want to type the names and press Alt+F11 Paste the following Macro into the blank right side of the Visual Basic Editor. Close Editor and type in Column A the names Michael , George , Ken . You can change the macro...

Excel: ############## in Excel, hi tim, cell a1

hi tim, cell a1, character limit: Hi Tim, I don t know what version Excel you are running. But in Excel 2000 and on. The character limit of a cell to display is 1024. The formula bar will hold 32767 characters. This all varies due to font, size, formatting, etc...To find out more about...

Excel: Excel, target address, blank window

target address, blank window, question the question: Hi Aman, I am sorry I wasn t clear. You can download an example workbook with this macro at. www.squish.com/cloverleaf/adda.xls Here are clearer instructions to use this macro. Copy macro at the end of this email. Go to workbook where you want to...

Excel: Excel, vlookup, time chris

vlookup, time chris, excel 97: If you have these Numbers & names in cells H1:I50, for example, then assuming you re going to enter the number in cell A2, put this formula in B2 to automatically fill with the name: =IF(A2= , ,VLOOKUP(A2,$H$1:$I$50,2,FALSE)) and fill down as far as necessary....

Excel: Excel, keyboard shortcut, shortcut menu

keyboard shortcut, shortcut menu, previous question: Hi, Brian, I see on that date a message I answered to a Janie, but I cannot find any previous message about that subject. However, I m happy to explain how to put a shortcut on the toolbar. 1) Point anywhere up on a toolbar, and right-click. A shortcut...

Excel: Excel, microsoft knowledge base article, microsoft knowledge base

microsoft knowledge base article, microsoft knowledge base, knowledge base article: I ve set out below a knowledge base article on this issue - the ONLY certain way to change the calculation is to have a macro run on the OPEN event of the workbook which changes the calculation mode (at least, that is my experience) (this is taken from...

Excel: Excel, target address, dear sir

target address, dear sir, good health: Hi Aman, Here is the corrected Macro, It will only work in Column A now. Also like I said if you enter a formula in a cell such as =20*12 it will return a result of 240 and add it to the column total. Let me know if you need something else. Private Sub...

Excel: Excel, double quotation marks, apple trees

double quotation marks, apple trees, column label: S I would start by saying that I think the Help files in Excel are great for this topic, so that s what I m using as a reference here. DSUM(database,field,criteria) Database is the range of cells that makes up the list or database. A database is...

Excel: Excel, column right, a3

column right, a3, explanations: Hi Mutthuvel, How are you? I think that the best thing you have to do is to look up these functions in trhe Excel help. The help explanations are better than those I could give to you. E.G. 1. Type HERE IN A9 2. Type =OFFSET(A7,2,1,1,1) in A3 you...

Excel: IF in Excel, 111111, a11

111111, a11, a10: Paul The rest is just math If 18 units equal a basket, and 9 baskets equal a stack, then 9 times 18 is how many units in a stack or 162 units. the 37.1111 we calculated is the number of stacks so we now need to calculate how many units .1111 of a...

Excel: Excel, numerical data entry, douglas m smith

numerical data entry, douglas m smith, minute segments: Hi John, This can be done but I would advise against using this convention. 7.1 and 7.10 look different but are the same numerically. Having them as text and them would make them different but it adds extra layers of complexity when ever you want to do...

Excel: Excel, change colors, thrue

change colors, thrue, office xp: Alma You need to use conditional formatting. Go to toolbar/format/conditional formatting. I think you will see from the conditional formatting window how to do it. If not let me know. You are limited to three different conditional formats in the...

Excel: Excel, ms excel macro, ms office document

ms excel macro, ms office document, test xls: I m not sure I understand the question, and I m not familiar with Image Writer which probably wont help, but an Excel macro CAN change the path or save a file to ANY path ( eg activeworkbook.SaveAs filename:= c: est.xls would save to the root of C...

Excel: Excel, douglas m smith, data validation

douglas m smith, data validation, brainbench: Hi Randy, I hate it when that happens! You want the Data Validation feature. Select the cell(s) to apply the list to and then select Validation from the Data menu. On the settings tab, select List from the first dropdown box and the enter (or point...

Excel: Excel, conditional formatting, cell b1

conditional formatting, cell b1, cell a1: Here s what I would do. Put =A1-1976 in cell b1. Then, use conditional formatting to make the font of b1 white if no value is in cell a1 and black otherwise. To set up conditional formatting, select the cell, click Format Conditional Formatting and follow...

Excel: Excel, quotation marks, explaination

quotation marks, explaination, madam: I think I understand now, Aman. You have values in A1:A3. You would like to sum those values in A4, and add on some words to the end of the sum. That s very easy. You need to use the & sign to concatenate (or paste together) a value with a piece of text....

Excel: Excel, paste details, excel sheet

paste details, excel sheet, pat pat: Pat, I can t say for sure, but I would guess that the answer would be not easily. I know it s possible to set up links to sites, and download data on a recurring basis, but I think there would be issues with having to provide a login and password through...

Excel: Excel, nam nguyen, target value

nam nguyen, target value, necessary range: You COULD convert them to hyperlinks (try recording a macro to do so & see what you get), or, you can do this: Right/click the sheet tab, select View code, enter this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If IsNumeric(Target.Value)...

Excel: Excel, cell b1, debug error

cell b1, debug error, mr ken: Hi Amanullah, Excel will not let you have an input in cell B1 and have a formula in cell B1. Because you are asking to either input the value of 2 in cell B1 or have cell B1 value change if cell C1 value changes. This is not logical for excel. I do not...

Excel: Excel, digits number, excel 2002

digits number, excel 2002, decimal places: =ROUNDUP(your current formula,1). From help: Rounds a number up, away from 0 (zero). Syntax ROUNDUP(number,num_digits) Number is any real number that you want rounded up. Num_digits is the number of digits to which you want to round number....

Excel: What is Excel?, squares rectangles, cheers

squares rectangles, cheers, homework: Hi Temitope, Excel is a powerful calculator that allow to compute complex calculations by resorting to built in mathematical, statistical, textual etc functions. It mimicks traditional sheet of paper, by dividing the working area into small squares/rectangles,...

Excel: Excel, madam, a3

madam, a3, a2: Here is the formula you put in A4: =SUM(A1:A3)& USD Unfortunately, you can t format only part of a formula s result. There isn t a way to bold the USD and leave the sum normal. If the cell contained the text 20USD you d be able to do so. But...

Excel: Excel, visual basic programming, vb excel

visual basic programming, vb excel, www tutorials: Dear Aman, I am doing fine. How are you? I know Visual Basic programming only at a beginner level. All my Excel knowledge is self-taught. I believe the books that I recieved the most help from was written by John Walkenbach. His book on Power programming...

Excel: Excel, excell 2000, copy paste

excell 2000, copy paste: Michelle I m not really sure what the question is. But let me try. Sounds like you have a total for the month that you don t want to change once you have entered the information for the month. One way would be to create a template that you use for...

Excel: Excel, douglas m smith, menu filter

douglas m smith, menu filter, autofilter: Hi Mike, I about to head out of town for the week so I won t have time to give as detailed of a solutions as I normally would but this should get you started. Here is a low tech solution. I would add a column for the number of items you want to order...

Excel: Excel, format cells, menu bar

format cells, menu bar, madam: Hello, Aman, You can format the cell to display its value with some text alongside it. Follow these steps: 1) Enter the number 45 in A1. 2) On the menu bar, go to Format Cells. 3) Make sure that you re on the Number tab, and choose the Category called...

Excel: Excel, cell a1, dear madam

cell a1, dear madam, format cells: Hello, Aman, Follow these steps closely: 1) Click in cell A1. 2) On the menu bar, go to Format/Cells. 3) In the Format Cells box, go to the Number tab. 4) Under Category, select Custom at the bottom. 5) In the Type field (it currently says...

Excel: Excel, windows 2000 professional, allexperts

windows 2000 professional, allexperts, word document: Kathy My understanding from your question is--- The worksheets/workbooks you are trying to open have not been protected so that you cannot open them, and when you turn your computer off and then back on you can access the files. Sounds like potentially...

Excel: Excel, ms office 2000, xlstart directory

ms office 2000, xlstart directory, dublin ireland: You d need a macro to do it as a shortcut. You ll like this macro: If you have a personal.xls then make it visible (window/unhide) and right-click the Excel LOGO near the file menu, select View Code and put this in - if you do NOT have a personal.xls then...

Excel: Excel, microsoft excel 97, sr 2

microsoft excel 97, sr 2, creating a new workbook: I can almost guarantee that EXCEL is not doing this on its own just to have fun. There is something else involved that either you are just not aware of or that you have not told me. Perhaps you are importing or copying and pasting data or filtering the...

Excel: Excel, format cells, amanullah

format cells, amanullah, sheet input: I just received notice that you did not receive my original reply so I m sending again. Select A2 and go to Format/Cells. On the Protection tab, turn on both Locked and Hidden. Click OK. Now go to Tools/Protection/Protect Sheet. Input a password, and...

Excel: Excel, ms office 2002, documents folder

ms office 2002, documents folder, double click: I have experienced exactly the same problem BUT cannot find it documented anywhere on the MS Site! My experience is that IF I start Excel, I can then open shortcuts or double click the file in Explorer with no problem but IF Excel is not running then the...

Excel: Excel, ms office 2002, regserver

ms office 2002, regserver, excel exe: 2 things you can try. with excel open, go into tools= Options= General tab and make sure ignore other applications is not checked. If it is checked, then uncheck it. The other is to close excel and then go to the windows start menu and choose RUN...

Excel: Excel, exact instructions, column c

exact instructions, column c, runyon: Hello, Seretha, Yes, it s very possible. Since you didn t give me much information about your spreadsheet, it s difficult for me to give you exact instructions, but I can give you some tips here to follow. Let s say that the vehicle age is in number...

Excel: Excel, excel spreadsheet, form letters

excel spreadsheet, form letters, packing slip: Yes, you can use VBA to bring in specific data, but probably easier is to use mail merge - sounds as though you are BASICALLY wanting to produce the form letters option (even though it isn t a letter) - just drop the fields you need in and set the filter options...

Excel: Excel, row headings, using ms office

row headings, using ms office, programming option: Hi, Jon, The only way to actually *select* cells that meet certain criteria is to program a macro to do so. But there s another non-programming option that may meet your needs: filtering your data. AutoFiltering is one of my favorite features in all...

Excel: Excel, cell d2, transcription business

cell d2, transcription business, michelle king: Hi Michelle, It s only complicated if you don t know how to do it. Fortunately, Excel is well equiped to handle this task. First, we ll need to build a table of your pricing information. Start it in column M2 to keep it away from your list of entries....

Excel: Excel, stock ticker symbols, quick excel

stock ticker symbols, quick excel, matt phillips: Yes - very possible A macro for JUST the situation you describe would be Sub ticker() For Each cell In Range( a1:a10 ) If cell.Value = bby Then cell.Offset(0, 1).Value = long term growth Next End Sub An alternative and better one is...

Excel: Excel, column spreadsheet, new computer system

column spreadsheet, new computer system, spreadsheet column: You could certainly have a formula, but unless I ve misunderstood the question I think you will find tha the Pivot table option on the Data Menu will do what you want a whole lot faster, as it can return the count for each item (or the sum of the quantities!...

Excel: Excel, column spreadsheet, new computer system

column spreadsheet, new computer system, target value: Right-click the sheet tab, select View Code, paste this in: Private Sub Worksheet_Change(ByVal Target As Range) Dim n As Integer On Error Resume Next If Target.Column 1 Then Exit Sub Application.EnableEvents = False n = 0 ...

Excel: Excel, address line, spreadsheet

address line, spreadsheet, comcast: Fran, What type of form is it? VBA form? Spreadsheet Form? Control Toolbox Form? My answer depends on what method you used to create the form. Perhaps the easiest way is for you to send me the file. If you wish to do that, you can send it to xeon{theone}flux@comcast.net....

Excel: Excel, excel spreadsheets, dbf file

excel spreadsheets, dbf file, blank rows: Not really enough information to provide a proper answer, but I m GUESSING that if you press Ctrl End you will get to row 65k ish - which may be the problem. Personally, I would live with the data and run a query on the DBF file to delete all completely blank...

Excel: Excel, cell a2, cell a1

cell a2, cell a1, georgette: Hi Georgette, Assume that you have a number in cell A1 and you want to divide it by a number in cell A2...in the cell that you want the answer to appear place this formula: =IF(A2= , ,A1/A2) It will show the result if there is a number in cell A2....

Excel: Excel, mirror image, thanks in advance

mirror image, thanks in advance, f1: either one at a time, or all at once then sort the columns by putting a dummy series at the top or bottom and sorting left-to-right by that row, or via a formula which does something like this: assuming your data is in A:F and you want the mirror image...

Excel: Excel, dataset, several ways

dataset, several ways, d3: Mike There are several ways to do this. The simplest may be this formula: =B3&C3&D3 for row 3 assuming the Sta is in column B, etc. Then just double click the lower right corner of the cell to autofill to the bottom of the congruous data set. You...

Excel: Excel, blank rows, payables

blank rows, payables, blank lines: Vee Since I don t know how you set up the worksheets to automatically get the data from the sales sheet, I can t really say how to fix what you have. What I would say is that you can use VBA code to allow more flexibility with where the data is placed...

Excel: Excel 7.0, excel 7, logging data

excel 7, logging data, excel 5: IF you are getting files from other people that you need to process, then an upgrade is probably sensible. IF however the program you have does the job you want, then stick with it - at the Insurance company I work for, we still have Excel 5 sheets in use...

Excel: Excel, category labels, excel microsoft

category labels, excel microsoft, well thanks: Alex, Without seeing your spreadsheet, I d recommend keeping all the items in one list and using an alternate descriptor in an adjacent column. For example, in my master list in column B, I have a list of items: Apple Pear Orange Gatorade Milk ...

Excel: Excel, format cells, savvy user

format cells, savvy user, excel worksheet: The EASIEST way to not show the math, etc is View/Formula Bar. But a savvy user can simply display it again. Another way is to select all cells, use Format/Cells, select Protection Tab, select Hidden. But then you d have to protect the worksheet and that MAY...

Excel: Excel, cd autoplay, excel sheet

cd autoplay, excel sheet, excel worksheet: An install wizard sounds like overkill for a spreadsheet, but you could probably make the CD autoplay (this is outside my area of expertise although I know it s possible). You could also get the Excel sheet to recognize that it is currently read-only when...

Excel: Excel, hh mm ss, timesheet calculator

hh mm ss, timesheet calculator, ss format: exactly what are you typing to enter 6 hours, 30 minutes? You should enter 6:30 - the colon is necessary. Entering this into a never-used cell will also format it as h:mm. The issue isn t necessarily how you format it, it s what it contains. I need to know...

Excel: Excel, format cells, dollar signs

format cells, dollar signs, plaes: Abby How do you format a cell to have dollar signs next to the numbers? Select the cells you want the formatting in and go to Format Cells and select Currency, this will format to the non accounting $ format. How do you add the contents of cells...

Excel: Excel, real choices, move selection

real choices, move selection, f5 key: If you want to stay in the same cell, you can hit ctrl/enter instead of enter, or, you can use tools/options/edit and deselect Move selection after enter To move to a specific cell, your real choices are up/down/left/right, as from above. But to go from...

Excel: Excel, vlookup function, functions in excel

vlookup function, functions in excel, cell contents: The EASIEST and QUICKEST way of handling this is to put the list on to a worksheet/workbook - it s only temporary, so can be deleted later. Then use a vlookup function to look up the values - any cases that match will show data, any that fail will show #n/a...

Excel: Excel, invoice number, myinvoice

invoice number, myinvoice: unique from what? You can always take all the values you have so far and add 1. If it s historical and you don t have all the others handy, you can keep the last invoice used in the registry (thru VBA): NextInvoice = GetSetting( MyInvoice , MyInvoice ,...

Excel: Excel, city of morro bay, umbrella agency

city of morro bay, umbrella agency, drive crash: diane Sorry I m slow getting back to you I had a hard drive crash on my computer. Anyway---You need to have the A column names appear in all of the rows so that you might do a sort based on column A and then on column B. Sorry to give you that news, but...

Excel: Excel, excel spreadsheet, lookat

excel spreadsheet, lookat, office location: One thing that can increase the size of a file is when people apply formatting to entire rows or columns - you can soon find out if this is the case by pressing Ctrl End to get to what Excel regards as the physical end of the workbook. IF it is this that...

Excel: Excel, list of names, xls

list of names, xls, sample book: Hi Steve, Sorry I have been busy and it took some time to get back to you. I have worked out a sheet that will sort out if the names appear in all three columns. Here is the formulas that I used. Column D =IF(COUNTIF($A$1:$A$100,B1) 0,B1, ) Column...

Excel: Excel, autofilter, excel microsoft

autofilter, excel microsoft, personal issue: Hi, Alex, Please excuse the delay. I had a personal issue to take care of. Are you familiar with AutoFilter? You can AutoFilter your master list, and see smaller lists from it at any moment. That way, you don t need to maintain any kinds of smaller lists....

Excel: Excel, run time error, history worksheet

run time error, history worksheet, stack space: 1. Probably the easiest way to clear the info is to manually select ALL the fields you want cleared EXCEPT any cells containing formulas, then give the resultant selection a name (Insert/Name/Define). Suppose you call this range ClearMe . The VBA is Sub...

Excel: Excel, cell b2, cell a2

cell b2, cell a2, cell c2: Tina Not knowing exactly how your document is set up I m gonna make some assumptions as follows: You have columns that list column A department number column B the number of Part time employees column C the number of full time employees So...

Excel: Excel, left mouse button, spell checker

left mouse button, spell checker, mouse pointer: Hi Shaun, You can insert a short Macro to handle this. Sub SpellCheckIt() Sheets( Sheet1 ).Unprotect password ActiveSheet.CheckSpelling Sheets( Sheet1 ).Protect password End Sub You will need to modify it. Change the word password to your...

Excel: Excel, word data, spread sheet

word data, spread sheet, b1: There are a variety of ways one might go about doing this, but I think CONCATENATE will be the simplest and most elegent solution. For example, A1 is blank, B1 is blank, C1 contains the word Data , D1 is blank, E1 is blank, and F1 is blank. Place the...

Excel: Excel, disc cleanup, internal hd

disc cleanup, internal hd, extraneous files: Jacques It could be one of many things, to little RAM ie 256 ram as opposed to 512 ram. Or you may want to run the cleanup in Start Programs Accessories System Tools Disc Cleanup to get rid of all the extraneous files that can appear on you pc,...

Excel: Excel, service pack updates, hardware conflict

service pack updates, hardware conflict, internal hd: I will admit upfront that this sounds like a hardware/software problem between office and the hard drive. As such, this is a little out of my area of expertiese - nevertheless, I ll ramble on with some of my various ideas... I did some searches on the...

Excel: Excel, disk compression utility, internal hd

disk compression utility, internal hd, chkdisk: Not sure, but if you re having the same problem with the other programs, it s likely you re running low -- 300MB is pretty small, these days. Try running Chkdisk or some disk compression utility, or browse your files & get rid of unnecessary data - or clear...

Excel: Excel, appearance tab, using windows 98

appearance tab, using windows 98, default format: ...Is there a way to link comments from Sheet 1 within a workbook to sheet 2?... To my knowledge, No To change the default format of a cell Comments: 1. Press +M (the key is located between the Ctrl and Alt keys) to minimize Excel and any other open...

Excel: Excel, outer border, column headers

outer border, column headers, ogilvy: Hello Willee You can hide the rows or columns you don t want viewed Go to the outer border of the sheet where the row or column headers are located and select the rows or columns you want to hide. Then do format= Row= Hide or Format= column= Hide ...

Excel: Excel, open office, aidan

open office, aidan, ms office: Difficult to answer, as it s not clear what would have caused Excel to want to be re-installed. The normal solution would be to do exactly that, but as you don t have the CD that s going to be hard. Alternatives are (1) use whatever backup process you use...

Excel: Excel, excel microsoft, cell a1

excel microsoft, cell a1, well thanks: Alex I don t know what your data looks like or what your criteria are for your smaller lists, but you can use an if then formula in the smaller lists and identify the criteria and link to the master list. The formula in the smaller list might look like...

Excel: Excel, cell addresses, blank row

cell addresses, blank row, n20: Not sure of the layout of your data, but if it s in A1:N20, for example, you should select A1:N21 (include the blank row at the bottom), and one click of the sum tool will do it. If this doesn t match your data, describe more clearly how it s laid out (include...

Excel: Excel, infinite series, aidan

infinite series, aidan, infinity: OK, that helps - though not sure what you need to do here - are you trying to run a calculation as many times as is necessary to get a particular value - if so the GOAL SEEK from the TOOLS menu may be what you need - otherwise, if you can clarify further I...

Excel: Excel, microsoft office 2000, triangular object

microsoft office 2000, triangular object, excel spreadsheet: I m not 100% sure, but I SUSPECT it s something like smart tags - if you click on the triangle it will PROBABLY give some clues as to what it is doing there! If it helps to mail a sample file (or perhaps a screen shot?) you can get me at aidan.heritage@virgin.net...

Excel: Excel, shets, staff location

shets, staff location, staff name: This is not a question per se because the key word here is system , which can involve quite a bit. You want me to develop/design a system for you for free or explain ALL that would need to be done if you do it yourself? All volunteers on this site answer...

Excel: Excel, windows 2000, dawn

windows 2000, dawn, cells: The easiest way in my opinion is to write two little macros that chanage the font color of the cell you do not want to print to white and then back to black after you print Sheets( Sheet1 ).Range( A1 ).Font.Color = vbWhite Sheets( Sheet1 ).Range( A1...

Excel: Excel, countif, sumif

countif, sumif, excel user: In cells A1:A3 put the number 1. In cells A4:A6 put the number 2. In A8 put 1; in A9 put 2. Then in cells B1:B6, put any numbers you like; these are the numbers you want to average. In cell B8 put the fomula: =SUMIF($A$1:$A$6,$A9,B$1:B$6)/COUNTIF($A$1:$A$6,$A9)...

Excel: Excel, track grades, homework assignments

track grades, homework assignments, t scores: If your spreadsheet is set up like this: column A: student name or ID columns B-P: scores for the 15 tests that count as 60% of the grade columns Q and R: scores for the tests that are 15% each columns S and T: scores for the 2 homework assignments that...

Excel: Excel 97, personal computer tutor, xls workbook

personal computer tutor, xls workbook, three keys: Hi Phillip, Ah Yes, I see. If you can get a file to your laptop that would be great. In Microsoft Word you can enter cents with the three keys Ctrl, forward slash and c. Ctrl+/+c then copy and paste to excel. You can also try a simple macro that...

Excel: Excel '97, sort function, excel 97

sort function, excel 97, swbell: I suspect I do not understand the question. If you have the following data in rows 1 thorugh 3 of columns A through C: A B C Green 1/1/06 Bob Blue 2/2/06 Bill Red 3/3/06 Brandy If I were to select B1 (1/1/06) and click the sort descending...

Excel: Excel 97, good answer, excel 97

good answer, excel 97, file extension: Joe Iam afraid I don t have a good answer for you. I have not had that happen and don t know what the .xlsm extension file type is. Of course creating a shortcut or using the old extension won t work if the file extension in the shortcut isn t the same...

Excel: Excel 97 Compatability Notification, microsoft excel 97, message boxes

microsoft excel 97, message boxes, type microsoft: The only fix I can find is to use Excel 97, create a copy of each worksheet in the workbook to a NEW workbook, then save that workbook - because it s done in Excel 97, you then have solved the problem. Otherwise, excel will recognize that a later version...

Excel: Excel 97 - Links, douglas m smith, manual option

douglas m smith, manual option, brainbench: Hi Lisa, Tracking down links like this are part science, part art. They can lurk just about anywhere. Try looking in your defined names. One (or more) of them could be referring to another workbook. If you still come up empty, look at charts or other...

Excel: Excel ('97) Macro Involving the Solver App, excel 97 macro, record language

excel 97 macro, record language, valueof: Though this may be what was exactly and initally recorded, which I doubt, then you have syntax errors. VBA can not interpret $E$12 , for example. It should be Range( $E$12 ) in every situation So, try changing, for example SetCell:= $E$12 to be ...

Excel: Excel 97 - Multiple if statements?, cell b1, cell a2

cell b1, cell a2, cell a1: I m not sure what you need to do for the comparision with B1, so I ve left that part of the formula as B1, but =IF(NOT(ISBLANK(A3)),B1,IF(NOT(ISBLANK(A2)),B1,IF(NOT(ISBLANK(A1)),B1, No Values ))) should do it for you if I ve understood correctl...

Excel: Excel 97 & Pivot Tables, table charts, pivot tables

table charts, pivot tables, pivot table: You can give it a auto-defining name, like Database, which refers to: +OFFSET($A$1,0,0,COUNTA($A:$A),6) which would grow as info is added. This would be from A1:Fx where x would be the last row. Once data is added, you can click the red exclamation mark...

Excel: Excel 97 rows & columns resizing themselves, financial worksheets, excel 97

financial worksheets, excel 97, courier new: Joe, unfortunately, I don t have a copy of 97 to check this with, and I can t remember this ever happening. I would be a little curious which font you are using. It used to be that Windows reported some fonts so that Excel wouldn t recognize how much space...

Excel: Excel 97 SR-2 - date format getting changed, excel xls, excel template

excel xls, excel template, date formats: Bob Well that takes the easy answer away. The only other option I can think of is that your windows may be set to a different date format than what you are using in excel. Excel takes from windows for its date formats. Make sure windows is set to that...

Excel: Excel 97, best bet, excel 97

best bet, excel 97, programmatically: Luis The easy way would be to use Access which would allow you to create one table and then queries for each store which would automatically include any new information you imported into the access database, however the next best bet would be some sort...

Excel: Excel, greek sigma, keith thomas

greek sigma, keith thomas, danville ca: KEITH Tell her to try this =SUM(A1:A5) This is usually on the toolbar using the greek sigma or sum sign Clic on that sign and then paint the cells that you want to sum then hit enter. Or of course you can type it. Hope this helps Richar...

Excel: Excel, blank cells, option works

blank cells, option works, menu choice: Use the data menu, then the Text to Columns option (works for Excel 97 onwards - you didn t give me a version, so hopefully the answer will work for you) - NOTE that you will need blank cells to the right of the data for this to work, and you should select...

Excel: Excel or Access, tables access, personal detail

tables access, personal detail, detail results: If the data can be represented in a single table, Excel can be a easier solution, but it depends on your familiarity with both programs. The size of the database is not going to be a issue, even if you have 100 times more students, Excel could be efficient...

Excel: Excel or Access inventory tracking, data validation, access inventory

data validation, access inventory, database type: You can certainly create a database type file in either application - probably easiest with Excel as you are simply recording the file name and it s location - I m assuming we are talking physical files here, rather than computer files. If the location is...

Excel: Excel: Add dates, format cells, leap year

format cells, leap year, tricky question: Hi Chante How are you? You have to convert 3.7 into days and then sum up to your date For example if you want to sum 3 years (there are no leap year in this interval) in A1 type the date in B1 type =3*365 in C1 type A1+B1 The problem is to convert...

Excel: Excel Add-ins, excel spread sheet, aidan

excel spread sheet, aidan, cells: I don t quite follow the question - certainly you can add formulas to your sheet that refer to other cells, which presumably would contain the data from the add-in. If you can clarify the question I may be able to help further - my email if it helps is aidan.heritage@virgin.ne...

Excel: Excel -- Adding Cells from different worksheets together, cell a1, time kevin

cell a1, time kevin, excel 2002: Kevin You need to use a linking formula that looks like this: =Sheet1!A1+Sheet2!A1 The formula is in worksheet three and says Go to worksheet 1 and add cell A1 from worksheet 1--- then go to worksheet 2 and add cell A1 from worksheet 2. You can...

Excel: Excel Adding Question, cell b2, column c

cell b2, column c, cell c2: Hi Terry, I just wanted one clarification on your question. Are the values in column C dependent on column B?... If both columns are independent of each other then your question can be answered in 2 steps. Step 1: Since you want the amount to increase...

Excel: Excel Addition Question, sum function, column c

sum function, column c, eldon: Hi Terry, To sum the values of column B use the sum() function and to round down the output use the rounddown() function. It should look something like this... =ROUNDDOWN(SUM(B1:B2),0) However, I need some clarification on what you mean by reduce...

Excel: Excel - Adjust cell size to contents, macro recorder, column width

macro recorder, column width, ogilvy: You can turn on the macro recorder and then select the entire row and do Format= Row= Autofit Turn off the macro recorder and you will have the code for executing an autofit. If autofit doesn t work as you want, then I am afraid VBA itself doesn t offer...

Excel: Excel Algebra, graph equations, algebra 1

graph equations, algebra 1, algebra problems: Perform Algebra 1 formulas? Like what? Excel is not a solution engine for Algebra problems. You re not going to find anything that will move a variable from one side of the equal sign to the other. Graphing equations, yes. There are some examples...

Excel: Excel - Alternate Units, metric conversion, simplest form

metric conversion, simplest form, a1: It is possible, using the & operator and repeating the formula that gives the result. In the simplest form, you can do something like this: =A1 & ( & A1*25.4 & ) Instead of the reference to A1, you will normally have the formula that yields the number...

Excel: Excel Amount in Word., len, job

len, job: This isn t available in Excel in the built in functions, so you will need to create your own function - I ve had to do this for other people, so here is a function that will do the job Function ft(n) n = Int(n) Dim convert$ convert$ = Trim$(Str(n))...

Excel: Excel Amount in Word., sorry jeff, excel sheet

sorry jeff, excel sheet, word one: Razzak, I do not know of a function that will make that happen for you. I know there is a function named BAHTTEXT that will convert a value to its word format, but in Thai Baht dollars. I don t know of any other function that is similar. Sorry Jef...

Excel: Excel API call, chris rae, hidden password

chris rae, hidden password, vba code: I believe these are one and the same. So, if you are using Mr. Erlandsen s code, it should still work. I don t have a disconnected machine to test with, so I can t verify it. However, I checked the SDK documentation and didn t see any other API function...

Excel: Excel API call, hidden password, logon id

hidden password, logon id, case statement: Bill, I m not sure if you really need to use an API. If the user logs in to the machine, does Excel change the active name of the user (it should)? If so, then you should be able to just check with Excel to see who the user is, like this: If Application.UserName...

Excel: Excel Array, google spreadsheets, range names

google spreadsheets, range names, google: I don t really follow (an example would help greatly -- feel free to send a sample wb to me at bobumlas@yahoo.com with the subject AllexpertsQ), but in general, to get more than one cell returned with more than one value, you must first select all the cells...

Excel: Excel: Array & Countif, array formula, array formulas

array formula, array formulas, sumproduct: Hi, Philip, There are two ways to accomplish this. #1: Array formula The array formula that you need looks like this: (=SUM(($F$2:$F$30= Unavailable )*($B$2:$B$30 =TIMEVALUE( 6:00 )))} #2: SUMPRODUCT By using SUMPRODUCT, you don t need an...

Excel: Excel Array Formulas, array formula, array formulas

array formula, array formulas, b100: Hi Betsy, Yes you can write an array formula in one sheet based on data in another sheet. Here is an example array formula. =SUM((Sheet1!A1:A100=A1)*(Sheet1!B1:B100=A2)) You can place this formula in Sheet2 and it will draw data from Sheet1 notice...

Excel: Excel Array Formulas, array formula, array formulas

array formula, array formulas, breakfast lunch: Are you sure you want an array formula? In the first part of your question you mentioned you wanted to copy the contents of B3, C3 and J3 to another sheet. That sounds more like an automated copy/paste macro in which case I have lots of examples of how...

Excel: Excel Array, array formula, cell a1

array formula, cell a1, position text: Robert Not sure if this is the best answer, but since the array formula won t work very well with FIND. or SEARCH, because they will produce the VALUE error message when the sarched for text isn t found, here is what I would do. First (copy the data...

Excel: Excel's Auto Filter Limitation, question column, client names

question column, client names, unique items: Hi Robert, Yes, you can trust the B filter. You could also test this by running a COUNTA formula on each of your three countries and then going back and doing the filter on USA, highlighting the column of USA and right-clicking down in the lower right...

Excel: Excel - Auto fill function, blank fields, blank field

blank fields, blank field, excel 5: Hi Jay! How are you today? Please try the following program in VBA 1. TOOLS - MACRO - VISUAL BASIC EDITOR 2. INSERT - MODULE 3. COPY Sub fillingGap() On Error Resume Next Dim A As Range Set A = Range( A1:A20 ) change the range to fit your need...

Excel: Excel Auto Numbering, excel template, pono

excel template, pono, newpo: THe parameters for GetSetting and SaveSetting are Appname,Section,Key,Default -- I used arbitrary names for the first 3 items -- you can use whatever you like. But whatever you use for the SaveSetting you should also use to retrieve it from the GetSetting....

Excel: Excel Auto sum...., format cells, row numbers

format cells, row numbers, sum function: Alex Sounds like a formatting issue--You can format the cells that are a problem for you by painting the cells that you want to format, then going to toolbar/format/cells/select number/number and clic on the 1,000 spacers box. This will put the spacers...

Excel: Excel Autocomplete, autocomplete feature, data validation

autocomplete feature, data validation, many thanks: Hi, Linda, As you can imagine, AutoComplete does not work with numbers. It is designed to work only with text. That s logical. All is not lost, however. I suggest you type the first couple of characters and then press Alt/ down arrow . A list of the...

Excel: Excel AutoFilter, office xp profesional, excel autofilter

office xp profesional, excel autofilter, ms office xp: Hi Amajit Basu! How are you? Yes,there is something wrong with Autofilter.It does not work with numbers but it works with strings. You can solve the problem using maths. 1. Type your numbers in A2:A10 2. in B2 compute the number of digits of A2...

Excel: Excel Autofill, excel autofill, data2

excel autofill, data2, cell a1: Bob Autofill is used to fill a series of dates, numbers, etc. where there is a logical progression of the series. In your example your series of data doesn t flow progresively. Sound like what you want to do is a simply copy/paste, which you can do...

Excel: Excel Autofilter default, excel autofilter, caption bar

excel autofilter, caption bar, filter criteria: Hello Veli-Antti Murrinen, Since the selections in the Autofilter require manual action, the only why I could think to do that would be to place a macro in the workbook that would reset the filters in the workbook_Open event. Private Sub Workbook_Open()...

Excel: Excel Autofilter, excel autofilter, pivot table

excel autofilter, pivot table, input box: I m not aware of any way of doing this that is automatically built into Excel - it would be possible to have a macro set the autofilter criteria and store this information in a given cell, which MAY be the way to go. You could record most of this macro, and...

Excel: Excel "Automatic text" check box, xy scatter chart, data labels

xy scatter chart, data labels, chart options: Hi Andy I hope you are well. I would do the following: 1. Type X, Y and label in C5:D5 2. Type x values in B6:B9 3. Type y values in C6:C9 4. type labels for x points in D6:D9 5. Plot the xy scatter chart considering the range B6:C9 6. Select D5:D9...

Excel: Excel: Automatically Search one sheet and populate entire row into another based on customer id, macros vba, cell a2

macros vba, cell a2, vba code: Here s a sample of code that you should be able to customize to do whatever you d like. This code assumes that on the summary sheet, the Customer ID is entered in cell A1. Then it assumes that on the address sheet, the Customer IDs begin in cell A2, and are...

Excel: Excel: Automatically Search one sheet and populate entire row into another based on customer id, macros vba, cell b2

macros vba, cell b2, vba code: You should be able to do this by using the vlookup formula in your summary for. for example, if your summary sheet contained the customer ID in cell B2 and you wanted the customers last name in cell C5, then in c5 you would put in the formula =if(B2= ...

Excel: Excel: How to Autopopulate aftter selection from drop-down menu, microsoft excel, drop down menu

microsoft excel, drop down menu, half hour: Hi, Rachel! I have the perfect example of a workbook like this as a tutorial. It s here: http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm It may take you a half-hour, but you ll understand EXACTLY what to do...

Excel: Excel AutoSave add-in, default settings, latest versions

default settings, latest versions: I m not aware of any way to change this, but thats partly because I m using the latest versions of office - from XP onwards it s NOT an add-in, but part of the standard build! I only have a limited number of PC s available, and have XP, 2003 and 2007 installed...

Excel: Excel Average Gridlines, axis values, continuous curve

axis values, continuous curve, shape objects: Hi Todd, If I understand you correctly, you would like to have Excel automatically mark the average value of a chart data series using a line (or lines if you have a continuous curve and want both the x- and y-axis values marked). There are several ways...

Excel: Excel Average Question, msoffice xp, bottom rows

msoffice xp, bottom rows, row numbers: Hi Randy, I m sorry I don t have enough time to spend on your interesting problem right now. I m going to be offline for a few days but I think I can give you some help that may get you to a solution. The OFFSET function appears to return a value but...

Excel: Excel - How to access Workbook Properties, douglas m smith, vba help

douglas m smith, vba help, informational type: Hi Joe, This information is only accessible through VBA. I ve included the VBA help text for BuiltinDocumentProperties which is how these properties are accessed. There is a short macro at the end that reads the available values and writes them to a worksheet....

Excel: Excel acting up, informational files, excel spreadsheet

informational files, excel spreadsheet, microsoft excel: Hi! Is this occurring in ONLY this workbook? Or in every workbook? If every workbook: http://www.officearticles.com/excel/troubleshooting_microsoft_excel.htm If only this workbook: http://www.officearticles.com/excel/troubleshoot_your_workbook_in_microsoft_excel.htm...

Excel: Excel adding 30 days to date, paste values, string functions

paste values, string functions, yyyy: You have got the right formula, the problem may be that the date is not a date, but a text. There is an easy way to check that, just copy the cell (select the cell, and go menu Edit- Copy), select a cell with no format, and paste values (menu Edit- Paste...

Excel: Excel adding question, left mouse button, cell b2

left mouse button, cell b2, cell c2: Hi Terry, If you type in cell B1 8.00 and in cell B2 8.05 and in cell C1 400 and in cell C2 402.5 then select all four cells B1:C2 and then drag the fill handle down as far as you want to continue the sequence. In case you have not used the fill handle...

Excel: Excel application that will generate the return of investment., risk profile, return of investment

risk profile, return of investment, excel application: Anna Send me your e-mail address (we don t get the address through allexperts) and I ll send you a spreadsheet that I think will answer your question. Once you have checked it out, you can alter it as necessary to look exactly like you want it to. My...

Excel: Excel auto fill colors, army aviation, aviation units

army aviation, aviation units, final risk: You are right, there is not enough information to give a detailed answer, but at least I will try to give some advice. First, linking the behavior of the spreadsheet to clicks on cells will force you to use macros. I hope that you are comfortable with them,...

Excel: Excel auto save of dde transferred data, steam flow, boiler room

steam flow, boiler room: I guess it would just about be possible - you would have to have a macro that copied the worksheets to a new workbook, then saved that workbook and closed it - it could be set to run on the change event of the original workbook - you can record most of this...

Excel: Excel autoassign ID#, address phone number, head of household

address phone number, head of household, vlookup function: yes, I would store the names in a seperate area of the workbook and assign the numbers here, then when you need to reference it use the VLOOKUP function to return the value you need (using the autofilter to filter out the blank records) - once done, you can...

Excel: Excel autofill, cell d5, target address

cell d5, target address, excel autofill: It is easier to make the autofill than detect it. I am not sure if this will work for you, the code uses the worksheet_selectionChange event to detect if the student has selected the cell D5, after that selected the range D5:D10 (that would be the autofill),...

Excel: Excel autofilter, excel autofilter, google searches

excel autofilter, google searches, excel view: That is a though one - As far as I can tell, there are no settings within Excel that let you control the size of that arrow. Even if you zoom in Excel (View- Zoom- 200%), the arrows remain the same size; however, the row numbers get bigger and the blue on...

Excel: Excel autofit question, times new roman font, autofit

times new roman font, autofit, size font: Hector, The following lines of code will expand every row and column in each worksheet of your workbook: Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets With Cells .Font.Name = Times New Roman .Font.Size = .Font.Size...

Excel: Excel automated summing, nike running shoes, hierarchy levels

nike running shoes, hierarchy levels, acceptable solution: Matt, I am sure there is a way to do it. Are there any limitations on using additional columns to do preliminary calculations or as you show with your formula for marking the hierarchy levels of the rows. Is a macro also an acceptable solution? ...

Excel: Excel - automatically linked cells, ms 2000, accounting firm

ms 2000, accounting firm, h7: From Sheet1/A3, if you simply press ctrl/[ (that s ctrl+left bracket), it will take you to Sheet8/H7. The double-click now enters the cell for editing, but you can reinstate the functionality of the double click taking you to Sheet8/H7 by Tools/Options/Edit,...

Excel: Excel automation?, interactive tool, best wishes

interactive tool, best wishes, methodology: Kevin, I m not sure that this can be done outside of using VBA code to make it happen. Using VBA code, you would need to use a userform with a listbox. The code would need to fill the list box, and then with If statements (maybe Case), the code would rebuild...

Excel: Excel Backup File, options window, backup file

options window, backup file, trying to find a way: You would have to do it in the Workbook_BeforeSave event. Something like this: (right-click the Excel LOGO near the file menu, enter this... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.EnableEvents = False...

Excel: Excel Books - From Beginners to Experts, learning excel, hi bob

learning excel, hi bob, expert level: I don t have any expertise on which books are the best books to take you from beginner to expert. I d only suggest you browse the books you see in the bookstores. Re 2003 vs 2007, I d say that most businesses will stay with excel 2003 for quite some time -...

Excel: Excel - Break workbook out into smaller workbooks, column headings, sheet names

column headings, sheet names, store numbers: As I recall, I tested this and it worked. I could be wrong as I answer a lot of question and don t recall specifically. In any event, if you want to send me a sample workbook with representative test data, and include the macro in the workbook, I can test...

Excel: Excel - Broken Spreadsheet, usb pen drive, spredsheet

usb pen drive, spredsheet, microsoft error: The solution often recommended is to download and install a free copy of OpenOffice at http://www.openoffice.org and try to open it with that. That software appears to be fairly robust in opening excel files. If you want to send me a copy, I can...

Excel: Excel background image, image bob, excel sheet

image bob, excel sheet, printed paper: I think your best choice would be to actually have an object in each sheet on a new workbook, print this empty sheet (aside from the objects), then using your data print AGAIN on top of this previous printout -- actually printing twice, as if you re printing...

Excel: Excel with bar code scanner, bar code scanner, upcs

bar code scanner, upcs, dollar value: I have no experience of using a bar scanner with Excel. If the scanned data is stored as numbers then simply start the input at say Row 5 and place SUM & SUBTOTAL formulas in the rows above. You could make this more efficient by creating a Named Range to...

Excel: Excel beginner tutorial, david horner, beginner tutorial

david horner, beginner tutorial, hey buddy: Hey Buddy, These tutorials should be a good start. If you have any specific questions, don t hesitate to ask! http://www.usd.edu/trio/tut/excel/ http://www.bcschools.net/staff/ExcelHelp.htm#Intro http://david.horner.faculty.noctrl.edu/Excel_tutorial/home.html...

Excel: Excel blinking cells, colorindex, cell interior

colorindex, cell interior, sheet tab: Change the worksheet calculate to something like this - I ve listed a random-ish set of cells to test & to blink: Private Sub Worksheet_Calculate() Dim TestCell As String, BlinkCell As String For i = 1 To 6 TestCell = Split( B10/D10/E3/E5/C2/D14...

Excel: Excel button VBa code, basicly, yahoo

basicly, yahoo: Need to know a few things. Are the new sheets named? That is, do you know if January s sheet is named January? Or is the last(right-most) sheet the last month s sheet which needs to be copied? What do you mean by the one column ? And where is the ending...

Excel: Excel button, excel sheet

excel sheet: You can add a button using the Forms Menu and by double clicking on the button you can then assign a macro. You can also use the Controls Toolbox. To use this control you must make sure that you are in Design View by clicking the Design View icon - the...

Excel: Excel: Calculating surplus, home budget, fund categories

home budget, fund categories, monthly budget: Dylan, Assuming that your year starts in January, you can get the multiplier by a simple =MONTH(TODAY()) multiply that by the category amount, and you have your budget to-date. Summing the spend to-date is simple, a straight SUM function, but by category,...

Excel: Excel Calculations, cell b1, cell a1

cell b1, cell a1, eligibility period: It is unclear how many rows have to be considered when looking for overlaps. Just the previous row or every row could be an overlap. Can a macro be used for this rather than formulas? If you want me to work on this, then send me a sample workbook...

Excel: Excel Calculations, cell b1, cell a1

cell b1, cell a1, eligibility period: I am not sure I completely understand. How many rows of data starting in 11 pertain to the dates you entered in A1 and B1? It looks like that the dates entered starting in row 11 all pertain to the dates entered in A1 and B1 but I was not sure. The more...

Excel: Excel Calculations, cell b1, cell a1

cell b1, cell a1, eligibility period: Hello Helen Thank you for a very clear and explicit summary of what you are trying to achieve...I wish they were all like yours :) The thing is Im not a formula man, as my profile says I work exclusively with VBA code. If you wish we can rework your...

Excel: Excel - Calender Control and Data Validation, excel tips tricks, data validation

excel tips tricks, data validation, drop down boxes: Q1: I d write code to restore the size of the calendar control in the Workbook_Open event. You can record the process & move it to the open event. Not sure why this is happening. Q2: I d have to see the code to see what s different. You might look at Selection.Resize(1,1)...

Excel: Excel Case Statement, run time error, blank cell

run time error, blank cell, case statement: Jerry, I would assume that it s a version conflict. At the least you need this part of that line: Selection.Find(What:= , After:=ActiveCell, SearchDirection:=xlNext).Select But you could go through and take out each parameter (between commas) and check...

Excel: Excel - Cell color based on data, background color change, conditional formatting

background color change, conditional formatting, background change: Brian you can do the background color change with conditional formatting. go to toolbar/format/conditional formatting/put in the criteria for the format, such as cell value is equal to A/then cick the format that you want by clicking on format bar, etc,...

Excel: Excel Cell color on conditions, value case, inputbox

value case, inputbox, format menu: LOL!! Always difficult to gauge the level of the of the answer when just reading words on a page - for reference, to get the macro to work press Alt F11 which will take you into the Visual Basic editor - the macro can be pasted as is onto the workbook panel...

Excel: Excel Cell Format Issue, double click, notepad

double click, notepad, yup: Scott: Yup, that happens sometimes when importing external data into Excel (or pasting from another program such as Notepad, etc). There is a quick way to get around the problem: 1) Find an empty cell and type the number 1 into it. 2) Copy that cell...

Excel: Excel / Cell Formatting, douglas m smith, dropdown boxes

douglas m smith, dropdown boxes, case colors: Hi Michael, Sorry about the delay. The bad weather took out my internet connection yesterday. Excel can handle this through Conditional Formatting although there is a limitation of three conditions (in your case colors) that can be used. I wish it...

Excel: Excel - Cell formatting not working for one tab of a excel file, one tabs, format cells

one tabs, format cells, indent: SACHIN Sachin Apparently your cells are formatted as left indent. To change this paint the cells that you want to reformat and go to toolbar/format/cells/alignment/right indent and clic okay. This will reformat the cells to be right indented, or moved...