Search Results

Search found 6455 results on 259 pages for 'excel vba guy'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 80/259 | < Previous Page | 76 77 78 79 80 81 82 83 84 85 86 87  | Next Page >

  • Is there any way to properly display negative time spans in Excel?

    - by Pepor
    Is there any way to make Excel show a negative time span? If I subtract two time values (say, when subtracting the actual amount of time spent on something from the amount of time planned for it) and the result is negative, Excel just fills the result cell with hashes to notify me that the result cannot be displayed as a time value. Even OpenOffice.org Calc and Google Spreadsheets can display negative time values. Is there a way to work around that issue by using conditional formatting? I really don't want to create some workaround by calculating the hours and minutes myself or anything like that.

    Read the article

  • How do I combine data from multiple rows in excel to one cell?

    - by Steve
    I have a list of product skus in one column in excel. I have thousands of these skus that need to be combined in one cell separated by commas with no spaces. There are too many rows of data to use the concatenate function. Not sure how to get this done. Here's an example of what I'm working with but with 6,000+ more rows. I'm using Excel 2003. A 140-12 1074-156 903-78 876-65 349-09 986-43 237-12 342-11 450-187 677-133

    Read the article

  • How do I completely turn off Excel 2010 autoformatting?

    - by Samuel
    I am using a lot of csv files at work with excel 2010. These have no formatting so Excel 2010 autoformats all the cells. I've found workarounds but the ones I have found require action for each file or each cell (i.e. adding a single quote). My current workaround is using the "show formulas" option under formula auditing in the formulas tab. This seems to show the raw data (since they are just csv files there aren't formulas). If I could just keep this active so I don't have to turn it on.

    Read the article

  • Is there a way to hide a row or column in excel without using VBA?

    - by AJ
    I know of several approaches using a macro (VBA) to show/hide columns and rows in Excel, but I cannot figure out or find a way to do this using either a formula or conditional formatting. Of particular interest is Excel 2007 - but I'd be curious to know if someone has managed to do it in any version of Excel. For those who want background, I have a spread of data with dates across the top and labels down the first column. I would like to specify a date window (on another sheet) as two cells with drop down dates (months) which would then show/hide the appropriate columns on the data sheet.

    Read the article

  • Bring Office 2003 Menus Back to 2010 with UBitMenu

    - by Matthew Guay
    Are you having trouble getting used to the Ribbon interface in Office 2010?  Here’s how you can roll back the clock a bit and bring back the familiar menus and toolbars from 2003. The Office 2007 Ribbon was both praised and criticized.  While many users felt they were more productive with the new interface, others felt frustrated searching for commands they had memorized in older versions of Office.  Now, with Office 2010, the ribbon interface has been brought to every app in the Office suite, and is integrated into many newer programs from Microsoft. If you’re moving from Office 2003, using UBitMenu allows you to add the old familiar menus back along with the new Ribbon interface for an easier learning curve. Also, with the customizability of Office 2010, we can strip away the extra Ribbon tabs to make it more like 2003. Get the 2003 Menus and Toolbars Back in Office 2010 Download UBitMenu (link below), and install as normal.  Make sure all of your Office programs are closed during the installation.  This handy utility is very small, and installed amazingly quick. Open Word, Excel, or PowerPoint and there’s now a new Menu tab beside Home in the Ribbon.  Now you can access all of your favorite old Office commands in the familiar menus, and access many of the newer Office features such as SmartArt.   Here’s a close-up of the toolbar.  Notice that the layout is very similar to that of Word 2003. You can access all of the new Transitions in PowerPoint 2010 from the menu bar.   The menu in Excel even included support for the new PivotTable and PivotCharts Wizard. One problem we noticed was that the toolbars were condensed to a drop-down menu if the Office window was less than 870px wide.  This may be a frustration to users with low-resolution displays, and you might want to use the Office Apps maximized. Get Rid of the Ribbon Now that you’ve got the old menus back, you can get rid of the extra ribbon tabs if you’d like.  Office 2010 lets you customize your ribbon and remove tabs, so let’s get rid of all the other tabs except for our new Menu tab.  In our example we’re using Word, but you can do it in Excel or PowerPoint the same way. Click the File tab and select Options. Alternately, in the Menu tab, select Tools and then Word Options. Select Customize Ribbon on the left sidebar, then uncheck the boxes beside all the ribbon tabs you want to hide on the right.  Click Ok when you’re finished. While you’re at it, you can change the default color scheme as well. Note: The color change will automatically change the color scheme in all of the Office apps, so you’ll only need to do that once. Now the ribbon only has 2 tabs…the File tab for the new Backstage View, and the UBitMenu tab we just installed.  It almost has the appearance Word 2003, but with the new features of Word 2010!  You’ll need to repeat these steps in Excel and PowerPoint if you want to customize their ribbon the same.   Conclusion If you’ve been having a hard time getting used to Office 2010, UBitMenu is a great way to get familiar with the new interface, or simply stay productive with your old tricks.  We do wish it supported the other Office applications like OneNote and Outlook. That doesn’t make it a deal breaker though, it can make the learning curve easier in Word, PowerPoint, and Excel. UBitMenu is free for personal use, and available at a very reasonable price for businesses. If you’re using Office 2007 and not a fan of the Ribbon, UBitMenu works for it as well. Download UBitMenu Similar Articles Productive Geek Tips How To Bring Back the Old Menus in Office 2007Upgrade Office 2003 to 2010 on XP or Run them Side by SideHow to Find Office 2003 Commands in Office 2010Make Word 2007 Always Save in Word 2003 FormatMake Excel 2007 Always Save in Excel 2003 Format TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window Latest Software Reviews Tinyhacker Random Tips HippoRemote Pro 2.2 Xobni Plus for Outlook All My Movies 5.9 CloudBerry Online Backup 1.5 for Windows Home Server Speed Up Windows With ReadyBoost Awesome World Cup Soccer Calendar Nice Websites To Watch TV Shows Online 24 Million Sites Windows Media Player Glass Icons (icons we like) How to Forecast Weather, without Gadgets

    Read the article

  • SumProduct over sets of cells (not contiguous)

    - by Craig
    I have a total data set that is for 4 different groupings. One of the values is the average time, the other is count. For the Total I have to multiply these and then divide by the total of the count. Currently I use: =SUM(D32*D2,D94*D64,D156*D126,D218*D188)/SUM(D32,D94,D156,D218) I would rather use a SumProduct if I can to make it more readable. I tried to do: =SUMPRODUCT((D2,D64,D126,D188),(D32,D94,D156,D218))/SUM(D32,94,D156,D218) But as you can tell by my posting here, that did not work. Is there a way to do SumProduct like I want? Thoughts, Answers, Questions, Comments? Craig

    Read the article

  • Guessing Excel Data Types

    - by AjarnMark
    Note to Self HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel: TypeGuessRows = 0 means scan everything. Note to Others About 10 years ago I stumbled across this bit of information just when I needed it and it saved my project.  Then for some reason, a few years later when it would have been nice, but not critical, for some reason I could not find it again anywhere.  Well, now I have stumbled across it again, and to preserve my future self from nightmares and sudden baldness due to pulling my hair out, I have decided to blog it in the hopes that I can find it again this way. Here’s the story…  When you query data from an Excel spreadsheet, such as with old-fashioned DTS packages in SQL 2000 (my first reference) or simply with an OLEDB Data Adapter from ASP.NET (recent task) and if you are using the Microsoft Jet 4.0 driver (newer ones may deal with this differently) then you can get funny results where the query reports back that a cell value is null even when you know it contains data. What happens is that Excel doesn’t really have data types.  While you can format information in cells to appear like certain data types (e.g. Date, Time, Decimal, Text, etc.) that is not really defining the cell as being of a certain type like we think of when working with databases.  But, presumably, to make things more convenient for the user (programmer) when you issue a query against Excel, the query processor tries to guess what type of data is contained in each column and returns it in an appropriate manner.  This is all well and good IF your data is consistent in every row and matches what the processor guessed.  And, for efficiency’s sake, when the query processor is trying to figure out each column’s data type, it does so by analyzing only the first 8 rows of data (default setting). Now here’s the problem, suppose that your spreadsheet contains information about clothing, and one of the columns is Size.  Now suppose that in the first 8 rows, all of your sizes look like 32, 34, 18, 10, and so on, using numbers, but then, somewhere after the 8th row, you have some rows with sizes like S, M, L, XL.  What happens is that by examining only the first 8 rows, the query processor inferred that the column contained numerical data, and then when it hits the non-numerical data in later rows, it comes back blank.  Major bummer, and a real pain to track down if you don’t know that Excel is doing this, because you study the spreadsheet and say, “the data is RIGHT THERE!  WHY doesn’t the query see it?!?!”  And the hair-pulling begins. So, what’s a developer to do?  One option is to go to the registry setting noted above and change the DWORD value of TypeGuessRows from the default of 8 to 0 (zero).  Setting this value to zero will force Jet to scan every row in the spreadsheet before making its determination as to what type of data the column contains.  And that means that in the example above, it would have treated the column as a string rather than as numeric, and presto! your query now returns all of the values that you know are in there. Of course, there is a caveat… if you are querying large spreadsheets, making Jet scan every row can be quite a performance hit.  You could enter a different number (more than 8) that you believe is a better sampling of rows to make the guess, but you still have the possibility that every row scanned looks alike, but that later rows are different, and that you might get blanks when there really is data there.  That’s the type of gamble, I really don’t like to take with my data. Anyone with a better approach, or with experience with more recent drivers that have a better way of handling data types, please chime in!

    Read the article

  • PowerShell: Read Excel to Create Inserts

    - by BuckWoody
    I’m writing a series of articles on how to migrate “departmental” data into SQL Server. I also hold workshops on the entire process – from discovering that the data exists to the modeling process and then how to design the Extract, Transform and Load (ETL) process. Finally I write about (and teach) a few methods on actually moving the data. One of those options is to use PowerShell. There are a lot of ways even with that choice, but the one I show is to read two columns from the spreadsheet and output statements that would insert the data using a stored procedure. Of course, you could re-write this as INSERT statements, out to a text file for bcp, or even use a database connection in the script to move the data directly from Excel into SQL Server. This snippet won’t run on your system, of course – it assumes a Microsoft Office Excel 2007 spreadsheet located at c:\temp called VendorList.xlsx. It looks for a tab in that spreadsheet called Vendors. The statement that does the writing just uses one column: Vendor Code. Here’s the breakdown of what I’m doing: In the first block, I connect to Microsoft Office Excel. That connection string is specific to Excel 2007, so if you need a different version you’ll need to look that up. In the second block I set up a selection from the entire spreadsheet based on that tab. Note that if you’re only after certain data you shouldn’t get the whole spreadsheet – that’s just good practice. In the next block I create the text I want, inserting the Vendor Code field as I go. Finally I close the connection. Enjoy! $ExcelConnection= New-Object -com "ADODB.Connection" $ExcelFile="c:\temp\VendorList.xlsx" $ExcelConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;` Data Source=$ExcelFile;Extended Properties=Excel 12.0;") $strQuery="Select * from [Vendors$]" $ExcelRecordSet=$ExcelConnection.Execute($strQuery) do { Write-Host "EXEC sp_InsertVendors '" $ExcelRecordSet.Fields.Item("Vendor Code").Value "'" $ExcelRecordSet.MoveNext()} Until ($ExcelRecordSet.EOF) $ExcelConnection.Close() Script Disclaimer, for people who need to be told this sort of thing: Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • How to create Automation Add In Formula/Function and Excel Add In buttons (vsto) for them together?

    - by ticky
    Ok, let me explain it little bit better. Here is one example how to create formula/functions http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx?PageIndex=1#comments I implemented something like that, I even added values in registry, so that this Automation AddIn doesn't have to be added manually in Excel, but automatically.. I created SETUP project for this project and it works GREAT. Then.. After some time, I wanted to create buttons in Excel for functions that I use. Those are custom functions, using some web services. I created Excel AddIn and added Ribbon with buttons - one button = one custom function. I can publish this project and I am creating VSTO, so this way, I can install excel ribbon buttons in custom group of mine. Now, I have 2 installations, first for Automation AddIn and second for Excel AddIn. How can I connect them? I tried to include VSTO to Setup - something like this: [I WILL ADD IT LATER] When I install it, it works great, it installs both parts. But when I install on my friends computer, it doesn't shows Ribbon buttons. What could be the problem? If there is some other way to integrate those two, I would be very grateful!!!!! Thanks! Tijana

    Read the article

  • alternate way to read excel files without providing "network service" security.

    - by Jebli
    Hi, I am using a web application to read the excel file and uploading it in the database. I have deployed the application in the server.I am using windows service 2003 as the server.I am able to read the excel file only if i set the set the security settings to "network service" for the microsoft excel application in the control panel -- administrative tools -- componetn service -- computer -- My computer -- DCOM config -- microsoft excel application . Why is it so . There are also other applications running in the server. 1) Doing like this will create any issue ? 2) Why do we need to do it ? 3) Is there any alternative way to do it ? 4) What are we doing actually by providing network service access ? Please help

    Read the article

  • How can Excel 2007 / 2010 consume a REST web service?

    - by jallen
    What options exist to consume a REST web service from within Excel 2007 / 2010? I can use XML Maps to consume a basic XML list, but that doesn't let me build a dynamic URL (so I could include parameters). For example, I can add an XML Map to Excel for http://machine/service/level/5 and display the values in the workbook just fine - no problem there. The real question is, how can I dynamically change the /5 part of the URL to come from another cell in excel? That way I can have a couple of cells that have the options (what ID, what name, etc.) and whenever those values change (ideally) a new dynamic URL would be constructed and the XML map would be refreshed. Is such a thing possible? Does anyone else have a better way to take some parameters, call a web service (REST or SOAP, I'm not picky) and shove the results back into excel for further manipulation?

    Read the article

  • How to handle the Date column of excel sheet in C# code?

    - by Lalit
    Hi I am reading the Excel sheet in c# using interop services. My excel sheet have the date columns of formate dd/mm/yyyy. while reading , runtime i am getting some different values for each cell as :38840,38808,39873...so n so... what does that mean? please help.How to handle the date columns of the excel sheet in C#. please guide me.

    Read the article

  • what is this value means 1.845E-07 in excel ?

    - by Lalit
    Hi , i am reading the excel sheet from c# by using interop services. My sheet have one of cell value as 0.00. but run time when i checking the value of that cell in c# code I am getting "1.845E-07" this value. When i check in excel sheet, on that cell right clicked , say format cell I got "1.845E-07" value in sample section. How to get exact value? Please help me. code is huge, so i can't provide it here . that line is: if (Convert.ToString(((Excel.Range)worksheet.Cells[iRowindex, colIndex_q10]).Value2) != string.Empty) { drRow[dtSourceEXLData.Columns[constants.Floor]] = ((Excel.Range)worksheet.Cells[iRowindex, colIndex_q10]).Value2.ToString(); }

    Read the article

  • How can I determine new & previous cell value on SheetChange event in Excel?

    - by Falco Foxburr
    I have some special cells in my Excel workbooks which are managed by my Excel Add-in. I want to prevent users from changing content of those cells, but I also want to know, what value users wanted to enter to those cells. On the SheetChange event I can check what users entered to my special cells, but how do I determine the PREVIOUS value in those cells and REVERT user changes? It is not a solution for me. If I lock cell in Excel, it becomes read-only - user can not even try to enter anything to this cell - Excel popups warning dialog in this case. My problem is that I want to catch what user entered to my cell, do something with this value, and then revert cell content to original value.

    Read the article

  • simplifying a =Mid() equation

    - by JT.
    lets say i want to test if the first letter in cell A1 is an "A" =Mid(A1, 1, 1)="A" Now lets say i want to find out if either the first and fourth letters in cell A1 is an "A" I would of thought you could something like this: =Mid(A1, or(1,4), 1)="A" Instead of having to do this: =IF(MID(A1,1,1)="A",TRUE,IF(MID(A!,4,1)="A",TRUE,FALSE)) Am i on the right track? Could i make the above Formula simpler? If not, why not?

    Read the article

  • How can I perform a reverse string search in Excel without using VBA?

    - by e.James
    I have an Excel spreadsheet containing a list of strings. Each string is made up of several words, but the number of words in each string is different. Using built in Excel functions (no VBA), is there a way to isolate the last word in each string? Examples: Are you classified as human? - human? Negative, I am a meat popsicle - popsicle Aziz! Light! - Light!

    Read the article

  • ODBC and Excel (2 replies)

    Hello, I am using the following connection to Query and Excel Spreadsheet: AConnectionString &quot;Driver {Microsoft Excel Driver (*.xls)};DriverId 790;Dbq &quot; &amp; ofdSelectFile.FileName &amp; &quot;;DefaultDir c:\;&quot; ASourceConnection New Odbc.OdbcConnection(AConnectionString) Dim ADataAdapter as new odbc.odbcDataAdapter(&quot;SELECT * FROM $Sheet1&quot;, ASourceConnection) ADataAdapter.Fill(MyDataset) This works Great, howe...

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 76 77 78 79 80 81 82 83 84 85 86 87  | Next Page >