I'm using Excel 2010 to link a table to a XML file on my server. I'd like to distribute the Excel file to a group of people and have it updated every time the XML file is updated on the server.
In Excel, I'm pulling the data using "Data", "From Web" and then I type in the path of the XML file.
Excel then builds a table with all the data but if the XML file is updated on the server, the data remains static, even if I click "Refresh" or "Refresh XML data" on the table.
It should be able to download new data including new columns to the table, if any.
Ok so I have an extensive worksheet that pulls a lot of data from the web, 47 connections to be exact. So on my work computer that runs excel 2007, the data refreshes instantly and I can see all the numbers flying through and changing as the sheets refresh.
However, my personal computer running excel 2010 hangs up with any type of data refresh, not just this sheet in particular. This computer has 8 GB of ram so I don't think that would be the issue?
Does excel 2010 have an issue with data refresh from the web?
I have an excel tool I am adapting to 2010 from 2003. It imports another tool which has been created elsewhere in my company and it is protected so I cannot alter it. This tool that I cannot change creates its own custom toolbar the "old fashioned" way so it dumps into the add-in tab on the ribbon. My tool opens this corporate tool and changes some items on the toolbar (enters values in a text box) and then imports data from it. However in run-time the custom toolbar never appears. Is there a way to refresh the ribbon in run-time after opening the file so I can then access the toolbar items?
I have some code that runs and loops through each pivot table to refresh it; however, I remmed out the code so that it only loops once - telling it to refresh the first pivot; the thing I'm confused about is that after the code runs, it appears to have refreshed all my pivots. I think it's also important to note the each pivot table is linked to the same data source. (an external ds)
Maybe when this is the case, refresh will always refresh all?
If a user changes some data in a pivot table and then refreshes the table, the changed values are supposed to revert to the source data, right? This does not happen in my pivot table.
Any changes made in the underlying data get transferred to the pivot table refresh, as expected. But if I change some values in the pivot table, followed by a refresh, then the cells that I changed remain the same unless I happened to modify the corresponding values in the underlying source data too. In other words, the refresh operation does not touch the pivot table except specifically where an update is driven by a change in the underlying data.
This behavior is different from what i remember from, say, excel 97 / 2000.
I have a report that uses external data to feed a Pivot Table.
I noticed that the data source had been turned off automatically by Excel and turned it on and added the file location to the trust centre.
I also set the data to refresh when the workbook is opened.
I saved the file and then tried to refresh the data which caused Excel to crash. Of course now I can't open the file either because when it attempts to refresh itself on opening that causes Excel to crash as well.
Addendum: I have removed the Trusted Location which has caused Excel to disable the connection again. This has solved the crash on opening but I still can't refresh the data.
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j. I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
I am using Excel 2011 and we have a link data source to the outside SQL Server.
For the 1st step, I linked to the Database and retrieve the data.
P1.png
2nd step. Sort the data by using the column "Description" Descending. Add another column to the table and put the 1, 2, 3, etc in that column.
P2.png
3rd step. I clicked on "Refresh" and the new column values are not consistent. I mean they are moving. So, I added another row in the database to test and it becomes very obvious. It can be clearly seen at the following image. The value of "A" should always be "10", but it has been changed to "1" after the refresh.
P3.png
how I could retain the position of another column after the data refresh?
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it. I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.
I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form: | Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
I have an input spreadsheet to capture site addess details using Excel 2010
The Site Name is in Column A starting at row 8, each row is formatted with validation rules etc. but want only expose rows with data and one blank row to add next site.
So I needed code to unhide the next row without data - found what I needed in this thread
Have adjusted to my needs
[Code].....
It works by adding the next row but if I then delete the last rows Site Name I get a run time error with "Unable to set the Hidden property of the Range class" at follow code line.
I have problems copying data (from notepad, values are in scientific notation) into Excel 2010 worksheet. This problem only occurs with one of my laptops. I also tried this on my partner's laptop and no problem at all.
My new laptop (which I would like to use in the lab) has Windows 7 professional installed on it. I bought my laptop in Austria/Germany, so I changed the language from German to English. I then installed Microsoft Office 2010 on my laptop (which I am also using on my main laptop- without any issues). I have changed my default language to English UK.
The issue is as follows: Here is a small sample of the data from notepad
I select & copy the data from the notepad (also tried notepad++) and paste it into the Excel 2010 worksheet. This is what I get in Excel: 3.33E+02 1.51E+02 0.00E+00 0.00E+00 4.97E+04 3.85E+02 6.24E+05 1.81E+08
When I use another spreadsheet package (MagicPlot Student version), there are no problems. I have also installed Notepad++ and experience the same issue. So somehow Excel is increasing the value by 5 orders of magnitude. When I copy the values from another Excel sheet into a new Excel sheet, there are no problems. I have re-installed the Microsoft Office suite several times and the problem is still there. I can't re-install Windows 7 as I don't have the installation discs.
When I had Excel 2010 and now with Excel 2013, whenever I open a workbook, it opens and displays to the row AFTER the last row of data. (So, if my last row of data is 38205, it will open starting at row 38206...hence I see no data until I hit Ctrl+Home). I have not found anything about this on the internet and I cannot find a setting. I need Excel to open to A1 so I don't freak out every time I open a workbook and see no data.
My business I work for is a collection agency and from time to time our clients send us files that are beyond recognizable for our collection software to understand nonetheless import into our database.
The file was a PDF at first but I have since been able to convert to excel format.
The data isn't too scrambled just scattered throughout rows. I would like to have rows of patient data into one row.
The file after being converted was all in row A but I have since used text to columns to divide into separate rows. However now one patient information is on multiple rows and I need each patient info on one row.
The file looks like:
|A---------|B-------|C----------| 1.John-----|Doe-----|123 Main St| 2.New York-|NY------| 3.11100----|SSN----|Balance Owed|
I would like the data from multiple rows to continue on row 1 like this:
|A--------|B-----|C------------|D----------|E--|F-----|G----|H-------------| 1.John----|Doe---|123 Main St.-|New York---|NY-|11100|SSN-|Balance Owed--|
I am no programmer in the least and have very little to no experience in pivot tables.
This file has about 7000 accounts with patient info spread through 3-4 rows for each set of data.
How I could go about doing this without manually copying and pasting into additional columns on the same row. That will take hours if not days.
I have two spread sheets that I 'converted' into tables. I need to combine them into a single sheet. When I attempt to copy and paste from one to the other the formatting and formulas do not carry 'down' as they do when I manually enter a new line or record.
Is there a way to copy multiple rows of data from one tabled sheet to another tabled sheet?
Imagine that A and C are basically the same. So the value that I really care about it (10 + 18) = 28 B and D are the same, so I care about (16+20) = 36
The output I need is:
A&C - 28 B&D - 36 E --- 16
Can I use SUMIF to basically say: Sum this IF =A OR or =B ?
Can I apply an IF function with concatenate to merge the A's with C's and B'd with D's?
The spreadsheet is large and this process will apply to many different groups of brands.
I have the following macro that imports a copy of data into a worksheet. I would like it to paste the data below in first empty row (where column A is empty) instead of A1. I am using Excel 2010.
