Filling Down A Series.
Aug 3, 2009
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.
Date Day ConsumptionWeek number
1/01/2009Thu 827 1
2/01/2009Fri 907 1
3/01/2009Sat 1,013 1
4/01/2009Sun 1,051
5/01/2009Mon 1,097
6/01/2009Tue 1,155
7/01/2009Wed 1,044
8/01/2009Thu 976 2
9/01/2009Fri 1,034 2
10/01/2009Sat 1,107
11/01/2009Sun 1,073
12/01/2009Mon 1,161
13/01/2009Tue 1,395
14/01/2009Wed 1,172
15/01/2009Thu 1,060
16/01/2009Fri 1,008
View 5 Replies
ADVERTISEMENT
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.
Simple example.xlsx‎
View 3 Replies
View Related
Sep 16, 2006
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...
View 11 Replies
View Related
Dec 20, 2011
whats the vba code in filling a selection with a series 1 ,2 ,3, 4, 5, and so on?
cant seem to find it in google and cant make it out on macro recorder
View 3 Replies
View Related
Aug 4, 2014
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:
[Code] .....
View 1 Replies
View Related
Dec 22, 2011
I have the following code:
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.
View 4 Replies
View Related
May 31, 2008
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?
View 4 Replies
View Related
Aug 21, 2006
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
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............
View 9 Replies
View Related
Jun 24, 2008
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...........................
View 2 Replies
View Related
Aug 22, 2014
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)
How do I write that kind of formula?
View 8 Replies
View Related
Sep 21, 2011
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.
Excel version: Excel 2010
OS: Windows 7
View 5 Replies
View Related
May 15, 2008
I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....
View 9 Replies
View Related
Feb 14, 2010
I’m not even sure I can do this- but- I want to be able to have a few columns, 3 of them I want to be Item #, Item, Cost.
Is there a way to put the Item # (Number) in, then have the other 2 automatically come up?
View 9 Replies
View Related
Jan 18, 2013
I have a table of information on a worksheet and one of the columns determines whether the item in that row is active (there will be active and inactive items in this "Main List")
On a seperate worksheet (in the same file) I want to only display the active items in a new table (Overview) and always ensure it has no empy spaces other than at the bottom of the list, so if row 1/2 in the Main List are inactive items then the first row in the Overview List would display row 3 from the Main List.
Ideally I would rather do it in a formula than VB if possible.
I've attached a file showing what I am trying to explain, where the Overview tab currently has the data typed in it should automatically populate based on column "I" on the MainList tab.
example.xls
View 2 Replies
View Related
Sep 25, 2007
I want to be able to have a macro that will compare one cell to the rest of the cells in a collum. I have used the COUNTIF() formula and it's getting on my nerves a bit because it seems as though everytime i sort the data, it totally messes up the formula (as in it changes the cell refernces to seemingly randomly generated numbers, but that is a different matter! lol). here is the formula, and it will be followed by the formula that is in the cell directly below it: =COUNTIF(F2:F65536, F2) and the one below it: =COUNTIF(F2:F65536, F3). Only one number needs to change, but when i fill down in excell, it wants to add one to every number, even when i click COPY cells... oh well. So i want to write this macro that will do just that. That way, it has fixed reference parameters e.g. F2:F65536, and one variable parameter e.g. F2, F3, F4 and so on. I'm not too savvy with this whole VBA stuff... the university insisted that everyone uses C++ nowadays, lol. I would like this macro to output its data into an adjacent cell ex. if F2 was compared, the data will be placed in H2.
View 10 Replies
View Related
May 1, 2009
I fill mentioned data by Qty values in Sheet2?
Sheet1
ABCDE1NameLogQtyCodeZop2A140005RFG-13B150001RFG-14C980003RTH-1
Excel tables to the web >> Excel Jeanie HTML 4
Required result like this.
Sheet2
ABCD1NameLogCodeZop2A14000RFG-13A14001RFG-14A14002RFG-15A14003RFG-16A14004RFG-17B15000RFG-18C98000RTH-19C98001RTH-110C98002RTH-1
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Nov 12, 2009
im filling out a worksheet.
on the rows i have this data ex. (123467.00) is there a way i can format the cell so the final product is this ex. (1234.67).
i've tried may things but what i get is (123467)
i have multiple worksheets like this just want something that can do automatically instead of manually.
View 9 Replies
View Related
Feb 24, 2010
How can you fill up a listbox on a form with data from a couple of cells.
For example you have the worksheet Colors and you have these values in the cells of column A
A1 = red
A2 = yellow
A3 = blue
Now I want red, yellow and blue to be in the Listbox.
View 9 Replies
View Related
Sep 26, 2006
In my worksheet I have a column with the number 1 in cell G3 ,I want to fill down the column with the number 1 as far as there are rows that have text in them. How do I tell Excel to fill down only as far down as the end of my table.
I would like to run this in a macro.
View 3 Replies
View Related
Jan 31, 2007
I have searched the site but cannot find an appropriate solution. I have a list of names in column a, some of the names are repeated if the name is repeated then at each occurence of the name I would like a '1' to be placed next to it in column B.
I am trying to adapt the conditional formatting formula:
= COUNTIF($A$1:$A$25,$A1)>1 and then I guess I need to add a '1' else ' null' command but I just can't seem to get the syntax correct.
View 9 Replies
View Related
Jul 22, 2014
I have a workbook where one worksheet is referencing cells from another worksheet. I need every third cell to pull from every single cell on the other sheet.
I have found on other threads that =INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+1)) worked. However when I filled down it wont increase the row reference.
ie.
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+1))
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+2))
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+3))
[ATTACH]334142[/ATTACH
The other threads mentioned it should fill down fine, but not working for me.
View 4 Replies
View Related
Jul 22, 2009
I'm working on some data dealing with time fields, and I need to have them counted in 15 minute intervals.
For example, one of the entries listed in a column has an event starting at 02:25:00 and ending at 02:55:00 (30 minutes). Along the top row are times listed in 15-minute intervals, e.g. 13:00:00, 13:15:00, 13:30:00, etc. In the corresponding cells, if the entry took place within a particular interval, I need it to say "1", otherwise have it say "0" or "-" or something denoting no event.
An IF statement was used, but it didn't seem to work.
If that's a little hard to follow, check the example attachment for a better idea of what I'm trying to describe.
In the example spreadsheet, Row 2 (manually typed in) is what I'm looking for. Row 3 is the (failed) attempt to use the IF statement.
View 14 Replies
View Related
Oct 13, 2009
I have to validate cell text are (1, Absent, 2,leave, 3 present, 4 late 5 no report)
at the same time Absent should filled by, red leave by pink, present by green, late by yellow and no report by blue.
Preferably I dont want to use VBA. I am using excel 2007 but I am not excel expert.
View 7 Replies
View Related
Dec 9, 2012
Not sure if this can or would be done in vlookp??. In my example the print page needs to get data from a list where people set.
View 3 Replies
View Related
Oct 30, 2013
I am working on an inventory sheet. Its probably only complex due to the amount of data. There are several hundred locations we service and these are in a single column, and then there is the equipment we use that is several hundred columns.
Each location uses 3 rows and the 1st needs the formula to equal 2 rows beneath it. i.e. cell E28 has the formula =E30. I basically need to fill down, but only put that formula into every 3rd row, as the two rows beneath this row need to stay blank, or show number i manually put into either.
The second part of this, is there a way once that is done, to just select those cells, and fill to the right all the way? Again, i do not need to fill the two rows under that one.
I have attached part of the sheet .....
The green cells are the ones that need the formula, and those will be copied down, and right. the others need to be left alone. I have been sitting here for an hour manually entering in the =XXX for each cell.
View 4 Replies
View Related
Feb 17, 2014
I got a list with numbers on Sheet"Bookings"
8000
8001
8002
and so on.
When i fill in a name like commission on Sheet1"Freshbooks"
The number 8000 must be connected with that ..so that it will appear on my last sheet "Ouput"
So to summ it up Sheet1 I fill in commision on table H the number 8000 must apear on sheet"output"
Is this possible in a Formule or in a VBA line???
View 1 Replies
View Related
Nov 12, 2008
filling an Array then I have used in this macro?
View 3 Replies
View Related
Dec 5, 2008
I have a userform with a textbox and a combobox
textbox input will be a house number
combobox will be a list of all the street names that have that house number in my database
column Q are house numbers, column R are street names
when a user inputs a number into the textbox and tabs to the combobox,
for each cell in column Q of workbooks("proposals.xls").worksheets("database") that has the textbox1.value I want the cell value of .offset(0,1) to be added to the combobox.
View 10 Replies
View Related
Jan 25, 2010
Is there a way to fill all rows of a column with the same formula without having to copy and paste it? My table is huge (about 6000 rows) and I need to perform the same computation on each row.
View 8 Replies
View Related
May 11, 2009
If I have a dropdown menu in A1 which has the following data:
Chair1
Chair2
Chair3
and so on..
How can I create a funtion to B1 which would work so that if there is the text "Chair2" in cell A1 it would automatically give a value 200€ to B1?
View 2 Replies
View Related
