option button, vba, q3: Just to clarify , all the reply books arrive by email and have the same name ? Also are you going to open ans save the replies as they arrive? if so why not individually number them at some point. Hi I havent hear dfrom you so I will assume you have...

Excel: auto update workbook, e mail, vba

e mail, vba: This amended code will place all reqired data into the table Option Explicit Sub ImportAnswer() Dim wbTarget As Workbook Dim wbSource As Workbook Dim rClTo As Range Dim sFil As String Dim sTitle As String Dim sWb As...

Excel: auto update workbook, aidan, heritage

aidan, heritage, virgin: If I understand the question correctly, you will be getting workbooks sent to you which you need to open and somehow use to update a master workbook? This is possible, but would require VBA to achieve - however, I may have totally misunderstood what you are...

Excel: auto update, application level, ogilvy

application level, ogilvy, multiple users: This is an application level setting for each user. You find it under Tools= Options= Edit tab, uncheck Ask to Update Automatic Links . Then the links will be updated silently. Again, this has to be done for each user. -- Regards, Tom Ogilvy...

Excel: auto-updating time on spreadsheet, explorer pane, mm ss

explorer pane, mm ss, autofit: John, The easiest way is to use the changeevent for the worksheet using VBA. If that sounds foreign to you, then it isn t an option for you unless you want someone to write the code for you. I don t see another way to do it. Here is code I would...

Excel: averaging, douglas m smith, countif

douglas m smith, countif, brainbench: Hi Doug, Thanks form thanks! You can create the equivalent of an an AVERAGEIF function to solve this problem: =SUMIF(range, 0 )/COUNTIF(range, 0 ) Douglas M. Smith Brainbench MVP for MS Excel www.brainbench.com www.abundant-solutions.com...

Excel: autofill function, run time error, cell position

run time error, cell position, b2: EndRow must be a variable based on the way you have coded this and ActiveSheet.Range( B2 , ActiveSheet.Range( B2 ).End(xlToRight)).Select does not get the row. I don t see where you have passed any value to EndRow. If you continue to want to...

Excel: autofill hyperlinks, hyperlinks, increment

hyperlinks, increment, hypertext: Greg I do not know of a way to incrementally change the hyperlinks, either by formula in excel or by macro. Having said that I am pretty sure that you can accomplish it by macro, since you can do almost any thing with macro, but I don t have that expertise....

Excel: autofilter on VBA, fwo, excel vba

fwo, excel vba, textboxes: I m not exactly sure what you are doing. Presumably you have a UserForm to set the criterias so try something like Dim rsp As String Dim dc As String Dim fwo As String resp = Me.TextBox1.Value dc = Me.textbox2.Value fwo = Me.textbox3.Value Selection.AutoFilter...

Excel: autofiltering in excel 2000 with very small number, excel sheet, altough

excel sheet, altough, excel 2000: The extra information is useful, as it indicates that the filter itself isn t required - so you would be doing something like for each C in range( C1:C1000 ) if c.value=Min_Low then sheets(1).range( a1 ).offset(counter,0).value=c.value counter=counter+1...

Excel: how to automate excel, john peltier, john walkenbach

john peltier, john walkenbach, stephen bullen: Chip Pearson s (large - multiple) http://www.cpearson.com/excel.htm go to the topics or pages index John Walkenbach (large - multiple - book author) http://www.j-walk.com/ss/excel John Peltier (charting) http://peltiertech.com/ Stephen Bullen:...

Excel: automate 'fill down' in excel, blank cells, cell a1

blank cells, cell a1, excel 2002: I really must learn not to answer questions quickly when I am trying to help someone on the phone at almost the same time - your correction is of course what I should have told you first time round - glad you worked it out!! www.aidanheritage.co.u...

Excel: How to automate finding a list of values from four columns, internal loop, range selection

internal loop, range selection, cell a1: Kris I don t have a copy of Excel 97, but I think the code is basic enough that it should be OK. Not that it isn t complicated, but the code is standard. Anyway, since I don t know all of the details, I am guessing at some of this, but I followed your...

Excel: automatic addition of rows to all worksheets, sheet tabs, visual cues

sheet tabs, visual cues, group mode: Hello, Kathy, There actually is a way to make edits to more than one sheet at once, but it s a little dangerous. I ll explain it to you, but I want to caution you to be careful with it. Here s the way it works: Let s say that you have Sheet1, Sheet2,...

Excel: automatic calculating, j4, cells

j4, cells, keyboard: Jason, I would start by saying the first thing you are doing wrong is not letting Excel make this formula for you. You would have found out, by simply typing =sum( and then selecting the sheet and group of cells on that sheet, then hitting Enter on the keyboard...

Excel: automatic date option in Cells, control toolbox, calendar control

control toolbox, calendar control, z31: You need to set up the valid date list first, perhaps in Z1:Z365 for a year s worth, or Z1:Z31 for a month s worth, and refer to that range in the Validation List. If you want it to vary depending on today s date, you can put =TODAY() in Z1, and in Z2 enter...

Excel: automatic file lookup, vba code, jpg files

vba code, jpg files, c files: Tom, just for starters, I don t think that it s possible to display pdf files in Excel. I may be wrong, but I ve never heard of that. Also, the common hyperlink system will want to open the picture or file in it s native program or with your browser, not...

Excel: automatic link, change source, file excel

change source, file excel, link menu: Ah - OK, that makes a difference - the break link was a feature added in Office XP I think. In that case, it s the old fashioned method - you can either (1) change the source and make the source the current document (2) use search to find the link and...

Excel: automatic progressive invoice numbers, invoice numbers, invoice number

invoice numbers, invoice number, progressive number: Toni, Let s say the invoice cell is A1. You need to put this code in the workbook s _Open event and it will do what you need. To get access to that event, from Excel press Alt+F11 which will start the VBA editor. Once it opens, press CTRL+R to show...

Excel: automatic read in previous .xls file, xls file, microsoft outlook

xls file, microsoft outlook, a10: IF I understand then you need to run some type of loop. For example: Dim X as Integer For X = 1 to 10 If Range( A1 ).Offset(X-1,0) = Something then Do something End If Next X This will cycle thru A1:A10 and check each cell one by one. ...

Excel: automatic time into a cell based on another cell having a date input, keyboard shortcut, macro code

keyboard shortcut, macro code, time column: Hi Ian, You re not dim, macros are probably the most complex part of excel, but once you learn them, they are really cool. Since you haven t used macros before, the first thing you should do is open a new workbook and go to tools/macro/ record new macro....

Excel: How to automatically Format the time, gyula, colon

gyula, colon: you could try: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox1 = Format(TextBox1, hh:mm AM/PM ) End Sub But it might require that you put in the colon (:). If you require your user to always enter HHMMam or HHMMpm...

Excel: automating the daylight saving changeover, eric eric, changeover

eric eric, changeover, ogilvy: Eric, I really can t tell you the answer based on the information provided. It is unclear what you mean by swap columns or how you use the information in the columns for your timetable. Are the results being built with formulas. Just too much unexplained...

Excel: autopopulate from multiple sheets based on criteria, budget spreadsheet, virus free

budget spreadsheet, virus free, security concerns: Sarah: You should not beat me up for your poorly phrased question. I did answer your question. Perhaps you should have phrased your question in such a way that would not elicit a yes/no answer only. Furthermore, since I can not see your code and...

Excel: autopopulate a row if, d1000, expense report

d1000, expense report: Hello Leon, This type of feature is accomplished via a macro versus a formula. I don t have time to work on the macro right now, however. If you still believe you need a macro and would like some assistance with the general thought, let me know. HTH...

Excel: autopopulate row, vba programmer, colum

vba programmer, colum, macros: Hi, Leon, The reason that I asked is that if you only wanted to sum or count the rows, then we could use formulas. If you actually want a list of all of them to print, then you need a little macro to do this. Though I m not a VBA programmer, there are...

Excel: autopopulate row if, a3, cells

a3, cells: Leon I d suggest you use a formula that will link the 37 worksheets with the over 300 worksheet if the amount is over 300. I don t know what your data looks like, but the link for one worksheet would look like this. +IF(Sheet2!A3 300,+Sheet2!A3, ) ...

Excel: average, excel vba, incorrect values

excel vba, incorrect values, eto: Your just averaging the COUNT of the items that meet the condition so you would get the proporiton of the rows in the total range that meet the condition. I think you actually want: Cells(1, 7) = Evaluate( AVERAGE(IF(( ETO !BF3:BF65536 0)*( ETO !AY3:AY65536=...

Excel: average based on multiple columns, spread sheet, dows

spread sheet, dows, price 1: Kris Don t know of a way to include (easily) it in one formula, but it would be relatively simple to put your multiplied cells in another part of your worksheet and then divide by the total of the unit column. This is easy since you can put +A1*B1 in...

Excel: average without blanks or zeros, earliest convenience, zeros

earliest convenience, zeros, jade: Hi Jade, If your data is in cells C2:C11, the following should work =IF(COUNTA(C2:C11) + COUNTBLANK(C2:C11) - (COUNTIF(C2:C11, )+COUNTIF(C2:C11,0))=0, ,SUM(C2:C11)/(COUNTA(C2:C11) + COUNTBLANK(C2:C11) - (COUNTIF(C2:C11, )+COUNTIF(C2:C11,0)))) Hope...

Excel: average a set of grades based on criteria, ansers, period number

ansers, period number, cell address: Hello Rodney: Perhaps: Period Number (1,2,3,4,5 or 6): C2:AZ2 Possible correct: C3:AZ3 Number correct: C4:AZ4 =sumif(C2:AZ2,6,C4:AZ4)/Sumif(C2:AZ2,6,C3:AZ3) The 6 can be replace by a cell address for a cell containing the number 6 or whatever...

Excel: average from sumif, blank cells, blank cell

blank cells, blank cell, sumproduct: =SUMIF(A1:A10, N ,B1:B10)/SUMPRODUCT(--(A1:A10= n ),--(B1:B10 )) there is a difference between a numeric value of zero and a blank cell. I have excluded blank cells in the calculation. Also, you sample data indicated it started in row 1 while your...

Excel: average, b24, b72

b24, b72, cross hair: In cell B24 enter =AVERAGE(A1:A24) Now, Select B1:B24 (B1:B23 are blank). The bottom-right hand corner of B24 is a little darker heavier square (if not, then use tools/options/Edit tab, Allow Cell Drag & Drop ). Double-click this little square (the cursor...

Excel: averaging with blank cells, blank cells, column c

blank cells, column c, set of numbers: OK, then how about this: =IF(COUNT(A1:A3) 0,AVERAGE(A1:A3),0)+IF(COUNT(B1:B3) 0,AVERAGE(B1:B3),0)+IF(COUNT(C1:C3) 0,AVERAGE(C1:C3),0) or, using SUM =SUM(IF(COUNT(A1:A3) 0,AVERAGE(A1:A3)),IF(COUNT(B1:B3) 0,AVERAGE(B1:B3)),IF(COUNT(C1:C3) 0,AVERAGE(C1:C3)))...

Excel: How to avoid this error, hi bob, excel sheet

hi bob, excel sheet, a8: Select the cell, use Format/Conditional Formatting, change Cell value is to formula is , and enter this formula (this assumes the active cell is B5 -- change it to the real active cell): =ISERROR(B5) click Format, then the Font tab, then choose a white...

Excel: How to avoid this error, error values, d8

error values, d8, error value: Because you re dividing by D8-E8, it s an error if D8 and E8 are blank. If they re blank, then D8-E8 is zero, and you can t divide by zero. If you want to show a blank, instead of a zero, in those cases, use this formula: =IF(D8-E8=0, ,B8/(D8-E8)/24) ...

Excel: How to avoid error, douglas m smith, cell a1

douglas m smith, cell a1, brainbench: Hi Jothiswaran, You will need to test if cells A1 or B1 are blank in the C1 formula. Something like this: =IF(OR(ISBLANK(A1),ISBLANK(B1)),0,your formula) If either A1 or B1 is blank, then the result in C1 will be zero (or whatever you want it to...

Excel: Backup files, excel vba, awb

excel vba, awb, backup files: Sorry, I assumed a basic knowledge of Excel VBA. The code requires a folder called MyBackups in this case on Drive D. You could change it to maybe C:MyDocumentsMyBackups and keep the folder in My Documents. Open your Workbook, then using Alt & F11...

Excel: Backup and Restore, t claim, blank file

t claim, blank file, zip program: Zip files require a zip program to unzip them - so POSSIBLY you haven t got an unzip program at work - one way round this is to create a self-extracting file from the zip. Excel itself doesn t zip files, so cannot read the zip, nor can it right to the zip...

Excel: Backup and Restore, writable cd, modern computers

writable cd, modern computers, cd drive: Most modern computers have a Writable CD drive. If you have windows XP as your operating system it should have the capability to setup you writable CD drive so you can drag and drop file onto it the same as any drive. If you don t have XP, then you drive...

Excel: Backwards Compatibility Problem, feature checks, compatibility problem

feature checks, compatibility problem, vba code: Ken This is definitely an issue with using different versions, especially if you have VBA code that was recorded in 2003. 2003 added some functionality which changes - usually adds - to the string of code that is recorded while performing specific tasks....

Excel: Bank Statement totals, excel 2003 bible, relevant chapters

excel 2003 bible, relevant chapters, first day of the week: Duncan Assume your dates are in column A and in column B are your amounts. In another column, assume I will put the first day of the week. For illustration, I will pick F2 for the date. I will start my weeks on Monday, so for 2006, I will put 01/02/2006...

Excel: Barcode, cell c2, cell b2

cell c2, cell b2, scanner scans: Hello Charlie, Your sample item number is 10 digits, but whatever the count is I believe this approach is what you want: If on sheet2 you have in columns A and B Cell A1: Item# Cell B2: Descriptions cell A2: WT7100089B cell B2: Woelcotherm...

Excel: Two Basic Questions, assistance tool, response one

assistance tool, response one, basic questions: You ll need a formula. If the above 4 3 2 1 is in A1:D1, then enter in A2, for example: =INDEX($A$1:$D$1,5-COLUMN()) then fill right. If the original #s were A1:Z1, then enter =INDEX($A$1:$Z$1,27-COLUMN()) and fill right. Once done, you can copy this formula,...

Excel: Beginning VBA, range names, pears

range names, pears, bananas: This doesn t require vba, but as a learning exercise, you can look at these two codes. They assume that the range names Apples, Bananas, Pears, and Fruit have already been defined. Sub Macro1() range( fruit ).FormulaR1C1 = =apples&bananas&pears ...

Excel: Beginning VBA, names of apples, concatenation operator

names of apples, concatenation operator, macro language: Hello Josh If you add on .Value to a name, that means it has to be a number, right? NO. It the value of the cell is a string, it refers to a string. If the value of the cell is a number, then it refers to a number. In any event, the concatenation operator...

Excel: Beginning VBA, range names, pears

range names, pears, bananas: 1) Right, first example places a formula into the cell, 2nd example places the value. 2) I put in .value for clarity, but it s not necessary. The macro will work fine if you drop the .value 3) range( fruit ) = [whatever] actually places the value into...

Excel: Beginning VBA, names of apples, concatenation

names of apples, concatenation, ogilvy: Hello Josh, Assuming you have used Insert= Name= Define to create defined names of Apples, Oranges, Bananas and Fruit and these defined names refer to individual cells, then I would do Range( Fruit ).Value = Range( Apples ).Value & , & _ Range(...

Excel: How Best to Learn And Succeed, lotus 123, excel development

lotus 123, excel development, power programming: The best books are VBA Developer s Handbook by Getz & Gilbert; Professional Excel Development by Bovey, Bullen, Green; Excel 2003 Power Programming by Walkenbach;. Look at http://www.garyradley.com/vba/ and maybe http://office.microsoft.com/en-us/assistance/CH790018021033.aspx....

Excel: Best way to forecast individual sales territories?, techonthenet, excel formulas

techonthenet, excel formulas, sales territories: Bill The best method is what works best for you, I would suggest looking at either forecast function or the growth function Here is a link explaing the forecast function http://www.techonthenet.com/excel/formulas/forecast.htm and here is the growth...

Excel: Best way to forecast individual sales territories?, time series forecasting, google

time series forecasting, google, linear trend: they write books on that and consultants make their living on answering questions like that. There is no simple answer. If you want to assume a simple linear trend, then you can use the Trend or Forecast functions. If you want something non-linear...

Excel: Best way to forecast individual sales territories?, sales territories, forecast sales

sales territories, forecast sales, microsoft site: Hi Bill, Excel has the Forcast function that you can try. I have not made a forecast worksheet before. But if I had to I would try and find another template that someone has made. I always try not to reinvent the wheel so to speak. Here is what I found....

Excel: Best way to plot delivery times data, excel spreadsheet, delivery times

excel spreadsheet, delivery times, inv: I would suggest that you create a line chart of the three dates. Having three lines going across the chart is a classic and very helpful way of showing comparisons. There s really no way to have three *separate* comparisons in one single chart. If you try...

Excel: Bill, Let me give it a..., shipment weight, correct response

shipment weight, correct response, destination state: Assuming thr weight is in A1 and the state is in A2, then: =IF(AND(A1 100,A1 200,OR(UPPER(TRIM(A2)= NC ),UPPER(TRIM(A2)= CA ),UPPER(TRIM(A2)= FL ))),21.17,17.5) UPPER and TRIM functions have been added to help assure that the formula works in case...

Excel: Billing Categories, source button, pivot table

source button, pivot table, educational consultant: The SUMIF function will sum values based on a condition, so =sumif(A:A, Prep , B:B ) would sum all the values in column B if the value in column A is prep. HOWEVER, a better solution would be to use the data menu, then pivot table and chart report -...

Excel: BLANK CELLS, blank cells, miscommunication

blank cells, miscommunication, thanks in advance: Yes. The best way in my opinion is to use the ISERROR function with your IF statement. ISERROR evaluates a formula to see if that formula creates an error. So, see if this works IF(ISERROR(A2+B2), ,A2+B2) If A2+B2 creates any error then display nothing....

Excel: BLANK ROM PROBLEM, asap utilities, blank rows

asap utilities, blank rows, false application: Hi Elmer, You can do this a couple of ways. First of all save the workbook and name it something like testcopy workbook so that you will still have your original workbook just in case something goes wrong. 1. Select the following Macro and copy it....

Excel: Blank cells still plot as zero in chart after processed through 'IF' formula, blank cells, empty cells

blank cells, empty cells, blank cell: The if statement is working correctly, but the cell ceases to have a null value as it has a blank string, which is evaluated to zero - I would do this by using the method documented here http://www.andypope.info/charts/piezeros.htm This has an example...

Excel: Blank cells still plot as zero in chart after processed through 'IF' formula, blank cells, empty cells

blank cells, empty cells, blank cell: A cell containing is not the same as an empty cell. Try this: enter 1 2 (empty) 4 5 and chart it with a line chart. You see a gap. In the empty cell enter = and you see the line drop to the axis. Now put =NA() instead of = and you see the line continue....

Excel: Blank Spaces, empty cells, excel sheet

empty cells, excel sheet, sh1: Hi, try to run the code below on the worksheet you want converted. Make sure you have a copy of your original, just in case. This code moves all rows with empty cells (even if there is only one empty cell in that row) into a worksheet called Temp. Sub...

Excel: Blank Spaces, blank cell, excel sheet

blank cell, excel sheet, blank spaces: If it s the first 10 rows you want to check (you didn t specify whether or not you wanted the rows which are being pasted to another sheet cleared from the copied sheet or not, so i m assuming you don t -- that it, the original sheet will look the same as...

Excel: Blank Spaces, blank cells, blank cell

blank cells, blank cell, excel sheet: The question isn t precise enough for me to know exactly what you want, so I ll take a guess, and make a suggestion. Say for example you have data in the range A2:F11. You want to find which of those rows (A2:F2, A3:F3, ... etc) include any blank cells,...

Excel: Block input, worksheet menu bar, excel objects

worksheet menu bar, excel objects, true application: Hi Ryan, To get ZabraniInput_BlockInput to run whe the workbook first opens, simply place the following code in the ThisWorkbook code module: Private Sub Workbook_Open() ZabraniInput_BlockInput End Sub To open the ThisWorkbook code module, right-click...

Excel: Blocking cells and working with automatic filters, microsoft excel 2000, using microsoft excel 2000

microsoft excel 2000, using microsoft excel 2000, chip pearson: Assume your talking about Autofilter under the Data= Filter= Autofilter menu. Unprotect the sheet Apply your filter so dropdown arrows are showing. You don t need to actually select any conditions. Now protect your sheet go to the Workbook_Open...

Excel: Blocking cells, entry cells, blank areas

entry cells, blank areas, necessary fields: I don t know a way to do this in Excel. Some things I have done to try to ensure users fill out necessary fields: 1) Color code the data entry fields so that they are easy to recognize. 2) Be sure the worksheet/form is protected and that only the...

Excel: Blocking out combo box selections, combo boxes, combo box

combo boxes, combo box, users input: John, I don t want to get your hopes up, but it should be possible to have one selection force a list for the next selection. The first concern I have though is the use of Combo Boxes. I don t like them because they allow the user to enter a value that does...

Excel: Blocking out options, norwich union, input cell

norwich union, input cell, countif: I m not aware of any ways of disallowing inputs like this directly, BUT you could have the list set to show different selections depending upon choice - I do have an example of this here with me but of course I cannot attach it to AllExperts - BASICALLY, the...

Excel: About Bob mixup yesterday., mixup, miscommunication

mixup, miscommunication, email accounts: I charge $65 per hour for my services and have a minimum charge of 3 hours, which is intended to discourage little projects. If you are still interested in wanting me to help you then send me the code I already provided you, the workbook, and a DETAILED...

Excel: Bonus Calculation, financial result, percentage calculations

financial result, percentage calculations, additional detail: http://home.swbell.net/nate-sus/excel/profit.xls I have uploaded a sample spreadsheet to the above location. I calculated two different percentages. One fits in with your 25-50% range...the other one seemed to make more sense to me (but that is probably...

Excel: Bonus Pay out, maximum payout, necessary thanks

maximum payout, necessary thanks, even distribution: Anoop: I presume, since you didn t specify, that the formula that works is this one: =+MAX(0,(number of points-50)/50*0.18) Lets say the person has exactly 100 points...I m going to do the math manually here... =MAX(0,(100-50)/50*0.18) =MAX(0,50/50*0.18)...

Excel: Break Even analysis charts, chart wizard button, douglas m smith

chart wizard button, douglas m smith, break even analysis chart: Hi Brenden, You will to set a column of inputs and a column of results. For example, cells A1:A10 would contain a range of sales volumes. Cells B1:B10 would contain the expected profit or loss (either entered as numbers or calculated by formula) based...

Excel: Break mode, microsoft query, query range

microsoft query, query range, code execution: Since A1 gets selected then it seems that the Macro8 is the culprit. What is in it? Or, how did you create the query, with the wizard? In the wizard there are options to Return Data To Excel and View the Data or Edit the Query . Which one was selected?...

Excel: Breakpoints, variable input, breakpoint

variable input, breakpoint, derivatives: Hi Ali I think I need you to explain what you require a bit moor expansively. What is a breakpoint ? I havent heard the term before and what criteria govern the standard ? values in c1:c5 and the derivatives in range D1:d5 of = . If sound like you...

Excel: Bringing a PDF file into Excel, optical character recognition, path of least resistance

optical character recognition, path of least resistance, useable format: Hi Julia, It depends on the protection scheme used on the PDF file. On some, you can select & copy or drag & drop the data directly into an Excel worksheet. You will probably have to do a little clean up too. However, if the PDF has a higher level...

Excel: Bringing select cells from other worksheets, ogilvy, cells

ogilvy, cells: this can be caused if the source cell 1 !K4 is formatted as text. If you remove the text formatting it should work. You can also select the cell with the formula and one other cell and do Edit= Replace what: = with: = this will often cause...

Excel: Browse for a folder, target directory, dirlistbox

target directory, dirlistbox, directory location: Hello Terence In Excel VBA you can show the normal save dialogue box with the following code, this may be of use to you. With Application.Dialogs(xlDialogSaveAs).Show End With It is also possible to load a complete list of directories...

Excel: Bubble Charts in Excel, bubble charts, excel user

bubble charts, excel user, sunday monday: sorry I didn t get back to you sooner -- can you email me a sample workbook with what your current result is and I ll be able to help -- much easier to show than explain. Please send to me at bobumlas@yahoo.com. However, I may not be able to get to this until...

Excel: Budget Table, budget table, countif

budget table, countif, cell reference: Yes, but switch your columns around so desc is in column B and expense in C. Then use =SUMIF(B:B, Electric ,C:C) or similar. See my article: http://www.officearticles.com/excel/sumif_using_a_cell_reference_as_criteria_in_microsoft_excel.htm I like to put...

Excel: Budget, combo boxes, time periods

combo boxes, time periods, dollar amounts: James: First off, here is a sample budget I prepared for someone last month. It may or may not have any useful formulas or ideas in it – I just wanted to drop a link so that you can review it if you want. http://home.swbell.net/nate-sus/excel/samplebudget.xls...

Excel: Budgeting, budget database, budget data

budget database, budget data, spread sheet: Troy, you say the account numbers are in column B on the database sheet and then you say to search column A to find the numbers. I will assume the numbers on on column B of a sheet named Database and the amounts are in column U of that sheet. Assume you...

Excel: Building a form, alphabetical name, vb environment

alphabetical name, vb environment, windows xp home: I don t know how you can use Excel WITHOUT having access to visual basic! What do you mean it did not open -- it s not something you OPEN, it s something you access - Alt/F11 should get you to the VB environment. If you really want to use a form, this is...

Excel: Building Menu on the fly, public const, mymenu

public const, mymenu, dear bill: You will have to run the routine above each time your user selects a different sheet but attaching the code to Workbook_SheetChange event. The code above will have to be modified so that each sheet s menu is activated when the sheet has been selected. ...

Excel: Business Chart, douglas m smith, secondary axis

douglas m smith, secondary axis, chart wizard: Hi April, Generally, you need to have your data set up in a structured table, with labels. The Chart Wizard is quite good at interpreting the data but doesn t always come up with what you want or how you want it. I don t know how your data is setup so...

Excel: Button Color Change after Click, colour option, command button

colour option, command button, button color: It depends on the command button - there are various ways of inserting them - MOST do not have a colour option, so you cannot change it. If there is a colour option, then just reference that button and set the colour as required (you can record a macro to...

Excel: Button to delete enter information, button cell, spreadsheet

button cell, spreadsheet, budgets: I assume you mean a budget WORKBOOK, when you say I received a budget ... . After clearing up that, what is your question. All you told me is what you need or would like to have. I assume the feature is a macro that is run when the button is pushed....

Excel: Buttons/if one cell is x then the next is y, perfomance evaluation, happy memories

perfomance evaluation, happy memories, mac os 10: Hi Peter, Do itashimashite. We had Japanese students stay at our house for three weeks at a time. My parents did this once a year for about eight years when I was young. I have many happy memories of the Japanese students staying and laughing with them....

Excel: Buttons in a macro, cell a1, push buttons

cell a1, push buttons, msgbox: Borko, This routine will insert a drawing object (in this case a square / rectangle) in the vicinity of cell D27, then assigns it a previously created macro named Macro1 , then returns focus of the file to cell A1. The numbers inside the parenthesis of...

Excel: basic excel VBA, c cells, blank row

c cells, blank row, basic excel: Hi James how are you? I think that something like Sub InsertRows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long, c numRows = 1 r = Cells(Rows.Count, A ).End(xlUp).Row c = Cells(r, 1).End(xlToRight).Column - 1 For i...

Excel: A basic question regarding data sorting., example aa, monthly expense

example aa, monthly expense, pivot table: The layout that you have chosen for this spreadsheet means that you are going to have to work quite hard to achieve the results you want - my own preference would have been to have had a maximum of three worksheets (income/expense/balance) and probably only...

Excel: very basic stuff, inventory code, inventory item

inventory code, inventory item, pearl size: Yes this can be done but based on your stated level of EXCEL expertise then it will be a bit overwhelming for you. Basically you need to create a table or tables that represent what each part of the entire inventory code represents, like KH (kuta hanging)...

Excel: Very basic Vlookup ques, vice president sales, nancy sales

vice president sales, nancy sales, workable solution: I don t understand the question! The example is an example of a lookup that would seem to work, it doesn t explain why it wouldn t work - I m ASSUMING that an OFFSET is probably going to be the solution you need, but as I don t understand what the problem...

Excel: very basic!!!, inventory code, inventory listings

inventory code, inventory listings, question thanks: Sonya Not as basic as you might think, this will involve creating a master list of all your items, then split the inventory item and then using a vlookup or similar option to access the inventory particulars. You would do something like this On a...

Excel: big help, spreadsheet template, section selection

spreadsheet template, section selection, first selection: Steve If I m understanding the problem correctly I believe that you can do what you need with the recording of simple macros. You can record the macros just by making the keystrokes necessary to produce the desired result. To record the macro go to toolbar/tools/macro/record...

Excel: bipassing security settings, digital signature, defeating the purpose

digital signature, defeating the purpose, brainbench: Cheryl, There may be a couple of reasons why your Excel keeps reverting to high security. The most common reason is that there is a network policy that Excel (and all other Office programs) adhere to. To change the policy, you would need to talk to your...

Excel: blank cell getting ignored, blank cells, cell a1

blank cells, cell a1, cell a2: Well, that s what End(xlUp) does. So, I am not sure I understand your question. You will not be able to use End(xlUp) if you want to ignore blank/empty cells. You will need another way to determine where you get data from and/or where you want to paste...

Excel: blank cell getting ignored, blank cells, cell a1

blank cells, cell a1, cell a2: The reason may be on the offset that you do while pasting overwrites the offset with the active cell. As you are doing the copy row by row, you could change the offsets to something like this: Dim rngWks as Range Dim i as Integer Set rngWks...

Excel: blank cell getting ignored, blank cells, cell a1

blank cells, cell a1, cell a2: A very long and complicated macro - try this method Dim N As Long N = Sheets( Sheet3 ).Cells.SpecialCells(xlCellTypeLastCell).Row Sheets( Sheet3 ).Range( A1:E15 ).Offset(N, 0).Value = ActiveSheet.Range( A1:E15 ).Value Sheet3 in this instance is the...

Excel: blank cell getting ignored, blank cells, cell a1

blank cells, cell a1, cell a2: I can t tell from the layout you ve given me what you are really trying to do, and I also don t follow your code -- your use of Activecell as a variable name is confusing. Here are a few independent ideas: 1) change blanks to ZZZ before the procedure, and...

Excel: blank cell getting ignored, blank cells, cell a1

blank cells, cell a1, cell a2: Hi Hari How are you? I am sorry I do not know exactly what you want to do. I assume that you do not want to copy the blank cells. To simplify my answer, I also assume that you want to copy from sheet1 to sheet2 of the same workbook Please take a...

Excel: blank cells and zero's, randall chapman, column locks

randall chapman, column locks, blank cells: Hi Randall, Sorry I took so long I was busy with the holidays. You can download a workbook here. http://www.squish.com/cloverleaf/unhidecolumns.xls You can use the following macros to unhide or hide your columns. You will need to change the columns...

Excel: blanks, blank cells, blank cell

blank cells, blank cell, excel 97: Michael I am assuming you are getting one when there are two blank cells being referenced, this formula should solve the problem. =IF(B12= ,0,IF(B12=C12,1,0)) The indicates a blank cell, as you only need to know if one cell is blank to create...

Excel: blink, flash schedule, cell a1

flash schedule, cell a1, colorindex: Hi Marvin, You can paste a little bit of code and get that to happen. Here s how. This comes from John Walkenbauch s website, an Excel expert. http://j-walk.com/ss/excel/eee/eee002.txt Open a blank workbook. Go to the Excel menu and select Format...

Excel: blinking cells, dropdown menus, drop down boxes

dropdown menus, drop down boxes, vba code: From the main Excel menu, choose tools, macro, visual basic editor (VBE). This opens the VBE in a separate window. If necessary, from the VBE menu hit view, project explorer. This project explorer is one of the windows you can open within the VBE. It...

Excel: bold, microsoft excel 2000, using microsoft excel 2000

microsoft excel 2000, using microsoft excel 2000, bold print: gdelmez Prolly not something that I can be of much help on. Ocassionally I will get the same type of problem, not that one specifically, but similar. Sometimes it helps to reformat the page, eliminate all formatting and then reformat back to what you...

Excel: box in excel 2007 or 2003, chip pearson, data validation

chip pearson, data validation, ogilvy: shanaj, You would need a macro to do the first item. You would use the change event to update the adjacent cell with the date and time. See Chip Pearson s page on Events to get started: http://www.cpearson.com/Excel/Events.aspx You can look here...

Excel: button or drop down list with color, visual basic programming, color cells

visual basic programming, color cells, drop down boxes: http://home.swbell.net/nate-sus/excel/colors2.xls The above spreadsheet contains an example of what I came up with. It uses a combination of a drop-down box (you can create drop down boxes with the forms toolbar and then edit the properties of the box...

Excel: button or drop down list with color, color shading, conditional formatting

color shading, conditional formatting, format menu: I can t help you change a cell s format by clicking on it. The only thing related to this is to use Conditional Formatting which is an option under the Format menu on the main toolbar. With Conditional Formatting you can change a cell s format (color,...

Excel: button formatting, cell d5, combobox

cell d5, combobox, inputted: Hi Brad, I think I understand your question. I am going to assume that you are using a combobox obtained from the Controls Toolbox toolbar (you can also do this with a combobox from the Forms toolbar, but the code would be a bit different). The combobox...

Excel: button picture, bob phillips, sheet technology

bob phillips, sheet technology, left arrow: As I said, I have forgotten most all I knew about the code that is that old since it is still version 5 code. I would suggest using userforms, which replace the dialog sheets. Dialog sheet technology is about 12 years old and is RARELY used anymore...

Excel: buttons, command button, new button

command button, new button, gdl: I assumed and hopefully concluded correctly you are putting a command button on a userform named GetDataForm - you did not say. Please remember I can not see what you see or know what you are thinking. ALL and EVERYTHING I know about your project is ONLY...

Excel: buttons or checkboxes and totaling, employee names, cb1

employee names, cb1, excel sheet: Hi, open a blank workbook and insert a module. Copy and paste the code from below. If you run it, it will place checkboxes from B2 to H10 (changeable) and it will put in column I the formula. Now if you check the checkbox, it totals the true values. ...

Excel: buttons, commandbutton, command buttons

commandbutton, command buttons, option buttons: Instead of Dim newbutton As MSForms.CommandButton you can use Dim newbutton As Object And yes, the code I gave earlier assumes that there s a form named UserForm1 that you re adding the CommandButton to. If the form you ve created has a different...

Excel: Cache link into Excel spreadsheet, hi aaron, excel spreadsheet

hi aaron, excel spreadsheet, google: I know what you re talking about, but I haven t used it personally. Not that it s a bad feature, just haven t had to. If you google on Excel Cache Link you ll find some resources to help you along. A quick scan turned up a few hits related to pivot tables...

Excel: Calc Depreciation by Quarters, cell b2, capital purchases

cell b2, capital purchases, cell a1: Chris: I have uploaded a sample spreadsheet here: http://www.snapdrive.net/files/487599/depr.xls I hid some columns (B through M) in the spreadsheet so the formulas didn t run into problems when calculating the previous 12 quarters of depreciation...

Excel: Calcuation for Turnaround time, turnaround times, turnaround time

turnaround times, turnaround time, military time: Connie Assuming that the table is set up like this Registration in Column A Exam column B an Report colum C In Column D you would have turnaround times. the formula for that is =B2-C2 wiht a time format in the cell. So in the instance above the...

Excel: Calculate 4 week periods between dates, 6th august, ogilvy

6th august, ogilvy, increment: Roger, I guess I am not following what you want. If I replace the date in A1 with =today(), then your formula returns 6.25 I am not sure how the 2 you show factors in or what you actually want to do? If you want to send me some more information or...

Excel: Calculate the begin- and endDate of a week (when date is given) XLS97, chip pearson, cpearson

chip pearson, cpearson, ogilvy: Here is information on week numbers (calender week is not self defining unfortunately). http://www.cpearson.com/excel/weeknum.htm This is Chip Pearson s site. Here is a link to Chip s top level page on Dates and Times: http://www.cpearson.com/excel/datetime.htm...

Excel: Calculate a date with conditions, second date, holiday weekends

second date, holiday weekends, workdays: Not sure how you re identifying holidays, but as for weekends, this formula will add 20 days to Date#1 & adjust for weekends. the leading 20 is your add number; you can make that a 10 or 30 or whatever. Maybe this is enough to get you started to adjust for...

Excel: Calculate deadlines, skipping weekends?, saturday and sunday, due date

saturday and sunday, due date, grabs: Hi Alison, What you need is a couple of IF statements along with the WEEKDAY function to evaluate whether the date will land on a Saturday or Sunday. The WEEKDAY function works by looking at the date and assiging a number to it. For instance the date 12/9/07...

Excel: Calculate entire spreadshet, office 2000, spreadsheet

office 2000, spreadsheet, thanks for your help: Marc I don t know of a way to automatically add the calculation when you add another sheet. Maybe as an alternative you create enough sheets to handle the problem initially. Create the formula in the first sheet then copy to all other sheets the full...

Excel: Calculate worksheet, array formulas, programming question

array formulas, programming question, manual tools: I will assume this is not a programming question. You can set calculation to manual (tools= options= calculation tab, choose manual.) After setting to manual calculation, then If you want to calculate a cell then select the cell you want to calculate,...

Excel: Calculate Year to Date and Quarter to Date, mtd, ytd

mtd, ytd, column headers: A few assumptions. 1) the column headers (Jan05...) are in row 1 (and are real excel dates, not text Jan05, but 1/1/05 FORMATTED any way you want). 2) the date you have entered in a cell is A10 (for this example s formulas). 3) the row of the #s to calculate...

Excel: Calculating averages, budget numbers, t effect

budget numbers, t effect, b15: Still not clear what the issue is -- you want to average BUDGET based on # of actuals? If so, this will do it. Assume actual is in column B, then: =AVERAGE(OFFSET(C1,1,0,COUNT(B:B),1)) the COUNT(B:B) will give the # of numbers in B (actual), so there are...

Excel: Calculating Dates with Durations, auto option, a3

auto option, a3, connie: Connie You can use the network days function, you will need a column with the starting date and one with the ending date if you want it to automatically calculate the days or you can manually type in the dates. Auto option In A1 type in =NETWORKDAYS(A2,A3)...

Excel: Calculating Dates with Durations, cell d1, cell a1

cell d1, cell a1, holiday dates: Hi Connie, You can use the following formula. =WORKDAY(A1,B1,C1:C15) If you place the above formula in cell D1 and the Start date in cell A1, The duration in cell B1, Holiday dates to exclude in cells C1:C15, the result will be in cell D1. If you...

Excel: Calculating Duration between two date/time where weekends, non-business hours are excluded, cell b2, task duration

cell b2, task duration, format cells: Hi, I believe that you meant the answer to this particular question is 10.0 hours. We do need the NetWorkDays function, so make sure that the Analysis ToolPak add-in is installed. -To do this, press: Tools-Addins- and check the Analysis ToolPak box....

Excel: Calculating a date in excel, date function, randi

date function, randi, insurance: Randi: Presuming your hire date/start date is located in cell A1, then this formula should work: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) NOTE - The DATE function works like this - DATE(YEAR, MONTH, DAY). For our formula I take the start date and add just...

Excel: Calculating the difference between two times, working the graveyard shift, cell b1

working the graveyard shift, cell b1, working the graveyard: You can check if the end time is smaller than the start time, and add a day to the equation, so the numbers are corrected. For example, you can try with something like: =IF(A1 B1,B1+1-A1,B1-A1) Where the start time is on cell A1 and the end time on cell...

Excel: Calculating a due date, toolpack, cell reference

toolpack, cell reference, addins: Scott To use this option you have to load the Analysis toolpack (unless it already is loaded) which you can find in Tools Addins and select Analysis toolpack (you will need your original disks or the location on your harddrive). Once you have that loaded...

Excel: Calculating in Excel, note excel, vba

note excel, vba, rolf: Thanks, Rolf, that s all I needed. :-) You need a formula that has 4 nested IFs. (The term nested IFs refers to an IF inside of an IF, etc.) Let s say that the number of kilos is in A1. This formula will test all 4 levels: =IF(A1 =1150,34,IF(A1...

Excel: Calculating in Excel, visual foxpro, silly programs

visual foxpro, silly programs, breaking points: Rolf, I m confident that if you got to know the power of Excel, you d be amazed! You surely would want to use it much more often. You can definitely do what you want with a formula. But first, I need two things from you: 1) Please write back and give...

Excel: Calculating Hours Worked, deb deb, military time

deb deb, military time, time clock: Deb, It seems this would be easier for me if I had an example. Do you enter the time, or do you get a file with this in it? How do you choose who took lunch? Are the entries to the minute, or to the quarter hour? If you can send a file to jkamps@gorge.net...

Excel: Calculating Hours, chip pearson, cell a2

chip pearson, cell a2, format hh: Kimberly, Assume the In is in cell A2 and contains the value 10:00 AM assume B2 contains the Out time 2:30 PM then in c1 put in the formula =B2-A2 and format the cell to display time (pick the format the shows hours larger than 12 or...

Excel: Calculating holiday payment, blank cells, empty cells

blank cells, empty cells, a2: For this example, we ll assume 2 rows of data, in Sheet1, range A2:T3. Each row has 20 columns; in Col U, you want to display the average of the last (right-most) 12 cells of non-blank data in each row. On Sheet2, cell A2, enter the formula: =IF(Sheet1!A2=...

Excel: Calculating hours worked, cell b5, b cells

cell b5, b cells, toola: Hi Tc How are you? Please do the following: 0. Type the time in B2:B5 as time, eg. 12:45:00 1. toola-macro-visual basic editor 2. insert - module 3. copy Function Wkh(B) t = 0: S = -1 For i = 1 To B.Rows.Count x = (Hour(B.Cells(i, 1)) +...

Excel: Calculating hours worked with lunch time if appropriate, hh mm ss, lunch column

hh mm ss, lunch column, numeric equivalent: Nita There is nothing wrong with your formula as typed in your question. I copied it directly to an excel spreadsheet and it worked fine. So I would suggest make sure the formula is exactly as typed in your question. make sure the cells involved...

Excel: Calculating hours worked, cell b5, cell b6

cell b5, cell b6, time calculations: TC The following formula should work for you in cell B6 =+IF(+(B3-B2)*24 0,-(B3-B2)*24,+(B3-B2)*24)+IF(+(B5-B4)*24 0,-(B5-B4)*24,+(B5-B4)*24) Ck out the help on time calculations and how excel handles them and if this formula doesn t work for you...

Excel: Calculating line items per order number, column c, question the answer

column c, question the answer, ogilvy: Dennis, Actually the formula should have been entered in C1 if you data is in columns A and B and starts in row 1. Here is what it produced for me: 10034 2 10034 2 4 10035 1 1 10036 3 10036 3 10036 3 9 That is what I interpreted you to want....

Excel: Calculating military time, text cells, four digits

text cells, four digits, time example: If there are stored as text, you may try with a formula like this: =IF(OR(ISBLANK(A1),ISBLANK(A2)), ,(LEFT(A2,2)*1-LEFT(A1*1))*60+(RIGHT(A2,2)*1-RIGHT(A1,2)*1)) This takes the first and last two characters using text functions, so it should solve the issue....

Excel: Calculating monthly expenditures from running yearly total, continuous fashion, inputed

continuous fashion, inputed, pivot table: Hi Navin, This looks like a prime candidate for a pivot table. This will give you a refreshable summary that will group your ID items by month, you could then link the results from the pivot table to another worksheet. Please let me know if you need help...

Excel: Calculating number of weekdays between two dates, assessment scores, workdays

assessment scores, workdays, weekdays: You need the NETWORKDAYS formula. Don t think of it as NETWORK DAYS, but as NET WORKDAYS. :) For instance, try this: =NETWORKDAYS(B2,C2) to get the number of Mondays thru Fridays between the start date (b2) and end date (c2). So, you may need something...

Excel: Calculating all odd # and All even Number seperately, douglas m smith, odd and even numbers

douglas m smith, odd and even numbers, array formulas: Hi Lisa, What a coincidence. This is the second question about odd and even numbers I ve answered today. Here are the formulas for odd and even rows respectively: =SUM(IF(MOD(ROW($A$1:$A$2432),2)=1,$A$1:$A$2432,0)) =SUM(IF(MOD(ROW($A$1:$A$2432),2)=0,$A$1:$A$2432,0))...

Excel: Calculating paid based on total hours calculated using time, pay scale, ogilvy

pay scale, ogilvy, e2: =(out-in)*24*Pay_Scale or assume the row shown is row 2 and 8:00a is in A2 in E2 put in =C2*24*D2 or =(B2-A2)*24*D2 time is stored as a fraction of a 24 hour day, so to get hours you multiply the difference by 24. -- Regards, Tom Ogilvy...

Excel: Calculating a percentage, 99999

99999: It s NOT 100% increase. From 0 to 1 is not 100%, it s indeterminate. From 1 to 2 is 100% increase. If you think 0 to 1 is 100% increase, what s 0 to .00001 or 0 to 99999? Makes no sense. #DIV/0 is appropriate. You MAY want to use =IF(last month=0,1,(this month-last...

Excel: Calculating Regular Worked Hours in 1 cell minus the OT hours, ot hours, overtime rate

ot hours, overtime rate, cell max: Steve: Time can be one of the trickiest aspects of Excel to get a hang of. Assuming your times are actually entered correctly as time (Excel can be picky about that), then this formula will work: =(E8-D8)*24 Excel treats time as a fraction of 1...

Excel: Calculating service engineers payments, microsoft excel 2000, using microsoft excel 2000

microsoft excel 2000, using microsoft excel 2000, excel function: Hi Stuart, There is an Excel function called WEEKDAY that will do what you need. It evaluates a date and returns an integer value corresponding to the day of the week. If the date to evaulate is in cell A1, the formula would be: =WEEKDAY(A1) The...

Excel: Calculating time interviews in Excell, hello bill, chronometer

hello bill, chronometer, time of day: To count the hours you will need to know at what time on yesterday to start counting or would the time on yesterday start at midnight and therefore assume 24 hours plus the number of hours until now? In other words, from what time of day on yesterday (10/4/07)...

Excel: Calculating time, minute slots, time slot

minute slots, time slot, text string: Hi Crystal, To add 30 minutes to the time, simply add 1/48 to the value. For example, if 8:00 is in A1, then =A1 + 1/48 will give 8:30. This is because internally in Excel times are values in days, and 1/48 of a day is 30 minutes. Another way to...

Excel: Calculating totals in realtime on a UserForm, ccur, spreadsheets

ccur, spreadsheets, realtime: Use either the Change event of the After Update event of each control in the grid (I assume you are using textboxes - you did not say) Use this code on every Change event of the After Update event: Dim Total as Currency On Error GoTo TotalError With...

Excel: Calculating Weekending Date, pivot table, daily basis

pivot table, daily basis, cheers: Hi Kevin, How are you? The only thing I can think of now is that you add to each record the date of the last day of the corresponding week and sort/filter the pivot table by this date. To find the the date of each record week you can sort out a formula....

Excel: Calculating Weekending Date, douglas m smith, excel2000

douglas m smith, excel2000, option number: Hi Kevin, WEEKDAY does not return a date, it return a value of 0-6 or 1-7 corresponding to the days of the week depending on which option you use: Option Number returned 1 (or omitted) 1 (Sunday) through 7 (Saturday) 2 1 (Monday)...

Excel: Calculation between two dates, excel spreadsheet, colums

excel spreadsheet, colums, excel 97: Birderfrank There are a few variables that can be used here but I will need to know if the colums are at a set amount or constantly being updated. If you can send a copy to littlewing@littlewingweb.com with whether or not it static or always being added...

Excel: Calculation in part of Date (Hours), time calculation, time hours

time calculation, time hours, time time: I suspect that you already have the formula, but just see the result incorrectly! Assuming that Time In and Time Out both fall on the same day, then the formula is just =timeOut - TimeIn HOWEVER, you need to format the cells to show time (would suggest...

Excel: Calculation, random integers, random sets

random integers, random sets, excel functions: Pete You just want to generate random numbers in the 3 cells you have designated for age. If so, assume the cells are A1, A2 and A3 in each enter the formula =trunc(rand()*20+1) this will generate random integers between 1 and 20 inclusive. Adjust...

Excel: Calculations in Excel, hth, drop down list

hth, drop down list, match: Nested IF s is limited to 7. A better approach is something like this: =G3*CHOOSE(MATCH(B3,{ books , magazines , movies , other4 , other5 , other6 , other7 , other8 , other9 , other10 , other11 , other12 , other13 },0),bookrate,magrate,movierate,other4rate,other5rate,other6rate,other7rate,other8rate,other9rate,other10rate,other11rate,other12rate,other13rate)...

Excel: Calculations using dates, cell a1, many thanks

cell a1, many thanks, ym: Assuming the earlier date is cell A1 and the later date is cell A2 then: Years =DATEDIF(A1,A2, y ) Months =DATEDIF(A1,A2, ym ) Days =DATEDIF(A1,A2, md ) If you d like to learn more about the undocumented DATEDIF function you can download...

Excel: Calendar Control, target cells, calendar control

target cells, calendar control: Hi Kim, It depends on how you are showing the calendar. I think you are using a form with the calendar control embedded in it. Is that correct? In that event, lets assume 1. Form is called UserForm1 2. Calendar control is called Calendar1 Your showIt...

Excel: Calendar Control, calendar control, control question

calendar control, control question, excel spreadsheet: I assume you have just inserted that object, the Calendar control, somewhere on the sheet. If so, then just by inserting that control DOES NOT mean that it will do anything more than just sit there and let you click on it. All it does do is just sit there...

Excel: Calendar, filling in series of cells easily, dropdown arrow, lengthy explanations

dropdown arrow, lengthy explanations, conditional formatting: Hello Detlef, This can be done using Data= Validation and for coloring, Format= Conditional Formatting. Under data validation, you will want to use the List feature and type in the choices you have listed. The dropdown arrow appears when the cell is selected....

Excel: Calendar Issue, calendar issue, weekends and holidays

calendar issue, weekends and holidays, vba programming: Steve, I honestly don t think this is possible without some VBA programming, because excel would need to sort through each individual date to see if it was a holiday or a weekend day. I don t really think I can help you out much on this one. You may...

Excel: Calendars, alternate dates, reservation calendar

alternate dates, reservation calendar, line reservations: I see the calender, but unfortunately it s in HTML format, which means I don t know what it does in Excel - HOWEVER, a date is simply a number, so you COULD do edit paste special and use the ADD button (having copied the appropriate number) to add that number...

Excel: Calender Control MACRO, cell a1, ms project

cell a1, ms project, click a date: Assuming you want cell A1 to be the trigger cell then put this code in the sheet of A1 that is to be your trigger cell. The code does not go into a regular module. In this case, click A1 and the calendar appears. Click a date and A1 will then contain...

Excel: Calling DLL from VBA for Excel 2007, run time error 53, dll function

run time error 53, dll function, open option: Hi again Kapil, Yes, now I remember reading about the new ribbon interface. You mentioned doing a File Open, but I believe that with the new ribbon interface you click on the Office button (at the top left corner of the window with the Office logo...

Excel: Calling various macros based on cell value, input sheet, input range

input sheet, input range, macros: As you wrote it, FETCH is simply a value, and can t be used as a reference to run a macro. As far as I can tell, you only need this: Sub Get_Connx() Activecell.Copy Sheets( InPut ).Range( WhereToPaste ) End Sub But it s not clear from your example...

Excel: Calulation formulas, hh mm ss, cell a1

hh mm ss, cell a1, evaluation example: It will show up with an AM, and in fact, Excel will convert it to 12:MM:SS AM - that is okay. You can use the cell formatting, as you have done, to make it appear as just 0:MM:SS. Now...as for getting an error...that seems odd. I can definitly put in...

Excel: Cant save formula, type conversion, copy paste

type conversion, copy paste, pickle: Rob You need to use the copy/paste special/values command. First clic on the corner intersection and paint the whole worksheet. The go to toolbar/edit/copy. Then go to the sheet you want to past to and clic on the intersection cell thus painting the...

Excel: Cant save formula, type conversion, pickle

type conversion, pickle, ms access: Rob Highlight the entire spreadsheet and then paste it into a new spreadsheet using the paste special option and select values only. This should do what you need. You can also automate this procedure with a macro. Importing into access with over 255...

Excel: Cant save formula, paste values, type conversion

paste values, type conversion, pickle: Not a pickle at all, Rob! This is a very simple and common thing to do in Excel. Just follow these steps: 1) On Sheet3, select all of the formulas. 2) Copy them to the clipboard. 3) Without moving the cursor (make sure they re still all selected), drop...

Excel: Cant save formula, type conversion, sheet tab

type conversion, sheet tab, pickle: Rob, Not too bad actually. What I would suggest is that you first make a copy of the sheet. You can either copy it to the current workbook or just go directly to another workbook (the second option will save you a step). You can do this by right clicking...

Excel: Cant save formula, type conversion, copy button

type conversion, copy button, csv: Rob - more like a cucumber. First, on sheet three, select all the cells in the sheet by pressing CTRL+A. Click the Copy Button, then click Edit PasteSpecial Values. Now the sheet will be delinked from all its formulas and you can save it as you wish....

Excel: Capatalize the first latter, lazy dog, title case

lazy dog, title case, farhan: Dear Farhan It is better to try to solve your problem with Word rather than Excel. In Word, select the text you want to change and then click Format , then Change case then click Title Case . As far as I know, you cannot do this in Excel. I hope...

Excel: Capitalization, proper target, target value

proper target, target value, value application: You can have another column which has =PROPER(A1), but that s not quite what you want. You need an event macro. Right-click the sheet tab, select View Code, enter this, which applies to column C (3): -- change the colulmn accordingly. Private Sub Worksheet_Change(ByVal...

Excel: Capturing Word Data Into Excel Using VBA, distinct fields, active document

distinct fields, active document, word data: Yes, both are possible - I ll start with the second one as it is arguably easier Sub XLPopulate() Dim myobj As Object Set myobj = GetObject(, Excel.application ) With myobj.activesheet .Range( A1 ).Value = ActiveDocument.FormFields( Text1 ).Result...

Excel: Cascading combo options/combo boxes, combo boxes, thanks in advance

combo boxes, thanks in advance, cascade: Julia Yes you can create list boxes that are dependent on your selection from other list boxes. I have done it in the past, but unfortunatley I can t remember exactly how, so I can t really answer you question right now. I suggest that you reask the question...

Excel: Cascading or Filtered selections?, data validation, emmar

data validation, emmar, valid choices: Assuming the defined list for Channel, Beam, etc is named Choice1, and the validation is in A1, then define a name, like Emmar, which is: =CHOOSE(MATCH(A1,Choice1,0),CHchoice,BEchoice,PIchoice,ANchoice) where CHchoice, BEchoice, etc. are the defined names...

Excel: Cascading lists, validation system, data validation

validation system, data validation, obscure location: Emmar First I would say there a couple of ways to do this. As you have noticed, they don t just build themselves. My preferred method is to use ListBoxes. The first Listbox has a rowsource that according to the 4 items listed. This rowsource will just be...

Excel: Cascading lists, data validation, beam pipe

data validation, beam pipe, drop down list: You will need to create a dynamic named range - however, this is difficult to explain without a spreadsheet in front of you! IF you can let me have your email I can mail you an example (which I actually created for another AllExperts question, but I think...

Excel: Cascading lists/combo boxes, douglas m smith, combo boxes

douglas m smith, combo boxes, data validation: Hi Julia, I don t think this is possible without macros for Forms or ActiveX control objects. Data validation has in-cell dropdowns and you can set it up for cascading. Here s a link to another expert s answer to a very similar question: http://experts.about.com/q/1059/3999633.htm...

Excel: Cascading lists, data validation, emmar

data validation, emmar, beam pipe: Emmar You have the Filter option which will do exactly what you want available from the Data menu. Another option would be to use a pivot table although that depends on the layout of your table. I would suggest the filter option using the autofilter to...

Excel: Case # and Excel cells, nature of the beast, boundcolumn

nature of the beast, boundcolumn, a3: The listindex is always which item you select based on the order in the list and is not at all linked / associated with the item itself. You can t count on listindex 1 being product1. It s just the nature of the beast. You re going to have to make some other...

Excel: Cash Budget, cash budgets, cash budget

cash budgets, cash budget, logical test: Nancy, when you begin to enter the If formula, get the =If( part entered, then click the little Function button just to the left of the formula in the formula bar. That should open the function arguments window. In that window, you can see what the results...

Excel: Catch and store, lars, hurry

lars, hurry, vb: IF I understand what you are asking then you will need a macro to catch the value of a cell. And IF you are wanting to catch this value every minute then you need to consider how many of the catches you want/need to store and where to stere them. In an...

Excel: Categorical scatter plot, bar graph, calculating the median

bar graph, calculating the median, scatter plot: I think I understand what you are looking for, and I have designed a sample graph and uploaded it to the following location: http://home.swbell.net/nate-sus/excel/carcinoma.xls There is quite a bit of tweaking from a standard graph, so let me know if...

Excel: Cell Calculation, empty column, blank cell

empty column, blank cell, vba macro: Ramesh, You could write a vba macro using the change event to do that if you are proficient in macros. You could define a name (insert= Name= define) Name: Rage Refersto: =31.70 then when entering your data put in =1234*rate rather than...

Excel: Cell Color Changes based on condition, douglas m smith, conditional formatting

douglas m smith, conditional formatting, format button: Hi Dan, This is exactly what Conditional Formatting does. Select the cell(s) you want to use and then select Conditional Formatting from the Format menu. In the dialog box, there are two options: Cell Value Is: tests the value of that cells Formula...

Excel: Cell Conditional Formatting, relevant cells, conditional formatting

relevant cells, conditional formatting, stock movements: Hello, Ian, I think the easiest way to handle this is not with a tick box, but by simply putting a character in each cell of column I. Perhaps you can use an X or a check or something similar. This makes Condition 2 s formula very simple: =I4 ...

Excel: Cell Copy, cell a1, a20

cell a1, a20, a10: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, and/or seem to think I am expected to do so unless they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros are...

Excel: Cell Copy, curser, macro recorder

curser, macro recorder, d20: So, what s your question? Asssuming both cells are on the same sheet then: Range( D20 ).Copy Destination:=ActiveCell or a shorter version ActiveCell = Range( D20 ) if on different sheets then put the sheet name in front of the cell, like ...

Excel: Cell will not calculate, corectly, excel 2000

corectly, excel 2000, cells: Tom I haven t experienced the problem that you mention nor have I had any other questions along these lines. However I would suggest that you create new default worksheets in a new default workbook. and make sure that all the cells are calculating corrrectly....

Excel: Cell changes color based on Sum, conditional formatting, format button

conditional formatting, format button, cell changes: Hi Tan, Yes you can use Conditional Formatting to do this. Select cell A13 and then go to the Excel menu and select Format then Conditional Formatting... to the Right of where it reads Cell Value Is select the drop-down list and select less than...

Excel: Cell changes color based on Sum, conditional formatting, cell changes

conditional formatting, cell changes, a12: Tan Yes you can. Go to toolbar/format/conditional formatting In the conditional formattin window select cell value is --then greater than --then put 1 in the next selection box. Then clic on format, and select the format that you want then clic...

Excel: Cell changes color based on Sum, conditional formatting, cell changes

conditional formatting, cell changes, menu format: Yes, you can use formulas in conditional formatting. In this case, select the cell A13, menu Format- Conditional formatting, and add these two conditions, selecting Formula is in the first drop down: =SUM($A$1:$A$12) 1 =SUM($A$1:$A$12) 1 For the first...

Excel: Cell color, cell interior, xf

cell interior, xf, cells: Haythem, By white do you mean the actual fill color white or do you mean no fill , which appears as white on the screen? Here is a routine that will tell you either way. Since you didn t specify how or where you wanted the 1 or 0 placed, I ll assume...

Excel: Cell color, douglas m smith, colorindex

douglas m smith, colorindex, cell a1: Hi Haytham, There is no direct way read a cell s color. It would require a macro. Something like: Sub ColorCheck() If ActiveCell.Interior.ColorIndex xlNone Then Range( A1 ).Value = 1 Else: Range( A1 ).Value = 0 End If End Sub When...

Excel: Cell color, dave hawley, dark teal

dave hawley, dark teal, rcell: Hi Haytham, Yes you can tell exactly what color is in a cell. Here is a user-defined function. Do no worry if you have not used VBA programming at all. It is simply a matter of copying and pasting to use this function. This was found on the www.oxgrid.com...

Excel: Cell color, excel 5, rem

excel 5, rem, b1: Hi Haytham, How are you? Please try the following 1. Tools - macro - visual basic editor 2. insert - module 3. copy Function cor(A As Range) Rem colour =1 Rem white =0 If A.Cells.Interior.ColorIndex 0 Then cor = 1 Else cor = 0 End If...

Excel: Cell color, conditional formatting, resumes

conditional formatting, resumes: Haythem If the cell is colored based on conditional formatting then you could use a formula that would give the result as a 1 or 2 depending on the criteria that is creating the red or white color. Otherwise I don t know of a way to read the format...

Excel: Cell color calc, douglas m smith, blue cells

douglas m smith, blue cells, sumif: Hi Ralph, You can t directly test a cell s color in Excel. It would require a macro. However, I m sure there s a reason the cells are different colors, such as values within a certain range or they are related to specific person, place or thing. You...

Excel: Cell color, hotbutton, color rgb

hotbutton, color rgb, test value: Haytham, I think the only way possible to do it right would be to use some VBA coding. Here is an example: A1 is the test value, and A2 is the result box. Sub Macro1() If ActiveSheet.Range( A1 ).Interior.Color = RGB(255, 255, 255) Then ...

Excel: Cell colot, ogilvy, background color

ogilvy, background color, capability: There are no built in functions that deal with the background color of a cell. You might look at the CELL worksheet function, for some limited capability, but probably not what you want. If you know VBA, you can write a user defined function, but it...

Excel: Cell colot, spreadsheet

spreadsheet: There is nothing built into Excel itself as such, but you CAN create a custom function to do this for you - open the VBA Editor, create a module for your spreadsheet and put the following in place Function coloury(cellref) If ActiveCell.Interior.ColorIndex...

Excel: Cell corner indicators SLOW-DOWN, work tomorrow, quicksand

work tomorrow, quicksand, ogilvy: I don t have a copy of xl2003 handy, but I would expect you can uncheck the option in tools= Options= View tab I will check the copy at work tomorrow and advise you more specifically. --------------- Looks like Tools= Options= Error Checking tab....

Excel: Cell division, a20, cells

a20, cells, cell division: Are all cells with Formulas the same? Maybe you have View Formulas checked in Options. From the Tools Menu select Options and in the View Tab look for Formulas in the Windw Options. If it is only the one cell, maybe you have inadvertently typed another...

Excel: Last Cell in Excel, speedy responses, line sheets

speedy responses, line sheets, true windows: No. See below: Dim Ord As Workbook, RG As Range On Error Resume Next Range( A1:M36 ).Copy Range( A1:M36 ).PasteSpecial Paste:=xlPasteValues Range( A1:M36 ).Copy Workbooks.Open Filename:= C:My DocumentsOrdersPOTemp.xls ActiveSheet.Paste...

Excel: Last Cell in Excel, false selection, false application

false selection, false application, true windows: The code you included does more things than what your questions originally said. And because you have recorded the macro, there is a lot of redundancy in it(the recorder typically does that). If I do what you ask and what you need then I would really need...

Excel: Cell Formats, 6 digits, dddd

6 digits, dddd, digit number: Can t be done without a macro. It is possible to use the 6 digits elsewhere: = Born on &TEXT(MID(A2,7,2)& / &MID(A2,9,2)& / &MID(A2,5,2), dddd )& of &TEXT(MID(A2,7,2)& / &MID(A2,9,2)& / &MID(A2,5,2), mmmm )& of &TEXT(MID(A2,7,2)& / &MID(A2,9,2)& / &MID(A2,5,2),...

Excel: Cell Formatting, format menu, background color

format menu, background color: If you look at the format menu, you should see conditional formatting - use this to set the conditions and formats you want and Excel should do as you wish - you can have up to 3 conditions (which gives four levels of colour - the fourth one being all tests...

Excel: Cell Formatting, background colors, hard on the eyes

background colors, hard on the eyes, conditional formatting: In your case, the ONLY way is with a macro that you will need to write. But, in my opinion, I would rethink your idea. If you have a worksheet with cells having 20 different background colors it will be very hard on the eyes and look like a Christmas tree...

Excel: Cell in general format evaluates as text, group data, room numbers

group data, room numbers, happy new year: Hi Nick, Yes sometimes you have to force Excel to return a cell to a number. Here are a couple things to try. 1. Have you tried physically entering the number 1010 into cell A10 on the Group data worksheet. Once you insert your number and press enter...

Excel: Cell Highlights, format button, tht

format button, tht, cells: Enter the list of your shows in another sheet, name the list shows . Then, on each sheet, select all the cells (or the cells containing data), and use Format/Conditional Formatting, change cell Value is to formula is , and enter this: =NOT(ISERROR(MATCH(A1,shows,0)))...

Excel: Cell History tracking, time dave, data changes

time dave, data changes, fred 62: Dave Sorry, I just don t know if you can do what you want. I suspect that it can be done, but its beyond my expertise. I d sugget that you reask the quesion of another allexperts person, and check the resumes for one who might have the experience and...

Excel: Cell justification in an, microsoft excel 2002, conditional formatting

microsoft excel 2002, conditional formatting, justification: The only way to do it right is with a macro - conditional formatting will not do this. However, you can play typewriter and embed extra spaces in front of the word you want the IF statement to display to simulate right justification and then have the...

Excel: Cell Limitations, mailmerge, maximum length

mailmerge, maximum length, sledgehammer: Although you can set a maximum length using the DATA menu and then the validation choice (choosing text length) this will NOT automatically move the focus to another cell once the length of the text is too long, nor will it check the length until you try to...

Excel: Cell link, Prevent updating, cell a1, seperate sheet

cell a1, seperate sheet, discussion point: Hi Mark Consider this possibility. As well as storing the data in the reference cell why not keep an incrementing date stamped/time log on a seperate sheet or alternate range on the active worksheet. Regards Des HI Mark I sorry to see...

Excel: Cell linking with macro, cell c2, cell a1

cell c2, cell a1, column c: If you don t want C2 to change, then you shouldn t link it to cell A1. You should rather just put the value from A1 into C2. e.g.: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 2 Then Target.Offset(0, 1).Value...

Excel: Cell Merging, cell b1, cell c1

cell b1, cell c1, cell a1: Assumption: Cell A1 --- 1009999; Cell B1 --- 1395.5 Formula in Cell C1 =CONCATENATE(A1,B1) OR =A1&B1 If you need to put a space or a hyphen un between... Formula would be -- =CONCATENATE(A1, - ,B1) OR =A1& - &B1 Hope this works.... Regards ...

Excel: Cell Names as Cell Contents, cell contents, warm regards

cell contents, warm regards, two solutions: Hi Mark, I am giving you two solutions, 1. Quick way of getting all Names alongwith cells is a single line macro- Worksheets( Sheet1 ).Range( A1 ).ListNames where Sheet1 is the name of your sheet and A1 is where the list will start. 2. Putting names...

Excel: Cell names, dangerous mistake, false assumptions

dangerous mistake, false assumptions, sheet1: Yeah, I m quite familiar with it. To begin with, you re making two false assumptions. 1. You think that the defined names exist because cells or sheets refer to them. That s not true. 2. You make the statement, I copied some sheets from another...

Excel: Cell Protection and COlors, color pallete, colors

color pallete, colors, cell protection: Dan You re not gonna have much luck here #1 with the protection on if the contents are protected (which you probaly want them to be) you can t either group or ungroup. don t know of a way around this. #2 I don t know of any way to add a custom color...

Excel: Cell Protection, format cells, row headers

format cells, row headers, appropriate selection: click in the upper left corner of the column and row headers so it selects all cells in the sheet. The do format= Cells, select the protection tab and uncheck locked. Now select the cells you want to protect. Then do Format= Cells, select the protectiong...

Excel: Cell protection in excel, skill knowledge, bob bob

skill knowledge, bob bob, good answer: Bob I can t find a way to simply solve your problem. I am sure that it can be solved with a macro, however I also am not a macro expert. I suggest you reask the question of another allexperts person, and ck resumes for VBA proficiency, I m sure you can...

Excel: Cell Reference, left hand corner, cell c2

left hand corner, cell c2, cell c1: Hi Mike, cell C1 would be the following formula. =SUM(A1:A24) cell C2 would be the following formula. =SUM(INDEX(A$1:A$2000,(ROWS(A$1:A2)-1)*24+1):INDEX(A$1:A$2000,(ROWS(A$1:A2))*24)) then you can select cell C2 and drag the formula down using...

Excel: Cell Reference Errors, paste feature, column c

paste feature, column c, cell reference: I don t know any method simpler than the following. Make the user entry range A2:A11. Protect the sheet except unprotect A2:A11 so user can enter the dates. In (hidden) range D2:D11, enter the numbers 1,2,3,...etc. In cell C2, instead of a formula like:...

Excel: Cell Reference to a tab, cell b11, entity number

cell b11, entity number, cell reference: #1. I have something in Cell B11, and want that cell to replicate the name of a tab (i.e. Closing #4). Yes but you will need to write a macro to do that - no formula will do that. #2. In cell B11, I have an entity number (PT2003), and would like...

Excel: Cell Reference: worksheets copied from different workbooks, cell b3, blank sheets

cell b3, blank sheets, cell reference: You can do this using 3D references: In the large workbook , put in two blank sheets and name them: Start Finish Copy all your worksheets after Start and before Finish in the tab order. For example: Summary Start sheet from book1 sheet from...

Excel: Cell References across spreadsheets, formula index, index formula

formula index, index formula, cell references: Since you have created an array called MONTHS, you can index this array on any sheet in the same workbook by using the INDEX formula. For example, presume MONTHS is actually a reference to Sheet1!A1:A12. You can access March with the following formula:...

Excel: Cell reference to name a file, cell reference, cell a1

cell reference, cell a1, numeric value: This is one piece of code that you can use in your code: ActiveWorkbook.SaveAs 2007 Week & Sheets( Sheet1 ).Range( A1 ).Value & .xls This will save the book as 2007 Week XX.xls, where XX is the value of cell A1 in the worksheet Sheet1 Hope this...

Excel: Cell references in a formula, column c, cell references

column c, cell references, reference numbers: From the example, I suppose that you are inserting the cell on column C, because if you insert it before the formulas adjust as you desire. As this is the default behavior when you insert columns between formulas, I would suggest a workaround: - Insert...

Excel: Cell references, cell references, case names

cell references, case names, database tool: Here s a simple example. You can take the basic structure of this formula and customize it to your needs. In this example, the formula looks at cells A1 and A2. It combines the dates in those 2 cells together with a space in between. But if A1 or A2 is...

Excel: Cell Shade, conditional formatting, charlie charlie

conditional formatting, charlie charlie, constraints: Charlie This requires the use of Conditional Formatting. It allows you to set 3 levels of formatting for any cell or group of cells. Select the cells you want to format, then go to Format/Conditional Formatting. You just have to tell Excel what the constraints...

Excel: Cell Shading, rgb values, color chooser

rgb values, color chooser, thnx: Hi Saeed, This is not as easy as Word, but still not too difficult. Tools/Options/Color Choose the current color square that you want to modify by clicking on it and then click the modify button. You should now be able to type in your RGB values and...

Excel: Cell Sizing, alignment tab, vlookup formula

alignment tab, vlookup formula, kim hi: Hi Kim, I am not aware of the ability to have a scroll bar within a cell. There is a capability to have a drop down list within a cell. But this will not help you. I can kind of get what you want with the following. What you can do is make the column...

Excel: Cell selection, blank cell, time range

blank cell, time range, cell address: If I understand what you are asking then the following code will get the first blank cell in column A. It will assign both the cell itself and the address of the cell to separate variables. Without seeing any of your code, I suspect that you will have...

Excel: Cell sums that require an IF factor, douglas m smith, pridham

douglas m smith, pridham, sum function: Hi Cindy, That is accomplished by testing the divisor (the number on the bottom) to see if it is zero. The formula would be: =IF(N24=0,0,N15/N24) You don t need the SUM function for that operation either. Douglas M. Smith Brainbench MVP for MS...

Excel: Cell Value, grid lines

grid lines: Amogne You can do this by adding a helper column which will add the text and the numbers together but still allow you to use the numbers in formula, but then you are doubling your work basically because you will already have the check number in the next...

Excel: Cell Values from another worksheet., cell values, microsoft excel

cell values, microsoft excel, e2: vlookup does NOT need to be sorted. :) Try using the forth argument: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm You need to use FALSE or 1 as the forth argument to find the exact match. (I m pretty sure...it s been so...

Excel: Cell validation?, data validation, cell a1

data validation, cell a1, vba: Jeff, Okay. A few things to set it up first. Select all the cells on the worksheet, and unlock them (Format Cells Protection, and uncheck Locked. Then add this code Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String =...

Excel: Cell value as Lookup Array, array value, level criteria

array value, level criteria, vlookup: Hi, William, I m certain that what you want to do can indeed be done, but I need a little bit more information in order to help you. Please write back again and tell me what s in which cell on your spreadsheet, and then give me an example of what you...

Excel: Cell values, cell a2, sheet tab

cell a2, sheet tab, cell values: You can cut A2 and use insert/paste to A1 which will swap them. If you want this to happen automatically, then you d need to use VBA, but if calculation is automatic, this will result in a recalculation. Suppose your cells each say =RAND(). This VBA code will...

Excel: Cells' values, excel help, exact number

excel help, exact number, ctrl key: It is what EXCEL thinks you want to do - either just copy the number down or continue the series that it thinks would exist if you drag down. It depends on the data and what you have selected before clicking and dragging. Try holding down the CTRL key...

Excel: CF, I think, f17, f20

f17, f20, numerical data: You can use logical comparisons in the formula, something like: =(F8*(F8 100)+F11*(F11 100)+F14*(F14 100)+F17*(F17 100)+F20*(F20 100))*NOT(AND(F8 =40,F11 =40,F14 =40,F17 =40,F20 =40)) The part before the NOT takes care of the first condition, only adding...

Excel: CHANGING A CELL VALUE WITH VBA, ms excel 2000, irow

ms excel 2000, irow, excel function: Hi Chad, I m not absolutely sure I am interpreting your question correctly. Since your formula includes the range $F2:$F$1000 I assumed that you wanted to check every cell in this range against AD1 and place the result in the same row in column G. Based...

Excel: CHARTS, douglas m smith, scrach

douglas m smith, scrach, brainbench: Hi Kelly, Can you clarify what you mean by shrink up ? Are a some of the worksheets pages shrunk up or are some charts shrunk on a page where others behave normally? If an entire page is shrunk up, check that page s Page Setup. The scaling may be...

Excel: CHaracter Count, character count, a3000

character count, a3000, b1: Hi, Rob How are you? To find How many characters A1 contain, type in B1 =Len(A1) To find How many characters A1 and A2 contain, type in B1 =Len(A1&A2) For counting the characters of 3000 cells, you have to write a vba function Function...

Excel: CHaracter Count, abc corporation, character count

abc corporation, character count, len: Hello, Rob, When you say field, I assume that you mean cell. There is a very easy way to get a character count for a cell. The function LEN gives you the length (number of characters) of a cell. If you wanted to length of A1, you would say: =LEN(A1)...

Excel: CHaracter Count, character count, len

character count, len, a1: Rob, I think you have 2 options. You can insert a formula for each cell =Len(A1) and then add up the values for that column. The other is to use a piece of code, and place the total in a cell. That would look like this: Sub ColumnTextCount() Columns(...

Excel: CHaracter Count, curly brackets, array formula

curly brackets, array formula, a3000: Rob If you mean all 3000 rows in one field then try this formula {=sum(len(A1:A3000))} A1 to A3000 is where you would put your range (field). The curly brackets indicate it is an array formula, this means after you have typed in the formula =sum(len(your...

Excel: CHaracter Count, character count, len

character count, len, a1: Not totally clear what you need to do, but =LEN(CellRef) would return the number of characters in the cell referenced (eg =len(a1) where A1 is ABC would return the value 3) if this isn t what you need, can you clarify the question and I ll try again...

Excel: CHaracter Count, len function, field column

len function, field column, best guess: Rob Here is my best guess at what you are asking. Without more details, this is what I think you want. In B1 put in =len(A1) This will tell you how may characters are in A1. You can drag fill this down column B to report the number of characters...

Excel: CHaracter Count, douglas m smith, len function

douglas m smith, len function, cell a1: Hi Rob, You can use the LEN function to count the number of characters in a cell. Example: Cell A1 contains the text abc 123 . LEN(A1) would be 7. Spaces and other non-printing characters are included in the count. Douglas M. Smith Brainbench MVP...

Excel: Chacking if cells are filled, followup question, programming question

followup question, programming question, excel sheet: Charles, This sounds like a programming question using VBA. if application.CountA(rows(activecell.row)) = 0 then msgbox This row is blank End if You can change the 0 after the equal statement to check the number of cells that are not empty....

Excel: Change Cell color from blue to orange, conditional formatting, cell interior

conditional formatting, cell interior, editor menu: Hi Avnessh, You can use a little bit of VBA code to accomplish this. The one item to check is to make sure you have a blue color #5 and Orange #46. To check you can go to this chart and make sure, if it is different color change the numbers in the Macro....

Excel: Change cell color based on value, format a1, format cells

format a1, format cells, format button: Hi, Chris, You need a feature called conditional formatting. It s powerful and very easy to do. Conditional formatting allows us to change formatting based upon cell values or formulas. Follow these steps to conditionally format A1: 1) Click in A1....

Excel: Change cells colour using formula, background color, cells

background color, cells, cpearson: What you are looking for is one of Excel s great features called conditional formatting. Please have a look at the page: http://www.cpearson.com/excel/cformatting.htm It has the answer to your question. Please let me know if you need further help! Gyul...

Excel: Change color based on given date, relative references, question thanks

relative references, question thanks, format menu: You need to make sure you are using relative references - so =a1 today() would copy across to other cells but =$A$1 today() would keep the reference as A1 - if not clear, email me a sample file (aidan.heritage@virgin.net) and I can walk you through...

Excel: Change from columns to rows, target cell, backup copy

target cell, backup copy, xf: Crystal, First, make a backup copy of the table (better yet the file) in case this doesn t produce what you expect. Now, highlight the entire table and click copy. Highlight the target cell where you want the new data to be, and click Edit PasteSpecial...

Excel: Change font characteristics in concatenation, format cells, change font

format cells, change font, concatenation: Hello, Dennis, I just found this among my questions, but I answered it several days ago. Not sure why it didn t go through...? The answer was that unfortunately, Excel does not have a way to accomplish this. There is indeed a way to conditionally format...

Excel: Change font color to red depending on value, cell c1, type b1

cell c1, type b1, conditional formatting: Don, Conditional formatting will do what you need. 1. Select cell C1. 2. Click Conditional Formatting from the Format menu. 3. In the first box, select Formula Is. 4. In the second box type =B1 A1. 5. Click the Format button. 6. Change the font color...

Excel: Change font colour in one column depending on value in different column, douglas m smith, format dialog

douglas m smith, format dialog, brainbench: Hi James, In the Conditional Format dialog box, select the Formula Is option and enter the following formula: =AND(ISBLANK(B1),A1 TODAY()-30) Replace my date checking logic if yours is different. I m assuming A1 is the date to check. Note that...

Excel: Change font colour in output of the IF command, cumulative frequencies, macro tools

cumulative frequencies, macro tools, macro security: I think I may not have understood the question properly. I presume you need to blankout data starting with 0. Try Fine & Replace with Find = 0*. Leave Replace with without any entry. Replace all. If you want record this as Macro and use it often through...

Excel: Change font colour in output of the IF function, frequency table, example question

frequency table, example question, field settings: We are a long way from the original question! And it s now not clear what you want to do - to put a macro on a button, you can right click a toolbar and use customize. To have a macro run automatically, you use the change event of the worksheet - may help...

Excel: Change Pivottable page field with VBA, vba code, target address

vba code, target address, target value: The only way would be to loop through all the items in the pagefield and see if it matches one before makeing the change Here is a tested revision. It worked for me: Private Sub Worksheet_Change( _ ByVal Target As Excel.Range) Dim pfld As PivotField...

Excel: Change page fields on multiple pivot tables at once, pivot tables, pivot table

pivot tables, pivot table, vba: I ve had lots of problems with programming pivot tables, but I would probably use the countif worksheet function (via worksheetfunction.countif in VBA) to determine if there is a match (or possibly sumproduct) and then use an IF statement to handle the two...

Excel: Change Source Data on Protected Sheet, cell a1, change source

cell a1, change source, book2: Changing the source would change the formulas, and if cells are locked so that the formulas cannot be changed, you are not able to do it - so you would need to unprotect the sheet before you can do it. One POSSIBLE way round it is to have all the links on...

Excel: Change Source Data on Protected Sheet, change source, source data

change source, source data, daily basis: I don t really understand your scenario, and what you mean when you say you change the source data. I m going to take a shot here though. You can unlock individual (or groups of) cells so that when a sheet is protected you can still edit these unlocked...

Excel: Change Source Data, book2, change source

book2, change source, newname: If I understand you correctly, simply overwrite the entry & re-run the code. or maybe substitute an InputBox for th Cell value, might look a little slicker and can prompt for the source each time it is run. Sub Button1_Click() Dim sLinkName As String...

Excel: Change a Userform Shape, shape objects, demo index

shape objects, demo index, rectangular shape: Hi Mark, I am not aware of any way to change a userform from the standard rectangular shape, especially now that a userform can be non-modal, i.e., runs as a separate process and therefore needs its own process window, complete with banner, border, and...

Excel: Changeing colum to rows, douglas m smith, jacob weiss

douglas m smith, jacob weiss, colume: Hi Jacob, You do this by selecting and copying the cells and then select a cell below these cells where you want the data pasted. You can t use a cell that s in the copy range. Now select Paste Special from the Edit menu. In the dialog box, check the...

Excel: Changing Cell/Text Format When Time Past, contenets, conditional formatting

contenets, conditional formatting, loan stock: Ian if you use =IF(E1-C3 60, yes , no ) in the cell next to the date of the stock where E1 is today s date and C3 the loan date it will show a yes if it is over 60 days. You can then set the conditional formatting for that cell to show red if yes is...

Excel: Changing a Chart's series with VBA, axis labels, data changes

axis labels, data changes, vba: Hello Ron, I would use a dynamic range and skip the VBA. Jon Peltier s site is the place to go to get the details http://peltiertech.com/Excel/Charts/ChartIndex.html#D look under Dynamic. If you want to use VBA, then that is the site as well....

Excel: Changing a cell background color, problem mail, conditional formatting

problem mail, conditional formatting, mail id: Dear Robert, Sorry for the delay. Conditional formatting may solve your problem. But you can differenciate only three variables + 1 Normal data. So you can maintain 4 different colors. If this is suffiecient for you, then no problem. If you want to...

Excel: Changing cell format based on another cell, company christmas party, format hh

company christmas party, format hh, decimal format: Denny, I have no idea what is in the cell so I have no basis for changing it back to time . Any number between 0 and 1 can be interpreted as a time value. For instance, if F12 contains the number 0.5, then it is displayed as 12°00 for me using...

Excel: Changing cell references, nature of the beast, cell references

nature of the beast, cell references, cell a1: Based on your description it sounds like a search and replace should be able to accomplish what you need. 1) Select the range of cells containing all of the H formulas. 2) Click edit- replace 3) In find what, put in H 4) In replace with, put in X ...

Excel: Changing the color of the entire text in a row, format painter, format menu

format painter, format menu: select the row, then use Format menu, conditional formatting - set the condition to formula is and set the formula to =$F2= HOLD and set the formatting of the background as appropriate. You can use format painter to copy this formatting to other...

Excel: Changing the color, cell b2, cell references

cell b2, cell references, cells: Sarah, this is tough to do without cell references and values as an example, but I ll try. :) Assuming you have YESTERDAY s date in cell B2, and you want it to be red: Select cell B2 Hit Format-- Conditional Formatting Choose Cell value is Choose...

Excel: Changing colors in column charts, preset colors, rgb colors

preset colors, rgb colors, changing colors: You can do it by FIRST doing Tools/Options/Color Tab, selecting a color you want to change, click Modify, click the Custom tab, then type in the RGB coordinates. Do this for each color which is different from the original 40. Then, when you make your chart...

Excel: Changing columns to negative numbers, negative numbers, spreadsheet

negative numbers, spreadsheet, cells: You can write a formula in an adjacent column to convert them. If you re numbers are in column A, the formula would just be: =-(A1) You can them cut or copy and paste these VALUES only (don t paste the entire cells - use the Paste Special option in...

Excel: Changing columns into rows, giant column, vp marketing

giant column, vp marketing, blank row: Well, there is no formula per se that exists to do this. Typically, you have you write the one you need. In your case, it will be 2 -4 VERY long ones to get everything you need separated into their respective and desired columns. A macro, though it...

Excel: Changing data source on fixed dates, active day, time change

active day, time change, excel 2000: Hi Eric, I m not sure how you have the spreadsheet designed. Once you calculate the information you need for a given day, do you need to keep the formula? If not, you can replace the formula with the results. This way your formula can be based on the...

Excel: Changing dates every 14 days, possiable, a3

possiable, a3, increments: Hi David, In the example you provided I believe you can use the formula =INT((A3+12)/14)*14+2 in cell B8. The divide and multiply by 14 accomplishes the change only on 14-day increments, and the +12 and +2 bias the result as required to get the change...

Excel: Changing dates to seasons in excel, isnumber, relative scale

isnumber, relative scale, answer area: Should work fine - I ll translate it =IF(Month(F2) 4, WINTER ,IF(month(F2) 6, SPRING ,IF(Month(F2) 10, SUMMER ,IF(month(F2) =12, FALL , NONE )))) is IF the Month of the date in F2 is less than 4 display Winter else if the month of F2 is less than...

Excel: Changing the dates in my spreadsheet, spreadsheet, closer look

spreadsheet, closer look, fiscal year: Claire: The following should work; if it doesn t, please let me know and I will need to take a closer look at your data: 1) Click on Edit- Replace 2) In the find box, type 2006 3) In the replace box, type 2007 4) Click Replace all. The above will...

Excel: Changing in Excel zip codes to city and state, csv file, u s post office

csv file, u s post office, vlookup: Pat, since the list from the USPS has the zipcode in the leftmost column of the list (column A) you can do this with a vlookup Assume column F of your original sheet has the zip code with the first zipcode in F2. Assume the sheet from the USPS has been...

Excel: Changing an excel error message, unwanted cells, sheet tab

unwanted cells, sheet tab, cell contents: You can prevent the user from even SELECTING the unwanted cells. Right-mouse click the sheet tab, select View Code, then in the properties window (F4 if not showing), click EnableSelection, and change it to 1-xlUnlockedCells. Then, when the sheet is protected,...

Excel: Changing File names, weird situation, file names

weird situation, file names, spreadsheets: This is a feature - if the file is opened directly by double clicking, it gets put into a temporary directory, and more often than not then gets a number on the end - if the file is saved first and then opened from that saved location obviously it won t...

Excel: Changing Fill Color or Font Color based due date, format button, menu format

format button, menu format, formating: You can do this with the menu Format- Conditional formating. For the condition that you have described, you can select on the Condition 1 drop down Formula is and then type a formula like this in the text box: =A1 TODAY() Then click on the format button...

Excel: Changing font to all caps, excel 5, b10

excel 5, b10, cheers: Hi Phyllis, How are you? Please do the following 1. tools - macro - visual basic editor 2. insert - module 3. copy Sub ucases() Dim a As Range addr = InputBox( type the range address, eg, A1:B10 ) Set a = Range(addr) For i = 1 To a.Rows.Count...

Excel: Changing font Macro, macro in excel, free time

macro in excel, free time, formatting: You were informed correctly - you will need a macro for the first part. Unfortunately for you, I don t just write macros for people on this site who need one, want one, and/or seem to think I am expected to do so. 99% of all macros are more involved than...

Excel: Changing fonts in footers by macro, footers, spreadsheet

footers, spreadsheet, footer: Without knowing how you tried to do this, (you did not say) then I can t tell you where you went wrong. But try this, it should work: With ActiveSheet.PageSetup .LeftFooter = &06 & ActiveSheet.Parent.FullName .CenterFooter = &06 page &P .RightFooter...

Excel: Changing formulas with a macro, 1 feb, xls

1 feb, xls, cells: Rhonda I would like to answer different than you suggested, because I would not use code for this, though it s possible. I would suggest that you simply copy the data in the DATAENTRY.xls workbook (not he month names), then use Paste Special to paste...

Excel: Changing header/footer of all sheets at the same time., q2 q3, q1 q2

q2 q3, q1 q2, page rem: Hi Amna Here is your code. Hoepfully it does what you require. If so let me know or if not let me know about any problems associated with installation or running it. Sub doheaders() Rem set text for header myheader = Quarterly Report FY 06-07...

Excel: Changing line color, command buttons, command button

command buttons, command button, bottom border: Hi Jimmy How are You? Assumming the line is the bottom border of a range, please do0 the following 1. Tools - Macro / Visual Basic Editor 2. Insert a module 3. Copy Sub Blue() Dim S As Range Set S = Selection With S.Borders(xlEdgeBottom)...

Excel: Changing other cells in Excel, b16, target

b16, target, poof: You will need to use VBA - you don t need to worry about B16, but BASICALLY you will use the CHANGE event of the workbook to pick up that B15 has changed and set the value of B14 something like this Private Sub Worksheet_Change(ByVal Target As Range)...

Excel: Changing part of a cell, vba macro, excel formula

vba macro, excel formula, multiple times: As I recall, my macro returned letters only, not YELLOWRED, so I do t follow that. However, this formula should do what you ask: If the answer is in A1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, RED YELLOW , ORANGE ), BLUE RED , VIOLET ), BLUE YELLOW , GREEN...

Excel: Changing printer commands in Excel2000, printer commands, excel2000

printer commands, excel2000, printer command: Eric I m not a macro expert, but I believe that when you write the macro yu can go to page set up and change the zoom setting as part of the macro. Usually when I do this I simply start recordng the macro and then make the keystrokes to do what I want...

Excel: Changing text to be able to Average the totals, mediation specialist, budget supervisor

mediation specialist, budget supervisor, numeric value: Hi Cindy, First to answer your question on creating a formula that will keep a running total - or keep up with the added rows. Let s say you have a data list from E2:E20. Then you have your total total formula in E23. If you include the blank rows...

Excel: Changing value of cell, cell a1, visual basic editor

cell a1, visual basic editor: In the ThisWorkbook object of the Visual Basic Editor, use code like this: Private Sub Workbook_Open() this code will execute when wkb opens, but only if today s date is Oct 17, 2007 or later. If Date = DateValue( Oct 17, 2007 ) Then ...

Excel: Character limits in cells, cell a1, blank sheet

cell a1, blank sheet, cells: Hi Mike, Yes you need to copy the cells and not the whole worksheet. To do this select the sheet you want to copy and click on the Select All button. You will find this button directly above the 1 in row 1. Once all the cells are select press Ctrl+C...

Excel: Chart, stock room, number thanks

stock room, number thanks, weight lbs: Still not clear to me why you need more columns. The example has 7 columns. What are the others supposed to contain? I also can t envision what your chart would look like. Can you mock one up and put it on a workbook & send it to me at bobumlas@yahoo.com...

Excel: Chart, column chart, budget data

column chart, budget data, amouts: 1. I would suggest a column chart. This website is a little old. But it shows the basic way to create a column chart. http://ed.fnal.gov/data/life_sci/data/tutorials/excel_steps.html 2. When you have the chart with the bars showing down. You need to...

Excel: Chart - age & sex, chart wizard, wizard tool

chart wizard, wizard tool, column chart: Hi, Srikanth, I assume that you have 3 columns of data: Age, Male, and Female. Creating this chart is so easy: 1) Click any single cell inside of your data. 2) On the toolbar, click the little Chart Wizard tool. (It looks like a teeny column chart...

Excel: Chart axes, secondary axis, axes

secondary axis, axes, xl: Sarah, Well surprise! There is indeed an easy way to do that. You create your chart, add two or more series to the chart, then right-click one of the series and in the properties specify that it uses the Secondary Axis . You ll then see a new axis...

Excel: Chart axes, chart wizard, hth

chart wizard, hth, axes: There s a way; - replicate your series, so if you originally had 1 2 3 you now have 1 1 2 2 3 3 or better yet: 1 =A1 2 =A2 3 =A3. Then select all the values, and in the chart wizard,step 1, click on the Custom Types tab; select...

Excel: Chart axes, douglas m smith, secondary axis

douglas m smith, secondary axis, style charts: Hi Sarah, Surprise, it possible! I use it once in a while to show things raw numbers and percentages on the same chart with different y scales. After you create the initial chart, right-click on the plotted data you want on the right-side y-axis. Select...

Excel: Chart / left column orientation, alignment tab, data labels

alignment tab, data labels, orientation: I have been unable to reproduce your problem, can you please send more details on the data that you are using and the steps that you take? In any case, you can usually change the orientation of the data labels by selecting the labels, right click on them,...

Excel: Chart Data Lables, data labels, percentage formula

data labels, percentage formula, bar graph: I m not sure which part of the question is actually the question - but I m assuming you are OK working out the percentage just not how to put it on the chart. You also haven t said how you have got the two values on as data labels - my OWN method would be...

Excel: Chart Object Coding Issue, axis scale, xunit

axis scale, xunit, xmax: A few things are wrong. When you said ActiveChart.SetSourceData Source:=DataRange, PlotBy:=xlColumns then the chart only has one column of values, or one series. So any reference so SeriesCollection(2) or (3) will fail. Also, you specify Range(TimeFrame),...

Excel: Chart question, budget data, amouts

budget data, amouts, info charts: Charting is not something I do an awful lot of, so I have a list of websites I can refer to if I need to do clever things - these sites are http://processtrends.com/ http://peltiertech.com/Excel/Charts/index.html http://www.andypope.info/charts.htm...

Excel: Chart window orientation, axis scale, dot plots

axis scale, dot plots, scatter chart: You don t want to actually rotate the chart. If you want to swap the X and Y axes, you can do that at the Source Data window. I don t see how that would cause any y-axis scale problems. If you just want to show one or both of the XY axes in reverse...

Excel: Chart, pivot table, root cause

pivot table, root cause, visualise: It is possible, but it s difficult to (a) visualise and (b) explain! Is it possible to see a copy of the report (my email is aidan.heritage@virgin.net) - my thoughts are Put in a column with a formula to convert the date into a fixed value =text(DateCell,...

Excel: Charting, empty space, spreadsheet

empty space, spreadsheet, email address: Hey Guy, Would it be possible if you could email me your spreadsheet? I can try to make a change to your macro if you can. My email address is bluecat74@yahoo.com Otherwise, I think this may answer your question: When you are creating your data set,...

Excel: Charting and analyzing Times' times data, new york times crossword puzzle, blank cells

new york times crossword puzzle, blank cells, crossword puzzle: Dear Johnny Whew! That s quite a question. My suggestions are as follows: 1. Why don t you put a separate line on the chart for each day? You can add another days data by going to the chart, right clicking, select Source Data , click Series then Add...

Excel: Charting and analyzing Times' times data, new york times crossword puzzle, blank cells

new york times crossword puzzle, blank cells, crossword puzzle: You could plot all the data, but as set up right now, all the times for a single week would fall on a single day and you would need multiple series. Otherwise you would need to organize a sheet where all the data was arrayed in a single column (this wouldn...

Excel: Charting and analyzing Times' times data, new york times crossword puzzle, blank cells

new york times crossword puzzle, blank cells, crossword puzzle: send me your email and I ll send you your file with all data on one chart. you can email me at bobumlas@yahoo.com. I m not sure what you re looking for for the rate of change - that s a math problem, not an Excel problem. Re P.S. -- what do you mean by...

Excel: Charting and analyzing times data, new york times crossword puzzle, blank cells

new york times crossword puzzle, blank cells, crossword puzzle: I m afraid that data analysis of this nature is outside of my area of expertise, however, using the tools menu then addins you will find you can add the analysis toolpak - this will give you analytical tools - you may also want to check out programs such as...

Excel: Charting info, axis scale, chart wizard

axis scale, chart wizard, column chart: One way, and there may be others, If you edit the individual data series in the chart wizard, each series has a box for x-axis lables. Set this range to a range of labels you want to appear on the x-axis. Make the cell in that range contain a zero and...

Excel: Charting, secondary axis, column chart

secondary axis, column chart, side column: Column charts aren t really meant to be displayed this way. However, if you stagger your data, you can do it. If your original data was: 1 2 3 1 4 3 you need to rearrange it to be 1 2 3 1 4 3 and when you chart A1:B6 (in this...

Excel: ChartObject Data from Different Sheets VBA, w49, g45

w49, g45, m45: Sub ChartFromSeveralSheets() curr = ActiveSheet.Name Charts.Add With ActiveChart .ChartType = xlColumnClustered .SeriesCollection.NewSeries .SeriesCollection.NewSeries .SeriesCollection.NewSeries ...

Excel: Charts, conditional formatting, style chart

conditional formatting, style chart, ends nov: What you are after is a project manager style chart, and excel doesn t do this (MS Project does!) What you COULD do is to create a worksheet with conditional formatting that functioned as your project planner, but shading the cells that fall within the...

Excel: Charts and, fancy charts, inventory worksheet

fancy charts, inventory worksheet, charts and graphs: Trina, welcome to AllExperts. I m not sure where to start. I always find it interesting when someone like yourself decides that something must be able to be done better. 8 years is a decent track record, so anything new would have to work well, or your...

Excel: Charts, value choices, chart changes

value choices, chart changes, excel sheet: Once you re looking at the chart sheet, put a text box anywhere you want from the drawing toolbar, then click in the formula bar, type =, then switch to the sheet containing the text you want to display, then press enter. Now you ll see something like =Sheet1!$C$1...

Excel: Charts, grid lines, many thanks

grid lines, many thanks, axis: You can manually adjust the scale and limits of the axis by doing the following: - Select the axis on the graph that you want to modify. - Right click on it, and select Format Axis from the emergent menu - Go to the Scale tab, and enter values for Minimum...

Excel: Charts, shift key, graphs

shift key, graphs, row sizes: For the settings of the sheets, like text and row sizes, you can do your adjustments at the same time in all the sheets, by selecting them all and making your changes in the visible one. To select all the worksheets, you can click on the worksheet tab of...

Excel: Charts, data labels, efficient frontier

data labels, efficient frontier, linegraph: IF I have understood the question correctly:- click on the chart, then right click the dots, use the format data series button and add the appropriate data labels You might find these pages useful for charting http://processtrends.com/ http://peltiertech.com/Excel/Charts/index.html...

Excel: Charts in Excel, chart wizard, excel 2000

chart wizard, excel 2000, aidan: The easiest way to see the difference is to click the button, as the preview will change but basically series in rows You have (say) the month in column A and the value in Column B, but each record is one row below the previous one Series in Columns...

Excel: Charts in excel, sure what version, 3d bar

sure what version, 3d bar, chart menu: Hello, Reiner, try these steps. 1) In the beginning, plot all three data series in columns or bars. 2) Click on the third data series. 3) On the Chart menu, choose Chart Type. 4) Specify a line, or whatever you want this third data series to be....

Excel: Charts in excel, default format, stock chart

default format, stock chart, different color: Hi, Sandy, If I m understanding you correctly, you can do a simple copy/paste. Simply copy HP s data (the same size as the other companies data, of course), and copy it to the clipboard. Then select the chart and paste it in. It will be inserted...

Excel: Charts with multiple data and Form Controls, data labels, number charts

data labels, number charts, numbers report: I d probably set it up so the chart reference range is always the same and you have a dropdown to select which range to pull into the range to be represented on the chart. This way you could easily include logic in the cell formulas to divide the number by...

Excel: Charts on a Useform, loadpicture function, image control

loadpicture function, image control, gif file: Yes. Add an Image control to your form. Write VBA code to save the chart as a GIF file, and then set the Image control s Picture property to the GIF file. You need to use the LoadPicture function to do this. Saving a chart as a GIF file The code...

Excel: Charts Using More Than One Worksheet, chart wizard, hi amy

chart wizard, hi amy, plot area: Hi Amy, De nada (regarding yesterday s help). The easiest way I have found to assign data from multiple worksheets is to create the chart using the chart wizard, initially assigning arbitrary (and wrong) ranges to the various chart series . Once the...

Excel: Charts, word perfect document, chart elements

word perfect document, chart elements, bar graph: Jane You can resize the main window and then click on the chart and resize it as well (ie when you resize the chart window it increases the size of the graph, you then click on the graph itself so that it has a shaded border, go to the corner and then resize...

Excel: Check Box and Excel, curly braces, cell attachment

curly braces, cell attachment, check boxes: Yes, the curly braces are correct. You assign a checkbox to a cell by how I described: For EACH check box, right-click it, select Format Control, in the cell link box, enter a cell address with = , or click a cell. Attachment coming in private email.....

Excel: Check Box formula, cell a1, d7

cell a1, d7, d8: Link the checkbox to cell A1, so that A1 is TRUE if the box is checked. In cells C8:C11, allow user entry, so that for example if the checkbox is checked, D8 will display whatever value is in D7, but if it s unchecked, it will display whatever the user has...

Excel: Check Box Link, joe carey, link source

joe carey, link source, check boxes: Huh? First you say that you want them to work together ...linked to another check box so that they work together in different worksheets... . Then you say that you want them to work dependently ...however can not link them to work dependently.... ...

Excel: Check Boxes, data validation, check boxes

data validation, check boxes, missing something: Shawn If you use the form check box it must be locked so that it will work and if unlocked it can be edited. One option is to use Data validation for what you want. Have a look at this link and if you find something suitable try it out. If not and...

Excel: Check Boxes, developing a questionnaire, season greetings

developing a questionnaire, season greetings, check boxes: Hi Dwayne, Very often I misunderstand the question in the short amount of words expressed. So if I misunderstood your question you can email me an example to cloverken@yahoo.com Otherwise I have shown a few examples of how to use the checkbox or option...

Excel: Check box help, quick responce, excel spreadsheet

quick responce, excel spreadsheet, work sheets: Antonio, just to make sure, the code we are talking about is in this container: Private Sub CheckBox1_Click() End Sub Correct???? If it s not in that container, then the code needs to be forced to execute in some other fashion. Now, at this point,...

Excel: Check box or radio button in form, butttons, cell border

butttons, cell border, radio buttons: I await your file at my yahoo account. meanwhile, you can have the group box border not show by overlaying 4 teeny rectangles just covering each border & mark the rectangles as no line, but leave the fill. Then you re effectively covering the borders of the...

Excel: Check boxes and e-mailing the list, mail letter, check boxes

mail letter, check boxes, thanks for your patience: you can email me if you want to bobumlas@yahoo.com. I m not very experienced in using email from within Excel - for that part you d probably have to contact another. The checkboxes part is pretty simply, but each checkbox would have to be associated with a...

Excel: Check comma if entered in row c60-c61-c62 and notify a message when user tries to save the file, parent cells, pl sql

parent cells, pl sql, rng: Dim cellWithComma As Range Set cellWithComma = Range( C60:C62 ).Find(What:= , , LookIn:=xlValues, _ LookAt:=xlPart) If Not (cellWithComma Is Nothing) Then MsgBox The cell & cellWithComma.Address(False, False) & _ ...

Excel: Check Digits?, california employment development, california employment development department

california employment development, california employment development department, employment development department: Hi Stephen! How are you? Please try the following: 1. TOOLS - MACRO - VISUAL BASIC EDITOR 2. INSERT - MODULE 3. COPY (that function is better, more general) public Function vericode(A) n = A Do While n Int(n) n = n * 10 Loop dig = Int(Application.Log10(n))...

Excel: Check duplicated data from row B77-B132, irow, inrange

irow, inrange, row numbers: In your Visual Basic Editor, enter this code in the WORKBOOK object: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wks As Worksheet Dim rngToCheck As Range, topCell As Range, findCell As Range Dim inRange...

Excel: Check for duplicates in a column (Exel), chip pearson, chris parsons

chip pearson, chris parsons, conditional formatting: Hello Chris, You haven t defined what is a duplicate. If a single cell contains 5,10,11,16,18, then is there duplicate only if another single cell also contains 5,10,11,16,18. Or if another cell contains a 10 in its string of 5, is that considered a...

Excel: Check marks in a cell, spreadsheet, clipart

spreadsheet, clipart, attendance: Hi Dick, There are a couple of ways to accomplish this. (Probably more than a couple, but we ll go with the simplest ways (no macros). 1. You can format your cell to use the Wingdings2 font and enter a capital R . By entering an R in the cell, you...

Excel: Check for multiple entries within a column, last question, microsoft excel

last question, microsoft excel, facsimile: Try this, Karen. If nothing else, you ll learn a cool feature of Excel. :) Data-- Subtotals. For column A, use the function COUNT. Then, collapse A so that it only shows the counts. Then, click the Plus sign in the sorting area for any of the counts that...

Excel: Check Range Duplicates, irow, a99

irow, a99, a9: Your argument for the COUNTIF that determined WHAT to count is coming from iRow, which is 1, then 2, then 3, etc. It should be the next CELL within the loop, as I have modifed below. See instances of EachCell Sub OneToNine() Check range A1:A9 for...

Excel: Checkbox cell, target value, cell b2

target value, cell b2, target address: Without form or activeX, you d need VBA code. This should do it - right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address $B$2 Then Exit Sub On Error Resume Next...

Excel: Checkbox in Sheet, check sheets, sheet1

check sheets, sheet1, checkbox: Place CheckBox1 on Sheet1 using the Control Toolbox toolbar. Then use vba code like this: Sub chkBox() Dim check As OLEObject Set check = Sheets( sheet1 ).OLEObjects( CheckBox1 ) If check.Object Then [first action] Else ...

Excel: Checkbox trigger, select tools, tmv

select tools, tmv, g6: Hi Glen, Yes, this can be done. It involves code that unprotects the sheet, locks (or unlocks) the cells, then re-protects the sheet. The following code example shows how this can be done: Private Sub CheckBox1_Click() With Worksheet( Sheet1 )...

Excel: Checkboxes, cb, work book

cb, work book: Assign ALL the checkboxes to the macro Clicker , then use this code: Sub Clicker() ActiveSheet.Select For Each cb In ActiveSheet.CheckBoxes If cb.Value = xlOn Then Sheets(cb.Caption).Select False Next End Sub Sorry -- meant...

Excel: Checkboxes and Combo Boxes, combo boxes, listindex

combo boxes, listindex, control tab: Would I be right in assuming that these controls are from the forms toolbar? The easiest way is to link the controls to a cell (right-click the control, select the Control tab, and set the Cell Link. The combo box will return the listindex to that cell,...

Excel: Checkboxes in Excel, sort objects, excel sheet

sort objects, excel sheet, checkboxes: Tools/Options/Edit and check Cut, copy, and sort objects with cells will solve the problem of moving rows, bot not for deleting. Unless you want to resort to a macro (not an easy one), you re going to have to manually delete the checkboxes as well (before...

Excel: Checkboxes in Excel spreadsheet, excel spreadsheet, checkboxes

excel spreadsheet, checkboxes, checkbox: Hi Kathy, Thank you for your kind words Thanks Ken ...

Excel: Checkboxes in Excel, cbox, tab view

cbox, tab view, excel sheet: In addition, in case the previous solutuin is not quite working for you, you could also copy in the worksheet code (right click on worksheet tab - View code: Private Sub Worksheet_Change(ByVal Target As Range) For Each Cbox In ActiveSheet.CheckBoxes...

Excel: Checking columns of data for matching cells, then identifying where unmatched cells are, columm, pivot table

columm, pivot table, quick response: Pivot table MIGHT still work if you put the rows where the columns are and the columns where the rows are. Could you send me some sample data with the type of report you are trying to see (email is aidan.heritage@virgin.net). Also note the suggestion I made...

Excel: Checking Duplicated data, parent cells, cell interior

parent cells, cell interior, range rd: Here are some adjustments to the code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim rng As Range, cell As Range, rd As Range Dim rb As Range, rngb As Range, s As Range Dim s1 As Range On Error Resume Next ...

Excel: Checking for duplicates on the fly, colorindex, contiguous area

colorindex, contiguous area, macro code: You can select a range using the mouse and return the selection using Application.InputBox Dim rngA as Range On Error Resume Next set rngA = Application.InputBox( _ Select Range with mouse ,type:=8) On Error goto 0 if not rngA is nothing then ...

Excel: Checking duplicates, color rgb, interior color

color rgb, interior color, aaa: Jawad, I already answered your original question on this issue but you didn t include that code. I integrated the functionality in the BeforeClose event. At the beginning of the beforeclose event, it colors the B77:B132 back to blue and then reevaluates...

Excel: Checking for grade regression, cell groups, representative data

cell groups, representative data, format date: Send me a sample workbook with representative data that illustrates where your data is, where you want the output and covers all situations to be tested for and I will see if I can give you a formula. Saying you have 12,2 cell groups is pretty meaningless...

Excel: Checking row id if filled saving the document, number rows, patient days

number rows, patient days, excel sheet: Jawad If I understand the question correctly you want to create the inability to put numbers in a cell or save, etc.etc. dependent on another cell. I am guessing that this can be done, however I do not know how to do it. I suggest that you reask the...

Excel: Checking if row id are filled then saving the worksheet, number rows, patient days

number rows, patient days, base font: Hi Jawad, Similar to the one sent earlier, you need to update the code that i sent earlier to the following: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim xlR As Excel.Range Dim xlS As Excel.Worksheet Dim i As Long...

Excel: Checking tables if I have customer no, good starting point, customer number

good starting point, customer number, list of names: The question is too complex and unclear for me to answer exactly. However, I ll show you a simple sample of code that might be a good starting point. Create a list of names with the range name indata . Then create a different list with the range name ...

Excel: Checks comma in rows and notify user when he tries to save the worksheet, parent cells, pl sql

parent cells, pl sql, rng: Hi Jawad, what you could do is before the ActiveSheet.Protect Password:= sjghc , _ DrawingObjects:=True, Contents:=True, Scenarios:=True statement, you can put this code: Dim comma_count As Long comma_count = 0 comma_count = InStr(1,...

Excel: Choice lists defining formulas, consecutive columns, vlookup formula

consecutive columns, vlookup formula, vlookup function: Keith One way to handle the problem is to create a list of choices and in the list use the choice name and the unit price and the unit type (sq feet, linear feet, each, etc. Then use the VLOOKUP Function to lookup the items you are using in the project....

Excel: CIRCULAR REFERENCE, circular reference, circular references

circular reference, circular references, excel 2002: Irena have a look at these links http://office.microsoft.com/en-us/assistance/HP052002851033.aspx http://www.uwec.edu/help/Excel03/trshoot.htm http://www.ozgrid.com/forum/showthread.php?t=16699 One thing about circular references is that you...

Excel: Circular Reference or VBA, cell c5, cell d5

cell c5, cell d5, circular reference: I m not quite following what you are doing. Why do you have a Summary sheet that appears to be included in a total in one of the summarised sheets? Have a look at the Summary sheet example here http://www.excel-it.com/workbook_downloads.htm See if...

Excel: Classes in VBA, chip pearson, class instances

chip pearson, class instances, that assign values: Joana Basically you need to put you objects (class instances) in a collection and then you can do a for each through the collection. Chip Pearson has given some excellent discussions on Classes and you might find two of his posts useful: http://groups.google.com/groups?threadm=eRE4J4Z2BHA.2840%40tkmsftngp05...

Excel: Clearcontents Method, pool league, word number

pool league, word number, weird problem: Sorry I don t accept attachments, virus issues more than anything else - been burned once and never again. This one just may have to remain a mystery (or an undocumented feature of EXCEL) since you can get it to work in another workbook. And, incidentally,...

Excel: Clickable Drop Down List, data validation, range names

data validation, range names, names thanks: Not sure what you;re asking. You want a dropdown list for range names? use the Name box top-left of the Excel window, or in a formula hit F3. A dropdown in a cell, with a list of sheets is possible by creating a static list of them on some worksheet, naming...

Excel: Clipboard, microsoft excel 97, microsoft knowledge base

microsoft excel 97, microsoft knowledge base, clipboard option: The only thing in the Microsoft Knowledge base on this is :- XL97: Cannot Empty the Clipboard Error Message When Dragging Cells Article ID : 196620 Last Review : March 1, 2002 Revision : 1.0 This article was previously published under Q196620 ...

Excel: Clipboard, versin, old computer

versin, old computer, clipboard: Paula That does sound annoying, I m not gonna be much help, cause I haven t experienced it nor have I had any other quesitions regarding the problem. (I have a computer with XP on it and an old versin of excel) There are several ways to do the copy maybe...

Excel: Clock Update, recalculation, time and date

recalculation, time and date, current time: now() will return the current time and date, but will ONLY update when the sheet is forceably recalculated (print, save, F9 would all update it). Your question doesn t explain why you want this to happen, but it sounds as though it s some form of test (possibly)...

Excel: Closed Workbook Data Import, vb knowledge, api functions

vb knowledge, api functions, excel user: There are several ways of doing this. All are proper ways and all but one involve some level of knowledge in macros. All are proper ways and each has advantages over the other. Which method you use all depends on EXACTLY what you need to do. There is...

Excel: Closing a reference Sheet with main sheet - XLS97, opend, reference file

opend, reference file: Interesting problem, but I did solve it. Suppose there are 2 workbooks named referenced.xls and master.xls (which contains the reference to referenced.xls ) In the close event (or auto_close procedure) of master.xls, do something like this: Private...

Excel: Closing a Workbook, electronic worksheet, close application

electronic worksheet, close application, bug problem: Use this: ThisWorkbook.Close SaveChanges:=True This will save and close the workbook that has the code in it. ThisWorkbook refers to the workbook that has the code in it Use: ThisWorkbook.Close SaveChanges:=False if you do NOT want to save...

Excel: How can I make the CODE unique?, analysis toolpak, cell a2

analysis toolpak, cell a2, conditional formatting: Hi again, Excel does not have feature to prevent you from entering a duplicate number like Access does, but there is a way to let you know if it happens. Select the cells your CODE values will be entered in and select Conditional Formatting... from the...

Excel: COLUMNS/numbers, numeric characters, hyphens

numeric characters, hyphens, hyphen: It s not infinite, as excel only has colums up to IV and rows to 65536 (from memory) but that s still pretty big - you can easily use all the space by putting borders on entire rows and columns for example - various ways of resolving it (I have a macro that...

Excel: COMPARING CELLS, 6 digits, combination of the two

6 digits, combination of the two, d6: If they are the same length, then I assumed that off by 1 character means that the 2 cells have exactly the same characters in the same position, except for one. If they re different # of characters, then how do you define off by 1 character ? You must...

Excel: COMPLICATE EXCEL FORMULA, ah4, excel formula

ah4, excel formula, accurate value: Here s an example. If you want cell A1 to always contain the error value #N/A, then enter this formula in A1: =NA() If, for example, you want a cell to contain D1/D2, but if D2 is zero, you want the result to be #N/A instead of #DIV/0, then the formula...

Excel: COMPLICATED EXCEL FORMULA, ah4, excel formula

ah4, excel formula, accurate value: Hi Charles, I think I would use and IF OR statement like this. =IF(OR( MONTH TO DATE !AH4=29, MONTH TO DATE !AH4=30), N/A ,0) But my real problem is I don t fully understand how your spreadsheet works. If you can send me an example that would be great...

Excel: COMPLICATED EXCEL FORMULA, ah4, excel formula

ah4, excel formula, accurate value: Ah4 should have something like 1 rather than =0 (only 0 is equal to 0, but -2,-1 and 0 are all less than 1). Probably the use of the iserror function with the dateserial function would work well for you as well if(iserror(dateserial(sheeet name & / ...

Excel: COMPLICATED EXCEL FORMULA, ah4, excel formula

ah4, excel formula, accurate value: Hi Charles, I understand the idea behind what you re trying to do, and I think adjusting your IF/THEN formula would probably work, but without seeing exactly what you re doing and how it looks, I don t have a suggestion I d stand behind. Can you provide...

Excel: COMPLICATED EXCEL FORMULA, ah4, excel formula

ah4, excel formula, accurate value: CHARLES Those pesky error messages can be a bit of a problem. You can use these to eliminate or change the error message-- =IF(ISERROR(A2/A3), NA ,A2/A3) Returns NA when the value is an error =IF(ISERROR(A2/A3), - ,A2/A3) Returns a dash when the...

Excel: COMPLICATED FORMULA FOR EXCEL, ah4, accurate value

ah4, accurate value, d23: Thanks for the explanation, Excel has the formula for your needs. Is is called NA(), and you can use it directly on the IF formulas as one of the options: =IF(B23=0,NA(),D23/B23) With this in mind, you can craft your worksheet to meet your needs. Hope...

Excel: COMPLICATED FORMULA FOR EXCEL, ah4, accurate value

ah4, accurate value, d23: Charles: Okay, I think I m following you here, but if not just email a spreadsheet to me at Lotus@swbell.net and I ll take another look. From your description it appears this formula: = MONTH TO DATE !$AH$4-30 Is returning 0, -1, or even -2 depending...

Excel: COMPLICATED FORMULA FOR EXCEL, ah4, accurate value

ah4, accurate value, d23: Charles, Hope you are doing well. I think this solution will work for you. 1. In the place that you calculate = MONTH TO DATE !$AH$4-30 which takes values 0, -1, -2 (the ones you want to avoid, change the formula to =IF(( MONTH TO DATE !$AH$4-30)...

Excel: COMPLICATED FORMULA FOR EXCEL, ah4, accurate value

ah4, accurate value, d23: Charles, I may not have understood the problem. But if you are referring to formula - = MONTH TO DATE !$AH$4-30 returning values positive or N/A, it is simple- =IF( MONTH TO DATE !$AH$-30 =0, N/A , MONTH TO DATE !$AH$-30) If this is not the answer you...

Excel: If, Then, and CONCATENATE, dynamic filter, horizontal row

dynamic filter, horizontal row, vertical column: I said at the start of the last answer I wasn t sure I understood the question - this is now definitely the case - or rather, I don t follow exactly what you are trying to achieve - it sounds POSSIBLY as though you need a dynamic filter (which can be done...

Excel: CONCATENATE IN EXCEL 2003, concatenate in excel, information text

concatenate in excel, information text, decimal places: As you have no doubt discovered, concatenation is good BUT looses all formatting - the TEXT function will come to your aid though!! =A1 & text(B1, 0,000 ) will concatenate A1 and B1, with B1 formatted in the manner you described - replace the 0,000 with...

Excel: CONCATENATE FORMULA, cell b5, 4 digits

cell b5, 4 digits, social security number: FOLLOWUP - F9 forces the spreadsheet to recalculate - which would only work if somehow calculation has been set to manual on the workbook. To turn automatic calculation back on click tools- options- and then select the calculation tab. Change the option...

Excel: CONCATENATE and Italic, conditional formatting, concatenate

conditional formatting, concatenate, fonts: You cannot change the font with formulas, you still can format the cell in advance, or use conditional formatting. In your case, it looks like conditional formatting will fit better, if you can come with a condition that the cells must fulfill and can be...

Excel: CONDITION FORMULAS, microsoft office 2000, zero number

microsoft office 2000, zero number, a3: Dear Laura Suppose the values you have are in cells A1, A2 and A3. Then the formula you need is something like this: =IF(COUNTIF(A1:A3, 0 )=0,0,SUM(A1:A3)/COUNTIF(A1:A3, 0 )) This checks how many values are non-zero, and uses that number to calculate...

Excel: CONDITIONAL FORMAT, douglas m smith, conditional formatting

douglas m smith, conditional formatting, format button: Hi Philip, Yes, this can be done. Select the row or cell range you want to change color and then select Conditional Formatting from the Format menu. In the first dropdown box, select Formula is . ( Cell is for a looking at it s own value, Formula...

Excel: CONDITIONAL FORMAT, set criteria, formating

set criteria, formating, a5: Philip You should be able to do it with conditional formating and using the formula is instead of the cell value is . Using something like the formula =A1:A10= A Meaning if A1 thru A10 is equal to A then format. Check out the help on conditional...

Excel: CONDITIONAL RESPONSE, cell d1, conditional formatting

cell d1, conditional formatting, e1 11: 1) Here s the code to put text in a cell, with a carriage return in the middle. Sub inCell() ActiveCell.Formula = first line & Chr(10) & _ second line End Sub Here s code that displays a message box with a carriage return. Sub inBox()...

Excel: CONDITIONAL, logical test, test a1

logical test, test a1, circular references: For cases like this I would suggest to use an additional formula and then play with the layout, to hide the cell that you don t want your users to see. This way, the formulas are kept simple, something like: =IF(Logical_test,A1,A1+1) It is easier to deal...

Excel: CONVERT function problem, timevalue, excel help

timevalue, excel help, time functions: Hi Andrew How are you? Please see the following 1) =CONVERT(H2*24, hr , mn ) or 2) type in H2 =TIMEVALUE( 07:20:00 Am )*24 and in I2 =CONVERT(H13, hr , mn ) Also look up the Excel help for time functions (hour, minute, etc) Cheers Adelaid...

Excel: CONVERTION OF NUMARIC IN TO WORD, excel sheet, convertion

excel sheet, convertion, invoice: Mazhar The closest conversion is the function TEXT, which will ocnvert, but I don t believe will do what you want. Check it out in the help section. I would suggest that you use excel for both the wording and numbering in the invoice and it should work...

Excel: COPY, excel spread sheet, deposit balance

excel spread sheet, deposit balance, bank balance: It looks like you need to return the latest number on a column. To do that, you can use the OFFSET function, something like: =OFFSET(D1,COUNTA(D:D)-1,0) This will return the last value of column D, if your data starts at row 1. You can change the column...

Excel: LAST COST ANALISIS, inventory summary, spreadsheet

inventory summary, spreadsheet, quantities: Chad You can link the formula tothe Invent in sheet and this will give you the last cost for each item. If I read what you want right that is. If you are looking for costing for both the new and the old then you would need to do an average formul...

Excel: IF + COUNT + OR, countif, sheet1

countif, sheet1, moshe: Your question is, I wish to count and find the word NEW in column B or in column B in the other sheet . Do do this you can use the following formula: =COUNTIF(Sheet1!B:B, NEW )+COUNTIF(Sheet2!B:B, NEW ) This will give you a combined count of the...

Excel: COUNT field incorrect, run time error, run time error 1004

run time error, run time error 1004, dbase tables: It sounds like an OBDC driver incompatiblity. Are the machines that this works on using a different version of Windows than the machines is doesn t work on? Robert ====================================================== Want to learn more? I write and...

Excel: COUNT formulas, dsum, pivottables

dsum, pivottables, pivottable: Providing that your sales are recorded in a table format, then you can use a PivotTable or the functions COUNTIF, SUMIF, DSUM or DCOUNT. There s an article on PivotTables at www.excel-it.com, I would need to know what Columns contain the relevant data to...

Excel: COUNT and IF questions, workday, delivery dates

workday, delivery dates, b1: As with any solution, there may be a more elegant way to do it than what I propose - nevertheless, this solution appears to work :). I took your idea of using COUNTIF and merged it with the INDIRECT function in order to only add 1 day to orders over the...

Excel: IF + COUNT + OR, countif, old hope

countif, old hope, greter: It looks like what you need is the same formula in each case, added together, but the second formula needs to reference the second sheet. As follows: =COUNTIF(A1:A10, 123 )+COUNTIF(Sheet2!A1:A10, 123 ) This simply says count if the client number...

Excel: COUNTA...COUNTIF...IF....not sure which one (if any) I need, blank cells, countif

blank cells, countif, c29: Barrett: There are a variety of ways to do this so I ll lay out a couple and let you pick the one you prefer: If your blank cells without names are actually displaying zero, then this should work: =COUNTIF(C28:C39, 0 ) If your blank cells...

Excel: COUNTIF(AND, array formula, ep 3

array formula, ep 3, mp 2: Sounds like an array formula! =SUM(IF(MONTH(A1:A100)=MONTH(C1),IF(YEAR(A1:A100)=YEAR(C1),IF(B1:B100= EP ,1,0))) A1:A100 is the range with dates you want to check, C1 is the output date - enter this formula by pressing Ctrl Shift Enter. Basically, any...

Excel: COUNTIF, fast track, spreadsheet

fast track, spreadsheet, pupils: Right here, Noor: http://www.officearticles.com/excel/index.htm I m not sure if you need to COUNT unique values or COUNT using multiple criteria. See which one works for you. You may find many of my articles helpful to finishing your course. :) I hope...

Excel: "COUNTIF" formula problem, time joe, joe joe

time joe, joe joe, d5: Joe, that s an interesting weakness of Excel. You can use the functions on named ranges that are contiguous, but not if they aren t. Microsoft suggests that you rearrange the data to make it contiguous - nice of them wasn t it! You can add multiple formulas...

Excel: COUNTIF formula, cell d1, array formula

cell d1, array formula, array formulas: Hi Mike, What will work is an array formula. If you have not used an array formula this is a website that explains more about it. http://www.cpearson.com/excel/array.htm If your numbers are in cells A1:A5 and the Cold or Hot are listed in cells...

Excel: COUNTIF on protected workbooks, pivottable, password protection

pivottable, password protection, correct answer: It s not something I have ever tried before and I have no idea how you can get round the password protection which I believe is causing this. It might not work but have you considered using a PivotTable instead? The data can be in another workbook, but...

Excel: COUNTIF three way array, column c, b1000

column c, b1000, sumif: This is no problem - use the SUMPRODUCT function - there is a sneaky way of getting this to do what you want which is to force it to return 1 if a test is true and 0 if a test is false - this will either do a SUMIF or a COUNTIF - in your case =sumproduct(--(a1:a1000=...

Excel: COUNTIF but in VBA, countif, sum function

countif, sum function, many thanks: 1. You don t need the SUM function at all here. Like =SUM(3+7) You get 10 whether or not SUM is used. The easiest way to get what you want (including the unnecessary SUM) is: Ans=Evaluate( SUM(COUNTIF(Temp_Results!W:W,Val1CellMenuWeekly3)+COUNTIF(Final_results!W:W,Val1CellMenuWeekly3))...

Excel: COUNTIF, array formula, hold down ctrl

array formula, hold down ctrl, curly brackets: Thanks for the very nice words, Dave. :-) Yes, I do see the comments that you input after receiving an answer. What you want to do here is very doable indeed. You need what s called an array formula. In case you re not familiar with it, an array formula...

Excel: COUNTING, douglas m smith, array formula

douglas m smith, array formula, countif: Hi Alan, I m guessing the extra spaces are in the column B entries. You can add an asterisk after the n in open : =COUNTIF(B:B, open* ) This will count any cells that start with open , including words like opens or opened. Another variation...

Excel: Code to customized filtering, column menu, perference

column menu, perference, multiple records: I ll sort out a file and email it to you. The mismatch is PROBABLY because line breaks are being created in the code that shouldn t be there - the IF ... THEN is all on one line (long line!) You are right that I ve made an error in the code - I was...

Excel: Code to Delete Excel File, dateserial, blank worksheet

dateserial, blank worksheet, ken wilson: Something like this will do what you want n = Now() y = ActiveWorkbook.BuiltinDocumentProperties( Creation Date ) MsgBox n - y in this instance, I ve used the current date - I would be inclined to do this as you can then test for the time difference....

Excel: Code doesnt bring up the error message, parent cells, rng

parent cells, rng, loop through: What you posted worked fine for me. I have modified it to go ahead and loop through all cells without using special cells. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim...

Excel: Code, excel worksheet, steve steve

excel worksheet, steve steve: Right-click the Excel LOGO (near the File menu), select View Code, paste this in: Private Sub Workbook_Open() ans = GetSetting( Steve , Steve , Steve , 0) + 1 If ans Mod 4 = 0 Then If InputBox( Enter password ) a Then ...

Excel: Collapsing columns in Excel?, hierarchical fashion, detail data

hierarchical fashion, detail data, menu point: Yes, you are refrring to Data Outline. Your sheet has to have been otlined first. If you do not see the outline symbols on the worksheet, select the Outline symbols check box on the View tab of the Options dialog box (Tools menu). From HELP: Outline...

Excel: Color Change, conditional formatting, formating

conditional formatting, formating, good afternoon: Jim You can use the conditional formating to do what you want. Go to toolbar/format/conditional formatting. In the conditional formatting window, first box Select cell value is In the second box select equal to In the third box Clic in the box then...

Excel: Color coding Calculations, exhibitor categories, additional email

exhibitor categories, additional email, column c: Crystal: If you set up the conditional formatting properly, you should be able to simply do the following: Select the cell with the conditional format Choose Copy Select the cells you want to paste the format to Choose edit- paste special- formats...

Excel: Color coding of cells per a list of values, conditional formatting, formating

conditional formatting, formating, color coding: I m not sure I understand the question, but it sounds like you are wanting the conditional formatting that is available on the toolbar, try it out. Go to toolbar/format/conditional formating --- In the window that pops up put in your conditions, and select...

Excel: Color fill in cell, what formula?, douglas m smith, blue cells

douglas m smith, blue cells, conditional formatting: Hi Avneesh, Unfortunately, there s no formula read or set cells colors directly so formulas and conditional formatting will not be able to do this. A macro will be needed to perform this operation. First, the exact color numbers will need to be determined....

Excel: Color fill gone..., different colors, segarra

different colors, segarra, spreadsheet: Hello, David, It s a little hard to diagnose without seeing the sheet, but my first guess would be that the spreadsheet somehow got protected. Check Tools/Protection, and see if one of the commands is Unprotect ---. If it is, then click on it. That...

Excel: Color formulas, conditional formatting, ems agency

conditional formatting, ems agency, doe john: If the names are in column A and the dates are in column B, and soon is defined as within 60 days, then: Select all the names; Use Format/Conditional Formatting; change Cell Value Is to Formula Is ; Enter this (assuming the active cell is A1): =$B1-60...

Excel: Color Macro, colorindex, invisible objects

colorindex, invisible objects, change colour: Use this code (on the Worksheet object in the Visual Basic Editor): Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim rngWithNumbers As Range Dim iFontColor As Integer Set rngWithNumbers = Me.Range( a1:j10...

Excel: Colors in Excel, conditional formatting, different colors

conditional formatting, different colors, checkboxes: Susan: Make SURE you don t have some kind of conditional formatting on those cells, first, okay? Click on a cell that s doing this. Hit Format-- Conditional Formatting, and hit DELETE, then choose all three checkboxes and hit OK. NOW...can you make the characters...

Excel: Colors -- help!, color palette, color tab

color palette, color tab, dialog box: Brianna, I can t duplicate that behavior. I went to tools= Options, color tab and redefined Yellow to be a shade of gray. I then closed the workbook. When I reopened it, it still was defined with he gray color. Recall I said the pallete is stored...

Excel: Colors, colorindex, color palette

colorindex, color palette, custom colors: I was trying to point out if colorindex 5 is yellow, and I color a cell background yellow, excel stores that as colorindex 5. If I go into the pallete and redefine yellow to be green, so colorindex 5 is defined to be green, then the cell will not be green...

Excel: Colour change of cell, conditional formatting, colour change

conditional formatting, colour change: Vishnu It is easier to change the color of the cell using condtonal formatting. Go to toolbar/format/conditional formatting. In the drop down box yu can pick the criteria you want to use for changing the format, and then clic on formatting and pick the...

Excel: Colum edit, help excel, excel 2000

help excel, excel 2000, cells: Jean, I guess I m not sure I know the answer to this one. I m trying to figure out what would be causing this to occur in the first place. You make it sound as if it only is a problem with specific cells, so I m curious if there is something about those cells...

Excel: Columes, scatter plot graph, making a scatter plot

scatter plot graph, making a scatter plot, slope formula: select A2:B12, make your xy scatter. Then select C2:D12 and drag THE BORDER into the chart. For the slope formula I believe you re going to have to rearrange the data, or put in A13: =C2, then fill down (and in B13: =D2 & fill down), then use A2:A24 as the...

Excel: Column Category Change, format cells, vlookup formula

format cells, vlookup formula, column type: Ernesto The Vlookup formula will work with either text or numbers, and should be working with your formula. I don t have your formula so I can t look at it, but I m guessing that the formula might not be quite correct in the way it is written. Sometimes...

Excel: Column A not Column B, example spreadsheet, vlookup formula

example spreadsheet, vlookup formula, nate: I have uploaded an example spreadsheet here: http://home.swbell.net/nate-sus/excel/2lists.xls I use a VLOOKUP formula to see if each item in column A is found in column B. If the value in A is not found in B(the result is #N/A), then it displays the corresponding...

Excel: Column Expanion beyond 256, spreadsheet control, expanion

spreadsheet control, expanion, expantion: Ron, No, there is nothing really you can do to expand that limit. The next version of Excel (version 12) is said to include expanded columns. According to David Gainer s Blog (MS developer), the new column limits will be... Current = 256 (2^8) XL12...

Excel: Column format, format cells, 6 digits

format cells, 6 digits, copy mode: Hello, Josh, This is a two-step process. First you have to divide all the numbers by 100. Then you need to format them to display two decimal places. Let s say that your numbers are in A1:A500. Here s an easy way to divide them all by 100. 1) Click...

Excel: Column Headings printing, column headings, sheet tab

column headings, sheet tab, underscore: Select the entire row containing the column heads, then use Insert/Name/Define and give it the name Print_Titles (no quotes) - the underscore is part of the name. OR File/Page Setup/Sheet Tab, in Rows to reoeat at top , enter $1:$1, for example or...

Excel: Column letter and row numbers, vertical scrollbar, horizontal scrollbar

vertical scrollbar, horizontal scrollbar, slider bar: Sub SetViewSettings() Turn them off With ActiveWindow letters and numbers .DisplayHeadings = False Horizontal Scrollbar .DisplayHorizontalScrollBar = False Vertical Scrollbar .DisplayVerticalScrollBar...

Excel: Column Rearrange, m1 11, oct nov dec

m1 11, oct nov dec, jan feb mar: Hello Robert, Your explanation leaves 10, 11, 12 in L, M, N, but instead of Oct, Nov, Dec, they become Jan, Feb, Mar. I assume this is a typo. Anyway, this worked as I expected. I hope it is what you expect: Sub GoFiscal() Range( C1:E6 ).Cut Range(...

Excel: Column in one sheet to rows in another, false selection, keyboard shortcut

false selection, keyboard shortcut, blank row: Looking at the code, I would suggest a different approach. Substitute the line Range( A5 ).Select For Range( A1 ).End(xlDown).Offset(1, 0).Select This will select the first empty cell on the column A, provided that there is data on the first two cells...

Excel: Column turned into many rows, giant column, vp marketing

giant column, vp marketing, broadway new york: without knowing that the last 2 lines of each chunk is address and city/state/zip, and that the first line of each chunk is name (is that always true?) then this is not possible, because there s no way to tell Excel that IniTech is not an address, etc....

Excel: Column Widths, auto size, advanced excel

auto size, advanced excel, column widths: Something like this placed in the WorkSheet_Calculation event, it will run each time a calculation is run. Instructions On How to Add Code to a Sheet Object Open the VBE (Alt+F11) and then double click the Sheet in the Project Explorer the code applies...

Excel: Column Widths, left mouse button, alignment tab

left mouse button, alignment tab, camera button: Hi Peter, Yes, you can merge cells to have that effect. If you select the cell furthest to the left and in row 1 and then holding the left-mouse button drag until you have gone far enough to the right selecting as many cells as you need to get the width...

Excel: Columns of information, cell b1, cell a1

cell b1, cell a1, excel sheet: Michael Now knowing what our data looks like, I.E. what column and row is occupied by the data its a bit difficult to tell you haow to approach it. having said that I m gonna suggest a method that might work for you, and that is--- to use linking formulas...

Excel: Columns - letters instead of #s, column headings, hey steve

column headings, hey steve, labels: Hey Steve, Follow these steps to change the column headings from numbers to letters: 1. Click on Tools on the top menu 2. Select Options 3. Click on the General tab 4. Uncheck the following box: R1C1 reference style 5. Click OK I hope that this...

Excel: Comair 2 columns for one count., excel sheet, comair

excel sheet, comair, click tools: Click Tools, Add-Ins and see if the Conditional Sum Wizard is checked. If not, check it. Then click Conditional Sum and follow the prompts. This will start a wizard that will build the formula you will need - to sum data based on two different criteria...

Excel: Combin feature, insult to injury, feature question

insult to injury, feature question, five elements: Ok, I haven t been able to come with a full code that will accept any amount of numbers, but this code has a pattern that can be easily extended. I don t know how it will perform with a lot of numbers, I have done the code for 6 iterations. Option Base...

Excel: Combination Formula in Excel, combination formula, aae

combination formula, aae, zeros: In range A1:C1 enter zeros. In A2, enter: =IF(SUM(B2:C2)=0,MOD(A1+1,4),A1) In B2, enter: =IF(C2=0,MOD(B1+1,4),B1) In C2, enter: =MOD(C1+1,4) Copy range A2:C2 down through A3:C64. In D1, enter: =CHOOSE(A1+1, A , E , I , O )&CHOOSE(B1+1,...

Excel: Combination, rng, ubound

rng, ubound, blank sheet: Excel will tell you how many combinations there are for 1 to 45 taken 6 at a time or 7 at a time 8,145,060 6 at a time 45,379,620 7 at a time you can get this with the combin function. Beyond that I see no reason to actually generate the combinations....

Excel: Combinations, debits and credits, cells

debits and credits, cells, a3: Hi Shamu, Is there a way to know that two entries in Column A correspond to a entry in column B? Possibiliies- (1) They are always below each other. For example- Row N - $125 -- $275 and Row N+1 $150 -- (Blank) (2) There is a third column which has identical...

Excel: Combinations - 25 selections, practical solution, acceptable solution

practical solution, acceptable solution, allexperts: Hi Alan, There are several possible causes I can think of, but there may be others as well. I would certainly expect there to be a problem with simply running out of rows on the sheet, and I believe this should happen above 16 (15 if the list doesn t start...

Excel: Combinations, trying to find a way, variance

trying to find a way, variance, free time: Yes, I think the approach needed would have to be a macro. But sorry, I don t write macros for people on this site who need one, want one, and/or seem to think I am expected to do so. 99% of all macros are more involved than what you think and rarely am...

Excel: Combinations, debits and credits, alternative solution

debits and credits, alternative solution, proble: Oh, now I understand. Sorry, I don t just write macros for people on this site who need one, want one, and/or seem to think I am expected to do so. 99% of all macros are more involved than what you think and rarely am I provided with enough specific and...

Excel: Combine multiple lists, column headings, sheet tab

column headings, sheet tab, microsoft excel: Yeah...I understood what you wanted. Sorry. I kind of ignored it because your way is NOT how I d do it. So, okay, you still need to get all your sheets into one workbook. Then you can do vlookups to the other sheets. For instance, on your summary: For...

Excel: Combine multiple row infor to one row, tom hope, retirement plan

tom hope, retirement plan, medical life: Are you looking for a macro? Assume Connie is in A2 Sub combineData() Dim lastrow as long lastrow = cells(rows.count,1).End(xlup) for i = lastrow to 2 step - 1 if lcase(Trim(cells(i,1))) = _ lcase(trim(cells(i-1,1))) then for i = 1 to 3...

Excel: Combine workbooks, empty workbook, macro code

empty workbook, macro code, ogilvy: This assumes both workbooks are open. It asks you to select a cell in each workbook, then copies all the worksheets in each to a new workbook. Sub CopySheetsFrom2() Dim r1 As Range Dim r2 As Range Dim bk As Workbook On Error Resume Next Set r1 =...

Excel: Combining 2 or more cells into 1, a2, a3

a2, a3, b2: Hi Roland! How are you? The easiest way, not the most efficient, is: 1st Type in B2 =A2& &A3 2nd Delete A3 3rd Move B2 to A2 You can record a macro for the 2nd and 3rd steps. You can also do a VBA program to do all the 3 steps automatically....

Excel: Combining analytical data, excel spreadsheets, chemical compounds

excel spreadsheets, chemical compounds, cross tab: Hi Kris I have emailed you a spreadsheet to look at. regards Des Hi Kris Sounds doable to me. COuld you eamil the two spreadsheets please and I will see what I can do for you. Limbic_lesion@hotmail.com Regards Des HI again ...

Excel: Combining Cells in Excel, vba procedure, reply back

vba procedure, reply back, column c: Jessica, There probably isn t a simple formula that will do what you need, but it can be done with a fairly simple VBA procedure. To insert the procedure press Alt+F11 to open the visual basic editor. Then click Module in the Insert menu. Paste the code...

Excel: Combining Columns, column spreadsheet, douglas m smith

column spreadsheet, douglas m smith, column data: Hi David, I ve set up a solution for you that uses a crossreference table: http://www.abundant-solutions.com/ColumnReorder2.xls Save the file to your hard drive, close it and then reopen it from your hard drive. You will need to fill down the information...

Excel: Combining Data in multiple rows to one row in another sheet- with a twist, random orders, pivot table

random orders, pivot table, test score: VJ, I think the easiest way it to do it with a pivot table. If you don t know how to do that, then you can send me the data workbook and I can set it up for you. If you want to try to figure how to set up the table yourself, you can also reference...

Excel: Combining Data, cell phone number, work sheets

cell phone number, work sheets, number address: With this layout, we can still use the same approach, but now a single formula will not be enough. I will suppose that you want to have the data on the master sheet one set after another, so for example the sheet2 values will go in the range C3:H10, the sheet3...

Excel: Combining data from multiple workbooks into one worksheet, data entry operators, instructional message

data entry operators, instructional message, open dialog: Sub ImportDistricts() Dim x As Long, z As Variant Dim bk as Workbook, sh as Worksheet Dim sh1 as Worksheet Change the next line to reflect the proper name and workbook where the data will be consolidated set sh = Workbooks( Master.xls...

Excel: Combining Formulae, countif, formulae

countif, formulae, combinations: Karen, You can t use AND directly in COUNTIF, but you can simulate it. =COUNTIF (A:A, =0 ) counts all cells in column A that are greater than 0. Of course, this also counts those greater than 6. But ... =COUNTIF(A:A, 6 ) counts those greater...

Excel: Combining functions, empty column, column type

empty column, column type, column c: Matthew, There is more than one way to do this. I ll show you two ways and let you decide which is best for you. 1. In column C (or some empty column), type this forumla in C3: =IF(AND(B3= Princeton ,C3= Not Sold ),1,0). Copy this formula down to...

Excel: Combining MIN and VLOOKUP in a statement..., 6 cells, vlookup

6 cells, vlookup, k50: You can combine the MATCH and INDIRECT to create the range for the MIN function. Having the lookup value in E2, and the range in K2:K50, you can use a formula like: =MIN(INDIRECT( K &MATCH(E2,K2:K50)+1& :K &MATCH(E2,K2:K50)+7)) You can even return the...

Excel: Combining MIN and VLOOKUP in a statement..., 6 cells, vlookup

6 cells, vlookup, e10: I would not use vlookup for this as it doesn t return a location, only the value - instead try =MIN(INDIRECT( E &MATCH(I10,E10:E28,0)+9 & :E &MATCH(I10,E10:E28,0)+15)) In this example, my data is in column E, cells 10 to 28, my lookup value is in I10...

Excel: Combining MIN and VLOOKUP in a statement..., vlookup formula, 6 cells

vlookup formula, 6 cells, ogilvy: Hello Peter Assume the number to Lookup is B1 Let s say your vlookup would have been =Vlookup(B1,K:M,3,False) to do what you describe =Min(Offset(K1,Match(B1,K:K,0)-1,3-1,6,1)) Offset is zero based. So if the match is on row 10, Match would...

Excel: Combining multiple filter results, filter command, results tables

filter command, results tables, filter results: set rng = worksheets( Report ).Cells(rows.count,1).End(xlup).Offset(1,0) sheets( worksheet ).range( A6:P3000 ).advancedfilter action:=xfiltercopy, _ criteriarange:=range( S6:Z7 ), _ copytorange:=rng, unique:=false Use the first line of code each time...

Excel: Combining SEARCH with an array, value flag, search lookup

value flag, search lookup, search function: John I have been looking and cannot find a way to do all that you want with your search/lookup/find criteria. Like you, I am guessing that it can be done, but I can t find the way. I suggest you reask the qquestion of another allexperts person and I m...

Excel: Combining Spreadsheets, contract sheet, vlookup formula

contract sheet, vlookup formula, empty column: There are many ways to do this, but the simplest one for you would be to: 1. move the equipment serial number column to the left (column A) in the contracts worksheet. 2. Now you can go in your equipment list worksheet. In an empty column enter a VLOOKUP...

Excel: Combining two sets of numbers, douglas m smith, cell c1

douglas m smith, cell c1, format cells: Hi again Greg, 1) To display a number like this, you can format it as a zip code (Format / Cells / Number Tab / click Special / click zip code). 2) To pick out the individual digits, with leading zeroes: B1: =MID(RIGHT( 00000 &A1,5),1,1) B2: =MID(RIGHT(...

Excel: Combining TextToDisplay with Hyperlinks, cell c1, cell b1

cell c1, cell b1, cell a1: You will have to write a macro that loops thru each of the rows and have the code inside the loop do basically what you are doing manually. And sorry, typically I do not write macros for people on this site who seem to think I am expected to do so, who...

Excel: Combining Values, four digits, arbitrary number

four digits, arbitrary number, a3: Hi James, Here you go. =IF(AND(MONTH(A3) 10,DAY(A3) 10),A1&A2& 0 &MONTH(A3)& 0 &DAY(A3)&YEAR(A3),IF(AND(MONTH(A3) =10,DAY(A3) 10),A1&A2&MONTH(A3)& 0 &DAY(A3)&YEAR(A3),IF(AND(MONTH(A3) 10,DAY(A3) =10),A1&A2& 0 &MONTH(A3)&DAY(A3)&YEAR(A3),A1&A2&MONTH(A3)&DAY(A3)&YEAR(A3))))...

Excel: Combining Vlookup, Offset & Match in one formula, richard stephens, reference point

richard stephens, reference point, e5: Grant, If I understand what you want, I would approach it a little different. If sounds like you want to list the name in row1 associated with rows who have an x in column A - the name would be from the column that has an OC in it. If that sounds right,...

Excel: Combining Worksheets into a Master Worksheet, r8, copy paste

r8, copy paste, c3: For this type of transformations, you may check the following web page: http://www.rondebruin.nl/tips.htm There is a section on Copy/Paste/Merge, that comes with examples, you may be able to adapt them to your particular situation. Hope this helps, Migue...

Excel: Combining workbooks in Excel 2004 for Mac, file open dialog box, generic example

file open dialog box, generic example, mac equivalent: Sorry, I m going to be of no use to you on this one - on the PC, it would be easy as you can use the Application.FileSearch to search a directory and handle each file found - so on a PC, I would program the display of the file open dialog box, use the...

Excel: Combo Box, exit design, combo box

exit design, combo box, control tool: In Design Mode (the icon for this appears on the Control Tool Box toolabr) RIGHT click the combo box and then left click Properties. Type a range, like A1:A10, in the ListFillRange property. A1:A10 is where you will type the data that will populate the combo...

Excel: Combo Box, combo box, input range

combo box, input range, combobox: If the cell link for Combobox1 is F1, for example, then you need a defined name, say List, which is equal to something like: =CHOOSE(Sheet1!$F$1,Sheet1!$I$1:$I$4,Sheet1!$J$1:$J$4,Sheet1!$K$1:$K$4) which means if you choose the 1st item in the first combobox,...

Excel: Combo Box in Excel 2000, using apostrophes, combo boxes

using apostrophes, combo boxes, shape control: Hi Cathy, There are two different kinds of Combo Boxes in Excel. They are very similiar in appearance and functionality, but setting the lists is a bit different for each. In addition, if you want an in-cell dropdown list to select a value to be entered...

Excel: Combo Box in excel, object dialog, control tab

object dialog, control tab, combo box: That combobox was made from the control toolbox, not the forms tool. It s an activeX control. If it must remain an activeX control, then right-click & go to Properties, not Format Control. Put A1:A10 in the line called ListFillRange. I recommend, tho, you...

Excel: Combo Box Formating Text, formating text, font option

formating text, font option, combo box: Pat For a combo box in the Forms toolbar you can t change the text as this is a standard in Excel, if you wish to though you can use a combo box from the Control Toolbar, this will enable you to change the font style by going to the design button on the...

Excel: Combo Box & Hyperlinks, lengthy paragraphs, combo box

lengthy paragraphs, combo box, hyperlinks: If you are a complete novice then I don t have the time to type everything I need to type to tell you how to achieve your request. I would need to type three or four lengthy paragraphs to explain EVERYTHING that you would need to know to do this. Sorr...

Excel: Combo Box , List Box Excel 2003, combo boxes, cell b1

combo boxes, cell b1, sheet1: Hi Pete How are you? I still do not entirely understand your question. However, I hope the following helps you. 0. Write your names list in B19:B30 of Sheet 1 and number the names in A19:A30 1. Draw a ComboBox in Sheet1 by selecting View / Toolbars/Forms...

Excel: Combo Box, sourcepath, workbook files

sourcepath, workbook files, toolbar menu: Hi Cathy, How are you? Please see SourcePath = TextBox1.Text Either you type the last slash eg. C:expe or you write NewPath = SourcePath + + Name + .xls Private Sub ComboBox1_Change() Dim Name As String, SourcePath As String...

Excel: Combo Box Printing, box printing, combo box

box printing, combo box, index number: The only way I know is by tying the combobox to a cell and printing the cell. If it is a combo from the forms toolbar you need two cells: one tied to cell 1 (the combo returns an index number), the second to extract the selected entry from the list of entries...

Excel: Combo Box - save and close, goldbook, sourcepath

goldbook, sourcepath, bob smith: so you mean you have multiple comboboxes? Well, one workbook had no knowledge of code or variables in another workbook. The best you could do is close any workbook not named Attendance Calendar.xls (adjust to match the real name) Private Sub ComboBox1_Change()...

Excel: Combo Box selections, control toolbox, combobox

control toolbox, combobox, combo box: in the properties window for listfillrange, put in something like Sheet1!A1:B20 go to the columncount property and put in 2. Assumes a combobox from the Control Toolbox Toolbar. If from the forms toolbar, I don t believe it supports this. ...

Excel: Combo Box, userform initialize, range names

userform initialize, range names, addess: Cathy, didn t I answer this question already??? Here s my earlier answer: there are a lot of ways to make this work. I will help you get the ListBox built and go from there. Note that you need to have the sheet with the customer data selected when you...

Excel: Combo Boxes, data validation, combo boxes

data validation, combo boxes, vba: If it could be the SAME column (i.e the cell they select) then data validation would do it - data menu, then validation, then type or link to the list of values. Otherwise it would need EITHER some VBA or by use of the forms toolbar combobox control you could...

Excel: Combo Boxes, example spreadsheet, combo boxes

example spreadsheet, combo boxes, purchase order form: Hi Perry! I uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/ifthencombo.xls The data isn t very organized in it, but I left all of the tables out in the open so that it would be easier to follow the example (hopefully)....

Excel: More on Combo Boxes, combo boxes, contiguous block

combo boxes, contiguous block, column count: Yes it is possible, but I don t think it s what you want - set column count to 2, and listfill range to a 2 column reference - HOWEVER, you will only be able to select one contiguous block - so POSSIBLY three linked combo boxes would work (where the second...

Excel: Combo box / Validation box, cell b1, cell b2

cell b1, cell b2, data validation: Hi David, Actually you answered it yourself. You need Data Validation. The following will input a dropdown box directly into Cell A1. Try this. Open a new workbook save workbook name as Test Project . On Sheet1 select Cell B1 and enter the words ...

Excel: Combo boxes, combo boxes, data validation

combo boxes, data validation, tibbetts: I m afraid I cannot think of a way of doing this WITHOUT VBA - at least, not if you want the value selected to be stored in the cell,as this would be the combined values of the two boxes - if you could have ONE set of data,then data validation would seem to...

Excel: Combo's and formula, application index, blank cell

application index, blank cell, excel spreadsheet: When you select January in a combobox, the linked cell will show 1, not January. You would need to assign a macro to the combobox, like this: Sub MyComboBoxChange() themonth = Application.Index(Application.GetCustomListContents(4), 1, [i1]) Workbooks.Open...

Excel: Combo, apostrophe, comb

apostrophe, comb, abcd: Run the first sub which will run the 2nd: Sub Comb8() Generate combinations of 8 items taken 1 thru 8 at a time For i = 1 To 4 Single apostrophe surrounded by quotes Comb 4, i, 1, Next End Sub Generate combinations...

Excel: ComboBox Control, excel spreadsheet, ctl

excel spreadsheet, ctl, excel 2002: Jesse, I don t know that I can help with this. I know the reason it won t work is because you have embedded comboboxes from the MSForms system, instead of VBA comboboxes. However, since I hate embedded tools, I don t know how to reference them all at the...

Excel: ComboBox problem, array formulas, combo boxes

array formulas, combo boxes, duplicat: You certainly can. Have a look at: http://www.cpearson.com/excel/duplicat.htm under Extracting Values On One List And Not Another also needed: http://www.cpearson.com/excel/noblanks.htm These two together can create the combo lists without macros...

Excel: ComboBox problem, cell b1, data validation

cell b1, data validation, source numbers: Example: In range A1:A4, enter the numbers 1,2,3,4. Select cell B1, and from main menu choose Data, Validation, Settings, Allow:, List, Source:, =numbers, OK. Now B1 is a drop-down to select a number 1 through 4. In Cell C1, do Data Validation just like...