About Experts Sitemap - Group 42 - Page 14
Excel: Excel blinking cell date expired, conditional formatting, using microsoft office
conditional formatting, using microsoft office, office excel: Flashing can only be done by an always-running macro, which severely limits what you can do. And then there s got to be a way to stop it from flashing. I d recommend conditional formatting instead. Flashing very quickly gets annoying, even if it looks cool...
Excel: Excel blinking cells, colorindex, cell interior
colorindex, cell interior, sheet tab: Change the worksheet calculate to something like this - I ve listed a random-ish set of cells to test & to blink: Private Sub Worksheet_Calculate() Dim TestCell As String, BlinkCell As String For i = 1 To 6 TestCell = Split( B10/D10/E3/E5/C2/D14...
Excel: Excel button VBa code, basicly, yahoo
basicly, yahoo: Need to know a few things. Are the new sheets named? That is, do you know if January s sheet is named January? Or is the last(right-most) sheet the last month s sheet which needs to be copied? What do you mean by the one column ? And where is the ending...
Excel: Excel buttons, input boxes, bottom pane
input boxes, bottom pane, screen moves: You may try with the menu Windows- Freeze panels. This will freeze a part of the screen, so you can keep your buttons on the top or left part, as it suits you best. Other option is on the menu Window- New window. This will create another window over the...
Excel: Excel cell forat
1:15:00 as a decimal you want would be 1.25, not 1.15. You can format the cells as H:MM to display as 1:15 instead of 1:15:00 AM, and you can also simply add these cells using a format of [h]:mm for the total (in case it s over 24 hours). But if you insist...
Excel: Excel cell format, cell reference, digit numbers
cell reference, digit numbers, separator: I have a slight problem, in that I m in the UK so I m not familiar with ZIP codes - however, taking your explanation and working with that you should be able to do it via a blank column (which you can then copy and paste special - values over the original...
Excel: Excel cell shading, conditional formatting in excel, ribbon
conditional formatting in excel, ribbon, cells: Clarkey: This can be done with Conditional Formatting. In Excel 2007 this is found on the Home Ribbon under Styles. In Excel XP or older you can find this under format- conditional formatting. What you want to do use a formula to set up your rule....
Excel: Excel changing cell colours, excel sheet, ogilvy
excel sheet, ogilvy, guru: Unprotect and protect it in your code: Activesheet.Unprotect Password:= ABC Range( G9 ).Select Selection.Locked = False With Selection.Interior .colorIndex = 0 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Activesheet.Protect...
Excel: Excel changing the tab names and file names, trial balance, thanks john
trial balance, thanks john, xls: John How do you send the files to your home? If it is in a link make sure you are attaching the files and not typing in the link into the document. The Group also indicates that Excel thinks it is networked to your pc. If the file isn t private...
Excel: Excel chart equation, horizontal cells, linest
horizontal cells, linest, range names: Meghan Well I have to say I don t work with trendlines but you may find the following to be helpful. You might also ask another expert who has experience with this function, try either bob ulmas or aidan heritage In general, here is one way... Select...
Excel: Excel chart, tab place, bar graph
tab place, bar graph, excel charts: Simon: You may color each bar individually in Excel by following one of these options: Option #1) Right-click on a bar and choose format data series . On the patterns tab you can change the bar color for all bars in the series. Option #2) The above...
Excel: Excel charting, john peltier, google search
john peltier, google search, edward tufte: http://www.strategiccomm.com/usecharts.html There is one person who I have heard several times and the Guru of using charts effectively. Unfortunately, I don t know his name. I think it is this guy: I believe Edward Tufte has several books on Charts...
Excel: Excel charts, data columns, excel charts
data columns, excel charts, sheet1: I m not familiar with the functions SERIES or SOLID, so I m a bit stumped (especially as I cannot find them referenced in my help file!) There almost certainly is a way round the problem, but I m not sure what it would be. If you want to mail me a file to...
Excel: Excel charts, sheet1, m33
sheet1, m33, source data: As long as the data range remains constant, it s no problem - simply select the two blocks of data (select the first, then hold down shift and select the second) and build the chart based on this - the source data should end up as =Sheet1!$C$32:$M$33,Sheet1!$Q$32:$AA$33...
Excel: Excel circular formulas, circular reference, circular references
circular reference, circular references, formula calculations: Hi Nancy, This message is Excel s way of warning you that you have a circular reference . A circular reference means that you have a formula that depends on the value that the formula itself yields. The simplest example of a circular reference would be...
Excel: Excel code, excel code, excel spreadsheet
excel code, excel spreadsheet, quotes: The answer to both questions would by: YES, you positively CAN .. Implementing it however might take up some time .. You d be asking too much of me to provide a solution - you re asking for a client-tailored solution...
Excel: Excel colum search., autofilter, column c
autofilter, column c, colum: in C2 put in the formula =if(countif(A:A,B2) 0, Copy , Leave ) then drag fill down the column. Select column C and do Data= Filter= Autofilter and in the resulting dropdown, select Copy . Select column B again and do Edit= copy, go to the new location...
Excel: Excel column and row counting, seperate sheet, vlookup
seperate sheet, vlookup, sheet1: I m not sure I ve got enough information to provide an answer, BUT are you talking about (for instance) having particular headings in row 1, but these headings could be in any order? If so, MATCH possibly used with INDEX would give you the information you...
Excel: Excel column matching, exact layout, team mates
exact layout, team mates, web environment: Sadie for #2 you will need to upgrade to Office 2007 and Windows live which will enable you to do what you want. That is the only way I know of having live changes through multiple users. for #1 I would need to know the exact layout, is it possible...
Excel: Excel combinations, brainbench, increments
brainbench, increments, combinations: Drew, You are right in noting that 1296 is 36x36. The right character is divided by 36^0 so that it increments on every row. The middle character is divided by 36^1 so that it increments every 36 rows and the left character is divided by 36^2 so that it...
Excel: Excel combo Box, excel combo box, data validation
excel combo box, data validation, dropdown menu: Hi venkatesan, I m not quite sure what you mean when you refer to a combo box (is this the same thing as a dropdown menu). My suggestions would be to format the cell where the combo box is, or to format the validation list. If I am way off on the definition...
Excel: Excel command line options, command line options, input cell
command line options, input cell, human interaction: Not exactly. You can use the command line, see link below, to open a workbook and then have Workbook_Open and Workbook_BeforeClose events in that workbook that will then run macros to do what you need done. This should be very close to what you want to do....
Excel: Excel comments, pivot tables, menu tools
pivot tables, menu tools, comments section: I am not able to replicate the error, and I don t know what may cause it, but in fact I rarely show the comments in the spreadsheet. You may try to hide the comments and show only the indicator (from menu Tools- Options- View Tab- Comments section- Comment...
Excel: Excel computing, stupid thanks, circular references
stupid thanks, circular references, number combination: Hector, The question seems simple but the answer is much more complicated than I expected at first but here goes. 1. The first thing to realize here is that you need to use circular references as you want to dynamically change the numbers till the average...
Excel: Excel computing, stupid thanks, infinite number
stupid thanks, infinite number, hector: Hector, There are infinite number of combinations with a given average. The key is to create a boundary on the data set that you want to create. Typical things to consider 1. number of variables you need 2. any distribution that the output should satisfy...
Excel: Excel conditional formating, conditional formatting, formating
conditional formatting, formating, b1: Hi benil How are you? Please do the following 1. select A1:A10 2. Format / Conditional formatting 3. Select Formula is on the leftmost text box 4. Type A1 B1 on the right text box 5. Press Format and choose the format you want 6. Ok Cheers Adelaid...
Excel: Excel conditional formatting, blank cells, t zero
blank cells, t zero, dmax: Yes, and I m sorry I didn t read your question properly yesterday - actually, having come up with this alternative, I prefer it!! =small(range,countif(range,0)+1) will return the value of the smallest number in the range that isn t zero - which can be...
Excel: Excel conditional formatting, blank cells, t zero
blank cells, t zero, dmax: If you look at the conditional formatting on the original, you will note there are TWO equals signs, as you are comparing two statements - so =small($c$5:$h$10,countif($c$5:$h$10,0)+1)=c5 would compare C5 agains the smallest value not equal to zero,...
Excel: Excel conditional formatting, excel conditional formatting, b1
excel conditional formatting, b1, many thanks: Paul: This can be done with conditional formatting. 1) Select column B. 2) Click on format- conditional formatting. 3) Use a formula 4) Enter this: =B1 A1 (be sure NOT to put $ s on these numbers) 5) Change the format to make the text or background...
Excel: Excel conditional formatting, excel conditional formatting, blank cells
excel conditional formatting, blank cells, dmax: I think you would need to use the DMIN worksheet function, although the D.. functions are NOT my favourite functions! I ve uploaded an example file for you at http://www.aidanheritage.byethost3.com/excel/Dmax_and_Dmin_With_Validation.xls As to the blank...
Excel: Excel conditional formatting, many thanks, b1
many thanks, b1, spreadsheet: Paul: 1) Edit the conditional formatting in the one working cell and ensure there are no $ s surrounding the formula. 2) Assuming we have a formula clear of $ s, select the one working cell and choose copy . 3) Select all the cells you want to apply...
Excel: Excel consolidation of 26 worksheets, correct formulas, c7
correct formulas, c7, attaching files: Lisa Tha answwer is easier than you might think. Yes when you copy the template formula over to another workbook it will reference the origninal workbook. To eliminate this turn the formula in the template workbook into text by deleting the = sign in...
Excel: Excel consolidation of 26 worksheets, correct formulas, c7
correct formulas, c7, attaching files: It s much easier than you think - you probably know that you can do =sum(c1:c7) to add all cells in c1:c7 on the current worksheet - but you probably DIDN T know that you can do =SUM( Pay Period 1:Pay Period 21 !C7) to add ALL the C7s on all the sheets...
Excel: Excel consolidation, swbell, spreadsheet
swbell, spreadsheet, len: Brian: I m not sure if I have enough information to answer this question. You say you have a formula showing the workbook name...I suspect that it is the CELL() function....like this: =CELL( filename ) It will show the workbook name AND sheet...
Excel: Excel contracts, allexperts, specifics
allexperts, specifics, subject line: Greg: OK. I will call you around lunch. I am in Alabama and in CST so expect a call betweeen 11:00 and 1:00 CST if that is convenoent with you. Please send the file to billedd@aol.com and reference ALLExperts somewhere in the subject line to make sure...
Excel: Excel convert nth cell in a column to row., cell b1, consecutive values
cell b1, consecutive values, excel formulas: You can use the OFFSET formula for that, for example: =OFFSET($A$1,(COLUMN()-1)*3,0) Entering this formula in the cell B1 will start bring every third value on the column A, starting with the value on A4. You can adjust the way that you calculate the offset...
Excel: Excel convert nth cell in a column to row., cell b1, consecutive values
cell b1, consecutive values, excel formulas: Sorry for the late, I tried to come up with a single formula that you could use on the other sheet range, without having to adjust it. This should work, if both the data and the reference will start on cell A1: =OFFSET(Sheet1!$A$1,(COLUMN()-1)*3+INT((ROW()-1)/2),MOD(ROW()+1,2))...
Excel: Excel copy value from one cell to another, column c, menu text
column c, menu text, seperator: I would do this in a couple of steps - in that, the comma is a seperator that can be used with the data menu, text to columns - this would turn the one column of data into two - BUT it would put it in D and E in this instance - SO my method would be to COPY...
Excel: Excel copy1 paste10, cell b10, simple solution
cell b10, simple solution, a1: Not sure I understand. Why not have this in B: =A1 =A1 =A1 (ten times all the way to cell B10) =A2 =A2 =A2 (ten times, you re now in cell B20 =A3 (ten times down to cell B30) Now select the 30 sells and copy them down. They *should* repeat, tho...
Excel: Excel coumn header changed, douglas m smith, letter column
douglas m smith, letter column, brainbench: Hi Les, That s a tough one to find if you don t know about it. Select Options from the Tools menu and click on the General tab. Uncheck the R1C1 reference style option and click OK. The numbers will change back to letters. Douglas M. Smith ...
Excel: Excel countif function, excel countif function, b1
excel countif function, b1, databases: Yes, the COUNTIF can be used for this. For a basic Yes/No flag, you can use something like: =IF(COUNTIF(A:A,B1), Yes , No ) If you just want the number of times the number appear, you don t need the IF part: =COUNTIF(A:A,B1) If you use the conditional,...
Excel: Excel counting formula, maximum number, b1
maximum number, b1: you can if you have an additional column that you can use assume that you have. let a1:a10 have the numbers column b is the temporary column B1 has the formula =IF(ISNA(VLOOKUP(A1,$A2:$A$1000,1,FALSE)),1,0) Important fact - in the formula $A2:$A$1000...
Excel: Excel currency formating, format dialog, format cells
format dialog, format cells, united states currency: the number format is $#,##0.00_);[Red]($#,##0.00) Manually, you would select the cell(s) and do format= Cells= Number tab, then choose custom and paste in the above (highlight the above and do ctrl+C from the email, go back to excel then ctrl+v...
Excel: Excel data manipulation, gilman drive, data manipulation
gilman drive, data manipulation, excel spreadsheet: feel free to send a sample workbook to me at bobumlas@yahoo.com - use subject of AllExpertsQ and repeat what you want to do. However, looking at the small sample of what you sent, there s not very much Excel can to to straighten this up since it s hard...
Excel: Excel data presentation, pivot table, pivot tables
pivot table, pivot tables, blank sheet: Mick, There are many possibilities for this. Your question is somewhat vague, as you don t say what kind of report you are trying to create. Is this sales figures for a region, per salesman, or analysis of a scientific device s test data? But...
Excel: Excel data validation and copy/paste, excel data validation, validation rules
excel data validation, validation rules, security requirement: Nope, this does not work as I had thought it MIGHT I doubt that you really need a range name anyhow to do this. Try using ONLY this, untested: Private Sub Worksheet_Change(ByVal Target As Range) Does the cell have validation? If HasValidation(Target)...
Excel: Excel data validation, excel data validation, blank entry
excel data validation, blank entry, dollar signs: Hi Diane, You would select cells B10:B25 and while holding the Ctrl key also select B200:B400. Then go to the Excel menu and select Data then Validation... then select Custom from the drop down list and then input the formula. =SUM($B$10:$B$25,$B$200:$B$400)...
Excel: Excel database, excel spread sheet
excel spread sheet: The short response is yes, you can, but I would need more information to give you detailed code. For example, you mention that you could get it to work, could you post the code that you are using? Also, you mention something about the order, can you be more...
Excel: Excel database, excel, save
excel, save: Sorry for the late, I was complicating it more that it should. You can try with a code like this: Sub SaveNew() ThisWorkbook.SaveAs ThisWorkbook.Path & \ & Format(Left(ThisWorkbook.Name, 3) + 1, 000 ) & .xls End Sub This is assuming that...
Excel: Excel database, excel, save
excel, save: You shouldn t have issues with the path, as the macro takes it from the current workbook using ThisWorkbook.Path. The name of the workbook should be XXX.xls, where XXX is a number between 000 and 999. In order to run the macro, open the Visual Basic Editor...
Excel: Excel date formula, cell a1, cell b1
cell a1, cell b1, benefit eligibility: Kim If you put the hire date in cell A1 and in cell B1 you put the formula +A1+30, the result in cell B1 will be a date that is 30 days from the hire date. For example if cell A1 is 2/15/07 the result in cell B1 will be 3/17/07. Hope this helps Richar...
Excel: Excel date list, excel tips tricks, 599cd
excel tips tricks, 599cd, allexperts: I show you how to do this in my Excel 101 tutorial... using the AutoFill Handle. Basically, you have to create TWO dates (say in A1 and A2) so that Excel can see your pattern. Then, SELECT them both. NOW grab the AutoFill Handle (that little button in the...
Excel: Excel dates in cells, input box, key dates
input box, key dates, cells: Hi Daniel, Select the cell that you are typing the date in. Go to the Excel menu and select Format then Cells... , select the Number tab, select Custom from the list. In the input box right underneath where it reads Type: type in the following...
Excel: Excel dates???, cell a1, cheers
cell a1, cheers: MICHAEL The formula =EOMONTH(A1,0) will convert the date in cell A1 to the end of the month date. So if cell A1 has 1/12/08 in it, the formula will yield 1/31/08. Of course the cell with the formula in it has to be formatted in the date format you want....
Excel: Excel day/month/year disable, construction bids, mouse button
construction bids, mouse button, lumber: Hi Loren, I also do construction bids with lumber everyday and know exactly what you are talking about. If you select the cell where you are typing in 5/20 and go to the Excel menu and select Format then Cells... then select the Number tab and...
Excel: Excel drop down and altering content displayed, data validation, drop down boxes
data validation, drop down boxes, excel drop down box: You can create in-cell drop-down boxes in a cell by selecting the cell, then selecting from Main Menu: Data, Validation, Settings, Allow: List, Source: [list of items in the drop-down] You can make other cells depending on the drop-down choices by using...
Excel: Excel drop down list, excel drop down list, drop down list in excel
excel drop down list, drop down list in excel, poor users: Bob, I don t know of any other way than the two you mentioned. So what is so wrong with those? However, if what you are trying to do is restrict entries in an input cell to integers from 0 to 10,000, there is a much easier way than making your...
Excel: Excel error, xls file, open outlook
xls file, open outlook, aidan: I m slightly puzzled by the reference to outlook, but still! BASICALLY there is a file attached (I suspect) to your personal.xls file which is called module 5 which has problems associated with it - when excel opens, press Alt F11 to get to the VBA editor,...
Excel: Excel expressing numbers, cell a1, correct answer
cell a1, correct answer, two letters: IF the patterns are ALWAYS as described, then it s easier than the suggested answers so far - the LENGTH of the value changes each time - one pattern (the correct one) is 7 characters long, the other only 6 so =if(len(a1)=7,a1,left(a1,1)& 0 &right(a1,6))...
Excel: Excel extract records, blank cells, header row
blank cells, header row, sort data: Maureen, There are several ways to achieve it. I am attaching some methods from the most trivial (but not so elegant) to more elegant methods. 1. Sort data based on the column where NULL values and then Copy the rows 2. Use Data - Filter - Autofilter...
Excel: Excel file extensions & a formula ?, cell a1, xls files
cell a1, xls files, program basis: I always work with .xls files; don t know anything about .xlms. Example for working with time. Say that cell A1 is the time in, A2 is out for lunch, A3 is back from lunch, A4 is time out. You may format all of them as time with the format style like ...
Excel: Excel filter, worksheet menu bar, custom filter
worksheet menu bar, custom filter, c application: No software will prevent them from doing a screen shot and pasting the result to somewhere (word,...) to print it. Also, they can email it as a file attachment, so I don t think you can prevent this, either. You can prevent cut & paste or printing from within...
Excel: Excel filter, worksheet menu bar, custom filter
worksheet menu bar, custom filter, c application: OK, this will handle the 2 you mentioned as well: Private Sub Workbook_Open() Application.OnKey ^+C , Application.OnKey ^c , Application.OnKey ^+X , Application.OnKey ^x , With Application.CommandBars( Worksheet Menu...
Excel: Excel filters, helper
helper: Because numbers are numbers and text is text (to Excel at any rate). You CAN filter on numbers with a is greater than x and is less than y, and IF you need to find all the numbers beginning with (say) 2, then you could use a helper column to format the...
Excel: Excel find (?) or other, e mail address, serial number search
e mail address, serial number search, des o connor: Hi Janet You sound as if you are pulling your hair out. I will try and help if I can but I m not going to promise a solution as I haven t seen your data or files. Id say i would need to see all the files by e-mail to sort this out for you. The first...
Excel: Excel find, conditional formatting, hypen
conditional formatting, hypen, negative values: I put in a negative sign/hypen in as the item to search for. - I also unselected find entire cells only and selected to look in formulas I was looking for negative constants. It worked for -5 and (1.00) (since the underlying constant had a negative...
Excel: Excel find, data validation, input cell
data validation, input cell, close match: Misca, I don t know of a way to create an autocomplete sort of function which will make suggestions while you are typing. All the excel formulas require the input cell to have been ENTERED (finished typing) before they work. The problem with...
Excel: Excel flow through sheet, cell a2, calcs
cell a2, calcs, raw data: I believe the sample I provided in the first response will not have any issues with transferring over the results of formulas. As far as the spreadsheet I designed is concerned, a static value and a formula will look the same when building the individual...
Excel: Excel flow through sheet, cell a2, raw data
cell a2, raw data, data sheet: Don: I have read your question a few times, but I m not certain I understand. Nevertheless, I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/rawdataentry.xls It has two sheets; one is called Raw Data and the other...
Excel: Excel flow through sheet, cemex, blank rows
cemex, blank rows, raw data: Don: The formula I provided uses the actual row number to determine what row to pull information from on the Raw Data tab. Here is the original formula: =IF(COUNTIF( Raw Data !$B$2:$B$1000,$A$1) ROW()-1, ,INDEX( Raw Data !$A$2:$D$1000,SMALL(IF( Raw...
Excel: Excel flow through sheet, cemex, raw data
cemex, raw data, data sheet: Note #1 - It will go down to infinity From row 1 to row ?????? In terms of Rows, Excel has a finite limit of 65,536 rows...just an FYI in case that is going to cause problems :). Note #2 - What you are trying to do is definitely possible, but is certainly...
Excel: Excel flow through sheet, cemex, raw data
cemex, raw data, data sheet: To enter or change an existing array formula, you will want to press CTRL-SHIFT-ENTER instead of simply ENTER. You will know the formula has been entered correctly if it shows braces, {}, around the formula. To copy an array formula you can just use copy...
Excel: Excel font colour, instrument calibration, format button
instrument calibration, format button, button font: Select all the cells which may be current or Due . Use Format/Conditional Formatting, change between to equal to , enter due (no quotes), click Format button, font tab, select red font; Click OK, clicl Add button, change between to equal to, enter...
Excel: Excel for Invoicing, invoice format, consecutive rows
invoice format, consecutive rows, quantity times: There are three options as I see it - one is to use VBA to loop through the records writing the results to the invoice if required. Second is to live with what you have, but use data, filter autofilter to filter out the blanks before printing/using. The...
Excel: Excel for Macintosh, windows explorer, macintosh
windows explorer, macintosh, apple: Hi Larry, I am sorry I have never used a Macintosh. On a PC I do not even use Excel to delete a file. I use windows explorer,navigate to the file location and select file and press Delete button or right click with mouse and select Delete . I did...
Excel: Excel for inventory use, excel user, countif
excel user, countif, tire shop: I would probably have an invoices tab on the sheet and use a SUMIF function (or countif) to sum/count as appropriate the number of matching entries on the invoices tab My email is aidan.heritage@virgin.net - feel free to use this for any follow up questions...
Excel: Excel formala, basic logic, excel sheet
basic logic, excel sheet, final result: I would use the OR, AND and IF functions I think - if(or(isblank(firstStep),isblank(SecondStep)), ,if(and(firststep,secondStep), Pass , Fail )) is the basic logic as I see it - I ve only included two steps, but hopefully this should make the process...
Excel: Excel format and formula, douglas m smith, fortune 500 company
douglas m smith, fortune 500 company, array formula: Hi Sandy, Yes this is possible with an array formula, something like: =SUM(ROUND(B3:B13,0)) Use Control+Shift+Enter instead of Enter. If you do this correctly, {} brackets will appear around the formula. It will then round each number in the cell...
Excel: Excel formatting, 4 digits, len
4 digits, len, formatting: The easiest way to do this would be to use Find and Replace. Select the Column of numbers, press Alt F to show the Find Dialog. Then first type ( in the Find Box, then click Replace, leave this box empty then click Replace all. Repeat to remove the other...
Excel: Excel formatting, visual basic programming, digit number
visual basic programming, digit number, decimal places: Hi Celine, You are correct to try to format the cell. A current date is stored in Excel as a 5 digit number. So it does appear to be a formatting issue. But to not be able to change the formatting is a problem. If you did not originate this template it...
Excel: Excel formula
The main part of the formula is this part (it is used twice with slight modifications) MATCH(LEFT(A17,5)& 01 &MID(A17,7,2),TRIM($A$2:$A$12)&TRIM($B$2:$B$12)&TRIM($C$2:$C$12),0),1)& - & LEFT(A17,5)& 01 &MID(A17,7,2), builds a search time like Item10101...
Excel: Excel formula - sumif, rita rita, excel formula
rita rita, excel formula, ogilvy: Rita, to sum everything corresponding to a date of 7 days hence, you would just use the first part of the original formula: QUESTION: I need help with Sumif formula. Let s day I have a list of date in column A and amount in column B. I want a formula...
Excel: Excel formula / vb script needed, vlookup, vb script
vlookup, vb script, excel formula: Hi, I have not understood the question.... (1) You have two columns (2) Column 1 has entries as either Completed or Uncompleted (3) Column 2 has dates (Date Sent) (4) Are you looking for first date (Date Sent) from row 1 downwards which has uncomepleted...
Excel: Excel formula converting with dec2bin, excel tips tricks, excel formula
excel tips tricks, excel formula, 599cd: Looks like you ve reached the limit of the DEC2BIN function. See: http://office.microsoft.com/en-us/excel/HP052090531033.aspx Perhaps split your number up into two cells? Let me know if you have any other questions or comments. Also, be sure...
Excel: Excel formula does not update in spreadsheet., array formulas, excel function
array formulas, excel function, excel formula: Mike: You are using arrays in your formula. Try confirming the formula with CTRL-SHIFT-ENTER instead of just ENTER when you put the formula in the cell. Let me know if that doesn t work and I ll examine the issue further (you might need to email your...
Excel: Excel formula entry, excel formula, click tools
excel formula, click tools, home computer: OK, that s good to know, I guess. But, what is your question? I am not sure what you want to know but check two things: 1. Click Tools, Options, View. UNCHECK Formulas 2. Check to see if the cell where the formula is has been formatted to TEXT. If...
Excel: Excel formula help, cell a1, excel formula
cell a1, excel formula, datevalue: Example: select cell A1. From main menu, choose Format, Conditional Formatting, Cell Value Is, greater than or equal to, =DATEVALUE( 19 Sep 08 ), Format, Patterns, [red], OK, OK. Now, if you enter a date in A1 that s 19-Sep-08 or later, the cell background...
Excel: Excel formula help, conditional formatting, excel formula
conditional formatting, excel formula, format menu: I don t quite follow the question - do you want one cell to reference itself, or to reference another - if another, then use the format menu, conditional formatting to enter a formula testing the two dates. If the same, then it would need VBA - if you can...
Excel: Excel formula into a macro, excel formula, time sheet
excel formula, time sheet, b21: No. And the macro does not really record this for you like you need and you can not drag dwon this kind of formula logic and make it be like you need/want. One way is to write a loop, something like, for example: Dim Counter as Long For X = 1 to 200...
Excel: Excel formula limit, k 102, excel formula
k 102, excel formula, character limit: You have to break it up into smaller pieces. Perhaps you can NAME parts of the formula, then use the name inside the formula. For example you can give the name Priya to =VLOOKUP($A$1,$B$3:$K$102,10,FALSE) then use something like =IF(Priya= whatever ,. etc...
Excel: Excel formula needed to populate headcount worksheet, cell a1, excel formula
cell a1, excel formula, january february march: Sorry for the delay in replying - I had been away for the weekend and had forgotten to update AllExperts to reflect this. Use an IF statement combined with the COLUMN statement to give you a formula you can move around in C =if($A1 Column()-2, ,$B1)...
Excel: Excel formula not working except when I hit enter in formula bar, excel formula, lbs weight
excel formula, lbs weight, metric tons: Meredith It sounds like the Automatic Calculation may have turned off somehow. In Excel 2003 go to Tools Options Calculation and make sure Automatic is selected. In 2007 go to the Office Button then Excel Options Formulas and make sure Workbook...
Excel: Excel formula problem, cell a1, data validation
cell a1, data validation, formulas in excel: Hi Adam, The formula would be. =(B1-A1)*24*C1 Most Time formulas in Excel either need to be multiplied by 24 or divided by 24. To understand more about Time in Excel here is a good website for that. http://www.cpearson.com/excel/datetime.htm#AddingTimes...
Excel: Excel formula problems, array formula, formulas in excel
array formula, formulas in excel, excel formula: Example: you have dates in range A1:M1 (formatted like 12/1/2007, or another format that Excel recognizes as a date). In A2:M2, you have values corresponding to those dates. You want a formula that sums up the values in A2:M2, but only if the date above that...
Excel: Excel formula question, array formula, column c
array formula, column c, excel formula: The AVERAGE formula can do the work over the column C, something like: =AVERAGE(C1:C10) You can also use it as an array formula, so you will save the intermediate calculation. For example: =AVERAGE(B1:B10/ABS(A1:A10)) Enter it with Ctrl+Shift+Enter. ...
Excel: Excel formula question, cell references, excel formula
cell references, excel formula, exacly: Mike This tip should do it for you http://articles.techrepublic.com.com/5100-10877_11-6130286.html Another option is to use the $ to lock cell references when creating the formula. ie =if(A1=$B$3, will lock the B3 cell rerence so that if dragged...
Excel: Excel formula questions, cell c2, excel formula
cell c2, excel formula, b2: I m assuming that, for instance, you want a Y in cell C2 if A2 is the same as B2, and an N in C2 otherwise. If this is the case, then use in C2 the formula: =IF(A2=B2, Y , N ) and copy this formula down col C. [I see that in your comments, you ve...
Excel: Excel formula references to another worksheet, sum formula, cell references
sum formula, cell references, excel formula: adriana There is no reason for what is happening unless you have the cells locked but as you have stated that isn t the case. There may be an error in some of the cells or it is linked to an outside workbook but in either case it is unusual (in fact so...
Excel: Excel formula stock options, stock option grants, personal preference
stock option grants, personal preference, excel formula: Dean: I have uploaded a sample spreadsheet here: http://www.snapdrive.net/files/487599/vesting.xls I presume you want to count vesting as of exact quarter-end dates. For example, even if the options are granted 3/30/2007, there is 1 quarter vested...
Excel: Excel formula stock options, stock option grants, personal preference
stock option grants, personal preference, excel formula: Dean: I have uploaded another sample here: http://www.snapdrive.net/files/487599/vesting2.xls This spreadsheet only has one vesting date and one share price instead of the option to have different vesting dates and different share prices for each row....
Excel: Excel formula to duplicate from one sheet to another, cell c3, cell reference
cell c3, cell reference, excel formula: Tryclopss, I won t ask you WHY you want to do this because it seems very odd to me. Okay, I will ask: why create exact duplicates of your main sheet on all your other sheets? However, you seem to say that you only want certain cells to copy over...
Excel: Excel formula to duplicate from one sheet to another, cell c3, cell reference
cell c3, cell reference, excel formula: Sherley, Did you create this workbook? If you did, then you should be able to handle this better than I. The FORM you are using involves MACROS to enter the data. If you read my profile carefully, you will find that I do not have expertise in MACROS....
Excel: Excel formula to populate cells., personnel number, peeople
personnel number, peeople, excel formula: IF I understand what you have and what you need then no since the person you would be looking up would be in the list twice, once as an employee and once also as a line manager. No formula can determine which of the two names you would want to display info...
Excel: Excel formula to populate cells., vlookup formula, personnel number
vlookup formula, personnel number, cell d1: That s different from what you first described IF I still understand what you have. Just simply use a VLOOKUP formula to find the name in D. Use a separate one to return the info you need when that name is found. HELP has a good example of how to...
Excel: Excel formula, array formula, eric hope
array formula, eric hope, excel formula: Eric, Hope you are doing well. It can be solved using an array formula. The formula is =SUM(SMALL(IF(E5:E14=0,MAX(E5:E14)+1,E5:E14),{1,2,3,4,5})) However, after entering this formula, instead of clicking on Enter , you need to use the keys - Ctrl+Shift+Enter...
Excel: Excel formula, array formula, excel formula
array formula, excel formula, b17: On a single sheet, I put this data in A2:D12 Item1 01 01 A Item1? 01 02 B Item1 01 03 C Item1 02 01 U Item1 02 02 V Item1 03 01 W Item2 01 01 J Item2 01 02 K Item2 02 01 L Item2 02 02 M Item2 02 03 N in A17: Item1 0101- A18: Item2 0201-...
Excel: Excel formula, array formula, excel formula
array formula, excel formula, b17: The main part of the formula is this part (it is used twice with slight modifications) MATCH(LEFT(A17,5)& 01 &MID(A17,7,2),TRIM($A$2:$A$12)&TRIM($B$2:$B$12)&TRIM($C$2:$C$12),0),1)& - & LEFT(A17,5)& 01 &MID(A17,7,2), builds a search time like Item10101...
Excel: Excel formula, array formula, excel formula
array formula, excel formula, hh: Victor: There are a variety of reasons the above formula doesn t work (and none of them actually have to do with the concatenated field). There is a missing parenthesis, the formula attempts to index a value well outside the given range, and the use of...
Excel: Excel formula, array formula, game logic
array formula, game logic, eric hope: Hi Eric, You had omitted to mention in your previous emails that the numbers could be blank. Also, if you are looking at golf scores, not sure why you can remove pars value = 0. Anyway, without getting into the nitty gritty of the game logic, attached...
Excel: Excel formula, cell a1, excel formula
cell a1, excel formula: Hi Ryan, Thank you for your kind words. Thanks Ke...
Excel: Excel formula, cell a1, excel formula
cell a1, excel formula, two answers: Slight problem in that A1 could have FULL which isn t numeric, or a value, and you didn t say what you wanted if A1 showed full - so two answers (1) if FULL is to be taken as not greater than zero =if(or(a1= Full ,a1 =0), No ,a1) (2) if FULL is...
Excel: Excel formula, cell a1, excel formula
cell a1, excel formula, a5: Kirupairajah, cell A5 should contain a similar IF statement... I don t see why you weren t able to write it, if you can figure out the IF in cell A1. You ve stated exactly what you need in your own words: =IF(A1 0,+a1, No ) By the way, the...
Excel: Excel formula, cell b2, cell b3
cell b2, cell b3, excel formula: You can use INDIRECT for that, and craft the range using the ROW function. Something like: =AVERAGE(INDIRECT( Sheet1!A &(ROW()-1)*2-1 & :A &(ROW()-1)*2)) Enter this formula in cell B2, and fill it down. You may want to change the name of the worksheet...
Excel: Excel formula, cell reference, excel formula
cell reference, excel formula, john hammond: Several ways: One is to use SUMIF. See this article: http://www.officearticles.com/excel/sumif_using_a_cell_reference_as_criteria_in_microsoft_excel.htm Something like: =sumif(A:A, University1 ) or =sumif(A:A,C2) (where C2 contains the name of University1)...
Excel: Excel formula, conditional formatting, countif
conditional formatting, countif, excel formula: A formula can affect the value of a cell, but not it s format - however, under the format menu you will find the conditional formatting option and this WILL allow to alter the format of cells - in this case by using a formula - the formula is a simple countif...
Excel: Excel formula, conditional formatting, countif
conditional formatting, countif, excel formula: Ideally, I d modify the format of these non numeric number cells to a custom one of 000-000-000 - that way, 222783435 (which is a number) would display as 222-783-435. Otherwise, you d have to modify the formula - which could be done - it s probably an OR...
Excel: Excel formula, conditional formatting, countif
conditional formatting, countif, excel formula: I think the only way to do this would be via VBA, and I m not totally sure of the best way to go about this - I guess it would need to be a macro you ran if you had a missing number, and it then re-ordered all the cells - I would probably be able to write...
Excel: Excel formula, custom option, excel formula
custom option, excel formula, select records: There isn t a formula that will delete rows, but you can use a countif formula to mark the records that would need deleting - for example, if dealing with data from A1 to A1000, put this in A1 =countif(a1:$A$1000,a1) and drag down. In your example,...
Excel: Excel formula, d4 d5, transaction number
d4 d5, transaction number, excel formula: Michael, So what you want is: IF (type-of-transaction-cell = blank, return blank; if type-of-transaction-cell = not-blank, add 1 to previously populated transaction number). Hmmmm. Let s say the type-of-transaction is column C and...
Excel: Excel formula, douglas m smith, array formula
douglas m smith, array formula, isna: Hi Eric, Give this formula a try: =SUM(SMALL(IF(ISNA($E$5:$E$14),999999,IF($E$5:$E$14=0,999999,$E$5:$E$14)),{1,2,3,4,5})) But use Control-Shift-Enter instead of Enter to make it an array formula. ISNA traps the N/A errors and the other IF statement...
Excel: Excel formula, excel formula
excel formula: I m slightly concerned that 16.52 would go DOWN to 15.98, not up to 16.98 as 16.98 is nearer than 15.98 A few alternatives =round(Value-.02,0)-.02 This version will round to the nearer .98 value =rounddown(value,0)-.02 This version will round to...
Excel: Excel formula, excel formula, a28
excel formula, a28, washing machines: Jen: I don t understand the question, I don t think. But, suppose you had a price list of all the parts that go into a washing machine, and they are in cells A1 through A28. Now, you want to know what it will cost to make 3 washing machines? Put the 3 in...
Excel: Excel formula, excel formula, array
excel formula, array, yahoo: The 0*ROW(INDIRECT( 1:29 )) is totally unnecessary and always produces an array of {0;0;0;0;0;...0 0}. You should simply use =RANK(G2:G30,G2:G30). But I don t understand what you mean by ...show a listing of 1-29 based on the results of these 2 arrays. ...
Excel: Excel formula, excel formula, combinations
excel formula, combinations, boss: This formula works -- enter it in row 1 somewhere, fill down to row 1296: =IF(ROW() 937,CHAR(INT((ROW()-1)/36)+97),CHAR(INT((ROW()-1)/36)+22))&CHAR(IF(IF(MOD(ROW(),36)=0,36,MOD(ROW(),36))+96 122,IF(MOD(ROW(),36)=0,36,MOD(ROW(),36))+21,IF(MOD(ROW(),36)=0,36,MOD(ROW(),36))+96)...
Excel: Excel formula, excel formula, consolidated report
excel formula, consolidated report, excel sheet: I m not sure that I would have the day and date in seperate columns, as excel knows that April 17 2008 is a Thursday, and can be formatted to display that, but that s incidental. What I probably WOULD have is a column that contained the formula =CellWithDate-day(CellWithDate)+1...
Excel: Excel formula, excel formula, invoice number
excel formula, invoice number: If you have multiple columns, you may have multiple formulas, each one with an IF statement showing either the number if it is on the range, or blank if it is not. Something like: Column 1-15: =IF((TODAY()-A1) =15,A1, ) Column 16-30: =IF(AND((TODAY()-A1)...
Excel: Excel formula, excel formula, message saying that
excel formula, message saying that, ok button: Well, I don t know what else to say. If the formula works fine here and you are having a problem with it there then I have to conclude something is wrong there. You must have a syntax error if EXCEL behaves the way you are describing. When I copy and...
Excel: Excel formula, excel formula, occurrance
excel formula, occurrance, a1000: If the cell will only contain the word you are looking for then SUMIF will do it for you =sumif(range,test) so for example =sumif(a1:A1000, Jeff ) would count all instances of Jeff in A1 to A1000 If you are looking for all cells that contain...
Excel: Excel formula, excel formula, question thanks
excel formula, question thanks, minor error: Just changing the format of the cells will not convince Excel the content should be interpreted the same. If you want numbers that are now interpreted as text to be interpreted as numbers for example, you can use Data, Text to columns, finish to achieve that...
Excel: Excel formula, excel formula, text columns
excel formula, text columns, g1: There may be other ways to do this but one way might be to use the text columns feature and separate the word into separate separate columns, and then with a formula add the letters together. I.E. separate the word BAD into A1=B, B1=A, and C1=D ...
Excel: Excel formula, excel formula, time formats
excel formula, time formats, nature of time: If 2:47 is in A1, then this formula will make it seconds: =A1*1440 but you ll have to format that cell as general (ctrl/shift/~ is a shortcut to do that). You can have the cell display 2:47, but the formula bar must contain 2:47 AM (or PM) - it s the nature...
Excel: Excel formula, excel formula, vlookup
excel formula, vlookup, h5: Hi Kiruba How are you? A macro would do this better, but please try the following: 1. Type 1,2, 3 in A3:A5 2. Type Bad in B3 3. Type A,B,C,D in H2:H5 4. Type 2, 3,4,5 in I2:I5 6. Type in C3 =Mid($B$3;A3;1) 7. Type in D3 =Vlookup(C3;$H$2:$I$5;2)...
Excel: Excel formula, incorrect result, excel formula
incorrect result, excel formula, quandry: Try using the Conditional Sum Wizard (Conditional Sum is an addin). It will write the formula you need. If you have already tried this (and you did not say WHAT you had done yet that created the error) then do you need help with making the formula look for...
Excel: Excel formula, macro in excel, excel formula
macro in excel, excel formula, a10: Is it always the same five cells, i.e., A5:A10? Or does it change? If it s ALWAYS the same five cells, record a macro that takes the following steps: Edit-- Go to, type in A5:A10 or your range, and hit Enter. Hit Ctrl+C Hit Edit-- Paste Special,...
Excel: Excel formula, numeric field, alpha numeric
numeric field, alpha numeric, alpha values: This function will work the other way around, checking if the character is a number and keeping it on that case. I hope that this can fit in your case, if not please tell me. Function TrimAlpha(rngData As Range) Dim i, myResult For i = 1 To rngData.Characters.Count...
Excel: Excel formula, path workbook, rainfall data
path workbook, rainfall data, annual rainfall: Have both of your workbooks open in Excel at the same time, the target book 2006.htm and the source book monthtxtdata-1-06.htm In the target book, select the cell into which you want the formula to go. Type an = into that cell, but DON T hit...
Excel: Excel formula, rainfall data, annual rainfall
rainfall data, annual rainfall, book2: Have both 2006.htm and monthtxtdata-1-06.htm open in Excel. To reference cell B4 of monthtxtdata-1-06.htm from within 2006.htm, use this formula: = [monthtxtdata-1-06.htm]Sheet1 !$B$4 Note the apostrophes in this formula (which are missing when you tried...
Excel: Excel formula, reply times, reply time
reply times, reply time, cell a1: kirupairajah, I m glad I was able to answer your question and that you rated my answer, but I do not understand the rating of 7 for timeliness. I answered your question in under two hours, which is a very fast time for this free service. As proof, here...
Excel: Excel formula, sales targets, calculation purposes
sales targets, calculation purposes, excel formula: Philip, It is hard to say since you have provided so few details, but perhaps I can offer some insights. Here is a pseudo formula that should represent the process =Target+if(lastweek =0, positive actions , negative actions ) the if condition...
Excel: Excel formula, set target, target cell
set target, target cell, solver menu: For this scenario I would use the solver add-in. This is the proposed process (I will suppose that the data is in A1:A5, change as required): - Ensure that the solver add-in is activated (menu Tools- Add-Ins- Check the box for Solver Add-In) - Fill the...
Excel: Excel formula, sorry for the confusion, correct column
sorry for the confusion, correct column, excel formula: You can try with a modified version of the formulas given on the previous answer. The point is that you need to fill the columns with formulas in order to get the data; you cannot make a value or a formula appear on a cell by using other formulas in different...
Excel: Excel formula, sum formula, blank cell
sum formula, blank cell, excel formula: Bob You can certainly send me any follow ups or new questions. If you like you can use the (answer@pcexperthelp.comp) remove the p from comp, email address. If the cells are set up in the same format as the original sum then the copy should reference...
Excel: Excel formula, target address, excel formula
target address, excel formula, explorer 3: Hi Kim How are you? Please copy to the code module of Sheet1 the following Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range Set R = Range( C4 ) If Target.Address = R.Address Then R.Cells(1, 1).EntireColumn.Insert R.Cells(1,...
Excel: Excel formula, validation field, jacket potato
validation field, jacket potato, excel formula: Aji, Easily done using two MATCH() statements and an INDEX(). Read about these functions and how to use them. Set one MATCH() to return the row number based on the word (KINGS in your example)... this column is not named in your image so...
Excel: Excel formula, vlookup, excel formula
vlookup, excel formula, microsoft excel: Problems: First of all, 5.0% is not a value but a calculation. But .05 *is* 5 percent and it s also a value. So try using: .05 instead of 5.0% Same with all the rest. Also, we *could* use an if statement, but I think you ll be better off...
Excel: Excel formula, vlookup, suitable function
vlookup, suitable function, cell reference: The first part is a vlookup combined with an if statement =if(iserror(vlookup(A_Value,AThroughGList,7,false)), Unlisted ,vlookup(A_Value,AThroughGList,7,false)) is your formula A_value is the cell reference in A AThroughGList is the second sheet reference...
Excel: Excel formula, youth sports association, answer mark
youth sports association, answer mark, excel formula: If you have this working formula and it is against the data you want to process then: I suggest your sumproduct is of the form (I will use two conditions to illustrate, but it can be 2, 3, 4 or more conditions) =sumproduct(--($A$1:A1=A1),--($B$1:B1=B1))...
Excel: Excel formula, youth sports association, boise idaho
youth sports association, boise idaho, excel formula: Mark: Check out this spreadsheet: http://home.swbell.net/nate-sus/excel/findduplicates.xls I believe this will accomplish what you are looking for. Summing it up - I just do a separate count of the number of times a particular name appears in each...
Excel: Excel formula..., excel formula, headway
excel formula, headway, caveat: You can do it with a formula in col F, but you need some defined names first. Assuming your first row of data is row 2 (row 1 has headings), select F2 (important step), and define rg1 to be =MATCH(Sheet1!$A2,Sheet1!$A:$A,0) define rg2 to be =MATCH(Sheet1!$A2,Sheet1!$A:$A,1)...
Excel: Excel formula: change text to value, excel formula, vtr
excel formula, vtr, a3: IF we are only talking about the one value, then an if statement will do it =if(a1= TRSP ,1,0) BUT if you have multiple values, then a lookup table would be needed. And not SURE if you would have an entry such as TRSP-2 which should also evaluate to...
Excel: Excel formulas, array formula, excel formulas
array formula, excel formulas, a5: One way of doing it is with an array formula similar to this: =SUM(INT(A1:A5))+INT(SUM(MOD(A1:A5,1))/0.6)+MOD(SUM(MOD(A1:A5,1)),0.6) Enter it Ctrl+Shift+Enter. You can change the range A1:A5 to the range with your data. The formula will return the data...
Excel: Excel formulas, hr clock, column input
hr clock, column input, time column: Jane, In the example, shouldn t the overtime be 5 hours (8:30-9:00 and 18:00 - 22:30) not 5.5 hours as you mentioned. Anyway, heres the solution. assumptions 1. Col A - DAY 2. Col B - Start Time 3. Col C - Break 4. Col D - End Time 5. Col E - standard...
Excel: Excel from the command line, macro in excel, excel macro
macro in excel, excel macro, google: 1. That depends on WHAT program you are referring to. You can have an auto-executing_ macro in EXCEL that will run itself when you open that EXCEL file. So, if the program in question can launch EXCEL then yes. 2. GOOGLE for Workbook_Open event and...
Excel: Excel from the command line, myfolder, file excel
myfolder, file excel, excel exe: Mihai, the is a workbook_open event that will fire when a workbook is opened. You would need to put code in there to perform the actions you require. http://www.cpearson.com/excel/Events.aspx Describes what events are available to you. So you...
Excel: Excel function for Covertion of numbers to words text in Indian system, excel, text
excel, text: The formula is called TEXT, and you can use all the available formats for the cell with it, including custom formats. Check the help of the function for the syntax and samples, if you cannot find there your specific need please let me know. Hope this helps,...
Excel: Excel function help, excel function, a10
excel function, a10, replacement text: Assuming you mean you are writing your own custom function - I could not really tell based on the way you phrased your post - then when your function identifies the cell where the data being looked for is found then use the .Offset property. For example,...
Excel: Excel function help, excel function, brainbench
excel function, brainbench, search text: Lena, This can be done using a formula. In a cell in the adjacent column (where you want the replaced text) type the following: =IF(NOT(ISERR(SEARCH( text ,A1))),LEFT(A1,SEARCH( text ,A1)-1)& Found &MID(A1,SEARCH( text ,A1)+LEN( text ),256),A1) Change...
Excel: Excel function that applicable to all open workbooks
Sorry - I answered quickly because I was answering while attending a meeting! Create your function macro (it IS a macro) in its own workbook, and when you save it, save it as an xla (add-in). You do this by the save as type in the save as dialog. When you...
Excel: Excel function to calculate individual values that make up the total value, excel function, quickbooks
excel function, quickbooks, total value: I have seen this question several times, but there is not a simple way. You could probably use Excel s Solver Feature. There s a VBA solution suggested here: http://www.ozgrid.com/forum/showthread.php?t=64253&page=2&highlight=solver And some help...
Excel: Excel function, excel function, b1
excel function, b1, b2: Hi Excel Beginner, I think you could create a second column that divides by 100 with rounding and then multiplies by 100 again. Then turn that column into constant by copying and pasting/special/values. A1 is 140 B1 is =ROUND(A1/100,0)*100 A2 is 190...
Excel: Excel function, excel function, module menu
excel function, module menu, editor menu: In this case, I think that the most convenient solution is a user defined formula, something like: Function IsFormula(rngCell As Range) As Boolean IsFormula = rngCell.HasFormula End Function To use it, open the Visual Basic Editor (menu Tools-...
Excel: Excel function, excel function, tool pack
excel function, tool pack, office microsoft: Lee You need to use the Mround function. You can access this function by installing the Analysis tool pack if it isn t available to you. your formula will look like =MROUND(A1,100) where A1 is the cell you want to round Instructions for toolpack loading...
Excel: Excel functions, odd rows, a25
odd rows, a25, excel functions: Kathy: Presuming your data is located in cells A1:A25, you can use this formula for the odd rows: =SUMPRODUCT((MOD(ROW(A1:A25),2)=1)*(A1:A25)) This formula is for the even rows: =SUMPRODUCT((MOD(ROW(A1:A25),2)=0)*(A1:A25)) This formula works by...
Excel: Excel gradebook question, excel gradebook, empty cells
excel gradebook, empty cells, excel 2000: If you use the AVERAGE worksheet function to calculate the average percentage of each student you should be OK, as long as you enter no numbers into the cell when a test has been missed, but rather nothing or some text. Empty cells and cells with text are...
Excel: Excel graphs, empty cells, vlookup formula
empty cells, vlookup formula, definitive answer: Joann, Hard to say without being able to see your data and your chart. However, I would start by selecting the chart and going to tools= Option, then the chart tab. There are some options there at the top for configuring how your charts treat various data...
Excel: Excel graphs, vlookup formula, good answer
vlookup formula, good answer, spreadsheet: Jo Sorry I m definatly not an expert in graphs--I seem to have as many problems as most people do, and I m guessing its because I really don t use them much. In any event--I suggest that you reask the quesitons of another and I m sure you can get a good...
Excel: Excel help with VB/Macros, relative coordinates, cell location
relative coordinates, cell location, arrow keys: Hi Brian, There are some options that will make your programming a little easier. If you use the check box from the Control Toolbox menu, that will be the most difficult to read and control. The LinkedCell property allows you send True or False to a specific...
Excel: Excel help, a18, cheers
a18, cheers, getpivotdata: Hi Rajesh How are you? If you want A16 to become A17, A18, A19, just replace $A$16 with A16 the $ sign applied to A and 16 makes A16 to be always A16 ... it does not change. But if you write just A16 it changes to A17, A18, ... when you copy the formula...
Excel: Excel help, b2000, error values
b2000, error values, a17: 1) Remove the $ s to make the references relative. That is: when a formula with $A$16 gets copied down, it remains $A$16, but A16 will change to A17. 2) If you have e.g. values in B1:B2000, and want the sum counting values alone, but ignoring error values,...
Excel: Excel help, blank copy, tutorials section
blank copy, tutorials section, spreadsheets: I m not sure exactly what has happened. have you added any Protection to the sheet? Does this only happen with this workbook? However, why don t you save a blank copy of this workbook as a Template (.xlt), then you can easily create new ones -read the article...
Excel: Excel help, col1, john x
col1, john x, jim c: Sub aBC() Dim rw As Long, col As Long Dim i As Long, col1 As Long rw = Cells(Rows.Count, 1).End(xlUp).Row For i = rw To 2 Step -1 If Cells(i, 1) = Cells(i - 1, 1) Then col = Cells(i - 1, IV ).End( _ xlToLeft)(1, 2).Column col1 = Cells(i,...
Excel: Excel help, graph chart, radar chart
graph chart, radar chart, regression analysis: Rachit The relationship between each variable is too large for it show all of them on one graph. You would have to use some of the variables as a percentage. This may increase the readability of your chart. A line chart or a radar chart would be the...
Excel: Excel help, graph chart, regression analysis
graph chart, regression analysis, indicies: The problem is you have 3 sets of values. col E is in the hundreds of thousands & up; Col G is in the thousands, and the rest are small. You can t chart 3 sets of values like this. You need to scale down at least col E or G, then plot the values on 2 axes...
Excel: Excel highlight box, grey color, question thanks
grey color, question thanks, critchley: Okay, I ve gone and asked. Here s the answers I got. __________________ Tim Critchley: It s a new one on me! Any suggestion of how the colour was changed in the first place? Will Riley: AFAIK (that means as far as I know ) the answer...
Excel: Excel hyperlink direction, excel, outlook
excel, outlook, mail: As far as I know, that cannot be done directly. One thing that works, and it is only one manual step, is to copy all the list of addresses from Excel in a single copy action, and paste them on the BCC part of an Outlook email message. Outlook recognizes...
Excel: Excel hyperlink problems, complex excel spreadsheet, excel hyperlink
complex excel spreadsheet, excel hyperlink, auto recovery: Hello Marty, First I would make frequent backups to make sure you don t get in a position of losing data. I don t think I have ever experience it, but MVP Dave McRitchie has compiled quite a bit of information about it. At one point he does say that...
Excel: Excel hyperlink problems, excel hyperlink, county entities
excel hyperlink, county entities, multiple city: Hi, Linda. I would definitely have to see the file to understand the exact differences. I am leaving in the morning for a week. I can look at it if you send it shortly. It s almost 8 PM here on the east coast. Send within an hour or two and I can check it...
Excel: Excel if command, a3, a1
a3, a1, b1: =if($A$1= buy ,A3*(-1),A3) Applying the $ in front of A and 1 make each of those references static. If you then copy this formula somewhere else, the other referenced cell will change relatively. =if($A$1= buy ,b7*(-1),b7) Is this what you are looking...
Excel: Excel if command, cell b2, search portion
cell b2, search portion, cell c2: Hi David, Here is another approach if you have some cells that may contain buy as part of the cell, like strong buy Here is your formula (to be placed in cell C2) assuming strong buy is in cell B2 and D@ contains the value to change: =IF(ISNUMBER(SEARCH(...
Excel: Excel import of ASCII [DOS] delimited text fixed width (variable), comma delimited, delimited text
comma delimited, delimited text, ascii dos: This used to work perfectly before Ecel 2003. I haven t tried it since I recently upgraded, so I am not sure what to do. However, there are several possibilities. You could send me your file and see if I can make it import ptoperly. OR You...
Excel: Excel inconsistent treatment of "nothing" cells, formula evaluation, header row
formula evaluation, header row, value error: There are different types of nothing cells - one is empty and another is null, and they re different. An empty cell is one which contains nothing (and never did or was edit/clear/all d). the other is as a result of a paste special of empty text. Try this:...
Excel: Excel is not calculating correctly, incorrect calculations, correct calculations
incorrect calculations, correct calculations, manual button: Hi Jacob, My first guess is that somehow your spreadsheet is on manual calculation. Let s try this: -Press the F9 key and see if the numbers change to the correct calculations. -If this works, Do: Tools-Options-Calculation, and see if the Manual...
Excel: Excel issue - CountIf help?, excel tips tricks, column c
excel tips tricks, column c, 599cd: Make column C say: =IF(A2=B2, 1, 0) Now if you SUM up column C, you ll get the total of rows where A = B. Let me know if you have any other questions or comments. Also, be sure to visit my FREE Excel Tips & Tricks Page at http://www.599cd.com/tips/EXCEL?key=AllExperts...
Excel: Excel knowledge, financial models, delhi india
financial models, delhi india, functionalities: Dear Nitesh, I advise not to join any institute as what they teach you is not practically of significance, rather than that surf the net and be the member of some good forums where you can have good discusions and opinions from the people across the world....
Excel: Excel labels in formulas, raina hawley, creating graphs
raina hawley, creating graphs, blank cells: Steven, There is an excellent solution to this but it is COMPLEX! You need to create these range names as DYNAMIC RANGES using the OFFSET function with an embedded COUNT function! This will define allow the range name to expand and contact as...
Excel: Excel limit., weather organization, would appreciate your feedback
weather organization, would appreciate your feedback, weather data: Hello Richard, I think what you re doing it s great, I really do. About your problem:Now I work as a Incentives and Commissions Analyst and I have to handle databases with more than 100.000 rows. For this, I could split data in more Excel files, but it...
Excel: Excel limit., weather organization, would appreciate your feedback
weather organization, would appreciate your feedback, weather data: Well, Excel 2007 is really different regarding charting (the engine has been completely rebuilt) and in my opinion it is still buggy. I d wait for at least service pack 1 before even trying. Chances are huge that screenlayout has changed too. So you r...
Excel: Excel limit., weather organization, would appreciate your feedback
weather organization, would appreciate your feedback, weather data: No, you will not have to re program the macros. The size of the charts should stay the same. The layout has changed and 2007 no longer has toolbars. It now has what MS calls a ribbon. Your best bet is to go to MS s site and you can find out all you...
Excel: Excel limit., weather organization, would appreciate your feedback
weather organization, would appreciate your feedback, weather data: Richard this link explains most of the changes in your area http://technet2.microsoft.com/Office/en-us/library/bee594b4-01b1-4d17-90ca-d43735a7382a1033.mspx?mfr=true The layout is different (toolbars and menu) and I would suggest going to the Moustamer...
Excel: Excel limit., weather organization, would appreciate your feedback
weather organization, would appreciate your feedback, weather data: Richard I would back up important files to (1) a second hard drive, (2) a DVDRW, and (3) zipped and encrypted to a network location.) Installation does give you the option of keeping some or all existing versions of Office programs with the exception...
Excel: Excel limit., weather organization, would appreciate your feedback
weather organization, would appreciate your feedback, weather data: You re welcome. No, you will not have to uninstall 2003. I can t guarantee that no complications will arise but they shouldn t. 2007 gives you the ability to create 2007 flavored files and 2003 flavored files. The drawback to the 2003 flavored ...
Excel: Excel limit., weather organization, would appreciate your feedback
weather organization, would appreciate your feedback, weather data: Macros will work in the later version so no problem there. Charts should be OK as well, but the layout of Excel 2007 is COMPLETELY different from earlier versions - all menu commands etc are in different places. An Access database could be used, the issue...
Excel: Excel limit., weather organization, would appreciate your feedback
weather organization, would appreciate your feedback, weather data: Access is a database program and would need some work to copy across what you have, so that is PROBABLY a non starter. Formulas should remain the same and as far as I can tell the graphing should be fine - might be worth a check at http://support.microsoft.com...
Excel: Excel limitation, home daycare, check boxes
home daycare, check boxes, dilema: Yes, it is possible. Anytime you need/want EXCEL to perform actions for you automatically you will need to write and then run a macro to do that automatic action. And since I can t see the workbook then it is hard for me to help you rearrange it to make...
Excel: Excel linked ActiveX ComboBoxes, target address, combobox
target address, combobox, address range: In the worksheet object, add this code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range( answer1 ).Address Then Me.OLEObjects( ComboBox1 ).ListFillRange = Range( Answer1 ).Value End If End Sub This code...
Excel: Excel linking individual sheets, cell a1, typist
cell a1, typist, relative referencing: Karen This is what the formula looks like =+Sheet1!A1 This will bring the value in sheet 1, cell A1 over to wherever the formula is. The easiest way for me to create the formula (since I m not a very quick typist) is make the cell active that I...
Excel: Excel links, blank cells, cell values
blank cells, cell values, best bet: Bose, Sorry, but I don t think you can do this. There might be some elaborate way using COUNT(columns-or-rows-in-the-remote-workbook) which would at least tell you how many columns or rows there should be, but to automatically add columns (presumably...
Excel: Excel links, email
email: What version of Excel are you using? Can you send me a copy of the file? The other steps are to have a look at tools options calculation and make sure calculation is Automatic and update remote references is active. my email is (answer@pcexperthelp.com)...
Excel: Excel links, link option, spread sheet
link option, spread sheet, task bar: Edit links is the right starting point - I would suggest two alternatives - one, if you don t need the formulas, just use the BREAK LINK option - this will replace any formulas with the current values. Alternatively, use the change link to get the link to...
Excel: Excel list question, vba code, website users
vba code, website users, database program: Probably would be safest/easiest to use a UserForm with some VBA code to check your entry. Feel free to send me a sample wb (or just your email address) & I ll send you a solution (If you send the entire wb, you ll have the finished product when I return it)....
Excel: Excel lookups, vlookup, hendrik
vlookup, hendrik, spreadsheet: Hi, Hendrik. This is no easy task in Excel...or any application for that matter. The closest I have ever seen to solving this is a challenge that was put forth to the posters at MrExcel.com. Here s a link: http://www.mrexcel.com/pc09.shtml I hope it...
Excel: Excel macro - 'run time error 1004', vlookup, style references
vlookup, style references, excel macro: Michael, I found two problems. You are using FormulaR1C1 and feeding it a formula with A1 style references. You have two extra right parentheses on the right side. this worked for me: Sub ABC() Range( F18 ).Select Application.CutCopyMode = False...
Excel: Excel macro - follow up, excel macro, ubound
excel macro, ubound, transpose: Again, I have to assume that you want to paste the data starting in A1 of sheet2 because you did not say. And again please remember I can not see what you see,know what you know about your data, your sheets, your workbook, your situation, your conditions,...