Filling Down Series Of Formulas In Several Columns
Jan 21, 2014
I need to 'fill down' a series of formulas in several columns - but the number of rows I need to fill down will vary dependent on my raw data.
See attached file.
Worksheet 1 - contains the raw data which is copied and pasted in from an external source. In this example, there are 20 records.
Worksheet 2 - brings in the raw data from worksheet 1 and does some calculations. The formulas are in the first 30 rows - in case the number of records grow. But what happens if the number of records is, say, 40?
I have a much bigger workbook in reality - so having too many formulas will increase the file size.
I m looking to fill down a column where I am assigning a number that maps to a certain week of the year. In essence after every 7th value the number should increment by 1.
I have two obscure Files and I want certain information from each file.
I started by importing the data on seperate worksheets.
and I wanted to make a table basically summerizing the info on a third worksheet
On a third Worksheet i wanted to reference from those files in a table as i mentioned. So i started referencing the cells that contain the data for the first line and second line and i wanted to fill the series so I wouldn't have to type the info into Excel (like =C8)
uhm to summerize the following::
the two files are a players file (which has there specific team and player name) and a stats file (which has there corresponding stats). So i have the following
Year Team League GP G A Pts PIM +/- 2011-12 Swift Current Broncos WHL 82 5 22 27 128 10
the cell references are
for the stats worksheet
C8, D8, E8, J9, I9 (GP,G,A,PIM,+/-) Pts is just an add
and the next line would be 25 plus the cell references (so like C33)
i would like to fill it all the way down the page for about 1000 players
but it didn't work for me - the next line was C12, C27 etc...
The charts I received have many Series formulas that reference and external spreadsheet
Is there a way that I can modify them and reference the same sheet name but locally
For your information When selecting menu Formulas and then "Show Formulas" chart data series do not display When selecting a Chart the "Show Formula" selection is not available (at least I did not find it) In addition, when selecting the Chart, Then Design, then "Select Data", it replies in the pop-up with the comment "The data range is too complex to be displayed...."
There are about 40-60 formulas as shown below!! The sheet name with data is D_Brand_Q
HTML Code: ' here is a sample of the current formula =SERIES('C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$1,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$E$197:$E$218,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$197:$CC$218,2)
A line graph is using a data row to plot the line; works fine. The data series ranges have been named for Week1 (H10), Week1_2 (H10:I10), Week1_3 (H10:J10), Week1_4 (H10:K10), Week1_5 (H10:L10), Week1_6 (H10:M10), Week1_7 (H10:N10), ...etc.
This is the data series formula name example to plot the data series from week 1 through week 7; ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7.
The series name formula Q3Wk1_7 for week 1 through week 7 is =$H$10:$N$10.
When this name is manually entered as a Series Value for the line graph, the graph plots exactly the way it is expected. The same is true if the Series Value is manually modified for some other ranges such as Week 1 through Week 9 (='Product Plan Q3 Week 7.xlsx'!Q3Wk1_9).
My challenge is to enter this Series Value based on the manually entered week number elsewhere; i.e., we're in week 7, the Series Value should be ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7. I've been trying to use an HLOOKUP table to select the correct Series Value formula based on the week number. The HLOOKUP works fine resulting in selecting the correct formula. I can also Name the range of data I want to graph and copy the name into the Series Value field with successful results. How do I get such a formula or Named range into the Series Value field without type it in? I want to set that Series Value to a range or name without having to select the line to manually enter the value.
In Column A, starting in row 8, I have a list of part nos. This list can be any number of rows long.
Further down the sheet, I want to put that same list into a horizontal array. I currently have this formula in the first column. It will, of course, pull the value from the 1st row.
=IF(ISBLANK(A8),"",A8)
However, when I fill across all of my columns, it changes the Cell reference to B8, C8, etc. whereas I need it to change to A9, A10, etc.
Writing a formula that will index the ROW reference as I fill across?
I am trying to increment a row when i copy it across columns. I have searched for a couple hours on how to do this, but I have not come up with a solution that I can understand that works for my situation.
I found this page, but I guess I am too novice to comprehend it. [url]
I would like the number in this formula to increment when i copy it to the adjacent column.
How to get "only Sunday" highlighted in a series of dates included in excel columns. I tried some formulas found from the web pages, but they all deal with both "Saturday and Sunday"...
I need to look-up and bring in one cell for each row only the 10 length numbers separated by space and in another cell the 13 length numbers space separated also.
The number of columns containing data is different from row to row ( from 2 to 60)....and the rows..are thousands..
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 have a small sheet that has a number in Column A and a larger number in Column B along with some data in Columns C and D. I wish to make a separate sheet that has rows numbered using the numerical range from Column A and Column B along with the data from Columns C & D
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?
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...........................
Is it possible to apply a formula with a relative reference to an entire column? I have a very long, constantly updated datasheet for pivot tables and this would be a most helpful thing to know.
I consider myself decent at Excel, but I don't have quite the depth of knowledge I would like. Here is my issue:
I currently have a series of 50 numbers for each row on a 300 row spreadsheet. I would like to write a formula that identifies the six largest numbers in that series and adds them together. So, for example, if the series was 8-6-4-4-4-5-1-1-0-15-3-9, the formula would spit out 47 (15+9+8+6+5+4)
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 would now like to as remove the first column and add another column a and b. A would say "Service From Date" and column B would say "Service To Date" In addition I would like to add a column between "5202 Meals Provided" and "5221 S.A. Facility Lodging" a column that reads "7025 Number of Days Care" and that tallies like the rest of them.
I'm working on a project that spans three sheets. A2:A151 of Sheet 1 and 2 is a list of names. The names may repeat and they probably won't fill all 150 rows. What I'd like to do with formulas is combine Sheet 1 and 2 into a single list in Column A of Sheet 3 without repeating names or showing blanks. I'm fine with multiple formulas and hiding columns, whatever needs to be done to make it work. Is this possible?
I have column AD where there are something like 2,500+ formulas. I would like to change the columns used in the formulas around. By that I mean the first formula is...
Code: =SUM(AA6-X6) ...and I would like it to be...
Code: =SUM(X6-AA6)
Now the thing is, the formulas are not continuous, there are breaks where there is a formula to sum say the previous dozen or so for example, then it skips a couple of rows and I have more formulas and then the sum say for the previous dozen for example and so on.
I just want those cells changed with the particular formulas as above.
I have 2 sheets in my excel document. The first sheet (sheet1) has student data (grade they got on a test) from C5:C35 all in 1 column. On the 'second' sheet (sheet3), I have made a profile for each student (there are 30 of them, spread out 1 per printable page). I want to take Sheet1!C5 result and put it in sheet3!D7. The formula in sheet3!D7 would just be =sheet1!C5. I want to 'drag' that formula to L7, then to T7 then to AB7 etc etc (they are all 8 columns apart), obviously not affecting any of the cells between them.
There are 12 pieces of data in sheet1 that I want to auto update in sheet3, for 30 students, so copy pasting, and adjusting the formula would take a long time.
i have a data entry tab where we put names and button to sort and hide blank rows from other tabs. Problem i want to move down the number entered also. something like extend sorting but not affecting the formula on the other columns.
