I was trying to set yp a formula that will automaticaly extend a series. I mean will add a sequential number to the one above. If I have in cell A1: CD001. I want to place a formula that will show me: CD002 in cell A2. I tried n function but it only converts it to the number and comes uout as 0. I don't know which one to use if there is any. Answer to the first question I will probably be asked: No I cannot drag it down with a mouse as I will place it in the conditional function afterall.
I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).
I have a column ( Total Hours) that I use to total the new numbers in the row.
If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula (=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))
How do I (Can I) get the formula to cascade into the new row.
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
I have a spreadsheet that requires a formula in column "e". How can I automaticlly extend the formula each time data is entered in column "d" of the next row.
I created the following macro (using the recorder), and now need to modify it so that the formula extends to the end of the data (and no further). I would also like to include a total at the end.
Sub ExcessUsage2() ' ExcessUsage2 Macro Rows("39:250").Select With Selection.Font .Size = 8 .Strikethrough = False
I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.
Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.
I am using office 2007 and here is the problem I am facing. I am using a formula and it is based on two columns data. The formula result is at C20:C2400, while the two data columns are at A20:A2400 and B20:B2400. I add one more row of data at A2401 and B2401, I expect the formula result would auto extend to C2401 but it doesn't, nothing happen.
I check that I need to turn on the auto extend check box in option, I check and it is already on.
i would like to know if Excel offers a feature whereby reminders for instance to call a client will pop up when you open a particular file? I am not referring to the comments tags that one can insert. I am looking for a type of pop up screen function ?
I have a worksheet that has a drop down box with the options PPP, MONTHLY, TOKEN & DEFAULT, next to this i have a date when a payment is expected, what i want to do is automaticaly set this to default after the date has expired, the problem i have is that there is data validatrion in this box so i cant enter a formula, is there a way around this
I have inherited a very complex spreadsheet which has some very simple formulas. As I changed some of the variables I noticed that the cells with the formulas are not updating automaticaly. If I highlight the cell and then position my cursor inside the formula bar then hit return the cell with update. Is there a protection or setting that I am unaware of that controls this behavier?
I want to enter any thing in column (A) and then the date & time automaticaly put in column (B) enter any thing in (A1), the date & time entered in (B1) automaticaly enter any thing in (A2), the date & time entered in (B2) automaticaly
1) Not using a macro and combining relevant w/s functions, is there an intelligent way to return the value of: SUM(n=n1 to n=n2) of (-1)^(n-1)*(n)^(n-2)/(n-1)!*X^n to a cell containing the formula ??
2)Example: n1 in cell A5:: 1 (integer variable) n2 in cell A6:: 32 (integer variable) X value in cell A7::1.00 Returned sum in cell A8:: 0.567143 ...
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:
Sub Macro5() ActiveSheet.ChartObjects("Chart 243").Activate ActiveSheet.ChartObjects("Chart 243").Activate ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(2).Delete End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
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?
I am working on a spreadsheet and I need values from Sheet1!a2, a7, a12, a17 etc...
I want to fill in the formula ='Sheet1'!a2 etc, to fill column A in sheet2!
When I highlight and drop down to fill excel fills the column in increments of five like i need but doesn't start on the right one. Instead it fills the formula as ='Sheet1'!a6, (For cel A1) and ='Sheet1'!a11, (for cell A2) and so on.
I want to create 3 stacked bars on a chart, where each stacked par points to the same series data. This is for an automated reporting process and I don't want to create a 'staging' table if I don't need to. I have discovered that while the series formula can take an array in curly brackets of actual numeric values, if i fill that array with the same spreadsheet cell reference 3 times it is unable to evaluate it. By way of example:
1. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,Chart1_Data!$B$7:$D$7,6)
2. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,{592210.02,592210.02,592210.02},6)
3. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1,'MI Excel Template v.1.xlsx'!testrange,6)
4. This doesn't =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,{Chart1_Data!$B$7,Chart1_Data!$B$7,Chart1_Data!$B$7},6)
I went through a lot of threads in this forum, although there were a couple relating to the same problem am facing, they didnt solve my error .. so here's my question again ..
When am trying to delink charts , i get an error stating "Unable to set the formula property of series class".
The code is as follows :
Sub DelinkChartData()
Dim iCtr As Integer, iChars As Integer, SH As Shape, iPlotOrder As Integer Dim nPts As Long, iPts As Long 'Holds the total no of points in the chart Dim xArray, yArray, sChtName As String, sSrsName As String Dim xVals, yVals Dim ChtSeries As Series ' var used to loop thru the series collection Dim ws As Worksheet Dim sChartType As String, iCtr1 As Integer
adapting a formula which I was provided on the forum earlier in the week (I have attached a worksheet as an example). The formula is in column A in the following format ‘=IF(B4=B3,A3,IF(OR(AND(B3<=B2,B3<=B4),AND(B3>=B2,B3>=B4)),A3+1,A3))’.
It is used to number groups of data and it should increase by an increment of 1 for each group. The groups are defined by a changing angle (column B) which runs from high to low (group 1) then low to high (group 2) etc.
The formula in column A works fine until the angle column does not change for 1 or more data points (which sometimes occurs on the change over between groups). See row 430 and 331, the group 6 needs to either be included in group 5 or 7 and then the current group 7 needs to become group 6 and carry on as normal. Is there any way of adapting this formula or changing it to account for this problem?
I have a lot of data series like these and the repeated angles could occur between any groups not just at group 6 as it has done in this example.
When i press the corner of a cell and drag it (example =D2) it will only go higher in the number (drag it 3 rows down it just goes D2, D3, D4, D5, D6).
Can I drag it down but make it stick to the 2 and instead go E2, F2, G2 etc?
I am editing a fairly sizeable name, address list and I would like the row highlight bar to extend across the page. For instance, when I click on the row number the highlight bar extends across the row, but when I hit return the highlight disappears and I have to click row by row. I would like the highlight bar to extend across a row and then move up or down with the arrow keys.
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range Dim CellString As String 'Stores a cell range in the form "AA27:AB39" Dim CurrentSeries As Integer Dim SeasonCount As Integer
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...........................
The D worksheet is a summary sheet that pulls in data from A, B and C.
e.g.
=A!A4 =A!C4
There are approx 50 of these references to Cell A. Is there an easy way to copy the same formulas but reference Sheet B and C without having to retype them all?