About Experts Sitemap - Group 75 - Page 4 2012-07-27
Excel: DATEDIF returns #value! error, value error, e trade
value error, e trade, question thanks: John A common way of correcting that problem (imported stuff that is treated as text) is to multiply the text by 1 (enter a 1, copy-paste special multiply). If you only have two dates it might be just as easy to re-enter them, but, if you have a column...
Excel: Data analysis, customer range, b cells
customer range, b cells, three strings: John, this is the way I would approach it. Sub SrchArr() Dim r As Range, cell As Range, r1 As Range, cell1 As Range Dim std1 As Long Dim std2 As Long Dim rw As Long Dim WrkSH As Worksheet Dim WrkShSrc As Worksheet sheet with existing data...
Excel: Data Referencing, meta data, document support
meta data, document support, excel template: Hi Craig, I am one of those who cannot program in VB. However, I believe that the easiest option is to direct the second drop down list function to pull the model number only from the specified area on the META DATA worksheet. Hope this helps. ...
Excel: Date values, spread sheets, date formats
spread sheets, date formats, spreadsheet: Hi Glenn, There is no way for Excel to know if a number is in fact a date or not, that is up to you. If the numbers that should be dates are in one (or more) columns, just select those columns and change the number format of them to one of the date formats...
Excel: Dates on a Pivot table, Sixthsense
Sixthsense: Hi Debbie Brown, I have not said that it is a home work question for you. I said that it is a homework question for me. The details you have provided is very less and the topic you are discussing is pivot table. If it is a formula or a simple question...
Excel: Dependent dropdown list, dropdown boxes, limeted
dropdown boxes, limeted, vba macro: Jan, If you want to send me a sample workbook with the dropdown boxes already on sheet Overview and sample data on sheet data. Also, you will need to tell me more about how the data is imported and when you want the macro to run to update the dropdown...
Excel: Detecting filter in VBA, filter conditions, present hope
filter conditions, present hope, autofilter: Bill, first, I have never heard of the clearfilters command. When you say a table, is it actually a table in Excel 2003 or later or is it just data arranged like a table. If you show me your code or send me a sample workbook I would have a better Idea...
Excel: Display weekday followed by calendar date, ogilvy, weekday
ogilvy, weekday: Gary, Weekday just returns a number between 1 and 7 inclusive. You can get what you want with this = Today is &TEXT(TODAY(), ddd m/d/yy ) produced Today is Thur 12/30/10 or = Today is &TEXT(TODAY(), dddd m/d/yy ) produced Today is...
Excel: Dividing Time, hh mm ss, work time
hh mm ss, work time, ogilvy: a1: 3:42:51 a2: =(A1/57)+time(0,1,51) so when I divide 3 hours, 42 minutes and 51 seconds by 57 I get 3 minutes and 55 seconds. When I add 1 minute and 51 seconds to that I get 5 minutes and 46 seconds is that what you want? format A2 as hh:mm:ss...
Excel: EXCEL 2003 FORMULA, cell d2, example id
cell d2, example id, b5: Michelle: I wasn t 100% clear on your question. I understood the part about checking to see if the ID# is the same as the row above; however, I wasn t sure on the 2nd part. I have decided you want to check and see if Q2 is greater than Q1 on the same...
Excel: Excel 2003 - Data referencing, excel vlookup, vlookup function
excel vlookup, vlookup function, meta data: Hi Craig, Yes, you can easily accomplish this without resorting to VBA by using the Excel VLOOKUP worksheet function. VLOOKUP is designed to look up a value (in your case the model number) from a given column of a table based on a finding a desired value...
Excel: Excel 2007 Formulas, ip ranges, sort option
ip ranges, sort option, filter option: Hi Dwayne, To accomplish this, you will need to add a new list to the sort option in Excel which tells the program to sort using the parameters that you want. See the attached image for an example of the list you will need to create under the Sort/Filter...
Excel: Excel, target 2, ogilvy
target 2, ogilvy, accomplishment: Reynon, I assume you want to return the numbers you show (1 through 5) based on percent accomplishment of target. So Assume Target is in A1 Assume performance is in A2 =IF(AND(A1 0,A1 ,A2 ),IF(A2/A1 1,5,IF(A2/A1 =0.96,2,IF(A2/A1 =0.5,3,IF(A2...
Excel: Excel Conditional Formatting, pivot table, office excel
pivot table, office excel, microsoft office: I think you will find if you refresh it on your machine it will do the same thing - a pivot table doesn t have conditonal formatting as part of it s setup as far as I know - depending on the pivot table, it MAY be possible to do the same thing with formulas...
Excel: Excel: Conditional formatting in pivot tables, excel conditional formatting, foe example
excel conditional formatting, foe example, pivot table in excel: Pivot tables are so vastly different between 2003 and 2007 as are conditional formatting. I don t think there s a way to keep them in sync aside from having a macro which tests which version of Excel you are in and if 2007 or later, the the macro needs to...
Excel: Excel Formula, database range, counta
database range, counta, excel formula: Chris Second part first since it is easiest; You can use the =COUNTIF() formula to count how many of each number and =COUNTA() to determine the frequency. So, if your unique numbers are in cells d2:d10 and your data is in a2:a21, the following formula...
Excel: Excel Formula Query, oil rig, excel formula
oil rig, excel formula, a2: use conditional formatting, set that to a formula and (for simplicity) 182 days as 6 months - I m not clear if your range is 6 months before, 6 months after, 3 months either side etc, but some formula like =A2-182 now() would return dates occuring up...
Excel: Excel formula, column c, excel formula
column c, excel formula, ogilvy: Ron, Say in D2 you put in the formula =if(C2= , ,if(C2 0,C2*75,0*C2)) of course 0 times C2 produces zero, so you could do =if(C2= , ,if(C2 0,C2*75,0)) then drag fill down column D I assume that only the listed values will be entered...
Excel: Excel formula, array formula, patient id
array formula, patient id, excel formula: Ana, =max(if($A$2:$A$500=1,$B$2:$B$500) entered with Ctrl+shift+enter (rather than just enter since this is an array formula) should tell you the max cycle for patient 1. Change the 500 to reflect the last row that could contain data. Assumes the...
Excel: IF - Excel function question, excel function, cat need
excel function, cat need, macro script: * Hmm. Sorry, this is stumping me. I agree about removing the additional next mysubset, sorry about that. But the problem definitely has to do with how excel is evaluating the values. Perhaps code that is centered around the Find concept will be...
Excel: Excel Macro, example john doe, vba programming
example john doe, vba programming, cell a2: On a new sheet, enter in cell A2 John Doe in B2 8/22/1968 and in C2 2005 Continue with the other names below this, in A3:C3, A4:C4, etc. In D1, enter =LEFT(A1,1)&MID(A1,FIND( ,A1)+1,1)&MOD(DAY(B1),10)&MOD(C1,10)&INT(RAND()*10)&INT(RAND()*10)...
Excel: Excel Multiple Sheets Same Cell, Sixthsense
Sixthsense: Hi Kunal, First of all sorry for the delayed reply, since there was some problem in my internet connection. Put the below formula in any of the cell and drag/fill it to the right. =INDIRECT( &COLUMN(A1)& !D &3) OR =INDIRECT( &COLUMN(A1)&...
Excel: Excel not printing cells in color., hp color, gray scale
hp color, gray scale, edoc: Hi Caroline, I am sorry to hear that you are having issues with your printing, but because you can print the same Excel file in color on other printers, the problem is with your printer and not with the actual file. I sincerely hope that this helps....
Excel: Excel - Return Date, Sixthsense
Sixthsense: Hi, Add -1 near to the first year function which will do your need. Like the below:- =IF(ISNUMBER(A2),IF(MONTH(A2) 11,YEAR(A2)-1,YEAR(A2)+1), ) For your easy reference I have created a sample file for you, Click or Copy and paste the below link...
Excel: Excel VBA Find & Delete, hobby activity, dynamic ranges
hobby activity, dynamic ranges, activity model: Hi Charlie, Please send me a sample file (maryalindsey@gmail.com) and I will create the macro and return same to you. I need to have a point of reference where I can fully understand what you are hoping to accomplish. I look forward to assisting you...
Excel: Excel - VBA - set enter/cell order, Sixthsense
Sixthsense: Hi David, First of all sorry for the delayed reply, since there was some problem in my internet connection. Thanks for sending your question my way, but as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question....
Excel: Excel - vlookup and if, excel vlookup, vlookup function
excel vlookup, vlookup function, kit configurations: Well, if you ve already got the lists all set up, then you can use the VLOOKUP function to pull the details from the result of your list. Here s a link to a tutorial on VLOOKUP. Watch that, and if you still can t figure it out, let me know: http://599cd.com/tips/excel/vlookup/?key=AllExpert...
Excel: Excel Weekday function, workweek, avail
workweek, avail: Bruce What weekday do you want to return and why? If you explain the rule, I am sure we can come up with a function to meet your needs. But, a function that will return a weekday leaves things pretty wide open. And, what do you mean by a weekday? Monday,...
Excel: Excel2003-VBA, vkeys, vke
vkeys, vke, column names: Hi Sasi, To handle differently ordered columns between Sheet1 and Sheet2 you can create what I call a header map array. This is just an array that says, for example, the header for column 2 in Sheet1 ( Part ) is found in column 4 of Sheet2. In my code,...
Excel: Excel2003-VBA, spath, sheet1
spath, sheet1, cstr: Hi Sri, See if this example helps you on your way: Sub GetOpenFileNameExample2() Dim vFilename As Variant Dim sPath As String Dim lFilecount As Long Dim lCount As Long sPath = c:YourDefaultPathGoesHere ChDrive sPath...
Excel: Excell scatterplot, slope and y intercept, linear regression line
slope and y intercept, linear regression line, arbitrary locations: Let s say your X-data is in column A and Y-data in B. Put slope in D2 and Y-intercept in E2 (arbitrary locations). In G2 put =MIN(A:A) and G3 put =MAX(A:A) in H2 put =$D$2*G2+$E$2 and fill down to H3 (y=mx*b) Select a data point from the first series so...
Excel: Extract 4 digit number from a text cell, array formula, cos d estournel
array formula, cos d estournel, digit number: Gregory, Assume the first entry is in D1 then =MID(D1,SMALL(IF(ISNUMBER(MID(D1,ROW(INDIRECT( 1: &LEN(D1))),1)*1),ROW(INDIRECT( 1: &LEN(D1)))),1),4)*1 entered with Ctrl+Shift+Enter rather than just enter (since it is an array formula) produced 1982...
Excel: edit multiple files, Microsoft Word 2003, linked charts
Microsoft Word 2003, linked charts, macros: Hi sk, Is your master chart linked? If it is linked to all 40,000 with unique data then when you open, it should update automatically. If you did it manually than the only shortcut would be to create a macro and each time you open the file use the macro...
Excel: editing multiple files, vba code, long long time
vba code, long long time, multiple files: * It can be done with VBA code, this isn t going to make it happen instantaneously, but you can surely use some code - run it and go make coffee or something (or let it run overnight). I am thinking the code should make the change in each file, then...
Excel: editing multiple files, Sixthsense
Sixthsense: Hi sk, It s surprising to hear that you have created 40,000 files and the solution for your query may be derived using VBA (Macro). But as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try...
Excel: employee scheduling in excel, staff names, sumproduct
staff names, sumproduct, employee scheduling: It will be possible, but not with the data structure you are using at the moment - I would be INCLINED to have the people in one area, with a start time and an end time - the schedule is then a sumifs sort of formula - summing with more than one condition...
Excel: excel 2003, nite shift, bottom section
nite shift, bottom section, evening shifts: In the VBS, insert a module, enter this: Sub CheckBox1_Click() If Range( C30 ) = True Then Sheets( Sheet3 ).Range( A5:I33 ).Copy Sheets( Sheet2 ).Range( A19 ) Else Sheets( Sheet2 ).Range( A19:I47 ).ClearContents End If End...
Excel: excel 2007 formula, Sixthsense
Sixthsense: Hi Peter, I request you, please don t post your formula and ask for solution. Elaborate your data and the result you are expecting to derive using the formula. Because elaborating your criteria and data structure will make me to provide beer solution other...
Excel: excel, format button, rectangular area
format button, rectangular area, ogilvy: Hello JOHN from Greece, I have attached an image showing the formula I copied out of your email applied to a worksheet to mark duplicates in yellow. After applying the conditional formatting, I opened the conditional formatting dialog so you could see...
Excel: excel, conditional formatting, true cases
conditional formatting, true cases, cells: I would use conditional formatting to identify these – format, conditional formatting then use a function =countif(YourRange,YourCell) 1 And set the colour to whatever you like – this would then make the duplicates easy to spot – you might also want...
Excel: excel, conditional formatting, cell reference
conditional formatting, cell reference, current date: Use format, conditional formatting to achieve this - use the formula option =YourDate today() replacing YourDate with the cell reference - set the colour you want and apply this to all relevent cells and any dates older than the current date will then...
Excel: excel incremental counter, Sixthsense
Sixthsense: Hi Greg, Yes exactly. If you are trying to do it by using worksheet function then it requires a cell reference where the last day of file opened date should be entered. For example assume that you are entering the current date (By pressing Cntrl+Semicolon)...
Excel: excel link, Micorosoft Excel 2010, absolute reference
Micorosoft Excel 2010, absolute reference, formulas: Hi Anne, What you need to do is add an absolute reference. This allows you to move the sorted cells to any location and the reference cells will track to actual data, not location (which may change) within document. Before sorting put $ before cell info....
Excel: excel2003-vba, irow, input sheet
irow, input sheet, header row: Hi again Srikar, The following code overwrites the Report sheet if it already exists, otherwise creates a new Report sheet. _____________________________________________________ Sub MakeReport() Dim InputWS As Worksheet The input (active)...
Excel: Filter by information in multiple columns, Sixthsense
Sixthsense: Hi Jodi, First of all sorry for the delayed reply, since there was some problem in my internet connection. I am not clear whether this is what you are looking for. Anyway have a look in the below links. http://www.excelfunctions.net/ExcelFilter.html...
Excel: Find Copy Paste Macro, question thanks, copy paste
question thanks, copy paste, numeric: Natasha Put Sheets( summary ).Activate before the End Sub line to switch back to the summary sheet. Moving data to the pre-formatted areas is certainly doable; but, you need to provide a lot more information. As I understand it, we are presently...
Excel: Find "." within text for each cell by using macro, j text, sh3
j text, sh3, afe: Hamdy, .Find( . , Cells(cell.Row, J ).Text should be Application.Find( . , Cells(cell.Row, J ).Text I personally would qualify Cells in both cases - but if it isn t causing a problem, then I guess that is OK -- Regards, Tom Ogilvy...
Excel: Format condition, orange cell, invoice number
orange cell, invoice number, res2: Hamdy, put this in the sheet module associated with sheet Invoice Private Sub Worksheet_Change(ByVal Target As Range) Dim lastrow As Long, i As Long, j As Long Dim sh3 As Worksheet Set sh3 = Me lastrow = sh3.Cells(sh3.Rows.Count, D ).End(xlUp).Row...
Excel: Formatting with formula, vba code, logical test
vba code, logical test, cell reference: Hi Dave How are you? Please consider the following VBA code: Sub formattingwithFormula() Dim R As Range Set R = ActiveSheet.Range( A14 ) If R 100 Then R.Interior.ColorIndex = 6 Else R.Interior.ColorIndex...
Excel: IF/Then Formula cascading through 4 cells, Sixthsense
Sixthsense: Hi Chip, Check this... =IF(ISNUMBER(A1),A1,IF(ISNUMBER(B1),B1,IF(ISNUMBER(C1),C1,IF(ISNUMBER(D1),D1, )))) OR =IF(A1 ,A1,IF(B1 ,B1,IF(C1 ,C1,IF(D1 ,D1, )))) Don t hesitate to ask again, if your requirement is not fulfilled. ...
Excel: IF Functions for a large variation, Vlookup Function with Diagram by Sixthsense
Vlookup Function with Diagram by Sixthsense: Hi Chase Wheeler, If you are looking to do it using the worksheet function then use Vlookup. For your easy reference I have created a sample file for you to know how to use Vlookup. Click or Copy and paste the below link in web browser to download the...
Excel: Calculation of Tax, irs info, gross salary
Excel: Calculation of Tax, irs info, gross salary, w4 form
Excel: Collate printing
Excel: Collate printing
Excel: DATEDIF returns #value! error, value error, excel 2000
Excel: DATEDIF returns #value! error, value error, excel 2000, tch
Excel: Excel 2007 Formulas, ip addresses
Excel: Excel 2007 Formulas, ip addresses
Excel: Excel Weekday function, excel function, cell a1
Excel: Excel Weekday function, excel function, cell a1, weekday
Excel: hyperlink to a picture in the same worksheet, tap
Excel: hyperlink to a picture in the same worksheet, tap
Excel: false data by countifs, g 5000, basicly
g 5000, basicly, collumn: Use the following formula: =ISERROR(COUNTIFS(scrub!$EB$2:$EB$5000, 1,scrub!$G$2:$G$5000,I217,scrub!$A$2:$A$5000,$G$204)/(COUNTIFS(scrub!$EB$2:$EB$5000, 1,scrub!$G$2:$G$5000,I217,scrub!$A$2:$A$5000,$G$204)+COUNTIFS(scrub!$EB$2:$EB$5000, 0,scrub!$G$2:$G$5000,I217,scrub!$A$2:$A$5000,$G$204)),...
Excel: If find and then copy & paste results, scount, source sheet
scount, source sheet, srow: Hi Ashley! How are you? What about the following below? Sub CopyBO() Copy cells of cols A,B from rows containing BO in col I of the active worksheet (source sheet) to cols A,B of Sheet2 (destination sheet) Dim DestSheet As Worksheet...
Excel: find email addresses, random column, custom filter
random column, custom filter, h20: Is putting them in consecutive cells actually helping in the ultimate goal (which of course is something I m not aware of ) - for instance, although they would be more obvious, a mail merge would be a problem. PLUS and rather more importantly, it is possible...
Excel: formating a cell, littlewing, scientific notation
littlewing, scientific notation, gmail: Steve Not over 15 numbers sadly, it will convert to scientific. I am a little confused as to why it wont allow you to change the cells after they have been formatted as text though. can you send me a copy of the file or one with sample barcodes and the...
Excel: if then formula?, happy holidays, screen shots
happy holidays, screen shots, allexperts: Sorry, you can t attach files to AllExperts, but you can include screen shots. In any case, it sounds like you just need to learn how to use the IF function. See this tutorial: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExpert...
Excel: formula regarding m.s.excel, gross wages, gross salary
gross wages, gross salary, e5 e6: Pamper This looks like exactly the same question that I reeived yesterday from taunkanti Here is the answer that I gave It appears that your problem can be solved with an If(AND) formula however I can t give you the exact formula since I do not understand...
Excel: What funcition to use and how to set up, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, excel tutorials: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...
Excel: generate permutations, lottery permutations, learning to use excel
lottery permutations, learning to use excel, number combinations: The macro is relatively straight forward, though I m not clear what you mean by add up to a value - I m assuming you mean something along the lines of for every value between 15 and 235 for every combination of 5 digits, check if it sums to the given...
Excel: Help w/ Excel dynamic table names and drop down lists., drop down boxes, excel user
drop down boxes, excel user, dynamic table: * I am advanced in some areas, beginner in others - Excel is a never ending world of possibilities and I ve just begun delving into it - as all folks find out- the more we think we know, the more we realize we need to know more. I am not 100% sure I...
Excel: Help w/ Excel dynamic table names and drop down lists., drop down boxes, excel user
drop down boxes, excel user, dynamic table: Hi James, You didn t mention which kind of dropdown you are using (whether a Forms control dropdown or an ActiveX combobox dropdown), so I am going to assume it is the newer ActiveX combobox dropdown. Strictly speaking you don t have to use a macro for...
Excel: Hex data formating in Excel., hex data, delimeter
hex data, delimeter, spath: Anil, Sub ReadHexfile() Dim sPath As String, sName As String Dim line As String, rw As Long, icol As Long Dim i As Long sPath = C:WorkingxlText sName = 1_1_a_a_A_Hex.txt Open sPath & sName For Input As #1 rw = 1 Do While Not EOF(1) Line...
Excel: Hyperlinks to Charts, world grain production, target value
world grain production, target value, target address: The name of the Sub MUST not contain spaces, so you simply can t use Sub GotoWorldGrain Production (g)() If a cell contains GRAPH: World Grain Production, 1960-2010 and you want that to activate a chart named World Grain Production (g) then there...
Excel: hyperlink to a picture in the same worksheet, documentation support, support specialist
documentation support, support specialist, kindest regards: Hi Nima You can leave the pictures in a folder on the hard drive of the computer you are using. Then hyperlink the cells to the picture (Insert / Hyperlink) then finding the location of the picture, clicking on the image and the cell and the image would...
Excel: hyperlinks, microsoft word document, universal naming convention
microsoft word document, universal naming convention, unc path: Dustin, Yes. There is a Hyperlink worksheet function HYPERLINK(link_location,friendly_name) =Hyperlink(B1,A1) see the help for details on how you need to struture your URL s. They have plenty of examples. say if you wanted to add http:\ ...
Excel: Identifying values, excel spreadsheet, richard florida
excel spreadsheet, richard florida, customer name: Cass You can write a formula that looks something like this =IF(+ISERROR(+VLOOKUP(B103,B$2:B102,1,FALSE)), New , existing ) Assuming the names are in coumn B. This formula says lookup the value in B103 within the range of B2 to B102. (If it is not...
Excel: Import or merge contact info in different Excel cells in a file, row headings, address phone number
row headings, address phone number, label option: Hi David, Do you have the row headings (i.e., First Name, Surname, Address, Phone Number) setup by columns in Excel? If you do, it is simply a matter of using the Word Label Form to complete the Merge. If you would like me to setup sample documents...
Excel: Inventory, sheet names, z21
sheet names, z21, index number: if the total is in the same cell on each sheet you can use 3D references =Sum(Start:End!Z21) where you have a sheet named Start and a sheet named End and each of the item tabs are physically placed between the Start and End tabs. then if you want...
Excel: keyboard shortcut, keyboard shortcut, pageup
keyboard shortcut, pageup, previous question: Bhakti: I believe you asked your original question to someone else (it sounds like you are asking a followup; however, I did not answer a previous question from you). Here is a keyboard shortcut to scan through the different tabs in a workbook: CTRL-PageDown...
Excel: LIST ALL DATA BY CRITERIA, microsoft office fluent user interface, microsoft office fluent user
microsoft office fluent user interface, microsoft office fluent user, office fluent user interface: Hi Antony, The only way to automate this process in Microsoft Excel is with the use of a macro which you can design by: To automate a repetitive task, you can quickly record a macro in Microsoft Office Excel. You can also create a macro by using the...
Excel: Linking Data in Two sheets corresponding to a specific number, cell c2, cell b2
cell c2, cell b2, vlookup formula: Aladin You can do what you want with a VLOOKUP formula. The formula might look like this =VLOOKUP(C2,Sheet1!A$2:B$100,2,FALSE) The formula is in sheet2 column B It says Go to sheet 1 and look in cells A2 thru B100 for the value that is in cell...
Excel: Lock selected range., format cells, column headings
format cells, column headings, toolbar tools: wasfiathhar It takes a few procedures but this is what I do. First-- go toolbar/tools/protection/protect sheet and protect the sheet. Second-- go to toolbar/tools/protection/protect sheet and unprotect sheet Third-- clic on all cells (intersection...
Excel: Looking up the date, patient thanks, patient number
patient thanks, patient number, pivot table: If you want to get all dates for all patients, then a pivot table may be the way to go - you didnt specify version of excel so it s difficult to give specific advice, but effectively this would (presumably) show patients down the left side, description of...
Excel: Looking up multiple values on excel, location column, column c
location column, column c, ogilvy: if it is a unique combination then Values being sought M1: date N1: location O1: location activity lookup in Column A: date Column B: Location Column C: Location Activity Column D: distance data in rows 2 to 5000 =sumproduct(--($A$2:$A$500=$M$1),--($B$2:$B$5000=$N$1),--($C$2:$C$5000=$O$1),$D$2:$D$5000)...
Excel: Lookup with Blank Spaces Question, abcdefg, sheet1
abcdefg, sheet1, blank spaces: Tom Lets start with the easiest fix =INDEX(INDIRECT( & $C$1 & ! & $S$1),MATCH(A4& * ,INDIRECT( & $C$1 & ! & INDIRECT($T$1)),0)) This will only have problems if the value in A4 could be shorter substring of the values in the lookup column...
Excel: lookup data in a table, cell a1, f13
cell a1, f13, e13: James, I couldn t duplicate your problem. But I am not sure why you are using Lookup if you are comparing two cells for an exact match. In the same location where you have the formula you show I would put =IF(F13=$A$1,H13, ) Instead of using =LOOKUP($A$1,F13,H13)...
Excel: if lookup formula, cell d1, ogilvy
cell d1, ogilvy, drop down list: Travis, I could probably do something for you, but not based on just what you have told me. I would have to see the sheets and set up the formula for the specific cells that have data and so forth. If you want to send me you file with a clear explanation...
Excel: lookup using charts, Sixthsense
Sixthsense: Hi Travis, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in Chart and therefore can not answer your Chart question. You might try one of the other Excel experts that claim expertise in Excel Chart. --- ...
Excel: Macro to auto subtotal, len function, d11
len function, d11, input data: Hi John How are you? What about the following Sub abovetiers() Dim R As Range, X As String, L As Integer Set R = ActiveSheet.Range( A3:D11 ) R.Cells(1, 1) = Mid(R.Cells(1, 1), 1, 1) X = InputBox( lowest tier ) + ; T = 0: P = I = 1 Do While...
Excel: Macro: Change sheets when > 0, target value, target address
target value, target address, chip pearson: Greg, Right click on the sheet tab and select view code. Put in a procedure like this Private Sub Worksheet_Change(ByVal Target As Range) If target.address = $E$200 then if len(trim(Target.Value)) 0 then if isnumeric(Target.value) then ...
Excel: Macro to clear cell contents of a cell in a row if data is entered in another cell in the same row., cell contents, l7
cell contents, l7, double click: Ivan What do you mean by for instance ? You want a macro that will delete the x in L7 if you put an x in M7? So, you want this to happen for all rows 7-51. If you put an x in o7 should it delete x s in L or M? What if you enter something other than x?...
Excel: Macro to copy filtered data from one sheet to another., data sheet, aa
data sheet, aa, employee code: Hello Prashant, This type of macro cannot be recorded , but must be written because the data to be copied depends on the employee code keyed in. I think the easiest way for me to help you with this would be for you to email me the Excel file, along...
Excel: Macro to copy filtered data from one sheet to another., exact format, prashant
exact format, prashant, tushar: Prashant, Only recording a macro will not always be enough. Some parameters will need not be modified in MACRO to generalize the macro to suite the requirements. This should be relatively easy task once the exact format of file / columns is known. Regards...
Excel: Macro to copy filtered data from one sheet to another., Sixthsense
Sixthsense: Hi Prashant, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim expertise in Excel VBA such as Bob Umlas,...
Excel: Macro that will create an auto backup., fileformat, auto backup
fileformat, auto backup, saveas: You need one line inserted -- see below: Sub Backup1() Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ C:Program FilesRotationlist.xls , FileFormat:= _ xlExcel8, Password:= , WriteResPassword:= , ReadOnlyRecommended:=False...
Excel: Macro help, cell interior, column c
cell interior, column c, ogilvy: assume you have headers in row 1 In this table I want to delete the rows that have data. If a row has data in all cells then I want to delete it. Dim r As Range, cell As Range, r1 As Range Set r = Range( A2 , Cells(Rows.Count, A ).End(xlUp)) ...
Excel: Macro to hide & unhide columns., true range, win 7
true range, win 7, ogilvy: Bill this will toggle the hidden status of column E Sub ToggleColumnE() Columns(5).EntireColumn.Hidden = Not (Columns(5).EntireColumn.Hidden) End Sub Here are individual macros to hide and unhide Sub HideColumnE() Columns(5).EntireColumn.Hidden...
Excel: Macro to import, left hand corner, code snippet
left hand corner, code snippet, getopenfilename: Bill here is a code snippet that should get you started. Dim r as Range, r1 as Range, bk as workbook, sh as worksheet Dim fName as variant set r = Activeworkbook.Names( ABCD ).ReferstoRange fname = application.GetOpenFileName() if fname = False...
Excel: Macro two logic events, soe report, run time error
soe report, run time error, logic events: Hamdy, If that is your actual code, you misspelled the word application in every case. That would raise the error you describe. applicaiton.WorksheetFunction.CountA should be application.WorksheetFunction.CountA -- Regards, Tom Ogilvy ...
Excel: Macro To Open Workbook In Default Path, test xls, path question
test xls, path question, spath: Kenneth, I too set up two workbooks. One was named WorkbookA.xls. The second was Named WorkbookB.xls. I put them in a directory C:WorkingMyfiles I put the first two procedures in WorkbookA.xls (copied from your posting) I put the second Workbook.Open...
Excel: Macro to save the selected part of a sheet, starter code, excel workbook
starter code, excel workbook, fileformat: Bill, Assume the named range is workbook level and located in the activeworkbook I want the macro to select a named range I assume the named range is MyRangeName - change to suit. save it to a separate Excel workbook I assume you want this...
Excel: Macro does not work properly if sheet isn't active, res1, p46
res1, p46, cell res: Hamdy Looking a the code: With Worksheets( Sheet1 ) res2 = .Cells(cell.Row, M ).Value + _ .Cells(cell.Row, J ).Value + _ .Cells(cell.Row, G ).Value End With If Not IsError(res) Then If IsNumeric(res) Then res = res - res1 -...
Excel: Macros help, count button, array type
count button, array type, jack oliver: 1) If your list of names is e.g. in range A1:A10 (with no blanks), the formula =SUM(1/COUNTIF(A1:A10,A1:A10)) will count the number of distinct/unique names in the list. The formula is entered as an array (type formula into cell, then instead of hitting...
Excel: Masking digits in excel, cell b10, paste values
cell b10, paste values, excel sheet: Pranay: Please place this formula in cell B10: =LEFT(A10,2)&REPT( X ,LEN(A10)-4)&RIGHT(A10,2) You may then copy this cell down the entire column B and it will keep the 1st 2 and last 2 digits of each account number and fill the rest with X s. From...
Excel: Match two columns in different tabs, sheet1, cell a1
sheet1, cell a1, richard florida: Ignacio I am not sure I undestand what you mean by match . If you want to make two tabs look alike where both have the same data then you can use a link formula to bring over from one sheet the data in the other sheet. For example this formula in sheet2...
Excel: Match and find values in Excel, countif function, c2 c
countif function, c2 c, vlookup: Ana, sice you don t give specific information that would be needed to write formulas, I will give you some examples. Assume in sheet1 I have Unique ID in column A, Cycle number in column B and the cycles are progressive so the last cycle would appear...
Excel: Matching Data on One Excel Sheet with Another, conditional formatting, format cells
conditional formatting, format cells, data validation: If you have excel 2010 or later or you are willing to create a defined name you can do that. conditional formatting allows you to format cells conditionally the countif function allows you to see if one cell matches values in another location. (or...
Excel: Merging cells in a MS Word Table, c2 r1, work queue
c2 r1, work queue, irow: Hello Again Sean, Have you tried creating a macro from the first time you start to clean up the file you receive, including when you merge the cells in Word and once accurately merged in Word, open the file in Excel. I am not a VBA expert, and I need...
Excel: Modification of Macro to Reference Subfolders, string s2, cell values
string s2, cell values, sh1: Andrew, Paste all this in a new module. Run the Start macro. revision - removed directory used for testing Option Explicit Dim DirectoriesAndFiles() As String Public count As Integer Public counter As Integer Sub Start() Dim DirToSearch...
Excel: m.s.excel formula, gross wages, gross salary
gross wages, gross salary, e5 e6: tarunkanti It appears that your problem can be solved with an If(AND) formula however I can t give you the exact formula since I do not understand all of the parameters of the contributions. For example you have said (Employees contribution on gross...
Excel: macro for calculating averages of quartiles of groups, blank row, excel sheet
blank row, excel sheet, quartiles: I can do it, but I need to clarify something first. Suppose the group has 5 rows. How do you want to consider the 1st 25%, 2nd 25%, etc? If the numbers are 1,2,3,4,5, what s the average of the 1st 25%, 2nd 25%, etc? Tell me how you d like that calculated...
Excel: macro for calculating averages of quartiles of groups, Sixthsense
Sixthsense: Hi Jan Lid, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim expertise in Excel VBA such as Bob Umlas,...
Excel: macro to combine projects into one row, project span, conditional formatting
project span, conditional formatting, jetblue: This macro should do it -- assumes the sheets are correctly named Forecast and Plan: Sub DistributeForecast() Dim n As Long, F As Worksheet, P As Worksheet, Planrow As Long Dim Startd As Integer, Endd As Integer Set F = Sheets( Forecast ) Set...
Excel: macro variable ranges, vertical axes, loop command
vertical axes, loop command, variable range: Sub Sorterdesc() For i = 1 To Range( B50000 ).End(xlUp).Row Cells(i, 2).Resize(1, 11).Sort key1:=Cells(i, 2), Orientation:=xlLeftToRight, order1:=xlDescending Next End Sub Sub SorterAsc() For i = 1 To Range( B50000 ).End(xlUp).Row...
Excel: NESTED IF, Nested If by Sixthsense
Nested If by Sixthsense: Hi Jim, Try this... =IF(OR(ISERROR(A2),ISERROR(B2)), ,IF(AND(A2= ,B2= ), ,IF(B2= , Delete A ,IF(A2= , Add B ,IF(B2 A2, Increase ,IF(B2 A2, Decrease ,IF(A2=B2, N/C , ))))))) Don t hesitate to ask again, if your requirement is not fulfilled....
Excel: need help with currency conversions, dollar conversion rate, currency conversions
dollar conversion rate, currency conversions, currency conversion: Victoria This isn t to difficult, but you have to have a conversion factor built into your formula. You can use a fixed conversion rate that you input, or you can use a conversion rate that is updated constantly as time passes. For example currently...
Excel: nested Vlookups and Ifs, array formula, index range
array formula, index range, correct names: Sarah, I have attached an image showing a formula that does what you describe. I suggest you have two lists of names - one for the annual leave on one for the Lieu time data since you have to look at two different locations. --------- Revision: Just...
Excel: Password Proecting individual tabs, necessary functionality, visible property
necessary functionality, visible property, inputbox: Hi Ted, Here is how I handle it when I want to force the user to manually enable macros when such is required in order for the workbook to have the necessary functionality. What I do is the following: 1) Hide all the worksheets in the workbook, save...
Excel: Picture in a conditional, cell a2, allexperts
cell a2, allexperts, cell a1: Yes, when you define the name pix , =OFFSET(Sheet1!$A$1,0,IF(Sheet1!$A$3 =5,0,1)) goes in the refers to box. When you select the original picture you inserted, you indeed don t get anything in the formula bar. But after you do the copy/paste-as-picture/paste-picture-link,...
Excel: Pictures in Excel, bmp image, word processing program
bmp image, word processing program, gridlines: Hi John, In Excel 2002, when copying hold down the Shift key and click on Edit / Copy Picture and this should allow you to copy anything as a picture. This is Excel 2007...(I think it is better, just a learning curve). Copy data as a picture in Excel...
Excel: Pivot Graphs, pivot table, pivot tables
pivot table, pivot tables, dropdown list: •I don t see the relationship between the code you supplied and a dropdown list change. •I don t know what you mean by the pivot table is Call Clear . •I don t know what you mean by for each option selected... It d help if you can send me a sample wb...
Excel: Pivot Table simplification, customer id number, zeroes and ones
customer id number, zeroes and ones, error checks: David: When setting up your pivot table you should have Contact ID in the Row section (I believe currently you have one of your Error Checks in the Row section ). In The Values section if the pivot table you should have all 10 of your Error check...
Excel: Place straight line on Excel scatterplot, slope and y intercept, linear regression line
slope and y intercept, linear regression line, slope of 0: Hi Andrea, It is easy to add a straight line to a scatter plot. Since the line is straight you only need to define the end points and Excel does the rest. Just as an example, lets say your x-axis is scaled to go from 0 to 50 and you want to create a...
Excel: Protecting Excel, protecting excel, excel spreadsheets
protecting excel, excel spreadsheets, correct horse: * First of all, please don t think this is ANYTHING like my normal answer on allexperts.com. But what you basically need is a small Excel APPLICATION (with programming and all, in the Excel programming language VBA .) I ve done this several times...
Excel: pivot table output format, column labels, pivot table
column labels, pivot table, report layout: Paul: I presume you are using Excel 2007 or later. What you are seeing is the default Pivot Table format for those versions of Excel. Here are the steps to format the way you want: 1) Right click on the Pivot Table 2) Choose Pivot Table options 3)...
Excel: am/pm breakout on 24 hour time, time question, c30
time question, c30, allexperts: This will require knowing how to do two things: 1. Getting a value from another sheet, and 2. Using the IF function The first item is easy. You can refer to a value on another sheet like this: =SheetName!FieldName So if you want to get F4 from...
Excel: How to print serial numbers on same sheet but repeated printing, k15, chiper
k15, chiper, richard florida: Andy I do not know of a way to change the number in K15 based on the pages printed. I sugget you reask the question of anther allexperts person and maybe someone else will have an idea for you. Sorry I couldn t be of more help. Richard Florida ...
Excel: REMOVE DUPLICATES, blank row, information column
blank row, information column, service pack 2: WENDY, If I understand the question correctly, I would go to column O or the next blank column. then in O3 I would put a formula =if(And(i3=i2,f3=f2), Delete , Unique ) and drag fill down the column. I would then filter on that column for the Delete...
Excel: Referencing value contained in another cell withing a formula, format a1, g 5400
format a1, g 5400, datevalue: A1 has the string/Text 1-Dec-10 then =SUM(IF( 2010 !$A$2:$A$5400=DATEVALUE(A1), IF( 2010 !$G$2:$G$5400= ST1 , 2010 !$T$2:$T$5400,0),0)) (if you format A1 as general it will show 1-Dec-10 if A1 actually contains 1-Dec-10 stored as an Excel; (if...
Excel: Remove duplicate cells from range, msgbox, ogilvy
msgbox, ogilvy, r rw: Steve, this worked for me Sub abcd() Dim rng As Range, r As Range, rw As Range Set rng = Range( $1:$1,$4:$4,$4:$4,$6:$6,$7:$7,$7:$7 ) MsgBox Before: & rng.Address For Each rw In rng.Rows If r Is Nothing Then Set r = rw Else ...
Excel: Easy Way to Combine All WorkBooks In certain order Into Single WorkBook, sheet names, dear tom
sheet names, dear tom, work sheets: Newbie-san, sheets.copy copies all sheets to a new workbook set bk = Activeworkbook set bk2 = Workbooks( Dest.xls ) sheets.copy After:=bk.sheets(bk.Sheets.count) appends copies of the sheets to end of the existing sheets in the destination workbook....
Excel: Reordering a Scatter Chart, Sixthsense
Sixthsense: Hi Dave, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in Chart and therefore can not answer your Chart question. You might try one of the other Excel experts that claim expertise in Excel Chart. My Profile...
Excel: Resizing a Range to Contain the entire row, rng, ogilvy
rng, ogilvy, f8: steve set rng = rng.EntireRow here is a demonstration from the immediate window in the Visual Basic Editor (VBE) set rng = Range( H4,H6,F8 ) ? rng.Address $H$4,$H$6,$F$8 ? rng.entireRow.address $4:$4,$6:$6,$8:$8 set rng = rng.EntireRow ? rng.Address...
Excel: Returning a single value based on multiple criteria, running microsoft office, excel examples
running microsoft office, excel examples, apple1: David, the easiest way to do this is with a Pivot Table. However, that will not account for the missing values. To do the missing values, you need to add them to the bottom of your existing data. That can be done with a macro or you might be able to...
Excel: Rewriting VBA in VB6, vba code, global object
vba code, global object, excel application: Hi Hadi, You can copy 99 percent of the VBA code to VB6. All you have to do after that is ensure you are talking with the right objects. In VB6, the Application object and many objects of Excel you would normally address directly (ActiveCell for example)...
Excel: Get a random sample, sh1, r1a
sh1, r1a, r1b: Jn, when I tested the code previously, I had the same problem because I had not changed the formula that determines what rows to copy properly. Once I fixed it, it worked perfectly for me. I had separate countif formulas to verify that it copied exactly...
Excel: Get a random sample, r1a, r1b
r1a, r1b, sh1: JN, I believe I have made all the changes you requested: My tests confirmed that the code performed as expected based on your stated requests. Sub SampleData_MultipleAnalyst() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, n As Long Dim...
Excel: How to run a macro across multiple workbooks, macro button, tabs
macro button, tabs: Kristin This should do it. There is no error checking, so it will break if cell A1 ever contains a value that is not a valid worksheet name. Sub myTabName() Dim sh As Worksheet For Each sh In Worksheets sh.Name = sh.Range( A1 ) ...
Excel: Seggregating text data, Sixthsense
Sixthsense: Hi moneessh, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in Chart and therefore can not answer your Chart question. You might try one of the other Excel experts that claim expertise in Excel Chart. ---...
Excel: Selecting all sheets and then making them visible., banging my head, richard florida
banging my head, richard florida, good answer: Hey Dude Sorry, although I write some macros, I am not a macro expert. I suggest that you reask the question of another allexperts person. Check the resumes and I am sure you can find someone who can give you a good answer. Richard Florida USA...
Excel: Specific value returns a date & time, target value, numberformat
target value, numberformat, sheet tab: Rikin, must have been a typo. Else If should be one word The below is tested and worked for me. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = Paid Then...
Excel: Split Function, cell array, tkey
cell array, tkey, split function: Steve, I am sure you are familiar with the immediate window. I will demonstrate that split worked fine for me: s = ABC v = split(s, ;; ) ? lbound(v) 0 ? ubound(v) 0 ? v(0) ABC ss = ABC;;EFG;; v = split(ss, ;; ) ? lbound(v) 0 ? Ubound(v)...
Excel: Start & Stop activity, cumulative report, daily basis
cumulative report, daily basis, start button: There s too much unknown for me to help you completely. Do you have a workbook set up that I can look at? If so, send to me at bobumlas@hotmail.com. If not, basically the Start button can run this code: Sub STartButton() Range( A1 ).Value=now End Sub...
Excel: If Statement for dates, If Statements
If Statements: Hi Debbie, I hope this works for you: =IF(A1 B1, Expedite , OK ) See the image attached here that represents the formula provided above. Kindest Regards, Mary A. Lindsey Instructional Technologist eDoc Support LLC www.edocsupport.co...
Excel: If SUM = , then, cell a2, column labels
cell a2, column labels, circular reference: I guess I am starting to get really confused. Right now, if you re doing what I answered last, you have in column A the sum formulas. in column b you have the numbers that are getting summed. in column c, well you don t probably have anything for the...
Excel: SUMIF and AND, sumif function, sum formula
sumif function, sum formula, richard florida: Rahul No the sumifand does not work. but you can add the two together for example =sumif(a2:z45, grow ,b3:z45)+sumif(a3:z45, up ,b3:45) Or if the grow and up are in separate columns you can combine the two with and and formula like this (assume grow...
Excel: SUMPRODUCT function with #DIV/0! result, array formula, dummy data
array formula, dummy data, a31: Mike, the easiest fix it so fix the formula that shows #DIV/0! instead of have =R13/S13 change it to =if(S13=0,0,R13/S13) another way is =if(iserror(R13/S13),0,R13/S13) I don t know what you formulas in AM13:AM31, but I would adjust...
Excel: Subtracting Time in Excel, negative time, date system
negative time, date system, time system: Tawnie, I know that excel will not show a negative amount of time, but I need it to do so so you are asking for a work around? A1: 11/30/10 8:00 A2: 10/31/10 6:15 if you want the answer in hours: (times are being treated as in the morning)...
Excel: Sum, value date, column c
value date, column c, reena: Reena, I am confused by what you say. I put your data in a worksheet starting with 100.00 in A2 and 1-Aug in B2 in C2 I put in the formula =IF(SUM($A$2:A2) =2500,B2, ------ ) and in D2 =SUM($A$2:A2) to verify that you don t reach 2500 until 09-Aug...
Excel: Sum Range inside =IF(iserror, Sixthsense
Sixthsense: Hi Travis, Yes you can write the formula like the below:- =IF(A36=N56,(B36)/P56,IF(A36=N57,((B36/P57)/P56), )) But up to excel 2003 you can use only seven if functions in a cell and not more than that. So keep in mind and choose appropriate function...
Excel: Summing columns within filtered list, header row, sh1
header row, sh1, sh2: Dave This worked for me: Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet Dim r As Range, r1 As Range, cell As Range Set sh1 = Worksheets( Sheet1 ) Set sh2 = Worksheets( Sheet2 ) Set r = sh1.AutoFilter.Range Set r = r.Offset(1, 0).Resize(r.Rows.Count...
Excel: Summing non-adjacent cells based on condition, instructional technologist, thought process
instructional technologist, thought process, kindest regards: Hi Gary, I don t really understand what you are attempting to calculate; however, I have attached an image to show you what the formula represents. Here goes: Tip Total (G2 through G8 is calculated =sum(E2-B2) ). This may be redundant but I just...
Excel: Sumproduct incorpating two criteria involving same references, array formula, a1500
array formula, a1500, a13: Mike, the only thing I see wrong is in the check for 0.95, you are looking at column AL instead of column AM =SUMPRODUCT(--($A$13:$A$1500=O3),--($AI$13:$AI$1500= Active ),--(IF(ISERROR($AM$13:$AM$1500),0,$AM$13:$AM$1500) 0.9),--(IF(ISERROR($AL$13:$AL$1500),0,$AL$13:$AL$1500)...
Excel: save code to save sheet 1 and sheet 2 different, customer invoices, customer name
customer invoices, customer name, c7: This is a bit long for a simple question, but in general you can save a worksheet with the following code: Sub SaveSheet() Sheets( Sheet1 ).Copy Activesheet.Saveas Range( C7 ).Value Activeworkook.close false End Sub similarly with sheet...
Excel: scatterplots with a tricky bit, Sixthsense
Sixthsense: Thanks for sending your question my way, but as I stated in my profile, I am not proficient in Chart and therefore can not answer your Chart question. You might try one of the other Excel experts that claim expertise in Excel Chart. --- Sixthsens...
Excel: similate a typewriter return, circular windows, move selection
circular windows, move selection, windows icon: It depends what version of Excel you re using. I ll answer for Excel 2007. With your Excel workbook open, click the circular Windows icon in the very upper left corner of your screen. From the Excel Options popup, in the left column, click Advanced. Under...
Excel: summing non-adjacent columns based on condition, sumif, countif
sumif, countif, cell references: It s not quite the way I use this version of sumproduct, which is a multiple condition sumif (or countif) - but in this case I don t think that is actually needed anyway. IF I follow the question correctly, you want BD to contain the running total of tips,...
Excel: summing non-adjacent columns based on conditions, array formula, l13
array formula, l13, h13: Gary, this worked for me =SUMPRODUCT(IF((I12:BB12*(MOD(COLUMN(I12:BB12),4)=2) G12:AZ12)*(I12:BB12 )*(G12:AZ12 ),I12:BB12-G12:AZ12)) it must be entered with Ctrl+shift+enter rather than Just Enter since it is an array formula (even though you...
Excel: Time Sheet calculation in Excel 2003, time sheet, test work
time sheet, test work, textboxes: Kiran, it depends on how you want to enter the time. What you show would suggest text strings. So you would need to format you cells in columns in Start Time and End time as text. Otherwise, you will enter 0600 and will be displayed as 600. If you do...
Excel: Time sheet calculation in Excel, custom time, time sheet
custom time, time sheet, time format: Hi Kiran, To ease the calculations, make sure times are entered as times, with the : as separator between hours and minutes: 8:30 21:00 After that, you can simply subtract them to get the time spent. To make sure the totals do not roll over per...
Excel: Time sheet calculation in Excel 2003, hi bob, time sheet
hi bob, time sheet, thanks in advance: In your sample, you show 0600 - if you re really seeing a leading zero, then you ve either formatted it as text or as 0000 , neither of which is actually a time (which os OK). Please confirm that your data appears as in your example, and not 06:00 AM, for...
Excel: Time sheet calculation in Excel 2003, time sheet, cols
time sheet, cols, b2: On a new sheet, select cols A:B, and format as date, (e.g. 3/14/01 13:30). In A2:A3 enter start times, e.g. 12/20/10 6:00 12/20/10 18:00 In B2:B3 enter end times, e.g. 12/20/10 18:00 12/21/10 6:00 In C2, enter =(B2-A2)*24 and copy down...
Excel: Trying to generate multiple sheets from one sheet of information, Sixthsense
Sixthsense: Hi Oliver, First of all sorry for the delayed reply, since there was some problem in my internet connection. Thanks for sending your question my way, but as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question....
Excel: tiered bonus, logic skills, bonus structure
logic skills, bonus structure, cell references: Nisha Sometimes it is easier to put several formulas in adjacent (but not contiguous columns) to calculate each tier of the bonus and then add all of those up to get the total bonus. Thats just a hint for future reference. I did put all of your criteria...
Excel: transferring range of data from workbook, correct syntax, adequate range
correct syntax, adequate range, excel workbook: * It sounds like you might want a macro (excel VBA code) that runs (preferably, triggered when the workbook opens, and/or also triggered whenever the user wants), in the second workbook, to copy rows if the date is today s date. Actually I think there...
Excel: UNDO EXCEL OVERWRITE, windows control panel, richard florida
windows control panel, richard florida, overwrite: Lisa Thats a bummer, but we ve all done it. Sorry I don t know of a way to undo an excel overwrite short of restoring the entire computer data to a previous date. Go to the windows control panel click on system click on restore. YOu may be able to restore...
Excel: Userform autopoulting cells from a single list box, james spain, irow
james spain, irow, value application: George It should be pretty easy. How do the lists relate to each other? Where doest the data in the list boxes come from? The easiest thing to to do would probably be to have a list somewhere on the worksheet that has a column of name and an adjacent...
Excel: unique values, Sixthsense
Sixthsense: Hi Michelle, I request you, please don t post your formula and ask for solution. Elaborate your data and the result you are expecting to derive using the formula. Because elaborating your criteria and data structure will make me to provide beer solution...
Excel: using excel macros/vba to print to word, excel macros, code snippet
excel macros, code snippet, selection sort: I m not clear if this is running from Word or excel - from the creation of an excel object, it seems to be from Word - if so, The problem is the line ThisWorkbook.activate, as ThisWorkbook isn t a WORD command, and the macro is being run from there -...
Excel: using excel macros/vba to print to word, excel macros, code snippet
excel macros, code snippet, selection sort: George This is pretty complicated and I don t do Word, but, I suspect the issue has to do with ThisWorkbook. Prior to leaving the workbook and doing Word stuff, ThisWorkbook is defined. Upon return, I believe you will need to identify the Workbook with...
Excel: VBA Copy Multiple Workbooks to one Sheet, column headings, spath
column headings, spath, sname: Mike, Sub ABC() Dim sPath As String, sName As String Dim bk As Workbook, r As Range Dim r1 As Range, sh As Worksheet Dim BaseWks as Worksheet, lastrow as Long Dim bFirst as Boolean bFirst = True Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)...
Excel: VBA code to populate excel sheet with userform, excel code, assignment name
excel code, assignment name, toggle button: Hi Justin, What you describe sounds fairly straightforward. However, the whole job is too time-consuming for me to do it for you. I don t know know much of it you have already accomplished, but I assume at a minimum that you have set up the Form and the...
Excel: VBA to create worksheets with row data, count cells, cell a1
count cells, cell a1, header row: Justin I would recommend that you avoid having a sheet for each client and instead use a pivot table. That would involve having a single header row over all your client data and using pivot table functionality to see specific client data, or even to create...
Excel: VBA Excel - Repeat forumla on multiple cells, b28, d25
b28, d25, g21: Phil, Dim cell as Range for each cell in Range( G14:G21,I14:I21,B25:B28,D25:D28 ) if cell.Value 8500 and cell.Value 1700 Then cell.Value = cell.Value + 600 cell.Value = cell.Value / 2 With cell.offset(0,-1) .value...
Excel: VBA Function, vlookup function, exact response
vlookup function, exact response, visual basics: Chase, Sounds like you have a data problem. Perhaps in Data in column A of sheet Data you have 3 entered as a text value rather than a number. For the lookup and match functions, Excel sees the number 3 and the text value 3 as two different things....
Excel: VBA link to specific worksheet, mailbox folders, mail data
mailbox folders, mail data, outlook mailbox: Stuart, I ran it from Outlook 2007 and it worked fine for me - so that wouldn t be consistent with a compile error. Where are you running it from? Now looking at the code, I use mostly your original code and you had fld dimensioned as Dim fld As...
Excel: VBA looping, batches, fg
batches, fg, bakery: Serg There are ways to get data from closed files without opening the file. If the data you are retrieving is in a consistent location you might be able to avoid opening the files. If this happens a lot, you may want to consider storing the data in the...
Excel: VBA printing in Excel 2003, range v8, color sort
range v8, color sort, color application: Ken using a number in the current context would not work unless you wanted to add a loop counter For i = 1 to #of cells to print Next is what your are describing. But why not just determine if each cell has a value or not: Sub PrintNames()...
Excel: VBA printing in Excel 2003, Sixthsense
Sixthsense: Hi Ken, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim expertise in Excel VBA such as Bob Umlas,...
Excel: VBA - Rows to Columns, blank sheet, follow ups
blank sheet, follow ups, allexperts: Phil, You would have to explain the output you want. On the sheet you sent me qty has merged cells over two columns in one section and one column in other sections. Then length is the opposite. A: Quantity B: Length C: Quantity D: Length E: Quantity...
Excel: VBA - Rows to Columns, csv files, vba macro
csv files, vba macro, screen button: Phil, Received the file. So the file will already be in a workbook when you want it processed? I will write the code to do that. You have 4 categories in your input, but you only show 3 for the output - plus the output look pretty random as far...
Excel: VBA-Running Macro from Personal.xlsb in different worksheets, ogilvy
ogilvy: Justin, It sounds like the macro is designed to work on the workbook that contains the macro. Does it have references like Thisworkbook. If so, you should use references like Activeworkbook and ActiveSheet. To give you more advice than that, I would...
Excel: VBA to save files with different format, iloc, redim preserve
iloc, redim preserve, spath: Justin, I like to use the Old Dir command rather than use the scripting commands Sub Modifyfiles() Dim sName As String, sName1 As String Dim sPath As String Dim v As Variant, cnt As Long, iloc As Long Dim s As String, i As Long, bk As Workbook sPath...
Excel: VLOOKUP/DLOOKUP, Sixthsense
Sixthsense: Hi Daniel, First of all what I would suggest is have a clear picture of the function which you are trying to use. What the function will do? and definitely if you know the usage of the function then i am sure you will pick the correct function on your...
Excel: Vlookup glitch?, correct result, cell a1
correct result, cell a1, hi mike: Hi Mike, I am unable to duplicate the problem you are experiencing. But because you said you imported the data, and because manually retyping the value eliminates the problem I think the problem is most likely with the imported data itself. I suspect...
Excel: Vlookup Macro, uk india, arange
uk india, arange, cell a1: Pravee Assume your list of countries and their codes is in sheet2 cells A1 thru B100, with column A being the country and column B being the country code. Also assume your list of countries which you wish to look the country codes is in column A of another...
Excel: validation (excel 2003), visual basic programming, conditional formatting
visual basic programming, conditional formatting, data validation: Jon: By default Excel only allows one validation setting per cell. You might be able to accomplish more with visual basic programming; however, that is beyond the scope of this question group. Alternatively, you could use a combination of Conditional...
Excel: vba time format, hh mm ss, time values
hh mm ss, time values, time format: Alan, demo d from the immediate window: ? TimeValue( 00:00:01 ) 12:00:01 AM ? format(TimeValue( 00:00:01 ), hh:mm:ss ) 00:00:01 so change your code to include the format command Sub ShowBannerTime() With UserForm2.ListBox2 .MultiSelect...
Excel: I Want Formula or Function in Excell, sohail khan, cell a2
sohail khan, cell a2, salary loan: Hi Sohail, You need a combination of INDEX and MATCH, like so: Suppose the name is in cell A2 and the date you want looked up is in cell B1, your data is on Sheet-1, cells B2:D5 =INDEX( Sheet-1 !$B$2:$D$5,MATCH($A2, Sheet-1 !$A$2:$A$10,0),MATCH(B$1,...
Excel: Worksheet and Codename handling, type mismatch error, worksheet cells
type mismatch error, worksheet cells, object browser: Steve, I have a workbook with a sheet with a tab name of AAA and the codename is Sheet1 Here is a demo of how I can use these. I did this in the immediate window then copied it here ? thisworkbook.VBProject.vbcomponents( Sheet1 ).properties(...
Excel: 3-D excel sheet, dimensional table, excel sheet
dimensional table, excel sheet, suggested solution: Your described solution is 2D - what I don t follow is how it is 3D - in that, do all processes need 20 approvals? If so, you could have 20 worksheets, each with the process down the side and the action next to it - with a summary showing the total of each...
Excel: Add Input Box to Rename Sheet, blank worksheet, input box
blank worksheet, input box, microsoft excel: You didn t give me any clues about what sort of sheet needed to be added - so I ve assumed a blank worksheet Sub workbookadder() On Error GoTo retry Dim mysht As Worksheet Set mysht = Worksheets.Add reinput: mysht.Name = InputBox( Please type the name...
Excel: Adding & Subtracting to get a Balance, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, column c: The way you have it set up, E3 should be: =E2+C3-D3 Then E4 would be: =E3+C4-D4 Then E5 would be: =E4+C5-D5 It works fine for me. Just put the correct formula in E2 and AutoFill it down the column. Let me know if you have any other questions or comments....
Excel: Adding & Subtracting to get a balance, excel, sum
excel, sum: If the setup is the way that you say, you have a circular reference on your first formula. The formula being: =sum(E2-D3+C3) On cell E2 will not work as expected, as the cell is referring to itself. This is probably not the cause of the errors. This...
Excel: Auto Columns, Sixthsense
Sixthsense: One good use of Data Validation comes when there s a need to link two or more comboboxes, making them, dependant. For example, I have a list of countries (USA, Australia, England), and a list of cities. How can I make than when the user selects...
Excel: AutoFilter in Excel 2003, littlewing, chq
littlewing, chq, example food: sam The only easy way without VBA is to split the first column using the - as a separtor, ie text to columns, hopefully the other items are similar in layout, if not then it will be a little (or a lot) more difficult. You can reply to my email at richard(dot)littlewing(at)gmail(dot)com...
Excel: Autosizing tab colums, change column, data changes
change column, data changes, visual basics: I m guessing you have code disabled in your Excel application. Once you begin the beautiful world of incorporating VBA code in your excel files, you need to, (and have all yours users do this), make this change. don t believe you ve told me if you re...
Excel: Not able to perform Vlookup function, Sixthsense
Sixthsense: Its working fine for me and I wont come across this type of issue till now. I can able to perform the vlookup in all excel versions. Anyway if you are using the vlookup Lookup Range for full column like A:B then define the range like A1:B500. To make the...
Excel: adding rows to Excel 2007 pivot tables, source icon, pivot table
source icon, pivot table, table options: As long as you re in XL2007, you might as well have the source data be a Table. When it is one, then refreshing picks up the new rows. If for some reason you don t want to use a table, you can select Change Data Source icon in the Pivot Table Options tab....
Excel: addition in time stamp, Sixthsense
Sixthsense: Assume that in A1 cell you are having the data like the below:- A1 Cell 1/6/2004 15:20 Paste the below formula in B1 cell =TIME(HOUR(A1)-3,MINUTE(A1),SECOND(A1)) Result: 12:20 PM This will get you the Date from A1 cell seperately =DATE(YEAR(A1),MONTH(A1),DAY(A1))...
Excel: alpha pre-fill, data validation, control toolbox
data validation, control toolbox, macro code: David, there are three type of dropdowns/combobox options in Excel. One is the list option using data validation. There is no support for prefilling. the second is the dropdown box from the forms toolbar (or in Excel 2007, a forms control found...
Excel: auto data transfer, cell a1, sheet1
cell a1, sheet1, appointment time: Hi Cris, In the cell in Sheet2, type the following: = Sheet1!A1 With this you will see the same info that you have in Sheet1 cell A1. If you want to clear error (like #NAME), you type the following: =if( Sheet1!A1= , , Sheet1!A1) With this,...
Excel: How to block first few rows at top of sheet, page layout view, mac apps
page layout view, mac apps, excel user: Hi Brian, I would not have answered the question this way, had I known you were on a Mac. I don t really have any knowledge of how things may or may not be different. I did find these comments online though by googling: Freeze panes MAC the solution...
Excel: Calculating hours, richard florida, time type
richard florida, time type, timelapse: How about this--if A1 contains 13:00 and A2 contains 19:00 and C1 contains 17:00 The the formula =C1-A1+A2-C1 will yield 6 The cells should be formatted as time type 13:30. You can get the same result with the formula =A2-A1 Hope this...
Excel: Calculating Time So It Only Calcs In Cell If Total Not Exceeded, ogilvy, sums
ogilvy, sums, part time: Scott, Possibly. If you want to send me a sample workbook with a couple of situations where you explain to me what you want to do, then I will be better able to advise you. send it to twogilvy@msn.com don t just send your workbook - I really...
Excel: Calculating time Question, time question, allexperts
time question, allexperts, gmail: Hi Scott, I am not following your question well. It will help me to understand if you could provide an example with figures. And regarding your question: Is there a way to make excel only calculate the time for each job until the total is reached and...
Excel: Call Function and apply it for each cell, text cells, l12
text cells, l12, error text: Lurenzo, Dummy FIFO Function to demonstrate assigning a value to the function name the function should return a Text Value, a Numeric Value or an Error. Text or number is decided by the values in columns H and I in rows 2 to 12. Public Function...
Excel: Column to rows with criteria, revital, sheet1
revital, sheet1, good afternoon: Hi Revital! How are you? This can be either simple or very complicated. The simplest way is to write in column A of sheet 2 =if(Mid(Sheet1!A1,1,1)= A ,Sheet1!B1, ) Then tranpose this coumun into a row Please see transpose function. I know this...
Excel: Combination Query., allexperts, aidan
allexperts, aidan, combinations: The answer I gave at http://en.allexperts.com/q/Excel-1059/combinations-2.htm will basically give you what you want - the macro will need minor modifications to handle the numbers you want, but basically is the one you need - let me know if you need...
Excel: Combine student data in 1 row, exam id, exam scores
exam id, exam scores, valu: Assuming Student ID is in A1, you can run this procedure: Sub Rearrange() assumes Student ID is in A1 lrow = Range( B60000 ).End(xlUp).Row Range( B2:B & lrow).Copy Range( B1 ).PasteSpecial xlPasteValues, Transpose:=True n = 1...
Excel: Combining data from multiple workbooks into one worksheet, credit card payments, getopenfilename
credit card payments, getopenfilename, sheet tab: Hello Anthony, this line determines the destination: Set rng1 = sh.Cells(Rows.Count, 1).End(xlUp)(2) the 1 in Cells determines the column. So to go to column B, change the 1 to a 2. Set rng1 = sh.Cells(Rows.Count, 2).End(xlUp)(2) I have done...
Excel: Conditional Formatting in Excel 2000, conditional formatting in excel, true dates
conditional formatting in excel, true dates, simple answer: I don t know what you are doing wring since you did not say what you are actually typing in the conditional formatting boxes. Regardless, assuming the first cell that contains your due date is in A1 then to make that turn red when that date is earlier than...
Excel: Conditional formatting can be VBA, excel, conditional formatting
excel, conditional formatting, time: Hi Kevin, I looked at your spreadsheet and the conditional formatting. Instead of using a more complicated formula or VBA, it appears to me that the problem is due to poor design of the spreadsheet for the task. In your spreadsheet, each row is in 30...
Excel: Consolidation of data, kind cooperation, filefilter
kind cooperation, filefilter, true title: The error would happen only if you had no data in column A aside from the ColA in A1. I d have to see the real data to help. I d also have to see the other workbooks to see what you re trying to do in the other columns. Unfortunately, I m about to go...
Excel: Consolidation of data, kind cooperation, filefilter
kind cooperation, filefilter, true title: Your macro will open files and move the sheets - I tend to declare a variable to relate to my workbook dim MyWorkbook as workbook set myworkbook=activeworkbook which makes it certain that you are referring to the sheet you want to refer to in your...
Excel: Consolidation of data, kind cooperation, sreekanth
kind cooperation, sreekanth, filefilter: HI Sreekanth, You are close. The code below is a modification of yours, it opens each workbook you selected and copies the contant of the first sheet to the sheet Sheet1 in your workbook containing the VBA code: Sub CombineWorkbooks() Dim FilesToOpen...
Excel: Convert text to date., column format, a1
column format, a1, variables: There are so, so many ways to answer this question, and variables depending on how much text you have in what cells, how are they formatted, etc. etc. Can t you just reformat the source cell as Date? Otherwise, I coiuld consider some steps like this:...
Excel: Converting a Value in to spelling in Excel 2007, prefixes and suffixes, using ms office
prefixes and suffixes, using ms office, string place: * Well obviously whatever code or function you used, it s not something I ve ever seen before. So you wrote some code to have a UDF and use in worksheet to spell numbers? That s cool ... But I need to see ALL of the code pasted in your answer to try...
Excel: Copying rows based on multiple dates., false selection, range selection
false selection, range selection, sh1: I would tend to use something along the lines of if test1 or test2 or test3 then sheets( Due in 14 Days ).range( A & counter & :L & counter).value=sheets( Database ).range( A & rw & :L & rw).value counter=counter+1 end if to handle the setting...
Excel: Copying rows based on multiple dates., crane inspection, sh1
crane inspection, sh1, column names: Tim I will assume by days out you mean you want to copy the records that have at least one of the 3 dates in the time period between now and the specified number of days from now. Sub copyData() Dim sh As Worksheet, sh1 As Worksheet Dim r1 As Range,...
Excel: Count diffrent color cells, color cells, colorindex
color cells, colorindex, color interior: Scott, this worked for me: Function AddColor(Color As Range, ParamArray rr() As Variant) Dim Cell_Range As Range, i As Long Application.Volatile True Dim Cel As Range Dim Total For i = LBound(rr) To UBound(rr) If TypeName(rr(i)) = Range ...
Excel: Count numbers within date range, countif, direct email
countif, direct email, sumif: Before working out a formula, can I check - will the data be ONLY current year, or do we have to cater for December being less than January of the following year? Once that is known, then a series of sumif and countif statements should give you the result...
Excel: Countif query, paste values, voting system
paste values, voting system, copy paste: Works fine for me, trying to replicate your situation as best as I can. It s definitely something about the formatting of what got exported into excel, that was good checking for spaces, another thing you might try is email me the sheet at ipisors@gmail.com...
Excel: Countifs conversion, array formula, array type
array formula, array type, calculation time: Nigel, In Excel 2003 you would need to use an array type formula. Unfortunately, an array formula can t use entire columns like G:G. In Excel 2003, a worksheet can have 65536 rows, so you can use one less than an entire column: G1:G65535. =SUMPRODUCT(--($G$1:$G$65535=0),--($F$1:$F$65535=0),--($G$1:$G$65535...
Excel: Counting entries in 2 columns, vlookup formula, logical answers
vlookup formula, logical answers, cell c1: Hi Lily, I would use the vlookup formula to find your matches and then count them from there. Lets assume you have your ID numbers in column A and column B and each is 3000 long. In cell C1, =VLOOKUP(A1,$B$1:$B:$3000,1,FALSE) copy this formula down....
Excel: Create a table with macro, excel, vba
excel, vba, outlook: Hi khaled, To create a table in Outlook using Excel VBA, create a HTML mail in Outlook. Please see the following code. ------------------------------------------------------------------------ Sub New_Cars() Dim App As Object Dim item...
Excel: Creating a CSV file from an Excel form, csv file format, csv files
csv file format, csv files, data validation: Jeff, I responded via eamail with a more detailed explanation of this demo code: Sub ABC() Dim cell As Range, s As String, sFname As String Dim sh As Worksheet, v() As Variant i = 0 For Each sh In Worksheets If sh.Visible = xlSheetVisible Then...
Excel: Creating a list of matching column cells, excel demo, array formula
excel demo, array formula, commas: Argh- sorry - I spotted the error - when I first read the question I thought I saw data from row1 - which of course isn t right - I had partially fixed the formula when I copied it over but not totally - all references in the formula to ROW() should be ROW()-1...
Excel: Currency to text, when different currencies in the same sheet., string place, decimal place
string place, decimal place, string representation: Jerzy, where the code uses text strings related to dollars and where it uses text strings that are related to cents , you would need to put in code to enter the appropriate string related to your currencies. Since you want to handle multiple currencies,...
Excel: Custom function, time duration, rtime
time duration, rtime, cdbl: Hirok/Sami, I received your email. I will see what I can do. It may take a day or two as I have a lot going on right now and this should take quite a bit of time to get back up to speed on the macro and then determine how it needs to be modified. ...
Excel: Customa Function for nested if and vlookup, r2 r3, cell a2
r2 r3, cell a2, trappable error: Sami, Usage in A2 would be: =checknames(B2,A2,$H$2:$H$29,$J$2:$J$10) and the formula can be drag filled down the column. Public Function CheckNames(r1 As Range, r2 As Range, r3 As Range, r4 As Range) Dim s As Variant s = Application.WorksheetFunction.IfError(...
Excel: calendar in excel, w1, cheers
w1, cheers, r1: Hi Tqah How are you? This is not the solution for your problem, but I hope it helps. 1. After opressing the date on the calendar and open the corresponding file you have to activate this file. It may look someting like: Sub filing1() Dim W As Workbook,...
Excel: cell ref. within a formula, c 700, referenc
c 700, referenc, cell address: Gord Say you had M1: Jan. N1: Feb. O1: Mar. and so forth. say this formula is in M2 then it could be =averageifs($j$2:$j$700,$b$2:$b$700,M$1,$c$2:$c$700,2010,$d$2:$d$700,3) and when you drag fill it to N1 and beyond, the M$1 will...
Excel: cell reference, cell reference, cells
cell reference, cells: you macro seems to just be a find macro, which I m not sure is what you want - if it is, I d just press Ctrl F and type in the value I wanted - quicker than running a macro. Knowing the rest of the process would help as I could suggest a method of doing what...
Excel: checkboxes, gayatri, checkbox
gayatri, checkbox: Dear Gayatri, Could you please send me your form to ariful.mondal [a] gmail.com I would do the necessary changes for you. Example: If Sheet1.CheckBox1.Value = True Then MsgBox True Else MsgBox False End If Please change the Sheet1...
Excel: "close fit" formula for an row of data, Sixthsense
Sixthsense: sort your data in ascending order and use Vlookup and in [Range Lookup] mention it as 1 instead of 0. you can use the Lookup function also for retrieving the result. You can use ABS function to ignore the Positive and Negative sign. I can able to give...
Excel: code available for all excel document, addin, excel workbook
addin, excel workbook, information table: Hiram Salazar, If you design your code to act on the activesheet, it can be in any open workbook. Most people put code like this in the personal.xls or personal.xlsm if you are in Excel 2007. You get a personal.xls by recording a macro and letting excel...
Excel: color the value, format tab, format menu
format tab, format menu, a3: Dear Eran, You would need to use conditional formating 1. Select Column or Cell A3 2. Click Format on Tool Bar 3. Click on Conditional Formating on the Format Menu 4. Select condition 1 Cell Value Is + equal to + 0 5. Click...
Excel: two columns, excel, sumif
excel, sumif, sum: Hi Ossi, Use the SUMIF function. If the first column list {A B C C C B B A A C} is in A1:A10 and the second list {1 2 2 3 2 1 2 2 1 2} is in B1:B10, use the following formula. =SUMIF(A1:A10, A ,B1:B10) Let me know if it works for you. Thanks....
Excel: combination formula for single row, free microsoft excel, going in circles
free microsoft excel, going in circles, excel tips tricks: Just place the percentage in a cell somewhere on your sheet, let s say cell K1, and then you can refer to it like this: =D23*I23-(H23*$K$1) That s called an Absolute Reference, and the dollar $ signs so it doesn t change when you Autofill it down. ...
Excel: combine two VBA codes, outlook application, code bits
outlook application, code bits, rng: The easiest way to combine the two is to write a third routine that calls each one, like: Sub RunBoth() Call mailbanking1 Call sendtoHO End Sub and then just run RunBoth. I suggest you contact the person who wrote these rountines for you to explain...
Excel: completing of code..., g26, option c
g26, option c, l6: Hi Ellis, Like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim R As Range Set R = ActiveSheet.Range( G6:G26,L6:L26,Q6:Q26 ) For Each c In R If c = N/A Then MsgBox This option is not available at the centre you have...
Excel: conditional formatting, free microsoft excel, conditional formatting
free microsoft excel, conditional formatting, ready crops: In Excel 2000 you can t format with conditional formatting based on a date being greater than or less than another date (or the current time). What you COULD do, however, would be to make another column that has a TRUE or FALSE value in it, which could be...
Excel: conditional IF formula, conditional if statement
conditional if statement: Dear Daniel, Are you looking for something like this in cell A4: =IF(A1= ,IF(A2= ,A3,A2),A1) - if A1 is blank, then will verify whether A2 is also blank, if A2 is not blank, then it will return A2, else it will return A3 Please let me know,...
Excel: connecting multiples row into one row, vba macro, gmail
vba macro, gmail, allexperts: Hi Luong, I do not know of a simple way other than to cut-paste the values. If it s really needed, we could certainly use a VBA macro. I will need your spreadsheet to know the layout and format before doing that. Thanks. Regards, Victor LAN victorlan.allexperts@gmail.co...
Excel: continuing question, hh mm ss, g7 g8
hh mm ss, g7 g8, conditional formatting: Hi Kevin, The formula I provided will work if you manually change it to adapt to each cells as required. I do not know the exact formula that you will use, as the original spreadsheet was not propertly designed and perhaps not suitable for the task. ...
Excel: how to count a coloured cell?, interior color, rg
interior color, rg, udf: Assuming you re talking about the FONT (not the pattern), then you can use a user-defined function to see if there s been any color applied. Press Alt/F11, use Insert/Module, enter this: Function color(rg As Range) As Boolean color = (rg.Font.color ...
Excel: Data from Excel to Notepad, row 24, a24
row 24, a24, a21: Glad to help hey, I know it is quite a lot of rows you have, I think there might be a VBA formula to speed the whole thing up, but I don’t know VBA yet. I do have another option for you, will take a bit of time but not a day. Section 1: Firstly:...
Excel: Data int Excel from notepad, notepad file, import data
notepad file, import data, semicolon: Dear EBony, Could you please send me the notepad at ariful.mondal [a] gmail.com Follow the below steps: 1. On excel tool bar click on Data 2. Select import External data 3. Click on Import Data 4. Choose as All Data Sources as File Type 5. Select...
Excel: Data Matching, excel, match
excel, match, wildcard: Hi Tadad, You can use the MATCH function with a wildcard inside a INDEX function to return the value in column B of Sheet-2. Example: =INDEX(Sheet2!B:B,MATCH(LEFT(A1,8)& * ,Sheet2!A:A,0),1) where: A1 contains 192.168.1.2 in ColumnA of Sheet-1....
Excel: Data Validation, data validation, number thanks
data validation, number thanks, delayed response: Hi Kelvin, Apologies for the delayed response. When a validation has been created, Excel will not automatically replace the corresponding values. A simple solution to the problem may be to use VLOOKUP function to get the part number. Thanks and...
Excel: Date Format in Excel, Sixthsense
Sixthsense: For converting Numbers to Text:- Assume that you are having the below value in A1 cell A1 cell 20100124 Paste this formula in B1 cell B1 cell =VALUE(MID(A1,5,2)& / &RIGHT(A1,2)& / &LEFT(A1,4)) Place the cursor in B1 cell Do Right Click Format cells...
Excel: Date stamp needed with referance to another Cell content, excel, timestamp
excel, timestamp, vba: Hi Lejo, The below code will do what you wanted. ------------------------------------------------------------------------------ Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Columns.Count...
Excel: Develop Database using excel (macro&vba), john walkenbach, bill jelen
john walkenbach, bill jelen, excel macro: If you re new to Excel & VBA, you re really not the guy to be given this task. There is not really a fast way to learn this stuff. You might look at http:///www.datapigtechnologies.com and search in the Excel section, Userforms. I highly recommend either...