Auto Chart W/IF Funtion
Feb 2, 2008Chart updates correctly when new pricees added.
But my IF function in column C leaves the annoying line to zero until prices are enter in column B.
ADVERTISEMENT
Len Funtion
Apr 25, 2006 Range("A1:F50").select
If Len(Cells.value) > 40 Then
activecell.clearcontents
End If
Countif Funtion For More Than One IF
Mar 30, 2009If I have column A with a department name e.g. A, B, C, etc
and
column B with a date next to the some of the entries of column A.
How would I compose a formula to return an answer of the amount of records that show column A as "A" and column B as "1".
This is probably immensely simple and I have a wet fish on standby to hit myself with.
If Funtion Is Not Working When Comparing ROI
Sep 11, 2009I created a spreadsheet to calculate property "Return on Investments" (ROI). I then added a column with the If command. Here is an example formula:
=IF(AND(G12>=G4; G12>=G5; G12>=G6; G12>=G7; G12>=G8; G12>=G9; G12>=G10; G12>=G11; G12>=G13; G12>=G14; G12>=G15; G12>=G16; G12>=G17; G12>=G18; G12>=G19; G12>=G20; G12>=G21; G12>=G22; G12>=G23; G12>=G24; G12>=G25; G12>=G26; G12>=G27; G12>=G28);"Buy!!";"--")
I dont know if it matters but the ROI is a very small decimal percent. However I found that when 2 properties get close the "Buy!" symbol dissapears from all cells. I have compared all the formulas and found no issues.
I have attached the file to this post
The issue occurs when 1750 is but in cell B12. I have narrowed the issue to cells A6 and A10.
=IF(AND(G6>=G4; G6>=G5; G6>=G7; G6>=G8; G6>=G9; G6>=G10; G6>=G11; G6>=G12; G6>=G13; G6>=G14; G6>=G15; G6>=G16; G6>=G17; G6>=G18; G6>=G19; G6>=G20; G6>=G21; G6>=G22; G6>=G23; G6>=G24; G6>=G25; G6>=G26; G6>=G27; G6>=G28); "Buy!!"; "--")..............
ISERROR Funtion Into An IF AND Statement
Sep 23, 2009I am trying to nest an ISERROR funtion into an IF AND statement.
Column B D E F
Cases Case Pack Reg Price VPR Price
1 14 2.25 0.00
1 14 1.75 2.00
When the value of F is zero, I want the formula to be B1*D1*E1, when the value of F is not zero, I want the formula to be F1*D1*B1. I can figure this part out. The problem is I have these values on a certain number of rows and I want a grand total at the bottom, but the rows that do not have values in them are giving me a #VALUE! error, which is messing up the grand total. I am not sure how to write the formula with the IF AND and ISERROR functions.
Sum Product Funtion Within A Date
May 11, 2007I have a column that lists names, and I need to count how many there are for each name, sounds easy enough use the following i think.
=SUMPRODUCT((A1:A10="Joe")
The trick is, I only want it to sum them if it's within the current month. I am entering something wrong because I get an error message.
Creating SUMIF Funtion
Jun 30, 2009I am looking for the exact code to make a "SUMIF" function so that I can modify it to add another search criteria, such that I only wan the sum if it finds both in the range.
View 9 Replies View RelatedFuntion/Code To Rename A Sheet/tab
Dec 13, 2007Is there a function to do this or only VB code?
View 14 Replies View RelatedHow To Return Value From VBA User Defined Funtion
Nov 26, 2009how to return value from VBA User Defined Funtion
View 9 Replies View RelatedLIST Funtion: Find The Highest Number ?
Jul 30, 2008I have several worksheets where I input data, and I would like a 'stats' page as worksheet 1. Work sheet one is a list of names in cells A4:A28. column B,C,D,E,F, and G contain the results using Countif. How would I now get excel to look down a column, for example B, to find the highest number in that column and then use the name from that line but in column A.
View 3 Replies View RelatedIncorporate Concatenate Funtion: Return Text Rather Than A '0'
Apr 2, 2009I have the following formula: ...................
and excel is saying I used too many characters and to use the concatenate function to do so, but I have no idea how.
I wanted to return text rather than a '0' in the case that all the original IF statements were false, I threw another IF statement in there and said if the original is equal to '0', then return "-". If not, then run through the original again (I pasted the original again after the "-").
Auto Chart Positioning By VBA Or Other Settings
Aug 8, 2006i try to change the chart position (chart properties) to make it auto- move to empty cells, but anyway i fail.
say example:-
i have a excel sheet,
Worksheet 1
------------
raw data, all records.
worksheet 2
-------------
1 pivot table + a graph/chart at the same sheet.
top area of worksheet is pivot table, below is chart.
The pivot table size can be change from time to time (by increasing the records at the worksheet 1)
settings or using vba to control it? by auto move the chart position down, and not overlap with the pivot table.
Auto Select Populated Cells For Chart
Dec 26, 2011Seeking a way to automatically select just the populated range of cells in Cols A, B and C starting with row 2. In this sample there are 48 however this changes.
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlBarStacked
ActiveChart.SeriesCollection(1).Name = "=Sheet1!$C$1"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$C$2:$C$48"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=Sheet1!$D$1"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!$D$2:$D$48"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$A$2:$A$48"
Auto-Extending Graph/Chart Range
Aug 21, 2008I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.
For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel ......
Auto Size Dynamic Changing Chart
Jun 14, 2008I have created a dynamic chart in excel where either the number of Series or the number of Data Points will change dependent on user inputs in the model. I've done this using the Offset Function in a Defined Name in Excel and dropped this Defined Name into vba code using ActiveChart.SetSourceData.
Anyway, all works fine, pulling the correct data, etc. What doesn't work is the final appearence of the chart. The chart has a Legend placed at the bottom of the chart, as standard, unless there is only one Series, when I remove the legend altogether. Whilst the legend works fine, the Plot Area of the chart doesn't update automatically to accomodate the changing size / existence of the Legend. Does anybody know how to do this? I tried using hieght, etc properties, but I don't know how to make them variable
Auto-Populate Items From Master Reference Chart Based On Job Description
Dec 3, 2012I have attached the sheet :
Certain job descriptions require certain training. Drivers needs these two particular trainings and an office employee only needs that training...
I have a reference sheet with what trainings are required for what job. I then want the next chart with each employee name and subsequent job title to have the required trainings automatically checked. (I can take care of the conditional formatting after that).
Job Desc.xlsx
Paste First Chart Into Second Chart But Maintain Format Of Second Chart?
Jul 12, 2013How do I paste the first chart into the second chart but maintain the format of the second chart?
View 2 Replies View RelatedAuto Size Cells On Visible Columns Not Auto-sizing Correctly?
May 27, 2014I have the following code:
[Code] ....
When I run the macro, some columns are already hidden. The macro doesn't seem to autosize cells correctly. For instance, one cell in a row appears to have some contents hidden (or below the reading area of the cell). In other instances, the rows are auto-size to huge heights and widths.
Get (tabs) Of Action Log To Auto-populate And Auto-delete In Master Log
Jul 29, 2014I need the tabs of a project action log to auto-populate and auto-delete in a master log. (So when something is added or deleted in a tab it is added or deleted on the master) I use excel a little bit for work and personal finance purposes but I have zero experience with macros or VBA.
View 1 Replies View RelatedAir Force Mission Tracker-Auto Counting & Auto Copying
Jun 19, 2009Making a mission tracker that requires less upkeep than my units previous methods. I am using Excel 2007 and have some things in mind that I would like it to do.
On the down side, I have only basic experience with excel and have never used macros or VBA.
Even if what I am after could be solved with functions (which I prefer), I simply do not quite know how to make what I want without assistance. Ok, now on to the description.
The mission tracker aside from showing a list of the missions we have done during our program, it needs to also generate metrics automatically. In my worksheet, I am using columns A-G and rows 1-501.
The mission scheduling type in Column E are listed below. The letters on the left are what I am using in the sheet, the part to the right is what it really means:
Column D has the day of the week listed as: Mon, Tues, Wed, Thurs, Fri, Sat, Sun.
now the metrics I need to be generated are the number of different types of each mission compared, how many of each type of mission was flown per day of the week, how many of each type of mission was flown per month.
Basically what I need is a way to automatically count the number of rows that meet a specific criteria throughout the range. This would require being able to check the value/text of multiple cells at a time and count the number of rows that meet that criteria.
[SOLVED] 1. The first part would be to have the worksheet be able to count the number of each type of the 9 mission scheduling codes throughout the range. I would be storing the count in new cells that would then be connected to pie charts.
[SOLVED] 2. The second part would be to count the number of each type of mission that was flown per day of the week. That would require searching both mission type and day of the week. All I would need is a working formula for 1 set, then I could switch mission codes and weekdays to match all the 63 possible outcomes.
3. Knowing the number of each type of mission flown per month. This would be checking the date and the mission type. The trickier part that comes up in my mind is that our programs span multiple calendar years, so it would have to track it by month and year, that way there is a difference between Jan 2009 and Jan 2010. The counted numbers would be used to make histograms. It would be nice if the chart titles could be automatic based on the dates inputed in the mission tracker. If I needed to choose a maximum time length for it to cover, I would pick 3 years.
4. I would like to figure out how to make an inputted line of data on the main tracker sheet to create a copy in the corresponding worksheets based on schedule type, that way a person could look at/print a specific type without having to sort the main list.
The actual goal is to make it where a person can enter the 1 line of data per mission (cells A-G) and the mission metrics update automatically after each mission is inputed allowing upper level supervision to have current information quickly whenever it is needed. Currently we need about a weeks lead time to get the information asked for on our current progress.
Auto Complete And Auto Delete With Data Validation
Jan 7, 2008Within a data validation selection, are you able to do both Auto Complete and Auto Delete? I have this posted at another forum [url]but have not been able to find a solution (a copy of the file, test. zip is there as well). I am not sure it is possible, or, at least I have been unable to get it working. I can do each, but not both.
I have also been utilizing:
[url]
[url]
Excel 2003 :: Daily Stock Chart AND Random Scatter Chart Data
Jan 14, 2014I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
Chart Hyperlinks: Attach A Macro So That When The Chart Is Clicked It Returns To Sheet - Home
Feb 24, 2007I know this has been discussed a number of times, but here is my problem
I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following:
worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.
Excel 2010 :: Add Pie Chart Into Data Point Marker On Line Chart
Nov 27, 2011I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
Excel 2003 :: Filter Data In Graph / Chart (without Having To Create New Chart)
Dec 29, 2011I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
Adding Comparison Line Chart To And Existing Population Pyramid Chart
Jul 14, 2012I have created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.
View 1 Replies View RelatedShow Missing Values In Pivot Chart (line Chart) Axis?
Apr 18, 2013How 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 RelatedOdd Chart: Combine Data From Multiple Worksheets And Make A Chart
Mar 26, 2009I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
Combine (Overlay) Bubble Chart & Point Scatter Chart
Jun 30, 2009I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart.
My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
Can Auto-hide Rows Or Auto-set Row Height
Jun 27, 2014I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell?
