sheet names, sheet1, exact value: in the worksheet1, select the cell with Snap1. right click on it and select Hyperlink. In the hyperlink dialog, Select Bookmark. You will get a list of sheet names. Select worksheet2 from that list. In the upper part of the dialog is a textbox - enter...

Excel: need advice on VBA nested IF Function for range senario, vba module, company payroll

vba module, company payroll, staff base: Anson, You need to reverse the order of your if statements. Suppose an employee makes $2210, the result should be 11.25. In your procedure it looks at the first If statement: is 2210 701? The result is true so stf_socso is set to 3.75 and the remaining...

Excel: I need your assistance to reformat copy in Excel., microsoft word 2002, section header

microsoft word 2002, section header, word 5: Kim, I m not sure I can answer this without actually having a document to work with. Working through a list of files isn t too difficult, as long as the code is identical for all of the files. If the files are all laid out the same, that part should be...

Excel: I need a column that count dates, count dates, format button

count dates, format button, track tasks: If the dates are in col B, then put this in the #Days passed column (starting in row 2): =TODAY()-B2 then fill down. You may have to format this column as General. Next, Select the column, use format/conditional formatting, Put Cell Value to Greater than 14,...

Excel: I need to see all combinations available, hard drives, combinations

hard drives, combinations, a1: Sub allCombos() Dim digit1 as integer, digit2 as integer Dim digit3 as integer, digit4 as integer Dim digit5 as integer Dim rngEntry as range Set rngEntry = Range( A1 ) For digit1 = 1 to 5 For digit2 = 1 to 5 If digit2 digit1...

Excel: need to count multiple criteria plus, countif, criterion

countif, criterion, subset: Hi Steve, Can you give me an example of what you have and what you are trying to accomplish. I kind of understand what you are asking but not quite. If you would like you can send me an example worksheet to cloverken@yahoo.com Thank You Ke...

Excel: need to count multiple criteria, array formula, cell b1

array formula, cell b1, array formulas: Hi Gena, To illustrate an example I will pretend that your Event type data is cells A2:A10 with the event type title in cell A1. I will pretend that your boro data is located in cells B2:B10 with the boro title in cell B1. In cell E1 type in...

Excel: Do I need to create a macro here?, excel tips tricks, value input

excel tips tricks, value input, yellow orange: =SUM(OFFSET(A1,0,0,N,1)) will sum A1:A30 if N is 30, for example. Or if this independent cell is G2, for example, then =SUM(OFFSET(A1,0,0,G2,1)) will do it. HTH I m leading a FREE 1-hour online Webinar on Excel Tips & Tricks on Nov 30. If interested,...

Excel: need a formula, conditional formatting, alpha sort

conditional formatting, alpha sort, shop names: I m afraid that (as far as I know!) there isn t a formula that you could use directly to do this - you COULD use conditional formatting if the choice is 4 or fewer shops (3 conditional formats and one default format where all conditions fail) and you COULD...

Excel: need a formula, insurance comapnies, medical insurance

insurance comapnies, medical insurance, dob: Len Sometimesw working with dates can be a bit problematic. How about this formula to solve your problem =ROUNDDOWN((+TODAY()-C4)/365,0) What this formula says is subtract the value in cell C4, which is the date of birth from the value Produced...

Excel: need a formula, missing something, l1

missing something, l1, cells: Shalk Try the following formula =IF(VLOOKUP(1,L1:L9,1)=1,COUNT(L1:L9), ) The formula says if vlookup finds a 1 in any of the cells L1 thru L9, then count the rows, otherwise put a blank ( )in the cell. I m not sure of your statement that the...

Excel: need a formula, column c, c100

column c, c100, spreadsheet: I have just seen the question on the pool, maybe it is too late but here is a way to do it. You can use the the SUMPRODUCT formula, in a way like this: =SUMPRODUCT((A1:A100= State )*(C1:C100 0)) This will count all the users that belongs to state State...

Excel: need formula, j8, ogilvy

j8, ogilvy, leaves: Hello Rich. The formula would go in I8, since that is where you want to display the results. in I8 you would put =if(and(j8 =1,j8 =20),9.26,if(and(j8 =21,j8 =30),11.11,if(j8 =31,13.89,0))) This is what you stated, but leaves out values 20 and...

Excel: need formula, j8, andrea

j8, andrea: Rich - You ve answered your own question. You want to use 3 IF statements - you just need to nest them. An If statement reads: If(test,if true,if false) So look at your If statements: #1 If(J8 =20,I8=9.26,if false do this) #2 If(And(J8 =21,J8 =30),I8=11.11,if...

Excel: need formula, j8, snippit

j8, snippit: Rich: There are a variety of ways to do this and here are two of them: =IF(AND(J8 =20,J8 =1),9.26,IF(AND(J8 =21,J8 =30),11.11,IF(J8 =31,13.89, = 0 ))) Here is a shroter one... =IF(J8 =31,13.89,IF(J8 =21,11.11,IF(J8 =1,9.26, = 0 ))) Place either...

Excel: need formula to count cells below, array formula, ogilvy

array formula, ogilvy, spreadsheet: In F2 put in the formula: =IF(E2,ROWS(OFFSET(E2,0,0,SMALL(IF(E2:$E$20=FALSE,ROW(E2:$E$20)-ROW(E1)),1),1))-1,0) enter with Ctrl+Shift+enter rather than just Enter since this is an array formula. Now drag fill down the column. -- Regards, Tom...

Excel: need formula to count cells below, e7, true value

e7, true value, spreadsheet: A relatively easy one this time! =IF(E1,MATCH(FALSE,E2:$E$7,0), ) This formula is put in E1 and dragged down - as you will see from my example, I ve used a small data range of E1:E7 so just change the 7 as necessary - this formula INCLUDES the true...

Excel: need a formula, filter conditions, filter auto

filter conditions, filter auto, data changes: Yes, you would need to do a filter - although the filter conditions remain the same, so you don t have much to do - by the same token, you would need to run the macro each time the data changed, so the change of data doesn t affect the need to update the data...

Excel: need help, b1 b2, a10

b1 b2, a10, b6: You can use the SUMPRODUCT function for that. You need to set the condition that has to be fulfilled, and multiply it for the range where the ranges are. For example: =SUMPRODUCT((A2:A10= Jan )*B2:B10) This will add all the values on range B2:B10 where...

Excel: I need you help!, colomn, excel spreadsheet

colomn, excel spreadsheet, time periods: It might help to see the file you are using (my email is aidan.heritage@virgin.net). However, you asked for the time to be shown as a decimal (so 5 hours is 5, not 5:00, AND 5:00 is actually 5/24 - excel would like to see the decimal value rather than a time...

Excel: need help, e mail address, cell a1

e mail address, cell a1, excel spreadsheet: For the formulas below, I have put the value (150) in cell A1, and the different percentages in cells A3, A4 and A5. With that, for the first segment we can use: =IF(A1 29.99,29.99*A3,A1*29.99) For the second =IF(A1 599.99,579*A4,IF(A1 29.99,(A1-29.99)*A4,0))...

Excel: I need your help, black arrow, excel macro

black arrow, excel macro, web query: Hi Daniel, It depends on what the data looks like on the website. If it was built as part of a table (on the website) you can export directly to excel. Right-click on the table (in the website) and then choose export to excel. Then when the New Web Query...

Excel: I need help with creating a specific VBA Macro in a Excel Spreadsheet., new begining, target address

new begining, target address, amouts: Private Sub Workbook_Open should be on the workbook object in the Visual Basic Editor, and it runs when the workbook opens. Private Sub Worksheet_BeforeDoubleClick should be on the worksheet object, and it runs whenever you double-click on that worksheet....

Excel: I need help creating a specific VBA macro in excel for a specific spreadsheet., new begining, macro in excel

new begining, macro in excel, amouts: Normally, I would want to retain previous days data, so I would have a table of columns one for each day. I don t think you can have a formula achieve what you want.VBA would work, but it seems a lot of effort when all that is necessary is to copy & paste...

Excel: I need help creating a specific Macro for an Excel Spreadsheet, new begining, amouts

new begining, amouts, project workbook: go into the VBE (from Excel, Alt+F11) and for your project/workbook in the project explorer window, find the ThisWorkbook entry. Double click on it. In the left dropdown at the top of the resulting module select Workbook. From the right dropdown select...

Excel: need help with graphs, budget data, amouts

budget data, amouts, personal preference: Roomi: Question 1: A lot of charting is personal preference and what you are looking for in the end result. I have uploaded my personal preference to the following location: http://home.swbell.net/nate-sus/excel/budgetcharts.xls The Budget and Actual...

Excel: need help in linking a piece of info from website to excel, query parameters, web query

query parameters, web query, currency rates: irfan, Since I know nothing of the web sites you speak of, the only help I can give is very general. It sounds like you could use a web query. Here is some information: http://support.microsoft.com/default.aspx?scid=kb;en-us;162051 XL97: How to...

Excel: need help, john walkenbach, excel tutorials

john walkenbach, excel tutorials, getstarted: book: those written by John Walkenbach http://www.j-walk.com/ss/excel on the left are links to his books and also links to sample code and other sites. Dave McRitchie has some links to Tutorials (VBA tutorials are after the Excel tutorials): http://www.mvps.org/dmcritchie/excel/getstarted.htm...

Excel: need to insert 3 blank rows, worksheet selection, blank row

worksheet selection, blank row, blank rows: Try this way - one of many ways to do what you want. I wonder - where did you get x3down from? Do Until ActiveCell.Row = 1 Insert blank row. ActiveCell.Resize(3, 1).EntireRow.Insert shift:=xlDown Move up one row. ActiveCell.Offset(-1,...

Excel: I need to locate duplicate..., filter feature, sequential number

filter feature, sequential number, huge list: Eraaj, Why not just sort the list by name? The duplicates would then be grouped togehter. If the original order is important, you can add a column with a sequential number. Once you delete the duplicates, which I assume is what you want to do, you can...

Excel: I need locate from a very..., douglas m smith, brainbench

douglas m smith, brainbench, variance: Hi Jonathon, You can use the following formula to mark the rows with a date in the range specified: =IF(AND(MONTH(A2)=7,DAY(A2) =5,DAY(A2) 11), X , ) MONTH returns a the month number from 1-12. DAY returns the day of the month from 1-31. The...

Excel: need a macro to..., copy worksheets, cell1

copy worksheets, cell1, b cells: Possibly something like this untested code: Sub copyData() Dim r As Range, r1 As Range Dim r2 As Range Dim cell As Range, cell1 As Range Dim rw As Long rw = 2 With Worksheets( Sheet1 ) Set r = .Range(.Range( E6 ), _ .Range( E6 ).End(xlDown))...

Excel: need macro to change color based on day of week, sheet tab, value case

sheet tab, value case, recalculation: F9 forces a recalculation. Try ctrl/alt/F9 (forces a full recalculation). If that doesn t work, add this code under the already-given code: Private Sub Worksheet_Change(ByVal Target As Range) Worksheet_Calculate End Sub if THAT doesn t work, send...

Excel: I need a macro/function/VBA solution, search target, target value

search target, target value, cell b2: Hi Steve, Yes, a function cannot do a GOTO--actually cannot do anything that modifies any other cell or cells on the sheet. To get the functionality you describe, the best way is to use a worksheet Change event procedure. This acts like a function, but...

Excel: need a reminder formula, format cells, c days

format cells, c days, column c: I d try something like this: Column A: payment due date Column B: # days late Column C: # days til payment is due Column A: data entry Column B: =IF(A2 =TODAY(), ,TODAY()-A2) alt: =IF(A2 =TODAY(), ,TODAY()-A2& days past due ) Column C: =IF(A2...

Excel: need to replace data in particular column, exact data, cells

exact data, cells: That doesn t make sense (YOU do, IT doesn t) -- if you highlight column D only, then the find/replace should only work on column D. Before you do the replace, us insert/name/define and ensure all you have highlighted is D. You MAY have merged cells somewhere...

Excel: I need a suggestion, reed jacobson, douglas m smith

reed jacobson, douglas m smith, john walkenbach: Hi Ritu, That s very admirable of you. There many resources available. A good book is invaluable. Any book by John Walkenbach (http://j-walk.com/ss/books/index.htm) or Reed Jacobsen (http://topics.practical.org/browse/Reed_Jacobson) is worth it s weight...

Excel: need to understand index match formula, coloum, data layout

coloum, data layout, sheet1: The examples will be referring to the data layout you have sent, ranges A1:F7 on sheet1 and sheet2 One fast solution can be this: =INDEX(Sheet2!$E$2:$E$7,MATCH($A2,Sheet2!$A$2:$A$7,0)) The first parameter is the variance row, and the second the match of...

Excel: needing more macro shortcut keys, upper case letters, microsoft windows xp home edition

upper case letters, microsoft windows xp home edition, shortcut keys: WHEW!!! You have up to 52 different macros that you also have to remember up to 52 different shortcut keys to know which key runs which macro? How do you keep up with them all and remember them all? You can t get additional shortcut keys. It seems it...

Excel: a negative column, format cells, drawl

format cells, drawl, help file: Concerning the first part, click Format, Cells and choose the desired format. Then choose Custom and put a negative sign at the beginning of the format. Concerning the second part, based on the way you have worded what you want then EXCEL can not arbitrarily...

Excel: nested conditionals, subject data, mr smith

subject data, mr smith, c100: Even if it doesn t sound fit, you can use SUMPRODUCT to achieve this task. In the parameters, just add the logical comparisons that you want to make, and it will return the records that match all of them. For example, supposing that the subject data is on...

Excel: nested countif and and function -XL 2000, array formula, countif

array formula, countif, sumproduct: I only use it with sumproduct where the conditions so warrant (but it could be used anywhere you need to perform that function). Other techniques are to use *1 such as you did or +0. Any mathematical operation to force the conversion. In your case, it wasn...

Excel: IF THEN nested formulas, e37

e37: You don t need to check the condition twice, try this way: =IF(E37 100, G35/50*G35, G35/100*G35) If the value in E37 is greater than 100, the result will be 1/50 (or error if G35=0), if less the result will be 1/100 (same error case) With numbers, as you...

Excel: nested functions, vlookup, d7

vlookup, d7, frame size: Here is my guess at what you want: =IF(C9= MESH (POLY) ,VLOOKUP(D7,AN180:AP222,3)-IF(OR(D7=247,D7=1760),20,0),VLOOKUP(D7,AN180:AP222,3)) You haven t really said if these sizes are only to be considered if it is MESH (POLY) or if it should be applied...

Excel: nesting?, response field, 3 things

response field, 3 things, response code: Keeshia As with a lot of the questions I get, it is very difficult to state them clearly, so that kinda makes it hard for me to figure out what is needed, that is the case with your quesion. I m at a loss to know what to do. It sounds as though you...

Excel: nesting countif statements, array formula, sumproduct

array formula, sumproduct, minus signs: I assume B4:B14 holds either true or false indicating the action was taken. then you can count on two conditions using an Array formula or Sumproduct (which can operate like an array formula with using array formula entry techniques) =SumProduct(--($A$4:$A$14=5),--($B$4:$B$14))...

Excel: nesting vlookup or something comparable, vlookup, column c

vlookup, column c, btq: Even if doesn t seem adequate, the SUMPRODUCT formula can fit on this case. You can use it in a way like this: =SUMPRODUCT((A1:A100= X )*(B1:B100= Y )*C1:C100) This will filter the column C to return the sum of all the rows that fulfill the two conditions...

Excel: if & nesting, cell a1, word chair

cell a1, word chair, aword: I have uploaded a sample spreadsheet, with the above two sample formulas, to the following location: http://home.swbell.net/nate-sus/excel/ifexample.xls You can now copy the formulas from this spreadsheet and change as needed for your situation...

Excel: network days and Median?, douglas m smith, entry boxes

douglas m smith, entry boxes, statistical functions: Hi Barrett, Excel has a built-in MEDIAN function. Here is what Excel Help says it does: Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than...

Excel: networking excel, simultaneous users, server machine

simultaneous users, server machine, odbc driver: Hi Jon, If you have formulas in your spreadsheet, Excel will automatically update the calculations as a user (any user) edits the file. One thing about an Excel file, is that it only allows one user to be in Read/Write mode at a time. First come, first...

Excel: new line character in an Excel cell, line feed character, line character

line feed character, line character, character number: Hi Smitha, Pressing Alt + Enter inserts what is called a Line Feed character. There are two ways to insert the Line Feed character in VB. The first is by adding character number 10: Line 1 & Chr(10) & Line 2 The second is by adding the command...

Excel: last non-zero entry, array formula, blank cell

array formula, blank cell, error values: Hello Mike, It would have been helpful if you provided the formula and I could have seen if it is different from what I offer =INDEX($F$1:$F$200,LARGE(IF(($F$3:$F$200 0)*ISNUMBER($F$3:$F$200),ROW($F$3:$F$200)),1),1)-750 this is also an array formula...

Excel: normalized distribution function, distribution function, new values

distribution function, new values, normal distribution: Hi sami, I am not sure if I understand your question correctly. Based on your query, attached is some information that may help you. In a normal distribution, the values can be from +inf to -inf, hence, not sure what you mean by saying that the new...

Excel: If number on both sheets then compare, b cells, newguy

b cells, newguy, sheeta: you say copy the entier line, but there are 2 sheets - which line should be copied? This code will create a new sheet with the differences and copy the info from sheetA, cols A & B and the differing number from SheetB column J (col 10). It also assumes the...

Excel: Can number of characters in a cell be defined to a fixed amount?, mainframe programmer, spreadsheet cell

mainframe programmer, spreadsheet cell, vba procedure: You would need to write a vba procedure to check the length of each of the cells & issue a message if there s an error before it s saved, or something like that. There s no way I know of to limit the length (outside of Data Validation, but that s not going...

Excel: Last number on a column, input values, a200

input values, a200, a11: Leandro, You aren t limited to A1:A200. You can change the range in the formula to anything you want. For example, if you are putting value in betwee A12 and A200 it would be =INDEX($A$1:$A$200,LARGE(IF($A$12:$A$200 ,ROW($A$12:$A$200)),1),1) ...

Excel: number combinations, permutation and combinations, number combinations

permutation and combinations, number combinations, code snippet: Here is a code snippet from Myrna Larson, the acknowledged queen of combinations and permutations. I am not sure about the part about 1-10 though ---------------------------------------------------------------------------- Following is a macro based...

Excel: number combinations, number combinations, a1

number combinations, a1: If you want to find out the number of possible combination, the result is 6^4 = 1296. If you want the combinations themselves, you can use a code like this to put them on column A of sheet 1: Sub GenerateCombinations() Dim i, j, k, l, z As Integer...

Excel: number should get convert to alphabets, buf, exit function

buf, exit function, time function: Dear Rizwan Excel does not have any functionality for this. However, on net you will find number of programs that convert Currency to words. You will need to modify any such program nd add this this as worksheet MACRO. I am giving blow one such prgram....

Excel: number format, text import wizard, substraction

text import wizard, substraction, column data: Jay, If you go through the text import wizard, then on the last dialog (step 3 of 3) you can specify that these columns should be handled as Text. Click on each column in that dialog that needs to be protected from Excel s interpretation and choose the...

Excel: number formats / values, numeric format, half of the time

numeric format, half of the time, mcc: From your formula I see that you are using Excel 2007. I think that the solution below will work on your version, but I cannot be sure, as I still have the 2003 version. From what you say, it looks like the issue is on the MATCH function not recognizing...

Excel: numbering rows that update themselves, row numbers, spread sheet

row numbers, spread sheet, j10: This very, very simple, Dave. Here s how you can set it up. Though there s more than way, I think it would be fast and easy to use the ROW function. This function simply returns the row number. Let s say you re putting 1 in A1, 2 in A2, etc. In each...

Excel: numbering rows, co wicklow ireland, vba procedure

co wicklow ireland, vba procedure, auto change: Hi Jean, How are you? I wonder whether the following is too simplistic, but there it goes: 1. type 1 in A1 2. Type =A1+1 in A2 3. Copy A2 to A3:A10 4. if you change a number, all the others that follow will be updated. If you want I can write a...

Excel: numbering timesheets for control, sequential number, control question

sequential number, control question, vlookup: The usual way to maintain an incrementing number is with VBA. Is this acceptable, and if so, what is the trigger that will tell the application to generate the next number. The concatenation is simple =payperiod&payperiod_week&seq_num assuming all...

Excel: numbering, heat transfer paper, serie numbers

heat transfer paper, serie numbers, preferrably: Hi Marc, I am thinking of using the ampersand sign & to connect text values such as 0000 with the numbers such as 1,2,etc.. and then to the text value -L or -R. But I need to know how high these numbers will likely go. Would you keep numbering them till...

Excel: numbers for words, cell a1, table of values

cell a1, table of values, broadsheet: You need a table of values - 2 cols with words in the first & corresponding value in 2nd. Say this is A1:B20 on sheet3 Then you need a formula next to the word. Say the word in sheet1 cell A1 is broadsheet. In B1 you need =VLOOKUP(A1,Sheet3!$A$1:$B$20,2,FALSE)...

Excel: None numeric 'Sums', list of names, sums

list of names, sums, cells: Chris: You are correct, COUNT() only counts numbers; however, there is another option - give the COUNTA() formula a shot. It counts anything that is not blank within a given range of cells. Please let me know if I may provide any additional information...

Excel: less than numeric value, h2

h2: Your formula: =H2 I2 is a statement saying the number in cell H2 is less than the number is I2. A statement is either TRUE or FALSE. You say you want the answer to be numeric, but you need to explain WHAT KIND of number. For instance, say you want...

Excel: Obelisk in memoriam of MS Excel authors?, easter eggs, simple graphics

easter eggs, simple graphics, obelisk: These hidden features are called easter eggs, and you can find many sites on the web that explain them - try www.eeggs.com - I ve not given you the specifics as you haven t specified the version of Excel, and I also thought you might well want to find OTHER...

Excel: ODBC, execel, query definition

execel, query definition, paste link: Unfortunately I know nothing about QuickBooks, so cannot answer this - IF the data was in Access, then copying the query definition (by right clicking on the name and copying it) and then using edit paste special - paste link would bring in a link to the query...

Excel: Odd Characters using =IF statements, source cells, mileage expense

source cells, mileage expense, hypen: apparently your cell isn t blank. if you format a cell using a custom format like: _(* #,##0_);_(* (#,##0);_(* - _);_(@_) then when it has zero in it, it displays a hyphen (as specified in the third criteria - criteria are separated by semicolons)....

Excel: Odd/even sort in Excel, text based adventure games, keyboard shortcut

text based adventure games, keyboard shortcut, down arrow: I cannot see my previous answer (at least not in a quick search of questions) but I ll do my best to clarify. Put a formula in the first cell, then EITHER click and hold the bottom left of the cell and drag down, or hold down shift and press the down arrow,...

Excel: OFFSET, cell b2, counta

cell b2, counta, additional number: Hi Val How are you? =offset(a2,0,1) refers to the cell B2, i.e. the 0 means that we refer to a cell in the same row and a column to the right =offset(d2,1,-1)refers to the cell C3, i.e. the 1 means that we refer to one row below and a column to the left...

Excel: Office 2007, asap utilities, installment payments

asap utilities, installment payments, thanks in advance: I cannot answer your questions definitively, however: 1. I have had NO problems opening older workbooks. Simply set your save type as 97-2003. (Office-- Excel Options) 2. I am sure that ASAP Utilities has an updated version: http://www.asap-utilities.com/...

Excel: Office Excel is Waiting for another Application to complete an OLE Action, excel vba, automation objects

excel vba, automation objects, recordset object: It looks like you have OLE objects in the origin recordset, and this method will fail in this case. From the help file: Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If...

Excel: Office Professional XP, office professional xp, methodist minister

office professional xp, methodist minister, registry entries: The software came from my father, and runs without being installed (at least, I think that s the case!) - it basically allows you to remove references to particular software from the registry - it s called JV16 powertools and I m not 100% sure that it s not...

Excel: Offset, command button, input box

command button, input box, default data: Doug, i put the numbers 1 to 256 in row 7 of the worksheet. Then in the immediate window I did: ? range( A7 ).Value 1 ? range( A7 ).Offset(0,55).Value 56 Note that offset(0,55) from column 1 is 56 - not 55. The base cell is considered zero,...

Excel: Offset, cell references, cell reference

cell references, cell reference, list of references: Jake, Maybe you don t need to reference a cell - I don t know what you know or don t know, so I will start with the basics first with no intention of offending you. Using absolute cell references will keep the base reference from changing as you drag the...

Excel: Offset and address combination, counta, indirect address

counta, indirect address, random letters: I m not certain this will answer your question, but it might. When I input the first formula you provided, Excel tells me the formula contains an error. The second formula seems to work (when I put in just some random sample date) and the third formula seems...

Excel: Offset & Expanding Range, tom hope, best guess

tom hope, best guess, ogilvy: =offset(A25,0,$F$2) put the value (25 in this case) in cell F2. Choose a cell that is appropriate. Update the single cell each week. Or perhaps you could replace the 25 with a formula. Something like: Subtract end of last year from the function...

Excel: Offset Formula in a Financial Report, cell c2, ax1

cell c2, ax1, ax 1: Francisco the best advice I can give is for you to ck out the help in excel and look up the offset function. you shouldn t be getting result you are getting in the formula you gave me. The offset formula returns a reference to a range of cells. And to...

Excel: Offset function, sheet1, excel 2000

sheet1, excel 2000: The offset function is certainly useful, but examples will rarely give you exactly what you need - however, the following MAY help =SUM(B1:(OFFSET(B1,A1,0))) This will add all values from B1 to B1 OFFSET by a number of rows, which is set in A1 - so leaving...

Excel: OK...Here's my query.I received..., excel spreadsheet, font style

excel spreadsheet, font style, formatting: I am not sure I understand your question. If you select any cell, you be able to tell the font style and formatting used for that cell. You use the toolbar which will show you the font style, etc. To copy the formatting to your spreadsheet, Use Copy...

Excel: OLE Action error, excel sheet, good answer

excel sheet, good answer, error message: Phi I havena t experienced the error message you are getting. I tried to create the message in various ways but I still couldn t create it. I am not sure why the message is coming up, but it sounds like the PDF file is busy, but I m certainly nto sure...

Excel: OLE Automation error, sql server environment, external data source

sql server environment, external data source, automation error: If you are in Excel, you do not need automation for the Excel objects (they are native), you need to setup a querytable to query the SQL server database (external data source). If you are in the SQL server environment using it s VBA, then you need to set...

Excel: OLE objects with VBA - worksheet as OLE objects, tool icon, column widths

tool icon, column widths, camera icon: If I understand correctly, you could use the Camera Tool to take a snapshot of your table then insert it into the report. Excel s Camera tool gives you the ability to select a range of cells and make a live picture that you can place anywhere in your workbook....

Excel: "Old" Excel macros, vba procedure, dear bill

vba procedure, dear bill, book1: Having old macros from EXCEL 97 does not mean they are written in XLM, the language from version 5 and below. They could be VBA. Since you did not say per se I was not sure. Do XLM macros written for previous versions of Excel work in Excel 97 and Excel...

Excel: "Old" macros, lotus 1 2 3, rvone

lotus 1 2 3, rvone, dear bill: All of the code you have included is Lotus 1-2-3 code that would have probably worked in the older versions of Excel Excel, especially when Lotus was much more of a competitor in the spreadsheet arena than it is now, has always given users the ability to...

Excel: Omit zero (0) cells, y2, k2

y2, k2, s2: Yes it will if I understand your poorly worded question that aks how to count the items from G2:Y2 that do not have a zero in them. The example I gave you says to count all cells from A1 to A100 that are greater than zero. Substitute your range for...

Excel: Omit zero cells, excel spreadsheet, y2

excel spreadsheet, y2, k2: Dan Try the following formula: =COUNTIF(G2, 0 )+COUNTIF(I2, 0 )+COUNTIF(K2, 0 )+COUNTIF(M2, 0 )+COUNTIF(O2, 0 )+COUNTIF(Q2, 0 )+COUNTIF(S2, 0 )+COUNTIF(U2, 0 )+COUNTIF(W2, 0 )+COUNTIF(Y2, 0 ) You can copy and paste this directly...

Excel: OnAction routine can't read commandbarsComboBox value, true style, window position

true style, window position, microsoft excel: Michael, I guess you could make those variables like SrcCombo as public and refer to them, but it is too easy for them to lose their assignments. The best is to use the caption of the combobox - and yes, the attribute is Text Public Sub DoLogin()...

Excel: Ongoing total in one cell, bearer of bad news, powertools

bearer of bad news, powertools, daily basis: Sorry, no - again, Access would contain a single value, and amending that value would update the record - I m not aware of any application that does what you want (that obviously doesn t mean it doesn t exist!!) - the only other method I can think of is to...

Excel: OnTime method, wilh, line application

wilh, line application, sorry for the inconvenience: Apparently you did not do what I suggested. When you run the macro that has as its only line: Application.OnTime TimeValue( 00:00:00 ), Day it will stay in memory keep checking the clock. At midnight the macro named Day will run. You have to keep...

Excel: OnTime VBA, open application, ogilvy

open application, ogilvy: Matt, The easiest solution is to Trap any error and ignore them On Error Resume Next code that cancels the ontime On Error goto 0 Just for info, here is a good reference for using OnTime and could be another approach you might want to use. ...

Excel: OnTime VBA, open application, cheers

open application, cheers: Hi Matt How are you? What about that? 1. Attach the following code to thisworkbook Public dTime As Date Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Save End Sub Private Sub Workbook_Open() Application.OnTime Now + TimeValue(...

Excel: OnTime, spreadsheet, intervals

spreadsheet, intervals, fires: (1) POSSIBLY the NextTick time is set in the past when this event fires? (2) could the Updateclock procedure check to see if the worksheet is open before doing anything? These are my current thoughts - I ve not used the OnTime method myself, so I cannot...

Excel: OPEING A FOLDER USING MACRO, venkat, new folder

venkat, new folder, ogilvy: You use of the word open for a folder isn t clear as to what you actually want to do. If you want to select a folder (rather than a file) by using the folder select dialog, then look at this example: http://www.j-walk.com/ss/excel/tips/tip29.htm Selecting...

Excel: Open File Dialog Box, test xls, myfolder

test xls, myfolder, string set: I ve looked into this but I don t think you can do this. I have written some code that asks for the file name & path then gives you the option to open the file(s) Option Explicit Sub searchFiles() Dim WB As Workbook Dim FileS As FileSearch...

Excel: Open Seperate windows, seperate windows, seperate window

seperate windows, seperate window, open folder: This feature was (I think) introduced in Office XP, and actually results in many people complaining about it - the wINDOW menu choice in Excel allows you to toggle between open files - there MAY be a setting in Excel 2000 that allows the files to appear seperately...

Excel: Openig Excel files, regserver, microsoft application

regserver, microsoft application, running office: Hi John, Try re-registering your Excel: 1 - Click the Start button 2 - Click on Run 3 - In the box, type this including the quotes, this may not be your exact path, use the browse buttton to find where Excel is on YOUR computer. C:Program FilesMicrosoftOfficeOfficeExcel.Exe...

Excel: Opening CSV Files in Excel, csv files, csv file

csv files, csv file, marketing campaign: Personally, I d load this into Access which wouldn t have the size issue, but IF you have to do it in Excel, you cannot get round the size limit so would have to use some Visual Basic to read the values - which could be set to create a new worksheet after...

Excel: Opening CSV Files in Excel, csv files, csv file

csv files, csv file, marketing campaign: Chris, Excel 2007 will handle 1 million lines per worksheet, but until then, you will have to put the data into multiple worksheets in a workbook. Have a look at some code at http://support.microsoft.com/kb/120596/ If you need help bringing the data...

Excel: Opening CSV files in Excel, csv files, csv file

csv files, csv file, telemarketing firm: Chris - With a database open in Access (blank or otherwise), go to the Files menu at the top of the screen, choose Get External Data, Import. The important step is in the Import window that pops up, at the bottom is a drop-down list Files of Type. Choose...

Excel: Opening CSV files in Excel, csv files, csv file

csv files, csv file, marketing campaign: Yes there is a way but I would suggest NOT using EXCEL. If you have 14,000,000 records the EXCEL file will be so large that your PC will crawl at a snail s pace. If you do the math you will see you will have approx 214 sheets in the EXCEL file you plan...

Excel: Opening My Excel Files, ms excel 2000, aitch

ms excel 2000, aitch, macros: Hi Andy How are you? The only reason for your problem I can think of is that the file you are trying to open is linked to another file that does not exist. Please, try the following 1st Open the file by pressing Cancel when the error message is displayed...

Excel: Opening Excel, two different places, excel spreadsheet

two different places, excel spreadsheet, left hand side: Hi Aida, Double check that the spreadsheet was not accidentally saved in two different places. To see if it was, when the spreadsheet is open (the one with data), click on File on the top menu, then Save As and then in the window that pops open, look at...

Excel: Opening excel files, microsoft office program, file excel

microsoft office program, file excel, good answer: Dennis I haven t run into this problem before, so I can t give you any specific help. I checked the help feature and cannot find a reference limiting the opening of the file, or the program, when you attempt to open the file. I suggest you check the properties...

Excel: Opening My file(s), douglas m smith, brainbench

douglas m smith, brainbench, mvp: Hi Andy, It s the file location that was the problem. If the file wasn t open but was still in the correct place, you would have received a message something like would you like to refresh the data from the linked workbooks? . Douglas M. Smith Brainbench...

Excel: Opening files, local disk c, excel worksheet

local disk c, excel worksheet, computer windows: Hi Nigel, You can change this by opening any excel worksheet and then go to the Excel menu and select Tools then Options then select the General tab and there is a spot to type in an address next to Default file location: type in C:Excel Documents...

Excel: Opening a PowerPoint Presentation, winword exe, powerpoint document

winword exe, powerpoint document, mydoc: I personally wouldn t SHELL an application from code for the most part as I generally want further control - I would TEND to create an object. This is possibly especially relevent with a powerpoint presentation as the default view for a powerpoint document...

Excel: Opening a PowerPoint Presentation, excel vba code, powerpoint application

excel vba code, powerpoint application, word application: I ve not had cause to open a powerpoint presentation - personally, I d probably go for the easy route and have a hyperlink - if the code needed to open the presentation it could then just follow the hyperlink. Otherwise, you would need to create an object...

Excel: Opening a Word Document, microsoft word document, document loading

microsoft word document, document loading, test doc: SInce you did not give me the code you wrote then it is REAL HARD for me to see what the problem is. You could record a macro that inserts a hyperlink in a cell that is behind the button. The hyperlink could open the word doc. Then this code could be assigned...

Excel: Opening .xls without Excel Software, excel software, question thanks

excel software, question thanks, adobe acrobat: Hi Marilyn, I believe this link would be useful to you: http://www.microsoft.com/downloads/details.aspx?FamilyID=4EB83149-91DA-4110-8595-4A960D3E1C7C&displaylang=en Please let me know if this does not answer your question. Thanks! Jessica S...

Excel: Opposite of Index Match?, seperate sheet, sheet names

seperate sheet, sheet names, employee names: Hello Deana, You can do it with a macro. Sub BuildList() Dim sh As Worksheet source Dim sh1 As Worksheet report Dim rw As Long Dim rng As Range, cell As Range Set sh = Worksheets( Data ) Set sh1 = Worksheets( Report ) Set rng = sh.Range(...

Excel: Opposite of Vlookup, lists of products, column c

lists of products, column c, opinon: Use the VLOOKUP with the IF statement. IF(VLOOKUP {list A} = VLOOKUP {list B}, [ available ], [else leave blank]) if you are familiar with the IF statement, then you got the solution. Actually the IF statement is oner of the reason why Excel is so powerful,...

Excel: Opposite of Vlookup, cell a1, lists of products

cell a1, lists of products, countif: Hi Moshe, Here is the formula: =IF(COUNTIF($B:$B,A1), Available ,A1) I believe this will help you. This foruma will scan all values in column B for the value in A1 and if the value is available in column b then it will write in column C as Available...

Excel: Opposite of Vlookup, coleagues, lists of products

coleagues, lists of products, column c: I understood what your need was but since I did not see a question in your post then, as I said, I did not want to assume anything. Had you asked a question then all of this could have been avoided. If you think I am patronizing you and/or being arrogant,...

Excel: Opposite of Vlookup, lists of products, cell c1

lists of products, cell c1, column c: Hi Moshe... Sorry for the delay in answering your question. You can solve your problem using a combination of IF and COUNTIF. Enter this formula in cell c1 and copy it to the rest of the cells in column c =IF(COUNTIF($B:$B,A1), available ,A1) ...

Excel: Option Boxes, microsoft excel 2002, option boxes

microsoft excel 2002, option boxes, option buttons: objects sit on top of the worksheet, so deleting a row will not delete the object. If you have many copies of the option buttons, it might be best to unoverlap them this way: 1 - create a new option button and put it WAY down the sheet, like in cell D200...

Excel: Option Button, group boxes, option choices

group boxes, option choices, option buttons: Hi Andrew How are you? The first you are missing is that you have to insert each group of option button in a Grame. First you design the userform Second Design the frames (If you do not see the frame control go to tools-references and enable Microsoft...

Excel: Option Buttons, group boxes, selection rectangle

group boxes, selection rectangle, option buttons: Once you create an option button you should right-click it and use the Control tab, and pick a cell for the linked cell . THis cell will be a number, corresponding to one of the option buttons. If you click the left one, for example, it might be a 1, and...

Excel: Option boxes, option boxes, true name

option boxes, true name, scrollbar: Hi Borko, I believe the problem with the optionbutton linkages is simply that you are using optionbuttons where it appears that what you really want are checkboxes. When you create a group of optionbuttons (grouping refers to the fact that they are linked...

Excel: Option buttons, option boxes, option buttons

option boxes, option buttons, example option: I suspect the issue with my explanation was the different versions of Excel (that and the fact I was being asked to do 5 other things at the same time as trying to answer your question!) You have used the CONTROL toolbar, and I suggested the forms toolbar...

Excel: Option buttons in a protected worksheet, dependent cells, conditional formatting

dependent cells, conditional formatting, dropdown boxes: Nigel, Not unless you wrote the VBA code to unhide them and attached it to the option buttons or used the change event. Since you don t appear to be using code, then I don t know whether this is an option for you or not. People generally are used...

Excel: Optionbutton problem, control toolbox, exact layout

control toolbox, exact layout, check boxes: YOu can get checkboxes, optionbuttons, and other controls from either the control toolbox toolbar or the forms toolbar. For control toolbox toolbar controls, when you click on the control toolbox and select your control, then go to the worksheet and click...

Excel: Output of the figures, dear sir, good health

dear sir, good health, hopeful: Hello, (Just thought I d mention that you sent this same question twice. You only need to click the Submit button once when sending a question. Since I need to answer every question that comes in, I ll mark the next one as a duplicate.) How did you arrive...

Excel: Overcoming the max number of nested IF statements in Excel 2003, max number, excel 97

max number, excel 97: Only by upgrading to Excel 2007 (64 levels of nesting). And there s nothing wrong with xl2003 s concatenate function. But, there is likely another solution aside from nested IF s -- if you care to post the formula you d LIKE to use (11-deep nested), perhaps...

Excel: Overdue dates, money recieved, a1

money recieved, a1, c1: =IF(A1 ,IF(B1= ,TODAY()-A1,B1-A1), ) This will handle all situations - Either or both cells being empty. This says that if A1 is not empty then do the calculation. If A1 is blank then display nothing. If A1 is not blank then look to see if B1 is...

Excel: Overtime caculating, spoon fed, lunch time

spoon fed, lunch time, overtime hours: Well, thanks for your feedback. We don t have to provide ready-made solutions if 90% of the answer is already provided. Sorry if your perception is that we volunteer our time to spoon-fed people solutions that already exist. Google is an excellent tool...

Excel: Overtime calculation, overtime calculation, spreed sheet

overtime calculation, spreed sheet, g26: Based on your formulas, you can use something like this to calculate running totals. For the current hours worked: =IF(((SUM(E$25:E26)-SUM(D$25:D26))+(SUM(G$25:G26)-SUM(F$25:F26))*24) 8*COUNT(E$25:E26),8*COUNT(E$25:E26),(SUM(E$25:E26)-SUM(D$25:D26))+(SUM(G$25:G26)-SUM(F$25:F26))*24)...

Excel: Overtime Formula, fee earners, overtime rate

fee earners, overtime rate, clock times: Good afternoon Jeremy! http://home.swbell.net/nate-sus/excel/times.xls The above link is a sample spreadsheet I prepared to help you adjust your current spreadsheet. It has all the formulas you will need (the above spreadsheet works in calculating overtime...

Excel: Overtime Forumla Continued, endtime, finish time

endtime, finish time, starting time: I met a similar problem and I went around it as follows: the idea is as follows IF(EndTime-StartTime 0,24+24*(EndTime-StartTime),24*(EndTime-StartTime)) EndTime is a Cell containing the ending time. StartTime is a cell containing the starting time....

Excel: Overtime formula, phone conversation, spreadsheet

phone conversation, spreadsheet, overtime: Hi, Steve. This is Chip s site index, which you might want to bookmark: http://www.cpearson.com/Excel/Topic.aspx This is his timesheet page. See the bottom of the timesheet page for a bunch of other time/date related links on his site: http://www.cpearson.com/Excel/overtime.htm...

Excel: Overtime, overtime hours, 8 22 2006

overtime hours, 8 22 2006, single day: Yup, the spreadsheet would need tweaking to change it to a 40 hour week instead of 80 hours. I have gone ahead and made those changes and uploaded the revised file to the same location: http://home.swbell.net/nate-sus/excel/overtime.xls The two new...

Excel: Overwrite existing file, returns error, target

returns error, target, excel 2000: If the file will always pre-exist, then the KILL statement on it s own will do this - format is simply Kill pathname where pathname is path/file name (blame Microsoft s help for this!) IF the file might NOT exist, surround the kill statement with...

Excel: object variable declaration, cell font, variable declaration

cell font, variable declaration, hth: the set in the 2nd procedure is OK, but dimming it as Range is better. Thiscell isn t changing in the loop in the 2nd proc. Since thiscell is A1:A5, you can t compare it to 10. You still need to use If Cell.Value 10. By changing thicCell.Font.Bold to...

Excel: # of ocurrances in a formula, xl2000, ogilvy

xl2000, ogilvy, len: Hello Ian You can do that with VBA. The formula isn t accessible from a built in worksheet formula. Sub ABC() Dim s As String s = ActiveCell.Formula MsgBox count of numbers is & _ Len(s) - Len(Replace(s, + , )) + 1 End Sub Replace was...

Excel: office 97 and windows xp, eccetera, office 97

eccetera, office 97, excel 97: Hi Matt, How are you? The only thing I can think of is that your file is Read only So you have to select the file from thr folder where it resides, right-clicked the mouse and remove the tick from the read-only attribute ... Yet, your problem could...

Excel: office update, installation disc, original media

installation disc, original media, software piracy: There should be a cancellation option on the install, but basically if you haven t got the original media then I think you are pretty much stuck - re-installation may be the only option, and the reason for asking for the disks is to stop software piracy, so...

Excel: offset to an absolute column, pastes, two rows

pastes, two rows, syntax: I don t understand what you are asking? Are you having problems with the cutting and pasting or the counta? What exactly are you trying to do? To get to the cell two rows above the active cell then ActiveCell.Offset(-2, 0) If the active cell is not...

Excel: offset,sum,match, heat units, b50

heat units, b50, c50: Say your table is from A1:B50 where A1 & B1 contain headings in F1, say, enter your planting date in F2 enter your specific value. You need formulas in C2:C50: In C2 enter =IF(A2 $F$1,0,C1+B2) and fill down to C50. Your answer date s formula would be...

Excel: offset , sumproduct and names, cell a2, array formula

cell a2, array formula, contextures: The SUMPRODUCT formula, even if it is not an array formula, uses arrays as its arguments. This means that it is not immediate how to split the formula, as you will be working with separate arrays, and they are handled differently. If you can tell me what...

Excel: onclick of a cell in excel to run a lookup, chip pearson, target range

chip pearson, target range, blank cell: It s called a worksheet change event. Here s Chip Pearson s change event to change entered data to caps: Copied directly from Chip s site at http://www.cpearson.com/excel/case.htm __________________________________ Changing Case On Data Entry In...

Excel: How to get only one workbook from a multi-workbook spreadsheet into an e-mail as an attachment, column headings, source document

column headings, source document, copy paste: Rebecca Sounds like the easiest way would be for you to create a new worksheet for e-mailing, but do it by using copy paste special and copy the values and the formats only. (Toolbar/edit/copy, then to paste, toolbar/edit/paste special then select values...

Excel: opeing an "xls" file forces and Excel 2003 install, excel exe, microsoft update

excel exe, microsoft update, xls file: Search your hard drive for Excel.exe. When you find it, create a shortcut for it on the desktop. Then right/click the shortcut & select Properties . There s a Target edit box. Add this to the end of it (it starts with a leading space): /regservr So, for...

Excel: open a file in network, array array, cell c2

array array, cell c2, instance c: Zulfatta Sorry, I m not a macro expert-- It looks to me as though your path should work, but obviously we are missing something. Try asking another allexperts person and I m sure you can get a good answer. Sorry I couldn t be of help. Richar...

Excel: How to open a file use the text in one cell of a excel sheet, fileexists, filesystemobject

fileexists, filesystemobject, excel sheet: .... u need a reference to Microsoft Scripting Runtime. ========================================================================================= Sub OpenFileLink() =========================================================================================...

Excel: can not open worksheet, excel exe, power failure

excel exe, power failure, excel worksheet: in Excel go into Tools= Options and make sure Ignore Other applications isn t checked under the General tab if it isn t checked, then with Excel closed, go to the windows start menu and select Run. In the textbox put excel.exe /regserver Then click...

Excel: can not open worksheet, explantion, power failure

explantion, power failure, excel worksheet: I have searched the knowledge base and cannot find anything to explain this, although I m sure that there MUST be an explantion - do you get any error messages when trying to open the file from My Documents - if so, the error message may shed some light on...

Excel: can not open worksheet, power failure, excel worksheet

power failure, excel worksheet, heather: Heather I ve not experienced that problem before nor have I had any questions regarding the problem. I don t know of anything different in excel that would not allow you to open the files from My Documents. Sorry I can t be much help on this one. ...

Excel: opening excel files in LAN, exact error message, office 2000 service pack

exact error message, office 2000 service pack, excel 2000: Hey, Can you give me the exact error message you are getting? If it is the one I am thinking of, you will probably need to obtain the latest office 2000 service pack. This link describes the process to obtain the service pack under the Resolution ...

Excel: opening multiple excel workbooks at once, open dialog, excel workbooks

open dialog, excel workbooks, ctrl key: You didn t specify if it s manually or by VBA code. Manually, you can hold the ctrl key down and select all the files you want to open in the file/open dialog. If they re always the same set of files, then while they re all open you can use File/Save Workspace....

Excel: opening spresdsheets, spreadsheet work, many thanks

spreadsheet work, many thanks, a5: Philip The workbook will open wherever it was last saved. I.E. if it was saved on sheet 1, with cell A5 being the active cell, then that is where it will open. I don t know of a way to have the find box appear automatically on opening the worksheet....

Excel: "What if" operation, douglas m smith, spreadsheet model

douglas m smith, spreadsheet model, scenario details: Hi Nisa, A what if calculation involves altering one or more inputs to see what the changes are. In a salary increase proposal, the main input to vary would likely be the raise percentage. The result would be based on adding the raise percentage to...

Excel: "What if" operation, spreadsheet model, scenario details

spreadsheet model, scenario details, scenario name: I m not sure how in-depth a solution you want, but using the Goal Seek menu choice could be what you want =-= alternatively, if the sheet is designed with the right formulas, changing values manually would do it - as the formulas would recalculate. The...

Excel: "What if" operation, spreadsheet model, scenario details

spreadsheet model, scenario details, salary increase: Nisa Check out the help on What-if analysis . Your question poses a lot of different potential questions. I think most of them can be answered in the help area. If you have a specific question after checking this out lemme know. Hope this helps....

Excel: option buttons, combo boxes, option buttons

combo boxes, option buttons, combo box: I m not sure I totally understand your question. You can t really have two combo boxes per cell. You can have one combo box per cell, each with a list of options, and you can visually make them appear as one with a simple border, but the dropdown arrows...

Excel: how to put an option within a list box, dim myitem, datasheet

dim myitem, datasheet, msgbox: An item in a list is either selected or not; there s not an option to select an item and have another choice without perhaps having the item in twice: once with yes and another wieh no (or paid / not paid ) Another possibility is that when an item is...

Excel: other cell to display calculation related to current cell, target target, elementary school classroom

target target, elementary school classroom, visual basic for applications: If I understand correctly, you want a cell that displays a result dependent on which cell has most recently been changed. The only way to do this is with vba (Visual Basic for Applications) code. Here s a simple example. In range B2:E2, put the number of...

Excel: outlook to excel, dim arr, excel spread sheet

dim arr, excel spread sheet, split function: Fair enough. You can use the Split function in VBA that splits the text into an array based on the delimiter of your choice (default is the space . Dim arr as Variant Dim i As Long arr = Split(olMail.Subject, ) For i = 0 To UBound(arr) ...

Excel: overriding a formula, minimum quantity, frame size

minimum quantity, frame size, h7: Well that s different from what you just said. A minimum of one is different from getting a separate value for 1 or getting another separate value for 2 or getting a still separate value for 3, all when less than 10. This sounds like your table just needs...

Excel: overtime trickiness, negative numbers, time sheets

negative numbers, time sheets, valu: Kim: First off...I don t think I could handle your hours :D. Second...can you check out this spreadsheet and let me know if any of it works for you? http://home.swbell.net/nate-sus/excel/paramedic.xls I put in a bunch of different calculations...

Excel: PAge numbering, page thanks, page numbers

page thanks, page numbers, footer: I am not aware of any means of doing this within Excel directly - Word will let you do it, so converting the project to Word could be any option - it may be that someone else DOES know how to do it however (the only method I could think of would be a little...

Excel: Page Numbering, page prints, page numbers

page prints, page numbers, macros: As politely as possible it would have been helpful had you told me that in the beginning so as to avoid all these extra posts and to help understand what you really were trying to ask and what you then needed. But, to answer your question, no. Sorry, and...

Excel: Page Setup Defaults, setup defaults, setup settings

setup defaults, setup settings, time printing: Hi Ian How are you? It seemed that it would be possible to change the page set up in the normal style ... but Format - style do not allow you to do that. Then, I tried the printer options in file - Page setup define a new page setup and pressing options,...

Excel: Page subtotals, sum formula, page breaks

sum formula, page breaks, page break: Emmar The only way I can imagine this being possible is with VBA code, but I have never tried it, and don t have the time to develop something like that. If you use the page break preview mode from the View menu, it will display where the page breaks...

Excel: Page subtotals, sum formula, pagebreak

sum formula, pagebreak, page breaks: Emmar Sadly there is no answer for this question that I have ever come across (going back to 97). The main reason for this is that excel can t think and act as to where the page break will be, ie if you use a different size font and change the page it...

Excel: Page view, excel spreadsheets, questios

excel spreadsheets, questios, ms xp: There is no way that I am aware of to hide the Excel logo, etc. upon startup of Excel. However, this could be accomplished using Access, which can also be programed to build/manipulate Excel spreadsheets. Robert ===========================================================...

Excel: Parsing name fields, array function, len

array function, len: Aha, I have just the thing for you. Had a similar issue a while back and found this code on the net somewhere. First Name - Easy - Use the Left function Last Name - =RIGHT(A1,LEN(A1)-MAX(ROW(INDIRECT( 1: &LEN(A1))) *(MID(A1,ROW(INDIRECT( 1:...

Excel: Parsing a text string, douglas m smith, format cells

douglas m smith, format cells, brainbench: Hi Ted, I am unable to reproduce the carriage issue you described. The problem might be your cell formatting. If A4 is set to wrap text (Format / Cells / Alignment / Wrap Text checked) and the The Kinks is too wide to fit in the cell, then it would...

Excel: Parts prices, cell b3, blank cell

cell b3, blank cell, table column: You need to create a lookup table: Column 1: product name Column 2: price You should select the list of product names, and create a RANGE name for them (if you don t know how to do this, look up NAMING RANGES in the Help file for the steps - it s pretty...

Excel: Passing data from one sheet to another, cell position, column c

cell position, column c, 123456: Well, if you are not a programmer or anything close then I might not be able to help. Typically I don t write code for people because there are always way too many back and forth emails to iron out SPECIFIC DETAILS. But here I made an exception and provide...

Excel: Passsing arrays between functions, gauss jordan, matrix algebra

gauss jordan, matrix algebra, exact problem: I think you misread my question. I d like to know how you call the function ReceivingArray : from a worksheet cell(s), from another sub, from a function,... Also it would be nice to have some sample data: what are you passing to the functions exactly...

Excel: Password, cracking software, word files

cracking software, word files, service names: Hello Haytham, I m sorry to hear that you need this type of service. I haven t had any personal experience with them, however I have heard others in the newsgroups mention various services. The impression I got was that they were fairly expensive. You...

Excel: Password, word files, good luck

word files, good luck, viruses: I don t have any recommendation. I have only need to do this once, and when I did I searched Kazaa (www.kazaa.com) using the keywords word password , found some freeware, downloaded it, and then never used it again. Maybe the same will work for you, but...

Excel: Password, office password recovery, password cracks

office password recovery, password cracks, advanced office password recovery: Haytham The ones I use for Excel are Advanced Excel Password recovery and Accent password recovery, for Word Advanced Office Password Recovery (although I haven t tried it with Word). Depending on your version of Office you may want to try typing in google...

Excel: Password, alphanumeric strings, dictionary words

alphanumeric strings, dictionary words, word files: Hi Haytham, The only cracker I have personally used is one at LostPassword.com, and it did the job very quickly. But I noticed that their prices have increased and their free trial is now limited to 2-character passwords. If you are quick (I hope you...

Excel: Password, query ability, pivot table

query ability, pivot table, souce: Daryl I m not real expert at this since I seldom use passwords, but I tried to recreate your situation using a couple of password protected workbooks and linkin them to my worksheet. I found that when I tried to activate the formula linked to the closed...

Excel: Password Protect Individual Sheets, excel passwords, excel workbook

excel passwords, excel workbook, ogilvy: JC, No, there is no built in support for that. If you hide sheets, then you can keep them from being unhidden by protecting the workbook (structure option). However, this is all or nothing. Also, this password, like almost all excel passwords, can be easily...

Excel: Password Protect Individual Sheets, keystroke combination, excel workbook

keystroke combination, excel workbook, confidential data: Personally, I would probably do it with two files - the confidential data on one, the rest on another - but yes you can do it, although it will need VBA In VBA something like this will do it for you Sub qkkd() ActiveWorkbook.Sheets( sheet1 ).Visible...

Excel: Password Protect Individual Sheets, sheet tab, password users

sheet tab, password users, inputbox: you need VBA. Right-click on each sheet tab, select View Code, enter a variation of this: Private Sub Worksheet_Activate() ActiveWindow.WindowState = xlMinimized If InputBox( Enter Password for this sheet ) ABC Then Sheets( OkToSeeThisOne ).Activate...

Excel: Password Protect Individual Sheets, case statements, true case

case statements, true case, excel workbook: Yes it is possible beause you can password individual sheets but I would suggest the following. Hide all but one blank generic sheet. Use that sheet as a background to prompt the user to enter his password. One set of passwords unhides one set of sheets...

Excel: Password Protection, excel sheet, wordpad

excel sheet, wordpad, aidan: There are programs that will crack the password - so if you can send it to me I can try and use one of mine on it (aidan.heritage@virgin.net) - however, before doing that, try opening the sheet in notepad or wordpad IF the sheet has macros - if so, the password...

Excel: Password protection, strange behavior, strong point

strange behavior, strong point, ogilvy: Sheets( Chas ).Select Activesheet.Unprotect Password:= ABC Columns( H:H ).ColumnWidth = 4.43 Columns( J:J ).ColumnWidth = 4.43 Activesheet.Protect Password:= ABC Sheets( Gaynor ).Select Activesheet.Unprotect Password:= ABC Columns(...

Excel: Password protection, uk insurance company, password thanks

uk insurance company, password thanks, file extension: Actually, you didn t need to change the extension as you could load it directly into Notepad, but it wouldn t help as the password is encrypted - BUT if you can email me the file (aidan.heritage@virgin.net) I can try to run it through a program I purchased...

Excel: Password protection, sheet1, cols

sheet1, cols, password protection: You could have a macro to do this, which would just prompt for the password (safer than having a password embedded in the macro!) - you can record this macro - BUT my thoughts are that you might not need to do this as you could simply have a formula in cols...

Excel: Password, word files, google

word files, google, afl: I don t know about best - they all do about the same. I guess it s a matter of price. Here are a few. Type Excel Password into Google to get a full list. http://www.lostpassword.com/ http://www.freewordexcelpassword.com/ http://www.password-studio.com/unlock/?afl=6121...

Excel: Passwords in Macros, password test, command button

password test, command button, macro recorder: Let me see if I understand. You only want the user to be able to make changes to workbok when he runs your macro, correct? And when he runs the macro it wil then unprotect the workbook, correct? If so, then you will still need to password protect the...

Excel: Passwords, excel 2002, single file

excel 2002, single file, sp3: Darryl, The following line of code will open a single file that is password protected. To look through a number of files, you would have to do a for next or for each loop that would cycle through each file in a directory. Yes a table of passwords would...

Excel: Past dates to track tasks in Excel, conditional formatting, tom hope

conditional formatting, tom hope, dear tom: Trudy, Yes, you can do that with conditional formatting. It is found under the Format menu in xl2003 and earlier (I don t have xl2007 installed - so can t help with that). This site has extensive information on Conditional Formatting: http://www.contextures.com/tiptech.html...

Excel: Paste into a doc I keep getting RE#, copy word, correct string

copy word, correct string, column c: I don t know where RE# comes from because that is not a valid error message for a formula IF you are getting an error. Even if you don t have data in just one or neither of the cells referenced in the the formula then EXCEL will not display an error. There...

Excel: Paste into a doc I keep getting RE#, copy word, correct string

copy word, correct string, column c: question isn t clear, but you shouldn t need to paste into word and back again - but clearly with a REF error something is getting muddled - could I see a sample file - OR is it simply that you are trying to get the VALUES of column C somewhere - if so, edit...

Excel: Paste an Excel Range into Word, word application, working solution

word application, working solution, image sizes: As you have tried this and failed, I had a brief look and confirmed that what you said would happen, and that I cannot find any alternative way round it USING THIS SOLUTION - BUT if instead of Documents.Add (default) you used a template, and if that template...

Excel: Paste the formular in difference columns, contiguous selections, mouse pointer

contiguous selections, mouse pointer, column c: Hi Irfan, Unfortunately, Excel will not let you copy non-contiguous selections into non-contiguous results. Have you used autofill before? Click on C2 so that it is the active cell, then move your mouse pointer to the lower right corner of that cell until...

Excel: Paste Link / Paste Special, memory thanks, paste link

memory thanks, paste link, company divisions: Alan If you are just getting totals the formula in the totals sheet would look something like this =Sheet2!A8+Sheet3!A15+etc etc This can be done by typing = in the totals sheet and then moving to the corresponding totals cell on each sheet (don...

Excel: Paste Link??, text import wizard, using ms office

text import wizard, using ms office, file conversion: I can t offer much unless you get very specific about exactly what information you want to transfer from Word to Excel. So I ll offer a simple example of this type of transfer, which may be useful. If not, you can re-ask, giving a very simple, but precise...

Excel: Paste into merged cell, word document, data entry

word document, data entry, manipulation: I find merged cells very inconvenient, and try to avoid them except for the presentation of data; they are a pain for manipulation, formulas and data entry. Having said that, you cannot paste directly from Word in the cell, but you can paste directly in the...

Excel: Paste Special, system bottom, date system

system bottom, date system, selection window: Clare Check the new file you are copying to and see if you are useing the same date system. Go to toolbar/tools/options/calculation see if the 1904 date system (bottom left of selection window) is checked or unchecked in both files. Another way to...

Excel: Paste Special automatically, cell a1, ken ken

cell a1, ken ken, month of the year: Hi Bob, Yeah sorry about that, I mentioned sending me a file and didn t give you an address. You can send a copy if you like to www.cloverken@yahoo.com I recieved this code from Jacob at www.mrexcel.com which he was kind enough to give me. You would...

Excel: Paste Special - Transpose, douglas m smith, address city state

douglas m smith, address city state, blank row: Hi Christine, Let s say your data starts in cell A1. Put the following formulas in the designated cells: C1: =IF(ISBLANK(A4),ROW(A4), ) D1: =A1 E1: =A2 F1: =A3 The C1 formula is going to mark the transposed rows we want to keep. Now...

Excel: PasteSpecial Macro, check values, pastes

check values, pastes, best wishes: Royston, I think you would be better served by learning how to record the code. Just copy what you want to paste, then select the cell you want to paste them into, then go to Tools/Macro/Record New Macro, and press OK to start recording. Then press Edit/Paste...

Excel: Pasting Data, cell reference, worng

cell reference, worng, customer number: There are some points that you can review in the formula: - The name of the sheet is the correct one (Second here is an example) - The column with the IDs is the right one ($A:$A was the example here) - The cell reference that you are using is relative...

Excel: Pasting into excel => Easy one (I think), douglas m smith, compatible desktop

douglas m smith, compatible desktop, brainbench: Hi Pat, This sounds like the work of the Text To Columns feature. Find an out of the way cell and enter a single character. Select that cell and the select Text To Columns from the Data menu. In the dialog box, select Delimited and click the Next...

Excel: Pasting into hidden cells, douglas m smith, hidden cells

douglas m smith, hidden cells, brainbench: Hi Crystal, I think you are pretty much stuck with that behavior when pasting to a filtered sheet. You might try pasting the filtered data out to a third sheet and then pasting the sheet(2) data there too. I think Excel 97 would paste just the visible...

Excel: Pasting information to a new worksheet, foul swoop, using microsoft office

foul swoop, using microsoft office, screen dump: I need more info - are all occurrances of FIRE.4 contained in the same column? If not, how is it organized? Feel free to send me a sample workbook at bobumlas@yahoo.com and repeat what you re trying to do (I get lots of email!) And it s one fell swoop ,...

Excel: Pasting, sheet names, mouse pointer

sheet names, mouse pointer, ctrl key: Hi Capeverde, You can copy sheets to another workbook. Open the workbook that you want to copy. Open the other workbook you want to copy to. Select the workbook and worksheet you want to copy, if you want multiple sheets hold the Ctrl key down while...

Excel: Payback period, nper, present value

nper, present value, vba: Mona The best explanation I can give you is to tell you to look up NPER in the help section. It will give you a good explanation with examples. Note that you need to put in a minus for the present value and the payment amounts to get it to calculate the...

Excel: Payroll Timesheet, payroll timesheet, value of time

payroll timesheet, value of time, decimal format: Linda To do this you will have to create another column where you can apply functions or formulas to the existing column. You could change the default value of time in the Control panel but that will then apply to all programs in Windows. If you have...

Excel: Payroll, annual salary, dalgleish

annual salary, dalgleish, pivot tables: Using a pivot table under the Data menu. Use Department as the row field and salary in the data Field. for more information on Pivot Tables look under P on Debra Dalgleish s site: http://www.contextures.com/tiptech.html -- Regards, Tom Ogilvy...

Excel: Percentage Formatting, decimal places, integers

decimal places, integers, zeros: Jim Of course the reason that .22 comes out 22% when you enter it is because .22 is in fact twenty two percent not .22 percent, excel just converts the fraction that you enter into a percent. So to get around that you can use a formula that will divide...

Excel: Percentages Report, cumulative change, trendlines

cumulative change, trendlines, percentage change: Hi With a sequence of cells showing monthly change (I will assume they are in a column), you could have an adjacent column showing change since Jan. Assume change data for this year is in cells A1-A10. Put this equation in B2: =IF($A2 ,SUM($A$1:$A2),...

Excel: Percentile analysis of salary data, salary data, questios

salary data, questios, personal emergency: If you want, send me a sample of the data with a sample of how you want the output and I ll see what I can do. However, as you will see below, I won t be available for the next several days to return an answer. Robert ===========================================================...

Excel: Perentages, excel expert, column c

excel expert, column c, cumulative total: Hi Brian, This is a series of IF THEN statements that will be made easier if you add a column (say column A) that has a cumulative total of your tax table. 1250, 2500, 3750..10,000 next to the 14% line. You do not need one for 15%. If the tax % are column...

Excel: Permanent Formatting, conditional formatting, format tables

conditional formatting, format tables, menu choice: I THINK you will find that this has been done with the use of CONDITIONAL FORMATTING - which you can check by looking at the format menu, then conditional formatting - this is certainly the way I would do this, and I would expect that it would use the ROW...

Excel: Personal Macro Workbook, office microsoft, macros

office microsoft, macros, placeholder: The Personal Macro Workbook is usually used as a placeholder for all your macros, so you can reuse them in different workbooks. In order to set it up, you can check the instructions given in the following page: http://office.microsoft.com/en-us/excel/HA010872961033.aspx...

Excel: Personal Macro Workbook, xls

xls: EXCEL will prompt you when you close it IF there have been any changes to the Personal.xls workbook, even when it is created for the first time. If you chose NO, especially IF the Personal.xls workbook had been created for the first time, then the macros...

Excel: Personal VBAProject, aidan, project explorer window

aidan, project explorer window: If the project explorer window isn t open you won t be able to see the personal.xls worksheet, so I d check this first - ctrl R or view menu, project explorer. This assumes you do have the personal.xls loaded which I m taking to be the case based on the information...

Excel: Personal.xls, question thanks, c program

question thanks, c program, microsoft office: Unhide the personal.xls, and create a new toolbar which you can attach to the personal.xls (Tools/Customize/Toolbars tab, Attach button). Put any buttons on this toolbar and assign it to run the macro in the personal.xls. To hide the personal.xls (windows/hide)...

Excel: Personal.xls, xls file, excel 2000

xls file, excel 2000: Not sure what your looking for here. What is the intent of the File? ie what should a personal.xls file do? I m not familiar with the terminology you re using. Can you give me more information? Looked through my books and did some online searches couldn...

Excel: Personnel Scheduling, cell a1, excel spread sheet

cell a1, excel spread sheet, military time: If, for instance, you use col A for the start time and col B for the end time, you might have 08:54 in cell A1 and 17:12 in cell B1. (Use Format, Cell, Number, Time if you want to format the time differently.) Then you could put in cell C1 =B1-A1 and it...

Excel: Phonebook, ascii code, sorry for the confusion

ascii code, sorry for the confusion, case version: Sorry for the confusion - in my original reply I gave you a method of selecting a sheet when a cell is changed. I then revised this method to pick up the appropriate sheet - ASC is a command that returns the ASCII code of the letter - a is 97, b is 98 etc...

Excel: Photo in Excel, out of sight, cells

out of sight, cells, yahoo: send me your email address & I ll send you a workbook which does something like you want. One trick is to have all the photos in the same workbook, out of sight (like column Z) and each taking the exact same # of rows. email me at bobumlas@yahoo.com and...

Excel: PIVOT, spreadsheet example, row labels

spreadsheet example, row labels, pivot table: Gigi: I presume your pivot table only shows the 07 totals. Otherwise, doing the below is going to cause the 06 totals to also vanish because the 07 total is zero. 1) Create your pivot table as you have already done. I presume you have row labels on...

Excel: Pick a cell that is next to answer, array formula, bxx

array formula, bxx, dow jones: Hello Jamie, You can do this by using an array formula =Max(if((Month($A$1:$A$199)=1)*(Year($A$1:$A$199)=2006),$B$1:$B$199)) Enter this with Ctrl+Shift+Enter rather than just Enter since this is an array formula. To get the corresponding date ...

Excel: Pick from lists, douglas m smith, calender dates

douglas m smith, calender dates, data validation: Hi Tim, You can do this with Data Validation. If the list of countries in on the same worksheet, you can use the cell range. If the list is on another sheet, you will need to assign the list a range name, like Countries . Select the cell(s) you want...

Excel: Picking random numbers from a worksheet, microsoft excel 2002, random pairs

microsoft excel 2002, random pairs, random row: If it were me... I d pick four random pairs of numbers and use them as the row/column offset in an OFFSET function. Each of the 4 pairs should have a random row and random column number. You have 10 columns. You ll need 4 random numbers between 1 and...

Excel: Pictures in Excel, playground system, grid location

playground system, grid location, cell dimensions: Luke, To the best of my knowledge, pictures inserted as you describe, don t have a handle - at least that is not the way one usually works with them. The do have a name (seen in the formatting toolbar in the upper left when you select the picture) and they...

Excel: Pictures in Excel, cell changes, mark mitchell

cell changes, mark mitchell, onchange: Mark, You need to place this code in the project for the Sheet, not in a Module. This has to be specific for each sheet you want it to work on. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Set VRange = Range( InputRange ) For...

Excel: Pivot Chart, location names, independent party

location names, independent party, company names: You don t have a question here -- you re asking my opinion on what would look good (I think). I have no idea what you WANT this to look like. Perhaps if you send me a sample workbook, and repeat the question, I can help better. Formatting a chart or pivot...

Excel: Pivot query, mocha latte, data manipulation

mocha latte, data manipulation, store c: Chris, I don t think a pivot table is going to work without some data manipulation. If the layout is always the same, I could probably write a macro to do it. If that would help, you can send me a sample file to twogilvy@msn.com -- Regards,...

Excel: Pivot Table automation, vba code, pivot table

vba code, pivot table, msgbox: Like this (remember to update your PivotTable name and the field name in this example): Sub FilterPivotTable() Dim oPi As PivotItem Dim sPiName As String sPiName = InputBox( Please give Pf name to show , 1) If sPiName = Then Exit...

Excel: Pivot Table Copy Paste and Borders, left hand corner, time andrea

left hand corner, time andrea, excel query: If you don t specify what you want to print, it prints down to the last cell. You can find that location by doing Edit= Goto= Special, then selecting Last Cell and OK. In my tests in xl2003, just selecting the upper left corner of the headers (to...

Excel: Pivot Table Data Entry, pivot table, revenue accounts

pivot table, revenue accounts, gilda: Hi Gilda, It sounds as if your pivot table is counting revenue accounts and you would like them to sum. I would check in the top left corner of the pivot table. If is reads Count of Revenue then you would need to double-click this and change it...

Excel: Pivot Table Funkiness, douglas m smith, pivot table

douglas m smith, pivot table, brainbench: Hi Linda, I don t work with pivot tables much but I suspect it s a data issue, with mal-sorting dates actually being in text format instead of date format. That would explain why sorting doesn t help. Check the cell format, it might be Text. Change...

Excel: Pivot Table - Multiple Data Sheets, pivot table, column titles

pivot table, column titles, analysing data: If the data is numeric, then a pivot table will allow you to SUM it - but if it is alpha, the only thing a pivot table can do is count it - it SOUNDS as though you have gone through the correct process, and the result sounds as though Excel has done it s best...

Excel: Pivot Table - Multiple Sheets, manpower costs, table wizard

manpower costs, table wizard, pivot table: Hmm...I use that particular type of Pivot Table to merge multiple lists for myself and although it does look different, I can usually still get the data I need (albeit the options are a little different). Can you send me an example of what you would like...

Excel: Pivot Table - Problems when using Calculated Item, apples and oranges, ms queen

apples and oranges, ms queen, pivot table: Good morning Sandi! Please review this spreadsheet: http://home.swbell.net/nate-sus/excel/maritimes.xls I believe I understand your problem, but I m not sure what is causing it. As you can see from my example, Tom, King, and Queen do not show up in...

Excel: Pivot Table problems, pivot table, a2

pivot table, a2, cheers: Hi Grace, I think it is better to try to explain it with a small example. E.g. Database A1: type the heading of this column B1: the same A2:B6 type some date I am sorry but I do not know if we are allowed to give out our email addreses. Cheers...

Excel: Pivot Table Question, table toolbar, incorrect spelling

table toolbar, incorrect spelling, pivot table: I can t replicate your first issue -- works OK for me -- feel free to email me a sample at bobumlas@yahoo.com 2nd issue -- try clicking on the refresh Data toolbar button on the pivot table toolbar (or right-click anywhere in the PT & select Refresh Data...

Excel: Pivot Table Source, format cells, table wizard

format cells, table wizard, two screens: There is a lot of info here...please let me know if you have any problems implementing any of it: Q1) Yes, you can: 1) Click on the pivot table wizard. 2) The third option is “Multiple Consolidation Ranges” 3) After selecting the above option and advancing...

Excel: Pivot Table, pivot table, pivot tables

pivot table, pivot tables, spread sheet: Comparision - as you say, you will run into problems with the pivot table method, but your comparision could be done by creating a comparision worksheet (for example) which has a simple formula on each of the equivalent cells - I m ASSUMING that the rows and...

Excel: Pivot Tables, pivot tables, pivot table

pivot tables, pivot table, b2: Randy, I am assuming that you are using the data after creating the pivot table and copy-pasteSpecial the data in some other location. there is no direct way to achieve this. The way I have done it in the past is to create a formula. if the company is...

Excel: Pivot Tables - Calc % using Sub-Total, not Grand Total as base, ms excel 2000, field settings

ms excel 2000, field settings, pivot tables: Doesn t seem like it s possible in a builtin way - you COULD copy/paste the resulting pivot tables from the Show Pages option one below the other...? Or you may need to make it all a non-pivot table & use VBA to compile the pieces. I think the first suggestion...

Excel: Pivot Tables - Calc % using Sub-Total, not Grand Total as base, ms excel 2000, field settings

ms excel 2000, field settings, pivot tables: I don t think it s do-able as a pivot table, except by doing it as you suggest. YOU COULD however do it by formulas, though you would need to know how many results you were going to need to return - the only problem with this is that the array formula necessary...

Excel: Pivot Tables - Calc % using Sub-Total, not Grand Total as base, ms excel 2000, field settings

ms excel 2000, field settings, pivot tables: Sandi I m definatley not a pivot table expert. I can t see a way to accomplish what you want to do, however that is a very uneducated answer. I suggest you reask the question of another and I m sure you can get a better answer. You might also consider...

Excel: Pivot Tables with data greater than max lines in worksheet, table wizard, pivot tables

table wizard, pivot tables, odd error: Good news, there is a way to use multiple columns (or spreadsheets) of data! I m going to truncate the steps a little since you are already familiar with Pivot Tables. 1) Open the Pivot Table wizard. 2) Put a dot in the option that says Multiple Consolidation...

Excel: Pivot Tables with Excel and Visual Basic, visual basic aplication, john walkenbach

visual basic aplication, john walkenbach, visual basic 6: Tomas, I believe that Visual Basic has the ability to control Excel, and that makes it possible to do what you are asking. However, it seems to me that it would be far easier to use VBA inside of Excel to do the same thing. I would recommend using the...

Excel: Pivot Tables Fiscal yr vs Calendar yr sorting, pivot tables, fiscal yr

pivot tables, fiscal yr, pivot table: Hi Karla, Pivot tables are my favorite. Two ways to make this work. One is that you can manually slide the items to the order that you want, provided your month field in the pivot table is set on a manual sorting order, not automatic. The other is that...

Excel: Pivot Tables - show/hide details, query definition, query in access

query definition, query in access, pivot table: It is because your data is external that Excel cannot manipulate it - you would therefore need to get the data into excel from Access - you could copy and paste if the data doesn t need refreshing - you could also try copying a query definition (i.e. view...

Excel: Pivot Tables - show/hide details, pivot table, source option

pivot table, source option, table options: Al This is by design from memory, but I haven t used Access in a long while, you can t drill down because of the structure that Access uses, one option it to import the access file as a spreadsheet and then use that for your pivot table. It adds a few more...

Excel: Pivot Tables, windows xp sp2, data columns

windows xp sp2, data columns, pivot table: KS, Try this. Right click anywhere in the date field and select Group and Show Detail. From there, select Group. From there set Starting At to 01.04.2002 and Ending At to 30.06.2005. You may group by day, week, month etc. from this menu item as...

Excel: Pivot table, blank cell, pivot table

blank cell, pivot table, formatting tools: You are asking about my #1 complaint of Pivot Tables :). There is no way, with the pivot table itself, to do what you want (not without defeating the purpose of summarizing data in the Pivot Table)...BUT...you can do some stuff to mimic what you want to happen...

Excel: Pivot table, lena lena, pivot table

lena lena, pivot table, grand total: Lena: Presuming Excel is still storing the information for your pivot table in memory , then you can retrieve it as follows: 1) Find the cell that contains your grand total for all items in the Pivot Table. 2) Double-click on that cell; It should...

Excel: Pivot table?, ford toyota, pivot table

ford toyota, pivot table, pivot tables: Wayne I am not an expert in pivot tables, because I never use them, even though they are certainly useful. I suggest you reask the question of another and I m sure you can get a lot better answer than I can give you. Sorry I couldn t be of more help....

Excel: Pivot table field change with VBA, vba code, invoice numbers

vba code, invoice numbers, cell a1: Santhosh, The first thing that is clear is that you have the two lines backwards. You need to set the PageF variable first. PageF = Sheets( invoice ).Range( A10 ).Value ActiveSheet.PivotTables( PivotTable2 ).PivotFields( VouchNo ).CurrentPage = PageF ...

Excel: Pivot table - field subtotals automatic calculation, ugly workaround, google search

ugly workaround, google search, pivot table: Mihails: Unfortunately, there is no default to have sub-totals set to none for Pivot Tables. I also did a Google search to see if anyone had heard of a way to override that default setting (perhaps someone knew something I wasn t aware of) and I couldn...

Excel: Pivot table with linked pictures, separate entities, pivot table

separate entities, pivot table, video company: John, Pictures and graphics are separate entities in Excel. they don t exist in cells, but reside on a layer above the cells layer. So there really isn t anyway to associate a picture with an item in a pivot table. You could probably put the picture...

Excel: Pivot table name for VBA, table toolbar, pivot table

table toolbar, pivot table, table options: I don t use pivot tables that much and can t say for sure without diving into some new code I would have to write. However, I d suggest simply recording a macro and manipulate the pivot table like you need and then use the recorded code as a basis from...

Excel: Pivot table page fields, pivot table, data2

pivot table, data2, unusual behavior: Tom: I may need you to email me an example of what you want before and after to look like to Lotus@swbell.net if the following doesn t answer your question. Presume you have the following information: Region Customer Data1 Data2 North Alpha 100...

Excel: Pivot table and pivot charts, pivot table, pivot tables

pivot table, pivot tables, google: I have created a quick example of a Pivot Table and a Pivot Chart. You may download it here: http://home.swbell.net/nate-sus/excel/pivotexample.xls Columns A:C contain the original data we are building from. The Pivot table in E:G uses A:C to summarize...

Excel: Pivot table problem, doe john, header row

doe john, header row, pivot table: Dianne: The are selected for your Pivot Table must have a header row. This means the range you select for your Pivot must look something like this: Class Name Amount A4 Sewell $75 A4 Doe $50 If you do not have the header row, then...

Excel: Pivot table, pivottable toolbar, web toolbar

pivottable toolbar, web toolbar, sheet tab: Andrew, I will assume that the PT or PTs are on a separate worksheet than the raw data. I will assume that the name of that PT worksheet is PivotTable . Right click on PivotTable s sheet tab and select View Code. This will start the VBA editor. There...

Excel: Pivot tables, pivot tables, office excel

pivot tables, office excel, error condition: Likely you have text where you think there are only numbers, or some error condition. If you can t spot it, or if that s not the case, please email me the file with the explanation of where you re trying to group & I ll see if I can find the problem. email...

Excel: Pivot tables from scenarios, scenario data, pivot table

scenario data, pivot table, pivot tables: Unfortunately, by design, Excel doesn t allow changes/refreshes to a Pivot Table created from scenarios. You can workaround this problem with the following steps: 1) Select the spreadsheet/tab which contains the scenario data 2) Make the desired changes...

Excel: Pivot tables, pivot tables, f4

pivot tables, f4, f3: Not sure what you re asking. Is the sample table your matrix? Do you want to be able to enter 2,4 somewhere and get E? You want a formula? You want VBA code? Using the above matrix and my example of 2 & 4, you can get the E by: =INDEX($A$1:$D$3,2,4) If...

Excel: Pivot, douglas m smith, cell reference

douglas m smith, cell reference, style report: Hi Pat, Pivot tables are more for consolidation in a database-style report format. To get what you want in a list format, you can used the advanced data filter. I answered very similar question the other day: You use data filtering to extract the unique...

Excel: PivotTable, excel pivot table, visual studio tools

excel pivot table, visual studio tools, microsoft visual studio tools: Amal, You are correct that the pivotcache can hold more than 65K records. You would want to link to your data source directly. The best way to get the code is to turn on the macro recorder, then build the pivot table manually. You would choose external...

Excel: PLEASE PLEASE HELP!!, dde command, data validation

dde command, data validation, command test: These are basically wild guesses but something worth a try. Open a workbook in excel - then go to tools= Options and under the General tab, make sure Ignore Other Applications is UNCHECKED. the other thing you might try is to close excel and then go...

Excel: PLEASE PLEASE HELP !!!, command test, file xls

command test, file xls, excel spreadsheet: Brian In the interest of time I am gonna suggest that you reask the question of another allexperts person. I can t give you a quick answer to the problem although I am sure I can work it out, but this will take time, which you don t have. So go ahead...

Excel: PLEASE PLEASE HELP !!!, command test, file xls

command test, file xls, excel spreadsheet: Open it as usual, then right-click on the Excel LOGO (Near the file menu) and select View Code, then paste this in. Save it and next time you open it it ll do what you want. Private Sub Workbook_Open() Sheets( Sheet3 ).Activate Range( A1:A3 ),Select...

Excel: PLEASE PLEASE HELP !!!, command test, file xls

command test, file xls, excel spreadsheet: Brian: I am unable to work anything out. Go over to www.vbaexpress.com and join and ask. It s helpful to put up a sample file. Make sure they understand that you want a command line to use from the RUN box. Give the version of Excel this needs to work with....

Excel: PLEASE PLEASE HELP !!!, command test, file xls

command test, file xls, excel spreadsheet: Hi Brian Missed the deadline - sorry. . . If the target sheet/cell does not change for a particular saved version of the file, select the desired sheet/cell combibnation and save the file. It will always open at the same place. Regards. . . ...

Excel: PLEASE PLEASE HELP !!!, dell monitors, file xls

dell monitors, file xls, command test: IF you are opening an EXCEL file from Windows Explorer then I am not aware of any switch you would or could use since all you do is simply double click the file name in Windows Explorer unless you are opening the file in some other way. If so you did not...

Excel: Place file inside excel sheet, microsoft word document, file tab

microsoft word document, file tab, excel sheet: What s the criteria for knowing a task is to be done? Is the date past? withing a day? a week? Is the time important or just the date? You CAN use conditional formatting to give a color to the task. Assuming your dates are in column D, Select all of column...

Excel: Placement Rankings!!, vba procedure, vlookup

vba procedure, vlookup, seei: No, as a user-defined function -- put this in a module, then enter =MyLookup(A1,$A$1:$B$4,2) Function MyLookup(LU As Range, Rg As Range, Col As Integer) As String Dim ans As String, more As String On Error Resume Next Set newrg = Rg ans...

Excel: Placing a worksheet into a word doc., excel worksheet, staffing agencies

excel worksheet, staffing agencies, word doc: Hi Sweetniss: There are a couple of ways to include an Excel worksheet with a Word document. The easiest is to copy and paste, but if you do not want to do that there is another option. Go to Insert Object in Microsoft Word. You will be given...

Excel: Play sound Macro, code execution, note feature

code execution, note feature, audible feedback: You asked for it!!!!!!!! Playing Sound From Excel Excel developers can sometimes benfit by including sound in their application. For example, you can use a sound to provide audible feedback when a specific cell exceeds a particular value. Or, you just...

Excel: Please Help VBA Code For Saving File From Cell Inputs, filename change, macro code

filename change, macro code, rng: Option Explicit Sub SaveData() Dim sh As Worksheet Dim rng As Range Dim bk As Workbook Dim fName As String Set sh = ThisWorkbook.Worksheets( RecordDB ) fName = sh.Range( A7 ) On Error Resume Next Set rng = sh.Range(sh.Range( A5 ).Value & _ :...

Excel: Please help with an excel question., cell reference, cells

cell reference, cells: Ashlee To make a cell reference absolute you use the $ sign. for example: assume the cell reference +C2 To make the reference to the column absolute the reference would look like this +$C2 To make the reference to the row absolute the reference...

Excel: Please help With "Memo Field" In Excel, numerical portion, memo field

numerical portion, memo field, mini word: I would do it by having the user type into a textbox on a form (with work-wrap on) and transfer it to a cell by VBA code. If you send me your email address, I can send you a workbook which demonstrates the vba needed, but it uses a textbox on a worksheet (code...

Excel: Please help with time formating and IF function, column numbers, time max

column numbers, time max, column number: James, In your first example you use column numbers 1, 2, 3 & 4, but then when you show the formulas you have tried, you use H6. I m not sure how H6 factors in, so the formulas below assume that the first time (19:00) is in A1 and the second time (03:30)...

Excel: Please help with time formating and IF function, chip pearson, cell b2

chip pearson, cell b2, cell d2: Hello James, Assume cell A2: 19:00 cell B2: 3:30 Cell C2: =MIN(5,MOD(B2-A2,1)*24) Cell D2: =MAX((MOD(B2-A2,1)*24)-C2,0) this will give you the numbers you seek. ***make sure you format cells C2 and D2 as GENERAL *** They may default to...

Excel: Please help, cell a2, cell a1

cell a2, cell a1, excel spreadsheet: I am not sure what can be wrong, probably it is my assumption. I have assumed that you wanted the result for the first formula on the same format as the origin, and the second formula will work over that. With that, the formula is expecting the number 830,...

Excel: Please modify VBA, cell c3, cell c2

cell c3, cell c2, cell b2: Hey Norm, Would it be possible to email me the spreadsheet so I can make the change and test it? If so, my email address is bluecat74@yahoo.com. Otherwise, I think your problem may be in the offset. Offset(1,0) will go vertically but if you try Offset(0,1)...

Excel: Please tell me the formulas..., logical test, logical expression

logical test, logical expression, logical value: I cannot determine from your question if you are referring to the Excel function or the VBA function. If the first, the easist way to get more information is to hit the F1 key for Help, type if , and press enter. Select if from the list that appears....

Excel: Plot a point on a line chart, xy scatter, performance point

xy scatter, performance point, scatter plot: You probably need to change you graph to a xy scatter plot rather than a line chart. Then you can put the two values (Salesman achieved performance) in the same orientation as your original data. Select the two cells and do Edit= Copy. Then click on...

Excel: Plot a single point on a line chart, bottom graph, single point

bottom graph, single point, aidan: I m afraid this is a question I cannot give an easy answer to - the ONLY possibility I have come up with is to have TWO graphs, one on top of the other - the top graph is formatted to have a clear background, and the axes are set to the same values as the...

Excel: Plot a single point on a line chart, xy scatter, single point

xy scatter, single point, quota: change the line chart type to an XY-scatter, then add in this new point (x & Y coordinates) as a new series with no lines. you can email me a sample wb & I ll send back a sample. email is bobumlas@yahoo.com please repeat the question and use Allexperts Q...

Excel: PlotArea Select Error, series names, snippet

series names, snippet, parea: Matt, Not sure what to tell you. Before suggesting this on the last exchange, I put a chart in the worksheet and tested it. parea was not nothing until I deleted the last series. Then the Chart became an empty white box (but still a chartobject) and trying...

Excel: Plotting on 2 axis, secondary axis, conveyor belt

secondary axis, conveyor belt, middle column: If you start the wizard by choosing the custom types tab & selecting lines on 2 axes, you should have no problem. If you insist on using column charts, there s an inherent problem in that there s no good way to tell which column goes with which axis. So you...

Excel: Plotting Charts, spredsheet, a1

spredsheet, a1, xl: I assume you mean you re using an IF formula for the chart data. In which case, all you have to do is set the IF formula to return the #NA! error for a value that you do not want to appear (zero for instance). A cell formula something like this... ...

Excel: Plotting data points, dimensional chart, excel 97

dimensional chart, excel 97, maximum number: Interesting question, Joel! The only way I could find this out for you is if I actually had the programs to go in look this up in Help. Currently on my system, I have Excel 97, Excel 2000, and Excel 2003. I just went to look this up in Excel 97 and here...

Excel: Plotting Stock Charts, chart expert, corrosion data

chart expert, corrosion data, stock chart: Marcy I am definitely not a chart expert, but it sounds as though you are trying to add each set of data independently of the other to the chart. If you put all of the data on a sheet and then chart it all at the same time I believe you can solve the problem...

Excel: Plotting two series of time stamp/values on the same chart, xy scatter, stamp values

xy scatter, stamp values, time stamp: Your data is currently text and needs to be in separate columns and date/time. There needs to be a comma after each day (and you also have 2 spaces after Aug and there needs to be one. Once your data is in place, select it all & create an XY-scatter, but...

Excel: Plotting two series of time stamp/values on the same chart, time stamps, xy scatter

time stamps, xy scatter, stamp values: You didn t give me coordinates of these values, but if source1 is in A14:B15 and source2 is in A17:B18, you can select the source1 values & make your xy-scatter chart, then select source2 data, copy it, select the chart, use edit/paste special, and click New...

Excel: PO Number, purple onion, purchase order template

purple onion, purchase order template, windows 2000 prof: Tou can t have both. You can t have it increase by one when it is opened AND also have it stay the same when it is opened again. Opening the workbook is opening the workbook and opening the workbook is what will cause a macro you need to run by itself...

Excel: Pointing to a worksheet, excel help, excel 2002

excel help, excel 2002, keystrokes: Jim You can do this by writing a macro that will go to the cell And then assigning the macro to a button on your spreadsheet. To record the macro go to toolbar/tools/macro/record new macro. You then need to make the keystrokes that are necessary, which...

Excel: Pop-Up boxes, worksheet cells, macro code

worksheet cells, macro code, sheet tab: Hi Jeff, Yes, several methods are possible but all will require macros, some more than others. I can give you a quick overview: MS Excel 5.0 Dialog Box: Right-click on a sheet tab and select Insert. Double-click on the MS Excel 5.0 Dialog sheet. This...

Excel: Pop Up Calender in XL, windows common controls, date time picker

windows common controls, date time picker, time picker control: I imagine Bob suggested the same thing I did. That is what is shipped with Office, but it is shipped for use with Access - not Excel - it is usable in Excel however. ---------- I see Bob suggested the Date-Time Picker control. It isn t distributed with...

Excel: Pop Up Calender in XL, time picker control, control toolbox

time picker control, control toolbox, exit design: View/Toolbars/Control Toolbox. Click the last tool ( additional controls ); scroll for Microsoft Date and Time Picker Control 6.0 Draw the rectangle (control) in the desired cell. Right-click it & select Properties; click LinkedCell, enter sheet name...

Excel: Pop-up Message as Reminder on Maturity Date, maturity date, maturity dates

maturity date, maturity dates, data layout: James, You can use conditional formatting (format= conditional formatting) to color the cells based on maturity date. Use the cell is between today()-1 and today() A popup message would required using VBA code. But if you want a summary report anyway,...

Excel: Pop reminder, xls workbook, visual basic for applications

xls workbook, visual basic for applications, menu tools: To do this, you need some understanding of vba (Visual Basic for Applications). Go to the Visual Basic Editor (VBE) by choosing from main Excel menu Tools, Macro, Visual Basic Editor. In VBE find the Personal.xls workbook object. If it s not there, return...

Excel: Populating blank cells with contents of cell BELOW, blank cells, rng

blank cells, rng, abc123: Dave, the usual request is to fill in all blanks with the value above, so this is a unique one for me. I assume you want a macro. This one worked for me on your test data: Sub fillBlanks() Dim rng As Range, cell As Range With ActiveSheet Set...

Excel: Populating cell depending on 2 parameters, cell a2, cell a1

cell a2, cell a1, digit number: Yes. IMHO, a VLOOKUP would be easier to write and understand. Of course you will have to create the table that shows all possible combinations the VLOOKUP would use. My suggestion for the table would be a two digit nunber in the first column and then...

Excel: Populating cells from a drop down menu, cell c3, target address

cell c3, target address, paste info: I m having difficulty with your terminology. There s no such thing as a Data Validation Dropdown Menu (the menu part throws me). If you mean a DV dropdown list, I m with you. But is your list cell addresses?? Lik: L2:L60 L62:L122 etc?? Then I still don...

Excel: Populating a column based on a cell value, column headings, circular references

column headings, circular references, paste function: This case is similar to one of the latest questions that I had. You can find my answer to that case at http://en.allexperts.com/q/Excel-1059/Excel-Copy-Paste-Function-2.htm Essentially, the idea is to use circular references, enabling the iterations on the...

Excel: Populating a drop down with data dependent from another drop down, data validation, spreadsheet

data validation, spreadsheet, swbell: Bryan: This appears to be the question of the week :). Please check out this spreadsheet (it is from a question I answered last weekend): http://home.swbell.net/nate-sus/excel/japan.xls It would need modification to work with your data, but the principle...

Excel: Populating Excel with data from Word - help!, code snippets, programming experience

code snippets, programming experience, local college: Sorry, I don t just write macros for people on this site who need one, want one, and/or seem to think I am expected to do so. 99% of all macros are more involved than what you think and rarely am I provided with enough specific and complete details. This...

Excel: Populating a range of cells based on dropdown box, autocomplete feature, cell b1

autocomplete feature, cell b1, cell c1: Darryl, A dropdown is awkward in this situation, in my opinion. I think the easiest way to do this is to take advantage of Excel s AutoComplete feature. After you type Friends and Business once in the column, Excel will automatically complete the...

Excel: Populating one sheet based on data from another sheet, microsoft excel 2002, sheeta

microsoft excel 2002, sheeta, central repository: I doubt yo will find a website that will help you create the macro you need except for all of the ones that discuss how to write macros in general. You might try another person on this site to write the macro for you. But I suspect you will be hard pressed...

Excel: Porblem with nested ifs, f1 data, t3000

f1 data, t3000, salzer: What you described is not what you started to type. The syntax must still be =IF(condition,value if true, value if false). Value if True (or false) can be another IF-statement. Looks like you tried a syntax of: =IF(Condition,value if true,value if false),If(condition2,...)......

Excel: Possible Combinations, number combinations, allexperts

number combinations, allexperts, macros: I had a similar question some time ago, just with 4 spaces instead of 6, but the mechanics are the same. You can check the solution at: http://en.allexperts.com/q/Excel-1059/number-combinations.htm There is a follow up from Chris, that does it with formulas...

Excel: Power Point VBA, equivalent object, excel code

equivalent object, excel code, saveas: No. But try using Application.GetSaveAsFilename instead. You will still need to actually save the file after the File Save As dialog has been dismissed. Here is some EXCEL code. This should work in PP as well but I don t do any coding in PP so I can t...

Excel: PRESCRIPTION DRUG EXCEL SHEET, brand name drugs, formulas in excel

brand name drugs, formulas in excel, dependent coverage: Sorry Dennis, I need to make a correction to my final equation - IF(A1= 600,IF((B1+C1)/2 600,600,(B1+C1)/2),0) Even Experts sometimes make mistakes - Andrea Dennis - Say Column A is monies paid by employee, Column B is generic drugs paid, Column...

Excel: PRINTING THE COLLUM HEADINGS?, column headings, sheet tab

column headings, sheet tab, mouse pointer: Hi James, Select the Excel sheet you would like to print. Then go to the Excel menu and select File then Page Setup then select the Sheet tab. On the Sheet tab you will see a box called Rows to repeat at top insert your mouse pointer within this...

Excel: PRINTING IN EXCEL, ms money, print dialog box

ms money, print dialog box: Pete It sounds like the file has been associated or saved as an excel file. I don t know money so can t help you with that. However in Excel there is an option in the print dialog box to fit the document to a certain amount of pages. This will depend...

Excel: PRINTING, printer print, ogilvy

printer print, ogilvy, spreadsheet: Unless you want to write a macro to set up the pages, I am not sure how you can override what excel is doing. Perhaps you can install the print driver he is using (you don t have to have that printer), make a copy of your workbook, open it and make his...

Excel: Present value of a lump sum, present value table, periodic investment

present value table, periodic investment, npv: =PV(5%,1,0,-1)^20*10000 the 5% is the assumed rate. the 1 is one payment (of $10000). the 0 is periodic investment amount. the -1 is to get the rate. the ^20 is that value raised to the 20th power for the 20 years, and that amount is multiplied by the value...

Excel: Prevent Delete, excel spreadsheet, click properties

excel spreadsheet, click properties, security settings: Ryan, This is probably not an Excel question. It depends on the Operating system and the permissions that you have. Theoretically, you should be able to change the security settings of a file (right click - properties) and define the group of users who do...

Excel: Prevent / reduce unauthorised usage, vba macros, cost effective solution

vba macros, cost effective solution, unauthorised person: I m not 100% happy with passwords embedded in macros (do it, then open the workbook in Notepad - the password is visible a lot of the time!), and I m not 100% sure how you would handle the change after a year. In the company I work for, the security is basically...

Excel: Prevent SAVE - follow up, case statement, admin password

case statement, admin password, pw: You could set a password & a Select Case statement Option Explicit Const pw As String = admin Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim ans As String ans = Application.InputBox( Enter your admin password...

Excel: Prevent SAVE, code option, left hand

code option, left hand, top left corner: Remember this will only work if Macros are enabled. Use the Before_Save event of the Workbook. Right click on the small Excel icon in the top left corner of the screen. This will open the VB Editor, make sure Workbook is selected in the left hand drop...

Excel: Prevent spreadsheet from being opened in Word, template style, spreadsheets

template style, spreadsheets, spreadsheet: (1) I m not aware of an easy one, although you COULD create a macro called FileOpen in word - this would replace the existing File Open command and could therefore refuse to allow excel files to be opened. My own method for sheets that I am in charge of is...

Excel: Preventing cell selection, sheet tab, toggle

sheet tab, toggle, cheers: right-click the sheet tab, select View Code. If you don t see the properties window, press F4 (it s a toggle for properties window). On the left, you ll see Enableselection and to the right is a dropdown in which you can choose NoSelection , which takes...

Excel: Preventing changes to an Excel spreadsheet, format cells, excel spreadsheet

format cells, excel spreadsheet, email attachment: You will need to do this for each sheet in the workbook with data you want to lock . 1) Click on Tools - Protection - Protect Sheet 2) There are some options here, but usually just having the first two checked is good. 3) Enter a password and confirm...

Excel: Preventing changes to an Excel spreadsheet, format cells, excel spreadsheet

format cells, excel spreadsheet, document solution: Dear Marty Kaye, i Understand your problem. Here is my suggestion to prevent your document. Solution # 1: Protect your worksheets. Goto Tool- Protection- Protect Sheet. A dialogue box will be appeared. In that text box creat your password. Below...

Excel: Preventing changes to an Excel spreadsheet, excel spreadsheet, email attachment

excel spreadsheet, email attachment, recipient: Marty Activate the worksheet to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional). If you do provide a password, that password will be required to unprotect the worksheet. Hope this helps....

Excel: Price List, sug retail, excel 2002

sug retail, excel 2002, sp3: A macro really won t help you all that much except to save you the little time you spend copying and pasting the formulas. Once the formula has been written, and it has so you say, then copying and pasting is the way I d suggest. Change your formula to...

Excel: Price list-grading, true relationship, sorry bob

true relationship, sorry bob, grade c: Lookups generally need to be in ascending sequence, not descending sequence. If this is truly the way your data is, a simple IF-formula can do: If the word Nissan is in A3, then if the price (9000 in your example) is in cell C7: =IF(C7 =B3, A ,IF(C7 =C3,...

Excel: Price target, price target, desktop category

price target, desktop category, 20ft container: I will take a look at it and see if it is something straightforward I can set up. No charge if it is - no charge for me looking at it and responding. Send me a sample workbook with all the information. I really don t understand your sample answer ...

Excel: Print Challenge, distinct sections, macros

distinct sections, macros, dictates: Best to use a userform with all the sheets listed in a listbox for you to multi-select which ones to print. maybe 2nd best is some variation on this: Sub PrintChallenge() untested On error resume next 1: SheetToPrint=Inputbox( Enter sheet name...

Excel: Print Different Ranges To Same Page, sheet1, macro recorder

sheet1, macro recorder, brainbench: Hi Ken, There isn t a simple way to accomplish this. Excel can t print from multiple worksheets onto the same page so you will need to add a worksheet. Copy all of the ranges onto the new worksheet. Print it and then delete the new worksheet. The easiest...

Excel: Print Multiple Ranges Via VBA, sheet1, linear fashion

sheet1, linear fashion, ogilvy: Your requirement isn t directly supported in Excel. You can set up a separate sheet and use linking formulas to link your data from multiple locations to form a single worksheet representation of your output. Then you can print that single sheet as one page....

Excel: Print Preview and Save, compatibility checker, print icon

compatibility checker, print icon, preview screen: Have not used 2007 yet but it sounds as if the file has been saved in a 2007 format and if so, regardless of how much data you have, the file probably has the extra number of rows and columns that 2007 gives you and this is what is causing the problem. ...

Excel: Print Preview to a specific page, page layout view, check selection

page layout view, check selection, excel spreadsheets: AFAIK, Excel will always start on page 1 unless you change the print_area (then it still starts at page 1 but it s not the original page 1). You may like the new feature in Excel 2007 which has a Page Layout View -- something between print preview and page...

Excel: Print preview using a dynamic string array, string array, dynamic string

string array, dynamic string, subscript out of range: Yes it does and you re welcome. Many times the desired result and/or what the question poster is trying and/or wants to do is not always the one that makes sense however. It all depends on what the question poster is trying and/or wants to do. Glad you...

Excel: Print problem, sheet tab, cell structure

sheet tab, cell structure, select tools: You can, but it s done with picture. Get the camera tool into one of your toolbars: View/Toolbars/Customize; select Tools in the left listbox, then about 3/4 of the way down on the right listbox is the Camera tool - drag that onto one of your toolbars.Next,...

Excel: Print Titles: Column(s) to repeat at left, split bar, right arrow

split bar, right arrow, printing purposes: If you set column J as print_titles then it will always print, but won t be at the left. I m afraid what you want to do can t be done without a macro to temporarily take col. J & move it before Col A, then print, then return it to its rightful place. If this...

Excel: Print table (PivotTable(?)), pivottable field, table wizard

pivottable field, table wizard, using microsoft word: The pivot table wizard SHOULD hold your hand most of the way, BUT before starting it I would do the following (1) make sure that each column has a unique heading (these are the field names) (2) Select the entire data range as a block before starting -...

Excel: Print Worksheets based on sum of column, column sums, vb question

column sums, vb question, sum function: One way to do it is to use the WorksheetFunction property of the Application object, that allows you to access many of the Excel functions from VBA. In this case, you can create a variable, for example iSumCA, and assign it to the sum of the full column with...

Excel: Printing, printer manufacturer, aidan

printer manufacturer, aidan, duplex: Each page is a seperate page, and a seperate print job, so as far as I know you cannot do it automatically - though you may be able to get more support for this from your printer manufacturer, or from an expert in that printer? The only way I can suggest...

Excel: Printing Comments, vba, spreadsheet

vba, spreadsheet: There are only 3 options for printing comments in Excel. 1. None 2. End of Sheet (really seperate sheet) 3. As Displayed I m afraid there s no easy way to do what you re asking. If even possible, it would involve using VBA. And I suspect it would...

Excel: Printing Excel Formulas, cell addresses, excel formulas

cell addresses, excel formulas, cell address: You would need a macro. This should do it: Sub ShowFmls() On Error Resume Next Set cur = ActiveSheet Set newguy = Worksheets.Add cur.Activate i = 1 For Each x In Cells.SpecialCells(xlCellTypeFormulas) newguy.Cells(i,...

Excel: Printing Excel Spreadsheet, change printers, excel spreadsheet

change printers, excel spreadsheet, printer settings: Hi Nancy, This leads me to believe something else may have happened. Do you share the same printer? Did someone change printers? I would try and think back 4 months ago and see if something changed with your hardware. It could be as simple as someone changing...

Excel: Printing Excel Spreadsheets, excel spreadsheets, array data

excel spreadsheets, array data, speadsheet: Hi again Eric, I m sorry to report that there is no way--that I know of anyway--to keep Excel from placing a form feed between pages. However, and as I mentioned before, it is possible in your code to copy the two sheets to Word and print them on a single...

Excel: Printing Excel worksheets, celld, pdfcreator

celld, pdfcreator, cost basis: if the sheet has to be dynamic in terms that when he or she makes entries, these columns are used in the calculations, then the best approach would be to put these columns on another sheet and make that sheet xlHidden, then passord protect the structure of...

Excel: Printing with Excel, command button, column width

command button, column width, data input: Hi Eric, How are you? Please try the following: Sub PreviewingAll() Application.ScreenUpdating = False Sheets(Array( Sheet1 , Sheet2 )).Select ActiveWindow.SelectedSheets.PrintPreview Sheets( Main Menu ).Select Range( A1 ).Select End Sub...

Excel: Printing invoices with excel, printing invoices, vlookup function

printing invoices, vlookup function, receivable system: August Sorry I hven t gotten back to you quicker, I ve been out. Your question encompaaaes a lot. It is possible to do invoicing from a data base. First you need to set up the database in such a way that the invoice page can acces the information, and...

Excel: Printing large text boxes, questionnaire responses, vertical scroll

questionnaire responses, vertical scroll, text response: I think you can only resize the Textbox to display the full text before printing. possibly you could use VBa to do this. If you want strategy then don t use Textboxes or size them to fit the contents, I can t answer more. I said you could probably use VBA...

Excel: Printing long text boxes, questionnaire responses, vertical scroll

questionnaire responses, vertical scroll, text response: I m not sure that excel is the best vehicle to do what you want, but obviously all I know about the project is what you have told me - my own preference would be to use Word, with form fields - when the results are returned, the data can be saved as results...

Excel: Printing a range on each page of data, census data, insurance broker

census data, insurance broker, cells: Corinne, I d really have to see the document to answer completely and accurately, but consider linking some cells or named ranges to Word...or using a macro to push them to Word for you: http://www.vbaexpress.com/kb/getarticle.php?kb_id=381 I hope it...

Excel: Printing specific columns, hth, cols

hth, cols: You can set up views to do this. Follow these steps (only necessary to set up once, then you can access the views you want) 1- View/Custom Views, Click Add, Give a name, like Normal , Click OK 2-Hide cols D,F,H,I,J,L,M 3-View/Custom Views, click Add, give...

Excel: Printing worksheet with printer tray # 3, salvador hernandez, kb article

salvador hernandez, kb article, support microsoft: Salvador, I m pretty confident that Microsoft hasn t provided a good way to do this. They do have a KB article that may help though, you can find it at http://support.microsoft.com/default.aspx?scid=kb;en-us;213815&Product=vba It s a bit convoluted, in...

Excel: Printing, debbie debbie, cell contents

debbie debbie, cell contents, decimal places: Debbie This usually indicates that the cell contents are wider when printing, this can be because the view is only too certain decimal places but the print is to all the decimal places, you can view what the printout will look like in Print Preview and...

Excel: Private macro, van inventory, flex line

van inventory, flex line, rng: Hello Art, Assume the data starts in row 5 - adjust if not Sub AddFormula() Dim rng As Range Const sAddr As String = C5 With ActiveSheet Set rng = .Range(.Range(sAddr), _ .Cells(Rows.Count, C ).End(xlUp)) End With rng.Offset(0,...

Excel: Private Sub Macro, event handlers, macros

event handlers, macros, refered: The private macros doesn t show in the macro options by default, as they are marked private. Usually, the only macros that need to be private in Excel are the worksheet/workbook events, or macros refered by other macros that don t need to be accessed by the...

Excel: Pro-Rata, julian dates, datedif function

julian dates, datedif function, leap year: I believe julian dates still recognize and count Feb 29. I am at a disadvantage because I have no idea what the purpose for this is and what must be precise and where you might have leaway - what differences you will need to calculate/what level of precision...

Excel: Prob. in Excel 2002 sp1,sp2 but working fine excel 2003-sp2, c oat, jet oledb

c oat, jet oledb, sp1 sp2: Amie I m not a good one to answer your Question I don t have excel 2002, and I haven t experienced the problem before. I suggest you ask another allexperts person and I m sure you can get a good answer from one. Sorry I couldn t be of more help. ...

Excel: Probably simple, shortcut keys in excel, secondary keys

shortcut keys in excel, secondary keys, simple hello: mike The only thing I can think of is that you are using a laptop and have activated the secondary keys (if it even has them, so do and some don t). You can t change the shortcut keys in excel, you can only add to them. The registry may have become...

Excel: Problem with array, brainbench, zero one

brainbench, zero one, default settings: Michael, You should be able to add as many conditions as necessary with a few exceptions. You cannot embed more than seven layers: for example IF(IF(IF(IF(IF(IF(IF(Condition, true, false),true,false)...)))). Also, when creating array formulas, all references...

Excel: Problem With Backward Compatibility, vba project, worksheet functions

vba project, worksheet functions, backward compatibility: I need more info -- when you say doesn t work -- what are the symptoms? Macro doesn t run at all? Macro has errors? Calculations/formulas returning different results? Working in XL2003 and saving in 2000 doesn t ensure compatibility. There are features...

Excel: Problem Creating CSV File Via VBA, text cells, csv file

text cells, csv file, vba code: Ken, I don t know that I can say for sure. It may be because you don t specifiy a sheet to start with, so the code could be looking at the wrong sheet. I m a little suprised that it even worked with the Fname setting you used. If the file is to be stored...

Excel: Problem closing workbook, vba code, spreadsheet

vba code, spreadsheet, cells: It looks to me as if this is simply calling itself Sub BeginProcess() refreshInterval = ThisWorkbook.Sheets(1).Cells(7, 6).Value * 60 timeInterval = Now + TimeSerial(0, 0, refreshInterval) Call UpdateWorkBook Application.OnTime timeInterval, BeginProcess...

Excel: Problem with Excel 2000 custom toolbar and macros, custom toolbars, little buggy

custom toolbars, little buggy, custom toolbar: Sheree, This is an interesting problem with Excel. To store a toolbar in a workbook file, select View/Toolbars/Customize to display the Customize dialog box. Click the Attach button to bring up that dialog box, listing all of the custom toolbars in the box...

Excel: Problem in Excel 2000 with custom toolbar and macros, little buggy, custom toolbar

little buggy, custom toolbar, sure what version: Hello, Sheree, The Excel 2000 toolbar information is kept in a file called Excel.xlb. I m not sure what version of Windows you re using, but you can probably find the file in this folder: C:Documents and SettingsAdministratorApplication DataMicrosoftExcel...

Excel: Problem with Excel VBA, professional service pack, vba code

professional service pack, vba code, excel vba: Hi Roberto How are you? This a correct version of your code Sub testa() Windows( second.xls ).Activate MsgBox ActiveWorkbook.Name MsgBox ActiveSheet.Range( A1 ).Value End Sub There were some syntax errors. But if you use a variable to...

Excel: Problem with MATCH, vba code, match

vba code, match, worksheet example: Use Application.Match() instead of Application.WorksheetFunction.Match() and use it just like you use the =MATCH() function on a worksheet. Example: if on your sheet you have a range named dArea and within it is a cell with the text value cat...

Excel: Problem with Macros in Excel 2000, macros in excel, clear browser cache

macros in excel, clear browser cache, macro development: Mohan Sorry I m not gonna be able to help you much with this. I ve not had the need to develope the expertise in this area. I suggest you reask the question of another allexperts person. I m sure someone will be able to get you a good answeer. Ck the...

Excel: Problem with number of sheets in Excel, master spreadsheet, steam turbines

master spreadsheet, steam turbines, steam turbine: This is a known issue with copying sheets. The solution is to ADD a blank worksheet, then copy the cells from one to the other (& objects, if any), then also, unfortunately, if named ranges need to be copied as well (that is, the name must be carried over),...

Excel: Problem setting formula in cell via VBA, vlookup function, quotation mark

vlookup function, quotation mark, quotation marks: 1) Arguments in a function are separated by commas, not semi-colons. 2) The IF function takes 3 arguments; in your code, you re giving it 4 3) The VLOOKUP function takes 4 arguments; in your code, you re giving it 3. 4) I m assuming that you want...

Excel: Problem with small inventory sheet, stack 2, sum formulas

stack 2, sum formulas, inventory sheet: I would convert all values to stacks, and then use the INT and MOD formulas to get each part. For example, having the data layout this way: A B C 1 Store Box Stack 2 Store A 1 3 3 Store B 1 10 The formulas would be...

Excel: Problems with Clipboard, screen key, clipboard

screen key, clipboard, support microsoft: The only way I know of to get a screen shot is to press the print screen key (or Alt print screen) - ordinary copy would copy the selection (if any) to the clipboard - I haven t been able to find this documented anywhere at the knowledge base either, though...

Excel: Procedure and Function, filesearch, quotation marks

filesearch, quotation marks, excel macro: 1) change your Go_Click function to a sub; also change the fltyp type. That is: Sub Go_Click(kywrd As String, fltyp As MsoFileType) 2) in your Flrch sub, remove the parentheses. Also, msoFileTypeAllFiles is a constant which DOESN T go in quotation marks....

Excel: Processing text data in a worksheet, comma separated list, type columns

comma separated list, type columns, type column: Sorry, I am confused. You say ... each cell in that column has comma separated list of product types (of the possible 31 product types... That does not make sense since there can not be more than one cell per column. Do you mean that there are as many...

Excel: Producing a Chart from dynamic range, cell addresses, range selection

cell addresses, range selection, source selection: Sorry I ve taken so long getting back to you -- been away/busy. How about using a defined name in the chart series instead of cell addresses. You can use any name, say sers . In your VBA, after the statement which does the selection, use this statement:...

Excel: Product lists - updating, dsl modems, category types

dsl modems, category types, product classifications: Well, it makes sense on the surface but without actually seeing it then it is hard to FULLY understand it. EXCEL will most probably do the job you need but again without seeing it it is hard for to tell if your workbook has been set up and designed efficiently...

Excel: Product Number and Pricing, microsoft excel 2002, example spreadsheet

microsoft excel 2002, example spreadsheet, data validation: Hi Janet, Cool! I would definitely create a template, especially if you are selecting the same product numbers over and over again. Please take a look at this example spreadsheet using VLOOKUP function with a drop-down list. Just pretend the numbers on...

Excel: Programming Excel Based on Cell Value, richard nash, filter auto

richard nash, filter auto, variable values: Dustin You could use Data Filter Auto filter and select the one you want (this would be quicker if you have more than two variables) but if you want to run a macro do the same steps but record a macro as you do. It would look something like this...

Excel: Progress bar code, nextpos, pieces of the puzzle

nextpos, pieces of the puzzle, progress bar: You can use the LOF function to get the length of the file in characters. LOF(1). If you have a constant line length or near constant line length, you can divide the number of characters by the number of characters in a line. Remember that you have two...

Excel: Projects and Reports, altman, office 2000

altman, office 2000, new job: Well, at least now you have a clean clock! :-) Thanks for writing in, Dan, I d be happy to help you. My availability is always open, and I m confident I can save you a lot of time. Anything that you need to do in Excel that is either repetitive or time-consuming...

Excel: Proper case, target range, data validation

target range, data validation, vba project: Union Method Returns the union of two or more ranges. Syntax expression.Union(Arg1, Arg2, ...) expression Optional. An expression that returns an Application object. Arg1, Arg2, ... Required Range. At least two Range objects...

Excel: Protect a Macro, vba project, active project

vba project, active project, ogilvy: do Alt+F11 to get to the visual basic editor. with you workbook as the active project (selected in the project window on the left of the vbe), in the menu, goto tools, then select VBAProject Properties. The VBAProject is the default name for the VBA project...

Excel: Protect macro with password, password tools, macros

password tools, macros, aidan: No problem - you will need to protect the macros themselves so they cannot be viewed without a password (tools, vba project properties, protection then lock the project with password from here). Then make the macros begin with a line that says if inputbox(...

Excel: Protected cells, listboxes, radiobuttons

listboxes, radiobuttons, c29: Sub IdentifyUnLockedCells() Dim rng As Range, rng1 As Range Set rng = Range( A1:B200,C29:D50,F10:G100 ) For Each cell In rng If cell.Locked Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If ...

Excel: Protected Sheets, protection options, print icon

protection options, print icon, excel 97: Alan, Post Excel 97, Excel allows one to specify on a line item basis which activities can still be allowed even though the sheet is protected. If the original author allowed hiding / unhiding of rows / columns while setting up the protection options,...

Excel: Protecting borders and formats only, douglas m smith, target cell

douglas m smith, target cell, paste values: Hi Heath, This can be done, but not as a drag & drop operation. You copy the cell, then use Paste Special (Edit menu), and selecting the Values option to paste just the text into the target cell. Paste Special is also available on the right-click pop-up...

Excel: Protecting a cell, doing something wrong, color question

doing something wrong, color question, cell a1: No, once a worksheet is protected, you can t change things like formatting, etc. There s another way to protect cells with formulas. You can use event code. Right-click the sheet tab and select View Code, then paste this in: Private Sub Worksheet_SelectionChange(ByVal...

Excel: Protecting cell on multiple sheets, sheet tab, group sheets

sheet tab, group sheets, cells: cells are protected by default. In any case, if you group the sheets, then whatever you do on one sheet is done on all the sheets in a group (in the same cell(s) on each sheet) -- to group sheets, you can either ctrl/click another sheet tab, or shift/click...

Excel: Protecting a cell, vba, cell protection

vba, cell protection: You can t - the cell protection ONLY kicks in when the sheet it is on is protected. You could PROBABLY use some VBA to fire on change on a cell, but as all that is needed to bypass this is opening the sheet without macros enabled it is not a good plan! You...

Excel: Protecting cells per user editing, pull down menu, spreadsheet

pull down menu, spreadsheet, cells: I am confused. As long as you are the LAST person to use the file then you should be able to and would, when applicable, delete an approval a prior user entered regardless if that prior user was authorized to approve the transaction or not or the user is...

Excel: Protecting Data, hyperlink

hyperlink: have all the data hidden, and then upon clicking the appropriate button, have VBA code ask for the password, and if it s correct, the VBA will unprotect the sheet (it has its own sheet password) and unhide the appropriate rows/columns. Before this workbook...

Excel: Protecting, Grouping and Greetings, target address, cell a1

target address, cell a1, allexperts: Hey Dan, Sorry! I should have put more detail :o) You put this code in the Worksheet Change Event. You get to this by opening VB and clicking on the Sheet that you want to inset this code in. You will see Worksheet in the first dropdown. Just select...

Excel: Protecting sheet, protectall, vba code

protectall, vba code, question thanks: To do that, you need to replace the Inputbox with a Userform which contains a textbox which contains a password character propoerty. I can give you a sample if you send me your email -- too hard to simply describe here. Actually, send the WB we ve been working...

Excel: Protecting all sheets in a workbook?, macro tools, excel 5

macro tools, excel 5, cheers: Hi Robert, How are you? You may write a VBA program such as the following 1. Tools - Macro - Visual Basic Editor 2. Insert -Module 3. Copy Sub Protecting() For s = 1 To Worksheets.Count Set ws = Worksheets(s) ws.Protect DrawingObjects:=True,...

Excel: Protecting sheets, format cells, staff rotas

format cells, staff rotas, sheet1: It SOUNDS as though there are only certain areas a person can update? Therefore, the first step would be to unlock the areas they CAN update (format, cells, protection tab) - this would ensure they didn t need a password to do the work. For the stopping...

Excel: Protecting a workbook, ogilvy, end users

ogilvy, end users, tabs: Reginald, on the menu go to Tools= Protection = Protect Workbook... Then protect at least the structure of the workbook. You can assign a password. this will prevent users from changing the name, changing the order, or adding or deleting sheets....

Excel: Protecting worksheet formulas -flexible formating, microsoft office applications, microsoft excel

microsoft office applications, microsoft excel, cells: To my knowledge, you must create macros (you can put buttons for them on a toolbar) that will: --Insert Row --Delete Row The macros will (you can record these): --Unprotect --Insert row (or delete a row) --Reprotect So: Record a macro. See:...

Excel: Protection of cell, format cells, cell c3

format cells, cell c3, format menu: Hi there... To allow changes only to CERTAIN cells in a spreadsheet you will have to password protect the sheet. Now in your case, you need cell C3 to be locked(protected) and cells C1 & C2 to be edited. Heres how you do it... By default, all cells...

Excel: Protection of cell, format cells, accidental erasure

format cells, accidental erasure, data validation: Hello Sy, There are several ways to do this. The most common way is to protect the cell or cells containing formulas. To do this you must unlock all the other cells so they won t be protected. You can select all the cells on the sheet by clicking on the...

Excel: Protection of cell, f4 key, process tools

f4 key, process tools, command key: Hi Sy, Yes you can. It is cell protection. What you will be doing is selecting the cells that you want to let users access and input data and unlocking the cells. So select cells C1 and C2. Then go to the Excel Menu and select Format then Cells... then...

Excel: Protection of cell, format cells, huynh

format cells, huynh, c1: Sy The first step is to select the entire sheet, then go to format cells and protection, remove the tick mark from Locked. Desect the cells and go to C3, go to Format, Cells, Protection and click on the Locked tick, this will make only this cell in the...

Excel: Protection of cell, data validation, error alert

data validation, error alert, input message: Sy Try using the data validation feature and putting a message in that will alert your co workers when the cell is selected that they are to make no entry in the cell. Toolbar/data/validation/settings input message and error alert. This should help....

Excel: Protection of cell, format cells, menu format

format cells, menu format, c1: Yes - firstly, format the cells you want the users to be able to edit and use the protection tab (from the menu, format, cells then click on protection, or right click on the cell and choose from here) - ensure the cell is set to UNPROTECTED. Then use the...

Excel: Protection of cell, format cells, ogilvy

format cells, ogilvy, c1: Hello Sy Huynh, by default, all cells are set as Locked. However, this setting is not used until the sheet is protected. Tools= Protection= Protect Sheet You can select the cells you want to allow the user to edit and then do Format= Cells, select...

Excel: Protection of cell, target address, format cells

target address, format cells, sheet tab: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Select Case Target.Address Case $C$3 , $D$17 , $A$12 ,$B$7 MsgBox Please do not select this cell , vbExclamation Range( A1 ).Select End Select End Sub ...

Excel: Protection of cell, input cells, format cells

input cells, format cells, different colors: Right Click on the cell and click on Format Cells . Click on the Protection tab. Check the Locked box. Check the Hidden box if you wish to hide the formula from the users. Now locking the cell does not have effect until you protect the worksheet. So...

Excel: Protection - Data Entry, entry cells, spreadsheet tools

entry cells, spreadsheet tools, format cells: Yes, Les, sometimes it s hard to get people to do it exactly the way you want. What I might suggest is to highlight the starting cell--with a colored border or fill--and then put a sign next to it saying, Start here. Also, you might want to include...

Excel: Protection + Delete Rows, target range, cell content

target range, cell content, sheet tab: You can do it with a fairly simple macro. Select ALL the cells which are not to be changed (ctrl/click through them all). Then give it a name, like Stuff by typing stuff into the name box (above cell A1). Then right-click the sheet tab, select View Code...

Excel: Protection using validation, format cells, ok step

format cells, ok step, settings tab: Select all non-formulas: Press F5 Special Constants Set constants as unlocked: Format Cells Protection Remove checkmark from default Locked state Then protect the sheet: Tools Protection Protect Sheet Regards, Aaron www.XL-Logic.com...

Excel: Protection and views, vba code, audit trail

vba code, audit trail: You can unprotect/reprotect what you need to do in the VBA code. Once it s protected, you re very restricted to what you can do without unprotecting. Same with 2003 and 2007. Have your VBA do things like: Sub DoSomething() Activesheet.Unprotect ThePassword...

Excel: Protection, douglas m smith, sensitive passwords

douglas m smith, sensitive passwords, frederickton: G day Nola, You can protect your file by requiring a password to open the file. To do this, call up the Save As... dialog box. Click on the Tools dropdown box (upper right corner of the dialog box) and select General Options...). (Older version...

Excel: Pull Data out of a String, intl exchange, capital request

intl exchange, capital request, onevoice: Say your string is in Cell A1. Make sure that A1 is formatted as TEXT. Otherwise, it won t be recognized as a string, but rather as a formula. Then go to a different cell that s NOT formatted as string, and use this formula: =MID(A1,FIND( [ ,$A$1)+1,FIND(...

Excel: Pull qualifying data from a worksheet, self expanatory, v company

self expanatory, v company, ogilvy: Do you want a macro do you want formulas How do I know it is a part I really can t provide any information unless I have specific information. You could send me a sample workbook either self expanatory or with an explanation of what you want and...

Excel: Pulling data from one cell to another., row numbers, word number

row numbers, word number, deep foundation: One solution can be using INDIRECT with the COUNTA function, that may work better or worse depending on your layout. An easy scenario is when you have the table starting on row 1, and there is nothing else below the table; this formula will return the last...

Excel: Pulling data from multiple workbooks into cover sheet, copy paste, specifics

copy paste, specifics, variables: It IS possible, but I d need more details. You can get the info by using an Event macro which fires when a particular cell is changed (the one in which you put 61 carramore, for example, and it would open the file, find the total row, copy & paste it somewhere...

Excel: Pulling Information from one Excel worksheet to another, full time equivalent, column c

full time equivalent, column c, d days: Hi Kim, Can you send me an example workbook? You can even send me your exact workbook but just have sample information in it. I don t want any real names or real information. But just like a picture is worth a thousand words , so to a spreadsheet is worth...

Excel: Pulling information from different sheets, macros, subject matter

macros, subject matter, syntax: Well, you just can t go to any resource per se to learn how to write a specific macro. You will need to learn how to write macros in general to grasp the concept, the subject matter, to learn the syntax, etc. before you can then write a specific macro to...

Excel: Pulling information from different worksheets, spreadsheet

spreadsheet: I really think that any spreadsheet that has data spread over 100 sheets is poorly designed. It should be possible to have all data on one sheet set out as a Table (ie Database) & then use Filtering, PivotTables etc to report the data. There is an example...

Excel: Pulling range data from one workbook on to another, key performance indicator, ppes

key performance indicator, ppes, cell reference: EXCEL won t do this. Because you have your external formula \Server1ppes-westPPUatExcel[SampleOnlineRpt02.xls]Online_Orders !B3 is in one cell then it can only retrieve one cell. EXCEL will not know how to put the data in the adjacent cells when...

Excel: Pulling time only out of a time/day stamp, type stamps, cell a1

type stamps, cell a1, pulling time: There are special functions for pulling out the date and time. If cell A1 contains a date/time: February 1, 2004 10:00 AM then you would use the following functions: Year(a1) to extract the year (i.e. 2004) Month(a1) to extract the month, (i.e....

Excel: Pulling txt files into large, premade excel file, student performance data, semi colons

student performance data, semi colons, seperate files: The issue I think is with the text file - you haven t said why you aren t able to read it - IF the file is within the size tolerance of excel, then file open, files of type, text files will allow you to open the data. IF the data is too big to fit into Excel,...

Excel: Purchase orders, master spreadsheet, macro button

master spreadsheet, macro button, bonuse: Hi Mark, I don t really have an answer for you at this time without a little more information. This also borders on the limits of my excel knowledge. You might be able to use a macro in the template and assign a button that would automatically look back...

Excel: Push Button Copy and Paste of one Spreadsheet to another, copy windows, cell a1

copy windows, cell a1, sequential number: You can try this variation of the code: Sub CopySelection() Selection.Copy Windows( Destination.xls ).Activate With Sheets( Sheet1 ).Range( A1 ).CurrentRegion.End(xlDown) .Offset(1, 0).Select ActiveSheet.Paste .Offset(1,...

Excel: Puzzle solving., left parenthesis, right parenthesis

left parenthesis, right parenthesis, commas: Yes, there is an easy way...here you go: 1) Copy and paste all of the pairs into one column in Excel....parenthesis, commas, and numbers. 2) Select that column and choose Data - Text to columns 3) Choose delimited 4) Select comma as the delimiter 5)...

Excel: page breaks in Excel, pagebreak, page breaks

pagebreak, page breaks, sum function: If it s SUbtotals via Data/Subtotals, there s a builtin option when you create them. If it s after any row containing a subtotal function, or a SUM function, you would need to run a Macro which would look for these functions & insert a pagebreak. If your...

Excel: page breaks when header changes, douglas m smith, brainbench

douglas m smith, brainbench, page break: Hi Phyllis, The only way I know of to do something like this is with Subtotals (Data menu) applied to a list. It has an option to Page break between groups . I m not sure if this is what you are looking for or not. Douglas M. Smith Brainbench MVP...

Excel: page number and row selection, location column, row selection

location column, row selection, hpb: Here is some code by Ron de Bruin to get the page number of the active cell: Function PageNumber() As Integer Dim VPC As Integer, HPC As Integer Dim VPB As VPageBreak, HPB As HPageBreak Dim NumPage As Integer If ActiveSheet.PageSetup.Order...

Excel: parsing excel, path text, import tool

path text, import tool, perl script: Shawn, I can help you with part of it right now, the batch file processing. That just takes a bunch of VBA code like this: Sub BatchProcess() Dim Files() As String Dim FileSpec As String Get file spec DefaultPath = ThisWorkbook.Path...

Excel: partial search field, partial search, mathematical background

partial search, mathematical background, log c: Hi Pete, How are you? 1. Type the part numbers in A1:A12 (e.g. 141,121,123,140,145,150,148, etc.) 2. Type part of the number you are looking for in C1 (e.g. 14) 3. Type in B1 =IF($C$1=A1/10^INT(LOG($C$1)), ==== , ) 4. Copy B1 to B2:B12 5. You...

Excel: partial transposing of a fill for barcoding, font barcode, barcoding

font barcode, barcoding, transposing: If I ve understood the question correctly then =OFFSET(Sheet1!$A$1,0,ROW(A3)-1) this goes in row three as an example - the A3 reference will alter to a different value dependant upon the row it is in. if you need further help or if I ve misunderstood...

Excel: partial transposing of a fill for barcoding, font barcode, indirect address

font barcode, indirect address, barcoding: In A1 of Sheet2 put in this formula: =Indirect(ADDRESS(TRUNC((ROW()-1)/4)+1,CHOOSE(MOD(ROW()-1,4)+1,MOD(ROW()-1,4)+1,MOD(ROW()-1,4)+1,MOD(ROW()-1,4)+2,MOD(ROW()-1,4)+1),TRUE,TRUE, Sheet1 )) in B1 of Sheet2 put in this formula =IF(MOD(ROW()-1,4)=2,Indirect(ADDRESS(TRUNC((ROW()-1)/4)+1,MOD(ROW()-1,4)+1,TRUE,TRUE,...

Excel: how to pass a formula down a column - NOT autofill (I think), autofile, error value

autofile, error value, colum: rd The autofile or copy/paste is the corect wqy to get the formula into the cells. The problem is that you have used a formula that will produce an error value when the variables are not in the cell. You need to change the formula so that it will not...

Excel: passing arrays, ogilvy, array

ogilvy, array: Passing arguments in the onaction command isn t officially supported. There is a trick you can use to pass a single argument but I doubt it would work for an array and since it is unsupported, no guaranteet that it would work in later versions of excel....

Excel: passing lots of values to a sub, excess number, ltrim

excess number, ltrim, msgbox: Hi Ali How are you? I do not know what you want to do ... there are so many ways to pass the parameters. But as you have suggested to pass the parameters as a string, please see the following example: Sub passingString() s = boolk1.xls + LTrim(Str(3))...

Excel: password, password recovery software, excel password recovery

password recovery software, excel password recovery, password recovery services: Hi John, Rest assured that your password can be recovered. There are numerous Excel password recovery services on the web. Most charge a nominal fee, and a few of them are free. Here s a password recovery service recommended by the MrExcel.com website:...

Excel: paste into a data filtered column, social security numbers, blank cells

social security numbers, blank cells, social security number: Jay: Here are the steps you will take... 1) Find a blank column and insert this formula into all the cells corresponding with a row you will be sorting. =ROW() This will place a unique key/row number into each row (or record). 2) Copy this new...

Excel: paste special transpose issue, cell a1, thanks in advance

cell a1, thanks in advance, copy and paste: You may try one additional step, I suppose that you are copying, and immediately after that paste special transpose. My suggestion is that after copying the cells, you select only one cell, that will be the leftmost cell of the new range, and with only one...

Excel: pastelink excel, conditional formatting, blank cells

conditional formatting, blank cells, paste link: If you want a row to be automatically inserted in Sheet2 when you insert a row in Sheet1, this cannot be done with paste link or any formulas. It can only be done with Visual Basic for Applications (vba) programming. If you don t know how to use vba, try...

Excel: pasting html into another excel spreadsheet, html descriptions, ubid

html descriptions, ubid, excel spreadsheet: Thanks for the steer - I ve tried copying a description of an Ebay item I m selling and then pasting it to another worksheet, which works fine - my description however may be shorter than yours (I m coming in at around 700 characters). It may be related to...

Excel: Where's my path option in footnotes, path option, hi bob

path option, hi bob, excel 2000: Path option does not exist in Excel 2000. You could do it with VBA (for each workbook you want to have this feature). Right-click the Excel LOGO near the File menu, select View Code, and enter this: Private Sub Workbook_BeforePrint(Cancel As Boolean) ...

Excel: payback period, cash inflows, thanks john

cash inflows, thanks john, outlay: John, Lets assume you have the values 0 1 2 3 4 in Row 1 (from A1 onwards) and -1000, 425, 300, 730, 800 in row 2 in Row 3, create the cumulative cost B2 = A2, B3 = A2+B2 and so on Your payback period would be =OFFSET(A1,0,MATCH(ABS(A2),B3:E3,1),1,1)+ABS(OFFSET(A1,2,MATCH(ABS(A2),B3:E3,1),1,1)+A2)/OFFSET(A1,1,MATCH(ABS(A2),B3:E3,1)+1,1,1)...

Excel: payment application vs aging process, payment application, aging process

payment application, aging process, thanks in advance: Hi Theresa, You can combine an IF statement and the date calculation to apply a change in cell value. Something like this - assuming the beginning date to compare (5 days after beginning) is in cell A3: =IF(NOW() =A3+5,50,0) I used the value of...

Excel: payroll s/s in excel, chip pearson, mathematical operations

chip pearson, mathematical operations, spreadsheet: Hi, Bridgette. Virtually every payroll worksheet question is already answered at Chip Pearson s site: http://www.cpearson.com/excel/overtime.htm Scroll down to the bottom for detailed information. I hope it helps! If not, you accomplish what you...

Excel: percentage, b2, a2

b2, a2: Okay, Judy. Maybe it s the way you re saying it (or I m saying it), but: If GROCERY is in A2 and Total income is in B2, then: =IF(ISERROR(A2/B2), ,A2/B2) Will give you the percentage of income that the grocery makes up. So, if: A2 = 10 B2...

Excel: percentage formula, target time, percentage formula

target time, percentage formula, swift reply: No problem - any blank cell has a value of zero, so will produce the error - so use an IF statement to trap it =if(CompletedTime=0,0,Calculation) I ve used a zero value for the default, but you could change this to - to show a dash, or have other text,...

Excel: percentage increase and decrease, percentage increase, b2

percentage increase, b2, fo: Ok, if I have understood correctly, you would like to know the increase between the sum of all the periods against the first value; in this case, 4+11+19 (34) versus 4. For that, you may use a formula like: =(SUM($A1:C1)-$A1)/$A1 Is this what you are looking...

Excel: percentage, c43, syntax errors

c43, syntax errors, denominator: There is a comma missing in both formulas, between the and the formula C74/C43, and that is probably giving syntax errors. In any case, I would do a formula like: =IF(C43=0, ,C74/C43) Just checking if the denominator is 0, and avoiding the division....

Excel: percentages, percentage formula, circular reference

percentage formula, circular reference, excel 2000: Rhonda Are you caculating in a different cell using the other two cells for you figures, if you are calculating and using the cell you are calculating in you will get a circular reference. If you can tell me some more on how the table is set up I will...

Excel: photos in excel, exel, hyperlinks

exel, hyperlinks, cells: The best you are going to be able to do is to have hyperlinks in Excel to the photos - the hyperlinks would be in cells, so would sort - photos are on the graphics layer, which is not at the same level as the worksheet. IF you use Access, you WILL be able...

Excel: picking text data from a file randomly, meaningful answer, good starting point

meaningful answer, good starting point, a11: If it s random, how would it be done automatically? I would need further information on the question to be able to provide a meaningful answer (how do we know which value we want to pick up would be a good starting point - maybe a lookup or offset function...

Excel: pie charts from spread sheets, expense categories, spread sheets

expense categories, spread sheets, pie chart: I don t know what your data looks like or how you ve created subtotals, I.E by your own formula for each category, or by using the subtotal feature, but what I d do is copy the data into another sheet then sort it into the categories that I wanted then do...

Excel: pie charts from spreadsheets, pie chart, pie charts

pie chart, pie charts, data source: Hey Joyce, It would probably be easier to explain using your specific spreadsheet, but let me see if I can answer your question. If you want to remove the subototals after the pie chart is created: -Click once on the entire pie chart to select it -You...

Excel: pivot Table - Excel, pivot table in excel, macro button

pivot table in excel, macro button, excel 2000: Hi nbp, I borrowed this solution from the microsoft.com page as it looks like your best solution is to create a macro that unprotects the sheet, refreshes the pivot table and then reprotects the sheet. You could assign a macro button on the sheet so that...

Excel: pivot table, prem anand, jaya prakash

prem anand, jaya prakash, pivot table in excel: Dear Tamizh, Pivot Table is one of the very best option in the excel. The main purpose of this pivot table to calculate or generate a report based on caluclations. Create a data list as follows: Name English Maths Science History Tamizh...

Excel: pivot table, pivot table, pivot tables

pivot table, pivot tables, source data: Hi, I think (if I have understood correctly) what you need to do is create a copy of original worksheet (may be call them Original & Data ) and then create Pivot Table out of Data . Make all changes to Data and update Pivot Table. Original will stay...

Excel: pivot table, sherry sherry, array formula

sherry sherry, array formula, pivot table: Sherry, Yes it is possible. The pivot table would get you half way there. You could copy the pivot table and then paste on top of itself with Edit= Paste Special and select values. Then use Index and match against you original data referencing the...

Excel: pivot table, wizard option, format cells

wizard option, format cells, pivot table: That is very odd. What version of Excel are you using? Since wizard does not appear in the normal location, you can also do this: 1) Click anywhere in the Pivot Table. 2) Click on Data- Pivot Table and Pivot Chart report This will bring up the...

Excel: pivot table, pivot table, paste values

pivot table, paste values, table values: Sorry, once you do the copy paste-values, you can t turn it back into a pivot table. The pivot table reflects the underlying values of the data it s based on. That s why you can t change its values; any change must be made in the underlying data. It s possible...

Excel: pivot table, pivot table, pivot tables

pivot table, pivot tables, source data: I don t think there is any other way than to re-create the pivot table. You might try just refreshing it (as if the source data had changed though in your case it seems it has not) if the pivot table is still in a sheet. This will prevent you from having...

Excel: pivot table, pivot table, pivot tables

pivot table, pivot tables, row heights: You can t convert a range like you have back into a pivot table. The data MUST come from a list, so if you want to make changes to the values, it must be done inside the original data, not inside the pivot table. You can t insert/delete rows, but you can change...

Excel: pivot table, pivot table, pivottable

pivot table, pivottable, pivot tables: Sorry, but I do not give my email address here and other public news groups. I get enough spam as it is. If you want to use an online service such as www.yousendit.com I will have a quick look, but as i have already said, you can t reverse what you have done...

Excel: pivot table, pivot table, macro tools

pivot table, macro tools, pivot tables: Hi Sue, Good question. I believe the reason why the data fields of a pivot table are locked against editing is that to edit these fields would cause the pivot table to become inconsistent with the source data, and at the very least creates the impression...

Excel: pivot table, pivot table, paste values

pivot table, paste values, pivot tables: OK, that helps - you are basically trying to change the data source as far as I can tell, so right click the pivot table, select wizard, use back till you get to the data area, then change this to the new data region and OK it - should now give you the new...

Excel: pivot table, pivot table tutorial, exclaimation point

pivot table tutorial, exclaimation point, pivot tables: Sue- Pivot Tables do not act the same way as regular Tables. The reason is, Pivot Tables do not have any data in them. They only reflect the data that is in a Table. This is why you can t delete data or rows from your Pivot Table - there is no data to delete....

Excel: pivot table drop down list keeps old items, pivot table, table updates

pivot table, table updates, drop down list: hi try to do this. redefine the data list without the column Date . then refresh the table and after doing that redefine again the data list with the column Date that should do it. sincerely i don t know another way to do that. if i find another way...

Excel: pivot table layout, hlookup, proper layout

hlookup, proper layout, data layout: Hi Radu How are you? An alternative way is to 1.Consider that your original data (liabiliy 50, 1, 5, 9 ....) is in B5:F12 Hope this helps Adelaide 1. Type the week numbers 1, 2,... 52 in C16:BA16 2. Type the expenses in A17:A23 3. Type in B17...

Excel: pivot table problems, pivot table, pivot tables

pivot table, pivot tables, resumes: Grace I m really not very good with pivot tables. I believe you will get a better and quicker answer if you ask another allexperts person. Ck the resumes and maybe someone is expert in the pivot table area. Sorry I couldn t be of more help. Richar...

Excel: pivot table, pivot table, windows 2000 professional

pivot table, windows 2000 professional, agy: Debbie, I am guessing you are asking about Pivot Tables because you tried and couldn t get it to work. That s because PTs summarize mathematically, which isn t really what you want. However, you can still use a PT to summarize this data. You can place...

Excel: pivot tables, password cracking software, excel passwords

password cracking software, excel passwords, note excel: Hi Alan, I am not sure if this is exactly what you want. But you can password protect the worksheet and would not be able to change anything on the pivot table. To do that you would activate the pivot table worksheet and then go to the Excel menu and...

Excel: pivot tables, how to use pivot tables, pivot table

how to use pivot tables, pivot table, excel spreadsheet: Hi Betsy, The pivot table does not update automatically. Evry time you update your data on the main spreadsheet...you will have to right click on your pivot table and choose refresh data . This way the pivot table pulls the latest updated data from your...

Excel: pivot tables - % on Grand Total column ONLY, cell a2, pivot table

cell a2, pivot table, pivot tables: Al, Great question - never thought of that before. Unfortunately, I don t have a great answer. There should be a way to do it, but I can t find a way to do it through the PT right now. I ll continue to research it, and it I come across an efficient solution,...

Excel: pivot tables, pivot table, pivot tables

pivot table, pivot tables, office 2000: Hi Jane, I just tried the normal steps to mark a data area as unlocked then apply protection. The error message, as I m sure you ve seen, will not allow the working of a pivot table on a protected tab. Is it possible with your design to put the pivot...

Excel: How to place a check mark next to an item in the submenu of a custom made menu, submenu, help file

submenu, help file, cheers: Its not something I ve had cause to do and the help file isn t very helpful - it SHOULD be possible to refer to the button by it s name or number - you MIGHT be able to find this out by recording a macro using the button. Alternatively, you will almost certainly...

Excel: placing button on spreadsheet, programming code, spreadsheet

programming code, spreadsheet, constants: Hi, Jim, I can t help you set up a button to do what you ask, since that requires writing some programming code. But I can tell you how to do it very easily from the keyboard. You ll be surprised how simple it is! 1) Click any single cell on your spreadsheet....

Excel: placing pictures in a table, formatting, headers/footers, page setup menu, headers footers

page setup menu, headers footers, decorative bars: Sorry about the delay in replying - for some reason I couldn t see this question on the system until now. Formatting is a problem as I IMAGINE that you have used styles, and the style in the new document will always take over - you can try using edit paste...

Excel: playing video clips in excel, code execution, route information

code execution, route information, video avi: Try this untested code: Private Declare Function PlaySound Lib winmm.dll _ Alias PlaySoundA (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long Const SND_SYNC = &H0 Const SND_ASYNC = &H1 Const SND_FILENAME =...

Excel: playing video clips in excel, word application, route information

word application, route information, video avi: David, while I can t truly help, I might suggest that you use the Windows camcorder to do this. You will need to set it up as an Object, and attempt to control the new object. Like I said, I can t really help because I haven t done this with the camcorder,...

Excel: pmt function, douglas m smith, estate calculators

douglas m smith, estate calculators, couple versions: Hi Marie, On a regular calculator, this is a very cumbersome calculation. Here are a couple of links to web pages that show a couple versions of the formula: http://pines.hsu.edu/faculty/worthf/libarts/chap05/5c/sld002.htm http://www.1728.com/loanform.htm...

Excel: pop up message, target address, type mismatch

target address, type mismatch, excel macro: Then that means that the data in one of the cells in yor loop is not what you are code is expecting it to be. And therefore you are trying to pass wrong data to your variable you get the error. Since I can t see your data then I can t tell you which...

Excel: pop up message using macro, question sir, excel macro

question sir, excel macro, column c: You say an error occurs, but if isdate(oCell.Value) then If Int(oCell.Value) = Int(Date) Then MsgBox Please dispose & _ CStr(oCell.Offset(, -2).Value) & , & _ CStr(oCell.Offset(, -1).Value) End If End if the check to see if the cell contains...

Excel: populate one cell based on another cell, vlookup formula, data validation

vlookup formula, data validation, combo boxes: You can do this with the VLOOKUP formula and an auxiliary table . Step by step: 1.- In a separate worksheet (let s say sheet2), fill the range A1:A7 with the project names (or if you are using combo boxes instead of data validation, the number that it returns...

Excel: populate data from 1 worksheet to another worksheet, jane doe, column name

jane doe, column name, columna: Ling, you can put that formula in any cell you like, except one that is locked due to Validation. Until you remove the Validation list from column A, you won t be able to put anything else in that cell. With the formula, though, you don t need the Validation,...

Excel: populate one sheet to another, example sheets, sheet1

example sheets, sheet1, vlookup: I understand the concept of relational database, but I am still unable to figure out what you need. When you sort in sheet1, the sorting only affects sheet1, and the sheet2 remains exactly the same as it was before, with the same level of synchronization...

Excel: populating different sheets, false selection, copy windows

false selection, copy windows, mike cobb: Mike, I think you have several options here. It still depends mostly on how similar the Region sheets are to the Division sheet. If you can t paste to both of them at the same time - meaning the data does not go into the same cells, then the following logic...

Excel: How to populating textboxs based on a combox, listindex, combox

listindex, combox, combobox: If the selection from the combobox is sourced from A1:A10, then the combobox s Listindex will point to which was chosen as an offset - a number from 0 to 9. If the addresses are in B1:B10, you can pick them up with this in the Combobox_change event: Me.Textbox1.Text...

Excel: all possible combinations, combinations, thanks in advance

combinations, thanks in advance: Jason Sorry to get back to you so late, i was away from computer yesterday. I don t know of a way to do your calculation--I m sure excel can do it but i don t know of a way to get started writing the formula. sorry I couldn t be of more help Richard...

Excel: all possible combinations of a sum, number combinations, nextl

number combinations, nextl, cout statements: Just change the prompt, no?: Sub getTotal() Cells.ClearContents tot = Application.InputBox( Enter # to sum to. Enter values over 95. , Type:=1) For i = 1 To 39 If NG(i) Then GoTo Nexti For j = i + 1 To 39 If NG(j)...

Excel: possible excel formula, inventory count, excel formula

inventory count, excel formula, pivot table: Tracey, I believe I can help but you left out a bit of information. I ll have to make some assumptions. If the assupmptions aren t relavent please don t hesitate to give more detail or even send your worksheet to me @ greg@gsquaredtech.com. Here we...

Excel: all possible numerical combinations, number combinations, integer numbers

number combinations, integer numbers, excel worksheet: Hi Len, Please forgive me for my slow response. I have been out of town on vacation all week, and found today when I returned that I had mistakenly entered the month for my vacation as 09 (September) rather than 10. I was horrified to find my AllExperts...

Excel: Is it possible to perform a two way lookup, rangle, a26

rangle, a26, index values: I assume you received my first answer so this is a correction: Sorry, I screwed that up. You want to start in A2 and B1 for the matches to avoid having to subtract 1. say I had names down column A (starting in A2 (A2:A26) and dates in row1, starting...

Excel: possible if/then question, conditional formatting, menu format

conditional formatting, menu format, false positives: You can use conditional formatting for this. For example, select the entire row 2, go to menu Format- Conditional formatting, and enter the following conditions there: Cond. 1: Formula is: =$B2= (format red pattern) Cond. 2: Formula is: =YEAR($B2) 2000...

Excel: Is is possible to subtract 1 from every value on a worksheet?, survey software, cells

survey software, cells, jonathan: Jonothan You can use the paste/special/value and accomplish exactly what you want. Here is what you do Put a one (1) in a cell somewhere on your worksheet. Make that cell the active cell. Richt clic and select copy. Paint the cells that you...

Excel: Is it possible to subtract 1 from every value in a worksheet?, cell a1, visual basic for applications

cell a1, visual basic for applications, survey software: This can be easily done using Visual Basic for Applications (vba). Since you don t say whether or not you know how to use vba, I won t go into that. You could create a range filled with cells that each contain the number 1, and then copy, pastespecial,...

Excel: Is it possible to subtract 1 from every value?, douglas m smith, brainbench

douglas m smith, brainbench, survey software: Hi Jonathan, Yes! Enter 1 in a cell and then copy it. Select the cells you want to subtract 1 from. Now select Paste Special from the Edit menu and a dialog box will appear. In the Paste section, click Values. In the Operation section, click Subtract....

Excel: Is it possible to get a Word macro to save an open Excel file, excel csv file, csv data

excel csv file, csv data, word macro: I think I d do something like this sub CheckExcelCSV on error goto Err_mark Dim myobj As Object Set myobj = GetObject(, Excel.application ) Dim windvar For windvar = 1 To myobj.Workbooks.Count If Right(LCase(myobj.Workbooks(windvar).Name), 4) = ...

Excel: possibly crosstraining into Excel, ms office suite, introductory book

ms office suite, introductory book, buying books: Hi Emi How are you? I really recommend you to get into Excel. It is big fun. An introductory book is always helpful. Also you look for personal tutoring or generic tutorials on in the Internet. Actually, I am doing some tutoring but in Portuguese :-(...

Excel: predictive text in combo boxes, matchentry property, data validation

matchentry property, data validation, combo boxes: Hi Paul, If you are using the standard ActiveX comboboxes (the ones that come from the Controls Toolbox toolbar) you should already be seeing this happening. It is called autocomplete and is the default setting for this type of combobox. It is set via...

Excel: How to preserve a spreadsheet's info -New Q, target range, speedy reply

target range, speedy reply, closing stock: Sorry, I still don t understand. Mainly, it s the word this in your question. What are you referring to when you say this in: Can this now be linked to a button on the new page to close the old page Can WHAT be linked to a button...? The code I gave...

Excel: How to preserve a spreadsheet's info, target range, closing stock

target range, closing stock, sensitive info: you can right-click the sheet tab with the info which should not be changed, select View Code, and enter something like this: (this assumes the sensitive info is in B1:B10) Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target,...

Excel: previous sheet cell 1, cell a1

cell a1: What you want to do makes sense, and I m not sure why it s not working. If you re working with cell A1 on each sheet, the formulas should look like this: sheet 1, cell A1: contains a value, not a formula sheet 2, cell A1: =Sheet1!A1+1 sheet 3, cell A1:...