I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?
View 2 RepliesA lot of the Workbooks that I design for use by myself and colleagues require data to be copied in from external data sources. To avoid named ranges from failing, I always use the following method:
Calculate the length of the data set:
Code:
=COUNTA(INDIRECT("'Data Sheet'!"&"$A:$A"))
(There will be no gaps in the data, hence a count is fine.) This named range is called DSROWCOUNT.
Example named range for the data in column A:
Code:
=OFFSET(INDIRECT("'Data Sheet'!"&"$A$1"),1,0,DSROWCOUNT-1,1)
I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.
The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
I have several ComboBoxes & ListBoxes on a Userform that populate by referring to Dynamic Named Range in the RowSource property. Also on the Userform is an area to edit/add/delete items in these named ranges. If I Add or Delete an item, the Named Range does not automatically resize--or at least the Menus don't "resize" accordingly--unless I unload the UserForm and load it back up. Is there a way to refresh these Comboboxes without reloading the UserForm?
View 2 Replies View RelatedThe code below is for a macro that allows the user to create a list of contacts. The column containing the names of these contacts are then referenced by a userform (code not present) by way of a Named Range. I want to update the named range whenever the user adds another contact so this new contact shows up in the user form.
I am currently referencing the range containing the names using R1C1 style, but I cannot get the variable aspect to work correctly.
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998
becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998
I have a countif formula on another tab collecting data from these cells. I want to define a combined name DecBestFL with cells A1:A14 and G1:G14. Can I do that in Excel 2007?
On the other tab I would use =countif(DecBestFL,G5) G5 = "Y"
I'm not sure if this is possible but I am trying to find a function that will organize data that will be in a set 2-dimensional array (ex. 6 x 8) into a single list column. The trick is to omit any blanks that may be in the array so that the final list contains no blanks.
This is for a template; the data will constantly be changing so it's not a one time project where I can just sort the data to omit the blanks.
In the example I am trying to automatically have the 'Initial Group' organized to look like the 'Final List.'
Anyone know code to count columns in a named range? Got some code that i want to utilize for multiple things so i cant hard code the column count in anymore.
Dim intColumnCount As Integer
Dim X As Integer
x=0
Range("Poo").select
intColumnCount = magic voodoo code
For Each cell In range("Poo")
x = X+1
If ((x / inColumncont) > 2) And ((x / intColumnCount) <= 3) Then 'if on actual row
If IsDate(Cell) Then ' if there is a date in actual
Cell.Interior.ColorIndex = 15 ' shade actual gray
Cell.Offset(-1, 0).Interior.ColorIndex = 15 ' shade prom gray
Cell.Offset(-2, 0).Interior.ColorIndex = 15 ' shade Sched gray
End If
End If
If x = (intColumnCount*3) Then 'went thru Sched, Prom, & Actual
x = 0 ' Reset x
End If
Next
left off a bunch more if statements that formats other stuff but u get the idea... Need Magic Voodoo code replaced with working code
I have a dynamically named range the contents of which I want to clear. However, I also want to clear the cell contents in the next column beside the range. So if my dynamically named range is A20:B40 can I clear the contents of A20:C40 without changing the initially named range. Background: The data I have is the result of an advanced filter. The range is named for printing purposes without the final column.
If I then change one of the raw data I want to run the filter again. To do this I need to clear the first filter.
Range("AIT_Change_Management").ClearContents
I am trying to hide columns within a named range, where the range corresponds with the values section of a pivot table.
My code hides an unexpected set of columns. It seems to be affecting a different range, depending on the number of columns in the pivot table, which is not part of the named range, as if there was some kind of offset.
Sub hideAll()
With ActiveSheet.Range("wholeyear")
.Range(.Columns(1), .Columns(12)).Hidden = True
End With
End Sub
I would like to use conditional formatting verify that the lines of data being pulled from my application into Excel, but totaled using "=sum" are equal to totals pulled directly from my application. I believe that I should be able to use conditional formatting but am not sure how to make the formula work correctly.
To illustrate my problem: I have cells A1 (a total of a range), A2 (also a total of a range) and A3 (total of data pulled directly into my Excel report). In cell A3 I would like to verify that lines A1 + A2 - A3 = 0. This I can do without any problem, but there is rounding involved. The total of the above formula should be between -1 and 1 and still show as correct. I want the number in A3 to show up in red/bold when incorrect.
I am trying to clean up my code by using named ranges so that it will still work if/when others add columns or rows to the spreadsheet. These should be pretty easy solutions for most of you but I can't seem to find any answers online.
Here are 2 examples of the code I am trying to update:
I was thinking that replacing "A" with "namedRange" would work but I suppose that's not how the Columns application works. How can I modify the code below to work with a named range instead of the fixed column "A"?
[Code].....
For the following I would have thought that replacing "AU" with "namedRange" would do the trick as that's how things have worked for me in the past using the Range application. Unfortunately I get an error when I make the change.
[Code] .....
Complete List of People in Column AA.
Partial lists of these same people in columns A, C, E, G, I, K.
Goal: Once I put that persons name in A, C, E, G, I, K, I would like it to be deleted from Column AA.
I have a worksheet which periodically is cleared down to allow new data to be added. i.e. if 40 columns are in use 20 might be deleted. There is a calculation that depends on a named range which always changes when these columns are deleted. s there a way to remind the user to expand the named after any columns are deleted? Columns are deleted from Column H.
View 3 Replies View RelatedLet's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
While making a spreadsheet, I somehow deleted a much needed column. I didn't notice this until after I had made several changes and 'saved as' many times. Is there any way to retrieve this data? I'm not tech-savvy.
View 4 Replies View RelatedI have 30 worksheets in a workbook in which the first sheet is called summary. The summary sheet has 29 formulas with all named ranges, so one named ranged per worksheet. The issue I have is when i clear old data and add new data every month, the named range changes on some worksheets not pointing to the correct range as previous month file even if I have $
Is there any way to use a Named Range in the "Source" field in a Data Validation? I have multiple sheets in a workbook that have the same layout, and I am using alot of different Data Validations. My problem is that whenever I need to change, or add a choice to one of these, I have to go to each sheet and change the Data Validations that are effected by that change. Is there a way to use a "Named Range" or something to put all of my acceptable choices, for that Data Validation, in one spot. And make any changes I had to that field.
View 2 Replies View RelatedFiltering data using named range
Code:
Sub FilterNamedRange()
Sheets("Des").Range("A2:E10000").Delete
Sheets("BASE").Range("A1:E10000").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("cri").Range("Extract"), _
CopyToRange:=Sheets("Des").Range("A1:E1"), _
Unique:=True
End Sub
I have created a macro (data on the left hand side) that allows me to copy data and paste it into an sql file. The problem i am having is that the data varies (e.g. data on the right) so this doesnt actually work. i was thinking of naming the values within the name ranges and then writing code to see if i can copy particular named range values but i dont know if this will work.
View 9 Replies View RelatedI have the following code:
[Code] ........
HierLastRow shows as 165 in Locals which is correct. When I look at HierUID in Locals the Range is B2:B2 and not B2:B165. How can I make the Range set correctly?
I have a cell for which I need the only options for data to be entered from a list in two different tabs from which I understand cant be done but is it possible to have two drop down options for the same cell?
The attached spreadsheet should explain this slightly better...
I have a range of data that is compiled from various cells that I have named as Range1. I then have another named range - Range2 which should be items selected from range1 and copied into range2. This is to enable other work to take place.
I have created an example userform with Range 1 shown but do not know the code to copy it over using a command button. Range 1 should not change. Also vice versa if possible i.e. remove the selected item from Range2 with another button.
I have a named range TOT_BUDGET. We use the current month number to access this range's index for current budgets.
=INDEX(TOT_BUDGET,3)
My quandary is that I need to calculate the YTD budget for one of our dashboards. So, I need to get months 1, 2, and 3 and SUM them. Next month I'll need to get 4 months, etc...
I've looked at SUMIF, but it needs to match against some sort of criteria... I don't have any numeric month labels on the budgets page. I cannot figure out what range/array I would match the month number to.
Now, I know the easy way would be to put a month number at the head of each column, reference that array and do the SUMIF. Blech.
Ideally, I would love to just be able to say =SUM(INDEX(TOT_BUDGET, 1-3)). Is there an Excel function that will let me select an array slice?
------------------------------
In addition, we are getting the Total Sales number out of Great Plains via an SQL query. We summarize many accounts using a pivot table. We then access this table by using the GETPIVOTDATA function.
GETPIVOTDATA("NETCHANGE",TSALES,"FISCALYEAR",CurrYear,"FISCALPERIOD",CurrMo)
Again, how can I do a total YTD?
Is there a way I can do something similar to a foreach loop in the cell formula? I'd like to avoid macroland with this if possible.
I have a named range "ABC", range is ("A1", "A3", "A5"). Now I need to use Macro to extend the range to ("A1", "A3", "A5", "A7"), that is, adding "A7" to the existing named range "ABC".
View 1 Replies View RelatedI need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
View 2 Replies View RelatedI am using a multi-column listbox on a worksheet. When I select an item from the listbox, the value from the listbox first column should populate the first empty cell in a range of cells L:16 to L:25. This should be easy but I cannot get it to function. Below is the code I used prior to modifications I made to the worksheet.
VB:
Private Sub ListBox1_click() 'allows user to select item from list box and paste to datagrid
Dim lngLastRow As Long
Dim lngcol As Long
[Code]....
I'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.
Picture2.jpg
Using the following code when updating just one criteria with several charts
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then
[valMeasurePicked] = ActiveCell.Value [code].....
which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.