I have a list of months (Jan to Dec) in column "A" sorted in ascending order. The current month is Sept. I have no idea which row the range will start or end on (Since the # of records vary from month to month) and there is data both above and below the range. I need to create a dynamic range of the current month. The dynamic range must start and end with the current month (eg Sep).
View 9 RepliesI need to create a validated list of month choices, and the list needs to be filled with the names (Month and year, I.E. Feb-07) of the last 13 months for which we have data.
(We get our data about 8 weeks late, so being that it is mid april now I only have data now up to the end of Feb-08. I have created a chart in which the viewer can change the month to view but up to now it has only been for the current year, now they want to see the past 13 months, obviously I don't want to go in and change this every time the next months' data is entered.
So, what offset formula do I use to reference the month-year names which are spread across Row 1, presently in columns C thru N, and the next months data (March) will go in column O, then April's in column, P, etc
I have tried several variations of this; =OFFSET(C1,0,0,0, COUNTA($1:$!)) but how do I get only the last 13 months?
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I am trying to update our sickness spreadsheet for work to make it a little more 'intuitive' and less cumbersome to use. There are dates (by day) accross the top, with staff names below. If someone is off sick, the manager writes the number of hours lost to sick in that day, under the correct day, then uses back color / font color to show its sick.
Our work uses a 3 periods of absence or more over a rolling 12 months and the staff are issues with a warning, same with over 8 days off sick. If they are off for longer than 14 days they are long term sick.
I would like excel to show whether each staff member has met any of those criteria in the last 12 months by either highlighting their name or putting text into an adjacent cell.
I have tried using offset, named ranges and anything else I could think of to automatically increase the range +1 day very day, but I cant !
Attached File : plan of staff for 2014.xlsx
I'm trying to split an amount within a given month:
Customer
From
To
Total number booked
Total amount booked
A
3 March 2013
5 May 2013
458
£5,000
Therefore, assuming that the number and amount booked is done evenly on a daily basis and considering the fact that in March, the period is 3 March - 31 March, while in May it is 1 May - 5 May, is it possible to have a formula that calculates the amount by month?
I have been using =D2/(DAYS360(B2,C2)) to find out the daily number booked, then multiplying this by the days in a given month to find out the monthly value, but is there a more elegant way of doing this where I can define the month in another sheet (Col. B), to return the following in Col. C and D?
Customer
Month
Monthly number booked
Monthly amount booked
A
March 2013
207
£2,258.06
A
April 2013
214
£2,338.71
A
May 2013
30
£322.58
I have found the answer to several of my excel questions here in the past, but I'm completely stumped on this one.
I have two dates:
Jan-08
Sep-08
And I need to know if the range of these dates (January-September) falls between the range of two other dates:
Jan-08
Jun-08
Basically I'm checking to see if the range of months Entered falls in the First half of a year or the second, or both
I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:
1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red
My goal is that the formula in this named range references the prior 12 months. This does reference a twelve month range in my workbook, but not the prior 12 months based on this month being MARCH.
DATES =INDIRECT("sheet1!A"&MONTH(TODAY())+1&":A"&MONTH(TODAY())+12)
Sample workbook for Excel forum.xlsx
I have multiple lists like company A company B so on and on. each has multiple accounts maturing at different dates.how can I group accounts maturing in one particular month? the maturing dates are different in some accounts and same in others.
company A
account no maturity date
account no maturity date
company B
account no maturity date
account no maturity date
so company C, company D etc..
All are on different sheets, I tried to figure out with Vlook up, and filter, but could not make formula.
I have a "start date" and an "end date". Is there any way to tell how many months are included in 2007 from those two dates using a formula? For instance, from 01-Feb-04 to 01-Feb-07, there is 1 month in 2007 (January). And likewise, 01-Jan-05 to 01-Jan-08 there is 12 months in 2007.
As noted, I have the start and end dates. I don't have the "how many months in 2007".
Start Date End Date How many months in 2007
01-Feb-0401-Feb-07 1
01-Jan-0301-Jan-06 0
01-Apr-0401-Apr-07 3
01-Feb-0301-Feb-06 0
01-Mar-0401-Mar-07 2
01-Feb-0401-Feb-07 1
01-Dec-0401-Dec-07 11
01-Jan-05 01-Jan-08 12
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.
View 3 Replies View RelatedI am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.
View 9 Replies View RelatedIm sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies View RelatedI am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:
=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
View 4 Replies View RelatedLine of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.
Range("DataSummary),(??,??) doesn't work.
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
I have this macro named ABC to bring the name ranges to another worksheet. The name ranges are already saved in the workbook with the below name range. I used "***" so that this formular can go to the last cell that has "***" on A column and it worked untill someone typed "(xxx)" and the range now stops right before the "(xxx)"
=OFFSET(Storage_Team2!$A$1,0,0,MATCH("***",Storage_Team2!$A:$A,-1),37)
What would be the best to create the range according to the specific words in a column?
i am coloring cells:
Sheets("MySheet").Range("a1:f200").Interior.ColorIndex = 5
i want a dynamic range, so something that would look like this as an excel function:
=OFFSET('MySheet'!$A$1,0,0, COUNTA('MySheet'!$A:$A),6)
i want the equivalent of this programmed (hard coded) into vba
i don't want to reference to a defined name with vba, because the user can delete that.
I wonder if there is a way to Sum the total of a Dynamic Range so that the summary will be presented in the upper Cell of the summed column?
(please see the attached picture).
The reason is to anable FREEZING of the first row (display the totals all the time even when scrolling down) as the list will get very long after a while.
I am looking to sum a dynamic range and can't quite figure it out. The data expands down rows every week, and I want the sum formula (the final row) at the bottom to include the newly updated rows. I can't dynamically name the numbers to be summed because the dynamic named range will include the sum number and create a circular reference. For example:
5
4
3
2
14
the bottom row (14) is the sum. Next week, my numbers are
5
4
3
2
5
19
I have a macro that will automatically put the sum formula in the correct row and column, but can't quite figure out the formula.
I am currently recorded a macros for a sumif formula. But since the number of rows will not be same every time.
I want the code for a dynamic range.
VB:
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('V1 Pivot'!R5C2:R32C2,'Task Level Tab'!R7C2:R30C2,'V1 Pivot'!R5C3:R32C3)"
Range("E7").Select
[Code] ....
I need the code for formatting to be edited, so that it work dynamically for any range
VB:
Range("B16:L16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
[Code] .....
Here the columns "B" and "L" shall be constant, but the number 16 may vary depending upon the number of lines in the sheet.
I have column A fill with dates and column B with customer code. I'm looking to count the number of time the customer code (column B) is repeating but ONLY in previous year. Meaning that the count range must be adapt for each customer code. Will a countifs is able to work with this request?
View 6 Replies View RelatedI am working with loans, and I'm trying to program a routine that adds every installment a client owes, so I can know the total amount owed, depending on how many unpaid installments she has. Let say for a client I have every installment in column A (rows 1 to 5), and in column B, I want to add them all. The output should be
Cell B1: "sum(A1:A5)"
Cell B2: "sum(A2:A5)"
Cell B3: "sum(A3:A5)"... and so forth
Since I have many loans, I need a dinamic routine to that sums every installment, where amount of installments vary from client to client. My proposal is (adding from the last installment to the first one):
VB:
months = 4 'an example
For j = 0 To months - 1
initial_row = ActiveCell.Row 'because I run this several times. In this example I am in row 18[code]....
For some reason this is not working, and what I see in Excel after running it is: SUM('AJ18':'AJ18'), which doesn't work.
I have data in 2 columns on which I need to do vlookup. This range for vlookup increases. I want to name this dynamic range. How do I do it?
View 1 Replies View RelatedI'm trying to name a dynamic range for A2:B10000.... I know how to use the OFFSET function but was wondering to do it using the INDEX function
Both column will always have the same number of used rows.
Another question:
If I have dates in Column A, and values in Column B....what formula do I need to get me the last value (last row,most recent Date)
1. I have a worksheet with subtotal and total row above all columns
I currently use SUBTOTAL(9, A6:A6000) and SUM(A6:A6000)
however the no of rows is dynamic based on imported data so I want the range end (i.e. A6000) to also be dynamic i.e. refer to the last cell with data in the column.
Something like SUBTOTAL(9, A6:LastCell in A)
2. I also use SUMPRODUCT to do calculations
e.g. SUMPRODUCT (($B$6:$B$6000 = "ABC") * ($A$6:$A$6000))
again how can I make the sumproduct dynamically use all rows to the last active row.
3. I have tried just setting the second part of the range reference to a very large number but am worried that this slows down the calculation. Does it?
e.g. SUMPRODUCT(($B$6:$B$100000 = "ABC") * ($A$6:$A$100000))