Bi-dimensional Dynamic Range
I've tried the below formula to get a bi-dimensional dynamic range that is defined by the last no blank column and last no blank row, too.
=A1:INDEX(A1:D18,MAX(NOT(ISBLANK($A$1:$D$18))*ROW($A$1:$D$18)),MAX(NOT(ISBLANK($A$1:$D$18))*COLUMN($A$1:$D$18)))
Does any one have a different approach to get this without using the offset function?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
2-dimensional Date Range With Dynamic Copy And Paste
Some of you may have seen my 2-dimensional Date Range Criteria thread . By request, that thread has been closed and I am opening a new one on a related, but infinitely more complex note. The solution to the previous thread worked, but not well with my project. I'll go a little more in depth into how my project works, and try not to be too detailed and irritating. I am trying to create a homebrew Learning Management System for some of the Training courses that my department offers. I've posted here a few times on the same project, and the advice from all previous posts has been excellent. What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question. The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the 2-dimensional Date Range Criteria of the report. What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.
View Replies!
View Related
Join Dynamic Multi-dimensional Array
the built in "Join" function can join all elements of a 1-dimensional array into a string with delimiter. Now, how do I do that with multi-dimensional array if I just want to join 1 dimension of it. For instance, I have: m = 10 n = 20 Redim my(m, n) 'assign values to array here... 'I want to join, say, my(5, 0 to n) only 'How to do that without declaring a new 1 dimension array? Also, I want to write a join sub/function to do the above for n-dimensional array, do I need to write each one for every number of dimension (1 sub for 2-dimension, 1 sub for 3 dimension, etc.)?
View Replies!
View Related
Creating A 2-dimensional Array From A 1-dimensional List
I've been a lurker on this forum for a long while and it's always been able to provide me with lots of excel tips, and for that I am grateful! But this time I have a question that I can't find the answer to here, or anywhere else on the web after a few hours of looking. As a note, I'm not very experienced with Excel, probably somewhere between novice and intermediate. What I need to create is a 2-dimensional array of data. The vertical (y-axis) are the tools, and the horizontal (x-axis) are the jobs. Where the y-axis and the x-axis intersect, will be the quantity of tools needed for that specific job. There are almost 1500 tools, and 100 jobs.
View Replies!
View Related
2-dimensional Date Range Criteria
Attached is a really simplified version of what I'm trying to accomplish. I'll attempt to get my brain working well enough to explain it: For each row, I have a person's name, their department, their site, and then a series of dates (representing dates of attendence). The four columns after the dates ("Counts" is the first) are me trying to keep all the right numbers in mind, and are the routes I've been trying to take on this. I have also tried to make a custom formula called ClassInRange, which isn't playing nicely. What I eventually need to populate is the little 2x3 section at the bottom where the three sites and the two departments I'm working with (down from 5 sites and 8 departments for the sake of sanity). What I need to be able to do is populate those cells with something that will tell me how many people from which site and department have attended something within the date range. So, for example, I need to know that the 2 guys in Wellesley attended something between January 1st and December 31st of 2007. (American date style, in case anyone's lost) If you're able to help, please keep in mind that I'm not the end-user; I'm just the slightly crazy dude trying to create this thing and make it simple to use. Eventually, the Dept/Site box will be part of a template which is copied and pasted up to four times on a report sheet, each fed by different date-range criteria. Also, the important information is the person's name, dept and site, which I hope gives some reason behind the organization; we want to find the person easily.
View Replies!
View Related
Fill Range From Multi-dimensional Array
I searched and found a few posts about transposing arrays into a range of cells, but none of them seemed to solve my problem. So, my problem is, I have a .Net assembly which provides various functions to allow Excel to access our Oracle DB stored procedures/tables, etc. This assembly is exposed via COM Interop. I call the GetSPINTypes() method, which returns me a list of type pairs (ID, Name), in a CSV string format. I split the CSV into rows, and then put each row into a 2-dimensional array. I then need to dump that array into one of my sheets in Excel, so I try to do the usual Range.Value = Array, but this sometimes tells me there is a type mismatch, and most times just doesn't fill the range. I've checked my arrays in the watch window, and they have definitely been filled in correctly, the values just don't appear when they are put into the sheet. See the code I'm using below:......
View Replies!
View Related
How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)
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
View Replies!
View Related
Dynamic Range For Pivot Table :: Error : Range Is Invalid
I 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.
View Replies!
View Related
Convert Dynamic Range To Static Named Range
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.
View Replies!
View Related
Dynamic Range 2003: Run Calculations On This Range
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 Replies!
View Related
Set Range Variable To Growing Dynamic Range
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.
View Replies!
View Related
Dynamic Name Range
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?
View Replies!
View Related
Dynamic Range In Graph
I have a table that has formulas that read off a pivot table. When the pivot table is updated, the other non-pivot table is updated. I created a graph off of the non-pivot table data. I want to do a bar chart, but if data is added to the pivot table (and then through formulas added to my non-pivot table) I want the graph to automatically update with the new fields. I can't figure out how to do this For example, if my table had Grapes, apples and peaches and their quantities in cell A2:b4 if I choose range a2:b5 I don't get any data in my graph at all, if I choose the correct range a2:b4, I get a bar chart with Grapes, Apples and Peaches and thier quantites. If I added Strawberries to the Pivot table, the non-pivot table would update, but the graph is not grabbing row 5, I have to manually change the range to a2:b5.
View Replies!
View Related
Dynamic Range Within VBA
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.
View Replies!
View Related
Sumproduct With Dynamic Range
I have a sheet with data in A1:A50 also in B1:B50 and amounts in C1:C50 I calculate using the formula sumproduct((a1:a50="yes")*(B1:B50="RED")*(C1:C50)). How can I change this to allow for rows being added on a regular basis.
View Replies!
View Related
Dynamic Named Range Somehow Changes
what would cause the formula for a dynamic named range to keep changing on its own? I enter the following formula: OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!D:D),1) Which I save and somehow gets changed to the following formula: OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!XFD:XFD),1) This is happening to several named ranges I created. I’ve tried to correct this by editing the formula which seems to work but changes again. I’ve also deleted the named range and recreated it with the same change process taking place. In case it matters the named ranges are being created on a table imported into Excel via MS Query.
View Replies!
View Related
Dynamic Named Range ..
I need to create a dynamic named range called "DIVLOC," that corresponds to a sheet called Divloc_List in the workbook. Then, I need to take this range and perform a vlookup against it. I tried to create the range in the sheet itself (it wasn't dynamic), and then used that in the vlookup in the vba code, but I got error 2402 (I think). I'm not sure how to define the dynamic range in vba code, and then use the range in the vlookup.
View Replies!
View Related
Formula For Dynamic Range
I am working with a dynamic range on sheet name "DataPrep", the named range is "TGSDataSourceRange". I need a formula that will look from row 2 down to the last row for columns "A-H". The purpose is to identify the active range. I think the function is "Counta", but I can't get it right.
View Replies!
View Related
Referencing A Dynamic Range
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))
View Replies!
View Related
Dynamic Range For Months
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 Replies!
View Related
Dynamic Named Range
I am trying to create a dynamic named range to select a list of text entries from a number of different columns that I am using for Data Validation. The columns have entries of variable length, none more than 30. What I am seeking to achieve is that having selected a heading in cell C3 the available list in DV dropdowns elsewhere are governed by the contents of C3. I have created a defined name, StartPos that uses the contents of C3 to evaluate to the cell reference (e,g $H$4) of the topmost cell in the relevant column. However when I try to use it in the usual formula =OFFSET($A$1,0,0,COUNTA($A:$A),1) formula I try =OFFSET(StartPos,0,0,COUNTA(StartPos:[ ],1) I get stuck at what to put in the brackets. Ideally I would like to use the COUNTA to go down to the last entry in the column. However, even when I try to give the range a height of 30 rows: =OFFSET(StartPos,0,0,30,1) I still get an error.
View Replies!
View Related
Dynamic Range Reference
I have a sumproduct function that is aimed at a big block of data. Is there a way that I can write the function to where it will always find the last row of the data set and adjust accordingly? For example if right now the range is A1:G2000 and next month I add 20 rows of data I want the sumproduct ranges to automatically adjust to A1:G2020.
View Replies!
View Related
Dynamic Range Name Creation
I'm needing some code that will program-magically do the following interactive stuff: 1. Go to the first cell that will be the start of my range, say "D1". 2. While holding down the "shift" key, press "end", then "down". All of the desired cells will then have been selected. [side note: how can I detect that the "d2" cell is not blank - I don't really want 65K cells in my range?] 3. Next, "Insert", "Name", "Define", then typein the desired name range ("schoolList", here) and press enter. This would seem to be a case for using the macro recorder but the range will always come out hard-coded, rather than the cells that would be selected using an "end" "down" selection.
View Replies!
View Related
Sum Dynamic Range
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.
View Replies!
View Related
Dynamic Range With 2 Columns
I'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)
View Replies!
View Related
Dynamic Range Formula
I have a dynamic range formula that refers to a list that I've set up in row 65 of my sheet. Each time I go to the cell the has the data validation referred to in this range, the drop down list shows the list from the last entry and with more spaces than needed. =OFFSET('GROCERIES AMEX'!$E$65,0,0,COUNTA('GROCERIES AMEX'!$E:$E)-15,1) The " -15" is the number that varies all the time. Is there any way to remedy this so the formula behaves as if the list was located in Row 1?
View Replies!
View Related
Dynamic Range Setting
This should be easy ... can't figure it out. If I want a formula that wants to perform an action (NPV, but I don't think that matters) on the cell directly above and the next "X" (say 20 cells/years of cash flow) to the right of it, how can I set it up so that I can copy the formula across as well as change "X". (Obviously, it's easy to select a 20 cell range and copy it across ... but what if I want the length of the range to by dynamic (ie I want to switch to a 40 year NPV instead of a 20 year)?)
View Replies!
View Related
Select A Dynamic Range
This will be an easy on for you seasoned programmers. I want to find the last used cell in a column, and then select an offset range based on the location of that cell. For example, in the attached workbook, I have a button at the top that activates a macro to add a new week to my time sheet. This simply copies the contents of the current week, and adds it at the bottom. What I would like to do next is clear the data from the newly added cells, leaving the formulas in columns A, B, C, J and K. In the attached example, I have just added Week 3. I would now want to locate the last used cell in Column C, (C24), then offset from that cell to select the range D18:I24, and clear the contents of those cells.
View Replies!
View Related
Dynamic Range Selection
I am filtering a list in Column H and depending on what criteria I filter on I need to be able to select only what I have actually filtered for. I am using the code below to find the lower right corner of my range that I'm trying to select and this works great.
View Replies!
View Related
Dynamic Range Performance
I have a relatively small Excel workbook that is using numerous lookup and Match function combinations. I also have a number of dynamic ranges defined and a few UDFs. Problem is I am encountering some fairly severe performance issues. Changing a single value on a sheet is taking ages. I suspect it may be due to the workbook dynamic ranges recalculating. Is there a limit to the amount of dynamic ranges in a workbook? And when do they get recalculated? Could it be something to do with sequencing of the calculations?
View Replies!
View Related
Dynamic Range Formule
I've a little problem with my formule in Excel. I 've checked the explanation of dynamic ranges on the site here but I can't seem to find a solution myself. I have a little table with data that will be filled in and change a lot. Now I want a table next to it with a result of a filter that also can change. All this has to be done with formules, I can solve this problem with VBa but I would like to learn how I can solve this with formules. I've uploaded a small example that shows my problem and the desired outcome.
View Replies!
View Related
Dynamic Range In Macro
I am attempting to create a dynamic range within a macro. Each week, a list of performance factors is recorded for employees. The macro is about to do an Advanced Filter to create reports based on user-provided criteria. Since there is new data every week, the range used for the Advanced Filter is dynamic. The code for the Advanced Filter refers to a range entitled “Database.” My problem is in defining “Database.” It is currently 534 rows and 8 columns, starting at A1. The relevant portion of the macro currently reads: Sub Check1() ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _ "=OFFSET('Loan Officer History'!$A$1,0,0, COUNTA('Loan Officer History'!$A:$A),8)" Range(Database).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "J1:Q2"), CopyToRange:=Range("J5:Q5"), Unique:=False End Sub When running the macro, the debugger stops at the line which starts "ActiveWorkbook.Names" and tells me “Run-time error ‘1004’. The formula you typed contains an error.”
View Replies!
View Related
Combobox Dynamic Name Range
how to control the contents of one combobox based on the selection made in another combobox. But, for some reason, ComboBoxTime1 (as shown in the code below) will only display the first item in the Dynamic Named Range when the user clicks on the pull-down arrow. The Named Range is consistent in size (4 cells) for each of the selection options in ComboBoxDay1. why only the first of four options are displayed in the second combobox? Also, when a selection is made in the first combobox, the second combobox immediately displays the first item in the Dynamic Named Range (the one selection option it allows). I would prefer for it to remain blank until the user has selected an item from the pull-down list. Private Sub ComboBoxDay1_Change() Dim strRange As String If ComboBoxDay1.ListIndex > -1 Then strRange = ComboBoxDay1 strRange = Replace(strRange, " ", "_") With ComboBoxTime1 .RowSource = vbNullString .RowSource = strRange .ListIndex = 0 End With Else LblTime1.Caption = "Time 1:" End If End Sub
View Replies!
View Related
SUM Dynamic Range ...
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.
View Replies!
View Related
Dynamic Range In Another Workbook
I am facing a problem in using a dynamic range(name) defined in a different workbook, when that workbook in not open. I have defined a dynamic range, 'complete' , in Main Assumptions.xls as shown below = offset( sheet1!$A$16, 0,0, counta(sheet1!$A:$A)-6, 5) I am trying to use 'complete' in another workbook, RCL.xls, as shown below. =VLOOKUP(C11, 'C:Chein[Main Assumptions.xls]Sheet1'!complete,5) This works well as long as Main Assumputions.xls and RCL.xls are open. However, When Main Assumptions.xls is closed and I try to open RCL.xls, I get a warning saying, Excel can't find 'complete' in Main Assumptions.xls. There are two possible reasons: 1. the name you specified may not be defined. 2. The name you specified is defined as as something other than a rectangular cell reference. Does that mean that I can't use dynamic ranges defined, in other workbooks?
View Replies!
View Related
Dimensional Table
i have a problem with my report. there is a dimensional table: DIM RESULTSDIM_111.1DIM_111.8DIM_111.2DIM_25.3DIM_25.5DIM_380.2DIM_380.1DIM_380.35 i need to make a new table in wich all "dim" will repeat only once and the results will be the average of the results that belongs to the same "dim" the times that the "dim" repeats can change and the "dims" could be not only till 3
View Replies!
View Related
Macro – Two Dimensional Look Up
The workbook has two sheets. Sheet1 has numbers in column A going down the sheet and dates in row 2 going across the sheet starting in column B. Sheet2 is similar but the column and rows do not line up with sheet1. I would like the macro to look at the numbers in column A and the dates in row 2 on sheet1 and find the same match of number and date in sheet2 and enter the value from sheet2 into the appropriate cell in sheet1. I understand it can be done in a formula with index and match but I would prefer a macro.
View Replies!
View Related
Formula Error #N/A When Dynamic Range
I use the function OFFSET for names in order to be able to add new rows below the table without the need of changing the range of the name. However, there are error produced if I work it this method. I am not sure whether it is due to the formula but maybe because of the data or format validation ? Generally, in my cells I include if(CellRef="";"";formula) in order to copy the formula down. Pls also look at the defined names ....
View Replies!
View Related
How To Separate Out A Dynamic Number Range
I have a range of numbers that are not completely sequential and I'd like to separate them out into their individual numbers. In cell A1 I have displaying "1-30" and then in cell A2 I have "50-72" and A3 "100-105", et cetera. I 'd like to have cell B1 through B30 display 1 through 30 (1 in B1, 2 in B2, 3 in B3...) respectively, and then cell B31 through B53 would have 50 through 72. I need to create a formula that can dynamically pick up the last number after the "-" so that it can work for any number range of any length. I've tried using left and right but that doesn't help when moving from the 10's digits to 100's digits.
View Replies!
View Related
Dynamic Named Range Without Offset()
When I make a dynamic named range, I typically do something like: =offset($A$1,,,counta(A:A),counta(1:1)) I remember awhile ago I saw (within some of the comments on Jon Peltier's site, I believe) a way to make a dynamic named range without using offset. Does anyone know how to do it?
View Replies!
View Related
Dynamic Sum Range- The Last Numbers
Can't seem to find a way to create these sum ranges. In column B, I want to sum the last 4 numbers in column A. ....Unfortunately, there are many (varying) blank cells in column A. ....Here is an idea of what I'm going for: For cell B19 =SUM(OFFSET(B19,0,0,-COUNT(B3:B100)=4)) ....This sum will be filled down. I want to sum the last 4 numbers, regardless of the length of this range.
View Replies!
View Related
Dynamic Name Range Incorporating VLOOKUP
I'm trying to create a dynamic Named Range using VLOOKUP in place of a sheet name. (Using Excel 2003 & Win XP Pro SP2). To illustrate:- Employees.xls contains employee's details on separate sheets for each department, e.g. Production, Admin, Sales, Personnel, etc. Each sheet is 12 cols. and 1 header row. Sheet 1 of this book contains a 2-column Master List of all employees and their departments. MyBook.xls has an employee's name in A1. I can get his department by using =VLOOKUP(A1,MasterList,2,FALSE) and what I want to do is incorporate that into the following in place of 'Admin' so that the range will refer to the correct department for whoever's name is in A1. =OFFSET('[Employees.xls]Admin'!$A$2,0,0,COUNTA('[Employees.xls]Admin'!$A:$A)-1,12).
View Replies!
View Related
Count Blanks In Dynamic Range
I want to count blanks in a horizontal range (all in one row) that will change (dynamic range). The values in the range could be numbers or words. Some values may be added to the end, but there may still be some empty cells to the right of the last value. My goal is to count blanks in the range up to the last entered value, but no beyond that. As an example: A6 = 2 B6 = empty cell C6 = 2 D6 = empty cell E6 = tt F6 = empty cell The range for the count blanks would be A6:E6. F6 is not included because the last entered value is in cell E6. The answer (count blanks in dynamic range) should be 2. I have got these 3 formulas to work, but it seems that there must be a better (shorter, faster calculating, more elegant) formula than these: =COUNTBLANK(OFFSET(A6,,,,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+TRANSPOSE(ROW(INDIRECT(""1:""&COLUMNS(A6:F6))))))) =COUNTBLANK(OFFSET(A6,,,,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+{1,2,3,4,5,6}))) =COUNTBLANK(A6:INDEX(A6:F6,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+TRANSPOSE(ROW(INDIRECT(""1:""&COLUMNS(A6:F6)))))))
View Replies!
View Related
Define A Dynamic Column Range
I'm trying to define a dynamic column range call 'Cost' If it starts in Column C, row 4 [C4]. I want the range that to go down to the Row I have defined as 'subtotal' The user will be able to insert new rows above 'subtotal' How can I define Column C4 so that any new row added will be including in the defined row range 'Cost'?
View Replies!
View Related
Keeping A Named Range Dynamic
In the following line of code in my subroutine I name this range which runs from A2:J2, the problem is later in the routine I delete certain unwanted columns which then shrinks this range. I always want it to be 10 colums wide. How do I make it dynamic with vba? ActiveWorkbook.Names.Add Name:="TranslateShow", RefersTo:=Worksheets("TemplateTest").Range("$A$2:$J$2") Can I also substitute the workbook actual name for ActiveWorkbook?
View Replies!
View Related
Dynamic Range Selection For Charting
I have three rows that each attempt to pull in data from a range on different worksheets. All but one return nothing but "#N/A" values. The one row returns values from the proper range. (User selections determine which row will have data). Second, I successfully pull data from the row containing actual information into another row, using the following formula: "=OFFSET(E$36,CHOOSE($Y$1,0,1,2),0)" on a cell-to-cell basis. The value in $Y$1 chooses the row to look at, based upon a dropdown selected on another worksheet. So I now have a row with =OFFSET(E$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(F$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(G$36,CHOOSE($Y$1,0,1,2),0) and so forth. I can find the last value in the row, but I cannot find a way to extract the address from that, and create a table that will use as a series the last 26 values in the row. For reference, I use "=LOOKUP(9.99999999999999E+307,E49:FD49)" to determine the last cell with a value (gleaned from a Dueling Excel Youtube Video, - very helpful!). I hope the above will be clear enough, but if not, I'll be happy to provide additional information / clarification.
View Replies!
View Related
Dynamic Named Range Cause Crashes
Anyone run across this.....Over the past 1-2 years...I have setup a number of complex excel workbooks, that automate a lot of reporting for my employees. Suddenly, over the past couple months, I am finding that if I try to go back and edit many of my named ranges (created a while ago) that were created dynamically: "=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A$4:$A$65000),4)"...excel crashes. I can't figure out why. All I have to do is simply CLICK in the "Refers to:" field of the "Define Name" window (for the dynamically named range)....and I get "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience." If I drill down on "What is in the report", I see this "Error signature":..
View Replies!
View Related
|
TRACKER
