axix, money column, info charts: I m not sure that this is going to be something that can be done with the data as laid out - my preference would have been to have had one column for time, and then columns for each of the cashiers. However, charting is not my strongest area, so you MAY like...

Excel: chart transfer-excel to word, douglas m smith, christmas merry christmas

douglas m smith, christmas merry christmas, word experts: Hi Aseem, The Word experts are listed here: http://www.allexperts.com/getExpert.asp?Category=1058 I would recommend Dreamboat. She also knows a lot about Excel too. Her profile is here: http://www.allexperts.com/displayExpert.asp?Expert=55275...

Excel: chart transfer frm excel to word, chart expert, phd thesis

chart expert, phd thesis, word program: Aseem I have to tell you that I m not a real chart expert. I ve not heard or seen of a situation where the chart disapears after having been copied to the word document. I m guessing that the real problem is not with excel but is either with the word...

Excel: chart transfer frm excel to word, phd thesis, word document

phd thesis, word document, cheers: Hi Aseem How are you? I guess that this happens because the charts demand lots of memory. You can try to copy each chart and then in word do Edit - past special. or save each one in a different word file or ... zip them... I know that this does...

Excel: chart, budget data, amouts

budget data, amouts, bar charts: I will try to give some short answers, and some links that can explain things better than me. 1 & 2.- I like simple bar charts for that. In this case, because you have negative numbers, I would choose horizontal bar charts vs. column charts, as negative...

Excel: charting, stock market symbols, excel stock

stock market symbols, excel stock, etrade: David Excel doesn t do streaming when it is linked to stock market symbols, you have to continually update it. I haven t used any stock market programs apart from that which is included with Etrade, so I can t give you a better answer, I would suggest...

Excel: charting data, line plot, scatter plot

line plot, scatter plot, 20999: Hi Joan, If I understand your question properly, you want to show the gaps in your data and still have lines from point 2 to point 4. I don t think this can be done. Unless you manually adjust the series by putting values in your cells and maybe marking...

Excel: charting, aluminum heat, speedy reply

aluminum heat, speedy reply, x bar: I m afraid I don t understand what you re asking. If you have a sample workbook you could send along, that d help. And perhaps fax a DRAWING of the desired chart (assuming you can t create an Excel version), that d also help. bobumlas@yahoo.com 845-365-362...

Excel: charts, line graph, microsoft xp

line graph, microsoft xp, excel help: Hi, Deb, The Excel Help page called Create a chart advises that data for a line chart should be arranged in two columns with a heading at the top of each one. If this doesn t help, please write back with more information. Can you be more specific in...

Excel: charts, excel 2000, violet

excel 2000, violet: Unfortunately, Fred, you can t combine regular bars and stacked bars in the same chart. There are indeed charts where you can combine types -- column and line is the most common combination -- but bars and stacked bars can t be done. How about a stacked...

Excel: charts, computer wizard, poker results

computer wizard, poker results, space column: Hey William, I think it may be easier if you could possibly email me your spreadsheet and I can make the changes for you. My email address is bluecat74@yahoo.com If this is not possible, let me know and I will write out some detailed instructions for...

Excel: charts, graph plot, line graph

graph plot, line graph, chart wizard: To create a line graph with only one line, you shouldn t have 2 columns of numbers. You should have one column of labels for the Category(X) axis and one column of numbers for the Value(Y) axis. For instance, in range A1:A4 put JAN,FEB,MAR,APR. In range...

Excel: charts, scatter graph, graph plot

scatter graph, graph plot, line graph: Shane: When setting up your chart you will need to select a XY Scatter Graph . This is one of the few charts that will actually uses ordered pairs by default (which is what I presume you are doing - x,y). Once you select the Scatter option you can...

Excel: charts, high score, good answer

high score, good answer, word document: Phil Sorry for some reason I did not get your question. I am a CPA and am super busy right now and don t have any time--I suggest you reask the question of another allexperts person adn I m sure you can get a good answer. Sorry I couldn t be of more...

Excel: charts, empty cells, line charts

empty cells, line charts, desired effect: Brian One option is to revise your formulas to show the 0 answers as N/A, excel wont pick those up in the chart. Depending on your formula you use it may look something like this =IF(A1+B1=0,#N/A,A1+B1) Another option is use some VBA, This link shows...

Excel: charts, data labels, info charts

data labels, info charts, excel charts: If I understand the question correctly, just right click on a y value and format the data series, then click on the data labels option. Add values here. For charting ideas, these sites are excellent http://processtrends.com/ http://peltiertech.com/Excel/Charts/index.html...

Excel: charts/graphs, column line, column chart

column line, column chart, excel 2000: Hi, Gary, Creating a combination chart is a very effective way of contrasting one data series against another. And the column-line combination is one of the most popular (and effective) ways to do it. The steps are very simple to follow: 1) Create...

Excel: charts and nonsequential X axis, xy scatter, wavelength

xy scatter, wavelength, axis: Well, I did it by using an xy scatter, and in formatting the x-axis, the scale tab, I entered the values 0 512 1 1 for the first 4 boxes. On the font tab i chose a size 5, On the Number tab I gave it a custom format of 0, and finally I had to drag the chart...

Excel: charts, line graph, chart key

line graph, chart key, microsoft xp: Hi Deb, When you create a chart in Excel, especially if you use the wizard or the F11 chart key, the default type is a bar chart. Choose the chart (click once on the outer edge). Now choose from the menu - Chart, Chart Type and select the XY type of chart....

Excel: check boxes, left mouse button, check boxes

left mouse button, check boxes, control tab: Turn on your Forms toolbar by selecting from the Main Toolbar: View, then Toolbars, then Forms. You should see the check box feature on the toolbar. Left click on it once to select it. Then move your mouse down to the spreadsheet - your cursor should look...

Excel: check duplicate data in rows B77-B132, base font, row numbers

base font, row numbers, excel sheet: Hi Jawad, Attached is the macro that you need to enter the the module ThisWorkbook . Click (alt + F11) and double-click on ThisWorkbook to enter the code. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim xlR As Excel.Range...

Excel: to check if the formula is same or not, cell a1

cell a1: Kiran I am not sure exactly what you are trying to get at, but--if the formula is the same the results will be the same. So you could compare results with a simple +A1-B1 type of formula (if the results are the same the answer to the formula would be zero)....

Excel: check for necessary values in range or duplicates, irow, necessary values

irow, necessary values, worksheet functions: Hi Kelly, Here s a fairly easy way to accomplish this. It is made easy by the fact that it is possible to call most Excel worksheet functions from VBA, so I made use of COUNTIF(). Here s the code: Sub OneToNine() Check range A1:A9 for numbers...

Excel: checkbox background, neck of the woods, background text

neck of the woods, background text, default behaviour: You are right, I was in born and lived in in the north of Spain, I have been in the U.S. for just two years now. Thank you for your note, I don t usually answer if there is not a question, but you have said something that I would like to comment: always...

Excel: checkbox, macro moves, isempty

macro moves, isempty, analyzing data: Hi jolyon, There are several ways to do this. The easiest is to link each checkbox s value to the underlying cell. The cell will then contain the value TRUE or FALSE depending on whether you check or uncheck the checkbox. If you are using ActiveX checkbox...

Excel: checkboxes, bookmark name, userforms

bookmark name, userforms, word document: Would it be possible to see the data/output files as this would clarify things a lot - I work quite often with excel data into word - normally, I would have a template in word with bookmarks, and these bookmarks would be populated by the output of the excel...

Excel: checking 2 cells for input and then calculating a formula based on which cell has the input, checkmark symbol, ebay sales

checkmark symbol, ebay sales, ebay: Since I don t know the separate calculations, you can probably figure it out from this... =IF(A1=CHAR(252),Calc1,Calc2) where calc1/2 are your separate calculations, Char(252) is the checkmark in wingdings, and A1 or B2 contains the checkmark, so if it s...

Excel: About checking rows, parent cells, rng

parent cells, rng, message box: If data is in D,then you want a cost center in B - for rows 77 to 132 - this is what I came up with (untested) Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim rng As Range, cell As Range On Error Resume Next...

Excel: choice the bin, paper bin, precise answer

paper bin, precise answer, printer properties: Hello loic, I am not certain I understand your question. You refer to the printer s sheet being always the same. Do you mean that you want to change the Excel Page Setup paper size to be consistent with the paper you have loaded in the paper bin you wish...

Excel: How to choose a item in Excel combo box and execute, excel combo box, cell b1

excel combo box, cell b1, cell b2: As I understand it, you have field names in range D13:Z13 in Sheet1, and immediately below each of these field names you have a list of values. On Sheet2, you have a combobox where the user picks one of the field names, and you want Sheet2 to display the values...

Excel: circular arguments, peak value, cols

peak value, cols, maximum value: If you have values in Cols A and B, and you want C1 to show the maximum value in these columns, the formula is: =MAX(A:B) Example: if the only values you have in Cols A & B are 1, 2, 3, and 10, then C1 would display 10. I m not sure what you mean...

Excel: circular reference, getting around this, target value, circular reference

target value, circular reference, project browser: Yes, there s a way around it. You need to attach code to the associated worksheet s change event so that if an entry is detected in column A or column B the appropriate value or formula is automatically entered in the opposing column. To illustrate, open...

Excel: circular reference question, circular reference, circular references

circular reference, circular references, reference question: feel free to email it to me at bobumlas@yahoo.com. Some circular references are way too involved to find and one can live with it by turning iteration on in tools/options/calculations. If R39 has =O55 and O55 doesn t pick up the value, it s likely that...

Excel: How to do circular reference, sheet tabs, cell values

sheet tabs, cell values, circular reference: Very few people advocate circular references - so I don t know why you would want to use this option for writing formulas using all functions . Stehpen Bullen has an example workbook showing how to use circular references: GS_Demo.zip it is on his...

Excel: class modules, msoffice 2000, copy functions

msoffice 2000, copy functions, addin: It s been a long time since I ve worked with class modules & using code to copy code. My first thought is that it could be done, but unless I m not understanding what you want to do, it seems it d be easier if you creaed code to re-enable cut/copy in the workbook_open...

Excel: clear cells, time aprox, macro recorder

time aprox, macro recorder, thanks in advance: Difficult to be more specific without writing the code for you - as I say, IF you use the macro recorder, you can record the action of selecting and clearing one or more cells - you can then use the VBA editor (Alt F11) to edit this if you didn t record clearing...

Excel: clear cells, wk, thanks in advance

wk, thanks in advance, cells: Hi Steve Do you still need help with this Regards Des ...

Excel: clip board, clip art internet, core skills

clip art internet, core skills, better luck: Hi Rosemary, I am sorry I do not know the answer to that one. You would probably have better luck posting on www.mrexcel.com message board, there you would have more people looking at the question and could probably get the answer for you. Thanks Ke...

Excel: code to convert date from TEXT format (03-02) to DATE format (200203), mm yy, new date

mm yy, new date, lower case: First of all, I did not read your question completely. The code would not work anyhow since 03-02 is in TEXT. But, you don t need a macro anyhow. Since you asked for that bit of code then that is what I gave it you. I assumed based on your question that you...

Excel: code, des o connor, close application

des o connor, close application, true application: HI Steve I dont know if you have your code yet but I have put this together for you, I beleive it would meet your requirements ; Private Sub Workbook_Open() If Range( IV65536 ).Value = Then Range( IV65536 ).Value = 0 Dim inc As Integer inc...

Excel: coding, spreadsheet cell, ogilvy

spreadsheet cell, ogilvy, cells: Yes if Cells(3,54) are not blank, you set lcurrentrow to the lastrow. If it is blank you set lcurrentrow to 3. Either way, at the end you set lCurrentRow to the row of the Activecell, but unless lcurrentrow is a global variable, you will lose the value...

Excel: cods and discrption, vlookup, column number

vlookup, column number, d10: This sounds like you want vlookup - which is =vlookup(WHAT,WHERE,WHICH,LOGICAL) WHAT is the value you want to look up (eg 123) WHERE is the location of the table containing this value (in the first column) and the data you want to return in any column...

Excel: collecting data, special education teacher, hi aaron

special education teacher, hi aaron, students progress: Excel would be the ideal application for this. DO NOT USE POWERPOINT FOR THIS. Unfortunately, I can t just teach you how to build an application in Excel in a 5 min response. If you ve never done anything with Excel, you should probably pick up a book...

Excel: coloms, selection range

selection range: Jos, the following code will split it up into 3 nearly equal groups. It doesn t require any user input. Sub SplitItUp() Range( A:A ).Select TotalRows = Application.WorksheetFunction.CountA(Selection) Range( A1 ).Select SplitRows = TotalRows / 3...

Excel: coloms, douglas m smith, brainbench

douglas m smith, brainbench, root canal: Hi, You can use the InputBox method to ask for a number to pass to the macro: NumRows = Application.InputBox( Enter the number of data rows ) Are you just looking for the input solution or the entire macro? If you need the macro, I won t have time...

Excel: coloms, inputbox, excel 5

inputbox, excel 5, dest: Hi Jos, How are you? It is not the entire solution, but you can start from the following 1. Tools - macro - VB editor 2. Insert module 3. Copy Sub MULTICOL() y = InputBox( ROW WHERE YOU WANT TO START MOVING DATA ) Dim a As Range Set a = Sheets(1).Range(...

Excel: coloms, expert listings, excel expert

expert listings, excel expert, easy job: Of course it s possible to create a macro that can do almost anything in Excel. And if this is something that you re doing repetitively, than it is indeed a good excuse to do so. I guess you didn t notice where I say that I m not familiar with macros and...

Excel: coloms, aidan, email

aidan, email, heritage: Yes it s possible - does the order of the data matter and if so do you want to go down the rows then across, or across the rows and then down - it may help to email me with further details (maybe even the file you want to process!) and I can do you a macro...

Excel: coloms, page greetings, colomns

page greetings, colomns, number 0: Sorry it took so long to get back to you... try this: Sub Reformat() Dim Rg As Range, Ans As Range On Error Resume Next Err.Clear Set Rg = Range(InputBox( Enter the Range to be rearranged , , Selection.Address)) If Err.Number 0 Then...

Excel: coloms, job

job: It is possible - not sure how important the order of the data is - this macro will do the job for you Dim ColCountVar As Long, RowCountVar As Long dim StartCell as string dim EndCell as string StartCell=inputbox( Please enter the starting cell ) EndCell=inputbox(...

Excel: color change upon import of Access query, target range, transferspreadsheet

target range, transferspreadsheet, column color: Example: here s a sub that will change the color of the first 10 rows of Sheet1, based on the values in H1:H10. This sub can be called by any other vba sub, or by any Excel event: Sub newColors() Dim cl As Range For Each cl In Sheets( Sheet1 ).Range(...

Excel: Having color change on linked cells, target value, colorindex

target value, colorindex, cell changes: Hi Cathy, How are you? Please try soething like: The following procedure must be saved in Sheet1 of book1 (in VBE, project explorer of Book1 (left-handside of the screen), Click sheet1) Sub Worksheet_Change(ByVal w1 As Range) MsgBox aa Dim w2 As...

Excel: color fill cells based on content, format button, city names

format button, city names, j10: Hi David, For example if the City names are in cells A1:J10 I would select cells A1:J10 then go to the Excel menu and select “Format” then “Conditional Formatting…” then you want the drop down list boxes to be selected as “Cell value is” then “Equal to”...

Excel: color selection to extract with VBA, colorindex, rcl

colorindex, rcl, color selection: This should get you started, I have put some alternatives into the code because it is not clear whether you are looking for blue font or background, or exactly what range you are searching - one column or several Option Explicit Sub ExtractCells() ...

Excel: color selection to extract with VBA, colorindex, rng

colorindex, rng, tab index: Sub extractBlue() Dim rng As Range Dim lrow As Long Dim i As Long lrow = Cells(Rows.Count, 1).End(xlUp).Row If lrow 55 Then Exit Sub For i = 55 To lrow If Cells(i, 1).Interior.ColorIndex = 33 Then If rng Is Nothing Then Set rng = Cells(i,...

Excel: colors in excel, excel conditional formatting, microsoft excel

excel conditional formatting, microsoft excel, macros: Is this even possible to do with an entire worksheet without having to enter the conditional formatting for EACH cell?? Certainly. :) See: http://www.officearticles.com/excel/conditional_formatting_with_formulas_in_microsoft_excel.htm If you...

Excel: column deletes, text string, spreadsheet

text string, spreadsheet: Alan, Here is a simple routine that should do the trick. Let me know how it works or if you need more help. AgentSmith Sub Alan() For Each cell In Range(Range( a1 ), Range( IV1 ).End(xlToLeft)) If cell = Ignore Then cell.EntireColumn.Delete...

Excel: Why is my column fommatting not affecting all rows?, annoying habit, paste data

annoying habit, paste data, excel spreadsheet: The reason is because the data IS text - excel has an annoying habit of doing this when you copy and paste data - you can fix it by editing each offending cell (just click in it, click on the end of the formula and hit enter - this will fix it - you can...

Excel: column format, four digits, header row

four digits, header row, coin catalog: Hi Karen, I apologize for the delay. I went on vacation and forgot to check my email for messages until now. If you have your list in column A and the number part is always four digits, then in cell B1 paste the following formula. =LEFT(A1,4) ...

Excel: column format, cell a1, coin catalog

cell a1, coin catalog, sort key: Say that your list starts in cell A1. In cell B1, enter the formula =LEFT(A1,4) and copy it down as far as your list goes. Then, when you sort, use columns A and B as your range to sort, with column B as your primary sort key, and col A as 2ndary sort...

Excel: Why is my column formatting not affecting all rows?, excel spreadsheet, cells

excel spreadsheet, cells, wizard: Esther, I can t say for sure, but I would be willing to bet that you have extra characters in those cells. Maybe a space or something that doesn t display. You need to select one of the cells, and examine it in the formula toolbar to see what Excel thinks...

Excel: Why is my column formatting not affecting all rows?, numeric format, excel spreadsheet

numeric format, excel spreadsheet, len: I recommend first reformatting the entire column to some numeric format (anything but text), then copy an empty cell, select the column & paste special values & also check add . THEN try the text-to columns if still need be. I m unfamiliar with how the SQL...

Excel: column to row, cell c2, absolute references

cell c2, absolute references, indirect address: Hi YA, Your best bet is two add a third column that uses absolute references of the second column and then to transpose that third column into the horizontal second row. You can then delete the third column and changing items in the second column will...

Excel: columns, dates and colors, tim wheatley, conditional formatting

tim wheatley, conditional formatting, default colors: Tim, you can manage 4 colors besides the default colors with Conditional formatting found under the format menua. Debra Dalgleish has quite a bit of information at here web site. http://www.contextures.com/tiptech.html look under C, then look at conditional...

Excel: columns in a Listbox (XLS97), mr smith, salutation

mr smith, salutation, doubleclick: Markus, I noted on the feedback site you found a solution, Great! I suggested showing multiple columns so you could see the data for each user. That s all. It s an interesting solution, I am a little curious what you set j equal to. I can see how the...

Excel: columun format, work sheets, new values

work sheets, new values, b2: One way of achieving it is to clear first the range, and then run the macro that you are currently running. To do so, add this statement at the beginning of the macro: Sheets( TOTAL ).Range( A2:H65536 ).Clear This will clear the contents of the TOTAL...

Excel: how to make a comand button, command button, code option

command button, code option, command buttons: Nitin, Hope you are doing well. The solution is a little bit more complicated but possible 1. Create a module - call it Module 1 with the code Option Explicit Global saved_by_cmd_button As Boolean 2. In the ThisWorkbook module enter the code Option...

Excel: combat training, microsoft communities, transition one

microsoft communities, transition one, course microsoft: Not that I am aware of, but perhaps let here read questions and answers on the Excel newsgroups for an hour or two. She can formulate her own answers before she sees others. Eventually, perhaps contribute her solutions. news://msnews.microsoft.com/microsoft.public.excel.misc...

Excel: combat training, transition one, intrument

transition one, intrument, basic excel: There probably is a web site like that, I just don t know of it. The best thing to do would not be drills, but practical examples. I play the piano & practicing the scales did nothing more than help me remember the scales. Didn t improve my piano playing....

Excel: combination and calculation, data validation, pull down menus

data validation, pull down menus, vlookup: Hi Shorab, It looks like your best bet would be to use a combination of data validation (to create your pull down menus) and vlookup for once a product or accessory is chosen, a value will be calculated. Hope that helps, -Craig -If you want to...

Excel: combination and calculation, vlookup formula, data validation

vlookup formula, data validation, pull down menus: Sorry, I misunderstood the part that you were asking for. In this case, it looks like you are looking for dependent drop down list, and there is a good tutorial on how to set them at: http://contextures.com/xlDataVal02.html If you have any issues with the...

Excel: combination and calculation, eventual price, data validation

eventual price, data validation, pull down menus: Yes it s possible - the three products option is easy enough as a data validation list will handle this. The accessories are probably more complicated, as presumably they could have any number of accessories from 0 to 100? I would probably use a listbox...

Excel: combination and calculation, data validation, cell c3

data validation, cell c3, vlookup: Shorab, It should be possible. Assumptions - 1. You have names of the 100 accessories in D1 to D100. 2. For each of the accessories, you have the rate in E1 to E100. 3. B3-B5 relates to Product1 - B6 is the output cost 4. B7-B9 related to Product2...

Excel: combination and calculation, data validation, vlookup function

data validation, vlookup function, pull down menus: You could add additional columns that use the VLOOKUP function to fetch the prices. To set this up, you would have to add a price column next to each product/accessory. Suppose the products and their prices are in Sheet3!$A2:$B101, and your current product...

Excel: combinations, lottery ticket, cel

lottery ticket, cel, computer program: Ok, I think I follow - I would suggest using Word for this as there is no limit on the length of the document - there are 230230 unique combinations of 6 numbers from a 26 number selection - the following macro will do this for you Sub CombinGen() This...

Excel: combinations of numbers, math problem, cell a1

math problem, cell a1, 26 letters: Jill, Yes it can be done. However, the formula is rather ugly. To begin, place the following numbers in column A beginning with cell A1: 48 49 50 51 52 53 54 55 56 57 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82...

Excel: combinations, digit strings, visual basic for applications

digit strings, visual basic for applications, vba code: To automate your work, you ll need to learn how to use Visual Basic For Applications (vba). Here s the vba code to create the list you re talking about: Sub allCombos() creates list of 4-digit strings, with each digit being 1 through iMax Dim iMax...

Excel: combine 2 lists into one, number column, february sales

number column, february sales, separate lists: IF the lists have the same number of entries, then I would use VLOOKUP to get the information - my site (www.aidanheritage.co.uk/Excel) explains this - you could then use edit copy, edit paste special and values to get the values in. Alternatively, a macro...

Excel: combine cols from 2 w'books, destination cell, coleague

destination cell, coleague, command button: Hi Dennis, How are you? There is certainly better ways to synchronise your work with your coleague. Yet, the way you purpose is straight forward. Please try the following 1st TOOL - MACRO - VISUAL BASIC EDITOR 2. INSERT - MODULE 3. COPY Sub copying()...

Excel: combine lines for indexing, exit function, excel sheet

exit function, excel sheet, blank lines: Hi there, here is a VBA function that returns the difference between string1 and string2. You can modify it to fit your needs. If you need more help with it, please let me know. Function StrDiff(str1, str2) As String j = 0 For i = 1 To Len(str2)...

Excel: combine lines for indexing, access databases, excel sheet

access databases, excel sheet, blank lines: I work with Access databases a lot. What are you importing into? Your data is originally organized in three columns, correct? To combine the information the way you are suggesting seems like you re making it less useful for a database - you re turning...

Excel: combine lines for indexing, column c, excel sheet

column c, excel sheet, ogilvy: You data came across like this to me: Student number name set 1 set 2 set 3 set 4 set 5 2134 Henry 234 2134 Henry 222 2134 Henry 444 2134 Henry 555 2134 Henry 789 5678 Sam 543 5678 Sam 234 5678 Sam 534 5678 Sam 546 5678 Sam 789 so there...

Excel: combine lines for indexing, unique records, excel sheet

unique records, excel sheet, student ids: You can do this on two steps, first extracting the unique student list, and then getting each value with a formula you can copy and paste on all your range. For the example I have copied your data on the range A1:G11, with the headers on row one. Let s go...

Excel: combine lines for indexing, blank table, excel sheet

blank table, excel sheet, pivot table: I can think of a couple of ways to do what you need; however, I believe the easiest method is a Pivot Table. It should work perfectly presuming each student only has one data value under each set (which is the way your example shows). If not, we would just...

Excel: combine multiple cells into one cell, c3, cells

c3, cells: You may run into problems in that a cell in excel can only contain a maximum of 32,767 characters. There is no direct feature in excel to merge and keep the values, so you could use concatenation (easiest way being =A1 & B2 & C3 which would combine all three...

Excel: How to combine two ranges and get an array, array definition, physical result

array definition, physical result, j3: You need a user-defined formula. This one is called UF, so you d enter =UF(A1:D3). Function UF(rg As Range) Dim st As String st = { For i = 1 To rg.Rows.Count For j = 1 To rg.Columns.Count If IsNumeric(rg(i, j)) Then...

Excel: combine vlookup and if statment, microsoft office applications, autofilter

microsoft office applications, autofilter, microsoft excel: Melissa: Excel is not designed to do this--it requires VBA to do it perfectly . If you want to view data in other ways, use the Subtotals or AutoFilter features of Excel or move your data to Access. Generally, the moment you copy data in Excel, you really...

Excel: combining choose function with other functions, account names, j4

account names, j4, source data: Hi Gary, As much as I love to use CHOOSE, it s a bit cumbersome for this task: =SUM(D4:CHOOSE(B3,INDIRECT(D4),INDIRECT(E4),...) Using INDEX and MATCH is a much more efficient method: =SUM(INDEX($D$4:$O$4,1,1):INDEX($D$4:$O$4,1,MATCH(B3,$D$3:$O$3,0)))...

Excel: combining data into one cell, cell cursor, bottom right

cell cursor, bottom right, e1: Miranda, there are 2 formula options here. In the first cell, use this formula: =D1& &E1 or =D1&E1 if you don t need a space between the 2 entries, or you can use this formula: =CONCATENATE(D1,E1) or =CONCATENATE(D1, ,E1) to add a space....

Excel: combining desired numbers, colums, column c

colums, column c, true values: Is the data in A, B, and C entered as true values or as text? Based on you example, it sounds as if it is entered as text (C2:01). If C2 contains values then it would be 1 and not 01. If this is the case then your formula should work. Or C2 could be...

Excel: combining pages, paste operation, manual effort

paste operation, manual effort, data areas: Dale, with no further information, I am afraid you are stuck with that. A column has a single width for the entire spreadsheet. Workarounds would include selecting the area in page 2, then hold down the shift key while you do Edit. You will have the...

Excel: combining pages, paste operation, cell structure

paste operation, cell structure, column width: Hi Dale, You can try going to the Excel menu and selecting Data then Consolidate however with mismatched data and cells it will probably not work. The only thing I can suggest is to send me an example if you want and I will take a look at it to see...

Excel: combining pages, paste operation, cell structure

paste operation, cell structure, column width: You don t say why you want to do this, but IF you need to do it I would suggest putting page 2 to the RIGHT of page 1, rather than below it - excel can only have one width per column, so (apart from merging cells) you cannot have two different widths in that...

Excel: combining stacked and column charts - graph is wrong, data labels, gross returns

data labels, gross returns, column chart: CARRIE Sorry I m just getting back to you-- I hae a broken computer, and haven t been able to get online, etc. I won t be able to answer the question for you, because I don t have access to the excel info I need to work out the answer. I suggest you reask...

Excel: combining worksheet, dropdown arrow, countif

dropdown arrow, countif, employee id: Melodye, If I were doing it, I would copy the data from each sheet into another workbook with one data set directly below the other data set so the columns line up. Then I would sort the data on the unique identifier (employee id perhaps - I wouldn t think...

Excel: combining worksheets, paste routine, blank rows

paste routine, blank rows, paste function: Mike Not knowing all of the parameters of your worksheets its a little bit difficult to answer, but--- I m assusming you know how to do the simple copy of each worksheet to the summary worksheet, but you want to copy the worksheet automatically, without...

Excel: combo box, combo boxes, vlookup function

combo boxes, vlookup function, exact formula: Yes, it s feasible, and you can do it with the IF and VLOOKUP functions as I explained in the last message. In order to do a drop-down box from the cell, select the cell, and go to Data Validation. Under Allow, choose List. The Source field will appear...

Excel: combo box, data validation, combo boxes

data validation, combo boxes, combo box: It can be done with cascading data validation. A lot easier to show you (with an explanation) than only an explanation, so if you send me your email address I will send you a file which shows how to do it. You can email me directly at bobumlas@yahoo.com with...

Excel: combo box help!, sheet names, combobox

sheet names, combobox, combo box: Surround your code with Application.EnableEvents = False ...your code here Application.EnableEvents = True End Sub It seems like whenever you move around in the combobox it keeps firing, so sheets keep getting added. Another thing you could do is...

Excel: combo box question, data validation, listindex

data validation, listindex, combo box: Why not have a lookup row? Have a cell to select an item from using Data Validation, then VLOOKUPs to display relevan data in the adjacent cells. With a ComboBox from the Controls Toolbox make sure the ListFillRange Property refers to the Column of...

Excel: combo box, combo box excel, data validation

combo box excel, data validation, drop down list: The basic answer to your question is yes. But, I am not sure I understand it. A drop down list is, in essence, a combo box. EXCEL has a combo box and a list box and both are commonly referred to as drop down boxes. The only differences between the two...

Excel: combo boxes?, cell d2, cell b2

cell d2, cell b2, word fish: John, The easiest way to do this is with a Validation List. For example, say your car, fish list was in cell B2 and B3. Highlight cell D2, then click Data, Validation. In the Allow: box, select List. In the Source box, type =$B$2:$B$3, or use...

Excel: combo boxes/arrows, combo boxes, control toolbox

combo boxes, control toolbox, 2 ways: 2 ways come to mind. First, make the # of dropdown lines be big enough to see everything when you click on it (all 50!) Second, you can use a control-toolbox combobox (activeX) and you can have code to reset it to the top when the mouse is clicked: Private...

Excel: More on combo boxes, combo boxes, cbx

combo boxes, cbx, combobox: Instead of using linked cell, which does not support this, use the click event Private Sub Combobox1_Click() Dim cbx as msforms.combobox set cbx = me.Combobox1 with worksheets( Results ) 1st column .Range( B9 ).Value = _ cbx.list(cbx.listindex,0)...

Excel: combobox - employee list that will call up file, john walkenbach, combo box

john walkenbach, combo box, new menu: Cathy this is best accomplished by creating an Add-in that can be installed on any computer that needs to perform this task. That way, the code would be able to run outside of any file that may be open. Basically, you would need to create a Userform...

Excel: combobox (List Fill Range), xls workbook, control tab

xls workbook, control tab, paste link: Sure: Open both your file with the combo and the CurrentOpsJobs file In your file with the combo: - Insert a new worksheet - hit control+tab to activate the CurrentOpsJobs file - select the range with the source data for your combo and click Copy...

Excel: comma demilimited file, text import wizard, comma delimited file

text import wizard, comma delimited file, training materials: I assume you mean you want to import a comma delimited file into Excel. If this is the case, you simply start Excel and click File, Open. The where it says Files of Type you click the drop-down arrow and choose Text Files . The you find the file and click...

Excel: IF command, cell b1, cell a1

cell b1, cell a1, command search: Hello Holly, So you want to know if A1 is a substring of B1, then in C1 put in the formula =IF(OR(B1= ,A1= ), No ,IF(COUNTIF(B1, * &A1& * ) 0, Yes , No )) this would not be case sensitive. If you need case sensitive you can use the Find command....

Excel: command button running module code, left icon, control toolbox

left icon, control toolbox, command button: Put the sheet in design mode. You can do this by making the control toolbox toolbar visible -- the upper left icon on the bar is the toggle for design mode. When it is depressed, you are in design mode. then double click on the commandbutton or right...

Excel: command button to sort, command button, selection sort

command button, selection sort, b2: Hi, You will need to create a MACRO and assign it to a command button. MACRO can be written or recorded. If the range / column is going to remain unchanged, you may record a MACRO by following method- (1) TOOLS -- MACRO -- Record a New MACRO (2) Change...

Excel: command button, cell a1, jimmy thomas

cell a1, jimmy thomas, command button: Hi Jimmy, How are you? Intersting question!! Please assigned to your button the following procedure: Sub Shiftingdown() Static G Dim orig As Range Set A = Sheets(1).Range( a1 ) G = G + 1 If G = 5 Then Set b = Sheets(G).Range( a + LTrim(Str(G)))...

Excel: command buttons and user forms, dialog sheet, command buttons

dialog sheet, command buttons, command button: Here is that little dialog I mentioned. It lists all the data with optionbuttons, click on one and it pastes into the activecell. ---------------------------------------------------------------- Sub BrowseData() ----------------------------------------------------------------...

Excel: command buttons, custom dialog boxes, bob phillips

custom dialog boxes, bob phillips, command buttons: This code uses an Excel 5.0 dialog box, so it s a very old style. I ll try to give you a little help anyway. This line of code: thisDlg.Buttons.Add 120, 50, 50, 20 will add a command button to your dialog box. You could put it, for instance, right after...

Excel: if command, handicap strokes, handicapp

handicap strokes, handicapp, score card: Robert I m a golfer also so I may relate to what you are trying to do,looks like you are trying to calculate points but ----- Are you asking for a formula that will print a cell, print a range of cells (like all 18 holes). Or by print do you mean calculate...

Excel: commandbutton to excel worksheet, command button, double clic

command button, double clic, pls: John Right clic on the command button and pick assign macro, and then pick the macro that calls the userform. If your command button is not on the worksheet, then go to the forms toolbar, double clic on the command button that you want, then double...

Excel: commands, cheers

cheers: Hi Richard, How are you? To add buttons in toobars, please do the following 1: TOOLS- CUSTOMIZE 2: TAB CUSTOMIZE 3: Choose the command you want and drag the button to any toolbar To protect a sheet, please do the following 1: TOOLS - PROTECTION...

Excel: compare 2 columns in 2 worksheets and removing dupes, excel csv file, unwanted inventory

excel csv file, unwanted inventory, inventory reports: You can use advanced filter and copy the results to another location. It d be easier to show than explain, so please let me know your email address and I ll send you an example. - also, please repeat what you re trying to do (I get LOTS of email) - send to...

Excel: Que:2 - compare 2 column in excel, column c, elegant solution

column c, elegant solution, b2: One way to do this would be to sort the table by column A, and then create a formula in column C that is: =IF(OR(A2=A1,A2=A3),B2, ) Then you could filter column C and it would just show you the rows where there was a duplicate value in column A. ...

Excel: compare two columns, reference numbers, irregularities

reference numbers, irregularities, a5: The countif function will return the unmatched cases - I would personally not worry about another column, as you can use data filter autofilter to find the unmatched - I m still not sure if you have potential unmatches in only one column, or in both, but that...

Excel: Followup To: compare two columns in excel, array formula, column c

array formula, column c, example purposes: To be honest, I m not sure I understand the question. Nevertheless, I have placed an Excel file here: http://home.swbell.net/nate-sus/excel/dhaval.xls I have placed two sets of formulas in the example (yellow and orange), both based on my presumptions...

Excel: compare columns, type b1, ascending order

type b1, ascending order, a1: Hi Annie, How are you? Please truy the following Assuming that the names in sheet2, sheet3, ... are in A1:A3 and names are in Master sheet in A1:A20 1. Sort the names in sheet2,sheet3,sheet4 and sheet5 by ascending order 2.In your master sheet type...

Excel: how to compare the contents of 2 cells, douglas m smith, letter combinations

douglas m smith, letter combinations, possible letter: Hi Valley, You will need to cover all the possible letter configurations in the formula. In this case, there are six: G H F G F H H G F H F G F G H F H G A nested IF statement would cover this, but an easier way would be to use the...

Excel: compare data, comparison feature, microsoft site

comparison feature, microsoft site, excel sheet: Emam, Excel really should have some sort of built-in method for comparing data. Unfortunately, it does not. Here is an article I found on the Microsoft site that talks about a couple of workarounds for this problem: http://support.microsoft.com/?kbid=139882...

Excel: compare differences in values in two workbooks, john walkenbach, dummy copy

john walkenbach, dummy copy, power utility pak: Kelly, my first thought was to send you to John Walkenbach s web site. He has a power utility pak that you can download that has an auditing feature that will compare 2 ranges or 2 worksheets (j-walk.com). It displays a list of all of the differences between...

Excel: compare excel and text files, text import wizard, excel sheet

text import wizard, excel sheet, macro recorder: To start out, turn on the macro recorder and open the textfile in Excel. You should get the Text Import Wizard - Select fixed width and then go through the dialogs. When done, turn off the macro recoder and look at the recorded code. This will record...

Excel: compare first five words in columns, many different ways, first approach

many different ways, first approach, book titles: I doubt you will find a macro online since it, as well as most macros, is SO specific to accomplish a specifc set of tasks. There are many many different ways to accomplish this but the first approach that comes to mind that a macro would do is perhaps...

Excel: compare two lists for duplicate numbers, vlookup, pivot tables

vlookup, pivot tables, type numbers: Hi Greg How are you? I guess that there is simpler solutions but I suggest the following 1. Assume your numbers are in A2:A11 2. Type in B2 =IF(ISERROR(VLOOKUP(A2;A3:$A$11;1))=TRUE; unique ; duplicate ) 3. Copy to A3:A11 or 0. Type numbers in...

Excel: How to compare multiple excel files..., open dialogs, getopenfilename

open dialogs, getopenfilename, wb1: I don t hate you but I m leaving for vacation (Aruba) and simply have no time to do this. Why not use this twice, first comparing workbooks A,B,C then comparing B,C,D and you ll preetty much have the results you want...? I won t be back until 7/10, and this...

Excel: compare text cells and if different insert row between them, text cells, bee hive

text cells, bee hive, blank row: Please note: I rarely write macros for people on this site who aske me to, have a situation in which one needs to be written, or in some cases, seem, to expect me to do so. If I did then that is all I would be doing in my free time. When I do write...

Excel: comparing 2 Excel spreadsheets to find missing records in one, robert fredrick, excel spreadsheets

robert fredrick, excel spreadsheets, mary alice: Hi Lori, You can use the MATCH function to cross reference the unique ID numbers and tag the ones that aren t found. (I ve used a technique very similar to this when balancing checks and accounts.) The formula (on Sheet B, in the next adjacent column...

Excel: comparing 2 fields, jayme, combination of the two

jayme, combination of the two, vba: Jayme - If you are asking to see if the two cells are identical, that is a simple IF statement: IF(test,if true do this, if false do this) IF(A1=B1,,C1= error statement ) However, if you re looking for the cells to not be off by one character, but...

Excel: comparing two columns of number, vlookup formula, index lookup

vlookup formula, index lookup, matching numbers: Laura: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/findmatches.xls You will want to use a VLOOKUP formula to test each item to see if it exists in the other column. Please let me know if you have any questions....

Excel: comparing columns and VLOOKUPS, relative reference, task completion

relative reference, task completion, column data: In this case, VLOOKUP is a good option. In fact, it doesn t compare two sets of data, but just one element against a set of data, so the different number of elements shouldn t be a problem. The problem may be that some of the values will return an error...

Excel: comparing columns, false values, cell a1

false values, cell a1, account names: You can use the equal sign as an operator, and it will evaluate if two values are the same, returning TRUE or FALSE. For example: =Sheet1!A1=Sheet2!A1 This will be TRUE if the cell A1 is the same on Sheet1 and Sheet2, and FALSE otherwise. You can drag...

Excel: comparing and copying data, sheet1, excel 2002

sheet1, excel 2002, today is the day: Bob, on Sheet1, you should be able to use this formula: =VLOOKUP(A2,Sheet2!A:B,2,FALSE) which will return the note from a matching entry in column A. You will want to drag that formula down column B for all entries. This assumes that the list (Sheet2) is...

Excel: comparing data culumns, excel 2002, dollar value

excel 2002, dollar value, address line: Orlando, Yes, there should be a more efficient way to do this, but I cannot make a reasonable suggestion without more detail or seeing the file. If you like, send the file and a more detailed description of your objective to me at xeonflux remove @comcast.net....

Excel: comparing data fields, column c, note column

column c, note column, c records: Lsmail: I have uploaded a file for your review: http://home.swbell.net/nate-sus/excel/fileforlsmail.xls NOTE - I got a false for AR0021R This may or may not be the same or similar answer I provided on the other question - but it is probably equally...

Excel: comparing data from two files and making changes, branch numbers, vlookup

branch numbers, vlookup, number column: VLOOKUP is indeed exactly what you need, Jeanne. Let me show you how to set it up I ll call your files OldData.xls (the larger one) and NewData.xls (the smaller one). Let s say that each one has data on a sheet called Sheet1. In the second file, I m...

Excel: comparing data in two worksheet, mouse pointer, duplicat

mouse pointer, duplicat, select sum: Hi Muhammad, Yes you can compare both lists if they are have the same range and cells. You didn t mention how you want to compare the two lists. In Excel if select a third sheet and then go to the Excel menu and select Data then Consolidate... you...

Excel: comparing a range and returning a field dependant on the row the value is found., proper references, sheet1

proper references, sheet1, a25: from the B4 perspective =Countif(Sheet2!$D$4:$D$25,B4) 0 or if you want Yes/no =if(Countif(Sheet2!$D$4:$D$25,B4) 0, yes , no ) If you want the answer to appear in A10, then you have to write the formula from the sheet2 perspective. On Sheet2,...

Excel: comparing values from 2 cells, cell references, vlookup

cell references, vlookup, column c: You can use the INDEX and MATCH formulas, in this case you can use the following formula in column D: =IF(ISNA(MATCH(B1,A:A,0)), ,INDEX(C:C,MATCH(B1,A:A,0))) The formula will return blank if the number from column B (cell B1 in this case) is not present...

Excel: comparing values, vlookup function, a3000

vlookup function, a3000, relative location: Arun, couple of questions. 1. is the values being looked up in a particular column? 2. If yes, do you need return value to be a Y/N or a particular column? - If so, use the VLOOKUP function. If you need the relative location, use the MATCH function ...

Excel: comparing two worksheet, cell a1, drag box

cell a1, drag box, sheet names: Hi Muhammad, There are many ways to do this, but I think this is probably the easiest. Simply enter the following formula in Sheet3 cell A1: =IF(Sheet1!A4 = Sheet2!A4,Sheet1!A1,Sheet1!A1 & = & Sheet2!A1) If your first sheet is not named Sheet1...

Excel: comparison of databases, whitch, matrixes

whitch, matrixes, final result: Zoran, that s not something I can do in this forum. However, you can go to John Walkenbachs site and download a trial version of his utility pack. You will find lots of tools, including one to compare ranges, in that set of utilities. You can use it for 30...

Excel: compiling 2 sheets info into 1, pivot table, left hand side

pivot table, left hand side, sheet1: Michelle: This can be partially done through the use of a Pivot Table; the rest will be by formulas. I have uploaded a spreadsheet here for you to refer to when following the below steps: http://home.swbell.net/nate-sus/excel/michelle.xls 1)...

Excel: compiling 2 sheets info into 1, array formula, vlookup function

array formula, vlookup function, blank sheet: I seem to remember answering variants of this question before - IF this is a one off job, I would be inclined to use copy and paste to get the column A (and B for sheet 1) information - with the data you have given, I would copy from sheet 1 first, and then...

Excel: complex averaging, cell a1, d10

cell a1, d10, d16: Maria: Presuming your formula is located in cell A1, you could use this formula: =AVERAGE(OFFSET($D$3,(ROW()-1)*7,0,7,1)) You could then simply copy this formula down and it will properly select ranges 7 cells apart. If your formula was in cell...

Excel: How do I make a complex formula in Excel?, format cells, cell reference

format cells, cell reference, a3: G day Anna To do this the easiest option would be to use an If formula which will check to see if you have two or more days and calculate the difference. Here is a formula for you to look at. You can place the formula wherever you want. =IF(A4-A3 =2,A4-A3,(A4-A3-2)*-1)...

Excel: A complex macro, max value, nada

max value, nada, adelaide: Hi Anna How are you? I can only solve your first quetion. I am sorry but it would take longer than I can afford at the moment. It does not matter if you have #Value or letters or numbers. The following macro computes the maximum and the minimum number...

Excel: computer shut down, data not saved, douglas m smith, recovery option

douglas m smith, recovery option, windows operating systems: Hi, That s strange it didn t give you a recovery option after startup. There is no way to retrieve it as far as I know. However, I d recommend you ask this question to other Excel experts and also to Windows operating systems experts. They may know of...

Excel: computer shut down, data not saved, tmp file, file search

tmp file, file search, harddrive: Vaibhav Usually there is a backup that automatically opens when you restart the pc, however sometimes it is shut off, you can look for a tmp file and see if it did timed backups to a tmp file, search the entire harddrive for a tmp file that has information...

Excel: computer shut down, data not saved, unsaved data, brainbench

unsaved data, brainbench, backup copies: Hi Vaibhav, I m sorry to say that any unsaved data is probably lost. Some versions of Excel do not save backup copies. If you were not prompted to open or save the file after reopening Excel, it most likely doesn t exist. Chad Welch Brainbench MVP for...

Excel: computers, reply thanks, formulas in excel

reply thanks, formulas in excel, excel function: Keshave It would not be practical for me list all of the formulas in excel because there are hundreds of them. the best way for you to learn or find a formula is to check the excel help and ask help for the specific formula type that you want. Richar...

Excel: computing grades, cris cris, format cells

cris cris, format cells, cell b3: Cris Not sure exactly what you are needing, but to compute a percentage in excel you use a formula that is similar to what you would normally use. To get the percentage that 70 is of 80: In excel the formula would look like this +70/80. If you want...

Excel: concantanating two strings, format cells, digit part number

format cells, digit part number, 4 digits: Harry, Sorry for the delay, I have been a tad busy. This code should do what you need Sub ProcessData() Dim iLastRow As Long Dim i As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, A ).End(xlUp).Row For i =...

Excel: concantenate function, cell a1, hi mike

cell a1, hi mike, zeros: Hi Mike, If your number 0005456456 is in cell A1 and Column B has 000 all the way then this formula would work. =A1*1000 then format the number by going to the Excel menu and selecting Format then Cells... then the Number tab then select Custom...

Excel: concatenate numbers without rounding, format cells, lucian

format cells, lucian, digits: Hello, It s not very clear for me why do you use concatenate for numbers. So, if you have 1.234 on a column and 2.345 on the other column you want to obtain : 1.2342.345? Am I right? If you want to sum them then you should use format cells, point tu numbers...

Excel: IF And condition, countif, condition c

countif, condition c, yx: Hi YX How are you? In Excel 2007 this will simpler because it offers a function that counts ranges that satisfy multi criteria. With 2003 you can try the following 1. Type in F2 =TEXT(C2; 0 )&TRIM(D2)&TEXT(E2; 0 ) 2.Copy F2 to F3:F7 3. Write the...

Excel: putting more than one condition in one cell, cell a2, conditional formatting

cell a2, conditional formatting, formula1: Suppose your categories are in a range of cells, say range A1:A7. Then you could try using a structure like this to get various formulas depending on the input in cell B1: =CHOOSE(MATCH(B1,A1:A7,0),FORMULA1,FORMULA2,FORMULA3,FORMULA4,FORMULA5,FORMULA6,FORMULA7)...

Excel: condition formating, conditional formatting, format button

conditional formatting, format button, version ms: Sorry this took awhile - busy... Select all of column A, use conditional formatting, change Cell Value is to Formula Is, enter this formula: =AND(A1 ,MAX(OFFSET(C1,0,0,MATCH(TRUE,A2:A1000 ,0),1)) 20) click Format button & select red pattern (or font)....

Excel: More than one condition in a formula, cell a2, conditional formatting

cell a2, conditional formatting, true hope: This formula should do what you expect: OR(AND(L2= SECURED ,COUNTA(E2:J2) 6),AND(L2= LOST ORDER ,COUNTA(E2:H2,K2) 5),AND(L2= In Progress ,COUNTA(E2:G2) 3)) I have changed the OR formulas to AND, so the two conditions must be met at the same time to return...

Excel: If condition, pout, lq

pout, lq, sucha: Amachado It is a little difficult to do what you want with one formula so I typically use several and pout them in a part of the worksheet that is invisible to the user. First you need to identify the different coneditions that may exist. This formula...

Excel: conditional combo boxes or list boxes, combo boxes, control toolbox

combo boxes, control toolbox, combobox: I understand that you made up an example to help simplify the problem but you did not answer my question. Again, which way did you use put your combobox or listbox on a sheet? Did you use the Forms menu or the Control Toolbox? It makes a difference. It...

Excel: conditional combo boxes or list boxes, combo boxes, exact code

combo boxes, exact code, combo box: You haven t specified which form of combo box you are using, but I would SUGGEST that VBA should be used to set the value when the first combobox is updated - the exact code depends on the type of combobox, but in essence it would set the value of the linked...

Excel: conditional copy of cells in a row, conditional formatting, array formulas

conditional formatting, array formulas, logical test: simple answer - no - complicated answer - yes! As in, one cell can ONLY have a formula that affects one cell. Multiple cells can have multiple formulas, OR array formulas (for example, a formula that is entered into multiple cells at once) - BUT in all cases,...

Excel: conditional font formatting with VBA, empty cells, marc marc

empty cells, marc marc, colums: Marc, Assume the sheet with the staggered data is named DATA. I assume in the summary sheet you are using a formula like =if(Data!A1= ,Data1!B1,Data1!A1) then drag filling down the column. So the logical thing to do is use a similar formula with...

Excel: conditional formating, douglas m smith, due test

douglas m smith, due test, brainbench: Hi Rob, You can do date math just like numbers. If you subtract 30 from the TODAY() result, that should push up the warning date. Something like this as your conditional format formula: =A1 TODAY()-30 Let me know if I m off base. Douglas M. Smith...

Excel: conditional formatting, conditional formatting, cell b1

conditional formatting, cell b1, moshe: Moshe, you can use conditional formatting for this. Just set the conditional format for B1 as Formula Is and =A1= aaa (without the outside quotes). Any formula that can be evaluated as True False works. For more info, you can read the bottom part...

Excel: conditional formatting, format painter, e68

format painter, e68, question thanks: Do you mean TEST multiple cells, or APPLY this to multiple cells - if the first, then an AND statement =and(e67 =today()-30,E68 =today()-30) if the second, even easier - apply it to the one cell, then EITHER use format painter to copy the formatting...

Excel: conditional formatting, auto increment, conditional statement

auto increment, conditional statement, absolute reference: Hi Raz, You re using conditional formatting, correct? I think all you need to do is remove the absolute reference in front of the 4 and copy this format to all your rows. from =$d$4= complete to =$d4= complete If you are not using conditional...

Excel: conditional formatting, data validation, punch list

data validation, punch list, format button: You would select the cells that need to be formatted and just change the conditionst to change Cell Value is to Formula is in the first dropdown then the formula would be something like =$A2= Complete where A2 contains the dropdown and will have...

Excel: conditional formatting, target date, cell c3

target date, cell c3, value zero: It sounds to me like you ve got it backwards. Remember you ve got a date value comparing against a zero. Today s date 11-20-2004 is equal to the date value of 38310. 38310 = 0 is TRUE 38310 = 0 is FALSE So, yes. One situations works, one...

Excel: conditional formatting, conditional formatting, cell changes

conditional formatting, cell changes, thanks for your help: I would suggest creating THREE levels of formatting The first level is a formula =OR(ISBLANK(A1),ISTEXT(A1)) where A1 is the cell you are dealing with - the formatting of this should be set to white (which gives you the no colour option) The second...

Excel: IF(AND) in conditional formatting, conditional formatting, excel97

conditional formatting, excel97, sr2: You don t need the IF in the condition - lets say we are talking about Cells C2 and D2 being empty - then the condition would be =AND(isblank(c2),isblank(d2)) IF both C2 and D2 are blank, the condition is true, so the conditional formatting kicks in...

Excel: conditional formatting, conditional formatting, visual basic programming

conditional formatting, visual basic programming, e25: Hi Rodney, This needs Visual Basic Programming. Select the sheet where you want the conditional formatting. Then press Alt+F11 double-click the name of the sheet on the left-side of the Visual Basic Editor. Then on the right-side blank pane paste...

Excel: conditional formatting, vlookup function, gpa

vlookup function, gpa, two ways: No, Conditional Formatting does not and can not change the contents of a cell, it just alters the appearance of the cell. Among several of the ways to do this, two ways are to either write a rather long IF statement or use the VLOOKUP function. See HELP...

Excel: conditional formatting, vb solution, vlookup

vb solution, vlookup, gpa: You cannot use conditional FORMAT to change the VALUE of a cell - but you can use either a formula or visual basic - the change event of the worksheet would be used if you want a VB solution, for formulas (which could be easier) I would have something like...

Excel: conditional formatting, conditional formatting, format feature

conditional formatting, format feature, cells: SHAKEEL The conditional formatting depends on the result that is present in the cell. I.E. if you want the conditional format feature to activate when the cell has a 2 in it, then +A1+A2 will work if the total of the two cells equals 2. They can be...

Excel: conditional formatting cells, gpa, a1

gpa, a1, cells: This is not a conditional formatting issue -- CF will not put values in cells, it will make them another color or put a border on or make it bold, italic, etc. To get A in for a 91 grade you need a plain formula, something like: =IF(A1 =90, A ,IF(A1 =80,...

Excel: conditional formatting excel, conditional formatting excel, cell changes

conditional formatting excel, cell changes, reply: I m so sorry, Sarah. I don t program either. Me and programming do NOT mix and I know there s tons of people out there who will do it for me. Hence, I created www.vbaexpress.com. If I were you, I would join there and ask my question there. Once you do, send...

Excel: conditional formatting excluding blank cells, spreadsheet examples, array formula

spreadsheet examples, array formula, conditional formatting: Michael: Check out my spreadsheet examples here: http://home.swbell.net/nate-sus/excel/averageignore.xls I came up with two potential formulas for you. Method 1 is similar to what you are already doing...the only thing I m probably doing different...

Excel: conditional formatting with FORMULA, conditional formatting, cell d2

conditional formatting, cell d2, format button: I selected row 1, then tabbed to C1 (so row1 is still selected and C1 is the active cell. Conditional formatting formulas with relative references are relative to the active cell). Then I did format = conditional formatting. Change cell value is to formula...

Excel: conditional formatting with Formula..., cell d2, conditional formatting

cell d2, conditional formatting, intermediate calculations: Send me your email address, I will send a workbook -- prety sure you need intermediate calculations in other cells to use in the conditional format. I have an example using your data which works & will forward it to you. email me at bobumlas@yahoo.co...

Excel: conditional formatting, maths sums, cell b1

maths sums, cell b1, conditional formatting: Hi Phil, This can be accomplished with Conditional Formatting. Assume the result to be tested is in cell A1 and it s correct answer is 100. Select cell B1 and then select Conditional Formatting from the Format menu. In the dialog box, select Formula...

Excel: conditional formattng, cell a1, red background

cell a1, red background: It s not done with a formula, it s done with Conditional Formatting. Example: how to make row 1 have a red background if cell A1 is the word red Select row 1. Then from main menu select Format, Conditional Formatting, Formula is:, =($A$1= red ),...

Excel: conditional formula, master spreadsheet, hi adam

master spreadsheet, hi adam, friend greg: Hi, Adam, This took a little bit of research, but thanks to my friend Greg, we figured out a way to accomplish this. It involves creating another column that repeats the cell address of any zero values. If you want, you can hide this extra column from...

Excel: conditional formula, master spreadsheet, javascript void 0

master spreadsheet, javascript void 0, hi adam: Hi Adam, This went beyond my knowledge. So I went to www.mrexcel.com and got an answer from Aladin, he is the Guru of gurus for Excel. You can view his answer at. http://www.mrexcel.com/board2/viewtopic.php?t=104906#javascript:void(0); I took Aladin...

Excel: conditional formula, master spreadsheet, conditional formatting

master spreadsheet, conditional formatting, pivot table: I think what you want is going to be difficult to achieve as the formula would have to look at lots of rows, and could get somewhat confusing - however, I can think of two ways of giving you the result I think you want (1) use format conditional FORMATTING...

Excel: conditional formula, master spreadsheet, addresess

master spreadsheet, addresess, numerical value: I am sure one could be written but it seems to me that it would be very long and/or very complex, not that that is a deterrent. It seems to me that one that would give the desired results would not be practical. Assuming you would have 100 or so, of the...

Excel: conditional formula, master spreadsheet, array formula

master spreadsheet, array formula, down arrow: There isn t a built in function that will do it. You could use an array formula that could put the information in an array, but then you wouldn t be able to see anything but the first value in a single cell. You would need to array enter it in multiple cells,...

Excel: conditional formula, array formula, a310

array formula, a310, step 1: Gloria, I think in 2), you meant P310 -P392, the most robust formula is an array formula. You type in the below formula and instead of pressing Enter, you press Ctrl+Shift+Enter. Lets assume the results are in C209-C291. In C209, enter the formula...

Excel: conditional formula, parentheses, quotes

parentheses, quotes: You need a nested IF, Mark. This one should do the trick: =IF(C6=11, Revenue ,IF(C6=12, Sales ,IF(C6=13, Expense , ))) The two quotes before the closing parentheses mean that if C6 does not meet any of those criteria, the formula will return a blank....

Excel: conditional links from one worksheet to another, conditional link, quantity column

conditional link, quantity column, hand inventory: Allen There is an option using formula that will do this, but it depends on how many lines you want to use for the Order Quantity.. If you can send the file to richard.littlewing@gmail.com I will look at a few options and give you the (hopefully best) solution....

Excel: conditional picture showing, racking my brain, formating

racking my brain, formating, b5: Assume you already have the picture on the worksheet, but out of sight (like the top left of the picture is in EE1 or something). If you select the cells below the picture and use the camera tool (you may have to customize your toolbars to get it - view/toolbars/customize...

Excel: conditional statements and values, conditional statements, infinite number

conditional statements, infinite number, error message: As fas as I know, because one of the factors in the comparison is a text, Excel is performing an alphanumeric comparison. In this type of test, letters rank higher than numbers, so that is why you get that result. If you want to get an error, try doing something...

Excel: conditional totals, column c, value option

column c, value option, c range: I THINK I know what you want: Sub XTotal() Untested for each thing in Range( C4:C65536 ).specialcells(xlcelltypeconstants) if thing.value= Y Then if cells(thing.row,9).value= Y then range( C3 ).value=Range( C3 ).Value + cells(Thing.row,8).Value...

Excel: Two conditions in IF formula, utexas edu, scorecards

utexas edu, scorecards, tho: Hi, Nat! You don t want an IF statement, tho you CAN use one that has up to 7 NESTED (or 8 total) IF statements. Use VLOOKUP instead. See how to do grades here, about halfway down the page: http://www.utexas.edu/its/training/handouts/excelgrade/ ...

Excel: conditions with "IF", ab1, aa1

ab1, aa1, happy new year: John Try the following =IF(AND(S1= ingles ,AA1= x ),+M1*0.069)+IF(AND(S1= ingles ,AB1 =1),+M1*0.0961)+if(AND(S1= euskera ,AA1= x ),+M1*0.038)+IF(AND(S1= euskera ,AB1 =1),M1*0.0568) This formula will work for you, however it is possible for the conditions...

Excel: How to connect Textboxes with Option button, cell a1, sheet1

cell a1, sheet1, textboxes: Here is some code that does that, assuming Sheet1 and CommandButton1: Private Sub CommandButton1_Click() Dim str As String Dim r As Long str = Trim(TextBox1) & Trim(TextBox2) & Trim(TextBox3) r = Sheets( Sheet1 ).UsedRange.Rows.Count + 1 Sheets(...

Excel: consecutive days, months etc.., consecutive numbers, days of the week

consecutive numbers, days of the week, consecutive days: Hi Karl, How are you? there as=re several ways to do that. The easiest one is selecting Edit - Fill - Series Other is doing the follwing 1st Type a date in a cell (e.g. B6) 2nd Type in B7 =DATE(YEAR(B6),MONTH(B6),DAY(B6)+1) or =DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))...

Excel: consecutive numbers, consecutive rows, intermediate user

consecutive rows, intermediate user, sequential numbers: What you ve described is called autofilling. You can autofill rows and columns exactly the same. As for writing a formula, I m not sure exactly what you re asking for. Can you write back and be more specific as to what you d like the formula to do? (If...

Excel: consignment software?, consignment shop, software products

consignment shop, software products, thanks for your time: Cara I am sorry but I haven t used any consignment software products, I have looked to see if there is anything that has been created for Excel or Access but as of yet I haven t been able to find anything. If I do find anything I will get back to you....

Excel: consignment software?, google search results, web browser navigation

google search results, web browser navigation, google: Cara, No first hand knowledge, but take a look at this google search results. You can probably tailor it to focus more on your specific needs: http://www.google.com/search?hl=en&lr=&safe=off&q=consignment+store+software+free&btnG=Search Put in that...

Excel: consignment software?, precise idea, consignment business

precise idea, consignment business, consignment shop: Hi Cara How are you? I am sorry but you are too vague in what you need. Perhaps it would be better to have a look at the software similar businesses are using to get a more precise idea of what you need. ...I am sorry but I have a very general what a consignment...

Excel: consolidation/combining worksheets, how to use pivot tables, creating a table

how to use pivot tables, creating a table, autofilter: Don t put them all on different sheets. Instead, put them on one worksheet. You ll probably have to add a column for the Representative and fill that in. Once you have all of your reps in one big flat worksheet, you can use Excel s features properly. See:...

Excel: consolidation, sumif, pivot table

sumif, pivot table, blank document: From the description, it sounds as though the key is the ID - but it s not totally clear - for instance, is the list volatile, do both lists have the same number of records on them? My current thought process would be to use a SUMIF or LOOKUP function to...

Excel: Take contents from one cell to another, cell b2, cell a1

cell b2, cell a1, employee absence: It s possible, but it sounds quite involved for what you are trying to do - if you email me (aidan.heritage@virgin.net) I can let you have the system I designed for the Insurance company I work for here in the UK which handles all forms of absence, and has...

Excel: control alignment, control alignment, white arrow

control alignment, white arrow, arrow tool: No, there is no Snap to Grid because there is no grid on the worksheet. Yes, there a standard procedure or tip to align macro buttons from the forms toolbar. Should I just stop here or should I assume you also want to know how to do it and not just is...

Excel: control buttons in Excel, cell a1, control buttons

cell a1, control buttons, many thanks: Let s say the user is entering replanted or fallow in cell A1 of the original sheet. Private Sub CommandButton1_Click() If Range( a1 ) = replanted then Sheets( replant chart ).Select elseif Range( a1 ) = fallow planted then ...

Excel: control forms, option buttons, option button

option buttons, option button, activex controls: 1 - you can t check option buttons - you select them 2 - you want that when you click an option button another control (a DropDown) opens? or Appears? (I assume apires = appears ) 3 - use WHAT value? You haven t selected anything in the dropdown when...

Excel: control id, exit design, design mode

exit design, design mode, vba: Here you go: Sub EnterDesignMode() Application.CommandBars.FindControl(ID:=1605).Execute End Sub Sub ExitDesignMode() With CommandBars( Exit Design Mode ).Controls(1) If .State = msoButtonDown Then .Execute End With End Sub HTH, Gyul...

Excel: control textbox, variable text, location thanks

variable text, location thanks, text boxes: As far as I know, the only interactive way of moving the objects from the Control toolbar is going into edit mode. The objects from the Forms toolbar are different, and they can be selected as objects without entering the edit mode, by using the select objects...

Excel: control on worksheet, s a design, basic principle

s a design, basic principle, working solution: I cannot really expand on my answer - I ve told you what the problem is, confirmed it s a design thing, and given you a working solution - sorry if this doesn t resolve it! For other suggestions, you could try www.support.microsoft.com or www.wopr.com...

Excel: controlling a filter with a combo box, access query, combo box

access query, combo box, allexperts: Yes I can provided that the list is in order (if it isn t in order, it will be more complicated!) - it s still not easy to explain via the medium of AllExperts, but I have a spreadsheet I can send you as an example if you can let me have your email address...

Excel: controls within file, mscal, calendar control

mscal, calendar control, office calendar: if you are using the standard office calendar control (mscal.ocx) then there is no need to worry (the user will also have it). If it s a third party ocx then yes, they will need the ocx file (installed) and also the license (for you to distribute it). ...

Excel: conversion to base 12 or other solution, douglas m smith, array formula

douglas m smith, array formula, len function: If years were in column A and months in column B: =(SUM(A1:A6)*12+SUM(B1:B6))/COUNT(B1:B6)/12 This assumes you enter zeroes if there are no months, otherwise the COUNT function comes up short. (Note: This is a regular formula, not an array formula like...

Excel: conversion of days to dates, format cells, leap years

format cells, leap years, numeric number: Hi Joe, It seems as though you have dates in your spreadsheet that are in two different formats, unless the 1 in front of 10109614 is a typo? I am assuming it isn t for the solution I have for you. Please let me know if this is not the case and I will...

Excel: convert date, text column, csv

text column, csv, a3: The problem is that the date could be correct, or could be an error - still don t know how you are doing the import - see the original answer - IF you have any control over it I would go for this - at worst, you can import the column as a text column - if...

Excel: convert horizontal to vertical, vertical cells, horizontal cells

vertical cells, horizontal cells: Hi Kyle, Yes you would select the horizontal cells and then while hovering over the selection right-click with your mouse and select Copy then select the uppermost cell of where you would like to paste the selection vertically and right-click again, now...

Excel: convert letters to currency in Excel, code test, instr

code test, instr, rg: You need a user-defined function to do this. Press Alt/F11, then use Insert/Module, then enter this: Function Amount(Code As String, Rg As String) As Single n = InStr(Rg, . ) For i = 1 To Len(Rg) m = InStr(Code, Mid(Rg, i, 1)) ...

Excel: convert to mph( miles per hour), douglas m smith, cell a1

douglas m smith, cell a1, cell b1: Hi Mark, The trick to doing conversions is to lay out the interim steps and rates. In this case, you are going from minutes to hours and conversion rate is 60 minutes/hour. If the time is in cell A1 and distance is in cell B1, the formula would be:...

Excel: convert strange number to date, strange number, wrong person

strange number, wrong person: ...I see that you offered formula but it is notposted... You must have the wrong person because I did not do this. However to convert this strange number to a date then: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) assuming that the number is in A...

Excel: convert strange number to date, leap years, numeric number

leap years, numeric number, strange numbers: Use this formula assuming the date to fix is in A1 =IF(LEN(TRIM(A1))=6,DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)),IF(LEN(TRIM(A1))=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)), )) To add error checking into the mix then: =IF(ISERROR(IF(LEN(TRIM(A1))=6,DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)),IF(LEN(TRIM(A1))=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)),...

Excel: convert tabular data into xyz triples, with a user input popup form for variable data sets, excel macro programming, column labels

excel macro programming, column labels, column label: Hi Jen Variable numberd of rows or columns are no problem. I for one cant wriete efficiently if you jjust want the data to be anywhere on a sheet...the result would be unpredicatble. SO to be clear I can write code to transform your data and I will...

Excel: how to convert a text in to a date, question thanks, excel sheet

question thanks, excel sheet, a1: Assuming the date is in A1 then =IF(LEN(TRIM(A1))=5,DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),2,2),LEFT(TRIM(A1),1)),IF(LEN(TRIM(A1))=6,DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),3,2),LEFT(TRIM(A1),2)), )) Format the cell to the desired date format. Copy...

Excel: How can i convert the text 'million' to its number format, blank cell, exact values

blank cell, exact values, thanks in advance: Hi, Kevin! I would think that the easiest solution would be: Hit Ctrl+H Type a space and then the word million in the Find What box. Don t put anything in the Replace With box. Hit Replace All. Now put 1000000 into a blank cell. Copy the cell. Select...

Excel: convert text to number, csv file, ebay

csv file, ebay, sales history: In this case, you can use the replace feature from Excel. Go to menu Edit- Replace, on the Find what box type £ (no quotes), and the Replace with type £ (one space after). Then you can hit the button Replace all to add an space after all the £ signs...

Excel: convert times to minutes, programming skills, lesion

programming skills, lesion: Hi PAul I can almost certainly help with this,perhapes a little more info is required What column(s) are the start times in What column(s) are the end times in Where do want the difference between start and end time and do you want it expressed...

Excel: converting 7 digits to 10, target value, 7 digits

target value, 7 digits, policy numbers: You need event code. If it s always in column A, for example, this code will ensure 10 characters: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Len(Target.Value) 10 Then Target.Value...

Excel: converting birthdate to age ( but in years and months, followup question, reading level

followup question, reading level, yyyy: Diane, Please be more specific. How are the cells containing the students age formatted? How are the cells with the students reading level formatted? If you cannot post a followup question (I may be maxed out for the day), then send me an email at...

Excel: converting column of time, matter of honor, partial solutions

matter of honor, partial solutions, ricky: Hi Ricky, Sorry for the delay, but this is more complicated than I thought. I ve put in far more time than I normally would but it s now become a matter of honor. I ve got partial solutions, but none that cover all possibilities in one formula. Email...

Excel: converting data on mass, spreadsheet, a3

spreadsheet, a3, currency: Susan Are the numbers all the same format ie if the cell has only 170,000 it would show as .17? are they all in the same column. If the answer is yes to both, insert a new column to the right of the current one, type in =A3 (being the first cell which...

Excel: converting date formats, option buttons, date formats

option buttons, date formats, answer format: I now understand, I think, what your problem is - it s not the date but the year of the date, right? This is happening because EXCEL assumes , in an effort to help you, that if you do not supply a year when fooling with a date then the omitted year is...

Excel: converting large text data into excel, street address city, john dow

street address city, john dow, array name: Here is a simple macro to do the conversion for you. Public Sub ProcessData() Dim i As Long Dim iLastRow As Long Dim cell As Range Dim sh As Worksheet Application.ScreenUpdating = False With ActiveSheet iLastRow...

Excel: converting Mac to PC, mac spreadsheet, office excel

mac spreadsheet, office excel, ms office: Frank I haveen t had the experience of trying to open a Mac excel file, so I can t be much help. But I would first try the regular excel/toolbar/open command, some files will open even though they aren t regular pc excel files, unfortunately I don t believe...

Excel: converting and merging fields, douglas m smith, static results

douglas m smith, static results, time column: Hi Ricky, You can use text functions to chop up the date/time text. If you want the results as text, use these formulas for the start and end dates, respectively: =TEXT(A1, mm-dd-yyyy )& &LEFT(B1,SEARCH( - ,B1)-1) =TEXT(A1, mm-dd-yyyy )& &RIGHT(B1,LEN(B1)-SEARCH(...

Excel: converting negative numbers into positive, absolute value function, negative numbers

absolute value function, negative numbers, a3: After giving it some more thought, you could sum an entire column using this formla: =-SUMIF(A:A, 0 )+SUMIF(A:A, =0 ) It takes the sum of all of the negative numbers and positive numbers separately and then adds them together to where you get the absolulte...

Excel: converting to number format, format cells, format feature

format cells, format feature, blank cell: Here is my first idea: 1) Select the entire spreadsheet (or the items you want to reformat) and change the format to number (format- cells). 2) Do a search and replace (edit- replace). Replace all occurances of the @ with an equal sign = . This should...

Excel: converting text to line chart, chart wizard, time periods

chart wizard, time periods, text import: If you don t get whan you want by simply selecting the dates and the A & B products, then using the Chart Wizard & selecting Line, feel free to email me the Excel WB (after the text import) and I ll give you step by step instructions. Email: bobumlas@yahoo.co...

Excel: convertion of time to numbers, douglas m smith, convertion of time

douglas m smith, convertion of time, time duration: Hi James, Time values in Excel are really just decimal numbers. One day is 1, 12 hours is 0.5, six hours is 0.25, etc. So to convert a time decimal to a number equivalent, multiple it by 24: =IF(G6 F6, G6-F6, 1+G6-F6)*24 Douglas M. Smith Brainbench...

Excel: coping formulars, q copy, worksheet functions

q copy, worksheet functions, google: Mary It depends on what you want to do with the formula, this link will give you a few options, just copy and paste it all if it isn t automatically linked. http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse_thread/thread/c6833a49d202e04c/80b1ef2d07182201?lnk=st&q=copy+formulas+excel&rnum=4#80b1ef2d0718220...

Excel: coping formulas through a wookbook, cell a1, shift key

cell a1, shift key, kelli: Hi Kelli, If it is one formula per sheet you can simply select the cell you want the formula in let us say in Sheet31 cell A1 and then type the equal = sign and then select the cell A1 in Sheet30. You would then select Sheet30 and repeat the process. ...

Excel: coping a row in excel by searching one columns items, example spreadsheet, spreadsheet work

example spreadsheet, spreadsheet work, excel spreadsheet: Well, it d be an incredibly long formula to search ALL sheets, but to give you an idea of looking at TWO sheets, you can try: =IF($A2= , ,IF(ISNA(VLOOKUP($A2,Sheet1!$A$2:$E$100,2,FALSE)),VLOOKUP($A2,Sheet2!$A$2:$E$100,2,FALSE),VLOOKUP($A2,Sheet1!$A$2:$E$100,2,FALSE)))...

Excel: copy automatically fields from another sheet depending on user input [XLS 97], e g smith, helen miller

e g smith, helen miller, vlookup: Marcus The only way to do this is to use a vlookup, however that will run into problems as you are sure to have more than one Smith or Jones. What exactly is the reason for using the second sheet with the same names from the first sheet? If you could...

Excel: copy cell value onto another sheet if..., cell b2, cell b1

cell b2, cell b1, cell c1: If you want to do this without vba programming, and without sorting the data, here s one way. On sheet1, enter a zero in cell C1, and this formula in C2: =IF(AND(MONTH(B2)=MONTH(TODAY()),YEAR(B2)=YEAR(TODAY())),C1+1,C1) then copy it down C2:C20. Then...

Excel: If then copy cell, initial numbers, secondary numbers

initial numbers, secondary numbers, initial number: I assume you are entering the number in the same cell each time - so the results area is always the same. Since you want several things, it would be easier if I could see the workbook and set it up and explain it from there. twogilvy@msn.com -- ...

Excel: copy column if cell is shaded, colorindex, f3000

colorindex, f3000, excel 5: Hi Leon How are you? Please do the following 1. Tools - macro - visual basic editor 2. insert - module 3. copy Sub copying() Set ori = Sheets(1).Range( A4:G3000 ) Set dest = Sheets(5).Range( A4:G3000 ) Set teste = Sheets(1).Range( F7:F3000 ) i...

Excel: copy a combo box.value, application index, index range

application index, index range, combo box: I assume you know the source of the list in the combobox. Let s assume that s range R1:R30. Use something like this: Sub RunMacro() TheText=Application.Index(Range( R1:R30 ),ActiveSheet.Dropdowns(1).Value) now TheText contains the value in the combobox...

Excel: copy data vba, roy cox, sheet1

roy cox, sheet1, pivottable: I can never really see the point in duplicating data on separate sheets. Why do you find it necessary? Have you considered a PivotTable? I think you could maybe use a variation of the DatabaseForm example that I have posted at www.excel-it.com, use the...

Excel: copy from drawing textbox to cell truncates at 255 chars, rowheight, screen application

rowheight, screen application, autofit: Hi Al, Yes, as you have observed, assigning text as in your statement r.Value = b.Text will only assign the first 255 characters from the Text Box. To assign all the characters in the Text Box you must use the Characters property of the Text Box...

Excel: copy from drawing toolbox to cell - truncates at 255 chars, rowheight, screen application

rowheight, screen application, autofit: Hi, this is a limitation of Excel when using VBA. The texbox can hold 2000 characters. Luckily there is the workaround using the Character method and copy in 250-character chunks. See sample code below: ********** code starts here **********...

Excel: copy from drawing toolbox to cell - truncates at 255 chars, rowheight, screen application

rowheight, screen application, autofit: Hello Al, Sorry for the delay, but All experts seemed to be down earlier. At least for me. Anyway, the method to overcome this is to use the Characters method. This is documented at this link with sample code: http://support.microsoft.com/kb/148815/en-us...

Excel: copy a excel sheet, excel sheet, book2

excel sheet, book2, sheet1: Use following macro instead of earlier one- Sub NewMacro() myWorkbook = ActiveWorkbook.Name On Error GoTo 99 User message to open the source file myOK = MsgBox( Please open the file containing Sheet to be copied..... , vbOKOnly, Open Source File...

Excel: How to copy info from cells to its comment, cells

cells: You can use a macro like this: Sub FillComments() On Error Resume Next For Each cell In Selection.Cells cell.AddComment cell.Comment.Text Text:=cell.Value Next End Sub This will add a comment and fill it with the cell value...

Excel: How to copy information from one spreadsheet to another using a macro, latter case, spreadsheets

latter case, spreadsheets, missing something: I could use a little more information: Do the sheets in Site Questionnaire already have the same names as the empty sheets in the Package? For example, Site Questionnaire has SheetA SheetB SheetX HermanSheet xyzSheet each of which has mucho data and...

Excel: copy information, using microsoft office, microsoft office 2003

using microsoft office, microsoft office 2003, excell: Bill, Try the OFFSET command in combination with the END(xlUp) command. For example, the command below will copy the contents of TextBox1 to the last row on column A. xf Range( A65536 ).End(xlUp).Offset(1,0).Value = TextBox1.Value Note: .Value...

Excel: How to copy/merge data from multiple columns into one column, insert sql, database instance

insert sql, database instance, application developers: If you can identify the sheets and columns you want to gather into one column (and where is this one to be gathered?), I can write you a macro to do it. Better yet, answer the above q s and email me the workbook (or a sample) and I ll send it back with the...

Excel: copy & paste special, paste option, link options

paste option, link options, copy paste: Jonathan I would guess the issue is that you are selecting a chart or some other object, not cells, when this occurs. If you click a chart or other object, then copy, you will only have the option to paste it as a form of picture. You need to instead select...

Excel: copy paste, cell a1, conventional method

cell a1, conventional method, select tools: Hey William, You can do this with a macro. Here are the steps you would follow. I don t know if you have any experience with macros, so I will make detailed steps: 1 - From the Tools menu, select Macro, and then Visual Basic Editor (this will open...

Excel: copy and pasting in macros, run time error, run time error 1004

run time error, run time error 1004, control toolbox: Phillip, Is the button a commandbutton from the Control Toolbox toolbar? If you put the recorded code in a sheet module, inside the click event of the commandbutton, then that could be the problem. In that case, when the macro does Worksheets( ABC ).Select...

Excel: copy perticular data from html files to excel, scripting runtime, cell position

scripting runtime, cell position, paste data: If you have 25 files in one folder, and you want your code to open each one in turn to copy/paste data, the basic structure is this (I m using in this sample as if the files are in folder C: emp but of course you change this to whatever folder you need):...

Excel: copy query, wool types, macro code

wool types, macro code, number 0: In order to write this code, I d need to know where these non-zero values are. So, without that, I m going to assume that the description is in column A and anywhere there s a number in column B I will copy A&B to a new sheet. This should do it: Sub Gather()...

Excel: copy query, wool types, macro code

wool types, macro code, users input: Well, since you are new to coding then ...what macro code... I suggest will not mean much to you. And, again, because you are new to coding, then it will be easier for me to just write the code instead of giving you some direction that you may have a hard...

Excel: How do I copy a range of cells on the active row to the next empty row on a new sheet?, copy worksheets, b cells

copy worksheets, b cells, sheet application: Hello Mike: Sub CopyValuesOnly() specify tab name of destination sheet as a constant Const s As String = Sheet2 Dim sh As Worksheet, sh1 As Worksheet Set sh = ActiveSheet Set sh1 = Worksheets(s) With sh .Range(.Cells(ActiveCell.Row, B ), _...

Excel: copy ranges of many workbooks to one sheet, copy ranges, yyyy

copy ranges, yyyy, ogilvy: there is no worksheet.format. Just use format ActiveCell.Value = Format(1 & / & Mid(ActiveCell, 5, 2) & / & 2007, mmm d, yyyy ) End If I wrote code based on your description of the problem. If it doesn t work, there is no way I can write new...

Excel: copy a row from sheet1 then paste it to sheet2 then sort it by date, filter method, time to go home

filter method, time to go home, time data: The code you have in your text above is NOT the version I gave you - I very rarely use copy - my code was Sub mover() Dim n Dim inputcounter As Long, outputcounter As Long n = Range( a1 ).Value While Len(n) 0 Sheets( sheet2 ).Range( 1:1 ).Offset(outputcounter,...

Excel: copy set of 2 sheets, shift button, sheet1

shift button, sheet1, dropdown list: Hi Vishwanathan, I am not sure what you are asking? But you can select sheet 1 and hold down the Shift button and select sheet2 and the right-click the sheetname Sheet1 and select Move or Copy then check the checkbox for Create a copy then select...

Excel: copy sheet to sheet, irow, count column

irow, count column, macro code: Hi Wayne, As near as I can tell from your instructions, this code will do what you want. If it doesn t, I suggest you email me a sample of the data and what the result should look like. My email address is VBAexpert@myway.com Here s my macro code:...

Excel: how to copy singel cell to merged cell, c cells, target

c cells, target, f31: I would advise NOT to merge the cell. Instead (only works if you re showing data centered) you could center the data across multiple cells: - Select the cells accross which you want to center - hit control-1 - click the alignment box - In the horizontal...

Excel: how to copy single cell to a merged cell using vba, c cells, target

c cells, target, f31: In place of: Union(.Cells(sRow, a ), .Cells(sRow, c ), .Cells(sRow, d ), .Cells(sRow, e ), .Cells(sRow, f )).Copy Range(Cells(dRow, c ), Cells(dRow, n )).PasteSpecial xlPasteValues Use: .cells(drow,3).value=.cells(srow, a ).Value .cells(drow,5).value=.cells(srow,...

Excel: copy specific cell to all sheets, sheet1, a25

sheet1, a25, excel workbook: Hey haplo, You can definitely change the same cell in all sheets. Just follow these steps: 1 - Right click on Sheet1. 2 - Click on Select All Sheets . 3 - Go to the cell that you want changed and make your change. 4 - Right click on Sheet1 and...

Excel: copy value instead of link, excel vba, ogilvy

excel vba, ogilvy: Sub CopyFirstSheet() this copies 1st sheet of activeworkbook to a new workbook ActiveWorkbook.Worksheets(1).Copy new workbook is now the activeworkbook remove all formulas with Activesheet .copy .pastespecial xlValues End With Application.DisplayAlerts...

Excel: copy value instead of link, false selection, aidan

false selection, aidan: This can pretty much be recorded, as the EASIEST way to do it is to paste special values, then paste special formats Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlFormats,...

Excel: copy value instead of reference link, excel vba, cells

excel vba, cells: Dan, The only way I know of to remove the reference links after you copy a worksheet into a new workbook is to disable the links on an individual cell basis after the paste. If cell A1 had a link in the original file, this routine would break it (using...

Excel: copy values from one sheet (by columns) to another sheet (by rows), blank row, button option

blank row, button option, vba macro: Try this code in a normal module, attached to your save button: Option Explicit Sub CopyTranspose() ActiveSheet.Range( A1 ).CurrentRegion.Copy With Worksheets( Sheet2 ) .Range( A65536 ).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll,...

Excel: copy a worksheet if certain criteria are met, target value, target address

target value, target address, sheet tab: Right-click the sheet tab of the Total Quote Sheet and paste in the code with this change: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address $C$3 Then Exit Sub If Not IsNumeric(Target.Value) Then Exit Sub If Target.Value...

Excel: copying, vlookup formula, exact formula

vlookup formula, exact formula, excel help: Sounds like a perfect use of the VLOOKUP formula. With this formula you will be able to look up the ID number and bring over the information in the columns adjacent to the matching ID nuber. I can t give you the exact formula you need without all of...

Excel: copying cells from one sheet to another that are not in the same row, sheet1, a15

sheet1, a15, trying to find a way: in Sheet2, enter this in A1: Sheet1!A1 (NO = , just the text!) Now select A1:A7 Use the fill handle to fill down as far as you want. You ll see this in A8: Sheet1!A2 and this in A15: Sheet1!A3 etc. Now, select Column A, and use edit/replace, replacing...

Excel: copying column without formula, mouse pointer, cells

mouse pointer, cells: Hi June, Yes you can do this. Select the cell or cells you want to copy in column 3, hover your mouse pointer over the selection and right-click with your mouse and then select Copy , now right-click with your mouse on the uppermost cell of where...

Excel: copying columns of data into another sheet, office 2000, xls

office 2000, xls, b2: Hi Matt, Not sure exactly what you need. But obviously you can select Sheet 2 and in column M type in the formula =Sheet1!B2 then copy the formula down. This would automatically be the same values as Sheet1. If you want to use Macros I have created...

Excel: copying data to other spreadsheets, h50, spreadsheet

h50, spreadsheet, spreadsheets: Bob, The code below will do almost what you are asking. However, it doesn t create a new sheet for each Center, it just places the different Centers on their own sheet within the current workbook. Here s that code: Sub MoveSimilarRows() ASheet...

Excel: copying excel content, excel workbooks, three sheets

excel workbooks, three sheets, array: Hi Satyam Here is is some rough code which will do what you have asked assuming books 1,2 and 3 are open. Iput the code in book 1. I have no idead what QTP is so I dont know if the code will run on it . You will have to make an module to house the...

Excel: copying in excel, sheet tab, cells

sheet tab, cells: As long as you want to add information to the same cells in all the sheets, go to the first sheet, then--holding your CTRL key, click on all the other sheets you want (OR you can right-click a sheet tab and choose Select ALL sheets). Make your change to the...

Excel: copying field from 1 column into many rows in another column, ogilvy, 3rd floor

ogilvy, 3rd floor: See may answer to your first asking of this question for a formula. I forgot to add, if you want to then eliminate the formula and have the actual words 1st Floor in the second column, then after you do the formula and fill it down, select the second column...

Excel: copying field from 1 column into many rows in another column, cell b2, cell a2

cell b2, cell a2, blank cell: Assume you have selected the first cell in column 2 where you want the entries made. In the adjacent cell in column 1 is the entry 1st Floor Sub Enterfloor() Dim rng as Range, cell as Range Dim sFloor as String set rng = activecell.offset(0,-1) ...

Excel: copying formulas between workbooks, tabs, formula bar

tabs, formula bar: I m going to assume the worksheet names are the same in both workbooks. Select the cell with the formula you want to copy. Highlight the formula in the formula bar at the top of the screen. Ctrl+C to copy Press Esc Select the cell in the new workbook...

Excel: NOT copying hidden columns, hidden columns, visible areas

hidden columns, visible areas, hidden information: The only ways I know of doing this are (1) select ONLY the visible areas, as blocks - so use the ctrl key to keep selecting - eg if column C is hidden, select A:B, then ctrl and select D etc. (2) via VBA you could specify the areas to be copied and this...

Excel: copying hyperlinked cells to different workbook/cell, cell a1, apologies in advance

cell a1, apologies in advance, hyperlinked text: Not 100% sure you mean hyperlink (which is a clickable link that would take you to A1 on sheet1), also not sure if you want to put the value or a formula - if the VALUE then edit copy, then edit, paste special and choose values - this will lock the value in...

Excel: copying page setup, margins, orientation

margins, orientation: The basic macro you will need is Sub Macro12() Macro12 Macro Macro recorded 21/11/2006 by heritaa For Each sh In ActiveWorkbook.Sheets With sh.PageSetup .PrintTitleRows = $1:$1 .PrintTitleColumns = ...

Excel: copying pages in excel wrkbk, exact copy, excel 2002

exact copy, excel 2002, imho: Page breaks can t get copied and pasted unless you put them there manually and even then I think they don t get pasted. Page breaks are put there by EXCEL when you print are there just temporarily. Though it is too late now, the better way, IMHO, to copy...

Excel: copying unique records, sheet1, unique records

sheet1, unique records, date1: Bob, the following code should make that happen for you. Sub ReplaceDupsOnSheet2() Sheets(1).Select Range( A2 ).Select Do Until ActiveCell.Value = FindOrd = ActiveCell.Value ActiveCell.EntireRow.Copy Sheets(2).Select On Error Resume Next...

Excel: copying, blue skyes, colored word

blue skyes, colored word, format cells: Hi, Skye, what a creative question. The answer is yes, there s a way to make a particular word turn blue, but it s not exactly the way you re describing. Excel has a wonderful feature called Conditional Formatting. Using this feature, the cell contents...

Excel: This one should be corect, random characters, email

random characters, email, yahoo: Sorry -- I see some random characters in ths formula which simply don t make sense -- there s a 97) near the beginning, a 22)) about 50 characters in, a 96 122 , etc. Maybe you can send it to me in private email - bobumlas@yahoo.com, and remind me what...

Excel: count, mouse button down, left mouse button

mouse button down, left mouse button, header rows: Hi Magda, You can use PivotTable. Select all data including Header rows Name and Status then go to the Excel menu and select Data then PivotTable and PivotChart Report... then click Finish then holding the left mouse button down drag from...

Excel: How to count if data matches 2 criterias, countif, pivot table

countif, pivot table, logical argument: You can nest a series of IF statements together and return a different result for each set of criteria. Example: =IF(AND(A1=1,B1= a ),1,IF(AND(A1=1,B1= b ),2,IF(AND(A1=2,B1= a ),3,IF(AND(A1=3,B1= b ),4, )))) Then you could either create a Pivot Table...

Excel: How do I count data validation boxes, countif function, data validation

countif function, data validation, drop down boxes: countif should work fine, although you will need one countif for each possible situation - a better solution would probably be to use the data menu, then pivot table - this would allow you to get a count of all entries. If you still have problems and can...

Excel: Getting a count then filtering that count, array type, countif

array type, countif, sev: The general approach is to use an array type formula. Using sumproduct will do that without having to Array enter this formula to get the proportion or break up the two parts to get the separate counts. =sumproduct(--($B$2:$B$200= Severity 1 ),--($F$2:$F$200=...

Excel: count if function, countif, worksheet functions

countif, worksheet functions, column c: Hi Sofia, Unfortunately, Excel s COUNTIF can only handle one criterion. What I usually do in cases where I want to handle more than one criterion is to use an available extra column to evaluate all the criteria, then use COUNTIF to do the count. For example,...

Excel: count of multiple criteria, array formula, a100

array formula, a100, numeric values: 1 - to count students between 10 & 14: =SUMPRODUCT((A1:A100 =10)*(A1:A100 =14)*(B1:B100= student )) -- adjust ranges accordingly 2 - Assuming you have numeric values in A1:A100 and you want to place them into ranges of #between 0-50,51-100,101-150,etc...

Excel: count question, quick responce, vba code

quick responce, vba code, blank cells: This routine will count all cells that are blank and non-colored in range B1:B20 and report that number in cell D4. The downside to this approach is that cell D4 will not update automatically should the range change. You will have to re-run the routine each...

Excel: count repeated items, animal crackers, cell animal

animal crackers, cell animal, copy option: Hi Sam! How are you? Please try the following 1. TOOLS - MACRO - VISUAL BASIC EDITOR 2. INSERT - MODULE 3. COPY Option Base 1 Sub counting() Dim d As Range, dest As Range, CONTA() Set d = Range( A1:A17 ) alter according toyour source range Set dest...

Excel: count-down within a set range, quick responce, ogilvy

quick responce, ogilvy, d12: Joseph, I gave you a formula that did what I understood you to describe. However, based on what you show, you and I apparently were not communicating successfully. Put this in D12 and drag down. =SUMIF(INDIRECT( D &TRUNC((ROW()-2)/10)*10+2& :D &ROW()),...

Excel: count unique entries based on criteria, div 5, solution data

div 5, solution data, 1n: I think I would be inclined to use a pivot table for this as the easiest solution (data menu, pivot table and chart reports) - this would bring in the necessary information. I cannot think of a simpler way of doing this (although there may well be many alternatives!...

Excel: count weekdays, correct weekday, array formula

correct weekday, array formula, dddd: In this case, you can use the TEXT function, and SUM as an array formula. Supposing that your dates are in the range A1:A20, you can enter the following array formula: =SUM(--(TEXT(A1:A20, dddd )= Friday )) Enter it with Crtl+Shift+Enter, and it will return...

Excel: count, microsoft excel 2002, color pallette

microsoft excel 2002, color pallette, cell values: You can write a VBA function to do this. Press Alt/F8, use Insert Module, enter this: Function CountPink(Rg As Range) Application.Volatile n = 0 For Each rg2 In Rg If rg2.Interior.ColorIndex = 7 Then n = n + 1 Next CountPink...

Excel: =counta and filters, counta, varp

counta, varp, numeric value: The first parameter of the subtotal function tells Excel which statistic you want. Here are the numbers: 1 AVERAGE 2 COUNT 3 COUNTA 4 MAX 5 MIN 6 PRODUCT 7 STDEV 8 STDEVP 9 SUM 10 VAR 11 VARP Therefore, Subtotal(3,range:range) will...

Excel: countif, playing second fiddle, camp 1

playing second fiddle, camp 1, b100: Ah, hah, so I m playing second fiddle, eh? :-) This calls for a function called SUMPRODUCT, Glenn. Don t be thrown by its name--it s perfect for this type of situation. Let s say that the camps are in A2:A100, and the genders are in B2:B100. Here is...

Excel: countif, midnight obsession, countif

midnight obsession, countif, honest statement: Pete, Apparently you are offended by my use of the word gibberish. The meaning is unintelligible writing . I don t see how that is offensive if I am communicating to you that I don t understand what you want me to do. I said it appeard as gibberish to me...

Excel: countif, sumproduct, f200

sumproduct, f200, neat trick: use a sumproduct for this =sumproduct(--(ProgressTracker7!F4:F200=1),--(left(ProgressTracker7!R4:R200,1)= 9 )) This is a neat trick which forces the SUMPRODUCT to evaluate TRUE as 1 and FALSE as zero - so only cases where the two situations match will...

Excel: countif, data cells, cross hair

data cells, cross hair, b8: I m afraid I don t understand the phrase ...just selecting the date and hour columns instead of selecting the data cells.. I THINK you re doing what you need to do -- until I understand better, I don t see any easier way to do what you want -- you re getting...

Excel: countif (and, countif, answer mark

countif, answer mark, mark thanks: Mark, thanks for making it clear as, well, mud. Kidding! If you just want a formula that can count the number of times 5 appears in the range A1:B10, then you just need this: =COUNTIF(A2:B10,5) If you have different ranges that need to be specified,...

Excel: countif(and, open parenthesis, excel formula

open parenthesis, excel formula, b22: Hey Mark, Adding an open parenthesis like this will fix the formula, but I don t know if it is producing the result you are looking for: COUNTIF(A1,5*(AND(B1:B22,5))) If you want, you can give me some information on how your data is set up and what...

Excel: countif, array formula, visual basic programming

array formula, visual basic programming, array formulas: Hi Luke, For your first question I could not find much to simplify there unless we use Visual Basic Programming. You can however simplify the range with a name. If you have not used range names here is some information about that. http://www.cpearson.com/excel/named.htm...

Excel: countif, array formula, even numbers

array formula, even numbers, logical progression: Various possibilities spring to mind for question 1 - IF you are adding up all even numbers then =SUM(IF(MOD(D1:D10,2)=0,1,0)) entered as an ARRAY formula (by pressing Ctrl Shift Enter) would do it for you - if it s not this logical progression, then...

Excel: countif, uncheck, a1

uncheck, a1, b1: If you want to add all even #s: ctrl/shift/enter =SUM(N(MOD(A1:D10,2)=0)) I think you made a mistake in the description of the 2nd issue - filling down will normally give you =a1,=a2, etc. You meant =a1,=b1,=c1, I assume! This is right out of my book,...

Excel: countif, bottom right corner, copy paste

bottom right corner, copy paste, a1: Luke I don t know of a way to write your COUNTIF formula any simpler assuming the object is to add the results of the countif of each of the numbers present in the range. Thats a very unusual objective, maybe your trying to get at something different than...

Excel: countif, expresso bar, ogilvy

expresso bar, ogilvy, real world: Countif provided a conditional count of items in a table. If I was a manager at a business that had a chain of book stores, looking at my data records, I might want to know how many had a expresso bar in the store. If column M contained either a Y or N...

Excel: countif, array formula, sumproduct

array formula, sumproduct, column c: You can use a clever feature of SUMPRODUCT to handle multiple conditions =SUMPRODUCT(--(NOT(ISERROR(SEARCH( tig ,A1:A4)))),--(B1:B4 20),--(C1:C4 2000)) would produce the result you wan t- where A1:A4 is the range with the model, B1:B4 the days in stock...

Excel: =countif formula, douglas m smith, array formula

douglas m smith, array formula, cell references: Hi, COUNTIF won t handle this situation, however, if I read your problem correctly, the following formula should solve your problem: =SUM(IF($A$5:$H$50= A ,IF($B$5:$I$50=1,1,0),0)) But, instead of hitting Enter, hit Control-Shift-Enter to make it...

Excel: countif function, a2, ae

a2, ae, score: You can use the MATCH function to find the name, and the OFFSET function to set the range to count on. Something like: =COUNTIF(OFFSET(B1,MATCH(AZ1,A2:A41,0),0,1,30), 6 ) Here I am supposing that you have 40 names on column A (range A2:A41), and the 30...

Excel: countif function, cell b2, blank cells

cell b2, blank cells, john thanks: John, Thanks for the feedback. I didn t even think that you might have time values included. If you didn t want to strip them you could do =COUNTIF(A2:A100, =12/09/2006 00:00:00 )-COUNTIF(A2:A100, =12/10/2006 00:00:00 ) -- Regards, Tom Ogilvy...

Excel: countif, sex camp, text entries

sex camp, text entries, e2: Glenn, I don t think I can answer this question as given. However, if you are willing to change the m and f to 0 and 1, then you can use DCount to get to a result. I don t know that DCount can figure out how to count text entries. It s possible that it...

Excel: counting cells with <>= certain numeric values, percentage value, l29

percentage value, l29, numeric values: The CountIf function will return a count of all cells meeting the Criteria, eg =COUNTIF(E1:I11, =.75 ), but I don t know how you could ignore ubsequent cells in the row. The only way I can think of is to add a Hidden Column and count occurences in each...

Excel: counting changes on column, quick solutions, colum

quick solutions, colum, craig: Hi Joaquin, I m not aware of any quick solutions or formulas to your question. I d start with some sort of IF THEN statement in column B that says, =if(b1=a1, , X ) This would identify for you when the items change, etc. but that is only the beginning...

Excel: counting color and text, yellow cells, football game predictions

yellow cells, football game predictions, excel spreadsheet: Only Excel 2007 can count colors. However, you can do it this way -- in the cell to the right of the cell which MAY become yellow, use Insert/Name/Define and use a name, say color , and the refers to is (assuming the active cell is D1 -- whatever cell you...

Excel: counting data cells, data cells, table wizard

data cells, table wizard, row location: Put all your names in a single column on a data base with their team name to the right. A1: Name B1: Team then the actual names and teams below that (actually, the column for Team is optional) Select both columns and do Data= Pivot Table Report...

Excel: counting the number of instances of months within a year, client interactions, sumproduct

client interactions, sumproduct, a100: Try this different formula: =SUMPRODUCT(--(DATE(YEAR($A$1:$A$100),MONTH($A$1:$A$100),1)=DATE(2007,10,1))) for Oct 2007 Can t recall all of the logic since I had this in my archives and I have not used it in a long time. I did not have the time...

Excel: counting numbers in given range., countif, counting numbers

countif, counting numbers, pivot table: IF you want the number of times EACH number appears across the whole column, then a pivot table will do this (data menu, pivot table and chart report, then follow the wizard). If you need an output in the format you have shown for a small part of the range,...

Excel: counting numbers in given range., counting numbers, bin size

counting numbers, bin size, b50: In cells B1 to B4 (or more) you type the number you want to use as the bin size, e.g: 1 2 3 4 Then select the cells in the range C1:C4 (just as many cells as you have entered bins for) and enter this formula: =Frequency($A$1:$A$16,$B$1:$B$4) ...

Excel: counting numbers in given range., counting numbers, b50

counting numbers, b50, b17: I don t follow. If you ve entered random #s in A, it s highly unlikely you d have repeats. And, if somehow you DO, how can you display if there are more than one number repeated in one cell? I think a specific example of what I d see in A1:A16 and what you...

Excel: counting ocurrences from 2 cloumns, logical expressions, excel2003

logical expressions, excel2003, j1: My favourite way of this counting two columns is using the function SUMPRODUCT. Even if the name sounds strange for counting, using it with logical expressions instead of pure numbers does the trick very well. In your case, you can try something like: =SUMPRODUCT((H1:H1000=6)*(J1:J1000=...

Excel: counting even and odd numbers, odd numbers, even numbers

odd numbers, even numbers, cells: Assuming the 5 cells are in range A1:E1... In cell A2, enter the formula: =IF(A1/2=INT(A1/2), EVEN , ODD ) then copy it to the range A2:E2. Then the formula: =COUNTIF(A2:E2, EVEN ) counts the even cells, and =COUNTIF(A2:E2, ODD ) counts...

Excel: counting # of rows, blank cells, functions in excel

blank cells, functions in excel, cell b1: Hi June, If you put this formula in cell B1 =COUNTA(A1:A1000)+COUNTBLANK(A1:A1000) It would count the rows you specify whether the there are values in column A or not. Such as if you change it to =COUNTA(A148:A296)+COUNTBLANK(A148:A296) it...

Excel: counting seconds, mm ss, column format

mm ss, column format, bottom right: Hey Shah, I think easier than making the macro you could do something like this.... If you type 0:00:00 in the first cell, the format should automatically be detected and set to h:mm:ss. Then in the second cell type 0:00:10. Then select both cells...

Excel: counting seconds, hh mm ss, macro security

hh mm ss, macro security, ltrim: Dear Shah How are you? Please try the following 1. Tools - Macro - security - security level - low Tools - Macro - security - trusted publishers- tick the two boxes at left-lower corner 2. Tools - macro - Visual Basic Editor 3. Insert module 4....

Excel: counting seconds, mm ss, stoptimer

mm ss, stoptimer, radar data: Here is the code: Declarations Private Declare Function SetTimer Lib user32 (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long Private Declare Function KillTimer Lib user32 (ByVal hwnd As Long,...

Excel: counting string cells in a table, array formula, blank cells

array formula, blank cells, error note: David: There are a couple of ways to handle this, but I believe the following is one of the better methods: =SUM(IF(COUNTIF(A1:A100,A1:A100)=0, , 1/COUNTIF(A1:A100,A1:A100))) This is an array formula and must be entered by pressing CTRL-SHIFT-ENTER....

Excel: counting % in a volumn, sumproduct, countif

sumproduct, countif, volumn: You ve given me conditions that contain holes. For instance, you haven t told me what to do with a number like 49.5% or 91% (you only mentioned you want percents greater than 91%?) However, I ll just make an assumption that what you meant to give me was:...

Excel: counting, collections, countif function

collections, countif function: It will a lot easier and less confusing if you have a separate column for just the year in question and then use the COUNTIF function. In that separate column you can use this formula to have EXCEL put 2005 in each row where 2005 appears in the cell where...

Excel: creat new tab

Hi Raquel, I am guessing that your workbook may be protected. You can check by going to the excel menu and selecting Tools then Protection then Unprotect Workbook enter password if there is one and click OK . Hope that helps. Thanks Ke...

Excel: How create a button that will sort data by decending., vba code, decending

vba code, decending, goodmans: Create the button by choosing View, Toolbars, Control Toolbox. Attach the following vba code to the button click: Sub vbaSort() Dim rngToSort As Range Set rngToSort = Sheets( Sheet2 ).Range( a1 ).CurrentRegion rngToSort.Sort Key1:=rngToSort.Range(...

Excel: create a formula, formul, warm regards

formul, warm regards, yyyy: Hi, I am sure you can have a better answer.... But my trials with combination of COUNT or COUNTIF with YEAR (Array) have failed. Hence, to save time, I propose- (1)In column B have YEAR(A1) and so on (2)Then use COUNTIF(A:A, 2001 ) Notes- (a) Though...

Excel: create keyboard shortcut in excel, keyboard shortcut for paste, keyboard accelerator

keyboard shortcut for paste, keyboard accelerator, paste values: Janie, there s one other thing I should have mentioned to you earlier! Go ahead and place the Paste Special button on your toolbar as I showed you before. When you do so, you ll notice that the S of Special is underlined. This character, called a keyboard...

Excel: create a list box on a cell based on data in another, cell a1, data validation

cell a1, data validation, granite countertops: Start with a blank sheet. In cells E1:E3 put Level 1 . In E4:E6 put Level 2 . In cells F1:F6 put various colors. Give a range name to E1:E6 rngLevels . In cell H1 put Level 1 ; in H2 put Level 2 . Give a range name to cell A1 levelPick . Give it...

Excel: How to create this pop up., target address, excel workbook

target address, excel workbook, address range: If you don t know vba programming, the best you can do is to enter a comment in that cell. If you do know vba, you can use the following: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = Range( a1 ).Address Then ...

Excel: create a questionnaire for multiple use, multiple choices, data validation

multiple choices, data validation, countif: No problem - multiple choices can be done with DATA VALIDATION, then create a list - probably typing it in the box is easiest. For the tally it s a COUNTIF I think, although the actual answer may be different depending on what you need to count or sum! ...

Excel: How to create unique item numbers, john walkenbach, auction program

john walkenbach, auction program, archtype: JeAnna, this is possible, but you have to know how to use VBA code to make it work. The userform would be built for the user to select a category (1-8 or whatever), and then the code would just cycle through a sequential item number until a new category was...

Excel: RE: How to create unique item numbers, three digit number, category number

three digit number, category number, classroom art: JeAnna, I think I have a solution for you. Rather than type 5 pages of how-to instructions, I d rather just send you the file. If you send me an email at agentsmith@stny.rr.com, I will reply with a sample file you can build your app from. Regards,...

Excel: How to create unique item numbers, category number, data input

category number, data input, digit number: Hi JeAnna, I apologize for not answering sooner. First of all I had problems thinking about your problem. I believe the solution is to change the way you would like to accomplish this. Hopefully I am not to late. In my experience I would create eight...

Excel: How to create warning message, roll numbers, textboxes

roll numbers, textboxes, message box: I think I have already dealt with this for you. The N/A returned in the other textboxes says exactly the same thing. Not Available. If you really want to popup a message box then where I had the code if Application.worksheetfunctions.Isna( etc. that...

Excel: I created a macro in Excel..., macro in excel, spread sheet

macro in excel, spread sheet, excel 97: Robby, There has to be a macro in the file somewhere for it to show the enable, disable,don t open dialoge box. Open the file and enable the macros. Press Alt+F11. Once the VBA editor starts, press CTRL+R - this starts the Project Explorer (if it was...

Excel: creating a chart in Microsoft Excel, home office computer, excel vba

home office computer, excel vba, data labels: If the valus of 2000- 2004 don t exist in the worksheet, you can click the series, then change the formula bar formula from =SERIES(,,Sheet1!$A$1:$A$5,1) to =SERIES(,{2000,2001,2002,2003,2004},Sheet1!$A$1:$A$5,1) (note that around the years are curly...

Excel: creating complex spreadsheets, interest formulas, interest calculations

interest formulas, interest calculations, interest amounts: Hi Arthur, You have a tricky problem. Developing a tool to handle it proper is beyond the scope of this forum but I can give you a quick overview of interest calculations. For the daily interest rate, divide the annual rate in effect for the current...

Excel: creating a copy of a shhet, low disk space, xls document

low disk space, xls document, excel sheet: Normally, holding down Ctrl while dragging a tab will create a copy of it. Here are some things that might be casuing you trobuel (but it is just a guess): 1) The file is protected so that no changes can be made to it. 2) There are already too many tabs...

Excel: creating dyanamic column, initial dosage, consuption

initial dosage, consuption, adult list: Hi, My trials so far indicate this may not be possible through formulae. I have presumed that you will keep adding data to ADULTS as well as PED and new drugs / format combinations will keep getting added. You may need to resort to use of macros. Is is...

Excel: creating excel database from MS Word labels, word labels, mass mail

word labels, mass mail, printing labels: Short answer is NO. I would copy and paste all the data at one time into excel. Then I would use formulas or a macro to re-organize it into a database. I assume you are going form J. Adams 123 S Str New York, NY 10316 to with a single...

Excel: I am creating a form in Excel.My..., range target, cell b1

range target, cell b1, target address: Hello anjali, There are several ways to do this, and it can be done with either validation dropdown lists (as you are using) or with userform comboboxes or listboxes. There are two ways to do it with event code, and may variations on each of these: 1....

Excel: creating important macro for my new important job :), email attachment, swbell

email attachment, swbell, carribean: I answered this question with my email address a while back, but it appears that answer didn t process through the allexperts system. Following that, I have been on vacation in the Carribean for the past week and had no idea the reply didn t go through...sorry...

Excel: creating list via an arrow for cells, text choices, dropdown boxes

text choices, dropdown boxes, data validation: Hi Leo, You can use Data Validation for this. First you need to input the 9 different text choices in a column somewhere off to the side of your worksheet. These 9 choices should reside on the worksheet where you want the dropdown boxes. Then select...

Excel: creating a "master" file in Excel, master spreadsheet, task list

master spreadsheet, task list, data sheet: I think you will need VBA to achieve this. In the past,I have set up a master spreadsheet on the server, then each time an individual workbook is opened it opens the master and copies the data sheet into the workbook, without the user knowing. Then the workbooks...

Excel: creating new macros, l4 l5, cell a1

l4 l5, cell a1, empty column: Hi Sudha, The formulas you listed should be working. The reference style $F$1:$G$36 locks the reference to those cell but the A1 style should change as you copy the formula to read the dates (B2, etc.). If you can email your file to doug@abundant-solutions.com,...

Excel: creating a pivot table, invalid property assignment, pivottables

invalid property assignment, pivottables, rsrc: One problem is you are using x1 (x-One) rather than xL (x- L ) for your constant names. I don t have anything later than xl2000 installed right now, but this worked for me. (I could see your row and column field names, but you left off the field name...

Excel: creating results in different cells, cell d1, cell a1

cell d1, cell a1, jan sales: Jamie The formulas should work EXCEPT The reference to $D1 in the formulas should be changed to $D10., I erred in putting D1 in the formula. The formulas are in Sheet B, and basically waht they say is---- =IF( sheet A !$D10=1,+ sheet A !A1, ) ...

Excel: creating a schedule in excel that excludes lunch time, cell b2, cell a2

cell b2, cell a2, taking out time: Send me the file and I ll take a look at your formulas, etc. rmadsen@iname.com. Robert ====================================================== Want to learn more? I write and sell training materials for Excel and many other software products. The materials...

Excel: creating scrollbars in Excel 2007, microsoft site, scroll bars

microsoft site, scroll bars, office microsoft: Paul: The control toolbox is rather well hidden in Excel 2007. You can gain access by following these steps: 1) Click the office button (upper left corner/circle) 2) Select Excel Options 3) Under Popular, enable show Developer tab in the Ribbon ...

Excel: cross reference, cell b2, cell a2

cell b2, cell a2, tom dick and harry: To do this, you need to understand the =VLOOKUP() function. I ll give you an example, and this should be enough to solve any problem of this time. Say you re using Sheet1 as a master list that matches names to status (active or retired) and policy #. As...

Excel: cross reference, counta, sheet changes

counta, sheet changes, excel help: Jan, There are several ways to tackle your issue but I was thinking about a solution that is elegant yet does not require coding. Also, I was looking for a solution which doesn t need to change as the number of records in one sheet changes. Anyway here...

Excel: cross reference, policy numbers, masterlist

policy numbers, masterlist, jan jan: Jan: I presume COL D of the mastersheet already has the policy number in it - if you copied from sheet 1 and sheet 2 directly to the masterlist, I presume you also copied the policy number. The reason I mention this is because to do what you ask we need...

Excel: cross reference, business adress, vlookup

business adress, vlookup, masterlist: Jan: I have uploaded a new spreadsheet here: http://home.swbell.net/nate-sus/excel/acive.xls If you still see the old spreadsheet that that address be sure to click refresh on your browser to obtain the new version. I have added a new function that...

Excel: cross reference, vlookup formula, business adress

vlookup formula, business adress, policy numbers: The VLOOKUP formula will search on the leftmost column of the range, so in this case it is not useful for you. You can try the combination of INDEX and MATCH, something like: =INDEX(Company!$A$2:$A$4,MATCH($C2,Company!$C$2:$C$4,0)) Hope this helps, Miguel...

Excel: cross reference, excel formula, green orange

excel formula, green orange, list of colors: Jan: Some functions, such as VLOOKUP, MATCH, and HLOOKUP, will return a value of #N/A if the item intended to be found is not found. For example, if you have a list of colors... Blue...green....orange. And I tried to find violet in the above...

Excel: cross reference, irrelevant details, 1000 names

irrelevant details, 1000 names, dates of birth: Hello, could you be more specific? You have information on different columns? Do you have any a match column, to have the same information - because you can use vlookup ... if theres something else, please let me know and Ill do my best to help you with...

Excel: cross reference, a77, bottom right corner

a77, bottom right corner, little knowledge: Select all the employees from sheet1 and use Edit/Copy. Select Sheet3, click in cell B1, use Edit/Paste. While on Sheet 3, click in cell A1, enter the word Active , and then select A1 & double-click on the fill handle (bottom-right corner of the cell). This...

Excel: cross-sheet calculation, cell c2, index array

cell c2, index array, cell a2: Julia: Your description was just fine :). I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/forjulia.xls The formula in cell C2 of sheet1 is what you are looking for, and it may appear a little complex, so let me break...

Excel: cumulative income, cumulative income, design projects

cumulative income, design projects, granularity: I am not completely sure of what you are trying to do, and where are the issues now. Do you have issues with the design of the forecast, or you have the design but you need the formulas to work? Also, is the data entered in the system in the same granularity...

Excel: currency conversion, currency conversion, canadian currency

currency conversion, canadian currency, access htm: Hi, Joanne. Using this site: http://www.officearticles.com/access/search_on_a_form_in_microsoft_access.htm It says 1.00 USD = 0.966357 CAD So, if you have US Dollars in A1, then put =A1*.966357 wherever you want the Canadian dollars to appear. Unfortunately,...

Excel: currency conversion, currency conversion, euros to pounds

currency conversion, euros to pounds, dollar currency: Hi Chris, How are you? As far as I know thereis only the convert to Euro addin. But you can write a VBA function that receives the exchange rate as an argument and the ammount of money in a currency and converts to the other. E.g. 1 pound = 1.5 Euro...

Excel: currency number to text, string place, three hundred ninety

string place, three hundred ninety, decimal place: I still don t see a question - all I see is now what you need. Assuming you are asking How do I do that? , then Googling for numbers to words in EXCEL would have found you many examples. Here is one - you will have to modify it a little to suit your specific...

Excel: how to get current date, hellow, current date

hellow, current date, ogilvy: Hellow June, in the menu go to View Header and Footer then select either custome header or custom footer. You have the choice of left, right or center. Select the location and click the Date icon to embed instructions to display the current date...

Excel: how to get current date, header footer, custom header

header footer, custom header, current date: June If you don t want to put it on the worksheet itself, you can put in the header or footer. Just go to File/Page Setup and choose the Header/Footer tab, then select Custom Header, select which section of the screen to place it (left, middle or right)...

Excel: custom button, keyboard shortcut, custom button

keyboard shortcut, custom button, blank button: It s easy to assign shortcut keystrokes to your macros. However, in this case, the code I gave you was relying on the fact that you clicked the command button as a way to generically target the button to toggle the state. We can assign a shortcut keystroke,...

Excel: custom cell format, douglas m smith, paste values

douglas m smith, paste values, accurate dates: Hi Barrett, To do everything you wanted, it would take a tricky macro. However, for a little extra work, it can be done without a macro. I d recommend entering the code in one cell and having the next cell create the full format. Example: ME123 is entered...

Excel: custom format-excel, spreadsheet, payroll

spreadsheet, payroll, workforce: Mel Is it listed anywhere in the spreadsheet. The reason I am asking is that if it is you can use the A1&B1&C1 will give you S000 from the 1st cell 10 from the second cell and t from the third cell. If it isn t then you will have to do it manually as...

Excel: custom format, dd mm yy, conditional formatting

dd mm yy, conditional formatting, numerical equivalent: Alan I believe you can do this with the conditional formatting feature, but you have to be kinda tricky with it. The dates will be converted to numbers as far as excel is concerned so you can use the conditional formatting and tell it that any dates greater...

Excel: custom formatting to insert dashes, format cells, letter combinations

format cells, letter combinations, ogilvy: When you add a letter, the whole entry becomes text. You can t format Text using a format. You will need to put a formula in an adjacent column and do something like (assume your first value is in A1, Then in B1 for example =Left(A1,1)& - &Mid(A1,2,1)&...

Excel: custom toolbars, custom toolbars, microsoft template

custom toolbars, microsoft template, custom toolbar: Alexa, I have no first hand knowledge of this template, but in general toolbars are either attached or created by code in the template. If attached to the template, it should create the toolbar went the template is opened. Since you don t see it, try going...

Excel: customized excel function, excel function, cars

excel function, cars, formula bar: Not totally clear, but if CARS is simply a cell, then insert a name of CARS on that cell (easiest way is to use the bar to the left of the formula bar and type the name, but you can also use the insert menu to insert a name. Once this is done, CARS will equate...

Excel: cut en past, coloms, input error

coloms, input error, max number: Hello Jos, This macro should do it for you. Sub SpreadColumns() Const nHeaderRows = 1 Dim nRowsPerCol As Variant Dim LastRow As Long Dim RowStart As Long Dim RowEnd As Long Dim iCol ...

Excel: cutting combination, cell d2, excel solver

cell d2, excel solver, maximum loss: This solution uses the =DEC2BIN() function, which is part of the Analysis ToolPak. Make sure that this is installed: from the main Excel menu, select Tools, Add-Ins, and check the box for Analysis ToolPak. Enter your 8 sizes in range A1:A8 (31 in cell A1,...

Excel: cutting/pasting, drop down menus, pressing control

drop down menus, pressing control, control x: Mark Sounds like a perfect use of the text to columns feature of excel. This feature will separate the data in a column in a fixed width way or by delimiter, such as space or comma, etc. Paint all of the data then go to toolbar/data/text to columns....

Excel: DATA SHEET, cell a1, michael higgins

cell a1, michael higgins, ogilvy: I assume you want a macro? Run this macro Sub MakeData() Dim v As Variant Dim cell As Range, i As Long ReDim v(1 To 50, 1 To 10) i = 456 For j = 1 To 50 For k = 1 To 10 v(j, k) = i i = i + 1 Next Next Range( A1:J50 ).Value = v ...

Excel: DATES, cell b1, cell c1

cell b1, cell c1, excel formula: Assuming that the from date is in cell B1, and the to date is in cell C1, the formula is: =(YEAR(C1)-YEAR(B1))*12+(MONTH(C1)-MONTH(B1))+1-IF(DATE(YEAR(B1),MONTH(B1)+1,1)-B1 10,1,0)-IF(DAY(C1) 10,1,0) Remember to format the cell that contains this...

Excel: DAYS360 excelsheet with empty cell, cell references, aidan

cell references, aidan, spreadsheet: =if(and(firstdate 0,seconddate 0),Days360, Date Missing ) will do it for you - it will ONLY return the value if BOTH dates have been completed - replace FirstDate and SecondDate with the cell references, and Days360 with the formula you have at the moment...

Excel: Daily Compound interest formula, compound interest formula, daily compound interest formula

compound interest formula, daily compound interest formula, amortization schedule: I just realized I never mailed you the formula I used to calculate the amount I show should be the Monthly Payment: =ROUND(PMT(((1+INTRATE/360)^30)-1,YEARS*12,LOANAMT),2) If LOANAMT = $1,662,000; INTRATE = .075; YEARS = 10, the above formula calculates...

Excel: Daily Interest Calculation, douglas m smith, amortization schedule

douglas m smith, amortization schedule, interest calculation: Hi Barry, I don t have handy to do this but there lots places on the interest to get the information you need. Here one I found that you could set up and modify: http://www.exceltip.com/st/Create_an_Amortization_Schedule_in_Microsoft_Excel/302.html...

Excel: Daily Interest Calculation, interest rate change, calculating interest

interest rate change, calculating interest, excel spreadsheet: Barry You can use the FVSCHEDULE()function to do your calculation. the earnings on each loan should be calculated using the different rates that apply during the periods in question. So in other words the value of the first payment at the payback time...

Excel: Daily Interest Calculation, excel spreadsheet, margin account

excel spreadsheet, margin account, interest calculation: If I understand you, then $20,000 per month is interest on $20000 in month1, interest on $40000 in month 2, interest on $60000 in month 3 etc. Put these figures in A1:A11, put the varying interest rates in B1:B11, and in C1 enter =A1*B1/12 and fill down. THe...

Excel: Daily overtime, hourly wage, workweek

hourly wage, workweek, multiplier: In cell A1 enter the number of hours worked in the week. In A2 enter the base hourly wage. In A3 enter the over-time multiplier (if A1 is 40, all extra hours get the hourly wage multipled by A3). The formula to calculate the total weekly wage including...

Excel: Data Analysis Plus, data analysis tools, box plots

data analysis tools, box plots, input range: Dan To use the Data Analysis tools do the following: 1 On the Tools menu, click Data Analysis. 2 In the Analysis Tools box, click the tool you want to use. 3 Enter the input range and the output range, and then select the options you want. I m...

Excel: Data Averaging, douglas m smith, mediation specialist

douglas m smith, mediation specialist, budget supervisor: Hi Cindy, I m sorry you haven t had a good experience on this board. I ll try and make up for it. You can tally text items with COUNTA. It counts all non-blank cells and works just like SUM. To count the Y/N entries, use COUNTIF. To count Y...

Excel: Data analysis, vbe, capital letters

vbe, capital letters, excel 2000: Luis, I believe the simplest way to do this is to create a custom function. Just like Excel has built in functions like Sum and Count, you can create one to extract pieces of data from a string of values. For example, you can place the following function...

Excel: Data in Columns, cell reference, column values

cell reference, column values, b2: RON I am not sure that I totaly understand the question, but i believe you can do what you want using a SUMIF formula. For example: =SUMIF(A2:B11, A ,B2:B11) This formula will sum all of the values from cells B2 thru B11 where the corresponding row...

Excel: Data compare in Excel, john walkenbach, conditional formatting

john walkenbach, conditional formatting, power utility pak: Catherine I don t know of a function that goes through the whole worksheet for you and does that automajically. However, it s not too tough to set up either. The formula would be simple: =EXACT(Sheet1!A1,Sheet2!A1) to check the contents of A1 on those...

Excel: Data Entry Form for Excel Spreadsheet, mail group, hi aaron

mail group, hi aaron, excel spreadsheet: First, I d just try using the built-in data entry form feature. Just select a cell within your worksheet list and select from the main toolbar: Data|Form If you want something more sophisticated it would indeed involve using VBA. In which case you re...

Excel: Data Entry Form, functional spreadsheet, spreadsheet program

functional spreadsheet, spreadsheet program, abacas: 1) Tools-- Macro-- Security. Set it to Low. 2) Hit Alt+F11. Double-click ThisWorkbook on the left. Copy the macro. From the menu, hit Insert-- Module. Paste the macro on the right-hand side. Rename it from Private... to Sub GetMyForm() and hit the SAVE...

Excel: Data Entry Masking, slashes, dashes

slashes, dashes, data entry: if you want it to LOOK like 12/12/07 when you enter 121207, then you can format it as 00 / 00 / 00, but it won t be a date, it ll be 121,207 formatted to look like a date. If you want a REAL date when you enter 121207, you would need VBA to do it. There is...

Excel: Data>Get External Data>New DataBase Query excel97, database query, rng

database query, rng, office 97: How many lines are being generated by the 167 worksheets - if less than the circa 60,000 maximum of an Excel worksheet, then I would suggest Open each file as a txt file copy the data paste to the master sheet repeat until all files done now run a...

Excel: Data entry from from MSAccess, attendance records, many different ways

attendance records, many different ways, msaccess: Hi Jim, This is one of those questions where there are so many different ways to accomplish possibly what you want that I thought I would list my thoughts on this and maybe that would help. Certainly going to the Excle Menu and selecting Edit , then...

Excel: Data/Format Layering, userforms, form factors

userforms, form factors, borders: What do you mean by a form ? EXCEL has its own customizable userforms, which are a kind of macro, that you can create or a worksheet can be formatted and made to resemble a form. Which are you referring to? Remember, I can not see what you see and ALL...

Excel: Data formula and analyzation, fgi, h2

fgi, h2, 4d: Sometimes tables don t paste very well into Allexperts...here is the table as I interpreted it: A,B,C,D,E,F,G,H 1= ID, Rev Quan,Rev Net,FGI,ASSY,WIP,DGI,Result 2= 1002-3P-1,4,$12000,1,1,2,6,Move from DGI 3= 1004-E-4D,2,$30000,3,2,2,14,Move from DGI...

Excel: Data Layout for printing, format columns, data layout

format columns, data layout, paste link: The absolute best way is to copy it into Word and Format-- Columns and choose 2 columns. I don t believe there IS a way to do it in Excel except by manually copying from the bottom to the top. When pasting to Word, you can use Eidt-- Paste special and choose...

Excel: Data List in Excel, school board elections, board of elections

school board elections, board of elections, information thanks: You will need a macro but as a rule I do not write macros for people on this site and this is one of those times when the rule applies since the macro you need is somewhat involved - more so that what you first think the macro will need to do. The number...

Excel: Data Lookup, information column, data elements

information column, data elements, data values: There is no way EXCEL can determine on its own which data item you out of the two it would have found. How could it know which one of two Sally Jones s it found with no other unique info about each Sally Jones. How would you know? You will have to make...

Excel: Data labels in excel, fuel consumption figures, data labels

fuel consumption figures, data labels, value axis: If by Data Labels, you mean what the data on the axis is, like MPG (miles per gallon), the easiest thing to do is select the label by right-clicking on it once. And then right-click once more, after a pause, on the text itself. A cursor should pop up and you...

Excel: Data Manipulation, data manipulation, inpt

data manipulation, inpt, hole 2: Also the sort on the ranking, I need to be automated... . The only way that can take place is with a macro. And since I was unaware, since you did not say, of the other things in the workbook, (i.e. ...scores per hole derived from another part of the...

Excel: Data Plot Line ''Disappears'' When X Axis Formating Is Changed, time series data, chart wizard

time series data, chart wizard, check boxes: John: Based on my understanding of the question, the plot line disappeared because the values on the line probably didn t fall between 1980 and 2008 (Excel probably tried to think for itself and plot stuff the way it thought you wanted instead of the way...

Excel: Data protection with use of filter option, business intranet, excel spreadsheet

business intranet, excel spreadsheet, filter option: Excel 2002 & beyond has a feature in the protection to allow autofilter. My guess is you have Excel 2000. I suggest upgrading! Otherwise, you re either out of luck or would have to resort to non-trivial VBA coding. And, FYI, Excel 2007 is due out in about...

Excel: Data Recording, a1 people, cell a2

a1 people, cell a2, rockwell software: Well, there probably is a macro SOMEWHERE that exists but I doubt it. Since most macros are so task specific then finding a macro that fits your EXACT task/needs is highly unlikely. This is a macro you will have to write yourself and sorry I can not write...

Excel: Data Transfer between Arrays, source example, column number

source example, column number, execution time: You are confusing Auto Filter with Advanced Filter or you are using Advanced Filter by filtering in place instead of extracting the data to a separate location, which is what I was suggesting. Use Copy to another location to do that. Then you loop the extracted...

Excel: Data Between UserForms, initialize event, userforms

initialize event, userforms, share data: One way is to assign to a PUBLIC variable what happens on form1 s option by using its click event. For example, on form1 Put this in a module Public TestVar as Boolean Then in the code for your forms Private Sub Optionbutton1_Click() If Me.Optionbutton1...

Excel: Data Validation, conditional formatting, data validation

conditional formatting, data validation, sorry for the confusion: If C1 contains a formula (& it does), using data validation makes no sense, since that s set to warn/stop users from ENTERING an invalid value. You can use VBA to trap it. Right-click the sheet tab,select View Code and paste this in: Private Sub Worksheet_Calculate()...

Excel: Data Validation, data validation, settings screen

data validation, settings screen, drop down list: Gary I don t have another answer as to why it is not appearing, but a solution may be to copy the entire sheet to another new sheet, which may very well get rid of the problem. First try a new sheet in the same workbook and second try a complete new...

Excel: Data Validation, douglas m smith, visual cue

douglas m smith, visual cue, dropdown boxes: Hi James, Unfortunately, doesn t work on calculated values, only entered values. You can however, flag an offending value with Conditional Formatting. Select C1 and then choose Conditional Formatting from the Format menu. In the dialog box, select...

Excel: Data Validation, data validation, cells

data validation, cells: Kelly The data validation reference needs to refer to a group of cells in the same worksheet (assuming you want a list) so if it needs to be linked with another worksheet or workbook you can recreate the list by linking the cells in the other workbook/worksheet...

Excel: Data Validation, data validation, ogilvy

data validation, ogilvy, bold font: Jim, Data validation only restricts what is entered - it doesn t force an entry. You would need a macro for that and of course, the user could always disable macros. Possibly you could put a default value in the cell and make it highly visible with a...

Excel: Data Validation, data validation, message box

data validation, message box, many thanks: Data Validation may not work the way you are thinking based on the way you have described your situation. To make EXCEL display the Data Validation msgbox you have to click in that cell that has had the validation applied to it. Simply changing a number...

Excel: Data Validation, cell b1, data validation

cell b1, data validation, quotation marks: Say that the data on name_info is in range A1:C10. First, give a range name to A1:A10. First select that range, then from the main menu select Insert, Name, Define, then type in ListOfNames (without quotation marks). In contact_list, select column A,...

Excel: Data Validation, cell b1, data validation

cell b1, data validation, quotation marks: You can t have both a formula and an override cell without a little trickery. You d need some VBA event code that would place the formulas back in col B & C whenever the user selects from the validation in A. Something like this (right-click the sheet tab,...

Excel: Data Validation, data validation, vlookup function

data validation, vlookup function, applicable code: Yes I am able to provide some information. Assuming you meant to ask what is that information or how can I do this and then IF I understand what you are trying to do based on the way you have described your project then you can use the VLOOKUP function and...

Excel: Data Validation, data validation, demo code

data validation, demo code, ogilvy: Paula, This would involve programming the worksheet_change event to take action on the value of the cell. If you know how to do that, then I have answered the question. If not, then if you want to send me a simple sample file, I can provide some...

Excel: Data Validation Across a Range, data validation, message boxes

data validation, message boxes, subject data: I need to validate a range of cells on the same row (o2:t2) so that a user can only put a value of 1 in one of the cells within the range. [That is what Data Validation is for] Should they try to add any value within the rest of the defined cells...

Excel: Data Validation across worksheets, target range, worksheet changes

target range, worksheet changes, data validation: Data validation across sheets isn t supported. You may need an event macro with code to do the checking for you as the worksheet changes. You can do something like this: Right-click the sheet tab, select View Code, enter: Private Sub Worksheet_Change(ByVal...

Excel: Data Validation - auto include new items, target cells, target address

target cells, target address, data validation: Use the Intersect method, just adjust the variable rng Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lReply As Long Dim rng As Range Set rng = Range( d4:d10 ) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target,...

Excel: Data Validation - auto include new items, target cells, target address

target cells, target address, data validation: If you want code to run if the target is say within range D4:D7, use code like Set rng = range( D4:D7 ) If union(target, rng).address = rng.address then ... Regarding data validation in general... if I wanted a cell to have a data validation list with...

Excel: Data Validation - auto include new items, target cells, target address

target cells, target address, data validation: Hi Al, If the range MyNames is truly a dynamic range (i.e., it automatically adjusts to the number of items of data in the range of interest), then the worksheet Change event is unnecessary. When you do the list-type Data Validation on cells D1 through...

Excel: Data Validation & Conditional Formatting, dd mm yy, conditional formatting

dd mm yy, conditional formatting, data validation: Hello Roger, For the data validation you could use the custom formula option and use a formula lile =Weekday(A1,1)=1 format the cell as dd/mm/yy for the 4th Friday, select alll of columns A and B or I guess you said just column B and do format=...

Excel: Data Validation, data validation, speciallist

data validation, speciallist, range names: use range names - name F1:F500 on sheet2 as test, for example, then use =test in the dropdown control. TO do item 2, you need to use either VBA code or involved formulas. For example, if dropdown1 has a linked cell of A20, then dropdown2 can use a named...

Excel: Data validation within Excel, target cell, validation rules

target cell, validation rules, data validation: Doing a Copy, Paste Special As Values will NOT clear out the data validation rules but the validation rules will not kick in, and detect, and prevent anything to be pasted that violates the rule. It seems to me though that you could have very easily tested...

Excel: Data variations in Excel, cell a1, number group

cell a1, number group, sheet1: You can use a macro like this, I have used the worksheet names Sheet1 and Sheet2 , and the data starting on the cell A1 in both cases, change it if required. Sub Variations() Dim rngOrigin, rngDest As Range Dim i, j, k, y, z As Double ...

Excel: Data, array formula, array formulas

array formula, array formulas, mouse pointer: Hi James, It is not comparing cell to cell but column to column to find those cells that are unique or alike depending on the formula. Perhaps it would be easiest to send an example and show what you would like the information to look like. You can send...

Excel: Database information, cell b1, data cells

cell b1, data cells, anaheim ca 92806: If I understand your question correctly, you have to be very careful when editing linked workbooks. Both workbooks need to be open if you insert columns (or any other cell movements) or the references in the saved (closed) workbook won t know about the...

Excel: Date, open range, current date

open range, current date, double click: I tried to help by providing a link. No such animal appears to exist. So, here goes. What you need is a macro that will run when you first use the workbook. To run the macro automatically on a newly created workbook, you need to open the workbook. Hit Alt+F11....

Excel: Date Calculation, douglas m smith, weekends and holidays

douglas m smith, weekends and holidays, brainbench: Jerry, I just realized I didn t quite answer your question. I look at it it a little closer and send you an answer in the morning. Doug =========================================================== Hi Jerry, There is a function called NETWORKDAY...

Excel: Date calculations, cell a1, holiday days

cell a1, holiday days, public holiday: Well, this SEEMS to work. You need a range of cells which contain all the dates which are holidays. Wherever you put them, name them holidays (insert/name/define...). Then, if cell A1 contains the start date, and A2 contains the # of days to add (56 in...

Excel: Date computation in Excel to track transaction on day of month, 31st february, month of the year

31st february, month of the year, cells: Well, OK, so what s your question? Regardless of that however, wait, let me see if I understand. You have a client who pays you for your time and work and you want me to do some of that work for free? All volunteers on this site offer their advice and...

Excel: Date computation in Excel to track transaction on day of month, douglas m smith, array formula

douglas m smith, array formula, 31st february: Hi CH, I m a little confused about your setup so I ll throw out some general date information. To tabulate monthly counts, I usually build a table: 1/1/2005 1/31/2005 2/1/2005 2/28/2005 . . I then use an array formula to read the dates in the...

Excel: Date computation in Excel to track transaction on day of month, count cells, 31st february

count cells, 31st february, precise formula: CH I m not sure what your data looks like so its a little difficult to give you a precise formula. I would suggest you consider referencing a cell in your count formula which represents the dates so instead of changing the formula in all of the cells,...

Excel: Date computation in Excel to track transaction on day of month, array formula, 31st february

array formula, 31st february, countif: CH Cohen Let me start by saying I don t have a firm grasp of what you need here. I am having a difficult time understanding what it is you are doing. It sounds as if you are counting the occurrence of each day of the month. Tedious is not the word for that...

Excel: Date computation in Excel to track transaction on day of month, 31st february, month of the year

31st february, month of the year, cells: I m sorry, but I don t understand what you re trying to do. Jan 01, 01 = 1 , for example, doesn t make sense to me. Also, I have to calculate a date on the fly for each month of the year, but need it to stop counting once that date is past. doesn t make...

Excel: Date conversion, dd mm yy, spreadsheet

dd mm yy, spreadsheet, conversion: Use Find & Replace. CtrlF, Find What; . ,Replace: / If you want code to do it, place this in a Standard Module (see this link for details of where to place code - http://www.excel-it.com/Forum/viewtopic.php?t=11) Option Explicit Sub ConvertTodate()...

Excel: Date conversion, monh, forward slash

monh, forward slash, number pad: Edward It looks as though you want to type in dates without putting in the forward slash between the day monh and year. This would be nice and certainly a lot quicker when entering dates. Here is what you can do. Usually when I enter dates I use the...

Excel: Date Data Entry, 6 digits, number pad

6 digits, number pad, actuall: JOHN You have to enter a slash or a hyphen, then excel assumes it is a date and will, depending on how the cell is formatted, put in 04/04/04 or 04/04/2004 or 04/04 or etc, etc. I find it easier to use the number pad and hit the hyphen or minus key when...

Excel: Date/Days formula, number of days between two dates, days between two dates

number of days between two dates, days between two dates, larger date: Phil: You have probably noticed you cannot subtract a larger date from a smaller one; thus, you have to use some trickery to get it to work. Try this formula... =IF(B2 A2,+B2-A2,-(+A2-B2)) Whatever cell the above formula is in you will need to...

Excel: Date-to-days formula, negative time, column c

negative time, column c, time note: Patrick: Presuming you enter these values: 1) C1 contains 5/10/2007 (I know you said year was irrelevant, but you will need to have a year for Excel to recognize the item entered as a date). 2) G1 contains 5/15/2007 3) Place this formula in H1: =+G1-C1+1...

Excel: Date defaults to currnet year in excel 2003, odd behavior, formating

odd behavior, formating, excel 2000: If you re typing june 10 in a cell, you should see 10-Jun and it would represent June 10, 2007. If you re actually getting June 1 2010, then somehow Excel is reading it as if you typed June 2010. And no amount of formatting will enable you to convert june...

Excel: Date in excel shouldn't change, douglas m smith, keyboard shortcut

douglas m smith, keyboard shortcut, date control: Hi Samuel, The is a keyboard shortcut to enter today s date, Control ; The date will be static and not change. You can also enter time with shortcut: Control Shift : Douglas M. Smith Brainbench MVP for MS Excel www.brainbench.com www.abundant-solutions.com...

Excel: Date Format, format cells, dear bill

format cells, dear bill, mm yy: Choose any date format (or any format for that matter) the way you would normally do. Then, while in the dialog box that lets you format cells, choose Custom. Under Type: type dd-mm-yy. It makes no differece if this is done in a financial prices spreadsheet...

Excel: Date Format Changes, vba code, expert listings

vba code, expert listings, excel expert: Hello, Nurul, You may have noticed where I say that I m not familiar with VBA. (I mention it both in my expert listing and on the question page.) I am a programmer, but I use the old macro language that came out with an earlier version of Excel. I can...

Excel: Date Format Formula, nestings, 1 feb

nestings, 1 feb, 10 nov: Presuming the date is in column A, you could use this formula: =IF(YEAR(A1)=2006,MONTH(A1)+12,MONTH(A1)) This formula calculates the month based on the date, and then adds 12 if the year is 2006. Presuming you need to increase the formula for 2007...

Excel: Date Format, space bar, mm yy

space bar, mm yy, many thanks: Seems like you have dates as text. First thing is select the column and format it as d-mm-yy. You may not see any change. If you do, you re done. If not, while the column is selected, use Edit/Replace and replace a space with a space -- that is, in the Find...

Excel: Date Formatting, spreadsheet column, mm yy

spreadsheet column, mm yy, format cells: Hi Ken, This is what you need to do to format a date from dd-mm-yy to mm/dd/yy: -Insert a new column next to the one that has the dates you want to reformat. -Assuming your date is in cell B1 (you will have to change that part of the formula to fit your...

Excel: Date Formatting, spreadsheet column, format cells

spreadsheet column, format cells, mm yy: Ken, that s one of the stranger things I have heard of. First off, the format you want is 03/14/01, which would be mm/dd/yy;@ if you select that from the list and check to see what it would be if it were a custom format. The strange part is why Excel...

Excel: Date Formatting, spreadsheet column, format cells

spreadsheet column, format cells, mm yy: After you formatted the column correctly, you may have to re-enter each date. You can do it quickly by selecting the column and using Edit/Replace. Replace / with / (yes, the same character) and this will re-enter every cell with a / in it. If that still leaves...

Excel: Date Formatting, douglas m smith, pete fowler

douglas m smith, pete fowler, change appearance: Hi again, You can do things like that: =AND(C4-TODAY() =0,C4-TODAY() 100) Other logical functions like OR and NOT can also be used to evaluate multiple conditions for single case. AND and OR can handle up to 31 parts. However, you are stuck with...

Excel: Date Formula, august month, july 6th

august month, july 6th, aj: AJ You might use the end of month formula in conjunction withthe NOW formula as follows: =EOMONTH(+NOW(),-1) This formula will result in the date from the end of the previous month. For example if you open file on june 9, 2006, the formula will result...

Excel: Date Formula, cell b1, cell a1

cell b1, cell a1, next box: Claudia You can subtract the start date from the end date and divide by 7. for example Assume start date in cell A1 is 12/01/06 and end date in cell B1 is 12/30/06 The formula would be =(B1-A1)/7 The result would be 4.29 Or 4.29 weeks...

Excel: Date Formula, pay stubs, cell references

pay stubs, cell references, b12: Lisa You can use an IFTHEN formula to determine which date you calculate from, such as the following. =IF(A12 39083,B12-39083,B12-A12) In this formula A12 is the hire date and B12 is the payroll date. The number 39083 is the number representing...

Excel: Date Formula, cell a1, rember

cell a1, rember, first friday: Hi Jamey, This formula assumes that the date that you are checking in a cell is in cell A1. It also assumes that if you subtract 11 days from that date and it lands on a Friday it will give you the date of the following Friday. Rember to format the cell...

Excel: Date file created, scripting runtime, menu tools

scripting runtime, menu tools, c test: From within any module in the Visual Basic Editor, choose from the main menu Tools, References and make sure that Microsoft Scripting Runtime is checked. Then create this function: Function getFileDate(strFileName As String) As Date Dim fso As Scripting.FileSystemObject...

Excel: Date format 2 questions, range al, quaters

range al, quaters, excel sheet: Ok. The code works as it should based on your description. However, I assume you do not want to use TODAY() or NOW() because they return today s date of November 14, 2006 (where I am). so therefore it will return 0611 (November =11, 06 = 2006 and Q1 November...

Excel: Date format masks, date validation, input masks

date validation, input masks, text boxes: The more detailed explanation does help. But, you did not say how you pass the dates entered in the two textboxes to the two date variables and how you do the calculation to determine if the two dates are within the required 7 day period. I assume you know...

Excel: "Date" format not working on the cells, yy format, blank cell

yy format, blank cell, excel workbook: I need to see your workbook to know for sure. You can send it to anne at office articles dot com. It seems as if the cells are formatted as text. Can you select any blank cell, and hit copy. Select your date cells, hit Edit-- Paste special, and choose ADD....

Excel: Date formats, regional settings, date formats

regional settings, date formats, yy: Possibly neither. It s the regional settings in the control panel. So you probably have different settings. You can enter january 11, 2007 and format it as m/d/yy and see what you get -- in europe, this would show as d/m/yy. So in any case it s not from within...

Excel: Date formats, dddd d mmmm yyyy, variable types

dddd d mmmm yyyy, variable types, text boxes: When values are entered into cells, Excel does it best to interpret what the user means when the value is not clear - this means that it will often get it wrong! For dates, they should always be entered in a clear format (eg mmm dd yyyy) - the format of the...

Excel: Date formatting in user form text boxes, type mismatch error, demo purposes

type mismatch error, demo purposes, mm ss: HI Rick I think I see the source of the problem In your Private Sub cboSTART_Click() you set X = start time and correctly assign the value to NTM_START_TIME.Value = X The this sub ends ; at that pouint you loose the value of X. Varilable values...

Excel: Date formatting, current date, thanks for your help

current date, thanks for your help, cells: The formula =Today() Will return the current date the formula =NOW() will return the current date and time The date will re-calculate whenever Excel calculates cells, and can be formatted as any other cell (format, cell, number and choose...

Excel: Date formula, edate, f4

edate, f4, f2: Lisa - You ll need two formulas for this. First, lets start out with your first two dates: Cell# Data F1 9/15/07 F2 9/30/07 The next two cells hold formulas: F3 =EDATE(F1,1) F4 =EOMONTH(F2,1) EDATE takes the date in cell...

Excel: Date formula, bottom right, a3

bottom right, a3, cells: Sy In A3 type in =A2+7. Go to the fill button on the bottom right of cell A3 and drag down the amount of cells you want every 7 days to show in. Select all the cells and change the formatting to date and you have the date incremented by 7 days in each...

Excel: Date generating, yyyy, dd

yyyy, dd, logic: If I have understood correctly, a script like this should do what you require: Sub PrintDates() Dim startDate, endDate As Date Dim i, j, k As Integer startDate = CDate(InputBox( Enter the start date )) endDate = CDate(InputBox(...

Excel: Date insertion in worksheet, vba programmer, excel user

vba programmer, excel user, macro code: Hi, Linda, There s not a built-in function that will accomplish this, but you can definitely do it with just a tiny bit of macro code. A small macro can be installed to update the date cell only during the save process. I m not a VBA programmer, but...

Excel: Date Modifcation for a cell, visual basic for applications, hatchery

visual basic for applications, hatchery, chickens: If you don t know how to use Visual Basic for Applications (vba) code, then you can do this manually: first, enter =NOW() into the cell, formatting it to show the date/time as you like. Then, from the main Excel menu, choose Edit, Copy, Edit, PasteSpecial,...

Excel: Last Date Occurrence, vlookup, rng

vlookup, rng, last date: Example: your dates are in A1:A7, and amounts in B1:B7. Create the following vba function: Function LASTDATE(rng As Range, iYear As Integer) As Double Dim cl As Range Dim maxDate As Date maxDate = DateSerial(1900, 1, 1) Set cl = rng.Cells(rng.Rows.Count,...

Excel: Date question, interest rate calculations, maturity date

interest rate calculations, maturity date, quarterly payment: Jane Sorry for being late, I just got your question tonight. I don t use excel for payment or interest rate calculations,I use another program, so I m not very familiar with the formulas. I suggest you reask the question of another allexperts person...

Excel: Date Range in Cell, initial cell, excel 2000

initial cell, excel 2000: Charles The way I usually handle this is with a macro that copies the date from the initial cell pnd uses paste special to paste the date in another cell. In the second cell you use the paste special/values feature to paste and thereby paste only the...

Excel: Date Range by Month, cell c1, false values

cell c1, false values, time span: For this example, start your dates in row 4, so for instance cell A4 would be 10/2/2006, B4 is 12/23/2006, A5 is 9/30/2006, etc, etc. Col C will be for computing Sep06, so put a 9 (Sept is month #9) in cell C1 and 2006 in C2. Then in D1:D2 put 10 and 2006,...

Excel: Date Ranges in Excel, excel spreadsheet, number ranges

excel spreadsheet, number ranges, address thanks: Please send the spreadsheet to Lotus@swbell.net and I ll make the below changes (or you can try yourself). Re: #1 - I believe the easiest way to do this is to convert everything to text...you can do this by adding a new column with a formula such as the...

Excel: Date Ranges, weekdays

weekdays: Depends on how it is you want to exclude them. Counting days between 2 dates? Filling dates with the fill handle? (That one is right-mouse drag the fill handle and you ll see an option when you let go of the mouse to fill weekdays). Tell me how you want to...

Excel: Date recognition in Excel, cumulative value, cell b1

cumulative value, cell b1, dear sir: Hi Jiblal, =SUMIF(Sheet2!A1:A1000,B1,Sheet2!B1:B1000) The first part of the formula SUMIF(Sheet2!A1:A10, is where the different dates in Sheet2 would be The next part B1, would be the cell B1 on Sheet1, this would be the given date you are...

Excel: Date reversal, dd mm yy, control panel settings

dd mm yy, control panel settings, douglas m smith: Hi Leon, Even if the cell is formatted yy/mm/dd, you still enter it as m/d/y. This order is defined by the Windows Regional and Language Options control panel settings. Examples: the English(US) setting for dates is m/d/y, English(UK) is d/m/y. You can...

Excel: Date Sorting, marvin hill, tues 9am

marvin hill, tues 9am, session logs: If the trainers initials are in row 1 of workbook B, then the formula for the 2 in workbook B for Bill Smith should be: =SUMPRODUCT((MONTH( [Book2]Bill Smith !$B$2:$B$100)=9)*( [Book2]Bill Smith !$E$2:$E$100=B$1)) and this can be filled right. If you...

Excel: Date Stamp, cell b2, time stamps

cell b2, time stamps, circular reference: The formula that you have here will return FALSE when the value of A4 is equal to Closed (the result of comparing the value on L4 with the result of NOW()), and empty otherwise, so we will need to change it. In order to use the circular reference timestamp,...

Excel: Date sensitive conditional formatting, conditional formatting, ogilvy

conditional formatting, ogilvy, current date: Hello Bryan, You say all dates that fall before 31 days before the date on the spreadsheet. What does that mean? Do you mean when the current date (today()) is approaching the date in the cell and is within 31 days before the date in the cell, you...

Excel: Date stamp, cell b1, cell a1

cell b1, cell a1, module menu: Sorry for not being able to solve it. The only thing that I can think on now is to check if the worksheet events works, creating it manually on the Visual Basic editor, but it wouldn t explain why the sample didn t work. In any case, thank you for the question,...

Excel: Date and Time formula's, cell b1, cell a1

cell b1, cell a1, cell c1: Hi Leon, 1st Question: My questions are 1st in cell A1 I have 5/18/05 22:00 and in cell B1 I have =now(), Is there a formula to count the days and time elapsed. ANSWER: in cell C1 type this formula: =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1) MONTH(A1),AND(MONTH(B1)=MONTH(A1),DAY(A2)...

Excel: Date &Time (- minus) Date &Time!!, eally, accurate results

eally, accurate results, vba: Im confused. Your example seems to imply that the Output area should be what s LEFT in A after the items in Exceptions are removed from it, yet your resultant example indicates something entirely different. Also, I d need to know if all this Date area eally...

Excel: Date &Time (- minus) Date &Time!!, ascending order, date time

ascending order, date time, exceptions: This is an interesting question, but unfortunately it comes in a bad timing for me. I am about to go to bed, and tomorrow early morning I am traveling for the whole weekend, with no access to computers. If you don t mind waiting until Monday, I will take...

Excel: Date &Time (- minus) Date &Time!!, time question, desired results

time question, desired results, ascending order: A formula-based approach will work but will not be practical because you will need to kep copying and pasting formulas as the lists grow. Two solutions are to try to use Data, Filter, Advanced Filter (and this may not be practical either) or to wrtie a macro...

Excel: Date &Time (- minus) Date &Time!!, hidden columns, stars2

hidden columns, stars2, date time: I have done a complete redesign of the spreadsheet. Here is the new version: http://home.swbell.net/nate-sus/excel/stars2.xls Here is how it works... 1) There are some hidden columns that perform some calculations - mostly to ensure the start and end...

Excel: Date/Time Stamp, microsoft office applications, up arrow

microsoft office applications, up arrow, shortcut key: Record a macro that: 1. Type =NOW() into the cell and hit Enter. 2. Hit the Up arrow to go back to the cell. 3. Hit Ctrl+C. 4. Hit Alt, E, S, V, Enter, Esc. Stop recording the macro. Assign your macro to a shortcut key such as Alt+D. Record the macro:...

Excel: Date table, integer portion, time dave

integer portion, time dave, time format: Dave, please excuse the delay. I usually answer more quickly, but in this case, I wanted to confirm that my answer was really the best one to use. I found out that it was. Of course it s extremely easy in Excel to do a lookup, that is, find an entry in...