I'm having a problem with named ranges in a chart. I'm using Excel 2007 in Windows XP.
I have a line graph with twenty different series all using dynamic ranges. I created each series individually, typing the name and then the dynamic range I had previously defined. As far as I am aware, Excel demands when writing the reference that you specify which sheet/workbook this named range is from, so, given the scopes of my ranges are all 'workbook', I added the name of the workbook to the references.
This seems to work fine, but when I save and then re-open the file, the dynamic ranges no longer work. If I go to Select Data and look at the reference for a series, it has been changed thus:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
My chart data range is: ='Summary Data'!$A$2:$BF$8.
Since it expands a few times a week to BG, BH, BI etc., it seemed to make sense to use the standard OFFSET and COUNTA formula to expand it. The every time I typed the Offset formula in, it worked but each time I save it, it changes to an updated (correct) fixed reference. i.e. back to the format above.
Many threads I've read seem to say that you should be able to use an expanding range in charting.
I am trying to create a chart that will allow the user to add/remove series to compare with each other. When adding a series, Excel gives it a name (e.g Series1) the macro is then set to rename it to a particular cell, however the name Excel gives the series varies depending on the order in which the series are added.
I'm looking for a way to name lines in a pivot chart instead of default numbering eg ActiveChart.SeriesCollection(1). This in order to attach variable lines to y-axis no 2 no matter which selection is made in the pivot.
I have a macro which performs some conditional formatting on a worksheet. Based on the value in cells in one column, if the value meets the criteria some simple formatting is performed (row is colored, font changes for that row etc).
The macro then calls a second macro which performs the same evaluation on the same cells from the same column. If the value meets the criteria (same as previously) it is supposed to change the value of the cell by 1/2.
Both macros work, however the 2nd macro also seems to remove all of the formatting, font changes, coloring etc that occurred from the first macro. I have had the first macro save the active workbook and close and have the 2nd macro re-open the file but it sill overwrites the formatting. If I do not have the 2nd macro run the format changes are saved. I have tried saving the file after the first macro runs with .xls and .xlsx extension and no luck.
I have a text file that I need to open in MS Excel 2007. The file contains the following data. (Each column is delimited by the "|" character.)
Part #|Inventory ID 1743|213,221 1864|10,40 1948|1170,1180 5265|100,104,107,10004
Ultimately, I need three columns but the data needs to look the same as it did before I opened the file.
When I open the file for the first time, I use “Text to Columns” to delimit the fields by the "|" character. The problem is that the Inventory IDs in the middle column get a bit whacky.
Here's what I'm left with: Part # Inventory ID 1743 213,221 1864 10,40 1948 11,701,180 5265 10,010,410,710,004
The comma is retained appropriately when there are two 2- or 3-digit Inventory IDs.
When a row has multiple, longer Inventory IDs or Inventory IDs that vary in length, the comma is moved to every 3rd decimal place.
I tried putting quotation marks around the data before I opened the text file for the first time but that didn’t do anything. I tried Custom Formatting to retain the comma position (0000,0000) but this only worked when I had two 4 digit IDs. I also changed the number type to Text and General. Once the comma was changed, I couldn’t figure out how to get it back.
How to best retain the format of my Inventory IDs.
I have encountered some difficulty in modifying a macro I wrote into what I need. I created a macro that searches a column (Column C) for a cell value of, "stop", and then it copies everything above that cell and pastes it onto another sheet. In the sample data set that I was using, "stop" first occurred in cell C541, so the macro copies C1:C540 and pastes it onto another sheet. The problem is that the macro created an absolute reference to C540. What I desire is for the macro to use the 'Find' function to locate the first occurrence of, "stop", offset one cell above that cell, and then reference the active cell (which was positioned by these last two steps) in the range that should be copied. Basically, I'm hoping to have cells C1 through the active cell copied and then pasted onto another sheet.
I am currently using MS Excel 2007. Is there a way in VBA to automatically insert missing columns based on a defined series or range of values?
For example, we have a report that displays columns of: Year 2004 to Year 2012 per column (9 columns in total). Sometimes, it displays missing a year or years (thus; our report has less columns). For example, it only shows 2008 to 2012 (missing 2004 to 2007). At times, it misses some years in between. What we do is manually add those missing years by manually inserting columns then inputting the missing year as their column headers.
Is it posisble for VBA to check if the columns contain all of the defined Year range (for example, you've initially defined: Year 2004 to Year 2012)? Afterwards, all missing columns are automoatically inserted (in chronological order or based on your defined values or range).
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader() Dim Red As Integer Dim Amber As Integer Dim Green As Integer Red = Range("H9").Value Amber = Range("H10").Value Green = Range("H11").Value Range("B8").Select ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh Range("D25").Select ActiveWindow.SmallScroll Down:=18 Range("B49").Select...........................
I would like to have two series of data using the same X axis (date, formatted in months). The Y axis is in intervals of 100,000.
The first data series is historical (actual) data (i.e. Jan 2009 to August 2011). The second data series is forecast (Sept 2011 - August 2012). So i want the forecast series to start immediately after the historical series. It is a 'line with markers' chart. The key objective is that the forecast data looks visually distinct from the historial series.
I am using EXCEL 2007. When I refresh the individual pivot tables, I don't get any error. But when I click on Refresh all Pivot, I get a Reference if not valid error. The source for my pivot is referencing to another workbook. Why is there this error and how can I resolve it?
Undo / Redo - they do NOT work properly after a save. Before a save, its great - I can undo / redo many many changes.
After a save, I can only undo 2 changes, but the worst thing is it ONLY REDOES ONE of them.
I work by saving very regularly, then I go and look at something on the sheet, and then use Undo / Redo to get back to where I was last updating the sheet and see what I had just been working on, especially if its a few minutes in between.
However, when I do that now, it undoes my last change, but the redo has been lost. So I do:
change, save, undo, redo
and it just doesn't do the redo. The redo button is greyed out.
If I do:
change, change, save, undo, undo, redo, redo
the first redo works, and the second fails as the redo has been lost.
It seems pretty basic that undo / redo should be symmetrical but they aren't, at least now.
I would rather just not have undo after save at all than this broken version.
Why are they putting so much stuff in Excel but they can't even get one of the most basic parts of spreadsheets correct - such as undo / redo, and the ability to open independent spreadsheets without having to change things in the registry (which I have had to do so I can work on two spreadsheets in a semi-sane fashion, undo being shared across your sheets otherwise). MS needs a back to basics...
Intention is to (automatically) fully protect each completed worksheet/workbook on "save"/"save as" in MS Excel 2007... Is this possible by means of a macro? Which one? Saving in a ".xlsm" format is required?
I have been trying to work on this excel worksheet for a while. I have give the charge column (tab:data) and the rate chart (matrix type with weight and zone). I want to find the formula that would tell me if the charge is in the rate chart (I know it would use countif), and tell me what the zone and the weight associate with it. Using