selection dialog, excel macro, ogilvy: http://www.j-walk.com/ss/excel/tips/tip29.htm should show how to display the folder selection dialog. here is the level above that with other utilities which might help http://www.j-walk.com/ss/excel/tips/userformtips.htm and the one above that ...

Excel: Excel Macro Question, stratgy, shees

stratgy, shees, excel macro: Ok so one stratgy would be to open the new the target work book and detele all unwanted work sheets but leaving one remaing work sheet. Rename this work sheet temp Then move the shees you wish to transfer to the target sheet with code BEFORE the dummy...

Excel: Excel Macro Question, excel macro, excel 97

excel macro, excel 97, thanks in advance: Try this: Dim L As Byte for each x in selection if offset (0,-1).font.bold = true then offset (0,-1) = offset (0,-1). & 1 L = Len(Trim(offset (0,-1))) offset (0,-1).Characters(L, 1).Font.Subscript = True end if next x Curious though - how...

Excel: Excel Macro for rounding and superscripting, hypen, excel macro

hypen, excel macro, question thanks: You re welcome. Yes, there is almost always a macro solution to anything in EXCEL, and code can be written to do what you want/need, but the formula suggestion will still work, if it can be applied to your workbook. AFTER you use the formulas solutions...

Excel: Excel & Macro's, variant types, input values

variant types, input values, excel macro: Your functions were not carried forward in these subsequent messages so I don t know why some values did not work. To delcare variables on the same line and to give them each their own type then: Dim X as Intger, Y as Integer, Z as Double If you use...

Excel: Excel & Macro's, range input, excel macro

range input, excel macro, value error: If you simply type this into the immediate window, you ll begin to see the issue: ?1150*34 Change your macro to: Public Function kalkvekt(n As Range) Kilos Weight input n will always be a number between 800 and 1500 Dim n1 As Long, n2 As Long,...

Excel: Excel Macro security, macro security, excel macro

macro security, excel macro, second computer: I don t know since that is a setting for EXCEL on each PC and not for each workbook opened. I am assuming all three files have macros in them and each of the three files you are opening on each different PC are the same files or same versions of the file...

Excel: Excel Macro to start Word, ms word document, boilerplate text

ms word document, boilerplate text, spreadsheet functions: I would SUGGEST that the easiest way to do the word document is to use bookmarks as these will enable you to specify where text is to go. The rest would be along these lines:- Dim appwd As Object On Error GoTo notloaded Set appwd = GetObject(, Word.Application...

Excel: Excel Macro using VBA, project workbook, excel macro

project workbook, excel macro: Hi You can solve this problem by creating a macro in the VB editor using the workbook open event as follows Private Sub Workbook_Open() Sheets( Sheet1 ).Activate Range( A1 ).Select End Sub Hope this helps. If you need more assistance please...

Excel: Excel Macro Writing, option buttons, command button

option buttons, command button, excel macro: A command button can only run one macro - however, that macro can show a userform in which there are other buttons to press - each to run a specific macro. Or, they can have checkboxes, etc. For example, when you use Tools/Options, that runs ONE macro (internal...

Excel: Excel Macro, print dialog box, dialog boxes

print dialog box, dialog boxes, excel macro: To display an EXCEL dialog box, you use the following code as an example Application.Dialogs(xlDialogPrint).Show to display the Print dialog box. EXCEL does not provide ALL of its dialog boxes to be accessed or shown this way and the Edit Find is one...

Excel: Excel Macros, excel macros, stockholm sweden

excel macros, stockholm sweden, len: Hi Kevin This code will do it for you in column A Sub ChangeFirstDigit() Dim lngLastRow As Long, lngRowNum As Long Dim strCellContent As String, intLength As Integer Dim strNewCellContent As String lngLastRow = Range( A65536 ).End(xlUp).Row...

Excel: Excel Macros, excel macros, blank cells

excel macros, blank cells, inputted: There is no need to delete data from the last of data to the end of the spreadsheet. The last row of data is the last row of data, period. So, I am confused. To reset the end of the spreadheet, simply use the same code again after any data has been added...

Excel: Excel Macros, address customer, record button

address customer, record button, choosing a name: Fran, I think this could be fairly simple. You would start by using Find, so something like this: Range( A1:A1000 ).Find(What:=InputBox( Who do you want to search for? ), LookAt:=xlWhole).Select which would ask the user for a name that must match...

Excel: Excel Macros, douglas m smith, code bloat

douglas m smith, code bloat, excel macros: Hi Ian, With all the data you have coming and going, it might not be the macro code bloat (more on that in a minute though). On your data sheet(s), hit Control-End. This will take you to the last active cell on the worksheet. If it is significantly...

Excel: Excel Macros, bangalore india, excel spreadsheet

bangalore india, excel spreadsheet, keystrokes: Raina I m not much help because I m definatly not a macro expert, but I ll make a few suggestions. Most of the key strokes that you make to perform a function can be recorded in a macro and then when the macro is played back later it performs the function...

Excel: Excel Macros, excel macros, lotus 1 2 3

excel macros, lotus 1 2 3, excel user: Press ALT F11. Right click on any worksheet in the top left hand corner of the screen (the Project Explorer - if not visible then CTRL R to display it) Copy and paste the entire code I sent you in the window on the right. Back in EXCEL, click Tools, Macro,...

Excel: Excel Macros, character length, monetary values

character length, monetary values, gyula: Try this as a worksheet function. It always returns 23 characters (padded with spaces or truncated if too long). Function Text23(str As String) Dim s As String * 23 s = str Text23 = s End Function Call it with =TEXT23(A1) for example. you can...

Excel: Excel and Macros, target value, target address

target value, target address, cell a1: Yes, you do it by putting code in the worksheet object, using the Visual Basic Editor. For example, here s the code that runs when you select the cell A1 of the sheet; the code puts the date/time in that cell: Private Sub Worksheet_SelectionChange(ByVal...

Excel: Excel Macros, excel macros, filename length

excel macros, filename length, excel macro: sree Sorry I haven t gotten back to you I have been out of town without computer. I am not a macro expert, and I can t really help you with your question. I suggest you reask the questions of another allexperts person and I m sure you can get a good...

Excel: Excel Macros, excel macros, vbe

excel macros, vbe, f8: This is not something you want to record. It would record each step. Simply use Alt/F11 to get to the VBE, then use Insert Module, then enter: Sub TryItYoullLikeIt() For Each thing in range( A1 ,Range( A65536 ).end(xlup)) thing = 1*( 7 & mid(thing,2))...

Excel: Excel Macros, excel macros, colunms

excel macros, colunms, blank row: Sorry, I don t understand what you mean by ...juggles all the other data... I assume you mean that the data does NOT sorted the way you need. The only way that can happen is if your data is not contiguous when you use the .CurrentRegion method since, as...

Excel: Excel Macros, excel macros, message box

excel macros, message box, cheers: Well, again you can t have both since you said that you want to prevent a user from closing a workbook. Once that prevention has been established then the other thing you wanted done when the workbook is closed will not be done since you have prevented...

Excel: Excel Macros, macros in excel, keyboard shortcut

macros in excel, keyboard shortcut, menu tools: Vivek Atodaria, Select the following menu: Tools | Macro | Macros. In the box that opens select the macro which is not running with the shortcut key. Click the options button and make sure there is a shortcut key listed. If there is, and it still isn t...

Excel: Excel Macros, excel macros, excel spreadsheets

excel macros, excel spreadsheets, adobe acrobat: Hi Martha, You can convert Excel spreadsheets to pdf files but I believe they will not have formulas and macros that work anymore. You would need to purchase Adobe Acrobat to make PDF files and you would then need a convertor that you can purchase below....

Excel: Excel Macros, resumes, thanks for your help

resumes, thanks for your help: Kevin I m definately not macro man--however I m pretty sure that you can run the macro in all of the worksheets, I ve seen it done, but I don t know the procedure. I d suggest you reask the question of another allexperts person and ck the resumes for...

Excel: Excel - Macros, douglas m smith, excel macros

douglas m smith, excel macros, brainbench: Hi Cindy, Macros are stored as part of the Excel file they were written in. It is not a separate file. By copying the spreadsheet file to other computers, the macros go with it. Douglas M. Smith Brainbench MVP for MS Excel www.brainbench.com www.abundant-solutions.co...

Excel: Excel Macros, xls spreadsheet, xls workbook

xls spreadsheet, xls workbook, macro in excel: You re trying to do this from the wrong place. You need to go to where the code lives, the VBE (Visual Basic Editor), by pressing ALT F11 from EXCEL. In the top left corner, you should see a small window named Project - VBA Project. If not, press Ctrl...

Excel: Excel Macros/Colouring, conditional formatting, excel macros

conditional formatting, excel macros, cell shading: I would suggest that applying the conditional formatting to the entire column would probably fix the problem IF conditional formatting will do it for you - basically, you cannot lock the colour to a particular entry, only to a cell, BUT you can use conditional...

Excel: Excel Macros - VBA, line graphs from, excel macros

line graphs from, excel macros, line graph: Hi Lisa, To always reference the currently active worksheet you can modify the SetSourceData and Location lines as follows: ActiveChart.SetSourceData Source:=Range( B1:U6674 ), PlotBy:=xlColumns ActiveChart.Location Where:=xlLocationAsObject,...

Excel: Excel Macros / VBA, joe shmo, csv files

joe shmo, csv files, excel macros: Steve unfortunately, I don t think that moving it to VBA code would be a simple edeavor at all. Creating these megaformulas can be just as tricky in VBA as they are in the workbook. What I would suggest though is opening the source file so the path won...

Excel: Excel Macros, excel macros, blank cells

excel macros, blank cells, alltogether: Kevin, unfortunately, for an example like this, the recorder just can t make it happen. The recorder is very useful for recording repetitive tasks that take no alterations from the user to repeat, but in your example, the recorder would disregard the original...

Excel: Excel Marco, c cells, marco 4

c cells, marco 4, hyper: Hi Chiu1Chiu Hope you are well. Please try the following Sub openFile() Dim F As Range Set F = Sheets(1).Range( A2 ) Filename = F.Cells(1, 1) rem you have to replace this statement by the one to open the MS picture ... I do not have this application...

Excel: Excel Memory Error, referenced memory, memory error

referenced memory, memory error, microsoft 2000: I would TRY saving it as Excel 95/97 (an option in the Save As dialog box under Save As Type), and see if that cures the problem. If it does, you can reopen it and save it as a normal workbook. If it doesn t, it may be corrupted and you d well be advised to...

Excel: Excel Menu Editor, toolbars tab, excel visual basic

toolbars tab, excel visual basic, visual basic for applications: Menu editor os pretty old technology and no longer exists. You can manually move items on & off toolbars by using view/toolbars/customize, then dragging tools from the command tab s commands listbox onto toolbars and/or dragging tools off the toolbars (which...

Excel: Excel Menu Editor, editor excel, excel visual basic

editor excel, excel visual basic, visual basic for applications: ...Where can I get more information on the above... I d suggest reading that chapter to find out more about a Menu Editor. EXCEL does not come with such a tool or feature, per se. You can create your own menus in EXCEL and/or in VBA and it is perhaps...

Excel: Excel Menu Editor, microsoft excel 97, shortcut menus

microsoft excel 97, shortcut menus, excel visual basic: Last version to have a menu editor was Excel 95 Now there are are commandbars: http://support.microsoft.com/default.aspx?scid=kb;en-us;166755 File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R) Excel 97 (and later) File Name:...

Excel: Excel Menu Editor, excel visual basic, visual basic for applications

excel visual basic, visual basic for applications, menu editor: jerome I m definatly not A VBA expert, and my excel version is older than yours. I don t seem to have the Menu Editor built into my system either. I d suggest you reask the question of another allexperts person, and I m sure you can get a better answer...

Excel: Excel Menus, excel spreadsheets, eric hope

excel spreadsheets, eric hope, excel spreadsheet: Hi Eric Hope you are well Sorry but I do not know what happened to your Excel spreadsheet. I need to know which version you are running. Anyway, just to tackle the problem please do the following: 0. Select Sheet1 1. Press Alt T and if the tools menu...

Excel: Excel and Merge, vertical button, vertical columns

vertical button, vertical columns, staus: The code I supplied will merge/unmerge the selected cells. I can t write any different code because I have no idea what you are doing.In any case, I would strongly recommend you forget merging cells because they will cause problems eventually either with calculations...

Excel: Excel Mirror Sheets, macro menu, menu toolbar

macro menu, menu toolbar, paste link: Instead of creating extra worksheets and worry about formatting and updates, why don t you just write VBA code that hides a certain column (columns). You can then attach that to a macro, menu, toolbar or anything like that and run it. Sub HideColumn(columnletter...

Excel: Excel Mirrror Sheets 2, google, gyula

google, gyula, lnk: Hi there, You could use the Workbook_SheetChange event. Please see for code: http://groups.google.ca/group/microsoft.public.excel.misc/browse_thread/thread/9aee4039e17f59ff/0d1ea605f0fedf14?lnk=st&q=Excel+link+master+worksheet+views&rnum=1&hl=en#0d1ea605f0fedf14...

Excel: Excel (in MS Office 2000, ms office 2000, xlstart folder

ms office 2000, xlstart folder, microsoft support: Hey Trevor, Wow, that sounds pretty annoying :( Unfortunately there could be a few reasons why this is happening. I have seen that a common reason is that the Excel Toolbar file is damaged (Excel.xlb). I have taken these instructions off of the Microsoft...

Excel: Excel Multi-Condition Formula, false values, a14

false values, a14, aidan: You can use SUMPRODUCT to handle this situation - which is a SUMIF multiple conditions being fulfilled. It basically works like this =sumproduct(--(FirstRange=FirstTest),--(SecondRange=SecondTest)) extend the range and test as necessary, and the test...

Excel: Excel - Multiline Cell to Columns, ms office professional, using ms office

ms office professional, using ms office, split cells: Hi Shaun, The built-in VBA Split function enables parsing the lines using the linefeed character separators. The following macro code will break out up to four lines in column A into columns A:D, starting with row 2 (I m assuming row 1 contains headers)....

Excel: Excel - Multiline Cell to Columns, ms office professional, using ms office

ms office professional, using ms office, right arrow: Select one of these cells and hit F2. Then hit control+home. Then hit End. Hold down the shift key and press the right arrow once. Now you should have selected the line break character. Hit control_c to copy that. Now select your entire column with addresses....

Excel: Excel Multiple Axis/scales on a scatter plot, format cells, time plot

format cells, time plot, scatter plot: Yes, but it s a bit involved... you need 4 columns: time, degF, time, degC Select 1st 2 columns, make a chart choosing custom types tab and Lines on 2 axes . Select 2nd 2 columns, drag the BORDER of the selection right into the chart. Next, select Chart/Chart...

Excel: Excel for macintosh, i book g4, cell c2

i book g4, cell c2, window software: Hi Egon, There is nothing wrong with your formula. =C2*15/100 That formula would return 15% of the value in cell C2. But the result is not a percent other than it is 15% of the value in cell C2. When you format a cell to show percentage in Excel it...

Excel: Excel macro, inputbox, excel macro

inputbox, excel macro, research papers: It seems I have already answered this question. Instead of using Find and Find Next, consider instead using a loop to loop thru all of the cells and find each occurrence that way. Here is a simple example to get you started. For X = 0 to 100 If Range(...

Excel: Excel macro, excel macro, step 1

excel macro, step 1, thanks for your help: This macro will delete every row where any cell in that row contains PID: Sub DeletePID() dim n as integer On Error resume next for i=activesheet.usedrange.rows.count to 1 step -1 n=0 n=rows(i).find( PID ,lookat:=xlpart).column ...

Excel: Excel macro, microsoft excel macro, macro work

microsoft excel macro, macro work, set time: In theory it is possible to have a macro work at a given time - the macro itself would have to be constantly running, but that shouldn t be a major issue - can you clarify clear cell quantities - does this mean clear the contents of a given range of cells,...

Excel: Excel macro, columm, macro in excel

columm, macro in excel, information input: Since I do this full time for a living then typically I don t do little projects and this is one is considered a little project. Therefore, I charge a minimum of $300 for any small project and this project, from what I know about it so far, should not exceed...

Excel: Excel macro, w 167, csv file

w 167, csv file, excel macro: I wouldn t do this with a macro, but use a pivot table instead. You can use a calculated item to create the field that will contain your data, by using a formula like: =(OT=WK)*OT on the calculated field. I have uploaded an example of this in the following...

Excel: Excel macro, keyboard shortcut, blank cell

keyboard shortcut, blank cell, value move: I would use either the specialcells(xllast) to get the last cell (which is the same as doing a ctrl end). HOWEVER, if people fiddle with sheets and put data in and then delete it this can be a problem. Therefore the offset function coupled with a count function...

Excel: Excel macro, minute intervals, lotus 1 2 3

minute intervals, lotus 1 2 3, control break: Grant: To spell out the solution to a user who knows nothing about VBA will require a LOT of typing, far too much for the amount of my free time I can devote to it. If you think about it for a moment, you will realize that it will require a great deal...

Excel: Excel / macro, cell d1, cell d2

cell d1, cell d2, cell a2: You re right - the only way is with a macro. You need to use event driven macros which run automatically when an event occurs. The event in this case is the Selection_Change for the worksheet or the WorkSheet_Calculation. It will take too much typing...

Excel: Excel macro, excel macro, xf

excel macro, xf, delimited: Lynn, You do not need a macro to do this. Simply highlight the cells in question. Click Data, then Text to Columns. Select Delimited, and the Next. Now select the Space check box, and then Finish. Note, to avoid overwriting data, ensure nothing...

Excel: Excel macro, excel macro, autofilter

excel macro, autofilter, aidan: The code that you have written is in a format not familiar to me - I m going to have to study it in more depth to see if I can use it for my own projects - however, for the job that you want to do, it sounds as though Data, Filter, AutoFilter would do it...

Excel: Excel macro, blank cell, excel macro

blank cell, excel macro, curser: You first need something that references a cell, rather that be a direct reference, lile Range( A1 ), or a variable that references a range, like Dim ThisCell as Range Set ThisCell = Range( B1 ) Depending on which direction you want to go, then use...

Excel: Excel macro, colums, simplistic approach

colums, simplistic approach, excel macro: If you are asking for a macro solution then sorry, typically I do not write macros for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you...

Excel: Excel macro, statistical data analysis, seed weight

statistical data analysis, seed weight, excel macro: HI Sarada Thank you for sending me your worksheet. I see it covers a large amount of raw data with associated statistical analyses. Firstly the request you have made to duplicate your current product by means of code is more than a simple piece...

Excel: Excel macro for comparing cells, conditional formatting, format button

conditional formatting, format button, excel macro: Keith, Select A1:A20 with A1 as the activecell of the 20 selected cells Do Format= Conditional formatting in the menu In the first dropdown in the resulting dialog, change Cell Value Is to Formula is Put in the formula =countif($D$1:$D$45,$A1) ...

Excel: Excel macro date, firstdate, macro in excel

firstdate, macro in excel, copyto: The precise code would depend on specifics like: once you identify which dates are =20 days old, where do you want to paste these dates? What other data do you want to paste with them? So I ve set up a little example. I m assuming the dates start in cell...

Excel: Excel macro date, macro in excel, string code

macro in excel, string code, excel macro: At the end of your code shown here: ActiveSheet.UsedRange.Autofilter Column:=6, Criteria:= =20 For Each oCell In Intersect(ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible), ActiveSheet.Columns(1)) Now do somthing here: EmailThisOne...

Excel: Excel macro to e-mail attachment, mail attachment, addressee

mail attachment, addressee, active document: CARLOS I am definately not a macro expert and can t answer your question very quickly. I suggest you reask the question of another and I m sure you can get a good answer much quicker than I will be able to give one to you. Sorry I couldn t be of more...

Excel: Excel and macro possibilities, himt, menu choice

himt, menu choice, print icon: A himt in the right direction is that you will have to remove the print icon from the toolbar, remove the Print menu choice from the File menu snd reassign the hot key (CTRL P) to prevent printing a manual way. You can t control the BeforePrint Event ONLY...

Excel: Excel macro question., excel macro, header location

excel macro, header location, conciseness: Owen, the code below is fine for conciseness. As for the hard return, you just need to add the proper character, which happens to be 10, as follows: Sub UpdateFooter() Dim varProjName As String Dim varClient As String Dim varProjNum As String...

Excel: Excel macro to split cell content, feed solution, cell c1

feed solution, cell c1, cell content: No need for a macro. Use these formulas on the 2nd sheet (assuming the info you want to split up begins in sheet1 cell C1). A1: =LEFT(Sheet1!C1,FIND(CHAR(10),Sheet1!C1)-1) B1: =LEFT(MID(Sheet1!C1,LEN(A1)+2,255),FIND(CHAR(10),MID(Sheet1!C1,LEN(A1)+2,255))-1)...

Excel: Excel macro that takes a user inputed filename, program outputs, accounting system

program outputs, accounting system, excel macro: Consider re-thinking your solution and approach to the idea. Have a file that is always used as the accounting report export. Prompt the user to open the original file the accounting system generates. Then when the code opens that file the code would perform...

Excel: Excel macro unexpected outcome, column references, unexpected outcome

column references, unexpected outcome, excel macro: No, I don t accept emails. But when you record a macro the code works on the specific cells you select. The cell/row/column references are not dynamic and will always refer to those specific cell/row/column references captured during the recording process....

Excel: Excel macro, sheet tab, excel macro

sheet tab, excel macro, popup menu: Kevin Yes, for excel 2000, use the Change event of the worksheet. Tip: a quick way to get to any sheet s code page is to right click on the sheet tab and select View Code from the popup menu. Have a look at the sample on this link http://cpearson.com/excel/events.htm...

Excel: Excel and macros, track changes, macros

track changes, macros, good luck: Yes. After you write the code for your project do the following. Click on the Tools - Protection - Protect and Share Workbook. You will be prompted with a Protect shared workbook dialog box check the box to Share with track changes and enter a password...

Excel: Excel macros, time and patience, excel worksheet

time and patience, excel worksheet, cells: Hi Neelesh, You probably need something like this: Function reduceFactor() Dim xls1 As Excel.Worksheet Dim xls2 As Excel.Worksheet Dim i As Long Dim j As Long Set xls1 = Excel.ActiveWorkbook.Worksheets( Sheet1 ) Set xls2 = Excel.ActiveWorkbook.Worksheets(...

Excel: Excel macros, table capability, excel macros

table capability, excel macros, array formulas: There are several ways to approach this problem. One method would be to create a dummy column that concatenates the first five values in each row, then use countif formulas against this dummy column. (see sample formula at the end) Another might involve...

Excel: Excel macros, excel macros, free time

excel macros, free time, matrix: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros...

Excel: Excel macros, excel macros, max range

excel macros, max range, looper: Sorry for the delay in reply - something like Sub checker() Dim returnvals As String Dim myrar As Range myvar = A1:C4 change this to the range you want to check or an input box or the current selection Dim MinVar, MaxVar MinVar = WorksheetFunction.Min(Range(myvar))...

Excel: Excel macros, excel macros, cell a1

excel macros, cell a1, number id: OK, if I have understood correctly, this code should use the ID instead of the row number: Sub Search3() Dim rngMatrix, rngResult As Range Dim i, j As Integer Dim strTri As String Dim d, a Set d = CreateObject( Scripting.Dictionary...

Excel: Excel macros - plotting, line graphs, gmail

line graphs, gmail, vba code: Mark, I would say that it is most likely doable. It would depend to some degree on how much things change between data sets and expected chart results. I would say that charts are one of the more difficult things to automate, but the VBA code can be manipulated...

Excel: Excel macros w/ VB, excel macros, relative coordinates

excel macros, relative coordinates, cell location: IF your goal is to hide/exclude certain columns of data for whatever reason then consider using Custom Views which allow you to create different views of your data and then easily display those different views. Typically you would hide columns and/or rows...

Excel: Excel macros, township police department, perry township police department

township police department, perry township police department, robert pendleton: Hello, Lieutenant Pendleton How are you? I ve tried a simple solution. Please see 1. Type the names of the 25 workbooks in a sheet of the worwkbook where you have the printing macro ...assuming those are also the names of the Officers (assuming...

Excel: Excel as mailing list?, mail label, word mail

mail label, word mail, mail program: Hi again, Here s detailed information on doing mailing labels in Word from an Excel data source (from Excel Help). To get this in a more readable format, search for mail label in Excel s Help system: Create a Word mail merge with Excel data In Microsoft...

Excel: Excel matrix, column labels, tab place

column labels, tab place, space character: Hi, Emmar, Believe it or not, Excel can automatically recognize row and column labels, and you can use these names in formulas! It s quite handy, and will be perfect for what you need. Watch this. By default, the ability to use existing labels in formulas...

Excel: Excel matrix, vlookup function, f6

vlookup function, f6, ascending order: Emmar, you will want to use the VLOOKUP function. The help files are pretty good for this function. Basically, though, it will want to know which value to look for in column A, and which column to retrieve the data from. This function assumes that the data...

Excel: Excel - merge to Word labels, word labels, address labels

word labels, address labels, concrete solutions: I have not been able to replicate your issue, so I can only guess. From what you tell, it is more likely to happen at the Word side than on the Excel side. You can do one test for determining that: export the data from Excel to a tab delimited text file,...

Excel: Excel mouse problem, pointer arrow, mouse pointer

pointer arrow, mouse pointer, bent corner: The MOST LIKELY explanation is that this is on a specific sheet (rather than general) and that you are referring to a comment marker - using the tools menu, then options and the general tab you can turn off these markers, or set the file to display the comments...

Excel: Excel Nested IF with Combined AND and OR Function, speedy reply, modified version

speedy reply, modified version, g8: You said ...H8 and I8 = 100... but you also said use the OR function. What you SAID requires an AND function, not an OR function. Based on THAT, what you want is: =IF(AND(H8=100,I8=100),0,IF(AND(H8 =0,NOT(ISBLANK(I8))),PRODUCT(I8,G8),IF(AND(H8 =0,(ISBLANK(I8))),PRODUCT(G8,H8),IF(AND(I8...

Excel: Excel Non-duplication, michelle tang, data validation

michelle tang, data validation, vba procedures: Hey Michelle, I think this solution will work for you: Let s say your data is in cells A1:A50, and you want to prevent the user from entering duplicate numbers in the list. First, select the range A1:A50. Then, go to the Data menu, and select the Validation...

Excel: Excel - Number & Text Formula, cell c1, cell a1

cell c1, cell a1, jonathan: Hi Jonathan, If 200 suites is in cell A1 and the number 147 is in cell B1 then I would put this formula in cell C1. =B1/LEFT(A1,FIND( ,A1,1)) I do not know of a way of formatting the cell 200 suites so that it is recognized as a number unless...

Excel: Excel Numbering, bottom right corner, a1

bottom right corner, a1, a2: Junilla Yes there is an easier way for example In cell A1 put the number 1 make cell A1 the active cell (clic on cell A1) on the bottom right corner of cell A1 you will see a little square. Place the pointer on the little square and right clic and...

Excel: Excel non-matching text, names thanks, false values

names thanks, false values, excel worksheet: If they are ordered, meaning that the names on each row should match, you could two additional columns with formulas like: =A2=C2 =B2=D2 And then filter by those columns for FALSE values. If they are not ordered, you can use the COUNTIF formula in those...

Excel: Excel - Office 2000- text to columns without losing leading 0, douglas m smith, menu cells

douglas m smith, menu cells, column data: Hi Melissa, Yes, there is an easy solution to this. On the Step 3 of 3 dialog box in Text to Columns , you can specify how you want each column to come in. In the Data Preview window, select the column with the leading zeroes and then click Text...

Excel: Excel (Office XP), michelle king, column c

michelle king, column c, navigation button: Thanks Michelle, I m proud of you. Working through the IF statement solution was extremely valuable, especially since it didn t work the first time. This process gives you a deeper understanding of how the functions and operations work, and just as importantly,...

Excel: Excel > Outlook Express, csv files, csv format

csv files, csv format, format menu: What I would do is export the excel file to a csv format (Menu file - Save as), and then launch the Import - Other address book from the Address Book of Outlook Express. It gives the option of using csv files as the origin, and you can use the file you...

Excel: Excel and Outlook, datetime value, olmail

datetime value, olmail, datetime field: Hi Sandra, The one thing about this that is kind of tricky is getting it to recognize when the date field = Now(). This is because Now() actually gives an Excel datetime value that is accurate to a fraction of a second. Therefore the chance of getting any...

Excel: Excel will only call folders with explorer side bar, computer folders, explorer sidebar

computer folders, explorer sidebar, documents and settings: Okay...here is my first solution : I created a folder on my desktop called Nathan . I then created a shortcut to that folder on my desktop. This shortcut runs the following program: C:windowsexplorer.exe /e,/root,C:Documents and SettingsNathanDesktopNathan...

Excel: Excel open two applications?, tile option, menu window

tile option, menu window, two windows: This particular file has had a new window created - windows can be useful as they allow you to see two different areas of the sheet at the same time - easiest way to get rid of one is to use the window, tile option which will set both windows visible at the...

Excel: How can an Excel operation..., orce, cells

orce, cells: Gabriel, Well there s the IF function in excel, but that only displays data. You will probably need a macro for what you need to do. What exactly are you needing? If you can specify a bit, I ll provide you with a piece of visual basic. Regards, Jaso...

Excel: Excel option, very time consuming, thousand miles

very time consuming, thousand miles, cut and paste: Hi, Cindy! I am so sorry for the delay in my response. I was over a thousand miles away from my PC. :) I hope that you have gotten an answer by now, but just in case... YES!! Excel has something called VBA or Macros. You can record them using the article...

Excel: Excel Page Setup, hp laserjet 4100, outlined text

hp laserjet 4100, outlined text, text draft: Michael I looked at the information on the printers and couldn t find a watermark option, however I didn t load up the drivers so if you look through all the options in the File Print Options button you might find an easy way of putting in the draft for...

Excel: Excel Password Protection, spreadsheet tools, cell b2

spreadsheet tools, cell b2, visual basic programming: There is probably an elegant way to do what you need with Visual Basic programming, but I know very little about Visual Basic. You might consider asking this question to someone who lists that item as an area of expertise. That being said, let me pitch...

Excel: Excel Password Protection?, format cells, hidden columns

format cells, hidden columns, excel password: Hi, Chad, There s more than one way to accomplish this, but I think that the most effective way would be to hide the columns and protect the worksheet. Let s say that the sensitive amounts on Sheet2 are in columns C and D. Follow these steps: 1) Select...

Excel: Excel to PDF Creates 3 files! WHY?, adobe pdf printer, visual animation

adobe pdf printer, visual animation, extreme thanks: Hi Mark, Very good question! This one required me to really dig. I easily duplicated the problem you illustrated using one of my Excel files. I found the answer in the adobe.com knowledge base, but not without a LOT of searching. I finally found...

Excel: Excel to PDF Creates 3 files! WHY?, visual animation, extreme thanks

visual animation, extreme thanks, excel spreadsheet: Mark I m not much help on this, but I m guessing that the problem is created by Acrobat not Excel. I d try reasking The question of another allexperts person maybe in the Acrobat area or another Excel person who maybe has had or solved a similar problem....

Excel: Excel to PDF Creates 3 files! WHY?, visual animation, extreme thanks

visual animation, extreme thanks, excel spreadsheet: Hi Mark, WOW! That was a great visual animation..very thorough :o) This seems to be a problem with Excel, it likes to create a seperate PDF per sheet usually. The thing that threw me off was that 2-7 were on one PDF file. Did you create those sheets using...

Excel: Excel to PDF Creates 3 files! WHY?, visual animation, extreme thanks

visual animation, extreme thanks, pdf driver: Hello Mark, I m afraid you re asking me to troubleshoot an aftermarket printer driver that you re trying to use with Excel. I don t have that printer driver and I don t know why it behaves the way it does. Similarly, I can t help Epson printer owners...

Excel: Excel to PDF Creates 3 files! WHY?, visual animation, extreme thanks

visual animation, extreme thanks, excel spreadsheet: I am not really in need of or really care if I get a good rating. The problem seems to be a feature of Adobe. You can probably get around this by writing code that would combine the data from all sheets into one sheet and create the PDF file from that...

Excel: Excel to PDF Creates 3 files! WHY?, douglas m smith, visual animation

douglas m smith, visual animation, adobe help: Hi Mark, I m not a PDF expert, but I was able to duplicate your result (Acrobat v5). I couldn t find anything to fiddle with in the printer settings that might cause or alter this behaviour. Interestingly, with three sheets, I only got two saves with...

Excel: Excel Percentage, column headings, percentage discount

column headings, percentage discount, creating a table: Becky, The final price is the same as 100 minus the discount percent, so instead of multiplying by 35 use 65 (100-35=65): =(A2/100*65). Alternatively, you could subract the discount price from the original price: =A2-(A2/100*35). Both answers will be the...

Excel: Excel Pick list, data validation, automobile auto

data validation, automobile auto, bank cards: I m assuming that you are creating the list with data validation. The SECOND cells data validation would be set to a list, but the list would be a formula =if(Cell1= automobile ,AList,if(cell1= Bank ,Blist,etc)) This method will work if you haven t...

Excel: Excel Is Picking Up Data From Other Files!, reporting department, report references

reporting department, report references, excel programming: JESSICA I haven t seen this specific thing happen but I have seen some inexplicable things go on with excel, for no apparent reasons. I do not know what caused your problem (assuming all of the information is as you explained) I would ck the possibility...

Excel: Excel - Pivot Charts, pivot table in excel, excel pivot

pivot table in excel, excel pivot, interum: Hi Lisa How are you? Which Excel version are you using? Please check if you used the data filter (the arrow to the right of the companies names) in the pivot table to select the companies you want. In order to get rid of a particular company in the chart,...

Excel: Excel Pivot Table and additional formulas, excel pivot table, relative info

excel pivot table, relative info, f4 key: Example: you ve got a pivot table, with a value in cell G6. You want a formula outside the pivot table (say cell K6) that will point to G6; then you want to be able to copy from K6 to K7 and have it point to G7, etc etc. 1) Instead of of typing an = into...

Excel: Excel and Pivot Tables ?, target resolution, target dates

target resolution, target dates, target date: I don t see where PivotTables occur in your explanation. I can offer some guides but without seeing an example workbook a solution is difficult. Assuming you log is an Excel table you could use VLOOKUP based on the ID number to view relevant information...

Excel: Excel Pivot Tables - Labels, abc corp, conditional formatting

abc corp, conditional formatting, pivot table: Darren: There is no feature in Excel that will allow pivot tables to display all labels instead of just the first one; however, that doesn t mean Excel cannot be tricked into doing so: I have uploaded a sample file here: http://home.swbell.net/nate-sus/excel/pivotlabels.xls...

Excel: Excel Pivot Tables, pivot table in excel, excel exe

pivot table in excel, excel exe, pivot tables: Do you have a problem trying to open other Excel files? One issue that I knw of is that an instance of EXCEL.EXE is running and that causes the problem you describe here. 1. Close Excel. 2. Control-Alt-Delete - see if you have running EXCEL.exe there....

Excel: Excel Pivot tables, blank cells, pivot table

blank cells, pivot table, up arrow: Hi Bruce, This is more of a half-solution. Unfortunately, you can t have an active pivot table fill those blanks in. You can however make a copy of your pivot table and paste it as values and then autofill in the blanks with a few quick steps. Highlight...

Excel: Excel Pivot, male smokers, female smokers

male smokers, female smokers, column headings: Cyndi, I cannot tell from your description how your data are set up, but think it s as simple as dragging the Smoke/NoSmoke field into the PT. For instance, let s say you have a table of data where the rows represent individual respondants and the column...

Excel: Excel PivotTables, visual simulation, simulation system

visual simulation, simulation system, excel spreadsheet: I assume you double clicked on a pagefield to create a separate sheet for each item in the pagefield, thus each sheet contained a duplicate pivot table with that specific value selected in the pagefield. I don t see why you don t just create the original...

Excel: Excel-PowerPoint Macro Sharing, powerpoint macro, powerpoint updates

powerpoint macro, powerpoint updates, powerpoint application: Very possible - something along these lines Sub tester() Dim pptobj As Object Set pptobj = GetObject(, powerpoint.application ) Dim pptPres Set pptPres = pptobj.activepresentation pptobj.Run tester End Sub I SUSPECT you will have to amend...

Excel: Excel-Powerpoint Macro Sharing, powerpoint updates, powerpoint macro

powerpoint updates, powerpoint macro, excel macro: Well, sorry, I just answered the question. I have never worked with Powerpoint so I can t say for sure if this will work but if Powerpoint has some type of event in it that is similar to EXCEL s workbook_open event then you should be able to have your VBA...

Excel: Excel Print Preview, douglas m smith, preview command

douglas m smith, preview command, dialog boxes: Hi Eric, Wrap the preview command in an IF statement: If ActiveSheet.PrintPreview = False Then End All dialog boxes return a FALSE value if you hit Cancel, or in your case, Close, which can be tested for. Douglas M. Smith Brainbench MVP for MS...

Excel: Excel Print Preview, preview view, command buttons

preview view, command buttons, print preview: If it s important to you then record a macro to capture all of the print settings before actually printing. Then have the print macro make the desired changes to the print setings. Finally, run the macro mentioned above to restore them as a last thing...

Excel: Excel Printing out too many pages., douglas m smith, brainbench

douglas m smith, brainbench, preview screen: Hi Douglas, A couple of things to check. Press Control-End and see what cell you land in. (This will be the last active cell on the sheet.) My guess is there s something in a cell far far away from the page one cells. Delete all the extra rows and columns...

Excel: Excel Problem- is there an answer?, exact format, excel spreadsheet

exact format, excel spreadsheet, time periods: I m assuming the 1900 is in A1 and the 345 is in B1. Let me know if you actually have both values in one cell, then I need to know if there s an actual dash surrounded by blanks like 1900 - 0345 or the exact format. So with the first assumption, this formula...

Excel: Excel Project- Cost/Volume/Profit Analysis, microsoft excel 2002, volume profit analysis

microsoft excel 2002, volume profit analysis, change factors: Lynne I m not familir with the term C/V/P analysis can you tell me what it is. Also I m not sure what the question is , do you need help with formulas to do the analysis? Ok go ahead and scan the instructions and I ll see if I understand. Send...

Excel: Excel Protection, format cells, cell b3

format cells, cell b3, excel 2000: Hi Will, Sorry, I did not notice that you were working with Excel 2000. You are right, this feature does not exist in that version. Unfortunately, I do not have that version, which makes it difficult for me to test for your environment. However, I may...

Excel: Excel - Protection and Macros, debugger, cells

debugger, cells: I normally use the Protect and Unprotect methods without writing the password:= part, just give it as the first parameter. As an example, here is the code that I use to unprotect all the worksheets in the active workbook: For Each sh In ActiveWorkbook.Worksheets...

Excel: Excel Protection, worksheet cells, cell contents

worksheet cells, cell contents, protection options: Hi Kevin, Worksheet protection is used to keep users from changing part of the worksheet. Cells can be set as locked (default) or unlocked but the locked cells are only really locked when the sheet s protection is turned on. You can set the locked property...

Excel: Excel percentage, percentage increase, column c

percentage increase, column c, percentage increases: Oliver The type of formula you need will be as folows: Assume column A is the cost column and column B is the dollar amount of the percentage increase and column c is the trade price. In column B +A1*.4 In column C +A1+B1 The first formula...

Excel: Excel pivot table question, excel pivot table, consolidated data

excel pivot table, consolidated data, sum value: Hi Christina, I would add a column to your consolidated data that identifies which sheet each row of data originally came from and use that as one of your fields. This would allow you to distinguish the value from sheet1 and the same value from sheet2...

Excel: Excel - plots (Chart), line plot, scatter plot

line plot, scatter plot, 20999: Joan As far as I know it can t be done with Excel, this option is only likely to be found in High End charting programs. You may be able to insert your own lines using the drawing toolbar. I will investigate to see if there is a way around it but I don...

Excel: Excel plotting, paste cells, contiguous data

paste cells, contiguous data, allexperts: In that case, I would use the OFFSET formula, combined with the COLUMN one, something like: =OFFSET( Commuter Mileage 2007 !$K$315,COLUMN(A1)-1,0) Notice that the reference to the range is fixed on both rows and columns, the offset is done by using the COLUMN...

Excel: Excel pop-up box using Macro, internal outlook, userforms

internal outlook, userforms, e mail: This can be done in EXCEL but you want and ask for is somewhat time consuming for me to do especially since I, and most all other volunteers on this site, answer questions here for free and on our own free time. I d suggest you investigate usinng userforms...

Excel: Excel; possible overloading of 1 or more speadsheets & tabs, dish network satellite, maximum view

dish network satellite, maximum view, excel spreadsheet: cen24810 I would guess that the reason it hangs is because it wants to update all of the links that come with saving that info to Excel. You can remove all of those links by running this code: Sub Macro1() ActiveSheet.UsedRange.Select For Each cell...

Excel: Excel print area, absolute references, f30

absolute references, f30, internal functions: Paul, I suspect you did this =INDIRECT(Sheet1!A1:F30) that isn t what I said =INDIRECT( Sheet1!A1:F30 ) was what I said. The argument is a string Sheet1!A1:F30 and must be in double quotes. Since it is a string, it is not an address until...

Excel: Excel prob?, ms access database, csv file

ms access database, csv file, will take some time: Mark, I don t do Access, so let s go back to doing this in Excel. There is a macro below that will do this for you, up to 1.3 million lines. You need to copy it, then paste it into a module in the VBE. So, open the file that you want to store this thing in,...

Excel: Excel prob?, ms access database, csv file

ms access database, csv file, million records: If excel reads the first 65536 records and puts the information into 17 separate columns, I would expect Access to do the same. The best I could do is offer to try to import it into Access and if I have problems look at the underlying structure of the records....

Excel: Excel problems again, object doesn, error line

object doesn, error line, help file: It sounds like either the help file or the help application has become corrupted - possibly re-installing office would resolve it, alternatively search for either the question you wanted help on or the error at http://support.microsoft.com Unfortunately,...

Excel: Excel procedure, aidan, b2

aidan, b2, cells: In addition to paste special which you know about, you COULD use Visual Basic to do the same job for each cellVariable in range( d1:d100 ) cellVariable.value=cellVariable.offset(0,-3).value next the code above would copy the values from range A1:A100...

Excel: Excel programming, john walkenbach, wkb

john walkenbach, wkb, excel programming: Purushothaman, It sounds like you want to display the folder selection dialog. John Walkenbach documents the procedure here: http://www.j-walk.com/ss/excel/tips/tip29.htm This should work in any version of excel from xl95 and later. You will need...

Excel: Excel programming, wkb, creating a new workbook

wkb, creating a new workbook, excel programming: ask for the path: ThePath = Inputbox( Please enter the full path (including the workbook name) for the file ) then instead of obook.SaveAs d: est.xls use obook.SaveAs ThePath and instead of Set wkb = Workbooks.Open( d: est.xls ) use Set...

Excel: Excel programming, wkb, excel programming

wkb, excel programming, macro programming: Hi Purushothaman For the first bit, set the path to that of the workbook containing the VBA code: ChDir ThisWorkbook.Path Then set a default workbook name and give the User the standard Save AS dialogue: startname = 3.xls File =...

Excel: Excel programming, wkb, creating a new workbook

wkb, creating a new workbook, excel programming: At the top of your module, enter this: Dim msFileName as String Then in your code, you could do this: Dim vFilename As Variant If msFileName= Then vFilename = Application.GetOpenFilename( Microsoft Excel Files, *.xl? , , Please...

Excel: Excel - protecting spreadsheets, cell a1, pc clock

cell a1, pc clock, false application: I understand about the user not knowing about the time bomb but it is a consideration nonetheless. If you want to disable the use of the file completely then why don t you just wipe out all data in all sheets and delete all sheets except for just a blank...

Excel: Excel - protecting spreadsheets, cell a1, false application

cell a1, false application, formulae: The problem is that you can t select on a hidden sheet. the good news is there is no reason to: If I understand what you are doing correctly: Private Sub Workbook_Open() If Date = DateSerial(2007, 8, 31) Then With ThisWorkbook.Sheets( Hidden...

Excel: Excel protection, excel sheet, excel 2002

excel sheet, excel 2002, new values: Alex, If you (and the other users) have excel 2002 or 2003 and I assume xl2007 (I don t have a copy installed), then you should be able to do this. For instructions on how, look at this page: http://office.microsoft.com/en-us/excel/HP052010591033.aspx ...

Excel: Excel Queries, excel query, microsoft query

excel query, microsoft query, base query: Hi Rob, This might be of help to you. 1. Create the base query. 2. Edit the query - Click Next till the last screen where you say View Query in Microsoft Query 3. Go to SQL View and change the query as you want - will depend on the database that...

Excel: Excel Query, excel query, budget unit

excel query, budget unit, vendor code: I really can t say why for sure. I believe most Microsoft products use TSQL which is a variant or enhanced version of standard SQL. I am sure Oracle has their own implementation of SQL. It might even support the use of the virtual name, but perhaps didn...

Excel: Excel Query Access, syntax error in from clause, excel query

syntax error in from clause, excel query, microsoft query: I would need a lot more info to solve this problem...how are you creating the pivot table, are you bringning the data into Excel from Access first and if so how...if you want to email me directly with attachments, feel free at rmadsen@iname.com. Rober...

Excel: Excel/Query Parameter Issue, excel query, type mismatch

excel query, type mismatch, query parameter: Ted Sorry-- I consider queries merely as query formulas that when entered incorrectly will not produce correct answers and vice versa. That is why I refered to your query as a formula-- in any event. I have not been able to reproduce the problem you are...

Excel: Excel Query pivot table, syntax error in from clause, pivot table

syntax error in from clause, pivot table, pivot tables: I have no experience of using the external data option, preferring instead to copy the data across (my pivot tables tend to need to go to others who may not have the original data, so this means they can get the results they need and manipulate the data if...

Excel: Excel Query - validation-blocking exit from workbook, excel query, sheet1

excel query, sheet1, validation: I have made a sample code, that you will surely have to tweak to fit your layout. What this BeforeClose event does is check the whole table starting at region A1 in Sheet1, and if the value of one cell in column A is not null, checks that row for the value...

Excel: Excel Query, excel query, reference number

excel query, reference number, spreadsheet: What version of EXCEL are you using? Even if XP, I don t think there is a command in that most recent version of EXCEL to do what you want. I know there is no command (function) to do that in earlier versions. You will have to write a macro that captures...

Excel: Excel Question, sumproduct, sheet1

sumproduct, sheet1, u1000: Hi, John, What you need is a not very well known -- yet extremely powerful -- function in Excel called SUMPRODUCT. Don t let the name fool you; it s not used only for summing products. I was using Excel for many years before I discovered its other hidden...

Excel: Excel Question, cell a1, mouse pointer

cell a1, mouse pointer, cell c1: Hi Reid, You can use the ampersand & for this. If you have an address number in cell A1 and the street address in cell B1 then the formula would look like this and be placed in cell C1. =A1& &B1 The ampersand & connects numbers or values together...

Excel: Excel Question, state abbreviations, address numbers

state abbreviations, address numbers, column c: Hi Reid How are you? The easiest way is to insert a new column C and type in C1 =A1& &B1 then Copy this down Cheers Adelaide PS Please Ask the other question later. I am in an hurry. This is referendum day in my Country. I have to vote ...

Excel: Excel If/Then Question, format menu, thanks in advance

format menu, thanks in advance, a1: If you look at the format menu, you should find conditional formatting - use this to set the formatting - I THINK you will probably need to use the formula option - with something like =countif(a1:z1, Healthy )=0 (if you mean you are looking at a number...

Excel: Excel Question, a7, h2

a7, h2, a1: Shannon - What you are describing sounds like using = . For example: Sheet1, cell A7 has the function =SUM(A1:A6) the answer to which is 15. I want that answer to appear on Sheet2, cell H2, for whatever use. In cell H2 I would put the function:...

Excel: Excel Question, array formula, sneaky trick

array formula, sneaky trick, sum formula: COUNTIF will only let you have one test, so this won t help you - there are various alternative solutions, but probably the simplest in this instance is an array formula =SUM(IF(HOUR(A2:A15)=8,1,0)) which is entered by pressing Ctrl Shift Enter (rather...

Excel: Excel Question, rcell, myword

rcell, myword, length len: Try this, select the columns with the data then run this macro Option Explicit Sub Find_Highlight() Dim rCell As Range Dim MyWord As String MyWord = InputBox( Enter word ) If MyWord = Then Exit Sub Else ...

Excel: Excel Question, price variations, countif

price variations, countif, rounding error: Audrey, I put your data in A1:F7 of a worksheet and put this formula in G2. =IF(SUM(SUMIF(B:B,B2,C:C)/COUNTIF(B:B,B2)=C2,SUMIF(B:B,B2,D:D)/COUNTIF(B:B,B2)=D2,SUMIF(B:B,B2,E:E)/COUNTIF(B:B,B2)=E2,SUMIF(B:B,B2,F:F)/COUNTIF(B:B,B2)=F2) 4, Bad , Good )...

Excel: Excel Question, rng, ogilvy

rng, ogilvy, vba: I put you data in as you have shown it and built this code to do what I understand you to want: Sub RearrangeData() Dim rng As Range, rng1 As Range Dim rArea As Range, rProduct As Range Dim ar As Range, s As String Dim s1 As String Columns(1).Insert...

Excel: Excel Question, cell d1, missing something

cell d1, missing something: I m possibly missing something fundamental here, but assuming I m not, click on the letter D to select column D, then use edit copy, click on the sheet 2 tab and then either on the letter D or in cell D1 and use edit paste (or if you want to replace formulas...

Excel: Excel Question, question thanks, spread sheets

question thanks, spread sheets, business project: I assure you, that plus sign means nothing! If anything, it s a bad habit to start that. This works: =Sheet2!C801 Again, follow these steps EXACTLY! Enter in cell A1 on Sheet1 the equal sign. Click the Sheet2 tab (you ll see the = sign automatically...

Excel: Excel Question, excel worksheet, a100

excel worksheet, a100, spreadsheet: JC Excel can handle your problem with a formula that references the sheet you want to transfer info from. The formula looks like this =+Sheet10!C801 In your example you would put this formula in sheet 1 cell A100 and it would transfer the data...

Excel: Excel Question, target value, target address

target value, target address, cell entries: Can t be done by a formula, but you can do it with VBA. This will work for single-cell entries, not things likt fill/down or using the fill handle, etc. right-click the Excel LOGO near the file menu, select View Code, paste this in: Private Sub Workbook_SheetChange(ByVal...

Excel: Excel Question, project sheets, question thanks

project sheets, question thanks, sheet1: The method I suggested using the formula I suggested pertains to the same workbook (file). Worksheets are the individual tabs at the bottom of a workbbok. The same formula will work if the sheets are in a different workbook. If the workbook name changes...

Excel: Excel Question, visual basic programming, target value

visual basic programming, target value, target address: Hi Chris, It needs to be done with Visual Basic Programming. Select the following Macro and copy. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address $A$2 Then Exit Sub Select Case Target.Value ...

Excel: Excel Question, qatari, question thanks

qatari, question thanks, minor changes: The spelling of the currency is done in this part of the code: Select Case Dollars Case Dollars = No Dollars Case One Dollars = One Dollar Case Else Dollars = Dollars & Dollars End Select Select Case Cents...

Excel: Excel Question Auto copy data entered into a cell to a new row on a worksheet, auto copy, incrementing

auto copy, incrementing, thanks in advance: Try this Option Explicit Sub CopyCells() Dim r As Long r = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Worksheets(2).Cells(r, 1).Value = Worksheets(1).Cells(1, 1).Value Worksheets(2).Cells(r, 2).Value = Worksheets(1).Cells(1, 2).Value...

Excel: Excel Question - End of Column, down arrow, shift key

down arrow, shift key, ogilvy: Barbara If you column is populated, then you would hit the End Key, then the down arrow. This will take you to the bottom of your data. If you want to select from the current cell down to the bottom, then hold down the shift key while you hit End, then...

Excel: Excel Question Follow Up, x cells, combobox control

x cells, combobox control, combo box: Hi Carol For Forms, Alt F11 to VBA, open the Forms object folder and find and double-click the form you want to amend. If the Properties Window is not visible, select it via View. Click on the ComboBox control, then Font in the Properties Window and make...

Excel: Excel: Question generator, vlookup function, test sheet

vlookup function, test sheet, excel spreadsheet: Thomas I a working on your question. Can you send me a copy of your word document that has the questions so that I may see the format of the questions. RIRSTRESS@aol.com What I believe you will ultimately be able to do is generate randomly a series...

Excel: Excel Question re Cell Matching, vlookup formula, leigh martin

vlookup formula, leigh martin, data names: You can use the VLOOKUP formula. In this case, if the cell with the name is E2, the formulas could be: Subject: =VLOOKUP(E2,$A$1:$C$10,2,FALSE) Age: =VLOOKUP(E2,$A$1:$C$10,3,FALSE) If you add rows to the table, change the part $A$1:$C$10 to reflect the...

Excel: Excel Question, pivottable, dialog boxes

pivottable, dialog boxes, excel help: Hi Neil, I am assuming that you would like to know how much an operation would cost at the end of the month. Such as for example code 162 would be to assemble Part A you want to know how many dollars are coded to that code each month. If that is what you...

Excel: Excel Questions (Mulitple Versions), track changes, spreadsheet

track changes, spreadsheet, cells: In order to track history, you need to enable the sharing of the workbook (Tools- Share workbook- Mark the check box in the Editing tab). Once you do that, you cannot change the settings of data protection, so you need to make all the arrangements (mainly...

Excel: Two Excel Questions, price target, cell a1

price target, cell a1, max iterations: =Indirect(A1& D3 ) where A1 holds XYZ this will be equivalent to =XYZ!$D$3 Since the D3 above is a string, it will not change even if you insert or delete rows. It isn t a range address until it is evaluated by the Indirect function during a sheet calculation....

Excel: Excel qn...may b easy 4 u.., cell a1, mouse pointer

cell a1, mouse pointer, proper case: Hi Pramod, Your question implied that you want to change the all uppercase company name to proper case. Here is how to do that. If the first company name is in cell A1 and cell B1 next to it is blank then you would type this formula into cell B1. ...

Excel: Excel query to handle filter time according to dates, attendance cards, cell d2

attendance cards, cell d2, excel query: I m assuming that the sample data you show begins in cell A1 (the lable DATE in A1, 12/18/2006 in A2, etc). Also assuming that the data is properly sorted by time. Create this formula in cell D2: =IF(OR(A2 A1,C2 C1),C2&MONTH(A2)&DAY(A2)& MIN ,IF(OR(A2...

Excel: Excel question, target cell, pivot table in excel

target cell, pivot table in excel, g max: Hi MUTHUVEL How are you? Sover is used for maximising or minimising a function subject to some contraints. E.G. Max Z=2x+3y subject to x+y =20 x =5 x,y =0 1. Type in A3 =2*A1+3*B1 2. Reserve A1 to represent X and B1 to representY 3.Tools - Solver...

Excel: Excel question, vlookup function, alphanumeric code

vlookup function, alphanumeric code, volunteer work: Yes but it won t convert it per se - it will just return, in an adjacent column, for example, the value that relates to that entry by using a formula, unless you write a macro. You will need to create a table that has the alphanumeric code in one column...

Excel: Excel question, table tool, excel help

table tool, excel help, data tables: The table tool is mainly used for what-if scenarios, where you have a list of parameters and want to know the result of the same formula for all of them. If you search the Excel help for data table , the topic About data tables tells you what is the purpose...

Excel: Excel question, attendance data, vlookup formula

attendance data, vlookup formula, sheet1: Sabre means nothing to me, but I suspect isn t relevent - it SOUNDS as though you need a vlookup formula (see www.aidanheritage.co.uk/Excel for a guide to this) - AllExperts doesn t lend itself to explanations typed in, so if sending a file would help my email...

Excel: Excel question, column c, blank space

column c, blank space, cassie: If there is a blank that is not a gap, then the IF should be done in a different column, although we still get the information from the name column. For example, let s suppose that column D has the same structure as column A, with the gaps in the same places,...

Excel: Excel question, claim period, explicit function

claim period, explicit function, paste code: Liam: The problem is you cannot explicitly tell Excel to stop updating just one formula (for example, you want to tell G13 to stop updating once you shift to a new period). The short answer is you cannot do what you want...the long answer is you can...

Excel: Excel question, data validation, auditing

data validation, auditing, phone numbers: Let s assume these are in column E for this example. Select ALL of column E, use Data/Validation, select Custom, enter this formula: =COUNTIF(E:E,E1)=1 click the Error Alert tab & enter an appropriate message. You will no longer be able to enter duplicates....

Excel: Excel question, combo boxes, vlookup function

combo boxes, vlookup function, cell b1: Hi Pete How are you? Yes, you are right. the Vlookup function should be =VLOOKUP(A1;Sheet1!$A$19:$B$30;2) But this assumes that the numbers are in column A and names in column B If you want it to be the other way round, I can write a VBA function...

Excel: Excel question, cell b1, suspect one

cell b1, suspect one: You may use an additional column, with a formula similar to: =INDIRECT( A & COUNTA(A:A)-ROW()+1) In this case, the column to invert is in column A, and I am assuming that the formula is entered in the row 1, for example on cell B1. If you want to start...

Excel: Excel question, page setup dialog, format painter

page setup dialog, format painter, format tool: Not sure these questions are well worded but still Preview button is on the standard toolbar, the file menu, and the file page setup dialog box (which is what you wanted, but not the best answer!) question 2 is the worst worded of the lot - it SOUNDS...

Excel: Excel question, macro security, quantity number

macro security, quantity number, spanish speaker: Hi Jose How are you? I have just dealt with the 10top SysId that spent more parts ... the other problem is similar Please do the following 1. Make sure that the SysID is in col A and Quantity in Col B of sheets1 2. Tools - Macro - security - security...

Excel: Excel question, quantity number, column c

quantity number, column c, excel worksheet: Hi Jose, If I understand your question correctly, you can use the RANK function. Here is an example of how it is used: Names are in column A and numbers are in column B john 25 jane 14 james 28 ron 5 In cell C1, enter this formula: =RANK(B1,B:B)...

Excel: Excel question, quantity number, anwser

quantity number, anwser, excel worksheet: 1) you need 2 unique lists for the sysID and parts. if the sysid s are in A1:A8000 (including the title sysID ), select that range, use data/filter/advanced filter, select Copy to another location , click an empty cell, say E1, for the Copy To range, and...

Excel: Excel question, array formula, x cells

array formula, x cells, quantity number: Try this: When you have a list of number in an Excel spreadsheet there are times when you may have to Sum only the top x numbers in the list. If the numbers are sorted then the task is a fairly straightforward Sum function including on the top, or bottom...

Excel: Excel question, quantity number, field settings

quantity number, field settings, pivot table: I would do this as follows (1) use the data menu to get to the pivot table and chart report - produce a pivot table that totals the items you are interested in (2) use the RANK function to rank these results, OR simply right click on any of the entries...

Excel: Excel question, validation method, data validation

validation method, data validation, case range: Anjalina (nice name by the way!), I do not know of a way to do what you are talking about with the Data Validation method. Not saying there isn t a way, I m just not aware of it. The method I use to accomplish what you are trying to do is through...

Excel: Excel Radial Buttons, microsoft excel 2002, selection values

microsoft excel 2002, selection values, vba code: You could possibly take advantage of the location of the radio (not radial) button, depending on where you ve placed them. For example: Sub OptionButton22_Click() Range( E & ActiveSheet.OptionButtons(Application.Caller).TopLeftCell.Row).Value = $10,000/$5,000...

Excel: Excel Radio Buttons to Copy group of cells with Formulas, cell a2, vlookup

cell a2, vlookup, commandbutton: A2 A7:G7 The formulas are in the same column as the search criteria cell. I think you want to duplicate the formulas in A7:G7 in the next row, but assume they will look to another cell for the criteria. You don t say which cell or how lookup criteria...

Excel: Excel - Random Assignment, cell b1, e120

cell b1, e120, student names: This is a possible solution using Excel. I will assume that the student names are in range A1:A100, and the seats in range D1:D120. The goal will be to fill the range B1:B100 with random seats that won t repeat, with a process that can be easily repeated....

Excel: Excel Ranking Question, speedy reply, question question

speedy reply, question question, case 2: The formula as given works fine providing you replace the words RANGE and Cell with the RANGE being tested and the CELL being tested - for example with your data in A1:A9, cell B1 would have =RANK($A$1:$A$9,A1)+((COUNTIF($A$1:$A$9,A1)-1)/2) This works...

Excel: Excel References, basic excel, excel workbook

basic excel, excel workbook, thanks in advance: Adam Looks like you just forgot to tell excel what to do if text wasn t present, so it puts in false. If you make your formula look like this it will put a blank in the cell. +IF(ISTEXT(CURRENT!A13),+CURRENT!A13, ) The , ) at the end of the...

Excel: Excel - Regression Equation and Groups of Data, polynomial coefficients, polynomial regression

polynomial coefficients, polynomial regression, regression formula: If the data you supplied starts in A1, then enter GG01 or GG02, etc in D1, and you can use this array-entered formula: First, select 4 cells, say E1:H1, enter this: =LINEST(OFFSET(A1,MATCH(D1,C:C,0)-1,0,COUNTIF(C:C,D1),1),OFFSET(B1,MATCH(D1,C:C,0)-1,0,COUNTIF(C:C,D1),1)^TRANSPOSE(ROW(INDIRECT(...

Excel: Excel - Replicated data across a row, iow, explorer 3

iow, explorer 3, excel 2000: Hi Robert How are you? I have no idea of what is going on with your btothers worksheet. I d check if it contains a macro that runs automatically as soon as you enter some data into a cell. Please check this by doing the following 1. Tools - macro -...

Excel: Excel - Replicated data across a row, format cells, format painter

format cells, format painter, format menu: I didn t actually suggest format painter, but that would do the trick! My suggestion was to use the format menu, then cells - which would have revealed the then current formatting (but as with many of these problems, it can take hours to work out the WHY...

Excel: Excel Reports, blank report, automobile insurance

blank report, automobile insurance, printing purposes: Report Manager will not help you that much at all based on the way you have designed this. And yes, the way you have designed this seems to be very inefficient. I d sugest setting up one of your sheets as a little database, or list, with each column representing...

Excel: Excel Rounding Question, cell reference, thanks in advance

cell reference, thanks in advance, aidan: There is indeed a function that will do this as you have said round UP to the higher 1000 (if you wanted to round to the nearer 1000 it gets more complicated!) the function you want is the CEILING function =ceiling(25320,1000) will return 26000. Obviously,...

Excel: Excel Rounding, cell b1, format cells

cell b1, format cells, decimal format: Patrick: This will need to be done in 2 steps. Step 1 - Formatting: 1) Select the cell you want to be formatted as X.XXXX and click on format- cells. 2) Select number for the format and change the decimal format to 4 places. 3) Click OK. *** This...

Excel: Excel - Row 1 Missing, left mouse button, mouse button down

left mouse button, mouse button down, mouse pointer: Hi Joyce, I know exactly what you are talking about because I have had the same problem and it is a little more difficult to unhide row1 but here is how you can do it. Here are three different ways to accomplish this. 1. If you hover your mouse pointer...

Excel: Excel Row Heights, row headings, format menu

row headings, format menu, double click: Per EXCEL help: Change the row height to fit the contents Select the row or rows that you want to change. Point to Row on the Format menu, and then click AutoFit. Tip: To autofit all rows on the worksheet, click the Select All button and then double-click...

Excel: Excel Row Won't Show All Text, format cells, point font

format cells, point font, times new roman: Is it marked as wraptext (Format/cells/alignment)? Is the row height tall enough? (If it s a merged cell, then double-clicking the row height won t work right - you d need to adjust it manually). If it s not wraptext, then is the cell to the right empty...

Excel: Excel - Rows to Colums Macro, cell b1, shot success

cell b1, shot success, alcohol drink: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros...

Excel: Excel: random question generator, 100 random questions, question option

100 random questions, question option, correct option: Hi Mr. Thomas, I have a Excel file with following specs. (1) It holds about 2000 questions (This number can keep changing. You can add / delete questions without affecting the working of the solution.) The columns in this are SrNo, Question, Option A, Option...

Excel: Excel random sampling, sampling function, random sampling

sampling function, random sampling, vlookup: Criteria range is M1:M2 (at least 2 rows of data). The single cell you should select should be ani in your real data, like somewhere between A1:K2000 in my example (A1 will suffice). The criteria range of M1:M2 has the formula I suggested in cell M2. You...

Excel: Excel most recent date selected from a subset of a list, array formula, date entry

array formula, date entry, auto return: I don t know if you are using Excel names or when you say name is just a convention. If you are using names, a formula you can use may be: =MAX(subgroupname) If there are no names defined, the formula accepts ranges, and the details will depend on how you...

Excel: Excel, reduce number of rows, input range, radio button

input range, radio button, installation cd: Charles You can use an Add in called Sampler. It s in the Analysis Toolpak Add-in. To load that, you need to choose Tools/Add-ins... and check the Analysis Toolpak Addin checkbox. If you don t see it in the list of add-ins, you should install it from...

Excel: Excel - report percentile data, field settings, pivot table

field settings, pivot table, c30: You can get very close easily by using a Pivot Table: - Select your data - Choose (from the menu) Data, Pivot Table and Pivot Chart report. - choose Excel list or database and Click Next - Make sure entire columns are selected as source (avoids having...

Excel: Excel report, sort routine, excel 2002

sort routine, excel 2002, number address: Tom I don t really use checkboxes very much so I m not really expert at answering this. However I don t think that what you want to do is a really good application of the checkbox. I think you might be better using the sort routine. Of course that depends...

Excel: Excel is rounding up large numbers, credit card numbers, number storage

credit card numbers, number storage, format settings: Hi Ulf, Excel uses a number storage standard named IEEE (Institute of Electrical and Electronic Engineers) Double Precision Floating Points. Numbers stored in this format are limited to 15 digits of precision, beyond which the numbers are rounded. There...

Excel: Excel running total, cell a2, input cell

cell a2, input cell, circular reference: It can t be done - not even a macro will do this IN THE WAY YOU WANT IT DONE. Nor will you idea of a circular reference work. As soon as you type anything in a cell, it s previous contents are gone and forgotten forever, period. A macro could be written...

Excel: Excel is running wild!, xlstart directory, stuck key

xlstart directory, stuck key, best guess: Ellice - I have never come across this. Perhaps there is a macro running. Try holding down the Shift key while you open the workbook. this will stop macros that are fired upon opening from being fired. If it still does the same thing, try starting...

Excel: Excel runtime error 1004, false selection, excel runtime

false selection, excel runtime, row 24: There isn t anything obviously wrong with the macro (although it can be shortened Dim CellVar For Each CellVar In Range( A24:B29 ) Range( E24 ).Offset(CellVar.Row - 24, CellVar.Column - 1).Value = CellVar.Value If CellVar.Column = 2 Then Range(...

Excel: Excel Search, vlookup formula, search question

vlookup formula, search question, cell reference: A vlookup formula would do it =vlookup(Sheet1UniqueValue,Sheet2DataRange,ColumnToBeReturned,False) replacing each of my typed entries with the value( or cell reference) and ranges - the value needs to be in the first column of the data range being searched...

Excel: Excel Search Bar, array formula, cell d1

array formula, cell d1, inturn: Yes it s possible, but it s not easy to explain - even with an example - I DID do something remarkably similar for someone else and had a better example than the one I can find, however I ll have to point you at the BASIC example!! http://www.aidanheritage.byethost3.com/excel/Array_Filter.xls...

Excel: Excel Security/Read Only Problems, excel security, microsoft excel

excel security, microsoft excel, fire station: If you say the the file sharing funtionality isn t working. Could it be that they weren t saving the files? If you haven t you could code a automatic save on close. Or may be the sheet is too complex? Maybe seperate each station to have its own sheet then...

Excel: In Excel:-Selecting specific rows of data in spreadsheet, observation data, time intervals

observation data, time intervals, excel spreadsheet: Brent: Yes, there is an easy way but to make it easy requires the use of a macro to do what you describe and want. And since you have never written a macro then you should understand that giving you instructions on a subject you know nothing about would...

Excel: Excel, Series, Indirect, number thanks, cell a1

number thanks, cell a1, correct solution: Hi Louis, Unfortunately, as far as I know...the series function in a graph does not allow the indirect function to be used within it. (Correct me if I am wrong) Try defining a NAME for your range and use the OFFSET function to dynamically increase your...

Excel: Excel - Set Text Color, conditional formatting, format button

conditional formatting, format button, add button: You can do this through conditional formatting, which can be found under Format - Conditional Formatting. You add the conditions by setting the fields to (as in your example) - Cell Value Is - Less Than - 0.05(5%) then click on the Format button and choose...

Excel: Excel - Share Workbook feature, hidden columns, share mode

hidden columns, share mode, text numbers: I ll answer question 2 first - the caveat is simple - DON T use the feature - it s a disaster waiting to happen. If the workbook is a database, then use a database (eg Access). If it s a calculation program, then have it read only as changes are not needed....

Excel: Excel Shared Worksheets, history worksheet, row numbers

history worksheet, row numbers, check boxes: Hi Barb, The best help I could find anywhere for this is Excel Help. Obviously the final worksheet will overwrite the first worksheet if there is a change. I would do what the Excel help suggests and tract what has actually happened to your worksheet,...

Excel: Excel Sheet 1 to Sheet 2, excel sheet, exclamation mark

excel sheet, exclamation mark, thanks in advance: Hi Matt, If you want to return a value from Sheet2 to Sheet1. Select Sheet1 and a specific cell such as cell A1. Then type an equal = sign, then click on Sheet2 and select the cell with the value you want to return. Then press Enter. Your formula should...

Excel: Excel Snapshot, roy cox, record mode

roy cox, record mode, macro recorder: Here s some sample code --------------------------------------------------------------------------------------- Module : Module1 DateTime : 05/03/2007 13:59 Author : Roy Cox (royUK) Website : visit www.excel-it.com more examples ...

Excel: Excel Sort Command, sort fields, excel spreadsheet

sort fields, excel spreadsheet, sort command: Michelle I haven t come across this before, but it may be that it is either too big or has become corrupted, if so try copying and pasting to a new spreadsheet and see if it still does the same thing. If it does get back to me with the version of excel...

Excel: Excel Sort/Filter, names thanks, member database

names thanks, member database, filter options: Sonny The easiest option without going to VBA would be in insert a new column next to the dates with the following formula =month(A2) where A2 is the first date. This will only show the month. The next step is to go to Data Auto Filter and in the filter...

Excel: Excel Sort and Merge, vba code, magazines books

vba code, magazines books, unique records: Scott, you have two separate tasks. First you need to do the merge. This can be done in VBA. Then, you can do the duplicate removal using Advanced Filter (unique records only) in Excel under Data-Filter on the menu. If you have trouble with the VBA code,...

Excel: Excel Sorting, dell p4, lotus 123

dell p4, lotus 123, click scroll: Sure, you just press F5 and enter the range you d like to select. With the range selected, THEN you can do your sorting. However, you may want to consider using an even faster keystroke method to select the range. If you start with the active cell at...

Excel: Excel Spellcheck Macro, misspelt words, spellcheck

misspelt words, spellcheck, ogilvy: Bing, Use the second format (assume you have single words in the cells). Sub spellcheck() For r = 1 To 5 spellresult = Application.CheckSpelling( _ Word:=Cells(r, 1), IgnoreUppercase:=True) If spellresult = True Then ...

Excel: Excel Spreadsheet, douglas m smith, cell b1

douglas m smith, cell b1, cell values: Hi Ken, Let assume your entries are in column A, starting in cell A1. You can enter a forumula in cell B1: =A*1.2 and fill it down column B. This will give you the results in formula form. To convert the results to static numbers, copy the formulas...

Excel: Excel Spreadsheet, left mouse button, cell a1

left mouse button, cell a1, cell b1: Hi Ken, I hope I understand your question right. If you have numbers in Column A let s say from cell A1 through cell A10. In cell B1 you can type in the formula =A1*1.2 If cell A1 has the number 60 in it then the result of the formula will be the...

Excel: Excel Spreadsheet, microsoft office 2000, running microsoft office

microsoft office 2000, running microsoft office, undelete program: Christi When you opened the file in Works it truncated the spreadsheet, works will only open one sheet, if you saved it back to the floppy with the same name then you overwrit the original and in most cases that would mean it is gone. The only option you...

Excel: Excel Spreadsheet, cell b1, handy website

cell b1, handy website, decimal equivalent: Hi Dale, If 8:30:00 AM is in cell A1 you can place this formula in cell B1. =A1*24 Now select cell B1 and go to the Excel menu and select Format then Cells... then select the Number tab then select Number from the list of formats then click...

Excel: Excel Spreadsheet data change between 2 worksheets, microsoft excel 2002, using excel in the classroom

microsoft excel 2002, using excel in the classroom, spreadsheet data: Barbara What you need to use in your worksheets is links between the two worksheets. You also need IF formulas that will allow or not allow a sale based on the inventory levels. You can link between the worksheets with a LOOKUP function type of formula...

Excel: Excel Spreadsheet Grows in Size..., spreadsheet format, excel spreadsheet

spreadsheet format, excel spreadsheet, rectangular area: Steve Excel looks like it has 65536 rows and 256 columns. However it only stores information on the rectangular area that it considers to be in use. You apparently changed what Excel considered to be in use to a much larger area than actually is in use...

Excel: Excel Spreadsheet, cell b1, header type

cell b1, header type, excel spreadsheet: K., Thanks for your generous rating. As for where the macro goes. Here s an easy method. From your saved worksheet press ALT+F11. This will start the VBA editor. Click Insert Module. In the window that appears on the right, paste in the macro I...

Excel: Excel Spreadsheets, douglas m smith, cell a2

douglas m smith, cell a2, cell a1: Hi Kyle, Excel does not directly have this feature. (Access does for records and Word does to bullets / outlines.) It can be simulated though. If cell A1 is 1 and the formula in cell A2 is =A1+1, then the numbers will increment when the formula is filled...

Excel: Excel Spreadsheey, excel formula, ebay

excel formula, ebay, markup: =MIN(A1,500)*0.3 + MIN(MAX(A1-500,0),500)*0.25 + MAX(A1-1000,0)*0.2 If you wish to better understand the logic I recommend you download my price_qty_disc_markup.zip example file and review the formulas and commentary on the Tiered Discount sheet....

Excel: Excel & SQL Macros, macros, left hand

macros, left hand, sql: I m sorry, but I don t work with SQL The macros can t be in the Worksheet module or else they would move with the sheets.I would think they are in Standard Modules of each Workbook. Check this by opening the VB Editor (Alt F11), you shuld see the macros...

Excel: Excel Stacked Column Chart, stock market chart, column chart

stock market chart, column chart, wrestling training: Rich: I figure the best way to present this solution is by example. As such, I have uploaded a spreadsheet to the following location: http://home.swbell.net/nate-sus/excel/weight.xls There are 2 charts: 1) The first one is your idea on the stock...

Excel: Excel IF Statement, douglas m smith, decimal time

douglas m smith, decimal time, cell references: Hi Rubens, The cell D10 formula should be: =IF(D22 TODAY()),D22,TODAY()) In the formula, D10=D22 does not assigned the value in cell D22 to cell D10, it just compares their values and returns either true or false. That logic is more appropriate in...

Excel: Excel IF Statement for Hockey, excel spreadsheet, hockey league

excel spreadsheet, hockey league, constraint: James, there is definitely a way around this, but I can t construct the formula until I know more about your spreadsheet. Can you write back and show me 4 or 5 rows? That way I can get an idea of how the sheet is laid out, and what the data looks like....

Excel: Excel IF Statement range problem, hepp, atr

hepp, atr, aidan: It is not clear from the question what you want to do - probably because I can see only text - IF the text is in one column and the value in another, then vlookup would do it - if not, could you clarify the question (if sending a file would help my email is...

Excel: Excel If Statement, countif, excel 2000

countif, excel 2000, excel if statement: =IF(ISERROR(FIND( Coast ,A1,1)), no , yes ) Basically, the formula looks to see if A1 has the word Coast anywhere within the text A1 using the FIND function. The FIND function returns an error if it does not find what it is looking for. Thus, the use...

Excel: Excel - If Statements, format menu

format menu: Yes, but not the way you are thinking - look under the format menu, and then conditional format - this choice will do EXACTLY what you want - you are only allowed three levels of conditioning (which gives you four possible results, as all conditions failing...

Excel: Excel SUM suddenly quit working, formula evaluation, sum formula

formula evaluation, sum formula, aggregate function: This is really strange, as the aggregate function in the toolbar works, but not the Excel function. At this point there is nothing else I can suggest without seeing the workbook, if you want to send it (or a copy with fake data and the error) to me at m4zapic...

Excel: Excel Subtotals, pivot tables, pivot table

pivot tables, pivot table, subtotal: You are not doing anything wrong, the subtotal feature only allows to group on one field at a time, where the other columns that are not calculated are blanked out. You can stack aggregations on top of each other, but they will create additional rows, which...

Excel: Excel Subtraction Formula, cell a1, a15

cell a1, a15, thanks in advance: Hi Chawni, If you have the amount owed in cell A1 and the payments in cells A2:A12 you can put this formula in cell A15. =A1-SUM(A2:A12) If you want to put the amount owed directly into the formula and the payments made in cells A2:A12 you can use...

Excel: Excel Sum If, sumif, statuses

sumif, statuses, total value: There is a function call SUMIF that can do what you require. For example, to get the sum of the missing items, you can use a formula like: =SUMIF(O:O, Missing ,L:L) Changing the second parameter to the other status, you can know the totals for each one....

Excel: Excel Sum Product, array formula, array function

array formula, array function, sumif: I understood your question. To stop an empty cell from being counted as January, try the following array function. Rememebr you to press Ctrl Shift ENETR to enter an array function. Doing this makes it an array formula by putting the french braces ( {}...

Excel: Excel Sumproduct Function, excel sumproduct, array formula

excel sumproduct, array formula, work thanks: I would suggest using the Conditional Sum Wizard though I realize you are wanting to count and not sum. This is an add-in. After installing the add-in and running it - a wizard will walk you thru the steps - an array formula will be written. Edit this...

Excel: Excel not saving - also creating a lot of temp files, spreadsheet program, excel spreadsheet

spreadsheet program, excel spreadsheet, temp files: Hello, Cathy, If you ve already checked the file attributes on your spreadsheet, then the most likely thing is the following: 1) Go to File/Save As. 2) Drop down the Tools menu and choose General Options. 3) My guess is that Read-only recommended is...

Excel: Excel scale, sheet tab, number changes

sheet tab, number changes, page breaks: Hi Vicki, It is so difficult to know without seeing it myself. I have one guess though. Under Page setup Have you checked the Sheet tab and delete any Print area that may be selected and then try print preview. If this does not solve it you can...

Excel: Excel search, excel macros, numerical entries

excel macros, numerical entries, cell b1: Byju Try the following lookup formula. =VLOOKUP(A1, consolidated indexing !A$1:A$8000,1) The formula is in the missing K nos file and is in cell B1. It assumes that the data you want to lookup is in cells A1 thru A8000, and the the data that you...

Excel: Excel search criteria >=, array formula, countif

array formula, countif, sumif: It would be possible to set a dynamic named area as the range for the pivot data, but as you don t want to go down the pivot table route I ll try another tack -how about an ARRAY formula this is for the COUNTIF example =SUM(IF(C2:C40= 1700 ,1,0)) ...

Excel: Excel search, excel macros, numerical entries

excel macros, numerical entries, numeric entry: I think you should be fine - yes, you can drag it down, and there is no problem referencing another file - as an example =VLOOKUP(A2, C: empdines[B715983.xls]B715983 !$A$5:$C$30,3,FALSE) would lookup A2 on the current sheet in a file called B715983.xls...

Excel: Excel searching a table of data, row header, table a2

row header, table a2, column header: I have tried to do this with formulas in the past and have been unsuccessful, I ll play with it a bit and double check. You can get very close but not quite. It has to do with the undynamic nature of formulas in cells. On the other hand but it is very...

Excel: Excel and searching, excel sheet, ogilvy

excel sheet, ogilvy, thanks in advance: select the column with the number (if you want to search for a product number) or the column with the names if you want to use a name. then do Edit= Find and in the box put in your product number or name and click find. It should select the appropriate...

Excel: Excel sequential numbering, cell b2, header row

cell b2, header row, header number: In column A (which you can later hide), beginning cell A2, put the sequential numbers starting with 4. For instance, you could enter 4 in cell A2, enter =A2+1 in cell A3, then copy cell A3 down as far as desired. In cell B1 enter 20.3. (since this is the...

Excel: Excel- series problem, cell a1, blank row

cell a1, blank row, continuosly: Start off with the following: A1: 1 A2: Blank A3: 2 A4: Blank A5: 3 A6: Blank Highlight and select A1 through A6. With the group selected, drag the fill handle to the desired cell and Excel will populate the area with 4..5..6..7, etc and will add...

Excel: Excel-series problem, drag method, blank row

drag method, blank row, continuosly: For instance, put 1,2,3 in A1:A3. Then put a blank, or text, in A4. Select A1:A4, drag the lower-right corner handle, and you ll get your number series with that blank or text cell repeating every 4th row. (I m using Excel 2000; I don t know about earlier...

Excel: Excel - series problem, drag technique, blank rows

drag technique, blank rows, blank row: The drag technique works the way you have exposed, by itself it doesn t allow to enter blank rows. You need to manual interact with the data by either inserting a new row, deleting a cell, changing a value and draging again from it, etc. It will depend on...

Excel: Excel shortcut related, open option, excel spreadsheet

open option, excel spreadsheet, warm regards: I would first check the settings in Explorer - use windows explorer and use the tools menu to get to folder options. Select this and then use the File Types tab - scroll down the list of files until you get to XLS (assuming this is the file type you want...

Excel: Excel sort by second name in a cell, forename, writeup

forename, writeup, aidan: The only ways I know of to do this are EITHER to split into two columns OR to use a blank column to get the surname part of the name and then sort on this column The formula to get the text AFTER the first space is =RIGHT(A1,LEN(A1)-SEARCH( ,A1)) ...

Excel: Excel sorting, sort columns, pivot table

sort columns, pivot table, excel 2002: Your english is much better than my Norwegian (of which I know nothing!) I m not TOTALLY sure what you mean by counting every unique column, and it s not totally clear how you want to sort them. IF for example, you want to find all instances of x in a,...

Excel: Excel sorting, blank rows, hi tommy

blank rows, hi tommy, header title: Hi Tommy, There should not be any gaps such as blank rows or columns. Then select the entire range of data including the header (title) row. Then go to the excel menu and select Data then Sort then select the dropdown box underneath where is says Sort...

Excel: Excel sorting on dates, true dates, yy

true dates, yy, date function: Assuming a text date in A1 and assuming the text date are in a valid recognized EXCEL date format, then use this formula to convert the text dates to true dates for sorting purposes: =IF(ISERROR(DATEVALUE(TRIM(CLEAN(A1)))),0,DATEVALUE(TRIM(CLEAN(A1))))...

Excel: Excel sorting, two answers, excel 97

two answers, excel 97, no gaps: (1) - put yourself in the top left of the range and press ctrl shift enter - this will select the entire range (2) - as long as your data has no gaps in it, put yourself at the top left of the range and then do data sort - it will automatically select the...

Excel: Excel - spell check a protected document, spell check, new option

spell check, new option, boxes: To protect the active sheet, but check the boxes that allow adding or deleting rows in the protected sheet, the code is: ActiveSheet.Protect AllowInsertingRows:=True, AllowDeletingRows:=True This is a new option for Excel 2003; I believe that you can...

Excel: Excel and spell check, custom dictionary, correct spelling

custom dictionary, correct spelling, excel 5: When you run the spell check and it finds a misspelled word, you should be able to type it into the Change To space, then click the Change All button. This will then change it for everytime it is mispelled that particular way without asking you each time....

Excel: Excel spread sheet, cell b5, douglas m smith

cell b5, douglas m smith, excel spread sheet: Hi Roger, To reference a cell on a different sheet, include the sheet name in the reference: =Sheet1!B5 Note: If there is a space in the sheet name, the syntax will change slightly: = Cost Data !B5 To add the nine sheets: =Sheet1!B5+Sheet2!B5+Sheet3!B5......

Excel: Excel spread sheet formula, excel spread sheet, time sheet

excel spread sheet, time sheet, fri: Hi, Steve, At first you say that Mon-Thu are 7.5 hours a day and Fri is 8 hrs. Then you said that Mon is 10, Tue is 6, etc. Which of these is the one that you d like to use? Also, can you provide an example of exactly what you d like to input and...

Excel: Excel spread sheet, macro security, excel spread sheet

macro security, excel spread sheet, b5: Hi Roger How are you? First, maybe it is interesting to try that to sum 2 values of different sheets: 1. in B5 of Sheet 10, type =Sum( 2. select Sheet1 and B5 Type , 3. Select Sheet2 and B5 4.Press enter You end up with =SUM(Sheet1!B5,Sheet2!B5)...

Excel: Excel spreadsheet, lee snyder, prize lists

lee snyder, prize lists, higame: Hey Jerome, I think this will solve your problem: Since the top set of winners takes precedence, this should work: -Select Column A -Click on Data on the top menu, then Filter, then Advanced Filter. -An error message may appear, click OK. -Make sure...

Excel: Excel spreadsheet, chris hansen, lee snyder

chris hansen, lee snyder, prize lists: Jerome, What I missed was that 2nd place in HiGame is better than 3rd place in Series. I believe a solution would require VBA code to do all of this checking back and forth. I don t see how a formula or anything else would do the job. I don t like the...

Excel: Excel spreadsheet, cell a1, cell b1

cell a1, cell b1, delimeter: lenore The text to columns feature may work for you. Go to toolbar/data/text to columns. This feature can separate data into separate columns using a fixed width, or a delimiter such as a space or a comma. You need to paint the column that the data...

Excel: Excel spreadsheet and drop-down list, spreadsheet output, spreadsheet data

spreadsheet output, spreadsheet data, excel spreadsheet: RJ, Have you tried PivotTables? They can summarize your data faster and more efficient that you are trying to do. Select any cell in your range and select Data Pivot Table. This will start a wizard and should be self-explanatory from there. X...

Excel: Excel spreadsheet formula, spreadsheet formula, logical test

spreadsheet formula, logical test, input areas: Hi Dean, I m a little confused on the details of the problem but I think I can give you general guidance that will help. Yes, nesting is one option. The general syntax is: =IF(test1,IF(test2,bothtrue,false2),false1) The second IF statement takes...

Excel: Excel spreadsheet Links, bill burgess, excel spreadsheet

bill burgess, excel spreadsheet, excel 2002: BILL Sorry--if there is no source file listed or no cell with a link in it that you can clear, I don t know of a way to eliminate the link. It must be a problem if they have created a break link in the 02 version, although I ve not seen any files where...

Excel: Excel spreadsheet sorting, douglas m smith, relevant volume

douglas m smith, relevant volume, product combination: Hi Amrit, You can do this a couple of ways but the easiest would be to create a pivot table. Select the cells in columns A, B and C, include column titles and then select Pivot Table and Pivot Chart Report... from the Data menu. The options you want...

Excel: Excel spreadsheet, lee snyder, prize lists

lee snyder, prize lists, higame: Hi Jerome How are you? Please try the following 1. Tools - macro - visual basic editor 2. insert- module 3. copy Option Base 1 Sub uponeplace() Set A = Selection n = A.Rows.Count For I = 1 To n - 1 For j = 1 To 2 A.Cells(I, j) = A.Cells(I...

Excel: Excel spreadsheets within a single workbook, target value, excel spreadsheets

target value, excel spreadsheets, value application: This can t be done with formulas because they d have to be entered into ALL sheet before you enter into other sheets, and there s no way to know if the new data comes from sheet 2, 3, 4, or 5. The only way to accomplish this is with a macro but that requires...

Excel: Excel startup prob., excel 2000, macros

excel 2000, macros, prob: Bob Sounds like maybe a macro is working to open the other docs, but I m not sure. Check out the macros that are recorded go to Toolbar/tools/macro/macros and see if there are any macros that would be doing this. I haven t had this problem nor have...

Excel: Excel if then statement, toolbar button, b2

toolbar button, b2, cells: Hi Phyl How are you? I am sorry I have not understood what you want (how many numbers do you want to sum? Where do you want to display the output? A full example would help) but maybe this help The following sums up the 2 selected cells and display...

Excel: Excel If statement, diamond ratings, q2

diamond ratings, q2, excel if statement: Tom: Unfortunately, versions of Excel prior to 2007 have a limit of 255 characters in a formula and IF() statements are limited to 8 nestings. Excel 2007 has much higher limits, and you may be interested to know, your formula works just fine in Excel 2007...

Excel: Excel IF statement, microsoft excel 2002, c16

microsoft excel 2002, c16, index function: 3 ways come to mind: 1 - This If statement is a series of NON-nested if s so it can go on indefinately: =IF(C16=22, Accounting Finance , )&IF(C16=2, Application Denied , )&IF(C16=3, Audit , )&IF(C16=4, Big Game , ) etc. Each piece is a complete if stmt...

Excel: Excel if then statements, recreation amenities, linen service

recreation amenities, linen service, column c: Hi again, Gwen, I m sorry but I still need to ask for more explanation, in order to give you exactly the right formula. What is total points ? Is it a single cell? Tell me if this is what you re saying: * If the cell called total points contains...

Excel: Excel IF statments, format button, menu format

format button, menu format, change font: It is possible to change the font to red, but not directly with an IF function. To implement the condition, go to menu Format- Conditional formatting, select Formula is on the drop down list below Condition 1, and in the text box enter the following formula:...

Excel: Excel "submit" style button for workbook, style button, free time

style button, free time, sheer: Yes I can help but perhaps not in the way you would prefer. Without seeing the workbook you mention and ONLY going on what you say, it sounds as if you need a macro. But, I do not write macros for people on this site who seem to think I am expected to...

Excel: Excel sumif with concatenated values, excel sumif, sumif function

excel sumif, sumif function, numeric equivalent: You ve just run into a system limitation on Excel - if you search in help for LIMIT you will find the system limits which include Feature Maximum limit Number precision 15 digits so I m afraid no, if you need to manipulate numbers this...

Excel: Excel summary sheet, current sheet, cell a1

current sheet, cell a1, excel workbook: Richard You can reference the sheet name by using this formula =MID(CELL( filename ,A1),FIND( ] ,CELL( filename ,A1))+1,255) place this in lets say cell A1 of the current sheet then use a link to the summary sheet to get the name. You can use a link...

Excel: Excel sums, a5, cells

a5, cells: Shayne If I am understanding the question correctly, this formula should work for you. =IF(+SUM(A1:A5) 22,+SUM(A1:A5)*17,+SUM(A1:A5)) This formula says ---if the sum of cells A1 thru A5 are greater than 22, then take the sum of A1 thru A5 and multiply...

Excel: Excel 'swap', cells, proper cell

cells, proper cell: Of course, you just have to use cut and INSERT two times and make sure you select the proper cell before you insert. How to insert: rightclick the row where you want to insert a cut row and select Insert Cut Cells (or for columns, the column where you want...

Excel: Excel Tables, microsoft excel 2002, macro security

microsoft excel 2002, macro security, excel 7: Hi Diane How are you? Please try the following: 1. Tools - Macro - security - security level - low Tools - Macro - security - trusted publishers- tick the two boxes at left-lower corner 2. Tools - macro - Visual Basic Editor 3. Insert module 4....

Excel: Excel Task Duration, task duration, time duration

task duration, time duration, funny answers: Sorry - a bug + I forgot to ignore weekends. Also I meant to tell you that below the holiday list should be nothing (empty OR text). And you DID have that OK in the sample you sent. Try this version: Function TotalTime(TaskStart, TaskEnd, WorkHoursStart,...

Excel: Excel - Text don't show completely, double arrow, mouse pointer

double arrow, mouse pointer, row numbers: I don t get the same problem you explained when I recreated this. I could see all the text. One thing to try: Set your column width manually. Then move your cursor to the left side of the screen where the row numbers appear. If the row your text...

Excel: Excel Text Formula, len value, cell a1

len value, cell a1, nbo: Neil, I can t think of a way to do it with the built in functions of Excel. However it can be done with a user defined function. To do that do the following: 1. Press Alt+F11 to open the visual basic explorer. 2. Click on the Insert menu and choose...

Excel: Excel Text Manipulation, separator character, nth element

separator character, nth element, text manipulation: Neil, there is a piece of code that will extract whichever part of the string you choose, assuming the string is separated by some character like the space in your case. Here s that code: Function ExtractElement(Txt, n, Separator) As String Returns...

Excel: Excel Text Manipulation, array formula, text manipulation

array formula, text manipulation, cell a1: Neil Assume your text string is in cell A1 =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$255),1)),0),FIND( ,A1& ,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$255),1)),0))-MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$255),1)),0)) will pull out the number as long...

Excel: Excel Text Manipulation, text manipulation, split info

text manipulation, split info, cell a1: Function GetNo(info) As String possible = Split(info, ) For i = LBound(possible, 1) To UBound(possible, 1) If IsNumeric(possible(i)) Then GetNo = possible(i) Exit Function End If Next End Function...

Excel: Excel Text Stripping, array formula, dave smith

array formula, dave smith, ogilvy: If the text will always be of the form #text where # represents 1 to multiple numbers, then =LEFT(A1,SMALL(IF(ISERROR(MID(A1,ROW(1:25),1)*1),ROW(1:25)),1)-1)*1 entered with Ctrl+Shift+Enter If you want me to adapt the formula you provided so it...

Excel: Excel - Textbox Change Focus, excel textbox, change focus

excel textbox, change focus, excel spreadsheet: Lloyd, I m not sure why, but Excel does not seem to support the EnterKeyBehavior property of text boxes embedded directly into a worksheet. I found this in Help, but I m not sure it addresses or answers the question - Support for AutoTab varies from one...

Excel: Excel Time Calculation, time calculation, time hh

time calculation, time hh, workdays: Laeeq Excel has a built in function, NETWORKDAYS, that calculates workdays. You can use that function to calculate the number of workdays between dates and then multiply that times the number of hours in a workday. That formula would look like this ...

Excel: Excel Time Spend on Task, time duration, federal holidays

time duration, federal holidays, weekends and holidays: Good morning! I have a spreadsheet for you to look at: http://home.swbell.net/nate-sus/excel/totaltime.xls This spreadsheet shows you how to calculate total time using regular math and a formula called NETWORKDAYS(). Networkdays automatically knows...

Excel: Excel Timesheet - Regular & Overtime hours, excel timesheet, overtime hours

excel timesheet, overtime hours, lunch break: First, you can t enter 14:15 pm and have excel understand. Use either 14:15 or 2:15 PM. Same with 17:00 and 21:00. If in your example the 5:15 AM is in A2, then enter this formula in C2 (for regular): =IF(24*(B2-A2) 8,8,24*(B2-A2)) and this for D2 (for...

Excel: Excel - Timestamping upon Modification, douglas m smith, data validation

douglas m smith, data validation, macro editor: Hi Jon, You will need a macro to accomplish this and the macro will need to be attached to the worksheet itself and not be in a module. This code should do what you want: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim a As Variant...

Excel: Excel Titles, cell b2, cell b6

cell b2, cell b6, reservation sheet: K. I m having a bit of difficulty understanding exactly what configuration you are looking for. It sounds like you want each column to represent a condo, so above each column we need owner name, condo #, size of condo (2 or 3BR/2B) bed configuration,...

Excel: Excel TODAY formula, douglas m smith, excel2002

douglas m smith, excel2002, brainbench: Hi Kim, Your formula is working but Excel usually assumes that since you are working with dates, you want a date format and applies it automatically. Just change the cell format from a date to a number format and you should see the correct answer. Douglas...

Excel: Excel Tool Tip i n Cells, data validation, combo boxes

data validation, combo boxes, tool tip: Sangeeta, I do not know how to do Tool Tips as you have described. As for combo boxes, there are several ways. The most straightforward is through Validation. Click Data Validation. On the Settings tab, select List from the Allow: box. In the Source:...

Excel: Excel Tracking xp version, peg, best wishes

peg, best wishes, thanks for your help: Peg, I have little experience with sharing and tracking changes in shared workbooks. The times I have tried to use that feature, I lost data, so I have resorted to other methods. I would encourage you to find another Expert that may know more about that...

Excel: Excel Trendlines, trend line, shading

trend line, shading, xl: Patrick, You posed the question as an EITHER/OR but left off the OR condition. In response to your EITHER query... There s no switches you can flip to do it easily. However, it is possible to build logic to use the trendline equation as the source...

Excel: Excel Can,t Find, location column, littlewing

location column, littlewing, gmail: Bill It should be finding the item numbers the same all the time, the only differences that can be attributed to the find problem may be that the active cell when starting the find isn t at the top left corner and/or the text/number you are looking for...

Excel: Excel table problems, blank cell, different numbers

blank cell, different numbers, excel 2000: Andy, there s a very cool way to do this in Excel, and very few people know about it! I discovered it many, many years ago, and use it often. Let s say that you want to increase the entire table by 35%. Follow these steps and see how easy it is. 1) Go...

Excel: Excel and tax calculation, case structure, x 1150

case structure, x 1150, correct result: Rolf, I m confused by the results you re claiming. It s true that a weight of 1150 yields a result of 39.1, as 1150 x .34 = 39.1. But when I enter a weight of 1151, the formula gives me the correct result of 78.268 (1151 x .68). 81.6 is the result with...

Excel: Excel template capabilities, capabilities of microsoft excel, ns dept

capabilities of microsoft excel, ns dept, xls workbook: Hi Kevin, Also another thing you can do. Is open a copy of a downloaded book that you want to apply your formatting to. Go to the Excel menu and select Tools , then Macro , then Record a new Macro . You can name this macro such as MyFormat just do...

Excel: Excel text, visual basic programming, format cells

visual basic programming, format cells, column width: I think I know what you want - you would like the text to shift around so that it is always completely visible. Althought exactly what you want is not possible without perhaps some visual basic programming, there are some other ways that may work. 1) Word...

Excel: Excel text box, report layout, text boxes

report layout, text boxes, textboxes: John, If a new print form is a spreadsheet layed out to print a desired report layout, then No, there is no such formula. You link the value of textboxes to cells. Then you can us a formula to refer to the cell that contains the value from the linked...

Excel: Excel text limitation, employee evaluations, character limitations

employee evaluations, character limitations, character limitation: Hi Lorraine, Yes there is a character limitation. It depends on what version of Excel you are using but an easy way to tell is to go the Excel menu and select Help then select Microsoft Excel Help then on the index tab type limit then select worksheet...

Excel: Excel text, cell a1, freeze panes

cell a1, freeze panes, cells: Merged cells by default are center-aligned. One thing you could do is left-align it and also freeze panes, say at column G or so. Otherwise, it s sort of the nature of the bease. You re almost asking to see cell A1 when the screen is scrolled to cell BB7000....

Excel: Excel time accumulation problem, keyboard shortcut, mm ss

keyboard shortcut, mm ss, time card: Al You could create the macro using the =today() function and the =Now() function for the date and time. it would look something like this Sub Time_Stamp() Keyboard Shortcut: Ctrl+Shift+T ActiveCell.Value = Now() Selection.NumberFormat...

Excel: Excel time formulas, decimal fractions, consistant results

decimal fractions, consistant results, mm ss: Excel stores times as decimal fractions (so 0.5 is 12 hours etc). The first thing I would do is to format all the cells that contain time AS time, and make sure they all look like they have formatted correctly, as I suspect that POSSIBLY some of them are...

Excel: Excel time problem, expert listings, excel expert

expert listings, excel expert, flight log: Hi, Eric, thanks for writing in. I guess you didn t notice where I say that I m not familiar with VBA. (I mention it both in my expert listing and on the question page.) I am a programmer, but I use the old macro language that came out with an earlier version...

Excel: Excel -time, microsoft excel 2000, using microsoft excel 2000

microsoft excel 2000, using microsoft excel 2000, format excel: There is no such time as 9998:00 or 10000:00 because time is ALWAYS a fraction in EXCEL. For example, if you type .75 in a cell and format that cell to a time format, EXCEL would display 6:00 PM, or saying it a different way, 3/4 of a day has elapsed. ...

Excel: Excel timecard - track type of hours, douglas m smith, sumif

douglas m smith, sumif, typical formula: Hi Andrea, COUNTIF has a buddy named SUMIF that should be what you are looking for. It s structure is: =SUMIF(test range, criterion, sum range) Normally, the data would be side by side and a typical formula would be like this: =SUMIF($A$1:$A$30,...

Excel: Excel Updating, target value, target address

target value, target address, hi thanks: Try the following code for WorksheetA: Private Sub Worksheet_Change(ByVal Target As Range) Dim strAddressChanged As String Dim rngOnSheetB As Range strAddress = Target.Address Set rngOnSheetB = Sheets( WorksheetB ).Range(strAddress)...

Excel: Excel Updating, hi thanks, thanks in advance

hi thanks, thanks in advance, a1: Hi Rich How are you? Can you please try the following 1. Type Hello in A1 of Sheet1 2. select Sheet2 3. Highlight B1 4. Type = 5. Select Sheet1 6. highlight A1 7. Press Enter now every change on A1 of sheet1 will be reflected on B1 of sheet2...

Excel: Excel used for inventory tracking, formulae, vba

formulae, vba, lorraine: Hi Lorraine, Yes it can be done with VBA. If you want you can send the workbook and I will put that programming in the workbook for you. If you do send it please make sure it is clear what cells would be cleared at the end of each month. You can send it...

Excel: Excel Validation, validation form, data validation

validation form, data validation, correct syntax: David, Why not use the list option under validation. You would just enter A,B After selecting the list option in the first tab. then when someone selected that cell, they would get a dropdown from which they could select A or B. You could also...

Excel: Excel Validation, douglas m smith, validation form

douglas m smith, validation form, data validation: Hi David, The syntax for this (and conditional formatting) is slightly different from cell formula. The formula needs to return either a TRUE or FALSE result. The formula you want would be: =IF(OR(A1= A ,A1= B ),TRUE,FALSE) Change A1 to whatever...

Excel: Excel Variable Ranges, variable range, combo box

variable range, combo box, google: Hi you don t need VBA. Try something called Dynamic Named Range (it expands as you add data). It is nothing special, it is just a smart way of using Excel s formulae. Google Excel dynamic named range . There are many sites. Also see: http://www.ozgrid.com/Excel/DynamicRanges.htm...

Excel: Excel VB, cell d1, kind assistance

cell d1, kind assistance, open application: m.lock, Glad to here that it is coming along. To have it run automatically when the workbook is opened, double click on the ThisWorkbook module and add the following lines of code: Private Sub Workbook_Open() Application.OnTime TimeValue( 16:37:00...

Excel: Excel -VB, toobar, column c

toobar, column c, d15: Hi Tomas, Okay, here is my solution. This worked for me on a simple test worksheet. Hopefully it will work as well for your more complex situation. This code should all be pasted in the worksheet s event code module. To do this right-click on the worksheet...

Excel: Excel or VB formula, number 0, first job

number 0, first job, spreadsheet: In col A, enter the number 0-100 to show the weight of that particular job. For instance, if the first job is on row 5 and has a weight of 10%, put a 10 in cell A5. In col B, put either a blank or an x , depending on if the job is complete. Then, in col...

Excel: Excel VB Macro Find Replace, excel vb, row address

excel vb, row address, q17: Something along these lines will do it for you Dim N As Long N = 17 Range( a1 ).Formula = =IF(ISTEXT($B & N & ),IF(LEADERBOARD!P$ & N & =LEADERBOARD!P & N & ,0,IF(LEADERBOARD!P$ & N & LEADERBOARD!P & N & ,-1*LEADERBOARD!Q & N & ,LEADERBOARD!Q$...

Excel: Excel - maybe a VB solution?, vb solution, excel user

vb solution, excel user, cell reference: Right-click the excel LOGO near the file menu, select View Code, paste this in (change the cell reference as desired - I m using A1): Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Sheets(1).range( A1 ).value=date End...

Excel: Excel VB Time stamp, unprotect cells, format cells

unprotect cells, format cells, excel vb: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect([H2:H12], Target) Is Nothing Then Application.EnableEvents = False activesheet.unprotect Cells(Target.Row, 2) = Now activesheet/protect Application.EnableEvents = True End If...

Excel: Excel / VB / VBA, excel vb, orange case

excel vb, orange case, cell interior: I d attach something like this to the VBA codepane for the worksheet that you want to have this special ability. Private Sub Worksheet_Change(ByVal Target As Range) Dim ColorRange As Range, cell As Range, x As Integer Set ColorRange = Range(...

Excel: Excel / VB / VBA, target value, excel vb

target value, excel vb, orange case: Right-click the sheet tab, select View Code, enter this (I used column A (see comment in code): Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column 1 Then Exit Sub change as appropriate Select Case Target.Value Case...

Excel: Excel / VB / VBA, cell interior, orange cell

cell interior, orange cell, excel vb: Randy, You can use the calculate event to update the colors on each calculate Right click on the sheet tab where the cells to be colored are located and select view code. At the top of the code module select worksheet from the left dropdown and Calculate...

Excel: Excel / VB / VBA, excel vb, value case

excel vb, value case, formating: Randy, Here s one method. This macro will need to be in the worksheet change event of the sheet that contains the column of numbers under observation. To get access to that event, press Alt+F11 from Excel. Once the VBA editor starts press CTRL+R to go...

Excel: Excel VB, macro name, bare minimum

macro name, bare minimum, stupid question: m.lock, That depends on what you want to do with Application.OnTime. Usually it will go inside of a new module, but it needs to be part of a sub routine or function. In other words, the bare minimum that you need in a module is: Sub A_Name() Application.OnTime...

Excel: Excel VBA, screen flicker, window98se

screen flicker, window98se, userforms: To my knowledge, no. It would defeat the purpose of the intent of userforms, in my opinion. You could write some code that would positon the userform a certain position form the left and from the top and assign it to the click or the activate event of the...

Excel: Excel VBA, automation expert, excel vba

automation expert, excel vba, outlook application: Sorry, that s not my area of focus. You should probably ask an Outlook expert (or maybe a BackOffice Automation expert if there is one). I don t know anything about the properties and methods of the Outlook application. I don t even use Outlook. ...

Excel: Excel VBA, blank cells, d apple

blank cells, d apple, tomato fruit: Hello Raj You can t as long as it remains a pivot table. (you can edit within a pivottable) What I usually do it make a copy of the sheet with the pivot table, clear out the pivottable on the copy, and then populate the data that remains. Assume the pivotTable...

Excel: Excel VBA, excel vba, blank row

excel vba, blank row, cell a1: Do you mean that you need the find the first blank row beyond A5 and then write data there? If so, then here is one way of many and the easiest IMHO Range( A65535 ).End(xlUp).Offset(1,0) = My Data Start from the bottom and work up and you don t...

Excel: Excel VBA, excel vba, cell interior

excel vba, cell interior, ogilvy: I believe this is what you wanted: Sub ABC() Dim cell as Range For Each cell In Range(Cells(6,2),Cells(133,4)) cell.BorderAround Weight:=xlMedium cell.Interior.ColorIndex = 6 Next End Sub Test it on a copy of your worksheet to insure it is what...

Excel: Excel VBA, novice programmer, population sizes

novice programmer, population sizes, line sheets: This line would work: max = Sheets( export ).Range(Sheets( export ).Range( D2 ), Sheets( export ).Range( IV2 ).End(xlToLeft)).Columns.Count You might prefer to do it like this dim shtExp as worksheet dim rngLeft as range, rngRight as range SET shtExp...

Excel: Excel VBA, excel vba, cell changes

excel vba, cell changes, hi bob: Erik, You were almost there. First, you don t need to link the checkbox to a cell, just use this routine, assuming that your Checkbox i called CheckBox79. Private Sub CheckBox79_Click() MsgBox Hello End Sub which goes in the appropriate worksheet...

Excel: Excel VBA, excel vba, blank cell

excel vba, blank cell, excel spreadsheet: If you don t have any gaps in column A, just use Range( A1 ).End(xlDown).Select If you have gaps, use Range( A & Rows.Count).End(xlUp).Offset(1,0).Select The difference between the tow is that the first works down the column until it finds the...

Excel: Excel and VBA, cell a1, copy worksheets

cell a1, copy worksheets, cell references: Private Sub CheckBox1_Click() Dim rng As Range, r As Range Dim r1 As Range Set rng = Me.Range( A4:C4 ) Debug.Print Checkbox1 , CheckBox1.Value If Me.CheckBox1.Value = True Then With Worksheets( Orders ) Set r = .Cells(Rows.Count, 1).End(xlUp)...

Excel: Excel VBA, excel vba, chart wizard

excel vba, chart wizard, command button: Brent, It is relatively simple, just 2 tings to be aware of. The first is that the page index is zero based, so page 1 is index 0. The second is that you must stop it going beyond the end. You can test the current page against the total count, but...

Excel: Excel and VBA, correct column, column names

correct column, column names, blank rows: I can do this for you (not trivial, so it d be for a fee ($25)), or I can try to get you started, but if you re not up to speed on macros I m not sure how far you d get. If you want me to do it for you, let me know at bobumlas@yahoo.com. Otherwise, here are...

Excel: Excel VBA 2002, john walkenbach, excel vba

john walkenbach, excel vba, textboxes: Mathieu, Use this technique proposed by John Walkenbach: http://j-walk.com/ss/excel/tips/tip44.htm Handle Multiple UserForm Buttons With One Subroutine His example uses commandbuttons, but you can use textboxes or other type msforms 2.0 control as...

Excel: Excel VBA 2002, decimal separator, text application

decimal separator, text application, local settings: Look at Application.International(xlDecimalSeparator) Have you checked out cdbl(userform1.Textbox1.text) I believe cdbl is sensitive to the local settings, but I can t say for sure. If not, you will have to do cdbl(replace(userform1.textbox1.text,application....

Excel: Excel VBA 2002, microsoft excel solver, solver users

microsoft excel solver, solver users, excel vba: Solver is an addin, so its dialogs would be produced by that addin. There may be a more direct way using the addin, but this worked for me: Sub ABC() Application.CommandBars( Tools ).Controls( Sol&ver... ).Execute End Sub The addin would already...

Excel: Excel VBA 2002, excel vba, ogilvy

excel vba, ogilvy, wb: Sub SolverInstall() // Dana DeLouis Dim wb As Workbook On Error Resume Next Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With wb.VBProject.References .Remove .Item( SOLVER ) End...

Excel: Excel VBA 2002, maximum compatibility, control toolbox

maximum compatibility, control toolbox, excel 5: Excel 97 and later support listboxes from the Control Toolbox Toolbar Excel 5 and later support listboxes from the forms toolbar. Try using the forms toolbar listbox ans assign a macro to it for maximum compatibility. I can t say why you are getting...

Excel: Excel VBA 2002, excel vba, command button

excel vba, command button, ogilvy: Maybe something like this: Private Sub CommandButton2_Click() With CommandButton1 If .BackColor = RGB(15, 200, 75) Then .BackColor = -2147483633 Else .BackColor = RGB(15, 200, 75) End If End With End Sub -- Regards, Tom Ogilvy ...

Excel: Excel VBA 2002, excel vba, excel 1997

excel vba, excel 1997, string 7: You can use application.Version. It reports major.minor (like 8.0). for major the numbers are: (it returns a string) 7 is xl95 8 is xl97 9 is xl2000 10 is xl2002 11 is xl2003 not sure how the mac versions report out. form xl2000 ? application.Version...

Excel: Excel VBA 2002, added precaution, excel vba

added precaution, excel vba, excel application: Set screen updating to false, then make the window hidden as an added precaution after you open it. You can change the option for what is displayed in the task bar. If you are in control of the file, make it hidden and then save it so it is hidden by default...

Excel: Excel VBA 2002, two dimensional array, excel vba

two dimensional array, excel vba, programming modifications: I assume by matrix you mean a two dimensional array: Sub Tester11a() Dim vArr as Variant Dim rng as Range Set rng = Range( I7 ).CurrentRegion vArr = rng.Value QuickSort vArr, 5, LBound(vArr, 1), UBound(vArr, 1) Userform1.Combobox1.List = vArr End...

Excel: Excel VBA 2002, 2x2 matrix, excel vba

2x2 matrix, excel vba, 3x3 matrix: Mathieu, Not unless you want to put the matrix down on the worksheet, then pick up the 2x2 matrix. Other than that, you would need to loop. In a 2 D array, you can pick off a column or row using the Worksheet Index function, but that is about the only...

Excel: Excel VBA 2002, excel vba, isnumeric

excel vba, isnumeric, first error: Use an error handler in the top routine. Any errors will climb back up the chain to the first error handler if finds if one is defined. Sub Callx() Dim var As Variant Dim var3 As Variant On Error GoTo ErrHandler var = abc var3 = x(var) MsgBox...

Excel: Excel VBA 2002, vba password, excel vba

vba password, excel vba, password cracker: the date command returns the system date ? date 1/28/05 it is of type Date. You can certainly write a routine in the workbook_open or other event, that checks the date and takes some action. However, this is easily defeated if the user disables...

Excel: Excel VBA 2002, excel vba, print button

excel vba, print button, ogilvy: You have the BeforePrint event available to you. So you could have a simple BeforePrint event where you set Cancel = True then when you button is pushed, you can disable events, print the sheet, then enable events. This way your event won t block...

Excel: Excel VBA 2002, excel vba, tbox

excel vba, tbox, textboxes: Mathieu dim bOk as Boolean dim tBox as msForms.Textbox dim ctrl as Control bOk = False for each ctrl in Userform1.Controls if typeof ctrl is MSforms.Textbox then if len(trim(ctrl.Text)) 0 then bOk = True set tbox = ctrl...

Excel: Excel VBA 2003, redim, concatenating

redim, concatenating, loop through: You would have to loop through the two arrays and assign them to another Sub BBB() Dim s, t, u Dim s1 As String Dim i As Long, j As Long s = Range( A1:B2 ) t = Range( E4:F5 ) ReDim u(1 To 2, 1 To 4) For i = 1 To 2 For j = 1 To 4 If j = 2 Then...

Excel: Excel VBA, excel vba, int len

excel vba, int len, v12: No new function in 2007 with this regard, but this code might help: With oCells2MergeAndWrap .WrapText = True .Cells.Merge .EntireRow.RowHeight = dRowHeight * (Int(Len(.Cells(1, 1).Value) / 93) + 1) End With Experiment by changing the...

Excel: Excel and VBA, newws, problem thanks

newws, problem thanks, radom: Hello Radom, Here is a simple macro (along with a helper function) you can use to do this: Sub UpdateOld() Updates this workbook s active worksheet with additional rows from new file (the other workbook in the Window list) Dim OldLastRow...

Excel: Excel and VBA, previous products, data cd

previous products, data cd, problem thanks: Again, what is your question? All you tell me is what you have and what you want to do with that data and you give me conditions about that data and what should happen if certain conditions exist. I see no question. I don t know if you mean to ask how...

Excel: Excel VBA, countif, a100

countif, a100, rg: you don t need code -- =COUNTIF(A1:A100, ABC ) will count the # of ABC s in the range. If you INSIST on code, you can use Function Countem(Rg as range, What as string) as integer Countem = Application.Countif(rg,What) End Function or am I not understanding...

Excel: Excel VBA, shading color, conditional formatting

shading color, conditional formatting, cell interior: Daniel I don t think that you can count conditional formats, because they don t return a true/false that can be checked. I would say that if you are able to set conditional formats on cells, you should also be able to set those formats with VBA, and add...

Excel: Excel - VBA, ulvi, concatenation

ulvi, concatenation, ok button: Ali, Your code looks fine to me. I used it in my sample sheet that was formatted like you described, and it ran fine filtering out all the dates below 7/31/2004. You did not mention how you declared the Rapor object, so that may be your source of error....

Excel: Excel - VBA, customer satisfaction questionnaire, internal customer satisfaction

customer satisfaction questionnaire, internal customer satisfaction, customer questionnaire: Hi Lisa, Assuming you are running this code from Excel (the way the code is written it could be run from any Office application) all you should have to do is change Set myRecipient = MailItem.Recipients.Add( clow,lisa ) to Set myRecipient = MailItem.Recipients.Add(Worksheets(...

Excel: Excel - VBA, excel vba, cell b1

excel vba, cell b1, expert solutions: Not having Turkish Excel, I m going to guess at what s going on: VBA ALWAYS uses English, so Range( B1 ).Value is being interpreted as an american date. I would recommend you RECORD the piece which does the filtering which works from the worksheet and first...

Excel: Excel VBA, heidi hi, excel vba

heidi hi, excel vba, excel workbook: Hi Heidi, How are you? Please try the following: 1. TOOLS - MACRO - VISUAL BASIC EDITOR 2. INSERT5-MODULE 3. COPY Sub savingfile() Name = InputBox( Type a file name ) ActiveWorkbook.SaveAs (Name) End Sub 4. FILE - CLOSE AND RETURN TO EXCEL 5. TOOLS...

Excel: ExcelVBA, sisman, column c

sisman, column c, excel sheet: Sisman, You do not need VBA for this. Use the PivotTable (PT)function to summarize data (click Data, PivotTable). If you are unfamiliar with PTs, use the Help (F1) feature to get you started. There is also an on-line tutorial many find useful (link...

Excel: Excel VBA & API, real time data, excel vba

real time data, excel vba, doevents: Sorry mate, this is a known bug of the Windows API Timer when you try to edit a cell manually at the same time when the TimerProc is called. There are a number of FREE Activex Timer controls that work much better than the Windows API Timer. Have a look...

Excel: Excel VBA Application.Run, vba application, excel vba

vba application, excel vba, guess: Jerry, I m not sure what to tell you. I ran your code on my machine (win xp + excel 2003) where I had a macro in my personal.xls file called Hi. The Hi macro was a simple msgbox routine like this, Sub Hi() msgbox Hello End Sub Your macro (with...

Excel: Excel VBA Current Region, excel vba, arrow key

excel vba, arrow key, header rows: Bob, 1. To find the next empty row use the following statement: Sheets(1).Range( L65536 ).End(xlUp).Offset(1, 0) To explain: Sheets(1): refers to the first sheet in your workbook. Adjust the number if it isn t the first sheet or use the sheet name:...

Excel: Excel VBA - chart properties, excel vba, mouse position

excel vba, mouse position, msgbox: IF this could be done, it would be incredibly difficult. First, you need a Class module to have Charts recognize events, which isn t that hard, but you re asking for the ability for Excel to recognize the mouse position to know what you might want to display,...

Excel: Excel - VBA code, excel vba code, cell a1

excel vba code, cell a1, source sheet: Tammy, My Guess is that you are copying a sheet from an existing workbook and then sending the new workbook. I would try selecting cell A1 (or whatever cell you want) in the source sheet, then maybe even save the source workbook, then copy the sheet and...

Excel: Excel VBA for conditional formatting, spreadsheet changes, excel vba

spreadsheet changes, excel vba, conditional formatting: Here s some code that will apply 2 conditional formats to the range A1:A10. You should be able to customize it yourself to apply to any ranges or conditions. Sub conditionalFormatExample() Dim rngToFormat As Range Set rngToFormat = ActiveSheet.Range(...

Excel: Excel VBA, conditional Row Hide, excel vba, array formulas

excel vba, array formulas, vbas: OK you will need to use the calculate event of the sheet3 worksheet. Private Sub Worksheet_Calculate() For Each cell In Range( B6:B70 ) If cell.Value = 1 Then cell.EntireRow.Hidden = True Else cell.EntireRow.Hidden = False Next End Sub Having said...

Excel: Excel VBA - Data base, excel vba, listindex

excel vba, listindex, combobox: The LISTINDEX would return the selected item IF it is in the list, and -1 if it isn t - so that gives you a test for an existing entry or a new one, and PROBABLY the use of offset would resolve the problem eg if combobox1.listindex 0 then range( A1...

Excel: Excel VBA debugging, excel vba, editor tab

excel vba, editor tab, auto data: The only other idea was on the previous answer, and it is the use of the Locals window. I cannot replicate the issue, because it works as expected in my computer, so it is hard to determine what the possible cause be. One thing you may try, does it not work...

Excel: Excel VBA Find, buggy code, number 0

buggy code, number 0, excel sheet: Change your code to this: Sub Validate() Dim A As Range, d As Range Dim Results As Variant Set d = Sheets( Sheet1 ).Range( E5 ) destination range Set A = Sheets( ReceiptingQs ).Range( D7:D58 ) A.Activate Range( D7:D58 ).Select c = 1: i = 1 ...

Excel: Excel VBA-Formatting a flat file report including inserting sum formulas, vba code, excel vba

vba code, excel vba, sum formulas: Matt, I got your message re: your file, but apparantly you are not getting mine. I replied to your message requesting more info and never heard back. Checking my email log tonight I found this message: Your message Subject: RE: Excel VBA-Formatting...

Excel: Excel VBA Forms, excel vba, outlook application

excel vba, outlook application, subject text: Katherine, it just takes this line of code: Set myAttachments = MailItem.Attachments.Add( C:DirectoryFile.xls ) If that file is currently open and hasn t been saved, you may have issues. I haven t tested that, so I m not sure. Enjoy Jeff...

Excel: Excel VBA Forms, excel vba, font characters

excel vba, font characters, excel help: Hi Katherine, I only know some VBA but... As far as Excel goes there are many different limits on different objects within excel. Excel limits textboxes to 255 characters. (See links below). Excel also limits the display of characters to 1024 per...

Excel: Excel VBA on formula, range a11, excel vba

range a11, excel vba, sheet1: You make A11 a text string. then you set A1 s formula to the value of A11. When Excel assigns it to A1 s formula, it assigns it as a text string. If you then click in A1 and hit enter without making any changes, it will be reevaluated as a formula....

Excel: Excel VBA Help, tag property, ogilvy

tag property, ogilvy: There was an undocumented way, but I don t believe it is supported in xl2002 or xl2003. (undocumented procedures are not guaranteed to be supported). It would be better to set the parameter property or the tag property of the control and inspect it...

Excel: Excel VBA to hide rows based on calculation, vbas, false application

vbas, false application, sheet tab: I suspect the problem is that for no change, interior takes xlNone and Font takes xlAutomatic, so Private Sub Worksheet_Calculate() Dim icolor As Integer, cell As Range On Error GoTo ErrHandler Application.ScreenUpdating = False Application.EnableEvents...

Excel: Excel VBA to hide / unhide a picture, excel vba code, s29

excel vba code, s29, rectangle: In this case, don t use the With statement and add one line for each object inside the if. Something like: Sub HideShowObjects() If ActiveSheet.Range( S29 ).Value Then ActiveSheet.Shapes( Rectangle 2 ).Visible = True ActiveSheet.Shapes(...

Excel: Excel VBA - Get image file, flag image, image folder

flag image, image folder, country names: If you know how to use macros, JE McGimpsey has already written code that does very close to what you want. You would probably need to make a few modifications to get it to do exactly what you want: http://www.mcgimpsey.com/excel/lookuppics.html If...

Excel: Excel VBA with Lan ID and passwords, network login name, computer disclaimer

network login name, computer disclaimer, excel vba: I m not sure about the LAN id, but you can log ccess on a hidden worksheet - named Log . Try adapting this code DateTime : 18/02/2007 09:42 Author : Roy Cox (royUK) Website : [url=www.excel-it.com] more examples[/url] Purpose : Log...

Excel: Excel VBA: How to list out combinations, redim preserve, ss ss

redim preserve, ss ss, msgbox: I have it show a msgbox as an indicator - however, the msgbox can only show 255 characters I belive so the list is truncated - but the variable ss contains all the combinations. I have left in code to write the results to the worksheet if you want to do that...

Excel: Excel VBA/Macro to Find from, vba macro, excel vba

vba macro, excel vba, complete database: Jad, You don t need VBA/Macro coding to achieve what you need to do. You can simply use Conditional formatting 1. Assumptions - Sheet1 has the database (Columns A:G, Rows 1 to 100). Sheet 2 has the entered data 2. Create a named range in Sheet2. ...

Excel: Excel VBA Macro help needed, vba macro, ac log

vba macro, ac log, false selection: Angie, I have attached a somewhat more elegant solution that might be of help and you should be able to tweak it as needed Copy this code to the final excel file that you have. Open all the input excel files. activate your final file and run the macro....

Excel: Excel VBA or Macro, microsoft excel 2002, vba commands

microsoft excel 2002, vba commands, excel vba: Use a little creativity and imagination. Simply create a new column and use =Hour(B1), for example where B1 contains a time, to get the hour portion of the time. I think the pivot table will let you do the same thing inside of it but it is probably easier...

Excel: Excel, VBA Macros and Vista, excel vba macros, excel2003

excel vba macros, excel2003, learning curve: The converter makes no mention of Excel 97 and I m not familiar with the converter anyway. My guess would be that it would NOT be able to open excel 2007 files (saved as normal xl2007 format). Your program is 10 years old -- I think it s time to upgrade,...

Excel: Excel VBA/Macros, relative coordinates, excel vba

relative coordinates, excel vba, cell location: Hi Brian May I ask you some questions about your project ? How many check boxes do you have on your spread sheet ? What code do you have for initialising each check box at the start of a new interegation ? Do you know about clickbox events ? ...

Excel: Excel VBA - Named Ranges, home office computer, target range

home office computer, target range, test cells: Unfortunately, there s no event for trapping the insertion of rows or columns. You can, however, know when the SELECTION is inside the range, but the only way you may be able to trap the change of size of TEST is to either check the # of cells in the range...

Excel: Excel VBA - Need help with "Find", excel vba, p77

excel vba, p77, correct syntax: Hello Bob, Assuming if I typed Masterfile in the Name box on the formula bar, I would go to K1:P77, (so it is a defined name in Insert= Name= Define) then Sub FindValue() Dim rng as Range Set rng = Range( MasterFile ).Find(What:=Range( A4 ), _ After:=Range(...

Excel: Excel VBA OnAction, excel vba, control toolbox

excel vba, control toolbox, textboxes: Hello Carrie, If the boxes are from the forms toolbar, then this is very straight forward. For example, for a textbox: Sub tbox_Click() dim tbox as Textbox, s as String s = Application.Caller set tbox = Activesheet.Textboxes(s) msgbox tbox.name...

Excel: Excel VBA Paste Link, excel vba, paste link

excel vba, paste link, excel workbook: It s going to be something like dim cell cell is a variable for each cell in range( range ) cell.formula= [servershare oldercopiedFile.xls]Sheet1! & cell.address next I m not 100% sure about the start of the formula, but you can always record a...

Excel: Excel VBA Procedure, vba procedure, excel vba

vba procedure, excel vba: Jeff Ck out your question and see if you can reword it I m not sure exactly what Column A is the holds the information that is common to the list worksheet and the other worksheet. or what your data looks like, or what the criteria is for deleting or...

Excel: Excel and VBA passwords, douglas m smith, excel passwords

douglas m smith, excel passwords, password character: Hi Alan, Yes, Excel passwords are easy to crack. There are dozens of program on the market to do it. The password character limit in the Save As dialog box is 15 characters, although it may be longer if you set it through VBA. (I haven t tried it.) ...

Excel: Excel VBA. Real time statistics, real time data, data cells

real time data, data cells, excel vba: Hi m.lock, 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: Excel VBA & Search, target value, excel vba

target value, excel vba, sheet1: That s not trivial at all (neither was my 1st answer), and I m quite busy at work do I don t know when I can get to this -- I d estimate it could take a week or more before I have that kind of dedicated time. It s not really a simple question, but a programming...

Excel: Excel VBA - If Statement, excel vba, cheers

excel vba, cheers, angela: Hi Angela How are you? Yes means different, not equal Have a look at this code Sub hhh() Set ws = Sheets(1) If ws.Name tab3005 Then MsgBox the name is & ws.Name Else MsgBox ok End If End Sub Cheers Adelaid...

Excel: Excel VBA to Summarize Duplicate Entries, irow, qty size

irow, qty size, size feet: Try this Option Explicit Sub CreateStraightBarSummary() Dim iLastRow As Long Dim i As Long Dim iRow As Long iLastRow = Cells(Rows.Count, 1).End(xlUp).Row For i = iLastRow To 7 Step -1 Cells(i, 5) = Cells(i,...

Excel: Excel:VBA Syntax Question, syntax question, excel vba

syntax question, excel vba, 43505: Where is Dec ? and Jan ? etc - it seems you re stuck on using D1 currently and would like to use another cell(s) (E1? D2?) for the other month(s), but I can t really tell from your description - is there any way you can be mroe specific regarding cell addresses?...

Excel: Excel VBA to search Inventory record., cell c2, cell b2

cell c2, cell b2, inventory record: Hi Ling, Hope you are doing well. You don t need VBA. Just formulae would suffice. Assumptions: 1. Sheet1 named Inventory has 3 columns. Col1 - BarCodeNum Col2 - Name Col3 - Status (2 values - blank or scanned ) Data from Row 2 onwards ...

Excel: Excel VBA Userform reset, excel vba, combo boxes

excel vba, combo boxes, initialize event: It is an odd behavior in a way. All you need to do is add a .Clear line before you add items With .cboStatus .Clear .AddItem A .AddItem A&F .AddItem C .AddItem C&F End With Or, if you unload, as opposed to hiding it, the...

Excel: Excel VBA, position parameters, excel vba

position parameters, excel vba, inputbox: Try Application.GoTo Reference:=Range( A100 ), _ Scroll: = True This should take the cursor to A100 and A100 should be the top left corner cell of the worksheet. Concerning your InputBox function, the Xpos and Ypos of the Inputbox are in twips. See...

Excel: Excel Vba/Macro to compare data on 2 sheets., vba macro, conditional formatting

vba macro, conditional formatting, excel vba: I would use conditional formatting for this. First, select column E on sheet2 and create an Excel name for the column (Insert Name Define, with a name of say Data). On sheet1 select all of the data starting at row 1 down as far as you need. Goto menu...

Excel: Excel Versions 98,2000,2003, excel 98, vba

excel 98, vba, ms excel: To detail ALL of the differences to each version will take a LOT of typing, wouldn t you suspect? But, yes they will depending on how you write your code or more specifically what options of certain things you are doing that you use. For example, later...

Excel: Excel Versions, statistical functions, hi bob

statistical functions, hi bob, excel 97: Excel 2000: Improved Open & Save; Collect & paste with office clipboard; See-through view; Euro currency sumbol; 4-digit date formats; List autofill Excel 2003: List functionality; Integration with Sharepoint; Improved statistical functions; XML support;...

Excel: Excel Vertical Lookup Problem, vlookup, trial balance

vlookup, trial balance, concatenation: George: After looking at your workbook, the problem is arising due to the fact that the series of numbers you are looking up from your concatenation are numbers and the lookup table has those account numbers listed as text or vice versa. I don t remember...

Excel: Excel VLook Up, vlookup formula, error value

vlookup formula, error value, string value: If vlookup returns and error value, then it should be handled by the first argument of the OR command. If the data actually contains a string value #N/A then you should check for a string value =IF(ISERROR(VLOOKUP(AG2, Label key !D$2:E$563,2,FALSE)),...

Excel: Excel VLookup, pain in the arse, colb

pain in the arse, colb, autofilter: Sorry it s not at all clear what you are asking. I really would recommend that all data is entered to one sheet then use Advanced Filter,AutoFilter and or PivotTables to manipulate the data. Putting data into two sheets is just inefficient. There s a PivotTable...

Excel: Excel Vlookup, employee id, a3

employee id, a3, match: Formats should not be the issue since names are always text. Try inserting the range address for the demo range name, or more accurately the range address of where the data is, to see if your range name is incorrect and identifies the correct physical range....

Excel: Excel Voting Buttons, creating a survey, spreadsheet

creating a survey, spreadsheet, tool bar: Hi Nick, I m not exactly sure what you are trying to do, but to add a button, you should just need to go to view/toolbars/and I think it is a buttons tool bar, but I m not currently at a computer where I have access to excel, so I can t tell you what the...

Excel: Excel vab 'Next' statement, a1500, vab

a1500, vab, b1: Hi Julian How are you? Just delete it the end if statement and everything will be fine. Sub Button1_Click() Range( B1:B1500 ).ClearContents Dim a As Range Set a = Range( A1:A1500 ) For Each C In a If C.Value 3 And C.Value 6 Then C.Offset(0,...

Excel: Excel - validation list, data validation, two different ways

data validation, two different ways, microsoft excel: Hi Eileen, There are two different ways you can do this. First if it is a short list your are entering for data validation you can simply type in the list. Such as typing in the source box Bob,Sue,Tom,George. If it is a long list. You can select a separate...

Excel: Excel vba 'Next' statement, douglas m smith, excel vba

douglas m smith, excel vba, a1500: Hi Julian, Just delete the End IF statement. Your two If statements are self contained and End If is not needed. If your code was structured a little different, then it would be needed: If C.Value 3 And C.Value 6 Then C.Offset(0, 1) = 1...

Excel: Excel-vba, cell a2, excel vba

cell a2, excel vba, cell a1: S. MUTHUVEL, I m not sure I completely understand your question, but if you cut and paste this VBA routine into your VBA editor (Alt+F11 from Excel, then insert Module. Alt+F11 gets you back to Excel) and then assign it to the ECE button (right click...

Excel: Excel version 4, backward compatibility, explanatory notes

backward compatibility, explanatory notes, excel 2002: Most likely there s no problem, due to backward compatibility inside versions of Excel. However, you MIGHT run into some condition which could be a minor problem which needs correcting. But Excel 2002, 2003,... do run excel 4 XLM-type macros, usually with...

Excel: Excel version 4, conversion issue, column headings

conversion issue, column headings, aidan: I no longer have access to version 4 of Excel (that was a LONG time ago!) but I remember that they were single worksheets - other than that, they SHOULD open OK in XP I would expect - if by column headings you mean typed information, then it may be a conversion...

Excel: Excel - vlookup, excel vlookup, vlookup formula

excel vlookup, vlookup formula, format cells: Jason: The most common cause of this issue is when the cell has somehow been changed to a text format. Subsequently, everything entered into the cell is treated as text. Presuming the above is the problem, here is how to solve it: 1) Select the...

Excel: Excel/Winzip, excel sheet, zip file

excel sheet, zip file, winzip: I don t understand youe question. WINZIP can t create a .zip file from an individual worksheet since a worksheet is not a workbook/file. A sheet is a part of a workbook. The workbook you create a .zip file from will have that sheet in it simply because...

Excel: Excel, Word, information paragraph, ogilvy

information paragraph, ogilvy, support microsoft: Carl, The information on how to turn it off in each program can be found in this article: http://support.microsoft.com/kb/816949/en-us Description of the Getting Started task pane in Office 2003 It is below the More Information paragraph. --...

Excel: Excel - Word 2002, spreadsheet cell, header footer

spreadsheet cell, header footer, excel spreadsheet: Yes and No. (understand that I don t know that much about word). From what I can see, if you do view header and footer and choose footer, on the header footer toolbar the first item is Autotext. If you click on that, one of the options is Filename and Path....

Excel: Excel and Word Question, word question, colums

word question, colums, sc1: Yes, but it will involve a fairly extensive macro. The two applications can not talk to each other the way I think you are thinking about - a macro will have to get involved. But since you did not say much about how you were or are going/planning to...

Excel: Excel & Word, number and a name, work sheets

number and a name, work sheets, word form: You will need to do this using VBA to control this - I can write it for you, but would need more information - for instance, you mention several different work sheets but don t specify how these relate to the word document. My email is aidan.heritage@virgin.ne...

Excel: Excel Work Books, newname, work books

newname, work books, c data: Sub Name2Files() OldName = C:DataTable1.xls : NewName = C:Results able1.xls Name OldName as NewName OldName = C:DataTable2.xls : NewName = C:Results able2.xls Name OldName as NewName End Sub I don t know what you re looking for re 2 different...

Excel: Excel Workbook Copy, cell a1, excel tips tricks

cell a1, excel tips tricks, column widths: Assuming cell A1 of sheet1 contains the name to save this under, this (untested) procedure should work (attached to the button): Sub MakeMeValues() For each sh in Sheets sh.cells.copy sh.cells.pastespecial xlpastevalues next thisworkbook.saveas...

Excel: Excel Workbook and Worksheet Passwords, excel workbook, master password

excel workbook, master password, ogilvy: Each sheet must be protected individually Each sheet must be unprotected individually. This burden can of course be eased by using a macro if you are familiar with macros. If the sheets don t need to be protected, but you want the workbook password...

Excel: Excel Workboon Open Event, faulty code, macro security

faulty code, macro security, excel 3: Hi Sampath, How are you? The easiest thing I can suggest is: 1. To set the macro security at medium level (tools - macro - security) 2.EXIT EXCEL 3. Open the workbook and answer Enable 4. TOOLS - MACRO - VB EDITOR 5. Correct the code Or you...

Excel: Excel to Works, office 2000, formatting

office 2000, formatting: Grant I think your answer, instal Works on mine and develop the forms using Works , is the simplest most straight forward one. I know of no reason that excel wouldn t open in works with formatting, etc. intact. sorry I couldn t be of more specific...

Excel: Excel Worksheet appears more then one., excel worksheet, upper right corner

excel worksheet, upper right corner: that s 3 different views of the same workbook, created by Window/New Window. You correct it by closing any of them BY THE X in the upper-right corner. The first time you do it you ll be left with only :1 and :2. the 2nd time, you ll be done. But now you...

Excel: Excel Worksheet Function, procedural documents, month today

procedural documents, month today, excel worksheet: Hello Jeanne, Yes, you can have a formula do that =date(Year(today())+2,Month(today()),Day(today())) The problem is that just like any other formula, when the values of the arguments change, the formula results can change. In this case, everytime there...

Excel: Excel Worksheet- Matching Entries in Two different Columns- sorting and listing/ deleting, d230, excel worksheet

d230, excel worksheet, isna: Without macro: In cell J1, say, enter this (by holding ctrl+shift, then press enter): =MATCH(1,(NOT(ISNA(MATCH(D6:$D$2200,$H$6:$H$2350,0)))*(J2 ROW($1:$28))),0) and fill down until you hit #N/A values - these are the locations of the duplicates. In K1 enter...

Excel: Excel Worksheet, excel worksheet, cells

excel worksheet, cells: You could have an extra column in your workbook that contains the linked workbook data. That column would have a sequential list of numbers that defines the desired sort order. Then you can sort the data by that column. Make sense? Otherwise, you will...

Excel: Excel Worksheets, microsoft office professional, microsoft office professional 2000

microsoft office professional, microsoft office professional 2000, index tab: Pam You can write a formula that looks like this: =SUM( week 1 !C6:C9)+SUM( week 2 !C6:C9)+SUM( week 3 !C6:C9)+SUM( week 4 !C6:C9)+SUM( week 5 !C6:C9) You can also use 3d referencing in a formula to check that out go to help/index tab/type in sheets/click...

Excel: Excel Worksheets in Word, using ms office, word template

using ms office, word template, aidan: If the workbooks are created from file (insert object, create from file) then you are going to be able to do this - otherwise I think the problem is likely to persist - what hasn t been made clear is WHY the worksheets are embedded - this may POSSIBLY give...

Excel: Excel Worksheets, excel vba, cheers

excel vba, cheers, cells: Hi Mark, How are you? Please try the following: 1. Write the name you are looking forward in, e.g., A1 of Sheet1 2. tools-vbe 3. insert-module 4. copy Function ShExist(A As Range) As Boolean Dim b As Boolean s = 1 b = False Do While s = Sheets.Count...

Excel: Excel web query, ms excel 2000, microsoft knowledge base

ms excel 2000, microsoft knowledge base, default web browser: Hi Jim, I certainly cannot say I know more than the Microsoft Knowledge Base. A good place to look for problems like this. Therefore I recommend that you read the article relating to your problem at. http://support.microsoft.com/default.aspx?scid=kb;en-us;218153&Product=xlw...

Excel: Excel web query, ms excel 2000, modem toshiba

ms excel 2000, modem toshiba, toshiba pcx2000: Thanks for the extra information - basically, you SHOULD be connecting as you have a permanent connection based on what you are telling me (or at least my understanding of it) - you are not on a network so that can be discounted. However, this doesn t get...

Excel: Excel web query, ms excel 2000, proxy account

ms excel 2000, proxy account, web query: As I stated, I was able to do the web query using Excel 2000, so the problem isn t Excel but something wierd about your Internet configuration. Are you using a proxy account? a firewall? Any kind of non-standard configuration could cause problems. Sorry...

Excel: Excel weekend, workday, workdays

workday, workdays, correct answer: All that s necessary is that you always add one day to the first argument of the WORKDAY() function, and subtract one after the end of the function. The explanation is sort of complicated, but if you really want to know... The WORKDAY() function assumes...

Excel: Excel to word reformatting, cell1, word application

cell1, word application, word document: Two options - one is to have the macro set the tab stops, the other is to use a table - here is the table version which is PROBABLY the best choice Sub produceletter() Dim appwd As Object Dim myvar As String myvar = Range( A1 ).Value Dim counter As...

Excel: Excel worksheet email, bottom turn, course excel

bottom turn, course excel, excel workbook: It sounds like you d like to learn how to isolate a worksheet in its own workbook. Follow these steps: 1) On the worksheet tabs at the bottom of the window, right-click the worksheet you want to e-mail and select Move or Copy... 2) In the Move or Copy...

Excel: Excel worksheet formula for ever reducing %, payout structure, vlookup

payout structure, vlookup, excel formula: Use =VLOOKUP() with a table that reduces the percentage rate as the sales figures increase. Here s an example that s very simple, but will show the structure that you can use in all similar cases. In range A1:A3, enter 0, 100, 1000. In range B1:B3, enter...

Excel: Excel, worksheet function, sumproduct, excel worksheet

sumproduct, excel worksheet, a100: Hello Joe, I assume that the word active is the only entry in the cell if it is to be counted. Assume the numbers are in A2:A100 and the word in F1:F100 =Sumproduct(--(A1:A100 90),--(F1:F100= active )) This is a case insensitive test. The above...

Excel: Excel worksheet tabs, print screen button, sheet tabs

print screen button, sheet tabs, excel worksheet: Sort sheet tabs: http://www.cpearson.com/excel/sortws.htm print showing the tabs - no, you need to create a footer. You CAN print by hitting the Print Screen button, which puts the screen into the clipboard, then you can paste this onto a sheet and you...

Excel: Excel worksheets, row widths

row widths: Hi, Garry, You can protect the workbook to keep people from changing the structure. Depending upon which version of Excel you have, you might be able to pick and choose which structural aspects you can protect. Go to Tools/Protection/Protect Workbook...

Excel: Excel worksheets, standard pacific, pulte homes

standard pacific, pulte homes, microsoft excel: Hi Jim, I forgot to answer your first question. We are a framing subcontractor for residential housing in the Bay Area California. We frame approxiamately 400 homes a year for developers such as Pulte Homes, Standard Pacific, etc... Thanks Ke...

Excel: Excel -writing to a specific cell based on row and column variables, booking hotel rooms, column variables

booking hotel rooms, column variables, chip pearson: Robert, It sounds like you would need a macro for this. It doesn t sound like something that can be done easily with formulas (if at all). Possilby you can go to the date by room table and put in a formula like =if(countif(Sheet1!B:B,1234) 0, Booked ,...

Excel: Excel X Y Chart, label option, data labels

label option, data labels, data columns: Hi Todd, I am not absolutely sure from your question whether you want to label each plot POINT (each data point in a series), or label the series itself. I am going to assume you want to label each point, but feel free to follow up with a clarification...

Excel: Excel XP, cell reference, meg ryan

cell reference, meg ryan, column c: Gail If I understand your question correctly you need a SUMIF formula such as the following: +SUMIF(B1:B100,2660,C1:C100) The formula says if within the range B1 to B100 where ever there is a 2660, sum the amounts in the adjacent cells in the range...

Excel: Excel XP, window bottom, users machine

window bottom, users machine, pc management: Yes it can be done, BUT it s a setting on an individual users machine, so cannot be set globably! The setting you have found is the right setting, but is machine specific - there IS an option under the edit, links menu for startup prompt, but this is a secondary...

Excel: Excel XP Advanced filtering, place filter, blank rows

place filter, blank rows, bottom turn: Hi, Eric, Let s say that you have labels in A1:I1, and that your data goes from A2:I3868. Follow these steps to find all the records that have unique values in column C. 1) Copy the labels in A1:I1 to a completely blank area. It s important to copy them...

Excel: Excel XP, amita, a3

amita, a3, enquiry: Amita here are a couple of examples from the help file A Data 1 15 2 9 3 8 4 =IF(AND(A2 A3, A2 A4), OK , Not OK ) If 15 is greater than 9 and less than 8, then return OK . (Not OK) =IF(OR(A2 A3, A2 A4),...

Excel: Excel Xp office, rsq, c505

rsq, c505, column c: I hope we will be able to get this finished - I m fine with the save (do you want the entire workbook saved, or just the export page), but I m not sure what you need for :- To finish, what about from row 650, the polynomials such as degree 6 and coefficients...

Excel: Excel - less then Zero Sums, sum function, handsets

sum function, handsets, c3: Hi Jenna, The formula would be this to return a zero. =IF( Plan Rebates !B2+Handsets!C3 0,0, Plan Rebates !B2+Handsets!C3) Or this to return no value. =IF( Plan Rebates !B2+Handsets!C3 0, , Plan Rebates !B2+Handsets!C3) Notice there is not...

Excel: Excel, pivot table, iname

pivot table, iname, pivot tables: The nature of a pivot table is to summmarize. Therefore all common data in the row and columns that are for the same thing are summarized into one thing. Can you give me an example of why you want row data to be duplicated? Perhaps you could send me a...

Excel: Excel2000-Formulas, greyhound race, x18

greyhound race, x18, swbell: Please email the spreadsheet to Lotus@swbell.net. In addition, might I inquire what the question is? It sounds like you want a formula that looks for a * and then converts it to TRUE or FALSE. =IF(A1= * ,TRUE, FALSE ) A1 would be the cell that...

Excel: Excel2000 Workbook .xls XP windows, b5, b2

b5, b2, weights: where the formula says #VALUE, you re probably subtracting text. Inside the formula, select a piece of it (like select the B5 in a (B5-B6) formula and press F9 & see if it contains quotes. Not sure what you mean by the formula wont enter into the cell......

Excel: Excel2000-XP Home, macros, spreadsheet

macros, spreadsheet, cells: I do think there is a solution that deletes and overwrites on the previuos cell. Even if there is, it will cause you problems cause once you delete, the formula disappears. There is an approach that I should work as follows: You calculate all the your TRUE...

Excel: Excel2000-XP Home, spreadsheet column, downloaded data

spreadsheet column, downloaded data, double space: Gene Sorry I don t know a way to fix the download--it sounds like there will only be the choice of fixing the amounts individually. Or it could be that the amounts are delimited with a double space between the whole number and the fraction that is causing...

Excel: Excel2000 XP home, spreadsheet column, gmail

spreadsheet column, gmail, littlewing: Gene Excel should recognise the download format, one option is to select the column and go to Data and select Text to columns and see if that helps. If it doesn t can you send me a copy of the file you downloaded so I can see if there are any hidden characters....

Excel: Excel2000-XP windows, vba project, excel macros

vba project, excel macros, using tools: I can t imagine why they d be missing. Are you going to the VBE and doing a Search in the VBA Project? Or are you in Excel and using Tools/Macro/Macros... trying to run them? I d recommend the former... perhaps they re there and you put the macros NOT in modules,...

Excel: Excel2000 xp Home, spreadsheet column, downloaded data

spreadsheet column, downloaded data, correct answer: Hi Gene, I am not sure exactly what kind of data you have downloaded but here are a couple of areas that will most likely help. 1. You can simply select the data that you are deleting the 1/2 and replacing with 1/2 by selecting all the cells that are...

Excel: Excel2000 xp home, data 3, macros

data 3, macros, face value: Not clear what the last cell formula should be, or indeed what each cell should be in the case of three, four, five etc days - it LOOKS at face value as though they are all supposed to be =$D$3=D4, dragged down. If you can clarify the problem, I am certain...

Excel: Excel2000 xp home, spreadsheet column, downloaded data

spreadsheet column, downloaded data, correct answer: Most probably, when converting the data from IE, it might have a space in the begining or ending of the value. if this is the case then the functions will not work. so you need to remove the trailing spaces. You can simply write a macro to accomplish this....

Excel: Excel2003 Sheet protection, worksheet menu bar, format columns

worksheet menu bar, format columns, password control: Right-click the Excel LOGO near the file menu, select View Code, put this in: Private Sub Workbook_Open() Application.CommandBars( Worksheet Menu Bar ).Controls( Edit ).Controls( Delete Sheet ).Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel...

Excel: Excel97 average times, excel97, mm ss

excel97, mm ss, hi bob: I m not clear what the problem is - just subtract the fields and include the Newtorkdays, which will give you the day portion in whole #s, then you can also subtract the times by a simple subtraction and also subtract the 14:00. So, it s something like this:...

Excel: Excel97-Excel2000, excel2000, excel 97

excel2000, excel 97, excel 2000: They re there -- if you know the name of the macro you can simply F5 (edit/Goto) to it -- you ll have to type the name in. Or, press Alt/F11 to get you to the VBE, then use ctrl/F to find something in your code (make sure Current Project is selected...

Excel: Excel97 and Macros, excel97, cell a1

excel97, cell a1, sort data: Well, no formula will insert a row, so you will need a macro. Try this (untested) Sub Inserter() for i=range( A65536 ).end(xlup).row to 1 step -1 if cells(i,1).value cells(i,5).value then rows(i+1).insert rows(i).copy rows(i+1)...

Excel: Excel97 Macros, douglas m smith, dos prompt commands

douglas m smith, dos prompt commands, macro sheet: Hi Rick, VBA has a full compliment of disk/file commands, most of which look like DOS prompt commands, like Dir, MkDir, and the one you want, Kill. The syntax would be: Kill C:inventory empinv.xls For details on Kill (or any other command) type...

Excel: Excell, debits and credits, negative numbers

debits and credits, negative numbers, spreadsheet: I am not sure on how this will integrate with your existing code, but at least it may give you some ideas: Sub CheckPositives() Dim rngStart As Range Set rngStart = Sheets( Sheet1 ).Range( A1 ) With rngStart For i = 1 To .CurrentRegion.Rows.Count...

Excel: Excell, conditional formatting, cell a2

conditional formatting, cell a2, format button: use Conditional Formatting. To apply a format condition to a cell or range of cells, first select the range to which you want to apply the format condition, then open the CF dialog from the Format menu. Then change the drop down to Formula is and type this...

Excel: Excell 2003, microsoft windows registry, microsoft knowledge base

microsoft windows registry, microsoft knowledge base, knowledge base article: All I know about this feature is in the knowledge base - I would GUESS that the file is present in the save location, but hasn t been automatically loaded - knowledge base article set out below: Description of the AutoRecover functions in Excel 2002 and...

Excel: Excell 2003 and macro's, memory problem, macro security

memory problem, macro security, memory allocation: Kevin It sounds like it could be a rights problem or a memory problem with the server. I haven t found any definitive answers to your particular problem though. If you were to close one of the open files and then open another does the macro then work?...

Excel: Excell 2003 and macro's, freeing up system resources, macro security

freeing up system resources, macro security, excel workbook: I have experienced a few oddities with opening Excel files over a network location. Typically they are solved as you have done (open them locally), or they can also be solved by freeing up system resources. This includes, but is not limited to: 1) Closing...

Excel: Excell 2003 and macro's, macro security, excel workbook

macro security, excel workbook, design mode: Unfortunately no I don t - in our office, I find that if I have a lot of files open, with security set to medium, excel interprets this as high - and to get round it I have to set to low, which is interpreted as medium! I have not experienced the problem...

Excel: Excell 2003, b6, b5

b6, b5, cells: Assuming that the formula references the cells in the same workbook (file, not worksheet) then the formula can not return a value if the cells that are referenced in the formula are empty. And if they reference another workbook then the same holds true. ...

Excel: Excell, parens

parens: Example of what you need to do: Instead of =AVERAGE(G30,K30,O30,S30,W30,AA30,AE30,AI30,AM30,AQ30,AU30,AY30,BC30) (short version, but demonstrates a solution), use =AVERAGE(G30,K30,O30,S30,W30,AA30,(AE30,AI30,AM30,AQ30,AU30,AY30,BC30)) (note the 2nd set...

Excel: Excell Automatic Static date and time stamp, time stamps, macro solutions

time stamps, macro solutions, timestamps: This is a good reference for timestamps in Excel: http://www.mcgimpsey.com/excel/timestamp.html In your case it looks like the macro solutions will be better, as you will need to code the event of workbook changes. If you have doubts about how to make...

Excel: Excell adding rows, autosum button, addition formula

autosum button, addition formula, automax: Tom - Click in the cell you want to put the grand total. Look up in the toolbars - there is a button that looks something like a Greek e . This is the Autosum button. Click it. There will be a dotted, dancing line. If that line is around E3:E54,...

Excel: Excell automation, vba code, automation

vba code, automation, cells: There isn t code as such to do this, but you could either unprotect and reprotect the sheet if you need to clear the entire sheet (activesheet.unprotect and activesheet.protect will do it) OR use an on error statement to enable the clearance of only cells...

Excel: Excell copying of cells, cell b1, spread sheet

cell b1, spread sheet, copy paste: Hi, Tom, Let s say that you d like to copy cell B1 on Sheet1 over to B1 on Sheet2. If you want to do a simple copy/paste without linking between the two, you can do so as follows: 1) Click on B1 of Sheet1 and press Ctrl/C. 2) Go to B1 of Sheet2 and...