stephen bullen, dde links, circular references: Mark, Excel doesn t support that type of formula unless you want to create intentional circular references. You can get a sample file from Stephen Bullen s site that implements circular cell references to track when DDE links update. This is very similar...

Excel: date comparison, ww 1, ldate

ww 1, ldate, destination field: I m guessing from your code that what you want is possibly Option Explicit Sub Test() Dim rLastCell As Long Dim lMyInt As Long Dim lDate As Long rLastCell = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row For lMyInt = 2 To rLastCell...

Excel: date and day, dd, cells

dd, cells, yyyy: I m not sure WHY you want this, as if you have the column formatted as dddd dd/mm/yyyy (or similar) you will see the day of the week and the date in ONE cell. If you need to do it, you are going to EITHER have to have a formula in all the cells waiting for...

Excel: date fill down in column A, format cells, mouse down

format cells, mouse down, ogilvy: Put Jan 1, 2007 (no period) in A1 Jan 2, 2007 (no period in A2 Format the cells to appear as you wish the date to appear (format= Cells= Number format, select Date). select A1:A2 and you should get an outline around the cells. In the lower...

Excel: date format changes, format cells, macro code

format cells, macro code, time jeff: Nurul, I wouldn t say it s worth the time, but here s how to do it anyway. With one of the workbooks open, go to Tools/Macro/Record New Macro, and choose to save the macro in the Personal Macro Workbook, and then perform the steps noted with the first go...

Excel: date format, click date, format options

click date, format options, mdy: Hi Irma, Time for a quick lesson in how Excel handles dates. The number 70105 is just that, a number (at this point). Excel calculates dates starting on January 1, 1900. Type 1 in a cell and format it as a date and you get 1/1/1900. Today s date number...

Excel: date formulas in excel, douglas m smith, cell b1

douglas m smith, cell b1, cell b2: Hi Bob, You can do this by comparing the cells. The formula for cell B2 would be: =IF(A2=$B$1, Today , ) However, you need to do one other thing, change =NOW() to =TODAY() in cell B1. NOW() returns a time of day component (0.5 is noon) but TODAY()...

Excel: more date question, maturity date, quarterly payment

maturity date, quarterly payment, cell references: =DATE(YEAR(Matdate),MONTH(StartDate)+3,DAY(MatDate)) Excel will handle the year needing to be the next one if the month winds up being 12. Substitute the cell references for where I have Matdate & StartDate, like =DATE(YEAR(A2),MONTH(A1)+3,DAY(A2)...

Excel: date range, ogilvy, a3

ogilvy, a3, a1: If cdate(TextBoxADate.Value) Sheets(2).Range( a1 ).Value and cdate(TextBoxADate.Value) Sheets(2).Range( a2 ).Value Then TextBoxAPeriod = 1 End if If cdate(TextBoxADate.Value) Sheets(2).Range( a3 ).Value and cdate(TextBoxADate.Value) Sheets(2).Range(...

Excel: date subtraction, negative numbers, decimal places

negative numbers, decimal places, ogilvy: Also, excel wouldn t see the numbers you show as being dates - it would see them as numbers. Perhaps you could use =(DATE(LEFT(A1,4)*1,MID(A1,5,2)*1,RIGHT(A1,2)*1)-DATE(LEFT(B1,4)*1,MID(B1,5,2)*1,RIGHT(B1,2)*1)) assumes A1: 20011130 B1: 20010816...

Excel: date & time calculation, time cells, service level agreements

time cells, service level agreements, time calculation: A long time ago I had to do something similar, where I also had to take account of service level agreements which meant that the start time and end time had to be taken into account (and adjusted if outside agreed service times) - that took me about 3 weeks...

Excel: date, cell a2, dear sirs

cell a2, dear sirs, myday: Haytham, I am unaware of a standard Excel function that will do this. However, you can make a custom function that can make the calculation. Here is the solution I came up with. To use it, copy the code below. Next start Excel and open the spreadsheet...

Excel: datedif calculations, use tools, david c

use tools, david c, longevity: Your AND function makes no sense -- The AND part is returning =AND( ) which is #VALUE! Once THAT s fixed, the right side of the formula (after the * also is , and you re multiplying something by which is also #VALUE!. It might help if you select...

Excel: datedif, c8, b8

c8, b8, david c: In this case, I wouldn t use the IF or AND functions, but logical comparisons and the MIN function. Something like: =MIN(DATEDIF(B8,C8, Y )*100,3000)*(DATEDIF(B8,C8, Y ) =3) In any case, if you want to use your original formula, I would remove the AND part,...

Excel: dates !!! :-), cell a2, cell a1

cell a2, cell a1, unit of time: Yes, Paul, there s a function in Excel that almost no one knows about called DATEDIF. Don t know why it s been kept so hidden over the years, but for some reason, only Excel 2000 documented it. The syntax is as follows: =DATEDIF(earlierdate,laterdate,...

Excel: dates..., validation field, data validation

validation field, data validation, sheet tab: Hello Chris. Using an event triggered by the entry in column 1 (column A) should meet your needs if users don t disable macros. Right click on the sheet tab and select view code. Then paste in code like this: Private Sub Worksheet_Change(ByVal Target...

Excel: dates..., validation field, data validation

validation field, data validation, value date: Chris, Best method I know is an event handler for the first column. The code needs to be placed in the specific project for the worksheet involved, not in a module. Here s the code: Private Sub Worksheet_Change(ByVal Target As Range) Set VRange = Range(...

Excel: all dates between two dates, text comparison, yyyy

text comparison, yyyy, step 3: VBA ALWAYS uses US date format. So datum is TEXT, not a date, whereas Worksheets(naziv).Cells(i,x).value is a DATE, not text, so the comparison is not valid. To make a valid text comparison, use datum=Format(datum, yyyymmdd ) then test: IF Format(Worksheets(naziv).Cells(i,x).Value,...

Excel: dates in excel, dd mm yy, format cells

dd mm yy, format cells, excel workbook: I can t really answer properly because I don t have the workbook to see. Presumably you are changing the Format using the Format Cells menu. Some suggestions: Are the dates imported from another Excel workbook or different program, this might affect the dates?...

Excel: dates, douglas m smith, conditional formatting

douglas m smith, conditional formatting, hi doug: Hi Alan, I think the problem lies in how you are entering the formula rather than your logic. Using the Formula is option involves entering an equation that makes a comparison, like this: =A1 DATE(2004,12,31) For this condition, you could use the...

Excel: dde share use, detail manner, excel sheet

detail manner, excel sheet, office microsoft: rao The easiest option is to use a link, this will update when the original is updated if both are open. Have a look at these links http://einstein.cs.uri.edu/tutorials/csc101/pc/excel97/link.html http://office.microsoft.com/en-us/excel/HA010548121033.asp...

Excel: deactivate the unhide Option using macros, worksheet menu, vba code

worksheet menu, vba code, format option: If you just want to make sure that people do not unhide your worksheets, there are two easier ways: 1. Protect the workbook structure (Under Tools - Protection) or 2. In your VBA code Worksheets( yourname ).Visible = xlVeryHidden This will basically...

Excel: decimal point appears as a 'square', format cells, unprintable character

format cells, unprintable character, linefeed character: Hello Hugh, I haven t encountered this one, but strongly suspect it is the result of using a font in which the decimal point is not defined. I believe the default font in Excel is Arial 10 pt, but if you have it set to something else (or it is the formatted...

Excel: deduce formula, seligmann, problem thanks

seligmann, problem thanks, a1: I don t understand -- are you looking for something that will make a formula to relate A2 & A1? If so, there would be an infinite # of formulas, like =A1+A2+20 or =A1*A2+21 or =A1*17+2*A2-1, etc. If not, I don t get what you ARE looking for... Please elaborate...

Excel: default dates in excel, format cells, change dates

format cells, change dates, click date: Saw, 1) Highlight the cells you want to enter/change dates 2) Right click on one of those cells 3) Click format cells 4) Click Date 5) Click on the format you want to use, or make your own =) Hope that helps. If you have any other question just let...

Excel: default format, xlstart directory, startup directory

xlstart directory, startup directory, template book: Did you change it by changing the Style for the Normal style so it is arial 10? (and there is only one worksheet in Book.xlt) I am assuming there was an existing Book.xlt in the xlstart directory. If so, perhaps you can send it to me at twogilvy@msn.com...

Excel: defaults for Excel, gridlines, default location

gridlines, default location, default configuration: I would do this by using templates. Do all the changes that you need in a new file, save it as a template (select Template (*.xlt) in the save as dropdown, the default location will change to the templates folder), and then you will have the option to open...

Excel: How to delete certain rows., excel spread sheet, xyz

excel spread sheet, xyz, abc: Hey Rick, It sounds like you would need a fairly lengthy macro to solve your problem. You should use an IF statement in your macro to first look to see if the P/N is the same in column 1 and then see if the dates match in the date column. Delete all...

Excel: delete columns in excel, filter auto, ascending order

filter auto, ascending order, cursor: OK - different question then! EASIEST way is to put the cursor in the top left of the data, press ctrl shift end, then do data sort, and sort by any column in ascending order - this will have the affect of putting all the blanks together, which should make...

Excel: delete columns in excel, blank records, column header

blank records, column header, arrows: Ramya Click on the Column Header (a or b or c etc) and then go to Edit and click on Delete, if you don t see delete (don t click on Delete Sheet) then go to the bottom of the menu and click on the two down facing arrows and delete will appear. Richar...

Excel: How do I delete duplicate listings on my speadsheet, douglas m smith, speadsheet

douglas m smith, speadsheet, header cell: Hi Rene, You can use the Advanced Data Filter to do this. First, select a header cell of your list and then select Filter from the Data menu, and then Advanced Filter from the submenu. The cell range of the list should appear in the List Range box....

Excel: delete first empty column, douglas m smith, array array

douglas m smith, array array, empty column: Hi Hector, OpenText has an option to take of this in the import process. It happens in the second number of the FieldInfo arrays. Change the first array settings from Array(1,1) to Array(1,9) and the first column will be excluded automatically. No extra...

Excel: delete a record, ford taurus, names thanks

ford taurus, names thanks, mechanic: Well, still I can not see what you see so I am still driving blind and since you are still giving me just generic info then it is real tough to tell you how to delete the data. That s like telling a mechanic over the phone that I have a car that won t run...

Excel: delete record, vba macro, toobar

vba macro, toobar, inputbox: Hi Gabe How are you, agan? Please do the following 1. View - toolbars 2. select Forms 3. Draw a button (Choose button in the forms toobar and draw a button in the sheet) 4.Choose the macro deleteRecord() from the list Keep excelling Adelaid...

Excel: How to delete a row, windows xp sp2, cell address

windows xp sp2, cell address, msgbox: hI bOB hOW ARE YOU? You may try to apply the following Sub deleterange4above() Rem Dim A As Range Set R = Selection MsgBox R.Address For i = -4 To 1 A = R.Offset(i, 0).Row delete the row or mark it deletion Next i Cheers Adelaide End Sub...

Excel: deleting alternate rows, run time error, run time error 13

run time error, run time error 13, type mismatch: Excel appears to be looking for a temporary file (at least, that is what I take the name to be) BUT I cannot see WHY it would want to do that - I ve searched the knowledge base and cannot see anything to explain it either, so I m a bit stumped I m afraid -...

Excel: deleting blank rows from column, check blanks, blank rows

check blanks, blank rows, false application: Hi Tim, Here are three ways to do delete empty rows. 1) Select Column A. Press F5, click Special button check Blanks click OK Press Ctrl+9 to hide or to delete rows permanently press Ctrl - then select entire row...

Excel: deleting cells, companyname, b2

companyname, b2, cheers: Hi Theresa How are you? How s that Private Sub Workbook_Open() Dim R As Range, companyname As String Set R = Sheets(3).Range( B2 ) companyname = R.Cells(1, 1).Value If companyname = Then MsgBox ( There is no company ) Else MsgBox companyname...

Excel: deleting columns, debug error, lookat

debug error, lookat, endless loop: Alan, I don t get an error - but I get an endless loop. When I wrote the original, you were deleting the target, so I just told it to stop when it couldn t find anymore. That is not the case with your current situation. So you have to use a different stopping...

Excel: deleting dashes, hyphens, zeros

hyphens, zeros, dashes: as long as they are not leading zeros and the hyphens are physically present in the cells (not produced with formatting) select the cells and using the menu do Edit= Replace Replace what: - Replace with: leave blank click Replace all -- ...

Excel: deleting duplicate rows, cell reference, aidan

cell reference, aidan, yyyy: I think the quickest way to do this is to create two new columns. In the first enter =a1 & _ & text(b1, mm-dd-yyyy ) This will give you a composite of the two values in A and B - obviously (hopefully!) in my example I ve put the formula in row 1....

Excel: deleting empty cells in btw data, stock market data, empty cells

stock market data, empty cells, blank rows: Ibrahim, you could do one of the following things- (1) Sort the data so that blank rows come at the end. (delete it if necessary) (2) If you do not want to disturb the order, Insert a column at left. Enter a formula in A1 - =IF(B1= , BLANK , ) and drag...

Excel: deleting extra text, blank folder, myfolder

blank folder, myfolder, follow ups: IF all the URLS are the same then you don t need the url in a cell - lets say the photo name is in Q1 - the formula would be = http://www.photographpros.com/mom/ & Q1 IF they aren t the same, then put the URLs in column R - and again the formula would...

Excel: deleting rows in protected worksheet, keystroke sequence, open application

keystroke sequence, open application, vba: Hi Chris, Yes, the reason that you can t delete a row on a protected sheet with a locked column is that the row intersects the locked column and would therefore delete a cell in that column if the row were deleted, so this is not allowed. Probably the...

Excel: deleting rows, digit search, cell a1

digit search, cell a1, wasting my time: No, I am not bitter at all. Put yourself in my place. HOW am I supposed to know what you want when you don t ask? If I assume and then assume wrong then there is another set of emails that would not have been necessary had you, or anyone else, asked me...

Excel: deleting unwanted rows/entries, seperate sheet, sheet1

seperate sheet, sheet1, warm regards: Hillar, Please provide following information to proceed- Will you be maintaining list of 18 WS s you are interested in, somewhere in workbook? In a seperate sheet or something? If you maintain data in Sheet1 and list of 18 WS s in Sheet2 (A2 to A19) I can...

Excel: designating ranges in a macro, contiguous data, excel 2000

contiguous data, excel 2000, a10: The range needs to be dtermined dynamically and since the the range you want thr macro to work with will always be different in size as well its starting point, then no single command will work. Methods used to commonly adjust or determine range sizes are...

Excel: How to determine the last row, richard nash, relative button

richard nash, relative button, cursor: Tc This macro goes to the last active cell and then down 1 cell and home Sub lastrow() lastrow Macro Macro recorded 7/3/2004 by Richard Nash ActiveCell.SpecialCells(xlLastCell).Select ActiveCell.Offset(1, -2).Range( A1 ).Select...

Excel: How to determine the last row, macros

macros: There are many ways. Here is one: Dim LastRow as Integer LastRow = Range( A65532 ).End (xlUp).Row will find the last row in column A. Your macro will have to edited to incorporate this. Recorded macros are always a good place to start but rarely...

Excel: How to determine the last row, d400, integer number

d400, integer number, column number: Hi tcvb22, Yes, there is an easy way in VBA to find the last row. For example: Dim LastRow As Long LastRow = [A65536].End(xlUp).Row finds the row number of the last row containing data in column A on the active worksheet. Note that I declared...

Excel: How to determine the last row, cell address, a1

cell address, a1, relative referencing: tcvb22, If you would have included the recorded macro, I might have been able to provide a more specific answer, but yes in general it is easy to determine the last row in a range without hard coding in the cell address. Let s say your range of data was...

Excel: How to determine the last row, worksheet cells, cell bottom

worksheet cells, cell bottom, bottom right: yes there is - or indeed, are - this line selects the last worked on cell (bottom right of the worksheet) Cells.SpecialCells(xlLastCell).Activate OR you could use an OFFSET with a COUNT function to move away from a given cell Hope that this helps,...

Excel: dialog boxes, dialog boxes, quetion

dialog boxes, quetion, command button: Hi Jon How are you? The best way I can think of uses VBA programs 1. Insert a Command Button in sheet1 View - Toolbars Tick Forms Draw the button Write the question on it 2. Right click this button Assign macro to Button1_click 3. Tools - Visual...

Excel: different in behavior between protect and not sheet, keypress event, excel sheet

keypress event, excel sheet, textboxes: Jerry Yes I believe that the result you are getting is because of the locked cells, and Unfortunatley I don t know a way around it. I haven t tried but maybe you could write a macro that would unselect the locked cells, allow the move to the correct cell,...

Excel: two different books, 6 cells, single quote

6 cells, single quote, ogilvy: Hello Kevin, Yes. Open both workbooks. Go to Home.xls in the cell to hold the results of A1, put in =[Work.xls]Sheet1!A1 then drag fill this down for a total of 6 cells. Now save Home.xls. Close Work.xls and you will the the formula automatically...

Excel: different color for genders, format font, menu format

format font, menu format, c10: Say for example that the your data is in range A5:C10, and the genders are in col A. Select the range A5:C10. Then from main menu: Format, Conditional Format, Formula Is:, =$a5= F , Format, Font, Color, [Red], OK, Add, Formula Is:, =$a5= M , Format, Font,...

Excel: different printers, line capability, data input

line capability, data input, excel 2000: Eric Sorry I don t know of a code that will control the printers and limit or control the number of lines that they can/will print. However, I am impressed with your code, and if you don t mind I believe I can find a use for it in the future. Sorry...

Excel: Having difficulty in using "If" Command., c25, p5

c25, p5, p3: I believe I will be able to help you with this, but the question is not quite clear to me yet. Your formula appears to work correctly in it s existing place, and when it is referring to cell C23. What cell is the formula in, to start with? When you...

Excel: digits to class's, q40, ab50

q40, ab50, ogilvy: Joseph, I added on additional condition, but with the numbers you present, it worked fine for me as you posted it. I did not array enter as you seem to indicate with {} as it does not need to be array entered. You are bumping up against the limit for embedded...

Excel: dim and strings help!, sheet names, byi

sheet names, byi, combobox: Not sure I totally follow, but this is wrong: Dim sh As String Set sh = ComboBox1.Value ---- You can t use the Set statement for string variables. Set is reserved for objects. Simply use: Dim sh As String sh = ComboBox1.Value That may solve the rest...

Excel: How to disable and Enable the CTRL+ALT+DEL using Excel, ctrl alt del, time sheet

ctrl alt del, time sheet, ganesh: S.GANESH KUMAR I am sorry, I do not know how to accomplish that task. I can speculate that you will need to use the OnKey event, together with some command to monitor further selections. My guess is that you would need to set the code so that pressing...

Excel: disabling email addresses in Excel, insert hyperlink, proper terminology

insert hyperlink, proper terminology, new email: No. You will have to select the cells and click Insert, Hyperlink and then remove Hyperlink. To select the cells that have already have emails in them WITHOUT creating an email (in addition to the way I describe earlier) then select a different cell....

Excel: disabling print, microsot, excell 2003

microsot, excell 2003, related event: Should I just say yes and leave it at that or assume that you also wanted to know HOW to do this as well? Try this. Be sure to put the code in the workbook object itself and not in a module or it won t work. If you are unfamilair with workbook related...

Excel: disabling the printscreen button, jp jp, registry hack

jp jp, registry hack, printscreen button: JP, I don t have a clue - but making up your own argument to onkey doesn t seem to have a very high probability of success. Even if you disabled it, what about the the built in camera button, or holding down Shift and going to the edit menu, or using a 3rd...

Excel: disadvantages, homework questions, decimal points

homework questions, decimal points, google: Rosemary I dont usually do homework questions, in this case though you haven t given enough information, disadvantages in what regard?. Excel doesn t do 15 decimal points well, that is a limit imposed because of binary code. Other disadvantages would...

Excel: disallow openning of new excel, worksheet menu bar, file tab

worksheet menu bar, file tab, excel sheet: Right-click on menu bar, choose Customize . Then left-click on File, right-click on Open, and choose delete. Then close the customize box. To bring back the open file, right-click on menu bar, choose Customize , right-click on File and choose Reset. To...

Excel: disallowing users to save changes, blank cells, spreadsheet

blank cells, spreadsheet, sorry for the delay: Hey Tom, Sorry for the delay in getting back to you. What you could do is just allow the user to open the template as a read-only spreadsheet. This will still allow them to make changes, but forces them to save the file under a different name if they...

Excel: disappearing buttons arrrggh!, control toolbox, control buttons

control toolbox, control buttons, strange behavior: I can only suggest you use Forms buttons and stay away from Control Toolbox buttons (ActiveX) as I ve seen strange behavior with these. I assume, therefore, you ARE using the ActiveX ones? And yes, I ve never seen this issue! Other solution: don t connect...

Excel: disappearing buttons in Excel 2003, invisible buttons, mouse changes

invisible buttons, mouse changes, active x: Well, I don t assume anything so it may have been obvious to you but not to me. A number of buttons can mean one of two ways buttons have been placed on the sheet. And since you did not say HOW, or which one of the two ways, you put the buttons on the...

Excel: disappearing macros, spreadsheets, excel 2000

spreadsheets, excel 2000, xls: The only way you d find the macro in NEW spreadsheets is if you ve created a book.xlt template which contains the macro. To get the macro into the current spreacsheet, you can copy/paste it from an older one, and recreate your book.xlt & put the macro there...

Excel: how to disconnect F1 from help, go tools, xls file

go tools, xls file, excel help: One has to create it if it does not exist. Since one said that one tried to write a macro to do this then I assumed one knew where to look for the Personal.xls in the VBE and one was also familiar with the VBE and macros. The Personal.xls will be a project...

Excel: how to display 0001234 in excel cell, 7 digits, format cells

7 digits, format cells, 0000000: There sure is! 1) Select the cell in question. 2) Click on Format- Cells 3) Choose Custom 4) Type 0000000 into the box (where it sometimes says General) and click ok This will add leading zeroes to any number to ensure the value is always 7 digits....

Excel: How to display a formula value in a label, sheet1, match

sheet1, match: Assuming your formula is in A1 on sheet1, then use the Initialize or Activate event of your userform and put this code there Me.label1 = Sheets( Sheet1 ).Range( A1 ) You need to use the WorkBook_Open event to show your userform so that it displays upon...

Excel: display multiple answers from a search bar, search line, stock numbers

search line, stock numbers, description column: Miro: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/formiro.xls Note: These formulas are very complex and not easily manipulated; unfortunately, there isn t a simple way to build a list as you have requested. ...

Excel: How to display Name on a userform, excel sheet, form1

excel sheet, form1, gyula: Hi, =IF(COUNT(B1:B6)=0, ,INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0))) will return the first name with the lowest value (you will have to figure out how to deal with the ties. In the ThisWorkBook object of the VBA editor, copy the code below: Private...

Excel: displaying two digits when applying percentage formula, douglas m smith, percentage formula

douglas m smith, percentage formula, result format: Hi Dee, I m a little confused. Was your formula supposed to be: =(680*100)/850 If so, here s the problem. You are using a calculator technique to display a percentage value as a number between 0 and 100. (I use it a lot myself.) Easier to read,...

Excel: distribute data, vba, sherry

vba, sherry, cells: Sub Distribute() Dim n As Integer, m As Integer, item On Error Resume Next For Each item In Sheets( Sheet1 ).Range( A2:A & Range( A60000 ).End(xlUp).Row) n = 0 n = Sheets( Sheet2 ).Columns(1).Find(item.Value).Row m = 0 ...

Excel: divide The workers hours to the job.., douglas m smith, head ache

douglas m smith, head ache, cell reference: Hi Robert, It sounds like SUMIF might solve your problem. The syntax is: =SUMIF(range of letters, letter to look for, range containing numbers to add up if matching letter is found) Example: If your code letter are in cells A1:A20 and the hours in...

Excel: dividing a database, paste values, disertation

paste values, disertation, false values: If that is the case, I think that my first proposal could do the trick. Essentially, it would work like this: Step 1, get some random data beside your set (the numbers are the result of the RAND() function) 1 0 0.317917725 2 1 0.517753665 ...

Excel: document to floppy, cam open, word issue

cam open, word issue, page document: A file s read-only attribute does not just get set automatically. The user in most cases has to set this attribute to be read-only unless the file was burned to a CD and then put back on a hard drive or diskette. You cam open a read-only file; you just...

Excel: Why documents save with the words, spreadsheet

spreadsheet: I would IMAGINE that although they share it, the workbook itself is not shared - so only one of them can have it open at a time to make changes, the second one will get a message advising it is open and would they like to open a read only copy - by default,...

Excel: doubt in excel, tiny box, bounding box

tiny box, bounding box, autofill: Hi Ramya, This can be done with a standard Autofill Down operation. Since a worksheet can have no more than 65536 rows, I will give you an example for incrementing from 1 to 100000 by increments of 10 so that cells B1, B2, B3, ... B10000 contain the numbers...

Excel: A doubt in excel, format button, question thanks

format button, question thanks, mr bob: First, if the words in column A contains all the words of column B , then how is it possible to find the items in column A which does not match column B? That s a contradiction, no? However, to filter them, you can switch my original formula to (again in...

Excel: doubt in excel, cell a1, series feature

cell a1, series feature, bottom right: Ramya If I correctly understand what you need want to do, here are two approachs: Excel has an autofill series feature that you can use. To use autofill series first select the cell that you want your first number in, and put in the first number....

Excel: download excel, microsoft works spreadsheet, microsoft com downloads

microsoft works spreadsheet, microsoft com downloads, excel spreadsheet: Jackie The view will open automatically (at least it should) when you open the xl file After you install this download, open a file in Excel Viewer 2003: On the File menu, click Open. In the Look in list, click the drive, folder, or Internet location...

Excel: Where is download kept, comcast cable, cell a2

comcast cable, cell a2, drive location: Max Short answer is I m not sure why it s taking longer on your computer. Seems like its not an excel issue since it only takes 10 seconds on your friends computer. Assuming all other things are the same on your friends computer as yours. I would...

Excel: another drag down option?, drag down, decimal integer

drag down, decimal integer, consecutive numbers: Hi Melanie I assume that your use of Hexadecimal is intentional. Easiest way to do it is to have the start data in one cell in Decimal Integer format (you can hide the whole column later if you do not want to see it) - assume it is in B2. In the next adjacent...

Excel: drop the 0 in excel, csv version, nature of the beast

csv version, nature of the beast, resave: The leading zero is still there (open it from Notepad). It s when you re-open in Excel that you lose the zero. If it were formatted as text, then saving & reopening it as a normal Excel file will show the leading zero. If you really want to save it as csv,...

Excel: drop down arrow box, down arrow button, data validation

down arrow button, data validation, dalgleish: Caley, It is under the data menu and it is called Validation . . . Extensive coverage of this feature is found at Debra Dalgleish s site http://www.contextures.com/tiptech.html Look in the index under D for data validation pages on here site. ...

Excel: drop down box, data validation, excel 97

data validation, excel 97, cells: Select the cells to contain these values, use Data/Validation, select List in the Allow dropdown, then simply type the values separated with commas (no need for quotes): Yes,No,Unknown,Thanks Then you can click the Error Alert tab and enter a message...

Excel: drop down list, farmer name, microsoft excel

farmer name, microsoft excel, drop down list: You said you d like a small instruction . The instructions are at this link (which I gave before): http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm ______________________________________________ Hi, Sussan. I...

Excel: drop down list if certain conditions met, input cell, drop down list

input cell, drop down list, drop down menu: You don t make it clear what you mean by a drop down and a drop down can be one of about four different controls, each with its own way of doing things . Remember, I, or anyone else you submit posts to, can not see what you see and ALL and EVERYTHING I know...

Excel: drop down list in colour, control toolbox, initial question

control toolbox, initial question, different colors: View/Toolbars/Control Toolbox. Pick a combobox, draw it on the worksheet, right-click it, select properties, change the backcolor to a red, fill in the ListFillrange property, and you should be all set. Repeat using different colors for different dropdowns...

Excel: drop down list to filter worksheet, target address, data validation

target address, data validation, autofilter: Darren Sorry I m not a VBA expert, so I m not sure how you would activate the drop down list with the Macro. Remember you can also get the drop down list to occur with data validation, where the list will appear and you can select from the list. You probaly...

Excel: drop down list in sentence, target address, data validation

target address, data validation, sheet tab: You can t have a dropdown reference PART of a sentence, but you can do this. Create the list of single words: always, usually, sometimes, and never, for your data validation. Say that data validation is in cell E7. In cell E9, for example, you can put something...

Excel: drop down list, cell b2, cell a2

cell b2, cell a2, range f1: Sorry, I think I misunderstood the scenario that you were presenting, when you were saying DD109 it looked like it was a cell reference, but it was not, as now you are referring to names like Don. The INDIRECT formula won t work in this case, unless you have...

Excel: drop down lists, drop down list, spreadsheet

drop down list, spreadsheet, shopping cart: Will - It sounds as if you want to create a shopping-cart effect, where you can change the amount ordered and the price will change: product1 0 $.50 $.00 product2 2 $.35 $.70 Where the first number is the drop-down. I do not think I understand...

Excel: drop down menu question, data validation, n1000

data validation, n1000, sheet1: Another sheet: if(C3= , ,Vlookup(C3,sheet2!$M$1:$N$1000,2,False)) Another open workbook in the same instance of excel: if(C3= , ,Vlookup(C3,[Databook.xls]Sheet1!$M$1:$N$1000,2,False)) ---------------- Hello Justin, Since you don t give...

Excel: drop down menus for sorting, excel spreadsheet, filter option

excel spreadsheet, filter option, drop down menu: Donnie Do you mean a filter option that will only show a particular brand etc? If so Data Filter will give you drop down list options at the top of every column If not then I would need to know more details as there are many different options that...

Excel: dropdown boxes, visual basic programming, dropdown boxes

visual basic programming, dropdown boxes, drop down boxes: Hi Nick, Well if you haven t used Visual Basic Programming, you are going to see how it will make your life easier. I do not know of a way to clear the combo box without VBA. So here it is. Private Sub Workbook_Open() Sheets( Sheet1 ).Range( A1 ).Value...

Excel: No dupes, vlookup, excel worksheet

vlookup, excel worksheet, a2: given this layout of the data on Claims October 2006: A B 1 Claim# Amount 2 1234 x 3 4444 x 4 1234 x then the formula for x is: =IF(COUNTIF($A$2:A2,A2) 1,0,VLOOKUP(A2, Master File !$A$1:$B$1000,2,FALSE)) ...

Excel: If duplicated data in any row from B77-B132 comes up with message, parent cells, syntax errors

parent cells, syntax errors, rng: first, I didn t test the code so there may be a typo or some other problem but I did compile it, so there should be no syntax errors. I took that bit of code and added a few lines so it would run for a test and it ran fine for me: Sub ABC() Set rng...

Excel: no duplication of numbers, douglas m smith, data validation

douglas m smith, data validation, message tab: Hi Beth, This can be done with Data Validation. Select the cells you will be entering the numbers into (example: A1:A30), the select Validation from the Data menu. In the dialog box, select Custom from the Allow dropdown box. In the Formula edit box,...

Excel: dynamic ?, excel 2003 formulas, exact formula

excel 2003 formulas, exact formula, b32: It looks like the final dot got included in the address, as this input box is plain text I have little control over that. You may try again, I am putting it on a separate line: http://www.ozgrid.com/Excel/DynamicRanges.htm I hope this time it works. ...

Excel: dynamic ranges, dynamic ranges, vba

dynamic ranges, vba, definitions: Deleting data CAN cause #REF errors, but CLEARING the data won t. If you have a defined name to be the INDIRECT of what you now have, then deleting it won t make a difference. For example, if you define Rodney to be =Sheet1!$A$1 and then delete row 1, the...

Excel: dynamic referencing of external sheets, master spreadsheet, slave files

master spreadsheet, slave files, generic formula: If you place the current sheet name in a cell, like putting Third Sheet in cell D3 (for example), then your formula can be =INDIRECT( C:[Slavefile.xls] & D3 & !A1) which will work fine with one caveat -- for it to calculate correctly, that file MUST...

Excel: E, column feature, delimeter

column feature, delimeter, cell a1: Jamal You can use the test to columns feature to separate the data in the cell. This feature will separate the contents of the cell and put it into adjoining columns. When you use the text to columns feature you have the option of choosing a fixed width...

Excel: E-mailing sheets with Macro's, target database, cell a2

target database, cell a2, style entry: Sure, no problem use the IF statement eg IF len(range( A2 ).value) 0 then do the processs END IF In this example, as long as SOMETHING is in cell A2 it will work You could also put an else statement to be helpful if... else msgbox...

Excel: E, row headings, pivot table

row headings, pivot table, null value: Difficult to answer without seeing the actual sheet, which I guess is confidential (if not and you can mail it to me you can get me at aidan.heritage@virgin.net) - however, worth RIGHT clicking on the pivot table and exploring the options for the field - I...

Excel: Easy way to highlight entire row if cell contains data?, conditional formatting, shift character

conditional formatting, shift character, blank module: First of all you can see all of the tying the person had to do to explain things for you and even then it might not work as he said. Secondly that does EXACTLY the same thing as my suggestion did. It saves you about two steps - the pre-selection of the cells...

Excel: Easy way to do this, cell a2 type, cell b2

cell a2 type, cell b2, cell d2: Hi Jallison, Here s a couple things you can do. Please understand that I will explain from the most basic, not knowing your experience level. Open a blank workbook. On the first sheet Sheet1. In cell A1 type in 8/30 In cell B1 type in 8/31 In cell...

Excel: Ebay Fees formula, ebay fees, insertion fee

ebay fees, insertion fee, ebay: Jason I don t have a clue about how the e-bay fees work, so I can t really give you a formula for calculating them If you give me some more information about how fees are calculated I can probably tell you how to set up a formula. Sorry I couldn t be...

Excel: Ecel, ineptness, unfortunatley

ineptness, unfortunatley, customer name: Tania You can do exactly what you want with a macro that will both sequentially number the quotes and then save each one in a quote file. I have done exactly that in the past. Unfortunatley I cannot find my file that has the quote template in it, and...

Excel: Ecel vs Access, ecel, office97

ecel, office97, access 97: Is this ANY macro? If so, I m VERY confused - if it s a SPECIFIC macro, it may be trying to load MS Access - POSSIBLY for queries to run - it certainly ISN T a problem with office 2002 (XP) and Access 97 as that is the combination I run in my office - at...

Excel: Ecxel, indirect address, sheet1

indirect address, sheet1, ogilvy: Hello Ivan, In sheet2 in cell A1 put in the following formula =INDIRECT(ADDRESS(TRUNC((ROW()-1)/4)+1,MOD(ROW()-1,4)+1,1,TRUE, Sheet1 )) then drag fill it down column A of sheet2 until you run out of data. This assumes 1) The sheet with the...

Excel: Edit link source, vlinks, demo demo

vlinks, demo demo, link source: Something like this: Dim vLink As Variant Dim vLinks As Variant Dim sNewLink as string sNewLink= C:demodemo.xls vLinks = oBook.LinkSources(xlExcelLinks) If IsEmpty(vLinks) Then Exit Sub For Each vLink In vLinks ...

Excel: Edit worksheet cells, worksheet cells, iname

worksheet cells, iname, combobox: The key here is to set the ControlSource property of each textbox. If, for instance, TextBox1.ControlSource = C7 , then the value in TextBox1 on your userform is linked to cell C7 on your worksheet; when you edit TextBox1, the new value will automatically...

Excel: Editing data in a column, blank cell, excel 2000

blank cell, excel 2000, copy paste: Try this, Les: 1) In a blank cell somewhere, enter the number .1 (point one). 2) Copy it to the clipboard. 3) Select all the prices. 4) On the menu bar, select Edit/Paste Special. 5) Under Paste choose Values. Under Operation choose Subtract....

Excel: Editing Excel 2000, excel 2000, cheers

excel 2000, cheers: Hi Joan, How are you? Oops ... I think that I did not understand your question. If you want to sum 5 to, for example, A1, A2,... A10, type 5 in C1 type =A1+$C$1 in B1 Copy down this formula Select B1:B10 Edit copy Edit -paste special Enable...

Excel: Editing a excel doc live on the net, excel spread sheet, lnk

excel spread sheet, lnk, google: Hi there, this cannot be done the way you set it up. In short, the index.htm file is NOT an Excel file anymore. A good explanation is on: http://groups.google.ca/group/microsoft.public.excel.misc/browse_thread/thread/5fb183afc0a85a9c/6bcc066899b8fe4a?lnk=st&q=Editing+Excel+live+web&rnum=3&hl=en#6bcc066899b8fe4a...

Excel: Editing links problem, source data, source file

source data, source file, error message: Hi, Mike, As long as the sheet with the link on it is unprotected, you should be able to update or remove the link. Go ahead and mail the sheet to me at kawai54@excite.com. I ll take a look at it. (Make the sheet as small as possible, since that account...

Excel: Elapsed Time Based On Service Level Agreement, maintenance coverage, fri

maintenance coverage, fri, time problem: I did do this for a previous job, and it was about a weeks work to do the formula! Yours is PROBABLY slightly easier than mine as you have EITHER a 7 day week, or a 5 day week - we had a 6 day week to deal with. The basic formula would need to look at the...

Excel: Elapsed Time Calculation, musical sequence, bill weber

musical sequence, bill weber, mm ss: Bill, this may or may not work for you. It s a bit convoluted in that the result actually will still calculate h:mm but we will correct that. You will also need to be very cautious about how you use the results in further calculations as well. Here s the...

Excel: Elapsed time fourmula, type time, time sheet

type time, time sheet, time mar: if you have both the date and the time in the same cell, you only need to subtract the lastest from the earliest. Mar 03, 2006 7:24 Mar 04, 2006 11:04 27:40 in C1 I had =B1-A1 and I custom formatted it as [h]:mm -- Regards, Tom Ogilvy ...

Excel: Eliminating Doubles in Excel, chip pearson, ogilvy

chip pearson, ogilvy, several ways: Minda, I don t use a MAC, but most of what I have should work on the mac. There are many ways to do this. Some involving macros, some using manual methods. I can t see your data or know what constitutes a duplicate (looking at cells in one column or...

Excel: Eliminating Duplicate Rows, range c, good solution

range c, good solution, pastes: Hey David, I think using a macro would be a good solution to your problem. I don t know your experience with macros, so please let me know if you need any help. This macro will definitely need some tweaking to suit your spreadsheet. The way it is currently...

Excel: Email Notification of Changes in Excel Schedule, target range, interior property

target range, interior property, mitem: No need to apologize and I was not accusing you of doing this - just stating my position. Did the code I sent not help? If Range( A1 ).Interior.ColorIndex = 48 Then MsgBox test End If You can test for more than one color, like If Range( A1 ).Interior.ColorIndex...

Excel: Email one spreadsheet only, click this icon, current sheet

click this icon, current sheet, attachement: Good morning Roger! If everything is installed properly, you can email directly from Microsoft Excel. 1) Next to the save icon there is an icon that looks like a letter and envelope. Click this icon. 2) Excel will prompt if you want to send just the...

Excel: Emailing, john walkenbach, refernce

john walkenbach, refernce, ogilvy: Here is a reference to all kinds of code examples on emailing from Excel: http://www.rondebruin.nl/sendmail.htm Here is an refernce to taking an action based on a cell value: http://j-walk.com/ss/excel/tips/tip87.htm Playing a Sound Based on a...

Excel: Emailing excel changes page setup?, microsoft excel 97, default printers

microsoft excel 97, default printers, legal landscape: Mel: The quick answer is no, you do not appear to be doing anything incorrectly and instead, there is something odd going on. Notes: 1) Even if you save as Excel 91 & 5.0/95, as long as you have set up your page as Landscape, anyone else who opens the...

Excel: Emailing Sheet that has combo box, employee id number, excel passwords

employee id number, excel passwords, combo boxes: Hi Tina, The best way to do this is to not send the first two sheets to anybody. Here is what I would do, Save the workbook with the first two sheets as your database of employee information. Then for the third sheet I would have a separate file for...

Excel: Emailing a workbook, dd mm yy, de bruin

dd mm yy, de bruin, excel application: Not sure exactly what the question is. Are you looking for help with VBA to rename the file and email the report. Thisworkbook.SaveAs sPath & _ report_ & format(date, dd_mm_yy ) & _ .xls Ron de Bruin s site has all kinds of sample code for...

Excel: Emailing worksheet pages, sheet tab, best bet

sheet tab, best bet, borders: Unless you are want to do this with a macro, then your best bet is to make a copy of the sheet. The easiest way to do that is to right click the sheet tab, choose Move or Copy, select new workbook form the dropdown and click Create A Copy. Then with that...

Excel: Embedded macro within Excel to save non Excel related files, liverpool uk, pictures2

liverpool uk, pictures2, excel spreadsheet: Rob, Try this and let me know how it works. xf Sub CopyFiles() On Error GoTo DirErr MkDir c:Pictures2 Makes new directory (need to change) FileDir = c:Pictures Source directory (need to change) TargetDir = c:Pictures2...

Excel: Embedded Objects, empty folder, new folder

empty folder, new folder, windows explorer: You can run this code on each workbook. WHen it s finished, a list of sheets can be seen in the Immediate window along with the top left cell address of the 1st object in the sheet (Ctrl/G from inside the VBE): Sub HasObjects() untested proc For...

Excel: Embedded table in Excel, answerable question, access file

answerable question, access file, hi john: This is an access question, but this is what I would do: Paste the table into excel, save it from access, file/get external data/link table pick the excel file now data will flow. I m sorry I can t help further, I thought I resigned from this years...

Excel: Embedding command buttons, control toolbox, command buttons

control toolbox, command buttons, commandbutton: Sorry, the command buttons i am using are made from the control toolbox Why do you say sorry - the code I gave you was written for a commandbutton from the control toolbox toolbar. Private Sub CommandButton1_Click() dim v as variant v = Application.InputBox(...

Excel: Embedding command buttons, itop, command buttons

itop, command buttons, control toolbox: Hi Paul, By far the simplest way is to save its position on entry to the button click event, and restore it before exit. Like this Private Sub CommandButton1_Click() Dim iTop As Double save button top position iTop = CommandButton1.Top...

Excel: Embedding images to cells, camera object, macro button

camera object, macro button, james evans: My usual approach to this type of scenarios is the use of the camera object, instead of macros. It is the equivalent of opening a window to peer on a different part of the document, and that part may perfectly be an image! One example of this technique,...

Excel: Embedding jpg's in Excel workbooks, word docs, excel workbooks

word docs, excel workbooks, xls file: Hi Kelly, I m not sure how to do exactly what you ask, unless you create a hyperlink (which is not an icon, but a link. Click on the cell and choose Insert/Hyperlink and go to the jpg that you want. It will create a blue text link, but you could change...

Excel: Embedding an or Statement into a if(isblank) statement, cell d1, using microsoft office

cell d1, using microsoft office, numerical values: Your Very Close. Excel AND and OR statements work a little differnent that you would expect. Also from my exp. IsBlank, ISErr, the IS statements only take one cell at a time. Try This: =IF(AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)),0,1) or you...

Excel: Emergency MS Excel and VBA Programming, phaseone, blank copy

phaseone, blank copy, return date: Since I don t know the ranges of your data (and therefore had to make some assumptions) then here is a sample of what you need. This should get you going. Your logic is good but your understanding of what you are trying to do and how you are doing it are...

Excel: Emergency MS Excel VBA Programming, copy worksheets, excel vba programming

copy worksheets, excel vba programming, blank copy: Jeanna, It is difficult to correct a code but I can give you a new one that will perform following- (1) Row 1 has headers (2) Column A Contact details (3) Column B date (4) Column C Date If Column B date is more than 24 days before today s date and if...

Excel: Emergency MS Excel VBA Programming, phaseone, excel vba programming

phaseone, excel vba programming, column letter: with the limited information provided, this would be my best guess: Sub Expiration() change G to the column letter of the column that contains the sent out date. Change the I to the letter of the column that will be blank if you need...

Excel: Employee Time Sheet, douglas m smith, time column

douglas m smith, time column, brainbench: Hi Chuck, Generally, the way you handle a situation like this would be with MAX and MIN functions. Example: Cells F1 adds up the hours in cells A1:E1, and can t exceed 40. G1 would show any hours over 40. The formulas would be: F1: =MIN(SUM(A1:E1),40)...

Excel: Emptying Clipboard, microsoft knowledge base, excel 97

microsoft knowledge base, excel 97, old computer: Hello, Paula, The only article I could find in the Microsoft Knowledge Base about this question applies to Excel 97. But take a look and see if it can help you: http://support.microsoft.com/default.aspx?scid=kb;en-us;196620 Good luck! Regards,...

Excel: "LAST ENTRY" Function?, array formula, curly brackets

array formula, curly brackets, formula index: Hello, Verril, Sorry about the delay but I wanted to research this question. I have no idea what fat-finger boo-boo means, but I do have a formula to find the last entry in a row or column. For this, you ll need to use an array formula. This type...

Excel: End of cell sound or warning, data validation, keyascii

data validation, keyascii, text boxes: The EASIEST way to see this is for me to send you a sample file, BUT I don t get to see your email address - can you let me have it and I ll mail you the sample (my email is aidan.heritage@virgin.net) Put simply though, create a user form with 12 text boxes,...

Excel: Enquiry for Excel Help, cell b1, cell a1

cell b1, cell a1, excel help: Chan The IF function can reference the EB only but it needs to ask the question of where the EB is for example +IF(A1= EB , YES , NO ) The fomrula (assume it is in cell B1) says Look at cell A1, and if it is equal to EB, then put a yes in cell...

Excel: Enter key and moving to a different cell, shift key, spreadsheet

shift key, spreadsheet, thanks in advance: i am not sure what your objective to move to another cell is! if you remove lock from the cell by right clicking the particular cell, select Format Cell and go to Protection and uncheck Locked, you will be able to select the cell, You have to unprotect...

Excel: Enter part of the date (Just time), time time time, format cells

time time time, format cells, time hours: Hi Pyarali How are you? Please do the following 1. Type in A2 =TIME(9,10,0) or simply 09:00:00 2. Type in B2 =TIME(10,10,0) or simply 10:00:00 3. Type in C2 =B2-A2 If there is something wrong with the formats, select C2 and Format-cells...

Excel: Enter value in previous cell, empty cells, target

empty cells, target, excel worksheet: Leslie, In order to do this as you have described, you ll have to take advantage of the Worksheet_SelectionChange event in VBA. Rather than bore you with details, I ll just provide the code below and let you do further research on HOW it works if you are...

Excel: Entering algebra into excel formulas, cell reference, excel formulas

cell reference, excel formulas, index type: Chris I m not sure what the P+P*r formula is, but maybe some hints as to why it won t work In the formulas you need to preceed them with an = or plus or minus sign so excel knows it is a formula. Excel will not know what P or r is without you giving...

Excel: Entering data, move selection, select tools

move selection, select tools, cavity: Hi Betsy, Go to the Excel menu and select Tools then Options then the Edit tab then below the checkbox that reads Move selection after Enter: you will see Direction: to the right of that is a drop down box click it and select Right click OK...

Excel: Entering days of the week, douglas m smith, dddd

douglas m smith, dddd, monday date: Hi Ted, Going across multiple sheets, there isn t a real slick way to do this. You can type a Monday date, like 10/16 in a cell (say A1) on the first sheet (Sheet1) and format it as custom format dddd to display it as Monday. In the same cell on the...

Excel: Entering information into a database, address phone number, column headings

address phone number, column headings, email attachments: There is no simple example I can give you because there is no simple example. The macro will involve having OUTLOOK and EXCEL talk to each other. And while both are certainly capable of doing this the code is not for the beginner. And, as politely as...

Excel: Entering numbers for military time, format cells, military time

format cells, military time, column c: Hi, Laura, I have a workaround for you. You won t be able to enter numbers and have them appear as military times automatically, but here s what we can do: you ll enter the numbers in column C, and the times will immediately appear in column D. Let s...

Excel: Entering User Defined Functions, question rank, free whitepaper

question rank, free whitepaper, excel 5: 1 - Press Alt/F11 (brings you to the VBE) 2 - Use Insert/Module 3 - paste in the code: Public Function MyRank(Rg As Range, This As Range) As Integer Dim col As New Collection On Error Resume Next For Each thing In Rg col.Add thing, CStr(thing)...

Excel: Entering Variable on another sheet, input value, ogilvy

input value, ogilvy, hurd: Assume on Sheet1 you will put the input value in A1 and want to see the result in B1: In the cell named input put in the formula =Sheet1!$A$1 ( if sheet1 is actually named Sheet 1 with a space, use = Sheet 1 !$A$1 using single quotes to enclose the...

Excel: Entries into next column and next cell, target address, sheet tab

target address, sheet tab, value date: 1 & 2: Right-click the sheet tab, select View code, paste this in: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address $A$1 Then Exit Sub With Range( AA65536 ).End(xlUp).Offset(1) .Value = Range( A1 ).Value ...

Excel: Error 91 "object variable not set" while running a script that previously worked, run time error, run time error 91

run time error, run time error 91, blue cells: Monty, You would get this error when no blue cells are found since the activate command is then equivalent to Nothing.Activate which raises such an error. Here is a way to see the problem Sub find_blue_cells() Set the search criteria for the font...

Excel: Error with adding and subtracting in Excel, numerical format, true numbers

numerical format, true numbers, subtracting in excel: Not sure what your question is since all I read is what you are having a problem with. Assuming you meant to ask how to get the numbers to subtract then check the format of the cells where the numbers are. I suspect the format is text. Assuming that...

Excel: Error creating new Excel file, space automation, startup directory

space automation, startup directory, xlb: Try these: find excel.exe, right-click and use Create Shortcut. On the shortcut, right click & select Properties. In the target box, add this to the end (start with leading space: /Automation Double click this to start excel. If this works, then you probably...

Excel: Error Message, span length, ft span

span length, ft span, continuation character: you either need to put that on one line: If Span = Then MsgBox Enter Span Length , vbCritical or use the continuation character: If Span = Then _ MsgBox Enter Span Length , vbCritical or include End If: If Span = Then MsgBox...

Excel: Error Message, ft span, span length

ft span, span length, setfocus: Hello Edgar, Private Sub cbAccept2_Click() Enter the Span value into the active cell Dim Span As Single Span = tboxSpan.Text If Trim(Span)= Then MsgBox Enter Span Length , vbCritical tboxSpan.SetFocus tboxSpan.SelStart...

Excel: Error Message, ft span, span length

ft span, span length, glulam: The correction is in ALL CAPS Dim Span As Single Span = tboxSpan.Text If Span 0.99 And Span 50.01 Then Range( C7 ).Value = sbSpan.Value END IF If Span = Then MsgBox Enter Span Length , vbCritical Else ...

Excel: Error Messages, message thanks, blow by blow

message thanks, blow by blow, lesion: Hi James Could you send me the errant work sheet ? If so use limbic_lesion@hotmail.com Plus can you supply blow by blow the actions you tak ein copying the worksheet. I will say now that most likely the whole of the copy action will have to be done...

Excel: Error messages, adobe acrobat, webx

adobe acrobat, webx, ogilvy: Bruce, This appears to be caused by Adobe Acrobat. I assume you have that installed. Here is a link to a discussion which describes how to correct the problem. There is at least one individual using office 2007. http://forum.adobe.com/webx/.3bc307c7...

Excel: Error upon opening an Excel file, douglas m smith, ok tab

douglas m smith, ok tab, running office: Hi Leon, Look under the Tools menu for Add-Ins... . There should be an item named something like Business Functions (I m guessing here) that is working name of BUSFUN.XLA. I m guessing it s checked. Uncheck it and the error message should go away....

Excel: Error upon opening an Excel file, ok tab, running office

ok tab, running office, business function: Hi Leon, BUSFUN.XLA is an independent excel Add-in. More than likely what has happened is Excel does not have the add-in installed. Since you reinstalled Office 2000 and did not reinstall the Business Function add-in. If you wish to use the Business function...

Excel: Error upon opening an Excel file, xlstart directory, ok tab

xlstart directory, ok tab, running office: Look in Tools= Addins and see if you have an addin selected with a similar name. If so, uncheck it. In fact, uncheck any addin you have selected. You can then close excel and open it back up. Then start selecting the addins you want. If one of them causes...

Excel: Error opening Excel file, novell network, experiencing technical difficulties

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)...