Search Results

Search found 4783 results on 192 pages for 'excel vba'.

Page 42/192 | < Previous Page | 38 39 40 41 42 43 44 45 46 47 48 49  | Next Page >

• Creating An OLEDB Connection From Excel To SQL

  - by Soo

  I am looking to run SQL queries using VBA code in an Excel file. It may sound like a bad way to do things, but the purpose of this is to support legacy functionality on a project I'm working on. I figured out how to create an ODBC connection, but it requires several steps which may be troublesome to implement on many computers, so I'm looking into the possibility of using OLEDB to get the job done. My question is how to go about setting things up so I can run SQL queries in Excel using VBA.

  Read the article

• Excel "Remove Duplicates" feature does not remove all duplicates

  - by raph82

  When the Data ToolsRemove Duplicates command is used on the following list, some duplicates are not removed: Key 46509 46509 51442 51442 73367 73367 80048 80048 88396 88396 102576 102576 108095 108095 118613 118613 127224 127224 137549 137549 140164 140164 151679 151679 152975 152975 154986 154986 167715 167715 169166 169166 170427 170427 176303 176303 187038 187038 199856 199856 206589 206589 225371 225371 229767 229767 230680 230680 245333 245333 253697 253697 256002 256002 262094 262094 273060 273060 275584 275584 285569 285569 301930 301930 304993 304993 305965 305965 307299 307299 311438 311438 314461 314461 340545 340545 344517 344517 355035 355035 358387 358387 359982 359982 363904 363904 368642 368642 378666 378666 378744 378744 382396 382396 393542 393542 394320 394320 395258 395258 402047 402047 421796 421796 427972 427972 442651 442651 442654 442654 445786 445786 447124 447124 456682 456682 467303 467303 470437 470437 476340 476340 480976 480976 481450 481450 498967 498967 516068 516068 517592 517592 522621 522621 524678 524678 529405 529405 532909 532909 535096 535096 540835 540835 553197 553197 566893 566893 567730 567730 570751 570751 572259 572259 581701 581701 589753 589753 597923 597923 602282 602282 612011 612011 643774 643774 649471 649471 654159 654159 661681 661681 680578 680578 709714 709714 728036 728036 728680 728680 740252 740252 750843 750843 763430 763430 777724 777724 777870 777870 779703 779703 787034 787034 789776 789776 793859 793859 798479 798479 812410 812410 816599 816599 820169 820169 821236 821236 833511 833511 833701 833701 845925 845925 854408 854408 882853 882853 891710 891710 906251 906251 929802 929802 982039 982039 984503 984503 984539 984539 987889 987889 997188 997188 1002869 1002869 1018493 1018493 1020735 1020735 1028780 1028780 1032460 1032460 1047306 1047306 1052136 1052136 1054482 1054482 1062633 1062633 1066509 1066509 1069500 1069500 1075339 1075339 1082282 1082282 1100206 1100206 1105407 1105407 1111409 1111409 1112175 1112175 1112407 1112407 1113400 1113400 1117243 1117243 1121732 1121732 1152461 1152461 1153727 1153727 1157252 1157252 1163384 1163384 1195324 1195324 1197373 1197373 1201828 1201828 1202786 1202786 1216328 1216328 1223541 1223541 1225848 1225848 1228595 1228595 1239281 1239281 1264378 1264378 1267348 1267348 1291510 1291510 1309891 1309891 1321106 1321106 1322886 1322886 1327690 1327690 1344145 1344145 1346250 1346250 1352350 1352350 1357920 1357920 1363386 1363386 1365046 1365046 1370383 1370383 1376666 1376666 1383834 1383834 1414472 1414472 1418967 1418967 1419811 1419811 1421587 1421587 1432639 1432639 1442854 1442854 1455236 1455236 1461896 1461896 1466376 1466376 1477670 1477670 1480154 1480154 1481801 1481801 1498209 1498209 1504776 1504776 1511904 1511904 1017854_447 1017854_447 1085496_19 1085496_19 1280601_77 1280601_77 1350662_135 1350662_135 141944_382 141944_382 1525893_483 1525893_483 455968_177 455968_177 469495_157 469495_157 641583_74 641583_74 704267_63 704267_63 709992_199 709992_199 819399_224 819399_224 832188_358 832188_358 857189_467 857189_467 898328_334 898328_334 91033_245 91033_245 960992_341 960992_341 999706_361 999706_361 BLA1048669 BLA1048669 BLA1052457 BLA1052457 BLA1055176 BLA1055176 BLA1060585 BLA1060585 BLA1067125 BLA1067125 BLA107601 BLA107601 BLA1087509 BLA1087509 BLA1097390 BLA1097390 BLA1122471 BLA1122471 BLA1126436 BLA1126436 BLA112694 BLA112694 BLA1148652 BLA1148652 BLA1149504 BLA1149504 BLA1155256 BLA1155256 BLA1222348 BLA1222348 BLA1225575 BLA1225575 BLA1227779 BLA1227779 BLA124071 BLA124071 BLA1247411 BLA1247411 BLA1258830 BLA1258830 BLA1281625 BLA1281625 BLA128165 BLA128165 BLA1305848 BLA1305848 BLA1307751 BLA1307751 BLA130926 BLA130926 BLA1334270 BLA1334270 BLA1335674 BLA1335674 BLA1340102 BLA1340102 BLA1345080 BLA1345080 BLA1353398 BLA1353398 BLA1369834 BLA1369834 BLA1424977 BLA1424977 BLA142669 BLA142669 BLA1429760 BLA1429760 BLA1443592 BLA1443592 BLA1447085 BLA1447085 BLA1453639 BLA1453639 BLA1456355 BLA1456355 BLA1458745 BLA1458745 BLA148663 BLA148663 BLA1494220 BLA1494220 BLA1522880 BLA1522880 BLA152562 BLA152562 BLA1548628 BLA1548628 BLA182644 BLA182644 BLA186707 BLA186707 BLA188073 BLA188073 BLA199127 BLA199127 BLA207148 BLA207148 BLA209939 BLA209939 BLA232119 BLA232119 BLA240725 BLA240725 BLA267275 BLA267275 BLA303829 BLA303829 BLA346044 BLA346044 BLA348268 BLA348268 BLA367863 BLA367863 BLA386897 BLA386897 BLA390799 BLA390799 BLA415724 BLA415724 BLA425291 BLA425291 BLA427093 BLA427093 BLA462355 BLA462355 BLA46821 BLA46821 BLA477637 BLA477637 BLA501111 BLA501111 BLA522126 BLA522126 BLA530906 BLA530906 BLA576525 BLA576525 BLA639361 BLA639361 BLA644530 BLA644530 BLA65754 BLA65754 BLA670003 BLA670003 BLA687525 BLA687525 BLA709367 BLA709367 BLA714281 BLA714281 BLA719328 BLA719328 BLA736457 BLA736457 BLA759669 BLA759669 BLA76056 BLA76056 BLA767666 BLA767666 BLA780853 BLA780853 BLA793029 BLA793029 BLA796639 BLA796639 BLA803743 BLA803743 BLA815298 BLA815298 BLA828243 BLA828243 BLA864030 BLA864030 BLA864110 BLA864110 BLA882892 BLA882892 BLA885665 BLA885665 BLA897478 BLA897478 BLA903861 BLA903861 BLA91627 BLA91627 BLA925523 BLA925523 BLA928243 BLA928243 BLA932395 BLA932395 BLA935341 BLA935341 BLA940705 BLA940705 BLA966386 BLA966386 A message is displayed indicating 293 duplicate values were removed and 307 unique values remain. 7 duplicates are not removed: 1280601_77 1525893_483 455968_177 469495_157 641583_74 704267_63 709992_199 The initial list consist of Excel-ordered values of: numbers between 40,000 and 1,500,000 numbers in the same range with and added _ and a 1-999 suffix numbers in the same range prefixed with BLA Each value is present exactly twice. There are no formula involved. I already checked for extra spaces. Immediately reapplying the Remove Duplicates command does not remove the remaining duplicate values. Reapplying the Remove Duplicates command after a reverse-sort seems to remove the remaining duplicates. Reverse-sorting before using the first Remove Duplicates changes the result (213 duplicate values removed and 387 unique values remained), only in the non-suffixed non-prefixed group. Can you reproduce this bug in Excel 2007 like I do? What triggers it? Could it the composite (numbers and text) data set? I'm not looking for alternatives. I want to know when not to use the feature and revert to already known (Excel 2003 era) alternatives.

  Read the article

• Can JSON be made easily and safely editable by the non-technical Excel crowd?

  - by glitch

  I'm looking for a data storage format that's very intuitive and easy to edit. It should be ideally targeted towards the same crowd as Excel. At the same time I would like the data structure to be a tree. Ideally this would be JSON, since it offers both the tree aspect and allows for more interesting constructs like arrays. That and parsing libraries for JSON are ubiquitous, so I don't have to reinvent the wheel. The problem is that, at least with a non-specialized text editor, JSON is a giant pain to edit for a non-technical user. I'm thinking along the lines of someone who might have used Excel in the past, but never a real text editor. Someone who might not be comfortable with the idea of preserving JSON syntax by hand. Are there data formats out there that would fit this profile? I'd very much prefer this to be a JSON actually, but then it would require a solid editing tool that would hide the underlying implementation from the user. Think Excel and how it abstracts CSV syntax from the user. The reason I'm looking for something like this is because the team has been working with pretty hierarchical data for a while now and we've hit the limits of how easy it is to represent in simple CSVs without having to create complex rules for how represent hierarchy semantics from each row. Any suggestions?

  Read the article

• Why can't set cast an object from Excel interop?

  - by AngryHacker

  Trying to get a reference to the worksheets (using Excel interop): Excel.Application xl = new Excel.ApplicationClass(); Excel.Workbooks xlWorkBooks = xl.Workbooks; Excel.Workbook xlWorkBook = xlWorkBooks.Open(fileName, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Excel.Worksheets xlWorkSheets = (Excel.Worksheets) xlWorkBook.Worksheets; // crashes The error is that it cannot cast it: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Worksheets'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208B1-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). Is my cast incorrect?

  Read the article

• How to check whether a excel file is write protected or not in C#?

  - by Pavan Navali

  Hi, I'm developing a sample application in which I have to open an excel file and check whether the file is write protercteed or not. The code is using System.Windows.Forms; using Microsoft.Office.Core; private void button1_Click(object sender, EventArgs e) { string fileNameAndPath = @"D:\Sample\Sample1.xls"; // the above excel file is a write protected. Microsoft.Office.Interop.Excel.Application a = new Microsoft.Office.Interop.Excel.Application(); if (System.IO.File.Exists(fileNameAndPath)) { Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass(); // create the workbook object by opening the excel file. app.Workbooks.Open(fileNameAndPath,0,false,5,"","",true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,"\t",false, true, 0,false,true,0); Microsoft.Office.Interop.Excel._Workbook w = app.Workbooks.Application.ActiveWorkbook; if (w.ReadOnly) MessageBox.Show("HI"); // the above condition is true. } } I would like know whether the file is write protected or not.

  Read the article

• A good documentation/tutorial for Excel PivotTables object model

  - by Patrick Honorez

  Do you know of any good doc on Excel PivotTables object model ? I have read "Excel 2007 Programmers reference", googled a bit, and found small bits here and there, but nothing global, coherent and complete. Preferably for version < 2007, so I keep upwards compatibility when developing. Thanks !

  Read the article

• Excel techniques for perfmon csv log file analysis

  - by Aszurom

  I have perfmon running against several servers, where I'm outputting to a .csv file data like CPU %time, memory bytes free, hard disk I/O metrics like s/write and writes/s. The ones graphing the SQL servers are also collecting SQL stats. The web servers are collecting .Net relevant stuff. I am aware of PAL, and used it as a template of what data to capture based on server type actually. I just don't think the output it generates is detailed or flexible enough - but it does a pretty remarkable job of parsing logs and making graphs. I'm borderline incompetent with Excel, so I'm hoping to be directed to some knowledge of how to take a perfmon output .csv and mine it in Excel to produce some numbers that are meaningful to me as a sysadmin. I could of course just pick a range of data and assemble a graph out of that and look for spikes and trends, but I'm convinced there is some technique to this that makes it more manageable than looking at a monsterous spreadsheet of numbers and trying to make graphs of it. Plus, it's pretty time consuming and not something I can do as a "take a glance at the servers" sort of routine. I'm graphing CPU, disk use, network b/sec, etc. in Cacti as well, which is nice for seeing big trends. The problem is that it is 5 minute averages, so a server could have a problem but it's intermittent and washes out in a 5 min average. What do you do with perfmon data that I could learn from?

  Read the article

• Error opening hyperlinks in Excel 2003

  - by richardtallent

  When clicking to follow hyperlinks from Excel, I'm now getting this error: Unable to open http://blah... Cannot download the information you requested. The hyperlinks in the Excel file are created using the HYPERLINK() formula. I use Google Chrome as my default browser. The web site in question uses Basic Authentication, and I've entered correct credentials when prompted (the dialog looked like an IE auth box, not Chrome's, but it's always been that way, even when it was working properly). This hasn't been an issue until recently. I'm guessing our IT department made some lame change to IE's configuration that is causing Office to not be able to open the URLs, despite having Chrome as my browser. Things I've checked already: URLs are good, they work fine when pasted manually into Chrome, IE, or Firefox. IE is not set to Work Offline (already found that suggestion on Google). I checked Program Access and Defaults and verified that Chrome is selected. Nothing in the URL requires URLEncoding, so it's no goofy issue with encoding I've had reports from some other users now and then about the same problem, but this is the first time I've experienced it myself.

  Read the article

• Backing up Excel Files to a different Directory

  - by Joe Taylor

  In Excel 2007 in the Save As box there is an option to 'Create a Backup' which simply backs up the file whenever it is saved. Unfortunately it backs up the file to the same directory as the original. Is there a simple way to change this directory to another drive / folder? I have messed about with macros to do this, coming up with: Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Saves the current file to a backup folder and the default folder 'Note that any backup is overwritten Application.DisplayAlerts = False ActiveWorkbook.SaveCopyAs Filename:="T:\TEC_SERV\Backup file folder - DO NOT DELETE\" & _ ActiveWorkbook.Name ActiveWorkbook.Save Application.DisplayAlerts = True End Sub This creates a backup of the file ok the first time, however if this is tried again I get: Run-Time Error '1004'; Microsoft Office Excel cannot access the file 'T:\TEC_SERV\Backup file folder - DO NOT DELETE\Test Macro Sheet.xlsm. There are several possible reasons: The file name or path does not exist The file is being used by another program The workbook you are trying to save has the same name as a... I know the path is correct, I also know that the file is not open anywhere else. The workbook has the same name as the one I'm trying to save over but it should just overwrite. I have posted the question about the coding on Stack Overflow but wondered if there is an easier way to do this. Any help would be much appreciated. Joe

  Read the article

• How to Create an XML File from an Excel File

  - by nicorellius

  I have an Excel spreadsheet file that has 5 or so columns and hundreds of lines. I need to convert this (export these data) to an XML file. I'm interested in three of the columns and they correspond to these XML tags, where info1 can be followed by info2, info3, etc... <?xml version="1.0" encoding="UTF-8" ?> <list> <info1> <id>111</id> <value>222</value> <des>333</des> </info1> </list> If possible, I would like to avoid building this XML manually. It wouldn't be too much trouble to rearrange the Excel file such that the three columns I'm interested in were in their own file. But then I would need to export those data into an XML file of the above format. Any ideas?

  Read the article

• Turn Excel spreadsheet into a formula

  - by ?????? ??????????

  I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has a about 10 different inputs (values a human enters in different cells of the spreadsheet) and then it outputs 5 independent calculations (in different 5 cells) based on that input. There calculation is using some pre-entered data in the spreadsheet (about 100 different constants) and doing some look-ups on them. Now I would like to use this whole spreadsheet as a formula on a different spreadsheet to calculate a set of input values and produce the corresponding set of output values. Imagine this as creating different table with 10 columns for the input variables and 5 columns for the outputs, then copying each input into the other spreadsheet and copying back the output in the results table. For instance: - A1, A2, A3,... A10 are cells where someone enters values - through a series of calculations B1, B2, B3, B4 and B5 are updated with some formulas Can I use the whole series of calculations from A1..A10 into B1..B5 without creating one massive huge formula or a VBA macro? I want to have a set of input values in 100 rows from A100, B100, C100,... J100 onward. Then do some Excel magic that will: 1. copy the values from A100...J100 into A1 to A10 2. wait for the result to appear in B1 to B5 3. copy the values from B1 to B5 into K100 to O100 4. repeat steps 1 to 3 for all rows from 100 to 150

  Read the article

• MS Excel 2010 - Using DSN + 32 bit drivers

  - by Kristiaan

  I need some advice as im running into a problem and so far i have been unable to find a solution. We have a set of reports developed in MS Excel that use DSN file to connect to data sources to retrieve data, these work fine on 32 / 64bit systems, however we are moving to a terminal server environment using windows 2008 R2 64Bit. The reports fail to run using the DSN's within this environment if we only have the 32bit drivers installed and configured in the ODBC settings, the minute we install the 64Bit drivers the software works. Is there a way / Method of getting Excel or the DSN file to NOT use the 64Bit driver, but force it to use the 32bit driver. ANSWERED - But due to low user score i cannot "answer" my own question... Sadly there is no way to-do what i want to-do, without a lot of very nasty and not 100% perfect reg hacks. If you need to access 32bit ODBC data sources the application in question has to be 32Bit. here is a link to just one forum post i found relating to this type of problem, it appears the only way i would be able to accomplish this is to remove the 64bit version of office and install the 32bit version instead of it. http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/5108f337-f06a-4518-afe3-d3c1abd040ef/

  Read the article

• Reference entire excel 2007 sheet to another sheet

  - by Keikoku

  I have one sheet with 100 rows of data. I have a second sheet that will use the same data but with filters applied. In fact, I will have a dozen sheets, each with different filters. My goal is to have references from the every sheet to the first so that prior to filtering, they all contain exactly the same data. This way I only have to modify one sheet and all sheets will reflect the changes. The purpose is to create external links from word to excel to display specific rows, but there appears to be a limitation to linking where it displays absolutely everything that you see on the sheet itself (and each view must be different). I can manually reference the first cell and then drag the black box to easily expand it to the required number of rows, but that would require me to go into each sheet and drag the black box again whenever I add new entries to the master copy. Is there an easy way to do this? Note that the issue is the same as Easy way for users to update linked documents Excel 2007, except this time I am using a different approach. Solutions to both would be welcome.

  Read the article

• Use autocomplete in dropdown cells with Excel 2007?

  - by Martin

  I want to make a survey with Excel and I therefore have defined the cells for the answers as a dropdown cell which only accepts answers from a certain list, e. g.: The two Lists List1 and List2 (yellow cells) are the possible answers for the questions in Block 1.x resp. 2.x (blue) . There might be a block 4 with more questions, which again use List1 for their possible answers. My problem is: I'd like to be able to use the autocompleate feature to fill in the blue cells with the dropdown menu, so that the user only types 5 and it automatically expands to "5: extremely important" or "5: extremely difficult". According to my research on the www, this should be possible if I add the list with possible answers directly above the cells where autocomplete should work (I did this with the green helper cells which could be hidden) . But I have to enter at least 4 characters 5: e to get the autocompleted suggestion. Is there a way to make autocomplete already replace a "5" by the corresponding valid term? As the survey file shall be distributed to a lot of people "outside", I can not use VBA magic because it may be blocked on their computer and might not work. EDIT: it seems to have to do with the numbers I use: If I'd start my List items with A, B, C instead of 1, 2, 3, it would work perfectly. Excel seems to ignore the pure numbers when they are entered and does not try to autocomplete them.. is there a workaround? (I hope it is clear what I want, it seems a little difficult to explain.)

  Read the article

• Get Layout Shape Corresponding to Slide Shape

  - by Ryan

  In PP2007 and using VBA, how can I get the placeholder shape on a Slide Master layout that is the "master" for a placeholder shape on the slide? I am currently using a loop to compare the position and size of the slide placeholder with the position and shape of each placeholder shape in the slide's layout, but this isn't fool-proof. For example, if the placeholder shape is moved on the slide, its position may no longer match the position of any placeholder shapes in the slide's layout. I could reapply the slide's layout to snap placeholders back into position, but that's not what I want to do. Something in the object model like "Shape.Master" would be ideal but, of course, that doesn't exist.

  Read the article

• Get records from Access table

  - by chianta

  On Access 2010 I need to use VBA to get the records in a table, process them and put them in a new table. Could you tell me how can I do? Is there a way similar to C # to put everything into a datatable the result of a query? I found an example on how to get the data. http://pastebin.com/bCtg20jp But it always fails on the first statement "ADODB.Recordset". I went to see the included libraries and library that uses ADODB is already included "Microsoft Access 14.0 Object Library". Thanks

  Read the article

• Check for duplicate rows in 2 columns before update

  - by user3891378

  I have a table with 4 columns, and I need to check to see if a Column Pair exists before inserting a row into the database: INSERT INTO dbo.tblCallReport_Detail (fkCallReport, fkProductCategory, Discussion, Action) VALUES (?, ?, ?, ?) The pair in question is fkCallReport and fkProductCategory. For example if the row trying to be inserted has fkCallReport = 3 and fkProductCategory = 5, and the database already has both of those values together, it should display an error and ask if they would like to combine the Disuccsion and Action with the current record. Keep in mind I'm doing this in VBA Access 2010 and am still very new.

  Read the article

• Excel - Filling images using a reference image

  - by tjans

  I have a spreadsheet that I use to create baseball cards for a tabletop baseball game. There are about 20 cards on my sheet, and I'd like to add a spot where I can set the logo and have it reflect that logo in each card without having to update 20 different images each time I create cards for a new team (and thus, a new logo). Is there a way to automate this process similar to setting one cell equal to the value of another (=A4, for instance)? I think the images aren't part of a cell and they float on top of the sheet, but I had hoped there was a way either with a macro or other VBA function (or maybe something built-in) that would accomplish this.

  Read the article

• Hot Link To A Specific Cell Comment In Excel 2007

  - by Optimal Solutions

  Is there a way to create a comment for cell D7 (for example) such that when the user hovers over it the comment has a hot link or hyperlink to cell A100? I dont know what else to call it other than a hot link or hyperlink, however its not like the hyperlink we know of in HTML. Or, maybe it could it be? The effect that is desired is that when the user sees that comment, he/she can click on the link to cell A100 if they choose to do so. I do not wish to create a macro or use VBA behind a button, etc.. It must be within the comment box itself. One of the issues I have run into is that once you move the mouse, the comment goes away. The comment might be able to be displayed all the time if it needs to be for this to work.

  Read the article

• Excel 2010 Move data from multiple columns/rows to single row

  - by frustrated529

  So frustrating! I get data sent to me and it looks like this: a 1 a 2 2 a 3 3 b 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 and I need it to look like this: a 1 2 2 3 3 b 1 2 2 3 3 4 4 5 5 6 6 I have about 30 columns that need to move to the top value in their group, then removing the duplicates (to which there are about 33 rows of duplicates, trying to get it down to about 8 rows). I have been searching forums for several days and trying bits and pieces of code. I am having such a tough time with VBA!!!! Same illustration, but graphically:     →

  Read the article

• How to link to an Excel pivot table that will expand over time in Word 2007?

  - by Daljit Dhadwal

  I have a pivot table in Excel 2007 which I’ve pasted it into Word 2007 using Paste Special (Paste link) - Microsoft Office Excel Worksheet Object. The pivot table appears in Word and the link to Excel is working. The problem is that if the pivot table expands (for example, due to showing 12 months of data rather than six months) the link to the pivot table in Word will only show the range cells that were originally copied over with the pivot table. I understand why this happens. When I paste as a link to Word the underling field codes look like this: {LINK Excel.Sheet.8 "C:\Users\myAccount\Documents\testexcel.xlsx" "Sheet2!R1C1:R8C2" \a \p} The codes refer to a fixed area (e.g., Sheet2!R1C1:R8C2 ) of the Excel spreadsheet, and so when the pivot table expands, the expanded cells fall outside the area that is defined in the field codes. Is there some way to have the link refer to the pivot table itself rather than the cell range that happened to be originally copied over from Excel?

  Read the article

• Excel: make comma separated list from column with blanks, fed by checkboxes

  - by Crystal

  I want to make a spreadsheet where user can check boxes on one worksheet, and have those values then be brought over, comma separated, into one cell, on another worksheet. The values of the checkboxes have to be capable of changing as a new row entry is made on the first spreadsheet. I have the associated name text of the checkboxes populating into an adjacent column when the box is checked (TRUE). This column is the one I want to pull the text from. I want it to also ignore blanks, and not include extra commas. I am not familiar with VBA, but with some hand holding, I could use some. Clever formula approaches also welcome! Thanks!

  Read the article

• Excel Subtotal if adjacent column is not blank

  - by Head of Catering

  I'm trying to create a subtotal for a range that excludes rows that don't have a wholesale price. I have a range of products, prices and units that have subtotals by brand, although the brand subtotal is a sum and not a subtotal because the total needs to be displayed regardless of what the user chooses to filter. These subtotal rows do not have wholesale prices. Here is the sumif formula I'm using to calculate totals in the summary area above the range: =SUMIF(B5:B12, "", D5:D12) I need to have a subtotal formula that works the same way. Is there an equivalent to the sumif formula for subtotals? Or maybe a worksheet function I can use? I need to be able to do this without using VBA.

  Read the article

• How to make a dropdown list such that... (see details)

  - by daysandtimes

  I want to plot the stock prices of certain companies VS the S&P500. I have all the price data downloaded in my excel sheet already. I want to create a line graph in Excel. One line is fixed and that would be the S&P500, and the other would be the company I select. I know how to use data validation to create a dropdown list, but how could I make it in such a way that when I select company A, I would only see company A's price data but not company B, C, etc. Then when I select company B, I would only see company B's price data & the S&P price data. And so on. The S&P line will be visible all the time, but the various company's price line will only appear if it is selected. Is there any easy way to normalize all sets of data in a way such that the starting point is always 100?

  Read the article

• MS Excel: Can I link images using a relative path?

  - by Port Islander 2009

  I am working on an MS Excel document that contains a lot of (around 200) images. They are currently saved within the document, so the file becomes huge and working gets very slow. Linking the pictures without saving them works very well - I now have the Excel document and a folder "pictures" next to it that contains all my image files. However, when I move the document and the folder to a new location, all my pictures disappear. This seems to be because Excel saves the link information as absolute paths. (Update: Actually, according to this thread, Excel stores the link information as relative paths as well. Now I really don't know why my links break down..) Is there a convenient way to save them as relative paths or have Excel automatically update the path information? Update: It's important that the images get displayed on the sheet and can be printed. I am working with Microsoft Excel for Mac 2008 and 2011. I really appreciate your help.

  Read the article

< Previous Page | 38 39 40 41 42 43 44 45 46 47 48 49  | Next Page >