conditional formatting & formula

Hello I'm working on a spreadsheet and would like to highlight the highest value across. i.e. Name 1 2 3 4 5 6 7 Mary 1% 2% 2% 25% 45% 15% 10% These are the student's probabilities to achieve grades 1 to 7 I'd like a conditional Format to highlight the highest probability, in this case 45% I would also like a column on the right of 7 to show the grade that corresponds with the highest probability in this case 5. which may have to be a formula Appreciate any help on this

Count Tick Marks from Code given by Jacob Skaria

I got this code from Jacob and now I need to know how to tally the columns and give a percentage of how many "x" marks were made. I am using Excel 2007, and no absolutely nothing about code, except maybe how to paste. Here is the code: Another workaround with fonts. Single click on any range to check/uncheck. 1. In the required range (suppose ColD) in all cells enter small letter "e". 2. Select all cells in that range and change the font to Marlett. 3. Adjust the column width to look like a square check box 4. Right click on the sheet tab and hit view code. Paste the below code

Subtracting/Adding dd:hh:mm:ss

I am receiving data from several counters on field generators. The time is in dd:hh:mm:ss. I am needing to calculate the run time on the equipment from one day to the next and then sum the total time run for the week. I have created a custom format for dd:hh:mm:ss for each reading but the subtraction (for calculating the daily run time) or addition (sum of daily run times) formulas are not working. Help please! -- Lucy

Transpose large data from columns to rows

Hello thanks for the help if possible. I have 2 columns. Column A contains names of employees column B contains the trainings the employees attended. Each employee has different number of trainings (I got this data sorted by simple pivot table) A B Employee X training 1 Employee X training 2 Employee X training 3 Employee Y training 3 Employee Y training 2 Employee Z training 5 Employee Z training 7 Empl

Expand list in Excel.

Hi. I had made a list menu (Validation data, drop down list etc.) in an Excel sheet. I would like now to expand (to add new cells with extra data) to the list. I tried to do this but I couldn’t . Is it possible ? How? (I don’t want to write the total workbook again from the beginning) Thank you.

Automatically closing slave workbook when main workbook is closed...

Hi Have two workbooks - lets call them workbook1 and workbook2 for ease. Workbook 1 is the main document, however, some of data is drawn from Workbook 2 which is currently mininmized by following code in VBA Workbook1: Private Sub Workbook_Open() Workbooks.Open "Workbook2.xls" ActiveWindow.WindowState = xlMinimized End Sub Ideally I would like Workbook2 opened and hidden so that user does not even know it is running and then shut down automatically when Worokbook1 is closed. PS - when Workbook1 is saved it will be renamed, however Workbook2 title will stay static. tha

VBA Syntax problem

I am trying to set up a macro which would only run if there is a value in any of the three cells to the left of active cell. The following does not seem to work and would appreciate any help in correcting this: If ActiveCell.FormulaR1C1 = "(SUM(RC[-3]:RC[-1])<>0" Then run macro code End If JP

Help!! urgent

I was using Excel 2007 quite happily, and must have hit something on the keyboard, because now every time I touch keys windows spring open and I am powerless to do anything on my spreadsheet. I have tried restarting the computer. How do i turn off this feature?

show with messaje box when it's time and date

HELLO FRIENDS NEED HELP I HAVE THE COLUMN (A) The time to write date and hour Next notice /Alarm, I need Warn me when it's time and date of notice/alarm start up show with messaje box Message with the message that I write in the same row of column ( B) in the same row EXAMPLE: A B 1 26/04/2010 10:00 AM payment LIGHT 2 03/05/2010 4:20 PM Remember payment Phone 3 06/04/2010 8:40 AM Appointment with Dr. Saludo KM**VE

Replace massively formulas with new formulas

Hi there, would like to get your input/hints on how to solve the following: In a series of cells in a row, I already have a formula calculating a division among two other columns. The rows are many (more than 500). But now I need to apply a ROUND formula, on top of the existing ones. ex. cell C5 = A1/B1 and I want to massively apply for all cells in a row the formula: =ROUND(A1/B1,3) I need the accuracy of the values to be at the 3rd decimal and not just the appearance of the cells. I also need to avoid VB programming. Anyone who can help? THanks M

Print Dialogue Box

What is the macro command (code) to just open the Print dialogue box without printing? I tried this macro ExecuteExcel4Macro "PRINT(2,1,1,1,,,,,,,,2,,,TRUE,,FALSE)" but it printed the worksheet. Than kyou.

vlookup

How do I use vlookup if ret =abcd, dept = 999 and return value of no = 4 ? ret dept no abdf 123 1 abcd 234 2 defg 999 3 abcd 999 4 rgrg 456 5 wdff 547 6 abcd 888 7

compute one of two missing value?

I want to create a table like this: column A is the name of the person column B is height in feet column C is height in meters How do I set it up so that if I enter a number in the feet column, excel would calculate and fill in the meter column, and if I fill in the meter column, excel would calculate and fill in the feet column?

Excel 2007 How To Add New Number Formats to Styles

The Styles Functional Group in the Home Tab contains the following Style choices Custom Good Bad Neutral Data Model Titles and Headings Number Format When I created a new style it went into the Custom category but I'd like to move it to the Number Format choice. How can I add a new Style to a specific section of the Style functional group? Thanks for reading and for any help you provide. Regards (8^>)-]=[

Conditional formatting

I am using Excel 2007. I am trying to format time values that can range from seconds to years. If the time is below some threshold, say 1 day, I want to use the hh:mm:ss format. If it's larger that 1 day, I want to use the 0.00 format. After a little study, I selected this conditional format string: [<1]hh:mm:ss;[>=1]0.00 It seems to do the job. After some thought, it occurred to me that the second test might be unnecessary so I modified it to: [<1]hh:mm:ss;0.00 This also seems to work correctly. Is there any difference between these formats? Is one preferable?

Extracting Values from Text

Thanks very much in advance for any solutions that can be offered. What formula would I use to provide the following result: A B C 1 123456-text 3" x 6 7/8" etc etc 3 6.875 2 654321-test 104 1/2" x 80 3/4" etc etc 104.5 80.75 The length is always shown in inches (indicated as such the the single quotation symbol). The length could be a whole number of fractional. The spacing and position of the text above is exact (but length of characters may vary).

Printing to Day-Timer pages from Excel 2003

I have old printouts that indicate that in the past I printed from an older version of Excel to 3.75"x6.75" pages, but I don't see any way to do that using Excel 2003. How do I do that from Excel 2003?

Excel 2007 Ribbon - Remove unwanted custom toolbars

We had a series of engineering worksheets created in earlier versions of Excel that utilized custom toolbars created solely for the worksheets. they would be attached to the files which is how they were distributed to guys out in the field. Now that we are in the Office 2007 realm, our custom toolbars have been inserted into the Excel ribbon when our worksheets were opened. We have rewritten our engineering sheets to work with Excel 2007. Our problem is we cannot get rid of the many custom toolbars that have been populated on our Add Ins tab when Excel 2007 is opened on our machi

Printing Multiple Sheets

I know that I've done this in the past without any problems, but I'm using a new (to me) computer running Excel 2003, and am having trouble printing multiple sheets at the same time. I'm trying to select 2-3 tabs and printing them all as a group. The advantage is that page numbers in the footer reflect the group of sheets vs. each individual one. It's also very helpful when converting to PDF. The problem I'm having is that the 2nd page is not printing properly. The right column is being cutoff. I've checked the print range several times, and it's correct, and the sheet

Macros behave differnet when invoked by different methods

Using XL 2003, i have recorded several macros. For example, i recorded one that changes the "Move selection after Enter", to toggle the selection either Up or Right. I created an icon, using the letter "Z" to invoke it. When I click on the icon, the press Enter, the selection will move as required. But, if i use Alt Z to invoke the macro, then i have to press Enter 2 times. I have noticed this with other macros, that when invoked using Alt + letter, then an extra Enter is required. How can i eliminate the extra "Enter"? Thanks... Tonso

Engineering Economy and the Decision-Making Process (Joseph C. Hartman) solutions manual

I have solutions manuals for these scientific textbooks .. They are all in PDF format .. If you are interested in any one, simply send me an email to macmorino(at)gmail(dot)com .. Please this service is NOT free. Here are solutions manuals to some titles.. Instructor's solutions manual for A Course in Modern Mathematical Physics by Peter Szekeres Instructor's solutions manual for A First Course in Abstract Algebra (7th Ed., John B. Fraleigh) Instructor's solutions manual for A First Course in Differential Equations - The Classic Fifth Edition By Zill, Dennis G Instructor's solu

Excel Automation Question

I am trying to open an Excel spreadsheet in VB 2008 using automation. Once the spreadsheet is opened, I want to modify it, and force a recalculation. The auto calc function is disabled. I can opene the spreadsheet and modify it successfully. When I try to get it to recalculate, all the cells with formulas show this: #Name? Thanks for your help. My sample code follows: Imports Excel = Microsoft.Office.Interop.Excel Public Class frmExcel Dim objExcel As Excel.Application Dim objBook As Excel._Workbook Dim objBooks As Excel.Workbooks Dim objSheets As Exc

Balancing

Hi I have a excel data range from that I have to create a daily balance register Say' in row 1 op value, row2 to row10 contains transcation details, I want a new row to be inserted in the end of the row (end of the each date) and the value of row1 & row2 to row10 to be combined in the inserted row any help ? Thanks Raj

excel for analysis

Hi, Please how may i use excel for analysis as in SPSS package.Otherwise which package works best for statistical analysis.

Indirect function call?

Suppose I have an array of sines with arguments such as =SIN(j*B3) where j is aninteger or another number. That is, the array has arguments conisting of a product of numbers multiplied by contents of a cell, for example. Is there a way to have a cell containing "SIN" or the like to provide the argument function. Thus,if I were to have COS or EXP in that cell my array would evaluate terms like =COS(j*B3) or =EXP(j*B3). I know I can do this if I have a separate array just for the arguments and use a similar size array for the results. I also know that I can copy and past o

docking menus to capture picture for instructions

I need to dock the pull down menus (ex: Tools) in the open position so I can use PrintKey to capture only the menu selection (not the entire screen) for added to instruction documents. Any help would be greatly appreciated!!! Thanks in advance!

Return 0 After Milestone is Reached

All, I am using this formula to calculate monthly taxes, which are based on a % of income. Once you've paid a certain amount of tax, you don't have to pay any more. I'm using this formula - which sucessfully works up to and including the month where you pay less than the average month. I now need to take it a step further, and say once the maxtax has been reached, return a 0. A4 to L4 are my monthly income amounts. =IF(SUM($A4:L4)*tax<MaxTax,L4*tax,(SUM($A4:L4)*tax)-MaxTax) Any suggestions? Thanks, Patrick

Position a chart according to the position of the userform

Dear all, In VBA, I am trying to move a chart according to the position of a userform. How do I calculate the x and y position of the chart so that it will be aligned with the position of the userform. Thank you Gaetan

Excel crashes

I got a user with Excel issues. Every time she tries to create/open an existing workbook , the program crashes. I've tried the following things to make it work but with no results: Uninstall/reinstall Office Uninstall proof and interop HKEY_CURRENT_USER\Software\Microsoft\Office changed that directory into HKEY_CURRENT_USER\Software\Microsoft\old_office and then reinstall. "AppName: excel.exe AppVer: 11.0.8033.0 AppStamp:449c383b ModName: msvcrt.dll ModVer: 7.0.2600.2180 ModStamp:41109752 fDebug: 0 Offset: 00037efb" BR Blacker

Macro to change pivot field

This is a re-post as my questions hasn't been answered by anyone... Hi there and thank you in advance for any help. I have written a macro that changes (hides / shows) fields on a pivot table. One of these changes the field used for the page fields section of the pivot table. However, when i add a field to this section, it defaults to All. How can have this field default to the latest value in this field. Let me explain further: I want the pivot to be shown either by date or by week. The macro then changes the page field section to show either the date or week field. If the

Data Validation dropdown list - don't want doubles in list

Hello, I am using excel 2007 and am creating a drop down list by selecting "Data Validation" and entering a column I have with different names in it that are repeated over again in no particular order. I need the list to only contain each name once to select from though. Is there any easy way to do this? Thanks, Ben

refer to same row in other columns by using entire column in formula

Context I have lots of columns of data which I want to do calculations on, and i always want to consider the same row only. I would like to use names in the formulas to make it easier to keep track of which columns are being used. So it's not as if I have a problem at the moment, just that it could be easier to follow if i used named ranges. Now, I noticed a 'feature' of excel and was wondering if it is safe to use. Here it is: Column A has, say, row numbers in it, so A1 has a 1, A2 a 2, etc. If in cell B1 I type: '=A:A', the result is 1. Below is cell the formula is typed i

Villes fran�aises...

Bonjour � tous, Qui aurait en magasin la liste des villes fran�aises de + de 10 000 habitants (avec le code postal) ? Note : Tout format de fichier (Excel, Texte, Etc.) convient. Si certains sont int�ress�s, j'ai la liste de toutes les communes fran�aises � dispo. (fichier .mdb) D'avance merci, jpg

Excel compatibiliy with newer version

I have Office 2000 and I understand there is a patch you can download where you can open files from newer versions of Excel. I can't seem to find anything like that on the MSOffice downloads. Can anyone point me in the right direction? Thanks and God Bless!! -- Sabrena Vick

Jump within IF statement?

I'm not sure if I can type my example here since its a diagram, so I'll try to explain generally what I'm trying to do. I made a tree diagram starting at the top with a question, which then branches into two branches for yes or no - IF(question here,yes action,no action). Now the tricky part is, I've gotten to a "no" branch where the action is the same as a "no" branch that is further up the diagram. Is it possible to tell the function to "jump" to another section within the same formula?

list macro or module on sheet

Hi all i have a workbook which runs lots of vba macros - what i would ideally like to do is to list on a new sheet the macros that i have run since opening the file. is there some code to do this. obviously i could put something into each macro to write its name somewhere, however i would like if possibel something more efficient than this? regards Simon

using sumproduct with a substring condition?

I have a calculation I just made that looks like this: =SUMPRODUCT(--(C5:C50="persons name"),I5:I50) which works out the hours billed from column I and works fine. Column E has a description in it which sometimes has at the end of the description the words "do not bill." Is there any way to add this condition to the sumproduct - i.e. exclude from the sumproduct any value in column I that has the "do not bill" sub-text in column e? Thanks, Ben

getting excel to give me a 'time to leave' time.

You would think this fairly simple. I want to be able to plug in a 1)start time, a 2)lunch out time, 3) a lunch return time, and then have exel tell me the the time to leave for an 8hr day. The problem comes in trying to subtract the morning hours worked + lunch from a constant, the 8. If I can get that to work, then it should be fairly easy to add that onto the lunch return time to get a clock out time.

Find (Ctrl-F) won't find "~"

The Find function (Ctrl-F) won't locate occurences of the "~" character. Is there some way I can search for that character?

Ctrl+v doesn't work correctly

I have Excel 2007 SP2. When I try to copy a formula using Ctrl+C and paste using Ctrl+V, only the copied value (i.e., not the calculated value using the formula) is pasted. When I copy using Ctrl+C and paste using a right mouse click, it pastes correctly. Also, if I copy using Ctrl+C and drag to the cells I want to calculate, it pastes correctly. If I copy using Ctrl+C in cell C1 (having divided A1 by B1) and then select cells C2-C3 and try to paste using Ctrl+V, I get a message that the data on the clipboard is not the same size and shape as the selected area, do you want to paste t

Excel solver

I cannot distinguish between the "precision" and "convergence" setting in the Solver. Anybody know about them. They seem to represent the same thing. Just increasing the "precision" seems OK. Thanks

Help with Vlookup Function

Hey there. I'm working with two sheets on a file. On the first sheet is a list of students and the schools that they attend, with the student in column A and the school in B. There are 200 rows. In sheet2, there is a list of the schools and a countif for the number of students at each. There are six students at School Alpha. I want to perform a lookup that returns the names of each of those six students, as listed in column A of sheet1. Any ideas? I'd appreciate it if anyone had any advice. If you need clarification, don't hesitate to ask. Thanks in advance.

sorting protected sheet not working

I'm getting the impression from my tests and reading various forums that this is not possible even though the protection dialog box has an option to allow sorting. I'm creating a spreadsheet on the fly from MS Access vba based on some SQL data. This is distributed to another company so they can fill in some of the missing data. There are 10 columns. The first 8 are for reference only and are not to be edited. The last 2 columns are the ones they can fill in if needed. So I'm protecting the sheet and unlocking the last 2 columns. All that works peachy but even when I say to all

Can't Run Macros; Blocked By Security

My hard drive crashed, and I had to re-install Office 2007. I open Excel, and try to open .xlsm workbook I'd created previously. when it opens, I have under the ribbon: Security Warning Macros have been disabled. So I click the Options button, and I get: "Security Alert This file contains encrypted macros that have been disabled because there is no antivirus software installed that can scan them. To run these macros, remove the encryption or permission restrictions on the file." The only option I'm offered in this popup box is "Help protect me from unkown content (recommended

Excel 2007 Macro shortcut key doesn't work but menu selection does

I have a very short set of Macro's one of my users has recorded. All of them worked at first but now one of them will not function via the shortcut key. It will function if you select and run it by using alt-f8, clicking on it then run. Out of curiosity I reinstalled office, same problem. Then I even gave him a new PC to play with. He recorded the macros and they ran fine for many days then the same problem recurred. Any thoughts? Thanks,

running macros

Hello, I have an excel file that it is opened every time I open excel (let=92s call it PERSONAL.xls). In it, I keep many macros that I frequently use. I still have to learn how to have the macros in this file operate in any other file that I open. For example I have this macro in the PERSONAL.xls. It supposed to create a link form the text in the cell (The file is a collection of search results). Sub Insert_Link() For I =3D 1 To 1000 R$ =3D Str$(I) a$ =3D Cells(2, R$).Value Sheet1.Range(a$).Select ActiveSheet.Hyperlinks.Add Anchor:=3DSelection, Address:=3Da$, TextToDis

selecting different columns

I want to do a select case that picks a specific column based on another, independent variable. Something like: select case colno case1 if a=1 then colno="C" (Select column C) if a=2 then colno="M", etc. Then I want to do a range select, such as range(2,colno).select, or range("colno,2").select, or something similar. I have tried all the combinations and quotes I can think of but no luck. Is there a correct format to use something other than range("C2").select to select that cell? TIA Bill Roberts -- Bill Roberts

Search and find test

I want to find all cells that contain whatever text I am searching for. Ctrl+F will do this and display everything it finds in a separate window. What I would like to do is have all of the cells and contents in the row displayed that the search finds. ..

Sub Totals as a calculated field in a pivot table

I have a pivot table that pulls revenues product line and displays in monthly columns. Can I create a subtotal that displays quarterly data as a calculated field? In another pivot table I have revenues and costs associated with a variety of projects. Is there a way to subtotal the costs separately from revenues? Thanks for reading.

Transfer of list of dates and events to a calendar format

I want way to transfer data from a list of dates in column A and events in column B. Given a monthly calendar such as commonly used, I would like to transfer the text of events in column B to the appropriate box in the calendar. Is there an easy way to do that? One way that would do the job is to make monthly calendar pages by using Excel resources. That should be platform independent. Om my case, I use a Macintosh that already has a calendar application called iCal available. I have no idea, however, on how to carry out that task simply. Please suggest a simnple way of do