Automatically Extend Pie Chart To Include Categories When They Have Values

May 29, 2014

I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.

I found the following formula which, when used as a named range, automatically extends a list for data validation:

[Code] ....

I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?

View 2 Replies


ADVERTISEMENT

Automatically Extend Formulas

Sep 6, 2007

Right now I have a spreadsheet where I have a person enter in 4 differant values in 4 adjacent cells of a row, then in another 4 adjacent cells of the same row calculations are done with respect to the 4 rows of entered information then displayed. Is there a more effective way in placing and copying these formulas in the last 4 adjacent cells rather then just copying the formula into the first 3000 or so rows of cells?

I say 3000 because each project is differant and will require a differant amount of rows, but by copying the formula, even little IF statements, slows down the program and makes printing a hassle.

View 9 Replies View Related

Extend Range Of Table Automatically To Fit Data

Jul 14, 2014

I have two data tables (Table1 and Table2) on two different sheets (Sheet 1 and Sheet 2). In Col1 of Table2, I have "copied" the values of Col1 in Table1 by using a simple equal (=) formula. Secondly, I have an autofilter on Table2 and a macro that automatically updates the filter when the sheet (Sheet 2) is selected.

My problem is that I would like Table2 to be more dyanmic, i.e. I don't want to change the range of the Table2 each time I add, or subtract, a value from Table1.

Could a OFFSET formula be employed in any way?

I have attached an example file.

The macro is:

[Code] .....

ExpandTableExample.xlsm‎

View 1 Replies View Related

Extend Formula Automatically As Data Entered

May 21, 2008

I found the following code here.

For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.

It works great except fot the first row (A9) where it copies the header row (B8:E8).

How can I get it to not copy when data is entered into A9?

Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error Goto 0
End Sub

View 4 Replies View Related

Pivot Table / Chart To Filter Categories

Apr 28, 2014

I have a list of customer satisfaction scores that are pulled in from a sharepoint list. The list is then used to create a pivot chart that is used in a web part on a dashboard in sharepoint. The chart is supposed to show average customer satisfaction scores per category per month.

The problem I have is that I can't filter the categories using a slicer, I can only filter the categories' values (i.e. the scores). I can filter by date though.

When I try to filter the various score categories/series (Support, Change Management etc) I only get the option to filter their values i.e. 1,2,3,4,5.

I want to be able to use a slicer or similar to be able to display either all of the series or just selected ones.pivottable.JPGpivotchart.JPG

View 14 Replies View Related

Managing Categories Based On 2 Variables And A Chart.

Aug 18, 2009

I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?

View 6 Replies View Related

Count By Categories Within Pivot Chart/Table

Mar 17, 2008

I can not get my Pivot Chart to count, sort and categorized my data. I have included a sample file for your viewing. I basically want the data in cells C2:E18 counted, sorted and totaled by categories and locale. I thought the pivot charting was simple, but I'm having quite a challenge with this one. The pivot chart data should reflect as my sample indicates in cells E24:K27

View 3 Replies View Related

Automatically Sorting Data According To Defined Categories?

Apr 25, 2014

I have two columns, in column A will have four or five categories repeating randomly, in columns B will have unique names.

What I would like to do is to define categories as a column names in a table and then to automatically fill that table with the data from column B depending on category.

View 2 Replies View Related

Extend The The Number Of Values In A List Box

Nov 3, 2008

I am using Excel 2003 SP3. How does one extend the the number of values in a list box and retain the same name to be used in the Data / Validation / Settings / Source entry? The only method I see is to extend the list of allowable values and give the longer list a new Name.

View 2 Replies View Related

Automatically Save As PDF But Include Location

Sep 13, 2012

I have a workbook with 4 worksheets the first is called "input" which I use to enter information which goes to the other worksheets which arecalled engine, gearbox, doors.

I am stuck on the following; after inputting the information. I want to save as pdfs in various locations.

Is it possible to have 4 buttons on the input sheet which automatically :

Button 1 - Save "engine" worksheet as a pdf to a specified folder
Button 2 - Save "gearbox" worksheet as a pdf to a specified folder
Button 1 - Save "doors" worksheet as a pdf to a specified folder
Button 1 - Save the sheets as multiple excel files to a specified folder

View 4 Replies View Related

Table Of Contents That Will Include Chart Tabs

Apr 27, 2009

As I mentioned in my other thread, I have a spreadsheet with 9 worksheets and 200+ charts in individual tabs. As you can imagine, searching back and forth across 200+ tabs is a bear. While I have used Tab Colors to help, it's still way too annoying.

Sadly, each chart must be in its own tab in order to ensure that the links to PowerPoint and Word continue to work. Unless I'm missing something, individual tabs are the only thing that have worked for me.

I recently found a macro that creates a TOC for worksheets, but it didn't include chart tabs. Is there such a thing, or is there a way to creat such a TOC manually?

View 5 Replies View Related

Automatically Include New Data In Pivot Table

Jun 10, 2008

I have a workbook that automatically creates a new sheet based on the current day. There is a pivot table in the workbook that gets values from multiple consolidation ranges.

Right now I have to manually add the ranges from the new sheet every time. I need that pivot table to update with the same ranges from the new sheet every time a new sheet is created.

There is also another sheet (named "Data Results") that needs to update too, but that's not as urgent.

I've searched everywhere on the forums and can't find anything like that. When the file opens, I've written script to have a box pop up asking whether you want to "View Data" or "Begin New Audit." If you click "View Data" nothing happens and you can just view the file. If you click "Begin New Audit" it creates a new sheet based on the current date.

I wanted to attach the file, but it's an Excel 2007 macro-enabled file (*.xlsm).

View 4 Replies View Related

Summing Values Based On Multiple Categories

Jul 22, 2014

I am trying to create an inventory control system that keeps tracks of what's in bins based on additions and subtractions to the bin. I am hoping to generate a summary output of bin contents based on the transaction list. I know how to do this using pivot tables, but am looking for a VBA or excel function solution since I will have hundreds of bins and thousands of transactions.

Bin number
Product Code
Pounds

1
A
50

1
A
-25

[Code] ......

I want to have the following table auto generated.

Bin
Product Code
Sum Quantity

1
A
25

1
D
75

[Code] .......

I'm using an array formula right now, but it is proving to be extremely slow.

=IF(ISERROR(INDEX('Harvest Generator'!$E:$I,SMALL(IF(LEFT('Harvest Generator'!$E:$E,LEN('Harvest Generator'!$E:$E))=LEFT(B$3,LEN(B$3)),ROW('Harvest Generator'!$E:$E)),ROW(1:1)),B$2)),"",INDEX('Harvest Generator'!$E:$I,SMALL(IF(LEFT('Harvest Generator'!$E:$E,LEN('Harvest Generator'!$E:$E))=LEFT(B$3,LEN(B$3)),ROW('Harvest Generator'!$E:$E)),ROW(1:1)),B$2))

Where Harvest Generator is the sheet where my table is on.

View 2 Replies View Related

SumIf: Check The Total Budgeted Amount Based On The Values I Enter On The Categories Sheet

Dec 18, 2009

I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.

View 2 Replies View Related

Editing Macro To Include Multiple Rows And Include Text Formatting For Leading Zeros

Apr 2, 2014

I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.

View 10 Replies View Related

Calculating Stdev When Values Include #n/a

Sep 14, 2009

I have a set of values. #N/A413636363632. The #n/a is there on purpose for charting reasons. How do I calculate the stdev of that range? i.e. ignoring the error?

View 2 Replies View Related

Sum Numeric Values That Include Units

Oct 23, 2009

I have lists of room areas in metres squared, I extract the web based lists to excel: for example.

10.56 m2
14.23 m2
21.34 m2

How do i calculate the total of these amounts?
Is there a formular to remove the "m2"?

I have tried using =SUBSTITUTE(A1,"m2","") which deletes the "m2" from the cell, but then I can't use =SUM(A1:A3) as this totals the 3 cells as 0.

If i use =SUM(A1+A2+A3) the total is correct, but I have over 3000 cells in one column to add together.

Is there a formula or something to remove all instances of "m2" or "m3" from the whole sheet or another way i can calculate the totals.

View 12 Replies View Related

Look Up And Match Several Values To Include Entry

Feb 9, 2010

I want to be able to look up and match several values in my worksheet to confirm, that an entry is valid. I would like the test to return an OK / NOT_OK value at the end of each entry. In the attached file you will find four different variables. In order to include an entry, it must be matched to find related entries, before it can be included.

For an entry to be OK, it must contain a company that has been listed in the same industry, in four different quarters (not necessarily in the same year). A company can be listed in several industries, but as long as there are not four entries in the same industry in four different quarters, the entries are not yet OK (hence NOT_OK). Therefore the only entries that are valid in the attached file are:

Company A Q1 2009 Administration OK
Company A Q2 2009 Administration OK
Company A Q3 2009 Administration OK
Company A Q4 2009 Administration OK

I work in XL 2007, but have converted the attachment to 2003 for maximum compatibility.

View 3 Replies View Related

Formula That Doesn't Include 0 Values

Dec 6, 2013

I'm calculating a variance for a set of actual numbers vs. variance numbers. When there are no actuals, I don't want the formula to calculate a variance. How can I do this?

Spreadsheet attached : Book1.xlsx‎

View 1 Replies View Related

SUMPRODUCT Criteria To Not Include Values Within Column

Apr 11, 2014

I'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...

That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula.

View 1 Replies View Related

Lookup Values And Include New Rows As Needed

Jan 8, 2009

I am looking to build a VBA to do the following action:

Look up an ID number from column F (ID numbers are separated by an empty row at current),
then check whether there is a corresponding value in columns N to P,

if there are values, copy them onto a different sheet into three different columns

if there values below this particular row, i.e. if the rows below say row 9 (where we looked up the ID number) in columns N - P are not empty, also copy all these values and add additional rows as needed on the sheet this is copied to

then look up the next ID in column F and do the same until there are no more IDs

View 9 Replies View Related

Automatically Include Details From New Worksheet In Summary Worksheet

Feb 15, 2009

I am starting within a workbook with a standard invoice/form. During the course of a month this invoice will be copied 20 to 30 times within the workbook and the amounts changed each time. Is there a way that I can:

a) Have amounts from 2 of the cells (say E30 and E31) within each new invoice worksheet automatically included on a summary page? These cells are calculated by formula.

b) (This one would be nice but is not essential) Have each new invoice worksheet that is added (by copying) automatically numbered sequentially in a cell (say A2) and also re-named with this number. The first existing invoice would be manually numbered and each additional invoice would follow from there.

View 5 Replies View Related

Adding Values (currency) From One Column Based On Value Of Another Column (Categories)

Nov 5, 2013

I have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.

Example.xlsx‎

View 1 Replies View Related

Autofilter Based On List Of Values In Range (Include Not Exact Match)

Feb 13, 2014

I need an autofilter which filters rows based on a list of values (+50).

The problem is that I am looking for rows INCLUDING values from the list, not for exact match.

I am not able to make excel filter values including the values from the list... I am able just to filter values matching exactly values from the list.

[Code] .....

View 2 Replies View Related

Show Missing Values In Pivot Chart (line Chart) Axis?

Apr 18, 2013

How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.

View 1 Replies View Related

Pie Chart - How To Use Hide Values But Not Show In Chart

Dec 17, 2013

I have created a pie chart showing top 10 best selling categories. However, on this pie chart, I want %age of overall sales to be shown.

At the moment say I put in the following

Catalogue 1 100
Catalogue 2 98
Catalogue 3 92
Catalogue 4 85
Catalogue 5 84
Catalogue 6 75

[Code]....

I add Data Labels and select the percentages. This shows at Cat 1 having 12.5% of the sales (100 of 800 sales), Cat 2 having 12.25%, and so on..... However, what you don't see is the following data:

Catalogue 11 60
Catalogue 12 58
Catalogue 13 57
Catalogue 14 57
Catalogue 15 56

So based on 1,088 sales, Cat 1 at 100 is actually only 9.19% of TOTAL sales.

I understand what is happening, the chart is giving the percentage based on the data inputted into the chart.

I did consider adding an 11th category to the chart, which was the total of Catalogues 11-15. Whilst this does give a more accurate percentage figure (as now all sales have been inputted), it makes the chart look ****

Is there a way of either adding the 11th category, and then "hiding" it, so that the chart doesn't display it, but uses it's value to calculate Catalogues 1-10s percentage?

BTW: The reason I ask is that I could be dealing with anywhere from 30 to over 100 catalogues. So it's not ideal showing the others in 1 chart.

View 1 Replies View Related

Automatically Highlighting A Bar Chart

May 23, 2014

I'm currently writing an automatic report to display information about a store and how it compares to its peers. My company works on a National>Region>Area>Store basis so my report lets a user select a store and the excel file brings back all the data for the relevant national, region and area data for comparison. That works fine.

I now want to develop a set of pages which display data for that store compared to other stores in the same area. For example: [URL]

My report brings back all the stores, ranks them by their performance against target and then puts them in a sorted table using vlookups. I then want to put them into the bar chart shown above, with the store selected highlighted with a different colour. In the example above, the Hunslet store is selected so its bar is highlighted.

Is there any way to make the store highlight automatic? I manually set the colours in the example but I don't want the user to have to do that each time. It would be possible to do using a macro, but I try and avoid them where I can since VBA knowledge is extremely limited in my office.

I have named ranges containing the names and lookup codes for the selected store, area and region .

View 2 Replies View Related

Fit Chart Widths Automatically In VBA

Jan 24, 2014

I can automate chart creation in VBA, but is there any way to also have it such that the x-axis of the chart begins with the minimum x-value and ends with the maximum x-value in the data set?

View 2 Replies View Related

Chart Range Changing Automatically

Jul 14, 2014

I have a workbook, with 2 sheets. first page has graphs, second has data. in my charts I refer to a named range on the second one as follows: Data!Named_Range, where "Data" is the name of the second sheet. However when i save this, it changes the range of the chart to: '101044.xlsm'!Named_Range, where 101044 is the name of the workbook. This is a problem for me as this workbook will often be loaded into a document handling system where the file will change name, throwing out all sorts of errors because the chart path is invalid, and i have to change the range of all the charts manually. why does it have to use the file name when reffering inside the same workbook? and how do i force this to not change?

View 5 Replies View Related

Automatically Create Chart Images

Jun 12, 2014

I currently need to create around 1200 chart images using excel for a report series I am performing for a client. I have been trying all kinds of different things, all failures, to automate this process. My current method is to filter a pivot table on the building I am analyzing, copying the chart, pasting it as a picture in Ms Word, then saving that image as a Picture. Obviously, this process is quite lengthy, especially if I multiply that over the course of all 1200 images.

I am looking for a solution using a macro, vba, or a combination of things to automate this process and maybe even create the jpgs automatically?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved