proper solution, combobox, invoicing: Hi Keith How are you? I know it is not yet the proper solution, but can you run the following VBa code? 1. Draw a combobox in sheet 1 2. Run the VBA Editor 3. Insert a module 4. Paste into this module Sub Macro1() Dim R As Range Set R = Sheets(1).Range(...

Excel: Excel file password forgotten recovery, navin, secure place

navin, secure place, recovery software: Hi Navin, I have had success in the past with commercial password resetting software. Of course, this is something you will have to purchase, but most such products provide a guarantee. Here is a link to one of the more popular products that retails for...

Excel: Excel formula query, spreadsheet work, excel formula

spreadsheet work, excel formula, entering time: I hope I have not completely misinterpreted your question. If you are asking how to write a formula in Excel I would suggest you to pick up a book teaching you the basics if Excel. Formula s are such a basic principle of Spreadsheet work that you cannot...

Excel: Excel function, bonus structure, excel function

bonus structure, excel function, tier 1: Neva, given you clarification on the rules, then the only assumption is that the number of items sold is in F5. Then this formula gave me the expected results/bonus =IF(F5 =26,(F5-25)*200+2125,IF(F5 =21,(F5-20)*150+1375,IF(F5 =16,(F5-15)*125+750,IF(F5...

Excel: Excel Macro to Create Financial Statements, cash flow statements, financial reporting standards

cash flow statements, financial reporting standards, balance sheet income statement: Hi Dan, The information you provided is still not sufficient. I need input data and format, output data expected and the format in an excel file to even start working. I m a finance professional from India, so you ll agree that it s not reasonable to expect...

Excel: Excel problem, countif, relevant message

countif, relevant message, time table: Hi Petros How are you? I think the easiest way to solve this is - Check each condition for each row eg. type in Aw1 =COuntif(A1:AV1, x ) =10 Type in Ax1 .... Type in Ay1 =COuNTif(A1:AV1; ) =14 Type in AZ1 - Then add an extra...

Excel: Excel Spreadsheet, conditional formatting, countif

conditional formatting, countif, sum formula: Sorry for the delay in replying - I ve been away from my Office 2007 machine for a bit and needed to refer specifically to that for you - I m assuming you ARE on office 2007 or later as otherwise you will be restricted to 4 levels of colour (3 conditions and...

Excel: Excel spreadsheet assignment, spreadsheet assignment, loan scenarios

spreadsheet assignment, loan scenarios, data labels: Hi Mansour, I had done something similar back in my school but there are many ways to put it in excel. (I have no idea about how different Excel on is from the windows version) If you can send me some sample of how your final data, I will prepare it...

Excel: Excel-VBA, Excel Sixthsense Excel Creating Pivot Table in Excel

Excel Sixthsense Excel Creating Pivot Table in Excel: Hi Nagaraj, I am not interested to build it in excel formula so given alternate method which suits for you. Download the file from the below link a href= https://docs.google.com/file/d/0B2TMxNyEPQ2rbm16N0Y5cVI0MGM/edit?pli=1 https://docs.google.com/file/d/0B2TMxNyEPQ2rbm16N0Y5cVI0MGM/edit?pli=1...

Excel: Excel/VBA - importing several TXT file at once, excel vba, folder files

excel vba, folder files, folder c: In my sample code, please look at the lines Set Folder = FSO.GetFolder( C: emp ) and Set copyFrom = Workbooks.Open( C: emp & file.Name) As you can see, in this sample code, I assumed that we want to get .txt files from the folder c: emp....

Excel: Excel VBA Macro, vba macro, hand d1

vba macro, hand d1, excel vba: Hi Steve, I ve managed to put together a quick macro for what you need. I m assuming that you wanted to have C2 = B2/10 and D2=C2/10 in your original question instead of having the denominator as 8. If I m right, then you can use the below macro and...

Excel: EXCEL VLOOKUP AND INDEX, excel vlookup, coloumn

excel vlookup, coloumn, spread sheet: You could use a formula like: =INDEX(C:C,MATCH( Apples098 ,$A$1:$A$8&$D$1:$D$8,0)) which MUST be entered via ctrl/shift/enter, not just enter. If Apples & 098 are in cells K1 and L1, you could use =INDEX(C:C,MATCH(K1&L1,$A$1:$A$8&$D$1:$D$8,0)) but the...

Excel: EXCEL VLOOKUP AND INDEX, excel vlookup, quick reply

excel vlookup, quick reply, spread sheet: Shah, I gave you an answer that I feel will do what you want. If you are not willing to use it, then I don t know what you expect me to say. The formula you show is a dead end. It will not do what you want unless you want to concatenate information from...

Excel: Excell Windows arrange all, excel application, ms window

excel application, ms window, excel workbooks: Arrange All means that when you have 2 or more Excel workbooks open in the same Excel application (i.e., in the same MS Window ), you can view them side-by-side etc. Open the Excel Application. From within Excel, open an Excel workbook. Then open another...

Excel: extending formulas, checkbook balance, credit card expenses

checkbook balance, credit card expenses, table wizard: The Table function certainly works and is a powerful, useful tool. Unless I didn t understand your question, the expansion row at the bottom of the table will always inherit the properties of the row directly above it. I d be happy to look at your document...

Excel: extracting names from a list, result column, a3000

result column, a3000, b1000: Christopher, that is because when I originally wrote it, I put in all five ranges. But I was going to say you needed a list of names to work against. Then I came to the conclusion that you could work against one of the lists and didn t need to check that...

Excel: extracting unique names, cell b1, christopher mitchell

cell b1, christopher mitchell, unique names: Christopher Mitchell, A solution was provided via email. I put the solution in column E but it can be moved to column B if you prefer. If you only want to mark the names that have multiple entries and every entry, then in B1 you can put =if(countif($A:$A,A1)...

Excel: How to erase spaces in a whole column, cell b1, two spaces

Excel: How to erase spaces in a whole column, cell b1, two spaces, second time

Excel: Fill Missing Values Help, resid, c8

resid, c8, c7: Hello Lubna, Thanks for the detailed question, but I m having a bit of confusion when I try to match this question with the excel file you have shared. For example, I m unable to find individuals AA, B etc in the file. The numbers you have indicated in...

Excel: formula..., test numbers, exit function

test numbers, exit function, check application: I answered this. Not sure why it s still here. You need a user-defined VBA function. If cell F1 contains the pattern and A1:A100 contains the range of test numbers, you can enter this in a cell: =getlike($A$1:$A$100,$F$1,ROW(A1)) and fill down. Here s...

Excel: formula vba, christopher mitchell, w20

christopher mitchell, w20, thanks for your patience: Chris Mitchell this will do 0, , Sub replacezero() Dim cell As Range For Each cell In Worksheets( Sheet2 ).Range( C1:C20 ) If Trim(cell.Text) = 0 Or len(trim(cell.text)) = 0 Then cell.Value = _ End If Next End Sub or Sub...

Excel: FORMULAS, cell a2, worksheet functions

cell a2, worksheet functions, exit function: Hi Tanya, If I understand your question correctly, there is no simple combination of existing worksheet functions that will do this. For this reason I wrote a simple user-defined function (UDF) to do it: Function FindX(R As Range) As Integer returns...

Excel: Function, Excel Sixthsense Excel Excel Function to Find How many cells in a column had a value

Excel Sixthsense Excel Excel Function to Find How many cells in a column had a value: Hi Marty, Thanks for sending your question on my way :) For getting the Count of Numeric Data in Column-A code =COUNT(A:A) /code For getting the Count of TEXT Data in Column-A code =COUNTA(A:A)-COUNT(A:A) /code For getting the Count of BOTH...

Excel: Graphing integers onto an axis of whole numbers, time graph, whole numbers

time graph, whole numbers, time values: I don t follow. 1:00 (as a time value) is not a whole number, it s 1/24. If the points you want to plot are time values as well, there should be no problem - Feel free to send a sample wb to me at bobumlas@hotmail.com and I can see what issue you re facing...

Excel: Inter Sheet formula help, sheet1, ab2

sheet1, ab2, gulshan: Hi Arjun, Based on your question, I m assuming that you have a very very limited experience on excel. Can you mail me the excel file you have with all the input and output fields and I ll insert the formula and send it back. I think instead of explaining...

Excel: ISnumber combined with SEARCH, f2 g2, thanks in advance

f2 g2, thanks in advance, ogilvy: Jodie, =IF(ISNUMBER(SEARCH( A ,F2)),G2*100, ) is your basic construct. If you want to nest it then Put this in row 2 to refer to F2 and G2, then drag fill down. It worked for me if I understand the situation correctly. =IF(ISNUMBER(SEARCH(...

Excel: linking multiple worksheets, nasty job, link operations

nasty job, link operations, paste link: Ouch - that sounds like a nasty job. having said that, I cannot think of an obvious approach to do the job without a macro - but if a macro is OK it shouldn t be TOO hard as the formula could then be standardised - effectively all you are doing with paste...

Excel: looking up a row, column c, eric eric

column c, eric eric, ogilvy: Eric, Say the dates are in column C and you place a date to search for in cell A1. then in A2 =if(A1 ,match(A1,C:C,0), ) When you enter the date in A1, A2 will return the row number where the match is found. -- Regards, Tom Ogilvy ...

Excel: Macro to copy cells, commandbutton, sheet1

commandbutton, sheet1, command button: Hi Shaun, Below is some code that does what you describe, and I added an ElseIf as an example of how to add additional test conditions and ranges to be copied. Since you didn t specify which ranges are on which sheets, I made some assumptions: I assumed...

Excel: macro to encrypt email, mail document, mail database

mail document, mail database, new mail: You are unable to encrypt because you don t know how to write the code for that or the mail that is sent is not encrypted and thus the code does not encrypt? I don t know anything about Lotus Notes however. Try setting the values to 1 and not 1 , just as...

Excel: Macro Excel 2007, letter combination, isnumeric

letter combination, isnumeric, ogilvy: Jim G. Select the cells to process then run a macro like this (test it on a copy of your worksheet) Sub removeletter() Dim cell As Range, s As String For Each cell In Selection If Len(Trim(cell)) 1 Then s = Right(cell.Text, 1) If...

Excel: Macro for font name , size,color,bold,align, c24, c30

c24, c30, c12: Sub Answer() Range( C6:C12,C24:C30 ).FormulaR1C1 = Font name: Times New Roman With Range( C6:C12,C24:C30 ).Font .Name = Times New Roman .Size = 10 .Strikethrough = False .Superscript = False .Subscript...

Excel: Macro- Moving data from Word to Excel, excel macros, excel workbook

excel macros, excel workbook, word files: Hello Ken, I would approach this in 2 stages: 1. First create a macro to open one word document and move the data to an excel file as per your requirement. As per my understanding, you have this part figured out. 2. Once you have the macro for step 1,...

Excel: merge info on duplicate rows found in a specific column, conditional formatting, cell a1

conditional formatting, cell a1, procedure names: Assuming the first thing in your image is in cell A1: Sub Combine() Dim stg As String, n As Integer n = Range( B50000 ).End(xlUp).Row m = n + 2 For i = 1 To n + 1 If i = 1 Then stg = Cells(i, 1).Value Else...

Excel: Merging duplicate cells, column c, richard florida

column c, richard florida, xyz: Martin Not sure if its an easy way, but try this-- First separate the values using text to columns. you should end up with the 10 place number in one cell and the related second part of the number in the next column on the same row. for example IN12345678...

Excel: merging macros, srng, xx3

srng, xx3, vsh: Christopher Mitchell, I would assume the bigger workbook requires much more recalculation (has more formulas and therefor perhaps more dependencies). What I would do would be to make calculation manual before running the macro and then set it back to automatic...

Excel: mixing equations, colored cells, color cells

colored cells, color cells, database portion: Sorry, Sue, but I follow several specific rules in data control and analysis. One of them is color is not data . Color is a human-eye thing and I have never seen an instance where the colors could not be replaced by real data that can be analysed in a...

Excel: need help with formula, room nr, al14

room nr, al14, initials: Hi Khaled, I m still not clear on what is the requirement. Can you send me the excel file with the inputs data, what will you enter in the columns and what will be the expected result. you can mail the file to gulshanrajpurswani@gmail.com, mention the...

Excel: Need to include, xls, suffix

xls, suffix, macros: No problem. I need to know: 1. what the file name is WITHOUT the date part of the name 2. HOW the date that is part of the file name is to be formatted 3. if the file will be saved more than once in a day then how should the file be named 4. if the path...

Excel: numbers, cell a2, cell a1

cell a2, cell a1, blank space: Open a blank sheet on Excel, and enter the example you give above (i.e., enter 2 in cell A1, 3 in cell A2, 11 in cell A3, leave cell A4 blank, etc etc. in cell A11, enter the formula: =IF(A1= , ,A1) and then copy it down as far as you need. The...

Excel: Numbers, blank space, shortcut menu

blank space, shortcut menu, set of numbers: Hi Kathy, So is this the projected end result: 2 3 11 (blank space) 6 22 1 97 (blank space) (blank space) 2 3 11 (blank space) 6 22 1 97 (blank space) (blank space) 2 3 11 (blank space) 6 22 1 97 (blank space) (blank space)...

Excel: Overtime Hours, overtime bill, overtime hours

overtime bill, overtime hours, time and a half: I am a little confused. Is it the elapsed hours or the time in and time out that cause over time pay? If I work 6 am to 2pm, 7 hours total, do I get overtime pay of 1 hour from 6 am to 7 am? Typically, it is the elapsed time and not the time in and time...

Excel: Page no. in Roman, page footer, npgs

page footer, npgs, ipg: Hello Jack, I m assuming you want to accomplish this by placing the page numbering in the sheet footers. Unfortunately there is not a way to put a different footer on each page of a worksheet in Excel because Excel doesn t have a footer object for each...

Excel: Penultimate value, ogilvy, e2

ogilvy, e2, b2: Umesh Suppose I have a data in row A Assume you mean column A assume your values are in B2:F2 G2: =E2 would be the next to last value (penultimate) if the last value is in F2 (using the order of the columns which is the only rule I see that...

Excel: Pivot table help, pivot table, resid

pivot table, resid, variance: Sorry - I don t know what you re looking for. Your PT shows 4 time series, not 2, so which ones do you want to take the variance on? It d be easier to communicate via email - I m at bobumlas@hotmail.com Perhaps you could mock up an example of what you d...

Excel: Private Sub Change by Value, target address, range f1

target address, range f1, real time data: Yes, only one change event per sheet. something like: Private Sub Worksheet_Change(ByVal Target As Range) If Target.column 1 Then Exit Sub If Target.Row 3 then exit sub If Application.CountA(Target.offset(-2,5).Resize(1,2)) = 0 Then target.offset(0,1).resize(1,4)=target.value...

Excel: Pulling out a row based on a reference cell, allexperts, thanks in advance

allexperts, thanks in advance, macros: You really can t do with this formulas and thus will need a macro. I don t write macros for free and based on my status with AllExperts I am allowed to charge a nominal fee for writing macros for people asking questions on this site. So, if you still want...

Excel: random data selection

Sub APercentOfEach() Dim arr() Dim arrcount As Integer Set curr = ActiveSheet Set sel = Selection pct = Application.InputBox( What percent? , Type:=1) If pct 100 Or pct = 0 Then MsgBox Enter a value between 1 and 100...

Excel: Re-arranging the data, input table, example input

input table, example input, null string: Toughie to do by formulas, but I got it. You need a helper column which you can hide. I used column O. In O7 enter this formula via ctrl+shift+enter: =SMALL(IF($A$7:$A$48=1,ROW($7:$48),IF($C$7:$C$48= TS2 ,ROW($7:$48),IF($C$7:$C$48= TS3 ,ROW($7:$48), ))),ROW(A1))...

Excel: relating lost focus events, target address, question pool

target address, question pool, focus events: It looks to me like you would PROBABLY be fine with the change event of the worksheet - as an example Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Address End Sub would display a message box with the address of the changed cell....

Excel: Rename/Group Cells and add corresponding Expense amounts, category column, excel 7

category column, excel 7, professional pc: Hi Monja, Yes, the clarification really helps. First, here is a macro you can use to do the reclassifications from the descriptions in column A to the reclassified category in column C: __________________________________________________ Sub ReclassifyExpenses()...

Excel: Returning multiple values horizontally, postcode districts, postal districts

postcode districts, postal districts, postal district: I can think of a number of possible solutions - obviously the simplest is a filter, but that would work for one record at a time which I guess isn t what you wanted! The next option would be an array filter - an example of this is at http://aidanheritage.byethost3.com/excel/ArrayFilter.xls...

Excel: Running a macro in excel 2003 without using keystrokes, john lyons, macro in excel

john lyons, macro in excel, a29: Assuming you macro was someithing basic like this: pre Sub PrintSheet() ActiveSheet.Printout Copies:=1 End Sub /pre You can add a test to the beginning to check cell A29. pre Sub PrintSheet() If Range( A29 ) = 1 Then ActiveSheet.Printout...

Excel: Running a Macro in excell 2003 without using key stroke, john lyons, key stroke

john lyons, key stroke, excell 2003: In the Visual Basic Editor, in the Worksheet object, use the following code: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range procedure is triggered whenever user changes cell A1 Set rng = Range( a1 ) If Not (Intersect(Target,...

Excel: Sheet name change implication, question pool, answer sheet

question pool, answer sheet, taha: (question found in question pool) - as long as the sheets are all on the same workbook then there is no issue - excel will update the references to the old name with the new name, in the same way as if you delete or insert cells onto a workbook - hope this...

Excel: Simple Macro Problem, r2 value, up arrow

r2 value, up arrow, a500: dp If you choose column A and do f5 to get the goto dialog, then choose special, then in the resulting dialog, you have a choice of constants or formulas. Choose formulas, then uncheck every subtype except numbers. Then click OK. This should select all...

Excel: Single digit number split into two digits, format cells, dropbox

format cells, dropbox, digit number: For Cell G3, go to format cells, click on custom and in the format field enter 00. This will make the cell format as 00 and 6 will start appearing as 06. Now in cell H3, give the following formula - =LEFT((TEXT(G3, 00 )),1). This should give you result...

Excel: sliding scale for menu, sumif, litre

sumif, litre, b1: I m not 100% sure this would be possible based on the text method you describe, but IF we were only dealing with one item (i.e soup) I d advise a vlookup table which contained the lowest number of persons that would be served per litre in the first column...

Excel: IF statement, taking time, c1

taking time, c1: Yes. Assuming you also want to know HOW then here is one way s example of doing what you want: =IF(AND(A1 1,A1 10),VLOOKUP(C1,D1:E12,1,FALSE), ) If A1 is between 2 and 9 then pull data from the range of D1:E12 that matches what is in C1. If not then...

Excel: Statistical Modes, array formula, countif

array formula, countif, d mode: As this is a question I ve been asked a few times, I created an example sheet at http://aidanheritage.byethost3.com/excel/DynamicRange.xls which shows the method I use to make a range dynamic - hopefully that gives you what you need, but again if you need...

Excel: SUM numbers in a consequtive time period, sum numbers, sum value

sum numbers, sum value, pivot table: On a blank sheet, enter in range C1:N1 the labels JAN, FEB, MAR, ... etc In range C2:N3, enter the volumes that we ll use for this sample. Select C2:N3 and click Conditional Formatting, New Rule, Use a formula to determine which cells to format, Format...

Excel: SUMIF and SUBTOTAL combined??, sumif, excel user

sumif, excel user, hi bob: =SUMPRODUCT(SUBTOTAL(103,OFFSET(A2,ROW(A2:A1000)-ROW(A2),0)),N(P2:P1000= PTC ),N2:N1000) Don t use entire columns (P:P), but you can increase the 1000 if you need. This comes from my book Excel Outside the Box , Page 62. http://www.mrexcel.com/outsidethebox.html...

Excel: SUMIFS multiple criteria, excel sumifs

excel sumifs: Try this tutorial on a href= https://599cd.com/tips/excel/sumifs/?key=AllExperts SUMIFS /a . That should answer your question. Be sure to visit my web site and watch my B FREE /B 90-minute A HREF= http://www.599cd.com/Excel/AllExperts Microsoft...

Excel: Summary sheet, sure what your question, summary sheet

sure what your question, summary sheet: I am not sure what your question is since you did not ask one but without seeing the workbook it is impossible for me say why you are getting the error. #REF errors indicate there is an invalid cell and/or worksheet and/or workbook name error. That s about...

Excel: table format, line colors, spreadsheet

line colors, spreadsheet, formatting: Julie - Fortunately, this is simple: Select the entire table Right-click anywhere in the table Select Table Convert to Range, It asks Do You Want to Convert the Table to A Normal Range? , answer Yes. This removes the table, but retains formatting...

Excel: tiered compounding bonus structure, bonus structure, tier 1

bonus structure, tier 1, video tutorial: This sounds like a job for a href= http://599cd.com/tips/excel/vlookup/?key=AllExperts VLOOKUP /a . Be sure to visit my web site and watch my B FREE /B 90-minute A HREF= http://www.599cd.com/Excel/AllExperts Microsoft Excel Video Tutorial /A ...

Excel: Time Difference, happy new year, time difference

happy new year, time difference, ogilvy: Jayendran, Are you asking if 24 hours have elapsed between the two date/time combinations. then =((A2+B2)-(A1+B1))*24 will tell you how many hours have elapsed. Your example gives me 13.9 hrs If you just want to see if they are on different...

Excel: IF Today = Monday, Today - 3, weekday, formatting

weekday, formatting: In Excel dates are numbers, not text. =TODAY() returns a number like 41235, which Excel recognizes as possibly being a date and allows you to FORMAT that number to show with any date display you d like. Formatting the cell as DDDD would show Monday...

Excel: Undo a Calculated Item, manual vba, pivot table

manual vba, pivot table, sorry for the delay: I ve not had experience with this sort of table, but you could use the worksheet change event to track changes - I would suggest storing in a known range - the method of storing the old value would be something like the discussion here http://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba...

Excel: Undo a Calculated Item, manual vba, source cells

manual vba, source cells, target: 1 - I don t see that the cells are editable in what you referenced. In general, fields in pivottables are not editable, only the source cells are. 2 - VBA undo is incredibly difficult unless you somehow keep some trail of every step the user is doing. 3...

Excel: Unprotecting dynamic rows in a protected worksheet, b cells, word production

b cells, word production, range c: Put this event macro into the sheet module where you want this behavior. We ll assume you ve already unlocked the column B cells so they are editable while the sheet is locked. The macro watches for changes in column B. Anytime you edit the column B values,...

Excel: Unprotecting dynamic rows in a protected worksheet, Unprotect sheet through vba code and unlock certain cells

Unprotect sheet through vba code and unlock certain cells: Hi Prabhat, Change this line b c.EntireRow.Locked = False /b To u b The columns are specifically declared in the below code /b u code Range( C & c.Row & :H & c.Row).Locked = False /code u b The Right Side Last Column will be automatically...

Excel: Unprotecting dynamic rows in a protected worksheet, target cells, target range

target cells, target range, word production: You could use this code behind the worksheet. Right-click the worksheet s tab and choose View code . Then paste in this macro: Private Sub Worksheet_Change(ByVal Target As Range) Dim oCell As Range If Intersect(Target, Range( B:B )) Is Nothing...

Excel: Unprotecting dynamic rows in protected worksheet, word production, h15

word production, h15, c10: run this macro: Sub Prot() ActiveSheet.Unprotect For i = 2 To Range( B1048576 ).End(xlUp).Row If Cells(i, 2).Value = Production Plan (Units) - Revised Then Cells(i, 3).Resize(1, 6).Locked = True Else ...

Excel: Unprotecting dynamic rows in a protected worksheet, macro development, word production

macro development, word production, richard florida: Prabhat Sorry to be late-I didn t see your question--I believe you will need a macro to accomplish what you want. I am not an expert in macro development, so I suggest you reask the question of another allexperts person and I m sure you can get a good...

Excel: Unprotecting dynamic rows in a protected worksheet, target text, word production

target text, word production, range c: something like this should do it - Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect If Target.Column = 2 Then If Target.Text = Production Plan (Units) - Revised Then Range( C & Target.Row & :H & Target.Row).Locked...

Excel: Unprotecting dynamic rows in a protected worksheet, word production, column c

word production, column c, pivot table: Hi again Prabhat, Okay, here is some code that I believe does what you want: Private Sub Worksheet_Change(ByVal Target As Range) check whether changed cell includes column B Dim TRow As Long TRow = Target.Row If Union(Target, Cells(TRow,...

Excel: Use VBA to Add Same Worksheet Event to Multiple Sheets, worksheet selection, target

worksheet selection, target, vogelaar: Anthony, This is not really a good interface for a detailed discussion - however, Chip Pearson has documented all you need to know on this topic. I will provide you with his site/page on this topic to so you can build a strong foundation of what you need...

Excel: Using numbers as delimiters, cell c5, blah blah

cell c5, blah blah, blank city: G day Leor, Apologies for the late reply to your question. Assuming you text is in Cell C4 Please put this formula in Cell C5 if it has ABC Company 1234 Place Ave Blank City AB 12345 =LEFT(C4,FIND( ,C4,FIND( ,C4,1)+1)-1) Please put...

Excel: VBA to copy ComboBox to Range of cells on multiple sheets, rejections, j2

rejections, j2, shapes: Anthony, You are not really copying it to the cells. You are copying it to the object/shape layer above the selected cell. So even if multiple cells are selected, it pastes it using the location up the upper left cell in the selection. Thus the answer...

Excel: VBA to copy range based on cell value, macro in excel, word tables

macro in excel, word tables, sh1: Yogi, This worked for me if I understood the situation correctly. It works on the active sheet. It writes to a sheet name Master (which you can change in the code). Sub ABC() Dim sh As Worksheet, sh1 As Worksheet, rw1 As Long Dim r As Range, rr...

Excel: VBA to Update Workbooks, three sheets, maste

three sheets, maste, wbs: Why not use relative references so when you fill A53 down it becomes A54 automatically? I m still missing something. What does this even mean:?? =[Review.xlsm]RawData!$A$2 =[Review.xlsm]RawData!$B$2 =[Review.xlsm]RawData!$C$2 =[Quality.xlsm]RawData!$D$2 ...

Excel: Vlook up and If function, pivot tables in excel, spread sheet

pivot tables in excel, spread sheet, apples: Hello Shah, If I understand correctly, you want excel to show all 4 lines which contain Apples. Vlookup doesn t do that. Vlookup will only display one matching record. If you want to see all the records which have Apple, you can try to use Pivot Tables...

Excel: vlookup query, ymail, spreadsheet

ymail, spreadsheet, weights: Gordon - I have some ideas, just from your description, but yes in this case being able to see the spreadsheet would help a lot. Please send it to: andrea.lynch@ymail.com This is an account I ve set up just for this sort of thing. I ll try to get...

Excel: vlookup query for invoice, question pool, aidan

question pool, aidan, spreadsheet: I m happy to look at the spreadsheet - which I think is probably essential to determine how you want it to work! My email is aidan.heritage@virgin.net (question found in question pool indicating the person you originally asked was unable/unwilling to answer...

Excel: Interactive Holidays on excel, cell a2, january first

cell a2, january first, excel sheet: Katie As easy as the answer to your first question was, the response to your second is much more difficult. that is mainly because excel doessn t have a function that automatically identifies holidays for us. So what that means is that you must identify...

Excel: Workbook protection, suspiscion, aidan

suspiscion, aidan, email: This isn t the behaviour I would expect from a workbook, so my suspiscion is that there is a macro at work which is doing this - is it possible to see the workbook in question (my email is aidan.heritage@virgin.net) - without that it s a little difficult to...

Excel: If & workday formula, celli, l12

celli, l12, workday: Hi again Billy, I am unable to duplicate your problem. When I deselect G142 or select a different option in the G142 dropdown, the value returned by the WEEKDAY function does change, and if the values in cells F140 or F142 change the WEEKDAY results change...

Excel: zero in front of #, spreed sheet, format cells

spreed sheet, format cells, zeroes: Hi June, You can make sure the cell keeps the zero by added an apostrophe ( ) before typing the zero. For example, for 0402 type 0402. Also, if the numbers all have the same length, you can use a custom format: I am not sure of which version of...

Excel: Adding numbers if cells equal

Hello arrendajo, try this formula: =SumIF(D1:D5; Si ;C1:C5) If you can not fix it please upload the file from http://programarexcel.com/p/contacto.html I check the file and give it back with the solution. visit http://www.programarexcel.co...

Excel: Auto-Scrolling help

the ii should be iii I also changed the cell to tell it to stop to J2. Even though J2 will not be visible some of the time, since it is selected, you can just type a letter and hit enter. (Otherwise use Ctrl+Break to halt the macro) I tested this and...

Excel: Check boxes in Excel 2010

Ginny, I can t answer your why does this happen question because I wasn t the one doing the copying. If I was going to copy a sheet, I would right click on the sheet tab and choose Move or Copy , then in the dialog, click the check box for Make a...

Excel: Clearing Clipboad

As far as I know Application.DisplayAlerts = False would suppress the message - BUT I would question the need to copy and paste workbooks( LOG VIN1900 19C.xls ).Range( T19:Y36 ).value=sheets( Whatever ).range( Whatever ).value would be the kind of method...

Excel: Clipboard messages

Eric, Application.CutCopyMode = False so Workbooks( LOG VIN1900 19C.xls ).Activate Sheets( brownsheet ).Select Range( T19:Y36 ).Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Workbooks( DATE.XLS ).Close...

Excel: Compare 2 Columns With Each Row

Srikanth I don t know how your data is arranged, but the formula you described is comparing only one column one row with the same row in the next column, so if B2=C2 and B3=C3 then the result where the formula is will be C2B3 or if cell c2 contains X and...

Excel: Conditional format based on date

In simple terms the formulas are =TODAY()-365 =Sheet2!A1 =TODAY()-330 =Sheet2!A1 =TODAY()-300 =Sheet2!A1 where sheet2!A1 represents the date - but in your case, that probably needs extending to be a lookup formula to get the date per person. Formulas...

Excel: Copy active worksheet to new workbook

Al, Activesheet.copy copies the activesheet to a new workbook So when that command executes, the copy of the sheet is placed in a new single sheet workbook. That new workbook is not the activeworkbook and the copy of the sheet is now the activesheet....

Excel: Copy and Paste 1 row from 45,000 xml files

Achilles, Here is some code which I think will work. I used as much of your code (and coding style) as I could so it would not look to foreign to you. I integrated that with code that does a recursive search of a directory structure. I wasn t sure on...

Excel: Copy and rename a pivot table via VBA

Yes, EXISTING pivots on that sheet. You just added a pivot table by copying and pasting PivotTableTAT. So if there were 1 to start, you d now have 2, and Activesheet.Pivottables.count would be 2, so n is 2, and ActiveSheet.PivotTables(n).Name = PivotTableCount...

Excel: copying absolute and relative references

In cell C3, enter the formula =OFFSET($B$2,COLUMN(E2)-COLUMN($B2),0) / $B3 - 1 copy this same formula down, and to the right. It will give you results equal to the formulas you show in your question. You may extend the copying as many rows and columns...

Excel: Count Number of Times

If this is simply a count of the entries then the count function will do it. If you want to summarize the times (so a time may exist multiple times but you only want to count it once) i would suggest using a pivot table to do this (insert ribbon). If this...

Excel: Create new rows for every fixed time interval

Public n As Integer Sub Auto_Open() Application.OnTime Now + 1/1440, post one minute End Sub Sub post() Sheets(1).Range( A1:H1 ).Copy Sheets(2).Range( A1 ).Offset(n).PasteSpecial Sheets(2).Range( I1 ).offset(n).value = Now Sheets(2).Columns.Autofit...

Excel: data validation list font size

Christopher Mitchell, No, you can not alter the font size of data validation dropdowns. Debra Dalgleish has sample code that simulates doing what you describe by displaying an activeX combobox instead. You can read her article and see her code: ...

Excel: Date formatting

Stuart, If you take on off the cells and format it as general does it appear as 41365 (this is for you example: 01/04/2013 00:00:00 - Apr 1, 2013) If it does, then you can click undo to get it back to the original format; but that establishes that...

Excel: embed video clips into Excel 2010 or 2013 cells

Not really my expertise here, as this is not often asked. Playing around with it I came up with this macro. You need to embed the video into the worksheet cell (for each one to play(Insert/Object/Create from File), then right-click the object & assign it to...

Excel: embed video clips in Excel files

In older versions of excel you could do this, but I don t think it s as easy in the current versions - http://excel.tips.net/T006861_Inserting_Video_into_Worksheets.html talks about it, but it s not a click and play version. Regretably, this isn t something...

Excel: embed video clips in Excel cells

Hello Richard, Have you tried to use menu Insert - Object and select the video clip option from there? Alternatively, you can also store your videos in the same folder and create a hyperlink. Second option is better if you don t want to increase the...

Excel: erase lines

Hi Willy, You have 2 options - 1. Remove the borders in excel by selecting no borders or colour all borders white and then copy this data into word 2. Alternatively, after pasting into word, you can select the pasted table and then change the border...

Excel: excel 2010 macro question

Sam If you don t already have S2-S60 listed in sheet S1 your workbook then you can do this in sheet S1 assume A1: = S &row()+1 A2: = S &row()+1 . . . A59: = S &row()+1 My code assumes your sheet names start in A1 and your marks would be...

Excel: Excel 2013

If they re not in the same cells, you ll have to just copy/paste. There s no automatic way to do it unless you want to use formulas in the other sheets to pick up the info from the main sheet, but you d have to know from where it comes. You said you were...

Excel: excel

Aman A typo mistake on my part - not in this case. You never mentioned cell B7 in your original question. I assumed by what you stated that you wanted the formula in A7. B7: =indirect(Address(A7+1,1)) I am assuming your list of items starts in...

Excel: excel

Hi Appavoo, I have given quite a bit of thought to methods of accomplishing the extraction of multiple numbers from a text string using only built-in worksheet functions. I have not come up with a solution for even two embedded numbers. I believe it is...

Excel: Excel doubt

If the value is in A1, enter this formula by holding Ctrl and Shift simultaneously, then press enter: =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1& ,ROW(INDIRECT(( 1: &LEN(A1& )))),1)),0),MATCH(TRUE,ISERROR(1*MID(MID(A1,MATCH(FALSE,ISERROR(1*MID(A1& ,ROW(INDIRECT((...

Excel: Excel Flash Fill?

1 - it may NOT be Flash Fill -- is it in a Table? Tables will do that behavior. 2 - after it does what it thinks you want, you can use Ctrl/Z (Undo) to fix it. 3 - I don t think there s a feature to turn off that feature in Tables, but you don t have to...

Excel: Excel formatting

Hi Harold, I assumed you want the entire row, not just the date cell, to be greyed out. The are two ways to accomplish this: via conditional formatting, and via macro. I chose the macro approach because putting conditional formatting in all the cells would...

Excel: Excel Formula questions

Please use the web address in my signature below to send me a sample file with a smaller set of data, 5-10 rows of data, and a mockup of the desired results from that mockdata. ================ Based on the file you sent, the formulas could be: G4:...

Excel: excel numbering

In Excel 2013, if you type in A1 into cell A1, and then A2 into A2, select them both, then a href= https://599cd.com/search/?Q=autofill&key=AllExperts AutoFill /a , you ll get a continuing sequence of numbers with the A prefix. Excel is pretty smart...

Excel: Excel Sheets

One doesn t open a sheet; one opens a workbook. You say there are 2 sheets but one disappears. Have you tried Home/Cells/Format/Hide & Unhide/Unhide sheet? Feel free to send me the wb and I ll find the 2nd sheet (if it exists!) - send to me at bobumlas@hotmail.com,...

Excel: Excel VBA 2003

#1 - Even tho the 2 files are on different drives, they can t both be open because Excel s list of open files must be unique. Once you ve saved it on the E (or any) drive, you should close it or you d run into that problem. #2 - Likely you re mixing xlsx...

Excel: excel work book

I m not sure I m following the question, but I ll do my best in B3 of sheet2, if you type the equals sign and then click onto sheet1 and click a1 you will get a formula that looks like =sheet1!a1 which gives you the data you wanted in both places ...

Excel: Extract the desired data with category wise in new sheet with category name

Amit, Perhaps this is what you want: Sub Extractdatatonewsheet() Dim curr As Worksheet Dim rg As Range Dim ss As String, s As String, x As Long Dim rng As Range, shNew As Worksheet, r As Range, cell As Range Set curr = ActiveSheet s = curr.Name...

Excel: Extract the desired data with category wise in new sheet with category name

Amit, If I understand what you want, then I believe this will do it. Sub Extractdatatonewsheet() Dim curr As Worksheet Dim rg As Range Dim ss As String, s As String, x As Long Set curr = ActiveSheet s = curr.Name If InStr(1, s, , vbTextCompare)...

Excel: find matching values across workbooks

Martin, I see that I have misunderstood the question. Here is a revised answer: Let us assume that all the workbooks are closed. In a master workbook you will enter formulas. Now let us assume that each of the 32 workbooks has a single sheet...

Excel: Formatting

Danyelle, If you had a database/list (multiple rows and columns) with Company Name in the first column, then you could use the vlookup formula. Assume the list is like this on a sheet Named Data A1: Company name B1: Street address C1: City D1:...

Excel: formula issue

Christopher, that error would indicate that there is no named range named indexdatabase or there is no named range named ROW or there is no named range named columns. I say that because INDEX, MATCH, BA13 and BF12 should all be legitimate. Maybe columns...

Excel: Formula to Organize Data

If you use some blank helper columns to turn all dates into dates in the current year, you could then sort on this column =date(2013,month(yourcell),day(yourcell)) to be doubly safe, have another column for the true year =year(yourcell) once done,...

Excel: Frequency Function

Jesse, The first argument doesn t have to be a single column - only a contiguous range of data. Did you array enter the formula in enough cells to get the output for the Bin array you provided? If you want to send me a sheet, I can take a look...

Excel: generate spreadsheets from a list and a template

Hi ML, Sure! What I would do is put a counter in cell A1 on workbook B (or any other convenient cell) and then use a function to fetch information from the other file. Lets assume the sheet containing the data is called Projects and you want information...

Excel: Hyperlink

Hi Ravi, This is quite straightforward. You can follow any of the below options: 1. Just enter the website address in the cell and excel will start reading it as a hyperlink. 2. Alternatively, you can go to Insert Menu and click on Hyperlink (keyboard...

Excel: Hyperlink

Since you don t tell me what version of Excel you re using, I ll assume it s Excel 2007. Open a new workbook. Select cell A1 of Sheet1. From main Excel menu, choose Insert, Hyperlink. In Text to display: type Google. Under Link to: select Existing File...

Excel: hyperlink question

Instead of using hyperlinks, try using selection change events (VBA) For example, this code will move to column X whenever you click in column A: right-click the sheet tab, select View Code, enter: Private Sub Worksheet_SelectionChange(ByVal Target As Range)...

Excel: Image appear with radio buttons - excel

Lani, Yes, my email is still twogilvy@msn.com You would need to send me the workbook so I can better understand the situation. After seeing it I might need to ask you for clarification if I do not understand the requirement. -- Regards, Tom...

Excel: Locate a NamedRange in a sheet

Create this VBA function: Function Where() As String For Each x In ThisWorkbook.Names If LCase(x.Name) Like cellheader* Then If Range(x).Parent.Name = ActiveSheet.Name Then Where = Range(x).Address Next End Function then in any sheet,...

Excel: Macro issue

Christopher Mitchell, This would be my guess: Sub hideneeds() hideneeds Macro Dim sh as Worksheet set sh = ActiveSheet Range( A1 ).Select Sheets( Sheet2rng ).Select Range( J2:K1500 ).Select Selection.Cut Range(...

Excel: Macro to stop a macro that is running at a specific time

Anand Shah You can add a line in the loop that checks the time if Time timeserial(15,30,0) then exit sub checks if later than 3:30 PM put in whatever time you want it to stop Make sure it is in the innermost loop (the do nothing loop),...

Excel: macro writing

Christopher Mitchell, It can be done without selecting anything, but I don t know if that would be even noticeable with such a small macro. Sub auttwo() auttwo Macro Sheets( Main ).Range( D2:F2 ).FormulaR1C1 = 12/23/2012 if...

Excel: Macros / VBA

You won t be able to (easily) record this but something like Sub tester() Dim n As Long n = WorksheetFunction.CountA(Worksheets( Sheet2 ).Range( B:B )) + 1 For Each cl In ActiveSheet.Range( B:B ) If InStr(cl.Value, a ) 0 Then Sheets( Sheet2...

Excel: Number Format

Gary, My understanding is that what is offered in the numberformat for numbers and currency is dependent on regional settings. I would assume the default for built in custom number formats could be dependent on regional settings as well. I don t have...

Excel: Open file2 if file1 not found

Doug, made some adjustments and tested it and it worked for me - including using the template.xls when the name was not found. Had missing files in several places in the list. revision: commented out my test path Sub MyCode() Dim bkData...

Excel: Prevent Hide Columns

It s not something I ve ever done, but i ve found some possible sources - a good discussion is here http://stackoverflow.com/questions/4457672/excel-vba-restrict-edit-area-in-excel-sheet which I got by typing excel vba prevent hide columns into Google (you...

Excel: Print from a list and increment on cell reference

Amit, Assume the button is an activeX commandbutton this would be the Click event for you button (so this code goes in the sheet module of the sheet with the commandbutton. Assume the button is on the same worksheet as the data you want to process....

Excel: problem with spreadsheet

Christopher Mitchell If you want to send me a single sheet with at least the three columns you describe and DC8 and indicate which rows would be counted, then I will see if I can provide a formula. Make sure the cells are exactly as they will be in...

Excel: Ranking scores in a new way

Rick, I don t really follow what you are asking/describing. If you want to send a worksheet with some representative data and then explain for that data how you ranked that or scored that, then maybe I can understand what you are doing and suggest something....

Excel: Reformat the output excel data

Arvind, Sure - that can be done. However, you have confused me. Excel labels columns (A, B, ...) and rows are numbers (1,2,. . . ) but you say the opposite (you may be talking about labels you have entered in cells in excel). So if you could send me...

Excel: rewrite formula

Chris Mitchell IF column E is a condition similar to columns B and DG then =IFERROR(COUNTIFS(B15:B3000, 11 ,DG15:DG3000, =12 ,E15:E3000, )/DC8*100, ) if you actually mean you don t want the formula to calculate unless E15 is not empty then ...

Excel: rewriting a formula

Christopher Mitchell, Best guess at what you want: =IF(C3 C2,IF(IFERROR(VLOOKUP(I2,Sheet2rng!$A$2:$CZ$652,10,FALSE), )= , , / & VLOOKUP(I2,Sheet2rng!$A$2:$CZ$652,10,FALSE)) & IF(IFERROR(VLOOKUP(I2,Sheet2rng!$A$2:$CZ$652,11,FALSE), )=0, , /...

Excel: Rollup across separate workbooks

Hi Mark, The code I gave you can only list files in a normal directory located either on a local drive or a network share os an external drive. It cannot handle internet style addresses. I have searched fora a bit and found some code here: http://www.mrexcel.com/forum/excel-questions/511617-sharepoint-library-visual-basic-applications-loop-through-all-files-library.html...

Excel: Last Row Not Incrementing

Achilles, lastrow = cells(rows.count,1).end(xlup).row Write to each row: Worksheets( Sheet2 ).Cells(1, A ).Resize(lastrow, 1).value = xmldoc.text or Worksheets( Sheet2 ).Range( A1:A & lastrow).Value = xmldoc.txt I could also do Worksheets(...

Excel: simplifying a macro

Christopher Mitchell. You have the command calculate in each of your macros. But that would indicate you have formulas in the sheet and that can slow down copy and paste operations so I just set calculation to manual, did the code, and then at the end...

Excel: Start macro when workbook is opened

In the visual basic editor (VBE) look on the left side at the project explorer. Your workbook should be shown there. right click on the ThisWorkbook entry for that workbook. Select view code. In the left dropdown at the top of the resultant module, select...

Excel: stock

Hi Harish, This would not be a simple task that I can tell over an email. I can provide a sample guideline. You would need to build up further on that. Suppose the first sheet(sheeta) has the following layout A B C CodeNo Stockqty...

Excel: time sum question

I would use the HOUR, MINUTE, and SECOND functions to break the time values up into their components, then add those together. If you divide the MINUTES by 60 you ll get a value to add to the HOUR column. This will prevent you from having to deal with the...

Excel: user form

Tony, I found the problem and sent it back. You were looping through more cells than there were labels to hold the data so that caused the error. When you change the name to Userform_Initialize2, that is not the name of an event, so that code doesn t...

Excel: VBA

Hi Aji, I m not sure why you are trying to populate the value in a combo box: ComboBox1.Text = Sheets( Sheet1 ).Range( F & row_number) Since you are always going to populate it through the macro, why not just create this as a normal text box and populate...

Excel: VBA Open file with wildcard

Doug, Here is a little tester routine with the critical code in it: Sub abc() Dim sPathN As String Dim i As Long Dim v As Variant Dim sName As String, s2 As String, s3 As String sPathN = D:C_DriveData ReDim v(1 To 1) ...

Excel: vlookup issue

Christopher Mitchell, You could be describing several different issues. I will assume that cell AL2 has been formatted to show a whole number but it actually contains the number 7.5 I will assume that numbertolevels contains an 8 in the first column...

Excel: Want to view ruler in print preview

Robin, This is an Excel group. You say Word 2010 but you also talk about cell. In any event, in either Excel or Word 2010, go to the view tab, then choose Print Layout under the document views. Then go to the show section under the view tab and make...

Excel: Writing Vlookup formula in VBA

David, This determines the address of the table: Set r2 = sh2.Range( A2 , sh2.Cells(sh2.Rows.Count, 2).End(xlUp)) It could be written Set r2 = sh2.Range( A2 , sh2.Cells(sh2.Rows.Count, B ).End(xlUp)) and then it would be more obvious what to...

Excel: Assistance with Conditional Formatting for showing the critical path

In cell H3 enter =MATCH(H2,D2:F2) + 2 Select A5:A13. From Excel menu, choose Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format, Format values where this formula is true: =offset(A5,0,$H$3)=1 Format, Fill,...

Excel: Automatically open second WB

Hi Jason, A good start is the Excel XXX VBA programming for Dummies book by John Walkenbach (version does not matter very much, but best to take at least the 2007 one). Also good are the step-by-step books by Microsoft Press. Always start by trying...

Excel: Comma between values when value is 100 and above

That s because 93,95,97 (whatever) can t be interpreted as a real number - it s clearly text. But 100,101,102 etc looks like a real number: 100 million, 101 thousand 102. Solution: Put a leading single quote before the string. That is, start Hold off...

Excel: Compare two spread sheet for data validation in Excel 2007

Kenneth: As long as there is a unique identifier field in the master (such as invoice number or social security number), then you can use a VLOOKUP formula to compare the master sheet to the extract sheet. If an item on the master sheet is not found on...

Excel: and / or condition parenthesis

attis, If c1=A or c1=B and c2 A or c2 B then c3=Y would be If (c1=A or c1=B) and c2 A and c2 B then c3=Y So in your first example I assume, you want C1 to be A or B AND C2 to not be either A or B to perform the action as I show above,...

Excel: Conditional Formatting

First, you can simplify your CF formula. By definition your CF formula is a TRUE/FALSE construct, so you don t need to test the results and put in your own TRUE/FALSE. For instance, in a cell, put in this de facto FALSE formula: =10=8 Since 10 doesn...

Excel: Consolidation Macro Problem with different sheet names

Sub importdata() Dim NewBook As Workbook, CurBook As Workbook Dim OutVar As Long Dim Looper As Long OutVar = 1 Set CurBook = ActiveWorkbook Set NewBook = Workbooks.Add For Each sh In CurBook.Sheets For Looper = 1 To sh.Cells.SpecialCells(xlCellTypeLastCell).Row...

Excel: Coping data from one excel to another

Mohammed, So you said the master workbook holds the code and that all the other workbooks are in the same folder as the master workbook. I assume every workbook in that folder needs to be processed (except the master workbook) and that all these workbooks...

Excel: copying cells

Weichao I can think of two methods-- 1 Use the copy and paste the entire sheet method described in the first answer. 2 Use the format painter-- to do this click on a row or rows that is/are formatted the way you want, then click on the paintbrush...

Excel: counting colored cells using VBA

Please email randomstu at gmail dot com. Attach the file itself. In the body of the email, reference specific sheet and cell(s) that illustrate the problem, and very clearly explain (using those specific cells as an example) what the correct values should...

Excel: Different sheet names everytime, so unable to consolidate the data

Hello, please upload the file from http://programarexcel.com/p/contacto.html I check the file and give it back with the solution In http://programarexcel.com examples can download free excel macros programmed in VBA, macros can be downloaded and adapted...

Excel: Different sheet names everytime, so unable to consolidate the data

MANY SHEETS TO ONE SHEET Here s a macro for merging data from multiple sheets into a consolidation sheet where the source sheets are the same layout. a href= http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/merge-functions/sheets-to-1-sheet...

Excel: excel 13

it would need some VBA to achieve this - I would SUGGEST a clear sheet button would be the way to go - this would record the data from your quotation on the summary sheet - it would be easier if I could see an example of the file - I can then write you a...

Excel: excel

Diane, You can t attach workbooks. As far as doing what you want to do, you could use the advanced filter, you could use formulas or you could use. For both a macro and a filter, you would have to execute or run it. With formulas, they will update...

Excel: excel formula

Example: In cell a1 enter 3, and format as Accounting with $ and 2 decimal points, so that A1 appears as $ 3.00 Then select a range that includes A1. From Excel menu, choose Home, Conditional Formatting, New Rule, Format only cells that contain,...

Excel: Excel Formula

Standard VLOOKUP will not give you ALL possible values. Try this - http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=119:vlookup-multiple-values-return-multiple-corresponding-values-for-one-lookup-value&catid=77&Itemid=47...

Excel: Excel Help

Hello, please upload the file from http://programarexcel.com/p/contacto.html I check the file and give it back with the solution In http://programarexcel.com examples can download free excel macros programmed in VBA, macros can be downloaded and...

Excel: Excel Import

Hi Richard, If you select all columns and select skip, then you are effectively telling excel not to import anything, so the screen will naturally turn up blank. If I have misunderstood your question, can you send some screenshots of the steps and problem...

Excel: Excel Userform

Hello Sunny, I have worked on something similar before. Can you send me your file with some sample data so I can update the macro to meet your needs and send it back to you. Please send the mail to gulshanrajpurswani@gmail.com and mention AllExperts...

Excel: Extracting data from a string of data

Gail, for the examples you show (no spaces in the surname), you can use =MID(A1,3,FIND( ,A1,3)-2) this is written to find the surname from the string in A1 A perhaps more robust approach would be to look for the w/e to determine the end of the...

Excel: Sorting lines in one document into search-term-entitled smaller documents.

Excel: Sorting lines in one document into search-term-entitled smaller documents.

Excel: File rename

Like so: pre Option Explicit Sub RenamePDFs() Dim fPATH As String, fNAME As String, OldCodes As Range, MyCode As Range, NotFound As Boolean fPATH = C:PathToMyFiles remember the final in the path string ...

Excel: format a cell

Example: In cell a1 enter .55 Then select a range that includes A1. Right-click on the selected range, and from the menu that appears, left-click on Format Cells. The Format Cells popup form will appear. On this form... Select Number tab. Under Category:...

Excel: If formua

Sid, If you have a list of words in D1 and you are trying to check the individual words, then this approach would not be appropriate. But If you have a phrase/string in D1 and you want to check if it appears as a substring of any cell in E1 to E300 then...

Excel: Formula

Two methods spring to mind - with a three way cost, a nested if statement would be one way, but do it largest first smallest last =if(quantity 15,cost3,if(quantity 10,cost2,if(quantity 5,cost3,cost4))) Alternatively, you could use vlookup with the match...

Excel: formula in Excel 2010

Make sure you re using a RANGE of cells for your SUM, like =SUM(A1:A20) Then make sure you use INSERT ROWS or INSERT CELLS to insert new rows INSIDE of that range. The formula will automatically update for you. Be sure to visit my web site and watch...

Excel: Formula results inserted into another sheet

You can refer to a value on a different sheet like this: =Sheet2!F4 That will give you the value in cell F4 from Sheet2. Be sure to visit my web site and watch my B FREE /B 90-minute A HREF= http://599cd.com/Excel/AllExperts Microsoft Excel...

Excel: Further re filtering

Jeremy, You could do this in several steps using an advanced filter. The advanced filter requires a separate criteria area. You would first filter on the list number using the copy option and output the list of people to a new location. Then you...

Excel: Hide/ Unhide Checkboxes in Excel 2007

excel 2007 is a version I don t currently have access to which may make this difficult to answer - do you have an example sheet I could see - this may help me to determine if I can come up with a solution for you - my GUESS would be that some form of data...

Excel: Horizontal Filter

Gail, no horizontal filter. I would insert a new row 1. Say your first date starts in d6 (old d5). In d1 I would put =if(weekday(d6) 5,na(), ) then drag this across all the columns in row 1 select row 1. Hit F5 to get the goto dialog. Click...

Excel: Including printing instructions in a macro

Gail, Sub ABC() Dim r As Range Set r = Range( A1:I & Cells(Rows.Count, A ).End(xlUp).Row) ActiveSheet.PageSetup.PrintArea = r.Address(1, 1, xlA1, True) ActiveSheet.PageSetup.Orientation = xlLandscape AcitveSheet.Printout End Sub -- Regards,...

Excel: linked a variable cell values to other worksheet

Weichao, If you mean the the cell $E$4 holds the name of the sheet where the range A1:B4 is located, (for example, E4 holds the value Sheet5) then =VLOOKUP(A1,Indirect($E$4& !$A$1:$B$4 ),2,TRUE) this would produce the equivalent of =VLOOKUP(A1,Sheet5!$A$1:$B$4,2,TRUE)...

Excel: Macro: Comma between values when value is 100 and above

Ah, now I get it. if you use the comma as a separator, Excel thinks you are entering numbers with a thousands separator in them and then just puts the number in without the thousands separator. It would have worked if you would have used a different separating...

Excel: Macro Security

If you use this: Application.AutomationSecurity = msoAutomationSecurityByUI you will be prompted whether or not to enable macros. Also, If you turn off events before letting the code open the workbook then workbook_open will not be run: Application.EnableEvents...

Excel: Moving data automatically from one excel workbook to another

Pam, You can send them to me at twogilvy@msn.com and I will look at them to see if I can provide assistance. I have a project I am doing tonight, so it may take a day or two until I can take a look at your problem. If that is too long, then...

Excel: OT AND Double time calculations

Elena, You picture is too small to make out any details. Why not send me a sample spread sheet and mark where you want the formula and how they should act. You might put in the correct and answer on the sheet as well. If I have questions, then I can ask...

Excel: Password Code

Tony, this statement is your problem Const pWord:=Sheet33.Range( w1 ) you are declaring pWord a constant and then treating it as a variable. Just declare it as a variable and use it as a variable or reference it directly. Dim pWord as Variant...

Excel: Please give me step by step process

Hey you should do a loop, but you should know some programming in VBA and notice in the following example. http://www.programarexcel.com/2013/05/macro-vba-recorre-filas-busca-y-copia.html Hello, please upload the file from http://programarexcel.com/p/contacto.html...

Excel: populating sub sheets and creating a formula or macro that will make changes to the sub sheet when i change the master.

Hello, please upload the file from http://programarexcel.com/p/contacto.html I check the file and give it back with the solution In http://programarexcel.com examples can download free excel macros programmed in VBA, macros can be downloaded and adapted...

Excel: Sorting lines in one document into search-term-entitled smaller documents.

Hi Jonathan, Here are two examples. One pulls out the lines that contains the word apple and the other that pulls out the lines that do not contain the word apple ------------------------------------- C:Documents and Settingsuser copy con sample.txt...

Excel: Splitting time calculation

Rob, Time is stored as a fraction of a day. so 6 hours is 1 quarter of a day and is stored as 6/24 = .25 Likewise, minutes are stored the same way. So to get the minutes back to the time format you can do (assume a time value in cell A1...

Excel: Trigger macro only if the active cell is what I require

Hi code must be entered on the sheet where you want to run the macro. Hi code must be entered on the sheet where you want to run the macro. Adapt the macro with the name of your page and where does the msgbox should be your code Private Sub Worksheet_SelectionChange(ByVal...

Excel: User-Defined Function and Vlookup

Question one sounds like a case for VLOOKUP - with the data stored on the spreadsheet somewhere (which makes it easy to update) vlookup will return the number in the class. For question two, IF that file is external and open, then COUNTIF will do it - if...

Excel: using conditional formatting to color some cells and not others

I warned you before this would drive you nuts. You should not be counting cells based on color, you should be counting cells based on the SAME formulas you used to conditionally color those cells. Anyway... Ambiguous Name means: this function is...

Excel: VBA

Tony, I want to be able to have it execute that specific macro, certainly didn t convey to me what you are saying now. Here is an approach to execute different actions based on which cell is selected Private Sub Worksheet_SelectionChange(ByVal...

Excel: VBA Cell Reference formula problem

Kie, My name isn t BoB If I put this in the immediate window actvr = 3 ? =if(left( & Cells(actvr, c ) & ,1)= k , x , yy and in cell C3 I have the string A6 then your string resolves to =if(left(A6,1)= k , x , yy so your...

Excel: VBA code to convert .dat to .xls

Nishant, You can use late binding. I modified your code to use late binding. I did not try to debug your code for other errors. You can do that. Sub Convert() Dim f As Object Dim fil As File Set f = CreateObject( scripting.filesystemobject...

Excel: VBA coding

I wouldn t bother with the item_in_review variable as it gives one extra line If Sheets( Sheet1 ).Range( A & row_number) = ComboBox1.Value and Sheets( Sheet1 ).Range( B & row_number) = ComboBox2.Value Then TextBox1.Text = Sheets( Sheet1 ).Range(...

Excel: adding images to a database using file times

Ben, When you first asked your question way back when, as I recall, I was amazed it worked for you because it was totally dependent on what order the Dir command returned the filenames. But you said it was working, so who was I to argue. So if you...

Excel: Altering VBA

Christopher Mitchell, Sub a() Dim btn As DropDown Application.ScreenUpdating = False ActiveSheet.Buttons.Delete Dim t As Range Dim v(1 To 3, 1 To 1) As String v(1, 1) = AAAA v(2, 1) = BBBB v(3, 1) = CCCC For i = 1 To 3 Set t = ActiveSheet.Range(Cells(i,...

Excel: array formula

Christopher Mitchell, =SUMIF($B$5:$B$363,$C$1508,$I$5:$I$363)/COUNTIF($B$5:$B$363,$C$1508) or just =AVERAGEIF($B$5:$B$363,$C$1508,$I$5:$I$363) Neither of these are Array formulas. If you really want an array formula, post back. -- Regards,...

Excel: Calculating zulu time

Martin, E2: 0545 F2: 1830 =SUBSTITUTE(TEXT((LEFT(F2,LEN(F2)-2)& : &RIGHT(F2,2))-(LEFT(E2,LEN(E2)-2)& : &RIGHT(E2,2)), hh:mm ), : , ) gave me 1245 which appears correct. That is the string 1245. If you want to make it a number as a result you would...

Excel: Change format of a destination sheet to be different from the origin sheet.

Monique, it would probably be easiest to just format the destination sheet the way you want it and then not copy the format. Sub Consolodate() Dim shDest As Worksheet, shSource As Worksheet Dim lastrow As Long, r As Range Set shDest = ThisWorkbook.Worksheets(...

Excel: colored cells that are really not colored

Chris When you copy a conditional format to a blank cell the chances are the values in the blank cell will not meet the conditional format criteria, therefore the blank cell will remain blank. As an example, if the condition is that the cell be greater...

Excel: Combo Box Not Working Now

When you protect a sheet, there are a lot of settings you can add. One of them is actually a HIDDEN parameter called UserInterfaceOnly. If you add this to your PROTECT commands, it will basically protect the sheet from humans, but leave the sheet free for...

Excel: Copying data from one excel worksheet to another

Monique, If I assume both workbooks are open and the code is in the workbook where you enter the data. For the destination worksheet I assume the name the sheet is named MyData In the workbook where you write the data, I assume the worksheet has the...

Excel: copying formulas in a "non-standard" way

Joe, Basically what you want to do is calculate the address to grab the cells from. So if I put =A1 in B2, then drag fill it, in B3 it will be =A2 so instead of =A1 in B2 I will put =row(A1) and then B2: 1 B3: 2 B3: 3 I change that to B2:...

Excel: Copying from one workbook to another workbook.

M You posted the code I provided in answer to the question when you asked it. You later asked to pastevalues. I will assume you still want to paste values assume the other workbook is named Otherworkbook.xlsx and it is open in the same instance of excel....

Excel: COUNTIF formula not working

Tara, it sounds like the problem lies with your data. there is nothing wrong with the situation you describe and the formulas you show. Best I can do is offer to look at the file. If you want me to look at your file, then send it to twogilvy@msn.com...

Excel: Deleting a Workbook

See the kill statement in VBA help: ======================================================================= Excel 2007 Developer Reference Visual Basic for Applications Language Reference Visual Basic Language Reference Statements Kill Statement...

Excel: Excel

Hi Amanda, For the click you can use the HYPERLINK function. say your sheetnames start on cell A2 going down. In B2 enter this formula: =HYPERLINK( # & A2 & !A1 , Click to goto Sheet & A2) For the coloring you would use the Conditional formatting...

Excel: Excel Find Changes

Mary, I assume you are not asking how to sort the data as you show it sorted. so assume 10.5 in your example data above is in C2 in D3 put this formula =if(And($A3=$A2,$C3 $C2), Change , ) then drag fill this down column D If that doesn...

Excel: Excel Problem

Hi Jean, I apologize for my long delay in answering. A recent death in my family has taken me away from AllExperts for several weeks. I believe your problem is that you accidentally got Excel into the 3-D editing mode. 3-D editing happens when you have...

Excel: Excell courser

Hi Sarfraz, This will select the entire row. If you want to be able to edit the data, just press tab or enter key to move across the range, macro will allow you to do that. If this doesn t serve your purpose, then I can suggest another approach given below:...

Excel: expanding an if/and formula

Chris Mitchell, One way if the condition $EF$337=$BA$10 governs all further tests then =IF($EF$337=$BA$10,CHOOSE($EC$340-100,EF340,EN340,EV340,FD340,FL340,FT340)) or =IF($EF$337=$BA$10,IF($EC$340=101,EF340,IF($EC$340=102,EN340,IF($EC$340=103,EV340,IF($EC$340=104,FD340,IF($EC$340=105,FL340,IF($EC$340=106,FT340)))))))...

Excel: Extracting the month and year from a date

Gail, Dates are actually numbers. They are stored using a date serial number. For example, if you put 41618 in a cell and then format that cell with a custom number format of mm/yyyy it will display 12/2013. If you formatted it as dd/mm/yyyy it would...

Excel: List files within folder into excel

Excel: List files within folder into excel

Excel: Searching for files from Excel sheet

Excel: Searching for files from Excel sheet

Excel: Filtering data using a macro

Instead of putting the variables in one cell, put them in a column in separate cells, like in column H (Change as necessary), then you can use this: Sub FilterMacro2() Selection.AutoFilter Dim arr() As Variant n = Application.CountA(Columns(8))...

Excel: followup on excel question

Ben, You only want to use the Dir command after you process the existing file. So I moved them (the dir to get a new file name) inside your IF tests. See if that improves the situation. Sub checkfiles() Dim sPath As String, sName As String Dim sFirst...

Excel: formula

Hello Chin, I apologize for my long delay in answering. A death in my family took me away from AllExperts for several weeks. In answer to your first question you can use the formula: =IF(B3 0,B3, ) This assumes that if the value in B3 is exactly...

Excel: Formula to calc. number of missing days

If it is literally the DAYS you want then its simply last day minus first day - as excel stores dates as serial numbers. Not sure if this gives you what you need - if it doesn t it s possible that seeing a sample sheet might help, my direct email is aidan.heritage@virgin.ne...

Excel: formula error

Christopher Mitchell, You don t need a right paren after you condition in the IF statements since there is only a single condition. You also did not close out the AverageIF function with a right paren. also, I noticed in this statement AVERAGEIF(Sheet7!B5:B1249,B32,Sheet7!S5:IS249...

Excel: formula issue

=IF(AND(L2=7,L1= Aut 1 ),Sheet7!I3),IF(AND(L2=7,L1= Aut 2 ),Sheet7!S3),IF(AND(L2=7,L1= Spr 1 ),Sheet7!AC3),IF(AND(L2=7,L1= Sp 2 ),Sheet7!AM3),IF(AND(L2=7,L1= Sum 1 ),Sheet7AWI3),IF(AND(L2=7,L1= Sum 2 ),Sheet7!BG3), )))))) You have 12 left parens and...

Excel: Formulas

There s a datepicker you can download from http://www.rondebruin.nl/win/addins/datepicker.htm which will put in a mouse right-click date picker command (free). If you send me your email (or reach me at bobumlas@hotmail.com) and send a sample height/weight...

Excel: Forula to enter todays date, but not update

VBA answer... right-click the sheet tab and select VIEW CODE, then paste this macro into the sheet module that appears: pre Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range If Not Intersect(Target, Range( A:A )) Is Nothing Then...

Excel: Help over transpose function.

Hi Venkat, Transpose is an array formula and needs to be used in a particular fashion (different from other formulae). Please follow steps and let me know if it worked - - To enter an array formula you must first highlight all the cells where the formula...

Excel: List files within folder into excel

No problem - I ve linked to the original answer and copied the text to save you following the link (!) http://en.allexperts.com/q/Excel-1059/2013/12/searching-files-excel-sheet.htm Excel prior to version 2007 can use the FileSystemObject - which means...

Excel: Macro rewrite

Chris Mitchell, Below worked for me. Sub ABC() rankaut1 Macro ActiveSheet.Calculate Range( A1 ).Select Range( EE102:EF136 ).Select Selection.Copy Range( EE64 ).Select Selection.PasteSpecial _ ...

Excel: macro writing

Christopher, Could I do this - I would believe I could. If you want to send a sample workbook (say one or two sheets - not some 10 MB file) I can set something up and then we can see if that is what you want. Then we could look at implementing it...

Excel: merging macros

Christopher, You can call them Sub Master() rankaut1 rankaut2 rankspr1 rankspr2 ranksum1 ranksum2 End Sub or you can just copy and paste all the code in another master macro - one set of code after the other - and then just run that master...

Excel: Multiple Filters in Pivot based on another columns/Sheet

Working on this offline -- will be a while, as wife was in an accident & my focus is not currently on AllExperts. If you feel it s appropriate to ask another expert, please do so. Not sure when I ll get back to this -- and we re already communicating outside...

Excel: pivot table

Hi Chin, Have you tried working with Data - Filter - Auto filter. Looks like what you need can be handled using filters. You can just select John and see the number of rooms in different lines. Alternatively, if you want totals only, then you can try...

Excel: Pivot values and defaulting

I m not aware of any way to do it - but it sounds as though you may be re-using pivot tables on a daily basis - if so, setting them up once with a dynamic named range as the data source could be the way to go - perhaps with a macro to refresh each table? ...

Excel: Protecting Formula

You can possibly use the Track Changes option as a relatively easy solution. Alternatively, something like the solution at http://vbaexpress.com/kb/getarticle.php?kb_id=1074 could be used - in that example, it tracks to another sheet, but it could easily...

Excel: Rank formula

Christopher, in general, rank works on a contiguous range of cells. It does not support Array formulas or any conditions. So I won t Rule Rank out but a lot would depend on specifics and my impression that rank isn t going to work for you. -- ...

Excel: Remove AutoFilter

Tim That would be my guess - but you really didn t elaborate on what you meant by not work. I had a worksheet with 4 tables (as introduced in Excel 2007 (the were listobjects in excel 2003 when introduced). I used this to clear the filters Sub abc()...

Excel: Searching for files from Excel sheet

Excel prior to version 2007 can use the FileSystemObject - which means you can use VBA to do a search for files - which could be written to then copy files to a folder. Since 2007, this very useful feature has been removed, and I ve not found a suitable alternative...

Excel: Select a cell and enter the current date

Monique, I assume you will use an ActiveX commandbutton, so assuming the name of this commandbutton is commandbutton1, then this would be the click event for that button to achieve what you describe. It should go in the sheet module of the sheet with...

Excel: Selecting non-contiguous cells in worksheet using VBA (without hard-coding cell references)

Nik, Here is what you asked for: Sub testSelect() Dim iRow, lastRow, firstCell As Long Dim fullSelection As String Dim fullRange As Range, r As Range Dim inputSheet As Worksheet Set inputSheet = Worksheets( Test1 )...

Excel: Showing Duplicate values

Rakesh, when there are duplicates, this should mark the first row as red and any duplicates beyond that as blue. Sub MarkDupes() Dim col As Long, i As Long, bDup As Boolean Dim minrow As Long Dim r As Range, cell As Range, cell1 As Range col = Cells(2,...

Excel: Simple Decimal Ages in Excel

Not trivial solution. Also, I can t tell the YEAR of the DOB in your diagram. If the first date is in cell A2, and the year you re looking at is in cell B2 (like 1958), then you need to do this: In cell C1 (it will contain Jan ) enter 1/1 (year is irrelevant...

Excel: tab color

It can be a macro you have disabled the right click. Open editor with alt f11 in the book inserted a module and copy this code that enables contextual menu Sub sin_clic_derecho1() inhabilita el menú contextual al clic derecho en celda CommandBars(...

Excel: tabulation

Hello Chin, Sincere apologies for the delayed response. Please see the answers below: For Question 1: 1. Option 1 is that you can try to generate a Pivot table and filter it by year. Assuming you are using excel 2007 and above, it allows you to filter...

Excel: Think you have made a mistake

Thank you for picking this up - yes, you are of course correct. I do tend to check my own answers for use in similar questions, so I m hoping this is why I made the same error twice, and I suspect it may have been partly a case of knowing what I needed to...

Excel: Tracking Monthly Money Contributions

Simon I am not sure how the suggestion I gave you limits the solution to a period. But I am guessing that the amount that is put in is the limitation, for example if you enter 12,000 as the expected contribution and you expect 1,000 per month to be contributed...

Excel: Tracking Monthly Money Contributions

Simon There are numerous ways to create what you want, it just depends on what you want. I will suggest something very simple. 1--Create a sheet for each of your members, and name the sheet for the member for example your sheet would be named Simon....

Excel: VBA alteration

Chris Mitchell, The code is set up to work when the selection changes. So it will not react to a change editing the cell. As long as the cell selection changes after a selection is made, then it should revert to the old zoom level if that new selection...

Excel: VBA and protected worksheet

Christopher Mitchell, You need to unlock the cells in the box area and in cell A1. with the sheet unprotected, you select those cells and right click - and select format cells - then go to the protection tab and uncheck Locked then get out. Now...

Excel: VBA and read write access

ActiveDocument.ReadOnly would return true if read only - though for your case you may need to reference the specific document, the readonly attribute could still be used - so if ActiveDocument.ReadOnly=true then advise user of the problem and close whatever...

Excel: VBA Solution

Jason, Sub formulas() formulas Macro ActiveCell.FormulaR1C1 = =PasteHere!R1C4 Range( A5 ).Select ActiveCell.FormulaR1C1 = =PasteHere!R[-3]C1 Range( A5 ).Select Selection.AutoFill Destination:=Range( A5:A19 ), Type:=xlFillDefault...

Excel: VLOOKUP and place results in separtate sheet

Charles, in G11 you would choose data validation and select the list option. In the resulting textbox you would type in =Sheet1!$AA$5:$AC$58 that will give you the list of counties. on sheet2 in K4 =IF(AND(Sheet1!$G$10 ,Sheet1!$G$11 ...

Excel: vlookup all sheet exept one sheet

Hanim, You already are excluding the sheet that contains the formula, so you would just add an additional check in that line that the sheet name does not equal master Public Function VlookupAllSheets(target As Range, Table As Range, Cool_Index_No As...

Excel: Adding a condition to an "if" formula, source names, s sales

source names, s sales, sales and use tax: Karon Head, =if(C8= , ,IF(ISNUMBER(SEARCH( use ,C8)), USE , SALES )) would be a start. If that doesn t work =if(len(trim(C8))=0, ,IF(ISNUMBER(SEARCH( use ,C8)), USE , SALES )) that would ignore a cell that has spaces in it or is actually...

Excel: Adding days to a date in Excel, cell a2, cell b1

cell a2, cell b1, cell a1: On a blank Excel sheet, enter in cell A1 2/4/2013 and in cell B1 5 and in cell A2 =A1+B1+CHOOSE(WEEKDAY(A1+B1),1,0,0,0,0,0,-1) Cell A2 displays 2/8/2013, because 5 days after 2/4/2013 is Saturday 2/9/2013, and the nearest weekday is Friday...

Excel: How to auto-populate a table?, erroneous code, vlookup

erroneous code, vlookup, popup calendar: AAron: A couple of things: 1. Your form is not a true form per se because EXCEL/VBA has what s called Userforms, like you see when you do a file open or display Tools, Options. Userforms give you a lot more flexibility and add a touch of professionalism...

Excel: Basic Excel Formulas, basic excel formulas, 599cd

basic excel formulas, 599cd, cell references: What is the formula? When you copy a formula it should update the cell references, so if you have =SUM(A1:A10) And you copy that formula and paste it into column B, it will update the formula to =SUM(B1:B10) Be sure to visit my web site and watch...

Excel: calculation error on excel, floating point numbers, calculation error

floating point numbers, calculation error, numerical representation: Caroline, It is a well known problems that computers in general have problem with representing floating point numbers. Just like decimal numbers have problems representing some numbers exactly such as 1/3 and 1/7. Most applications represent these numbers...

Excel: to change by percent, Sixthsense Excel Sixthsense Finding Discount For Products based on Price

Sixthsense Excel Sixthsense Finding Discount For Products based on Price: Hi Rick, Thanks for sending your question on my way. Assume that your first Row contains Column headers and your data is starting from 2nd row.... Column-D is Price Column-E is column we use it for entering discount In Column-F Discount Price (Arrived...

Excel: to change by percent, ok job, excel sheet

ok job, excel sheet, aidan: Not quite by doing only one step, but you can do it - type the value in to a cell, copy that cell, then select the range of cells which you wish to alter and choose edit, paste special - use the operation area and select multiply and then click OK - job done...

Excel: change text color for values beyond a specific value, employee logs, conditional formatting

employee logs, conditional formatting, question pool: conditional formatting will handle this - you didn t specify the version of excel, so hard to give instructions as they differ depending on version - prior to the ribbon, it s data, conditional formatting - here you can set the rules you want to use to colour...

Excel: combining duplicates from one column keeping all other data, time time time, procedure group

time time time, procedure group, time group: Still can t read it. You can send me a file using the link below. Make sure the file you send has a complete set of mock data, then a complete set of RESULTS on another sheet showing how all that mock data should be reduced. Your mock data should have...

Excel: Compare data in two columns to find matches, duplicates, excel

duplicates, excel, conditional format: Amy This Conditional Format will highlight all the duplicates in Column A. Click on the letter A to highlight the entire column Go to Conditional Formatting Excel 2003: Go to Format Conditional Formatting and select Formula is, type in =(COUNTIF($B:$B,A1)=1)*(A1...

Excel: compare now() time to a list of timevalues, cell values, vlookup

cell values, vlookup, stock value: Hi Ray, To my knowledge, excel will not dynamically update formulae values on its own unless you have a trigger. I can think of two possible answers: 1. Instead of trying to update the cell by tracking an income feed, you can probably store the feed in...

Excel: Comparing Strings in Seperate Spreadsheets, excel spreadsheets, vlookup function

excel spreadsheets, vlookup function, cell a1: CHRIS I am not sure what you mean by strings , but you can use the vlookup function to compare a cell in one worksheet to a range of cells in another worksheet. This sounds like what you want to do. Your formula might look something like this. =VLOOKUP(A1,Sheet2!A1:A100,1,FALSE)...

Excel: Comparing & transfering a sets of non repeated values, code values, column c

code values, column c, problem description: Hi Steven, I apologize for my slowness in answering your question. I am uncertain about a number of things in you problem description, and hope you can provide some clarification. 1. You mention repeated values. Do you mean that code values in column...

Excel: Complex Automatic Math Functions in Excel, functions in excel, math functions

functions in excel, math functions, a20: Sorry for the delay in replying - it was my daughters birthday party yesterday and the preparations took longer than I d planned! By default, all excel cells are set to protected status BUT the sheet is set to unprotected - so to do the first part, select...

Excel: Concatenate values in listbox?, userform initialize, question thanks

userform initialize, question thanks, jack smith: Joe, So if I understand the situation, the List contains entries with 3 right slashes like Smith, Joe / Schempf, Brad / Mouse, Minnie / Duck, Donald if that is the case than v1 will contain v1(lbound(v1)) = Smith, Joe v1(lbound(v1) + 1) =...

Excel: Conditional Formatting, conditional formatting, video tutorial

conditional formatting, video tutorial, 599cd: I assume you mean Excel 2007. If so, use the REMOVE DUPLICATES feature. I have to admit that I don t have 2007 installed on any of my machines any longer, but in 2010 it s under the DATA tab in the TOOLS section. Be sure to visit my web site and watch my...

Excel: Constructing a series, gross fixed capital formation, gross capital

gross fixed capital formation, gross capital, gross fixed capital: Hi Sadia, Even after seeing the email you sent, I m unable to help you on this issue. Can you repost the question to the common pool so another expert can help you out. Alternatively, please let me know if it is possible to speak and maybe we can get a...

Excel: Converting number to words, numerical format, richard florida

numerical format, richard florida, excel worksheet: Question 1--- There is not an easy answer--here is a link to check out to see how it can be done with Visual Basic. http://support.microsoft.com/kb/213360 Question 2--The simplest way is to create your formulas and then save the sheet as a template....

Excel: copy 2 worksheets to new workbook, fname, sheet1

fname, sheet1, saveas: Tommy, I assume you know the name of the sheets so I have put in Sheet1 and Sheet2 as placeholders for the name. Sub ProcessSheet() Dim bk As Workbook Dim fName As Variant Worksheets(Array( Sheet1 , Sheet2 )).Copy creates new workbook Set...

Excel: Copy paste special values, fname, saveas

fname, saveas, previous question: Tommy, there is no copying of cells in that routine - it copies worksheets. So best I can guess is that you want me to then copy the data on each sheet and paste special values. Sub ProcessSheet() Dim bk As Workbook Dim fName As Variant Dim sh...

Excel: copy/paste text box between Excel books, copy paste text, mouse movements

copy paste text, mouse movements, arrow: Sorry, but I am not sure since I have not seen the file that had the text box or block arrow to see what s happening with them and I don t know what key strokes or mouse movements you were doing and/or are doing now. In other words, I need to see the file...

Excel: Data Sort out, csv format, pivot table

csv format, pivot table, perfect health: It looks as though you need to use a pivot table - this would summarise all the data easily - you didn t specify the version of excel, so it s hard to give specifics as to how to do this, but I m more than happy to talk you through it - if it helps, you can...

Excel: Data Sort out, csv format, cell a1

csv format, cell a1, pivot tables: Bill if you are using excel 2007 or later, then you can use sumifs this is a new function introduced in Excel 2007. It allows you to sum on multiple criteria Assume the left most entry ID is in cell A1 in your example then in cell I3 (under mail...

Excel: Data validation in EXCEL, data validation, cells

data validation, cells, choices: Simplified answer. If the choices in col 2 are a, b, or c, then you can use this as the data validation rule. Choose List, then for the list enter this formula (which assumes the choices for a are in cells I1:I2, for b are in I3:I4, and for c are I5:I6):...

Excel: Deleting multiple columns, sheet tab, 599cd

sheet tab, 599cd, video tutorial: Click on the first sheet tab. Now hold down the SHIFT key and click on the last sheet tab. This will select all of the sheets in the middle in addition to the two end sheets. Now select the columns you want to delete. Delete them. This will delete them in...

Excel: difference between dates, chris mitchell, addin

chris mitchell, addin, ogilvy: C. Mitchell Uses the Networkdays function. It is detailed in Help, so I am sure you can see how to use it. You can also define what your holidays are and it will consider those in its calculations. This used to be in an addin, but is a built in function...

Excel: Dynamic Array of Sheet Names, cell b2, cell a2

cell b2, cell a2, dynamic array: Joe, I was demonstrating that my formula worked perfectly for what you want it to do. I put my suggested solution in column B merely to compare it to your original formula in column A. You seem to completely have misunderstood my second response. I...

Excel: EUR Currency format to USD, italian colleagues, currency format

italian colleagues, currency format, conversion rate: krystle You can use the find and replace feature (it should be on your toolbar in the home tab) to replace all of the decimals with commas. But remember this doesn t do a conversion to USD for you it merely replaces the decimals as commas. If the data...

Excel: excel 2003, cell a1, a2

cell a1, a2: Select row 1 and format as Date. If you want cell A1 to be today s date, enter =TODAY() In B1, enter =A1+1 to display tomorrow s date. Copy the formula in B1 to C1,D1,... to display each date after that. Note that =TODAY() will change if you...

Excel: Excel 2012, exact spelling, data validation

exact spelling, data validation, mandatory programs: Aaron: This auto-fill can be done in Excel; however, it requires some slight trickery. To accomplish this task, the range of cells being used for your data validation list box needs to be located immediately above (or below) the cell with the drop-down...

Excel: Excel, transaction sheet, excel vba

transaction sheet, excel vba, barcode scanner: Sammy, You say you are not expert at programming - so that would lead me to believe you have some expertise with programming in Excel VBA. With so little information, it is clear that I can t suggest any specific code. You said two things: 1) I would...

Excel: Excel Cell Color Flashing, code option, module option

code option, module option, interior color: Put this code in a normal module: Option Explicit Dim mdNextTime As Double Sub FlashCells() Dim oCell As Range Dim oArea As Range For Each oCell In Range( D4:D100 ) If LCase(oCell.Value) = n Then If Not oArea...

Excel: Excel drop down for date and time., keyboard shortcuts, semi colon

keyboard shortcuts, semi colon, entering time: I don t think you are going to be able to do this without VBA, but these keyboard shortcuts MAY be the way to go? •To insert the current date, press CTRL+; (semi-colon). •To insert the current time, press CTRL+SHIFT+; (semi-colon). •To insert the current...

Excel: Excel/Expand a hyphenated series into a comma-separated series, string s2, string s1

string s2, string s1, string base: Jeff, Sorry - I pasted in the wrong macro. I had a bunch of workbooks open at the time. Here is the correct macro: Sub abc() Dim lb As Long, ub As Long, i As Long, k As Long Dim cell As Range, s As String, s1 As String, ss As String Dim ldr As String,...

Excel: Excel formula IF & AND, cell a1, excel formula

cell a1, excel formula, stock value: Hi Alan, Answer: =IF(A1 =10, Stock ,(IF(A1 =1, Low stock , No Stock ))) Detailed answer: The logic you have given seems to contradict in some places. I m assuming you mean -- If stock is less than 1, then No stock ; if stock is greater than or equal...

Excel: Excel Formula, excel formula, richard florida

excel formula, richard florida, florida usa: M. This will require a macro using visual basic. Which can be complicated to write unless you are familiar with VBA. Attached is a link to a website that explains the coding. http://ruzbeh.info/how-to-convert-figures-to-numbers-in-ms-excel-2007/ Hope...

Excel: Excel Formulas, pmcd, column c

pmcd, column c, excel formulas: kapila, Here is my answer to what I think you are asking: Assume the sheet with the data is named Data in the second sheet you show A and B but it appears that Jan-13 is in column B and Feb-13 is in column C I assume you want to add up all the...

Excel: Excel Macro - for sending mails through Outlook, mail need, macro in excel

mail need, macro in excel, excel sheet: No, I don t need payment but I was asking for it. This site has recently allowed some volunteers, who have qualified and earned the privilege over the years, to charge a small fee for the answers that take a little more time than just a reply to an email....

Excel: Excel-Process, consultant freelance, everyday basis

consultant freelance, everyday basis, excel application: Hi Jay, Yes, this is a type of task that can be done very easily by macros. It may even be possible to use Excel s record mode to record a macro to do this and not have to write any code, but this would only be the case if you always extract the values...

Excel: Excel Protection, note excel, thanks in advance

note excel, thanks in advance, cursor: You should have asked the questions separately, but... 1. You must first select the entire sheet and unprotect the cells--they are locked by default, but protection does not lock in until you protect the sheet. Then you must lock cells only column B....

Excel: Excel Question 1, last question, ogilvy

last question, ogilvy, r6: Abdule In sheet report in the following cells: In R6 =IF($S$3= , ,OFFSET(Database!$A$1,ROW()-1,MATCH($S$3,Database!$A$2:$M$2,0)-1,1,1)) In S6 =IF($S$3= , ,OFFSET(Database!$A$1,ROW()-1,MATCH($S$3,Database!$A$2:$M$2,0),1,1)) In T6 =IF($S$3=...

Excel: Excel Question, o4, n4

o4, n4, ogilvy: ABdul, In N4 =IF($N$2= , ,OFFSET($A$1,ROW()-1,MATCH($N$2,$A$2:$J$2,0)-1,1,1)) In O4 =IF($N$2= , ,OFFSET($A$1,ROW()-1,MATCH($N$2,$A$2:$J$2,0),1,1)) Then select N4:O4 and drag fill down the column until you get to the last row you need. Tested...

Excel: excel return part of cell, trim right, menu choices

trim right, menu choices, tools group: Sorry, in excel prior to 2007 version, it s the menu choices data, followed by text to columns - you would need to select the data first, then use that choice. In 2007 onwards, click the Data tab and then click the Text To Columns command in the Data Tools...

Excel: Excel seems to be open but doesn't show up, video tutorial, 599cd

video tutorial, 599cd, task bar: Sorry, I have no suggestions for you. It sounds like something is corrupted. I would say to UNINSTALL and reinstall office, but if you ve lost your discs, that could be hard to do. I wish I could be of more help. Be sure to visit my web site and watch my...

Excel: Excel Subtraction, cell b1, cell a1

cell b1, cell a1, richard florida: Andrew Sorry for being late in answering-I did not receive your question until today. I am not sure where yu are putting the formula--if it is in cell A1 of page 1, then you will get the result that your are getting. This is because you are replacing...

Excel: Excel VBA Clear Contents, excel vba, ogilvy

excel vba, ogilvy, cells: Doug, Sub abc() Dim sh As Worksheet, r As Range, cell As Range Set sh = Worksheets( Sheet1 ) Set r = sh.Range( A2 , sh.Cells(sh.Rows.Count, 1).End(xlUp)) Set r = Intersect(r.EntireRow, sh.Columns(6)).Resize(, 8) For Each cell In r If cell = 0...

Excel: exel, question sheet, invoicing

question sheet, invoicing, premise: Yes, it s possible. There s a sample file here called My Invoicing, though that was written for 2003 and may require some tweaking with the code. It is the same premise, and copies data to the sales worksheet. http://www.theofficeexperts.com/downloads.htm#ExcelDownloads...

Excel: Exit Sub philosphy, vba functions, excel workbook

vba functions, excel workbook, philosphy: Hi Dave, It is good practice to reduce as much overhead as possible. But that said I would have to say that in well-written code the use of Exit Sub has very little to do with reducing overhead, and more to do with making the code clean and understandable....

Excel: Extract data from a field and paste multiple locations, h6, spread sheet

h6, spread sheet, gas oil: Jeremy H6: AHU ELECT & GAS & OIL & P/U & GEOTHERMAL E6: =TRIM(MID(H6,FIND( & ,H6)+1,FIND( & ,H6,FIND( & ,H6)+1)-FIND( & ,H6)-1)) F6: =TRIM(MID(H6,FIND(E6& & ,H6)+LEN(E6)+2,(FIND( & ,H6,FIND(E6& & ,H6)+LEN(E6)+2))-(FIND(E6& & ,H6)+LEN(E6)+2))) ...

Excel: Find and Highlight Macro, arvind, yahoo

Excel: Find and Highlight Macro, arvind, yahoo

Excel: Vlookup and sum answers with same data, prod, cells

Excel: Vlookup and sum answers with same data, prod, cells, actuals

Excel: filter rows, blank rows, home tab

blank rows, home tab, number column: Since Pennsylvanians have long felt sorry for those from New Jersey, I ll answer. :) Just teasing. As long as your data has no completely blank rows and no completely blank columns in between other rows and columns, then you should be able to click anywhere...

Excel: Find and Highlight Macro, conditional formatting, vlookup

conditional formatting, vlookup, thanks in advance: Hi Michael, If I understand right, you want a cell highlighted if the value matched with the data available in another worksheet. You should be able to achieve this by using a combination of vlookup and conditional formatting. Please let me know if this...

Excel: Formula with 3 possible answers, blue cells, b16

blue cells, b16, performance sheet: Hi Ben, Assuming you are going to place the formula in E9, E16, and E23, the below formulae should give you the required results: Formula in E9 =IF(AND(B9 B16,B9 B23),3,(IF(OR(B9 B16,B9 B23),2,1))) Formula in E16 =IF(AND(B16 B9,B16 B23),3,(IF(OR(B16...

Excel: Formula for Calendar dates, calendar dates, weekday

calendar dates, weekday: In cell A1, enter 5/15/13 In cell A2, enter =A1+CHOOSE(WEEKDAY(A1+10),11,10,10,10,10,10,12) and it will display 5/27/13. This means that 10 days after Wednesday 5/15/13 is Saturday 5/25/13. The next non-weekend day after that is Monday 5/27/13...

Excel: .formula does not work in french setting, error trapper, french computer

error trapper, french computer, false indication: VBA should be fine because it s always written in english regardless of the language - I WONDER if the error trapper is giving you a false indication of where the error is though because you are using FileFolderExists which isn t native to excel so MAY be...

Excel: Formulas in cells vs VBA, calculation speed, vba code

calculation speed, vba code, multiple times: Hi Dave, The word better is a bit subjective. Whether an approach is better depends on whether you are concerned about efficiency (calculation speed), file size, functionality (i.e., does it work the way you want it to?), simplicity (are the formulas...

Excel: Hide Worksheet Based on Cell Value, tim tim, thanks in advance

tim tim, thanks in advance, cells: Tim You make your decision on the the value of the last cell in column O in the activesheet. As soon as a sheet is not hidden, it remains the activesheet and is tested over and over again rather than the sheet pointed to by the ws variable. Your intent...

Excel: Link cells in one sheet with unavailable sheets, employee details, employee names

employee details, employee names, dear tom: so for row 2, assuming column E is the bonus column so in E2 =if($A2= , ,Indirect( &A2& !H87 )) this assumes the employee name will have a space in it and as you say the name of the sheet of emp X is emp X, You can then drag fill this formula down...

Excel: Linking embedded spreadsheets in Word docs, ms word doc, accommodation bookings

ms word doc, accommodation bookings, excel spreadsheets: You could do this in two steps: 1. Create the calc sheet in Excel and save it 2. Copy the part of the sheet you need and paste-special, link it in the Word documents. Now if you edit the embedded Excel in either document, both documents are updated...

Excel: Linking to a master sheet, sheet tab, inputted

sheet tab, inputted, spreadsheets: If the information is in the exact same cells, then you can group the sheets before you enter the data or insert rows, etc. With the master sheet active, ctrl/click on the other sheet(s), then whatever you do on one sheet will be done on all the sheets in...

Excel: List box's macro: Adding index formula to Linked Cell, index formula, bbb

index formula, bbb, ccc: Now I understand. Comment out or delete the .LinkedCell = $A$5 line Double click the Listbox in EDIT mode (I assume you know how to do that since you created the control to begin with) and you should see this: Private Sub ListBox1_Click() End Sub...

Excel: List to start at top, blank cells, data validation

blank cells, data validation, dynamic range: I m a little puzzled on a number of counts - firstly that the named range defaults to the bottom, as this isn t normal behaviour (do the blank cells actually contain a space? If so, remove the space and it should behave). Secondly, that a dynamic range won...

Excel: Lood down column, copy data to multiple workbooks, blank cell, tom hope

blank cell, tom hope, sheet1: Paul, in your example, I am assuming the eof is really a blank cell - so you want to stop processing when the next Month name would be a blank. first I would check this statement: Filename:= C: ilepath & x & .xls and make sure that is a correct...

Excel: lookup multiple critera, vendor number, formula index

vendor number, formula index, number column: Sherry, Just as a way to understand the formula, as the first phase of development of the formula, you can get the row of the entry with this forumula =sumproduct(--(Sheet2!$B$2:$B$500=$A2),--(Sheet2!$H$2:$H$500 =1*$K2),--(Sheet2!$I$2:$I$500 =1*$K2),Row($B$2:$B$500))...

Excel: Macro, matell, account description

matell, account description, information account: Hi Leanne, Can you share the excel file or mail it to me at gulshanrajpurswani@gmail.com with subject containing AllExperts Please replace real data with dummy data but still maintain the correctness in data. Please send the excel format as you would...

Excel: Macro for Hiding Rows, blank cells, blank cell

blank cells, blank cell, column c: Sharon, Sub HideRows() Dim cell As Range, rng As Range Cells.Rows.Hidden = False For Each cell In Range( C12 , cells(rows.count, D ).End(xlup).offset(0,-1)) If cell.Value = cell.offset(-1,0) Then cell.EntireRow.Hidden = True End If Next End...

Excel: Macro to loop a find and highlight text, irow, search range

irow, search range, endless loop: You didn t do anything necessarily wrong - you just told the macro to do the search 65534 times. It should stop after that. If you want it to be a little more dynamic you would do something like Sub Search() Dim r As Range, sAddr As String Range(...

Excel: macro to record sequence of numbers, target value, sheet tab

target value, sheet tab, v1 v2: C. Mitchell, Right click on the sheet tab where you want this functionality and put in this code I recall you had a range of cells where you wanted this behavior - I don t remember the exact last row so I put in C20. Change that to represent the range...

Excel: Manipulating multiple .csv files, importing, sorting, combining, csv files, spath

csv files, spath, sh1: John, as I understand your description, you want to copy row 1 from the scratch sheet (sh1) after you sort the data descending on column C. What is somewhat confusing is whether you have a header or not or if you do, you are assuming it will be sorted out...

Excel: Matching 2 criteria and copying data from one workbook to another, ws2, ws1

ws2, ws1, book2: Hi Derrik, Untested: Sub MatchAndCopyData() Dim WB1name As String Dim WB2name As String Dim WS1 As Worksheet Dim WS2 As Worksheet Dim LastRow2 As Long the last data-filled row in WS2 Dim iRow1 As Long Dim iRow2...

Excel: Microsoft Excel 2010, blank cells, blank cell

blank cells, blank cell, value error: I m not aware of any issues with Windows 8 specifically, but might be worth checking what version of excel is being used by your colleague - I would also check that all cells are actually blank and formatted as numeric - any cell containing a space would cause...

Excel: Military Time and Date, mary lindsey, military time

mary lindsey, military time, time field: Hi Geri, In the formula bar or the cell where you want the time and date to appear, type: =now() The result will be 2/19/2013 13:10 If you want the date to show as 02/19/13, then with the cell selected, go to the custom date/time field and edit it to...

Excel: Need Formula, saturday date, bob bob

saturday date, bob bob, fri: Bob, In cell F10, put in this formula: =IF($C$6= , ,IF($C$6=DATE(YEAR($C$6),MONTH($C$6)+1,0),IF(COLUMN()-5 =WEEKDAY($C$6),$C$6-(WEEKDAY($C$6)-(COLUMN()-5)), --- ),IF(MONTH($C$6-(12-COLUMN()))=MONTH($C$6),$C$6-(12-COLUMN()), --- ))) then select...

Excel: In need of Trendlin analysis, left hand corner, secondary axis

left hand corner, secondary axis, chart functions: Gary: You will need to perform several steps to add this trendline . Please note Excel has a built-in trendline function; however, it is not the same kind of trendline you are talking about. 1# First step is to add a column to calculate the Collections...

Excel: Offset, column c, a500

column c, a500, target: I would do this using the change or selectionChange event of the workbook - an example is shown here for selectionchange Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 3 And Target.Row = 1000 And Cells(Target.Row, 1).Value...

Excel: Overhead - Exit Subs, vba programmers, vba functions

vba programmers, vba functions, exit location: Dave, Public Function ABC(r as Range) as Variant assign error value to function ABC = CvErr(xlErrRef) if r.cells.count 1 then exit Function code to perform the function purpose with single cell reference . . . assign value to function...

Excel: paste values, paste values, empty column

paste values, empty column, sh2: C. Mitchell Perhaps this Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet, i As Long Set sh1 = Worksheets( Sheet1 ) Set sh2 = Worksheets( Sheet2 ) For i = 4 To 20 change 20 to reflect when you want to stop looking for an empty column If Application.Count(sh2.Columns(i))...

Excel: Pasting cells and losing formatting, text cells, down arrow

text cells, down arrow, paste button: Michael, First, format the cells as number or general if they are formatted as text. Then you can type a 1 in a separate cell. Then copy it. Then select all the cells you want converted and do a Paste Special (click on the down arrow of the paste...

Excel: pivot table issue, pivot table, malini

pivot table, malini, space bar: Hi Malini, If I understand correctly, you want to have a pivot table, you want to prevent the user from modifying the pivot table and you want to do this without protecting the worksheet. Firstly, the user should not be messing around with a pivot table...

Excel: Pivot tables row fields, string variables, cell addresses

string variables, cell addresses, valid names: What kind of error are you getting, you did not say? And, are all the string variables valid names and not any of VBA s reserved names? Finally, variable names like yours, COULD also be cell addresses (there is a rf1 cell address), are never a good idea....

Excel: About:: Preparing Students' Mark-Sheet, mary lindsey, progress data

mary lindsey, progress data, excel spreadsheet: Hi Abhi, You can use an Excel spreadsheet to record progress reports by entering the student data on one spreadsheet and have it linked to another sheet, so that the big-sheet data and the progress data are simply replicas of each other. For example,...

Excel: About:- Preparing Students' Mark-Sheet, long term solution, ms access

long term solution, ms access, progress report: Hi Abhi, Its good to know that you are interested in automating the activities and save time. However, marksheets in schools can be large amounts of data, if I might suggest, you can try using MS Access as a tool. It will probably take a bit longer...

Excel: About: Preparing Students' Mark-Sheet, richard florida, florida usa

richard florida, florida usa, progress report: Abhi So you are going to end up with a big sheet which will be computer generated and a computer generated sheet for each student. The big sheet appears to be one that contains only the marks for all students who took a particular exam, and the computer...

Excel: Make profile in AutoCAD using excel sheet, japanese scholar, aiso

japanese scholar, aiso, construction field: As far as I can see the excel side of the macro should work fine in the later version, however the autocad bit is a mystery to me - I have no knowledge of autocad - for VBA to control an external program it needs to use the appropriate terms FOR that program...

Excel: =Proper vs number and letter capitalization, letters and numbers, capitalization

letters and numbers, capitalization, comma: This seems to do the trick: =IF(SUM(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1)),1,0)) 10,UPPER(A1),PROPER(A1)) What it does is count the number of instances where a digit 0-9 is NOT found in the string. If that equals to 10, there are no numbers in the...

Excel: PROTECTED WORKSHEETS & ALLOWABLE ACTIONS, spell check, thanks in advance

spell check, thanks in advance, scenarios: Will, If the sheet is hidden, then you can t select it. so just move that inside the IF statement like this: Sub spell_check_allsheets() Dim WS As Worksheet Application.ScreenUpdating = False For Each WS In ActiveWorkbook.Worksheets If lcase(WS.Name)...

Excel: Queries on Preparing Students' Mark-Sheet, progress report, queries

progress report, queries, report marks: abhi You would need to have the Marks Ledger on one Sheet and the Marks sheet on another then you could link the two so that any updates on the ledger would go to the Marks sheet. There are other options that would need to be done but I would need more...

Excel: Query::- Preparing Students' Mark-Sheet, excel spreadsheet, internet microsoft

excel spreadsheet, internet microsoft, template site: Abhi, I support your idea. I am sure that typing a grade one time into an Excel spreadsheet would then allow you to use that information many times in computing total grades or keeping records or whatever. There is no particular method associated with...

Excel: Query:: Preparing Students' Mark-Sheet, step guidance, direct email

step guidance, direct email, little ones: If I understand the question you need to split the big sheet into lots of little ones, which are sheets per pupil? If so, it SOUNDS as though you would be using mail merge - this would enable you to split the large sheet into the data you need for the little...

Excel: Query:::- Preparing Students' Mark-Sheet, result exam, example database

result exam, example database, exam result: Hi Abhi, If this is something that would be used in an entire school (involving multiple grades, classes, schoolyear -to keep historical data- and the like) I would advise to find someone to create a small database for this. Doing this in Excel is possible,...

Excel: Query::: Preparing Students' Mark-Sheet, vlookup formula, spelling errors

vlookup formula, spelling errors, help files: You shouldn t have to enter the data twice. You main big sheet is where you would enter the data row by row for each student. The first column A should have some sort of unique identifier, like a student code or the student s name (careful with names, spelling...

Excel: Question on Preparing Students' Mark-Sheet, absolute addresses, vlookup

absolute addresses, vlookup, mark sheets: Hi Abhi How are you? Do you have mark sheets for each subject and want to join all these marks into another file? Try first some simple examples 1. One file (AllMarks.xls) with 3 students and 2 subjects Number, Name, English, Maths in A1:C1 2....

Excel: reduce cell numbers by %, cell c1, cell a1

cell c1, cell a1, excel sheet: Yes you can. Let s assume your price list is in cells B1 through B40. In cell A1 you can put the percentage change you want. In this instance the new price list will be in cells C1 through C40. Put the increase or decrease in cell A1. Put the curser...

Excel: reduce cell numbers by percent, cell b1, cell c1

cell b1, cell c1, excel sheet: Rick: There are a couple of ways to go about your question. Here is one option: Option 1) Lets say your prices are in cells A1:A40. You could create a formula in cell B1 which looks like this: =A1*($C$1+1) Then you would copy that formula from...

Excel: reduce cell numbers by percent, price markups, 599cd

price markups, 599cd, excel sheet: See this tutorial on a href= https://599cd.com/tips/excel/2013-0305-price-markup-paste-value/?key=AllExperts PRICE MARKUPS IN EXCEL /a . Be sure to visit my web site and watch my B FREE /B 90-minute A HREF= http://599cd.com/Excel/AllExperts Microsoft...

Excel: reduce cell numbers by percent, cell c1, excel sheet

cell c1, excel sheet, cell numbers: Hi Rick, I can think of two ways. 1. Formula: =A1*(1+$C$1) (put the percentage in cell C1) 2. Copy paste - Put one plus the percentage in a separate cell like this: 10 percent becomes: 1.1 and copy that cell - Select the cells that need...

Excel: Regarding excel drop down for Time and Date., data validation, entering time

data validation, entering time, cell a2: On a new Excel workbook, select cells A1:A2, and apply the Date/Time format you want. In A1 enter =NOW() Select A2, and choose from menu Data, Data Validation, Allow: List, Source: =$A$1, OK. Whenever you want to enter the current Date/Time in cell...

Excel: Regarding Excel Protection., format cells, column c

format cells, column c, antony: 1) How to protect on column only By default, all cells on a sheet have the b [x] Locked /b paramater set, so if you protect the sheet, no cells would be editable. - Highlight all the columns you re using - Press CTRL-1 to open the Format Cells window...

Excel: Regarding excel sheet protection., format cells, empty columns

format cells, empty columns, excel sheet: Protecting: - Select the cells you want the user to edit - right-click and select format cells - On the protection tab uncheck Locked - Now protect your worksheet (review tab). Text swap: - Select names - Data, text to columns, delimited, space,...

Excel: Regarding excel sheet protection., format cells, b cells

format cells, b cells, hidden flag: Hi Ravi, 1. I did a quick test and the formula works fine even with a protected sheet. Are you trying to enter the formula on a locked cell of the protected sheet? 2. For your second question - If you have followed the steps 1-8 in the exact way, you...

Excel: Regarding Hyperlink., cell a2 type, visual basic for applications

cell a2 type, visual basic for applications, google: 1. If you don t know how to use Visual Basic for Applications (vba, or macros ), then any type of automation is impossible. If you do know vba, then In cell A2, type Close file Select A2. Choose from menu Insert, Hyperlink, Place in This Document,...

Excel: Regarding Protecting excel., cell b2, protecting excel

cell b2, protecting excel, format cells: 1. Select cell. From home menu, in the Cells section, click arrow to right of Format. To change the column width of the cell, choose column width. To change the row height of the cell, choose row height. 2. When you right-click on the sheet tab, and...

Excel: Rename sheet based on cell value, employee names, aidan

employee names, aidan, len: This VBA Dim mysht As Worksheet Set mysht = Worksheets( Master ) Dim n As Long n = 1 While Len(mysht.Cells(n, 1).Value) 0 Worksheets( Emp ).Copy After:=Worksheets( Master ) ActiveSheet.Name = mysht.Cells(n, 1).Value n = n + 1...

Excel: Repeat each row specific times, sheet names, index number

sheet names, index number, thanks in advance: You re welcome. Yes, I can write the macro, for a small fee of $50. This site allows me to charge a nominal fee for writing macros. If you still want to use me then I will need more details about the data (what column it is is, what column the output...

Excel: request for help, format cells, sheet tab

format cells, sheet tab, thanks in advance: Open new Excel workbook. Select A1 and right-click on it, choose Format Cells, Protection, and uncheck Locked , OK. In A1 enter 5 In B1 enter =A1*2 Right-click on the sheet tab at bottom, choose Protect Sheet, OK. Now you can send workbook...

Excel: Ribbon identifiers for Solver, button element, command element

button element, command element, office fluent ribbon: Hi Ed, Your question has turned out to be quite challenging. It appears that Microsoft has made it very difficult to ascertain IDs for add-in buttons. I just want you to know at this point that I m not giving up but it may take longer, especially as I...

Excel: Round VBA, negative numbers, formula 1

negative numbers, formula 1, len: Hi SK, Something like this: Sub RoundCells() Dim c As Range For Each c In Selection.Cells If c.HasFormula Then c.Formula = =ROUND( & Right(c.Formula, Len(c.Formula) - 1) & ,0) Else c.Formula =...

Excel: What if scenario for subtraction, monday tuesday wednesday, blank value

monday tuesday wednesday, blank value, q7: the formula doesn t need the sum - just the sumif - but your formula doesn t seem to do what you described? is it possible to see a sample workbook? My email is aidan.heritage@virgin.net - having said that, it sounds as though a sumif on it s own would do...

Excel: select data, delford, vba macro

delford, vba macro, macro code: Strangely enough, you don t really need a formula. You need a filter...an Excel filter. Click in your data (no empty rows or columns can be in your data), and hit Sort & Filter, and then hit the dropdown on your Vehicle column and choose the vehicle you...

Excel: Set Cell value based on new sheet, cell d5, subsidiary name

cell d5, subsidiary name, d7: My eyesight isn t what it once was (!) so I have problems with the images on this system - however, to set a value on the new sheet ActiveSheet.range( D7).value = whatever you want would be a method I would use - for the bonus calculation, are it would...

Excel: Shape name and macro, new question, aidan

new question, aidan, macros: Sorry for the delay in replying - I wasn t able to find the time to do the research needed until today - once I started, I ve found this is actually relatively straightforward - see http://www.excelvbamacros.com/2011/08/print-shape-name-and-text-you-clicked.html...

Excel: Simple Inventory Count Edit, target value, ogilvy

target value, ogilvy, r1: Edean, Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, r1 As Range If Target.Count 1 Then Exit Sub If Target.Column = 1 Then If Len(Trim(Target.Value)) 0 Then Set r = Range( A1 , Target.Offset(-1, 0)) If Application.CountIf(r,...

Excel: Simple Inventory count, target value, upc barcode

target value, upc barcode, inventory count: Mark, I think this change should allow that. Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, r1 As Range If Target.Count 1 Then Exit Sub If Target.row = 1 then exit sub If Target.Column = 1 Then If Len(Trim(Target.Value))...

Excel: Sorting multiple instances of serial numbers, serial number data, manageable format

serial number data, manageable format, home tab: Dave, If you just select all the data and then sort on column A, all the serial number data will be together. It is unclear how you determine what should be the first and last for a serial number. Excel uses a persistent sort, so sorting by serial number...

Excel: Spread Years Macro, company abc, abc company

company abc, abc company, blank rows: The only change I see is you originally said the years were in AC and now you say they are in AB so to accomodate that change, the macro would be: Sub abc() Dim rw As Long, cnt As Long, rw1 As Long, i As Long rw = 2 Do While Cells(rw, A ) ...

Excel: How to sum known columns with unknown rows using VBA, o12, n12

o12, n12, tim tim: Tim, the following worked for me. It works on the sheets in the workbook that contains the code. Sub abc() Dim sh As Worksheet, lastrow As Long, i As Long For Each sh In ThisWorkbook.Worksheets lastrow = sh.Cells(sh.Rows.Count, N ).End(xlUp).Row...

Excel: Sum macro, cell reference, connally

cell reference, connally, search reference: QUESTION: I use the following macro: =sumif( LABOR DATA ENTRY !B4:B3000, Connally , LABOR DATA ENTRY !M4:M3000) Try =sumif( LABOR DATA ENTRY !B4:B3000, , LABOR DATA ENTRY !C2 , LABOR DATA ENTRY !M4:M3000) Just as you referenced the other cells. I...

Excel: Swapping the excel text, cell a1, antony

cell a1, antony, quick response: FIND( ,A1) returns the location within the text in cell A1 where the first blank occurs. So for David Anthony it d return 6, since the blank is the 6th character in this text. MID(A1,7,200) returns the text in A1 starting with the 7th character (one...

Excel: tabular to frequency, column c, pivot table

column c, pivot table, pivot tables: Paul, If you want to show even the outliers, then you would select both columns and create a pivot table. You would have the company in the row field and the second column, the number, in both the column field and as the data. In the data, you would...

Excel: Tank Formula with different increment factors, volume a1, vertical tank

volume a1, vertical tank, section thanks: You nest the 5 section formulas inside each other starting at the HIGHEST section of the tank. In INCHES only: Top = 160 inches+ 2nd Starts at 120 inches 3rd starts at 80 inches 4th starts at 40 inches 5th is the remaining 0+ inches =IF(D1 160,...

Excel: time sheet forumla, cell a2 type, chip pearson

cell a2 type, chip pearson, cell a1: You first have to know that Excel requires specific entry, and that Excel reads the value of 1 as one DAY. In cell A1, type: 8 a and hit Enter. It should then read 8:00 AM. In cell A2, type: 5 p and hit Enter. It should then read 5:00 PM....

Excel: Tricky array formula, array formula, array function

array formula, array function, california office: Sorry - still a little confused. You originally said you wanted to count the number of COMPANIES, yet later on you said ...we d count New York because it purchased in both 2012 AND at least one prior year... which seems to imply you re wanting to count LOCATIONS....

Excel: Trim numbers after decimal, 4 digits, numeric value

4 digits, numeric value, decimal places: Hi Steve, What you want cannot be done via cell number formatting. Thus there is no way to round the value DOWN to two decimal places and keep the underlying cell value (e.g., 999.1268) in column B. However, you can use the ROUNDDOWN worksheet formula...

Excel: Unhide sheet only Selected option from drop down list, sheet names, quick macro

sheet names, quick macro, drop down list: Hi Shivendra, This can be handled by using a macro. You could write a quick macro which can work on Case Select Case = M4A hide sheet 1, sheet 2 case = M4x hide sheet 3, sheet 4 etc. If you are ok with this, please send me all the drop down...

Excel: User Form in Excel, mole fraction, gas chromatograph

mole fraction, gas chromatograph, mass fraction: Hi Stephen, Firstly I want to apologize for my slow response. I saw your question last week but didn t have time then to do it, and I forgot that I had a question pending until I got notification from AllExperts that it was overdue. It would take quite...

Excel: using sumif statement, o clock, column c

o clock, column c, time range: Grace - Because you are searching for a time range (i.e. 13:00 through 13:59), you will need to use the SUMIFS() function. It allows you to search for multiple criteria. =SUMIFS(SUM_RANGE,CRITERIA_RANGE1,CRITERIA1,CRITERIA_RANGE2,CRITERIA2...) SUM_RANGE...

Excel: VBA coding, sheet1, sht

sheet1, sht, eyesight: I always have problems with the screen shots - not your fault, my eyesight I think - but it makes it hard to see what it is you are having problems with - could you send me an example file and I can then code it for you - my email is aidan.heritage@virgin.ne...

Excel: VBA Sum/Count, m51, true need

m51, true need, ogilvy: Miguel, Sub abc() Dim r as Range, tot as double Dim r1 as Range, cell as Range set r1 = Range( M54 ) set r = Range( M4:M51 ) tot = 0 for each cell in r if cell.value = r1.value then tot = tot + cell.offset(0,1) end if Next you don...

Excel: VBA workbook syntax for zooming - Excel 2007, target cells, vba code

target cells, vba code, ogilvy: Jake, You are in the correct module (thisworkbook) but using the wrong workbook level event. The equivalent event is the SheetSelectionChange Event: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Target.Cells.Count...

Excel: Vlookup and sum answers with same data, vlookup formula, quarterly production

vlookup formula, quarterly production, work sheets: Example: the text Prod.A is in cells B2, B6, and D2. C2 has the value 5, C6 is 10, and E2 is 15. You want a formula to sum up the values to the right of each Prod.A, which in this case would return 30. Use the formula =SUM(SUMIF(OFFSET(B2:B6,0,{0,2}),...

Excel: Automate update Excel spreadsheet at specific time of day, lee chilcoat, excel spreadsheet

lee chilcoat, excel spreadsheet, file excel: The Excel file which contains the formulas which need updating must be open, and must be of type .xlsm (not .xlsx). If you (or the programmer) has that file open, then he must press Alt/F11 (taking you to the programming environment), then doubleclick on...

Excel: Calculating and summing military time, chip pearson, sum function

chip pearson, sum function, military time: In b1, you need: 8.00 Because, in Excel, a 1 is 1 day. I can t figure out why it shows 16, but if you change it to a NUMBER format instead of time, you ll get 1.67, which is 1.67 DAYS. To convert it to hours, you must multiply by 24. Chip Pearson...

Excel: Calendar, cell b3, time periods

cell b3, time periods, description column: Hello Jessica, Thanks for a detailed question. Looks like you have done a lot of work already. Can you send me the file you are working on with sample data. That will help me understand better rather than reading the text above. You could mail me the...

Excel: Comments in Cells is too small, macro code, tahoma

macro code, tahoma, milestone: Here is a sample macro that sets the font size, color and name of all comments residing in the selected cell or cells. Sub SetCommentsProperties() Dim Cell As Range For Each Cell In Selection If Not Cell.Comment Is Nothing Then With Cell.Comment.Shape.TextFrame.Characters.Font...

Excel: Concatenate List, school campuses, sh2

school campuses, sh2, rw 1: Enrique Garcia The macro assumes the data you show is on a sheet named Sheet1 with the first 111 in cell A2 It writes the data to a sheet named Sheet2 and starts in row 2 and writes in columns A and B. It writes the headers in row 1 Test this in...

Excel: Conditional formatting, blank cells, conditional formatting

blank cells, conditional formatting, formating: RAVI I cannot be much help without knowing how you did your conditional formating and how you did your protection. I suggest that you look back at your criteria for your formating and make sure that your condition does not include blank cells. And do...

Excel: Conditional If formula, cell d1, voice international

cell d1, voice international, column c: Usman An if formula will work perfectly and it would look like this. Assume your first column of data is A the second column is B the third column is C so A1 would contain BB B1 would contain UK C1 would contain 1 The formula you would put...

Excel: Copy three sheets to a new file, lct, three sheets

lct, three sheets, sanjay: If you want to copy sheets in sets of three (assuming the file contains a multiple of three sheets, so 3, 6, 9, 12, ... then this is the code you need: Sub CopyAndSaveSheets() Dim lCt As Long For lCt = 1 To ThisWorkbook.Worksheets.Count Step 3 ...

Excel: Copying a column value to a string variable, variable string, string result

variable string, string result, output values: Hi Shrunga, I can suggest concatenate function or just the simple & function to handle this but I m sure you have thought of it. Eg. B1 could have a formula =A1&A2&A3&A4. In case there are more combinations possible in each column, then we might have...

Excel: To count repeated entries in a given month, h12, a12

h12, a12, daily basis: I only created a table out to column BT, you ll need to adjust this formula out to cover all the columns you re using. The formula in B12 would be: =SUMPRODUCT(($B$2:$BT$4=B$11)*(TEXT($B$1:$BT$1, MMMM )=$B$10)*($A$2:$A$4=$A12)) After you adjust...

Excel: Counting, cell a2, cell a1

cell a2, cell a1, ct application: Maybe something like this would work: right-click the sheet tab, select View Code, put this in. Cell A2 is keeping track of the # of times A1 is a 1 whenever the number crunching is done. Public ct As Long Private Sub Worksheet_Calculate() Worksheet_Change...

Excel: To create a MACRO for excel, direct email, public holidays

direct email, public holidays, public holiday: It s not clear why this has to be a macro rather than simply formulas =-= unless the only indication of public holiday is the red shading - I m happy to generate a macro for you but I find it difficult to read the information on pictures on this site (due...

Excel: To create a MACRO program, link windows, macro program

link windows, macro program, exact solution: Hi Permal, Thanks for sending your question on my way... Please attach your file somewhere in the web for getting exact solution then attach the file in any of the below mentioned websites and send me the link. Windows Live Skydrive:http://skydrive.live.com...

Excel: creating a macro, public holidays, richard florida

public holidays, richard florida, florida usa: Permal I don t know if you sent a copy of your sheet, but if you did, I don t see it or any other information that will allow me to give you an answer. I believe, from what you have said, that your counted hours can be done with a formula and will not...

Excel: Creating a string of IF Statements, question pool, vlookup formula

question pool, vlookup formula, vlookup function: It may help you to follow this from a sample file - I have an example at http://www.aidanheritage.byethost3.com/excel/ExampleVlookup.xls but what may possibly be easier is to send me a sample file (aidan.heritage@virgin.net) and I can update that for you....

Excel: Excel 2003 VBAscript, sponsor id, member numbers

sponsor id, member numbers, jane 5: Ben, I have attached an image where I copied your data from your posting into columns A:C. In D2 I put my formula and drag filled it down. It worked perfectly (as expected). #N/A indicates the lookup value (value in C2) is not found in column A. ...

Excel: Excel 2010 Automatic Cursor Row Highlighting, sheet tab, target

sheet tab, target, a1: 2) Select ALL the cells, and, assuming A1 is the active cell, use Home/Conditional Formatting/New Rule, select Use a formula to determine which cells to format , enter this formula: =cell( row )=Row(A1) then click Format, Fill tab, select your color...

Excel: Excel 2013 multiple "if" statements with text, column c, good evening

column c, good evening, thanks in advance: something like this in C1, perhaps: =IF(A1= CHOICE 1 ,IF(B1= 2ND CHOICE , Answer1 ,IF(B1= 3RD CHOICE , Answer2 )), A1 Not Choice 1 ) If this doesn t help, send a sample wb to me at bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what needs...

Excel: Excel, excel, match

excel, match, index: VLOOKUP only works well for very structured tables. You d be better off with MATCH and INDEX. See: a href= http://599cd.com/tips/excel/match-index/?key=AllExperts MATCH and INDEX FUNCTIONS /a Be sure to visit my web site and watch my B FREE /B...

Excel: Excel auto Timesheet, keyboard shortcuts, time sheet

keyboard shortcuts, time sheet, maco: Hello Sainath, Can you mail me a format of your file, I have one idea that might work, but it s hard to explain it, I can build it into your format and send it to you. Please mail to gulshanrajpurswani@gmail.com, please mention AllExperts in the subject....

Excel: Excel button macro- transfer data, sh2, sh1

sh2, sh1, allexperts: Private Sub CommandButton1_Click() dim sh1 as worksheet, sh2 as worksheet Dim r1 as Range, rw as Long, bk as workbook Dim s as String, bk as workbook Set sh1 = Worksheets( Sheet1 ) s = M:Folder1SubFolder1Master.xls = change s1 = master.xls...

Excel: Excel -- How do delete specific values/rows, down arrow, home tab

down arrow, home tab, command button: Aska, Your data is a bit jumbled, but I will assume that column A only holds the numbers or a number with a hyphen on the end. I would insert a new column A to use as a helper column. Assuming your data starts in row 1, then in A1 (after inserting the...

Excel: Excel formula, paul hancock, array formula

paul hancock, array formula, indirect address: Paul, Assuming Cat is in A1, then in B2 I put in this formula =IF(C2 2, ,INDIRECT(ADDRESS(1,IF(COUNTIF(D2:O2,2)=COUNT(D2:O2),4,LARGE(IF((E2:O2=2)*(D2:N2 2),COLUMN(E2:O2)),1))))) doing Ctrl+shift+enter rather than just enter to commit the formula...

Excel: Excel Formula for the following, excel formula, city names

excel formula, city names, pin code: Hi Jackson, You can try using the text to column function in excel. This is assuming that your data will always be separated by a , Goto data - text to columns, select the delimited option and delimiter as comma , that should do the job. Hope this...

Excel: Excel formula query, spreadsheet work, link windows

spreadsheet work, link windows, excel formula: Hi Chris, Thanks for sending your question on my way... Please attach your file somewhere in the web for getting exact solution then attach the file in any of the below mentioned websites and send me the link. Windows Live Skydrive: http://skydrive.live.com...

Excel: Excel formula question, spreadsheet work, question pool

spreadsheet work, question pool, blank cell: That s no problem In column C =IF(D1= Y , ,if(B1= Y ,A1,IF(B1= N ,70,0))) which would make D1 override B1 In column J =IF(D1= Y ,A1,IF(D1= N ,70,0)) I wasn t sure if you needed the N bit in column J,but hopefully you can modify if needs...

Excel: Excel Outside The Box, excel visual basic, visual basic for applications

excel visual basic, visual basic for applications, excel user: If you take your time, you ll probably have no problem (if you do, contact me!) On another note(s): I m leading an online 2-evening/3-hr each class in VBA. Details here: The class is held June 4 & 6 from 8-11PM est.If you re interested in taking a live,...

Excel: excel problem, cell d1, matching number

cell d1, matching number, column c: Tom: Your column D is currently formatted as a plain number (a date in Excel is a count of the number of days between 1/1/1900 and the date entered). Try selecting column D and formatting that column as a Date. Once you do that it will show the results...

Excel: Excel if statement, spreadsheet setup, s m l xl

spreadsheet setup, s m l xl, value error: Bret If your formula is typed as follows it will work. =IF(OR(D2= YXS ,D2= YS ,D2= YM ,D2= YL ,D2= YXL ),B37,0) As you can see you just forgot to tell excel that D2 was the cell you were testing in each of the various choices. The same type of...

Excel: Excel Sum Help, best guess, countif

best guess, countif, richard florida: Scott This is what my original formula will do if the number is 500 it will add 250 If the number is 125 it will add 125 If the number is 642 it will add 250 If the number is 25 it will add 25 etc, etc. As you see it adds all of the numbers that...

Excel: Excel VBA loop through columns in range and copy, excel vba, cell b1

excel vba, cell b1, spath: Rob, In the place where the code has Set bk1 = ThisWorkbook sPath = bk1.Path If Right(sPath, 1) Then sPath = sPath & you would add a line Set bk1 = ThisWorkbook sPath = bk1.Path If Right(sPath, 1) Then sPath = sPath & ...

Excel: how to make the excell sheet row flicking, colorindex, different color

colorindex, different color, fr: OK, you asked for it. Change Sheets( sheet1 ).Range( A1:IV1 ) to be the desired range you want to flash. Change the 1 at the end of the next line to make a more or less delayed flash NextFlash = Now + TimeSerial(0, 0, 1) Change 3 in this line to...

Excel: excell spreadsheets, excell spreadsheets, setup tab

excell spreadsheets, setup tab, video tutorial: Excel doesn t display footnotes like Word does. If you INSERT COMMENTs in your cells, however, you can go into Page Setup and tell Excel to print them (File Print Page Setup Page Setup Tab). Be sure to visit my web site and watch my B FREE /B ...

Excel: Exel, nested if then, excel, if

excel, if, nested if: You want a a href= https://599cd.com/tips/excel/complex-if-example/?key=AllExperts NESTED IF FUNCTION /a with some a href= https://599cd.com/tips/excel/count-between-two-dates/?key=Allexperts AND /a functions in there. IF(AND(A1 0,A1 =.25),10,IF(AND(A1...

Excel: Formula that calulates hours and minutes worked, cell c1, format cells

cell c1, format cells, zero seconds: On a blank sheet, enter into cells A1:A2 12/12/2012 7:15:00 AM 12/13/2012 11:30:00 AM Right-click on cell C1 and choose Format Cells, Number, Category: Time, Type: 37:30:55, OK. Enter into cell C1 =(A2-A1) C1 will display 28:15:00, meaning...

Excel: Help with consolidating data from 2 worksheets, assessment dates, sort feature

assessment dates, sort feature, due diligence: Eric Here is how I would handle it. To keep it simple, I believe you can use a simple sort feature to identify the products that need the due dili or the assessment or both. How about this Create a third sheet and copy the data from sheet1 to the...

Excel: hyperlink, insert hyperlink, comupter

insert hyperlink, comupter, richard florida: Vio If you have an image on your comupter that you want to link you can first select the cell that you would like to activate the link from, then right click, then select hyperlink from the drop down list, then select the image from the insert hyperlink...

Excel: importing email data into excel, export facility, sounding board

export facility, sounding board, vba: I ve done some research into RoundCube - which is really where the problem lies - and it LOOKS like this uses a MySQL database to store the data - if so, you should be able to write SQL to interogate this - using excel as the front end. HOWEVER, I have no...

Excel: Inserting lines every 5 columns, redwood street, c columns

redwood street, c columns, mytext: Try this instead: Option Explicit Sub InsertRows() Dim LC As Long, LR As Long, Rw As Long, c As Long Dim MyText As Variant Application.ScreenUpdating = False LR = Range( A & Rows.Count).End(xlUp).Row LC = Cells(1, Columns.Count).End(xlToLeft).Column...

Excel: Inserting multiple pictures, spath, sname

spath, sname, c cell: George, I fixed the path (used the same as you) Sub ProcessFiles() Dim sPath As String, s As String, r As Range Dim shp As ShapeRange Dim c As Range, cell As Range, sname As String Dim p As Picture, diffwidth As Double, diffHeight As Double ...

Excel: Koppeling Excel - Internet tabel, currency rates, van windows

currency rates, van windows, koppeling: Hallo Renee, Daar is weinig tegen te doen, je hebt nou eenmaal geen controle over hoe externe partijen zaken op hun website plaatsen, net zomin als je controle hebt over hoe anderen hun Windows c.q. Excel instellen. Wat je kunt doen is de web query...

Excel: Linking Excel sheets within a workbook, vassar rd, honey ct

vassar rd, honey ct, west condominium: Kristina, I have sent it back to you with my suggested solution. Note that while the cells in the copy of the master sheet will update, if you change a value on which the sheet is sorted, you will have to resort that sheet - the sheet won t sort automatically....

Excel: Linking pages, customer data base, code vba

customer data base, code vba, database program: It would be MUCH easier to maintain if all you had was the master sheet and the ability to sort by any column you wish, perhaps by double-clicking the column header. If that would work for you, then I can give you the code (VBA) that would easily do that...

Excel: Looking for a starting row using VBA - Follow up, cell b2, empty cells

cell b2, empty cells, blank row: Tim, rng30 never changes what cell it refers to. It always refers to Cell B2 of the Sales Master Data sheet. So your IF test will only be passed for the sheet that has a name matching the value in B2. I would suggest something like this where you...

Excel: Macro for deleting duplicate rows, gary taylor, thanks in advance

gary taylor, thanks in advance, macros: The macro LOOKS fine, but I d suggest getting calculation turned to manual before it did it - alternatively, do this without a macro - which tends to be the way I do it - use a formula in a helper column which has a decreasing count if =countif(e1:$E$72700,e1)...

Excel: Macro to Skip Items not Found., ogilvy, thanks in advance

ogilvy, thanks in advance, a1: Jayendran. R, Sub FindJay() Dim sh As Worksheet Dim r As Range Set sh = ActiveSheet Set r = sh.Cells.Find(What:= Jay , _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ ...

Excel: Macro- transfer data, sh1, string s1

sh1, string s1, test2: Gatlin, this has been compiled without error. There were some typos in the code. Private Sub CommandButton1_Click() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, rw As Long, bk As Workbook Dim s As String, s1 As String Set sh1...

Excel: macros, s box, saveas

s box, saveas, spell check: This can be a whole host of issues so it will be time consuming to cover them all. Your line 12 line of code, which is different than what I sent you, suggests to me that you are, or VBA, is trying to un-protect ALL workbooks, whether open and visible or...

Excel: Match and copy, ws2, ws1

ws2, ws1, book2: Hi Derrik, I believe this code does what you want. Check it out and let me know. Note that I had to switch the two loops so that now the worksheet 1 loop is the inner loop. Sub MatchAndCopyData() Dim WB1name As String Dim WB2name As String...

Excel: Multiple Double Vlookup, index function, vlookup

index function, vlookup, input value: Hi Manish, I wasn t sure where you enter the input value. Based on my understanding, I have prepared the following solution: Cells C1 to F1 contain values A,B,C,D Cells A1 to A12 contain values 1 to 11 Cells B2 to F12 contain the table you posted...

Excel: Neeed some help converting this macro to run as a as a shortcut key, target cells, rowheight

target cells, rowheight, autofit: Dereck, no, your original code does not process a whole column or the whole sheet. It is fired/runs for any cell that you change, but if that cell is not merged with wraptext then it would do nothing. But each time it runs, it does not process the whole...

Excel: Password to edit, unwanted changes, design flaw

unwanted changes, design flaw, 3 things: This is a known design flaw in Excel. The way to hide a sheet so a user cannot see it is to hide using VBA using the xlSheetVeryHidden flag. This means the ONLY way to unhide the sheet again is to use VBA, it cannot be done by hand. Sheets(...

Excel: =Proper vs number and letter capitalization, doctors without borders, proper case

doctors without borders, proper case, lct: Hi Randy, Hmm. I see no formula way to do that. This used defined function appears to do the trick: (paste into a normal code module in your workbook, save workbook as a file type that holds macro s) Option Explicit Public Function ProperOrUpper(Value...

Excel: Protect ALL sheets macro that allows column and row formatting, column width, pwd

column width, pwd, ogilvy: Eric, there isn t a setting to just allow the column width and row height to be set. These settings allow the user to change the row height and column width, but it also allows the user to format the column. Sub ProtectAll() Dim wSheet...

Excel: range, macro, false selection, irow

false selection, irow, location range: Sub doCopying() Dim wksFrom As Worksheet, wksTo As Worksheet Dim copyFrom As Range, pasteTo As Range Dim iRow As Long Set wksFrom = ThisWorkbook.Sheets( Sheet4 ) Set wksTo = ThisWorkbook.Sheets( Sheet3 ) For iRow = 0 To 5 ...

Excel: Reference Sheet Linkages, wajid, model control

wajid, model control, data model: Use defined names. For example, name the range B2:D2 as Range1 (or something more meaningful). in the Data Model, use =SUM( [Control File]Weekly Data !Range1). If rows have been inserted in the control file, the range1 name will still reference the right cells....

Excel: Regarding Excel, macro program, concrete example

macro program, concrete example, ravi: 1. If you have a question that can be demonstrated with a clear, simple, concrete example, then you can ask it here. 2. Hold down Alt key and then hit F8 key. The Macro pop-up will appear. Select the macro you want to delete, then click the DELETE button...

Excel: Retain Format AND Formulas removing hyperlink, excel macros, cell style

excel macros, cell style, background colors: This routine will clear the hyperlinks and keep the formatting: Sub ClearHyp() Dim thing As Range, Unused As Range On Error Resume Next find unused cell to use to hold format: Set Unused = Range( A1 ).Offset(ActiveSheet.UsedRange.Rows.Count)...

Excel: Retrieve Data from a Cell in a Workbook, database approach, vlookup

database approach, vlookup, question thanks: You re welcome. And, a database approach will work if you want to go the trouble now of having to create the wheel again. You ll need a macro approach if you decide to still use EXCEL. And finally learning VBA is certainly doable but you ll be spending a...

Excel: Returning a result when same result is in two ranges, tom apple, joe kyle

tom apple, joe kyle, joe hughes: Sure. Use the a href= http://599cd.com/tips/excel/match-index/?key=AllExperts MATCH function /a . If the value exists in the second list, the MATCH will return it s position. Be sure to visit my web site and watch my B FREE /B 90-minute A HREF= http://599cd.com/Excel/AllExperts...

Excel: put two rows in one, initial approach, two rows

initial approach, two rows, ogilvy: Panagiotis, A2: 119 B2: 10 C2: 7 D2: 5 A3: 119 B3: 9 C3: 8 D3: 4 then in E2 you could put a formula E2: =if($A3 $A2,na(),B3) then select and drag fill to G2 Now select E2:G2 and drag fill down your column. That would be my...

Excel: From rows and columns to one column, source sheet, indirect address

source sheet, indirect address, x365: Assume you have numbers or daa in cells A1:X365 on Sheet4 and you want these all in column 1 of another sheet. first you need to be able to have the columns references count from 1 to 24, then repeat. You can do that with this formula =MOD(ROW()-1,24)+1...

Excel: Smarter way to paste special, recalculation, random numbers

recalculation, random numbers, copy paste: If it s 10,000 values all at once, then you can copy the entire column and paste special all at once. Otherwise, you would need to use a macro to populate the random numbers - which would then be stored as values rather than the rand() function. The only...

Excel: Sort with VBA, sheet cells, blank cells

sheet cells, blank cells, blank rows: Doug, what you describe would be cells(1, A ).End(xldown).row mine is equivalent to going to the bottom of the spread sheet (row 65536 in excel 2003 and earlier, row 1048576 in excel 2007 to present) in column A. then hitting the end key followed...

Excel: if statement, spreadsheet setup, s m l xl

spreadsheet setup, s m l xl, value error: this IF(OR(D2= YXS ,D2= YS ,D2= YM ,D2= YL ,D2= YXL ),B37),IF(OR(D2= S ,D2= M ,D2= L ,D2= XL ),B38,0),IF(D2= 2XL ,B39),IF(D2= 3XL ,B40) would be this IF(OR(D2= YXS ,D2= YS ,D2= YM ,D2= YL ,D2= YXL ),B37,0)+IF(OR(D2= S ,D2= M ,D2= L ,D2= XL ),B38,0),IF(D2=...

Excel: Subscript out of range error, run time error, sheet names

run time error, sheet names, p33: First of all, to my knowledge, there should no need to convert since VBA has essentially remained unchanged. Do the files still exist? Are the file names valid? Do the sheet names still exist? Try using the full path for Exp_Func_p33_p3c.xls and Clean...

Excel: sum cells, error sum, inconsistency

error sum, inconsistency, mileage: There s nothing wrong with your first formula. It works for me. The only reason I can think it might error is if there is an error in one of those two sum cells on one of the sheets. Check them all. The second formula could simplify down to: =LOOKUP(H81,...

Excel: Sum help, excel function, best guess

excel function, best guess, dollar amounts: Select coluln K, use COnditional formatting, select Use a formula to determine which cells to format , enter this formula (assumes K1 is the active cell): =AND(K1 30000,K1 50000) click FOrmat, select a yellow fill Then do another New Rule for conditional...

Excel: Time calculation, time calculation, return answer

time calculation, return answer, departure time: Jorge: Please try the following formula (assuming the cell with the time to be modified is in A1): =IF(OR(MOD(A1,1) 17/24,MOD(A1,1) 8/24),IF(MOD(A1,1) 17/24,DATE(YEAR(A1),MONTH(A1),DAY(A1))+17/24,DATE(YEAR(A1),MONTH(A1),DAY(A1-1))+17/24),A1) If this...

Excel: transposing a dynamic range, excel visual basic, visual basic for applications

excel visual basic, visual basic for applications, dynamic ranges: You would need to have the transposed array on the worksheet somewhere, like array-entered =TRANSPOSE(D5:AD5), for example, then name that range and use IT in the listbox. In order to display them in ascending order you d either need VBA to populate the listbox,...

Excel: Trying to extract data from two workbooks into one new workbook, time clock system, job costing system

time clock system, job costing system, question pool: It s always hard to work from pictures (my advancing years I think) but I m happy to help you with this - I THINK it would probably need VBA to work properly - do the files you work with have standard names, or are they always in a known location? My direct...

Excel: Using Excel to create a time schedule, cell a2, time length

cell a2, time length, ogilvy: in B1 put your time length as an excel time (as an example, so it displays as 0:05:00 which would be 5 minutes in A1 put your start time in A2 put in =A1+$B$1 then drag fill cell A2 down the column for as far as you need to go. You can then change...

Excel: VBA: Copy used range, copy columns, ogilvy

copy columns, ogilvy, cells: Doug, This should do what the original macro did, but only copy columns A:R for the specified rows Sub copyData() Dim r as Range, lastrow as Long With Worksheets( Sheet1 ) Set r = .Range(.Cells(1, 1), _ .Cells(.Rows.Count, 1).End(xlUp)) _ .EntireRow.Cells...

Excel: VBA Macros, k56, wks

k56, wks, interior color: Sub greenShade() Dim oneRow As Range, bigRange As Range Dim wks As Worksheet Set wks = ThisWorkbook.Sheets(1) Set bigRange = wks.Range( A2:K56 ) bigRange.Interior.Color = xlNone For Each oneRow In bigRange.Rows If wks.Range(...

Excel: VBA to rename short filenames to long, de bruin, copy folder

de bruin, copy folder, names john: John, VBA can read the files in a directory and it can rename files in a directory - but it would need to know the new name for the file. Possibly it would be easier to copy the files using VBA. Ron de Bruin has already written some code that based...

Excel: VBA Solution, name0, case statement

name0, case statement, string name: Jesse, excel doesn t support what you describe as you describe it. I will show you a workaround. Then I will show you an approach that demonstrates how VBA is designed to do something similar to what you want. If you want to go with separate variables,...

Excel: VBA: VLookUp issue, blank cells, ogilvy

blank cells, ogilvy, zeros: Doug, To illustrate what you are observing: Select two empty/blank cells - say A1 and B1 in A1 put in =B1 A1 will display a zero instead of appearing blank. So your formula is exhibiting this same behavior. Now in A1 you could use a...

Excel: weighting rankings, jennifer jennifer, dropbox

jennifer jennifer, dropbox, weighted average: Jennifer: What you are talking about is a weighted average. I have uploaded a sample spreadsheet here: https://www.dropbox.com/s/mae3407wku91wby/ForJennifer.xlsx Essentially you need to assign a weight to each criteria, and the total of your weights...

Excel: writing a formula into VBA, vlookup function, christopher mitchell

vlookup function, christopher mitchell, legitimate function: Christopher Mitchell == added usage example at the bottom === first, your function isn t really a legitimate function so it leaves the request open to interpretation. I would think what you are actually asking is this: =if(isnumber(match(AM,AG1:AK1,0)),Vlookup(A2,$AS2:$AT200,2,FAlse),...

Excel: zeros in excel cells only when data is entered., richard florida, zero value

richard florida, zero value, drop down list: Hey Pauline I am not sure what you mean by when data is entered in the cells it corresponds with , so I will give you a couple of answers. one is --You can use the excel advanced options and eliminate zeroes in all cells. To do that you go to the toolbar...

Excel: Adding elapsed time to date/time column, decimal fraction, question pool

decimal fraction, question pool, time column: I would suggest NOT using the time function - instead, as you have values in minutes simply use =YourValue/60/24 (or =YourValue/1440) this will convert the minutes to the decimal fraction that excel expects time to be in - to excel, the decimal fraction...

Excel: Autofilter using VBA, application range, location level

application range, location level, acceptable range: Hi Stephen, Without analysing your macro, one way to speed things up is to turn off screenupdating and calcualtion at the start of the code and turn it back on afterwards: Applictaion.ScreenUpdating = False Application.Calculation = xlCalculationManual...

Excel: Automatically filling text, based on a number, vlookup, department numbers

vlookup, department numbers, department names: Easy peasy. Use a VLOOKUP. It s explained in an article I wrote ages ago: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm Make sure your lookup range (that other spreadsheet with departments and numbers on it) have the name...

Excel: Bank Holidays, list of holidays, bank holidays

list of holidays, bank holidays, time and a half: David, Hard to tell without knowing anything about your data and your layout. If you are using the weekday function to discover a date where pay is increase then you could modify it to also consider a list of bank holidays. Say B9 holds the date...

Excel: Calculating the average date from a list of dates with different years, text strings, text string

text strings, text string, miranda: MONTH(B2)& / &DAY(B2) will give you a TEXT string, like 8/12, not really a number, so when you average all the text strings you do get #DIV/0! Try this (works:) =DATE(2000,MONTH(B2),DAY(B2)) and fill down, and take the average. The 2000 is totally an...

Excel: Check macro security via macro, vba procedure, center settings

vba procedure, center settings, open macro: Best way is to have a Private Sub Workbook_Open() procedure in your ThisWorkbook module in the Visual Basic Editor. If macros are enabled, this procedure will run; otherwise it won t. So you could for example have a file that has only one visible sheet....

Excel: Compare 2 spreadsheets and highlight differences, conditional formatting, mycell

conditional formatting, mycell, comparison sake: I can help (thanks for finding me!) but it d help ME if I could see an actual workbook - please send a sample to me at bobumlas@hotmail.com, use subject of AllExpersQ and repeat what needs doing. Also, note that there ll be another User conference Sept...

Excel: conditional formating, conditional formatting, due dates

conditional formatting, due dates, spreadsheet: Sorry for the late reply, have been unavailable to answer. Use a simple formula using Conditional Formatting. Here is an example of one way of doing it. =$B$1+30 $A$1 Assuming today s date is in A1 and the training date is in B1. Hope this helps ...

Excel: Copy a row based on a cell in a coloum, sheet names, excel sheet

sheet names, excel sheet, reply: Sorry for the late reply, I have been unavailable to respond until now. I could write the macro but I don t have the time to do it for free. If you still want me to write it for you then my estimated fee will be $50 and I will need more detailed info if...

Excel: copy strings, irow, b13

irow, b13, e7: Your method of copying looks fine BUT if you try to do this outside of a macro you will find that (as far as I know) it s not going to work as excel isn t happy about copying and pasting non contiguous ranges. However, you don t have to copy and paste within...

Excel: How to create monthly attendence sheet in excel for students and update it using barcode reader with barcode ID cards, attendance sheet, barcode number

attendance sheet, barcode number, column c: There is no special barcode functionality built into Excel. If you were to scan something with a bar code reader, it would simply type that code into the selected cell. The Scanner essentially just types things for you. Now, if you had a REF sheet where...

Excel: Data aliening with criteria, mail id, origin and destination

mail id, origin and destination, grate: Rakesh, If you want to put in the same three rows just adjusted for ORI and DEST combinations, then a macro could be used to do that. Another way would be to generate all the added rows using a formula and insert them at the top - then sort your data....

Excel: Getting Data into Consecutive Cells, empty cells, different fashion

empty cells, different fashion, maximum rows: Robert, ========== revised response ============ After sending my response I noticed your subject said that you wanted data in consecutive cells and I missed that fact in your problem statement and misinterpreted your example. My bad. I have revised...

Excel: Data, Filter, Auto Filter, Autofilter

Autofilter: Art - Here is one answer to your request. I am sure there are other answers that can be done with macros or VBA; I went for the most direct and easiest to manipulate. The problem was filtering out only those companies with a total over 100. The problems...

Excel: Data Validation, data validation, office purpose

data validation, office purpose, format parameter: Manikandan when you protected the sheet you should have been given an list of parameters you could specify. Usuually allowing to select locked or unlocked cells are checked and the other choices are unchecked. Some of those other choices pertain to...

Excel: Delay a macro, timer event, data updates

timer event, data updates, excel sheet: There are various ways of pausing a macro - you could simply insert a message box asking the user to press OK once completed - this would suspend the macro indefinitely until that action happened. You could use a while...wend loop to check for a cell changing...

Excel: Delete excel temp files code, command line switch, empty folders

command line switch, empty folders, temporaryfolder: Best way to tell is to try it and see if it deletes temp files. This is too long for me to test, and since I don t have my own version, I can t advise if there s a better way. Sorry I can y be of more help here. It s also pretty easy to delete Temp files...

Excel: difficulty with font in textbox, default printer, honest answer

default printer, honest answer, fontsize: Hi Al, I see why you d want to use a textbox indeed. Since I am a VBA programmer, I would create a userform the users could call up using a button, which would contain a textbox to edit the comment. The userform would not help much for viewing lots of...

Excel: Directory, macro code, auto insert

macro code, auto insert, simple solution: Hi Raja, I haven t yet upgraded from Excel 2007 to 2010, but unless 2010 has some new capability to customize the default footers there is no way to do this without a VBA macro. A macro is a pretty simple solution, though. You could assign it to a button...

Excel: Dymaic chart formatting, dynamic chart, default formats

dynamic chart, default formats, maximum values: There is a way to save a chart format as a template, then you can use that template to create new charts. Or, when you reproduce the chart, you can ctrl/drag the existing chart to make an exact copy and then make whatever changes you want to the copy. Unfortunately,...

Excel: Dynamic Offset, free columns, column c

free columns, column c, typical data: Robert, If you need the last column that has a value say in row 10 then you could do cells(10,columns.count).End(xltoLeft).column so assuming that row 2 would be good for this ScoreTester() Column B are names Column C are Football results...

Excel: emailing worksheets macro, mail selection, brenda cox

mail selection, brenda cox, source cells: Hi Brenda, I strongly suspect this code has been copied from ROn de Bruin s website. I suggest you to look here: http://www.rondebruin.nl/win/s1/outlook/amail2.htm or here: http://www.rondebruin.nl/win/s1/outlook/amail1.htm or here: http://www.rondebruin.nl/win/s1/outlook/amail3.ht...

Excel: Excel 2010 & Excel 2003 compatibility, validations, allexperts

validations, allexperts, subject line: Hi Sourabh, This is a bit strange because normally filters shouldn t get affected by the version difference. Do you have any kind of sheet protection or file protection in this file. If the data is not confidential, can you mail it to me so I can convert...

Excel: excel, cell a2, column numbers

cell a2, column numbers, 2s: Hi Kal, You can use the countif function in excel. Eg. if cells A1 to O1 contain the values shown in your question above, then you can use countif formula in cell A2 and find out how many 2s are there in the above range. Let me know if you need any...

Excel: Excel data validation, excel data validation, j3

excel data validation, j3, k5: Using all the possible values for column 2 (I used 3 in this example, a, b, and c), put in a formula like this in the data validation for List: =IF(B1= a ,J1:J3,IF(B1= b ,K1:K5,IF(B1= c ,L1:L4))) in J1:J3 are the values for a, K1:K5 are the values for b,...

Excel: Excel date tests, cell d2, conditional formatting

cell d2, conditional formatting, allexpert: Natalie: I have uploaded a sample file here: https://www.dropbox.com/s/qsrgobzc28yescu/Natalie-Allexpert.xlsx You can use an IF function, combined with AND to create a formula that will let you know if the date matches criteria. You can also use...

Excel: Excel docs only open in part screen, program shortcuts, win 7

program shortcuts, win 7, document name: Hi Nico, I have noticed this myself with the newer versions of excel and have also look for answers to this. The one answer I have found that makes sense to my situation is that I am using the personal macro workbook . This is a hidden workbook that I use...

Excel: Excel email list, start mail, mail list

start mail, mail list, select recipients: Hi Nick, I suppose you are wanting to send emails using your Excel mail list and that requires knowledge of the mail merge functionality in Word (yes, I said Word). 1. Make sure that your Excel document has these headings: First name, Last Name ...