I have a spreadsheet where the start date is manually entered in Cell B1 and an end date is calculated in Cell B3. I would like to have the dates between the start and end date, with a step value of 7 days show in the rows under Cell B3. How do I get this to work?
View 3 RepliesPrior to declaring a variable i used this code and it worked:
Sheets("442000ON-1+6").Range("C7").formula = "my formula"
range(C7).select
Selection.AutoFill Destination:=Range("C7:N7"), Type:=xlFillDefault
Than i declared a variable b/c i have a number of formulas i need to use but still needed it to fill the formula in a series after the formula ran. I started getting an error.
Fix was:
Dim formula1 As String
formula1 = "=SUMIF(Pivot!$A$6:$A$108,'442000ON-1+6'!$C$3,INDEX(Pivot!$B$6:$BP$106,0,MATCH('442000ON-1+6'!$B$1,Pivot!$B$5:$BP$5)))"
Sheets("442000ON-1+6").Range("C7").formula = formula1
Range("C7").Select
Set SourceRange = Worksheets("442000ON-1+6").Range("C7")
Set fillRange = Worksheets("442000ON-1+6").Range("C7:N7")
SourceRange.AutoFill Destination:=fillRange
Excel 2003, I have a list of products with corresponding prices located on a hidden worksheet. I have a spot on another worksheet where users can select a certain product and next to that cell there is a column for price. What I want excel to do is auto-populate the price cell based on what product the user selects. For instance, if the user selects OD2000N, then I want the price to auto-populate with the corresponding price that is found on the hidden sheet. Like I said before, I know this has probably been answered, maybe even mulitple times but I can't seem to find the appropriate thread.
View 9 Replies View RelatedAuto fill series based on Condition.(Excel 2003)
I know very little about macros (basically just concepts). I found this macro suggested in another thread (posted By VoG II…Thanks)
Sub test()
Dim Lastrow As Long
Lastrow = Range("A5").Value + 4
Range("B5").AutoFill Destination:=Range("B5:B" & Lastrow), Type:=xlFillSeries
End Sub
When this macro is run the first time it will fill in the numbers 2 through 15 (as I had hoped). Given 15 is the variable and 1 is the start of the range...
I am newer here and I want a macro in mentioned title. I have a number in column a2 and in column b2 quantity if in column a2 number is 2101400 and in column b2 i write quantity 50 so said number automatically fill series in column a like mentioned below...
View 9 Replies View RelatedI have a excel sheet of data numbers which blongs a packet of inventory where i found number series like this:
Start Range End Range Qty
2101200 2101499 300
I have to draw this onto a series like 2101200 then 2101201 then 2010202 etc.
but some time these numbers are in qty 30000 or more. I have questions
1- If any macro colud fill series suppose if write number in A2 and qty in B2 so it reads the qty of b2 and fill the series in A. If i write 1 in A2 and write 50 in B2 so the series should be auto fill 1 to 50.
My Second question is opposit of my above question.
2- if i have different number series in column a and i want them to be as start number in column b and end number in column c and qty in column d based like mentioned below example.
Series [b]Start Range End Range Qty
1 1 1 1
3 3 3 1
5 5 7 3
6
7
regards the two attached files (both are identical) except '1' has TWO Rows of data above the Macro Button and works correctly whereas '2' has only ONE Row (the desired option but does not work correctly!( Inserts Row in wrong place and incorrectly fill series)).
basically the program inserts a additional row below the last data entered, copies a formula cell from above and finally (the problem area) Series Fills a cell from data above.
I have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use as sort of a staging area to format info before bulk uploading to my product database. The products I'm working with at the moment have 10 subcategories below each of them. Sheet1 is the full product database listing. I place new subcategories in blocks of 10 that I plan to add to the database on Sheet2. Sheet2 also holds the default partial category names in cells A2-A11.
Part 1:
I am currently using the following formula on Sheet2, in column C to combine the category name value on Sheet1, with the default partial category name values on Sheet2 (A2-A11). I'll paste two blocks so you have a clear picture of what I mean by blocks of ten.
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$2)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$3)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$4)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$5)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$6)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$7)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$8)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$9)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$10)..................
I am trying to produce a line chart, which will use imported data on a separate sheet (but within the same workbook)
The imported data can be of different sizes ie column widths and length. Some columns may contain only zero and should not be included in the chart, and will be to right of the data.
I have created a named range for the data series called ChartData using =OFFSET(Data!$C$1,0,0,info!$C$7,info!$A$2) - C7 and A2 are calculations to set column and row sizes. I have tried to use this as the chart's data series, but without success.
I want the below code to run from R1C10 (i.e. cell J1) to the end of the data in the row, which will vary in position, rather than having to define where the end of the row will be beforehand (i.e. R1C21).
Is this possible?
I have data which is imported from a txt file and always starts at the same row (R19) but is variable in length (end of data R??). I want to be able to select the chart xvalues (R19C10:R?C10) and values (R19C4:R?C4) based on the number of cells with data in them for a given column. In addition I believe I'm having troubles with sheet references, when the txt file is opened the sheet name is set to the file name, since I want to use this macro on many different files I need the sheet referencing code to account for this. Anyway here is the section of code which seems to be giving me trouble (the initial sections just create a new column of data which needs to be referenced as the xvalues) ...
View 3 Replies View RelatedI need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.
View 2 Replies View RelatedI have create an excel formula that I need to input data daily.
Every day, I need to insert a line and input my new data.
Below is my formula
=IF(V159="Skip","Go",IF(P160>0,IF(COUNTIF(C60:D154,">"&H159+3),"Good"),IF(COUNTIF(C60:D154,"<"&H159-3),"Good")))
The problem is that when I insert a line. Most of my variable will change according except C60:D154
As you can see below
V159 change to V160 = This is good
C60:D154 do not change accordingly. How can I make it change accordingly ?? Why it is not changing when I insert a line ?
=IF(V160="Skip","Go",IF(P161>0,IF(COUNTIF(C60:D154,">"&H160+3),"Good"),IF(COUNTIF(C60:D154,"<"&H160-3),"Good")))
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
I want to return the data from a list in a cell on worksheet1 if I input a variable on worksheet2. I need to avoid VBA and pivot tables.
Example, if I put "Expense" in the input cell on worksheet2, I want to return a list of each row that has "Expense" in it on worksheet1:
Worksheet 1 (ColumnA/ColumnB):
Revenue/45,000
Expense/20,000
Asset/43,000
Liability/21,000
Revenue/6,000
Expense/9,000
Expense/11,000
Liability/13,000
Worksheet 2 required output (no row gaps or spaces):
Input cell "Expense" - in A1
List required (A3:B5):
Expense/20,000
Expense/9,000
Expense/11,000
I have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?
View 7 Replies View RelatedI'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 RelatedBet this is simple to someone...
How can a continue this fill series down a column?
=$BC$3
=$BC$4
=$BC$5
=$BC$6
I would copy, paste and edit only there are 2500 rows!
I want to run a series of cells but how to do it. E.g.
Cell A1 to contain the data 2A
the next cell (A2) to read 2B
the next 2C etc etc
I've tried just dragging the cells down but it just duplicates 2A, 2B, 2C whereas I want it to fill the next cells as 2D, 2E etc
And I've tried using the fill series option but neither seems to be working (presumably due to the letter).
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 am not able to drag & fill series only in one particular sheet, in other sheets this function is working fine.
View 2 Replies View RelatedUsing XL07 I'm trying to programmatically set the fill type to solid and the colour to a colorindex constant for a series in a chart.
I can select/activate specific charts and series, but can't work out how to modify the properties of the series.
I can't for the life of me piece together the method, properties and syntax for doing this.
I would like to use the Fill Handle to create a column of dates.
The dates must be formatted like this: DD/MM/YYYY HH:MM
The Fill Handle completes the series, but not the way I want.
I want a series that changes the day only. So:
02/02/2012 09:00
03/02/2012 09:00
04/02/2012 09:00
For whatever reason, Excel currently adds a minute or two to the minutes and does not increase the date.
13/2/2012 09:20
14/2/2012 09:20
15/2/2012 09:20
13/2/2012 09:21
[Code] .......
I am trying to use the auto fill series but I need the series to start over every time the value change in another column.
If i use the auto fill series, it will copy 1,2,3,4,5 and so forth til the end of the column. In this case, i need the auto fill or some other function that will restart once it encounters a different value under the ID column. I've started below to show an example.
IDNo
0.11141
0.11142
0.11143
0.1121
0.1122
0.1123
0.1124
0.1125
[code]....
How to make fill series number with simple excel formula, like table below :
col.a (desired result)
col.b
1.
john
2.
lia
3.
leo margarette
4.
david lee
etc....
note : not use Vba...
I have a column:
LABEL COUNT
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 2
empty cell
1 of 16
15 empty cells
I need to fill the empty cell with the "series" (2 of 2, 2 of 16, 3 of 16). This is just an example of data - there are all different series - 1 of 12, 1 of 5, etc.
Anyway - was hoping to highlight the whole column and be able to auto fill instead of having to extend each series. I have tried straightforward fill series which doesnt work of course. I have tried go to special - blanks but cant find right combo to make it work.
Will it work or do I have to either do manually or with a macro?
I need to fill multiple number series in the same column. Example:
Have
Want
1
1
[Code]....
I have a list of 600 unique project IDs in column A. I want to create a second list in Column B that references the contents in Column A, first row then duplicates that item for two subsequent rows, then a formula in the fourth row of Column B a reference to the second project ID in Column A row 2, with two duplicates in the fifth and sixth row.
Column A
Row 1 000891.AB
Row 2 000892.CD
Need to create:
Column B
Row 1 000891.AB
Row 2 000891.AB
Row 3 000891.AB
Row 4 000892.CD
Row 5 000892.CD
Row 6 000892.CD
I would like to find the blanks within a column. Using the available cell values above and below compute a sort of linear function then, using that linear function fill in the blanks in between. So the values are linearly increasing or decreasing (whatever the case maybe) to the next available nonblank cell value. spreadsheet attached.
View 3 Replies View Related