Using Cell Reference As List Title When Transposing Named Range
Jun 21, 2014
I am building an interactive sheet to profile a number of divisions based on a summary sheet of about 1200 rows and 90 columns of profiling criteria
I have attached an example but essentially:
1. I would like to be able to select from a list a value which would also be a named range title
2. Have the range information transposed into a row
What works:
1. I can transpose the range information when I use the following formula
I used OFFSET(Governance,COLUMN()-MIN(COLUMN(HGovernance)),0)
The problem:
I cant get it to use a cell reference (i.e. When I select Governance out of a Drop down list of Finance, HR, Governance etc). Having to type in "Governance" to bring back governance range information is fine, but not great when I am trying to profile the Finance or HR division as it then becomes too manual.
View 11 Replies
ADVERTISEMENT
Apr 8, 2008
I have a formula which is using named ranges-
=SUMPRODUCT(xxx-30,Ship_30)/SUM(Ship_30)
where Ship_30 name references the following-
='sheet 1'!$C$20:$C$38
The value of 20 is static, the value of 38 is dynamic. Each new time period adds another value (i.e. 39,40,41)
Having to edit each name range reference in the name manager is not acceptable since there is potentially over 30 to edit each month.
I would like to edit a cell which would have contained the value 38 (or C38) with a new value of 39 (or C39) so the range reference would be updated with the new value.
The goal would be to have a couple of rows with the following that could be edited with new values
View 9 Replies
View Related
Sep 18, 2012
I'm working with a very complex financial workbook with over 3,000 named ranges and would like code to replace the named ranges with the cell references and display it in a message box so the user can easily trace the references without removing the named ranges from the formula. There is a lot of code with the named ranges, so actually converting them to cell references is out of the question.
I found this code, but obviously it physically replaces the range names in the cell. Hopefully it can be converted to display it in a text box.
Code:
Sub FixReferencesToCellNames()
Dim c As Range, n As Name
For Each c In Range("A1:IV65536").SpecialCells(xlCellTypeFormulas)
[Code]....
What I am looking for is that a user select a cell that they want to see the references, run the code to see the cell references for that particular cell in a message box.
View 5 Replies
View Related
Nov 14, 2006
In the attached spreadsheet I am trying to always reference the sum column from Qty in Det Est.
This is because the number of roads changes for each project.
I created a dynamic range name surfacing with the following formula:
=offset(qty!$a$1,0,0,counta(qty!$A$A), counta(qty!$1$1)).
My question is what formula do I use to reference the sum column for each row on the Det Est Sheet.
View 4 Replies
View Related
Nov 7, 2012
If I have two cells and a named range mexico
A1, A2.
A1 contains the string: "mexico"
A2 is the cells that has required data validation (drop down list).
I want A2's validation reference to be dynamic, in the sense that I can it reference it to A1; converting the string "mexico" to a named range mexico
I tried inputting = INDIRECT(A1) into A2
But I lack clarity in my understanding of referencing.
What is the correct procedure here.
View 2 Replies
View Related
Dec 23, 2008
I want to customize my header title using the value of a cell within the spreadsheet. e.g.
I want the value of cell A3 to also be the header title. Is there a way to accomplish this? I am using excel 2000.
View 3 Replies
View Related
Apr 28, 2009
I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
Set rngFind = Range("rngVal"). Find(Target.Value)
'If data in column C changes, do the following
If Target.Column = 3 Then
'If the value is found on the validation list, do nothing.
If rngFind Is Nothing Then
Else
MsgBox "You must enter one of the following in this cell:"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
Else
End If
End Sub
View 6 Replies
View Related
Nov 14, 2008
I'm trying to use the INDEX function to return a value from a named range. But I'm not directly typing the named range into the INDEX argument. Instead, I'm grabbing the named range from a vlookup table (essentially, another named range). So, if "vh" is the name of the named range, I'm not using the formula:
View 14 Replies
View Related
Sep 15, 2014
I have a named range called ReportMonth and I want to embed it into this Table reference to make the number of columns that are summed dynamic:
=SUM(tblFcastTemplate[@[Column1]:[Column28]])
I tried ="SUM(tblFcastTemplate[@[Column"&(ReportMonth+1)&"]:[Column28]])"
View 4 Replies
View Related
Apr 22, 2014
I am attempting to obtain the last non-empty row in a column of a spreadsheet by using the following formula
=SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A))))
This works fine.
However, I'd like to make it more dynamic and be able to obtain a usable column reference (i.e., the A:A portion of the formula) from a named range (single cell).
View 1 Replies
View Related
Apr 30, 2009
How would I used the indirect function to reference a named range in the workbook in formula creation.
For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:
=sum(indirect(A1))
In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.
I'm using Excel 2007.
View 9 Replies
View Related
Oct 5, 2006
I have two workbooks wkA and wkB. I have a named range in wkB called BName.. can someone tell me whether it is possible to set a range variable (say, raA) from wkA to this named range in wkB. I have tried something to the effect of set raA = wkB.range("BName") but this doesn't work.. I've also tried set raA = [wkB.name].range("BName") to no avail. I'm wondering whether named ranges can actually be read from other workbooks.
View 4 Replies
View Related
Feb 14, 2008
I have a split named range covering non-contiguous areas, ie:
Name: status_web
Range: =$F$14:$I$122,$F$700:$I$746,$F$798:$I$830,$F$905:$I$933
This appears to work fine when selecting the name however it returns a #VALUE! error when using it in a formula, eg: = COUNTIF(status_web, "*p*"). Is there any way to get this to work, or do I need a separate named range for each contiguous section? NOTE: Not sure whether this extra fact would through a spanner in the works, but I am referencing this named range from a separate workbook.
View 3 Replies
View Related
Jul 21, 2006
I have a macro which inserts rows at certain places in a data table. This works fine. I then want to copy the formulae from the the row aboove or below (dependent on certain criteria). This sort of works.
I have is that when the row is copied, the row numbers within the formulae do not change relative to the new row. Eg if I copied down "=A6" one row, the formula still says "=A6", rather than "=A7".
I have tried variations of
Selection.PasteSpecial Paste:=xlPasteFormulas
I also tried the AutoFill method (but this didn't work at all when I tried it with a whole row)
Previously, I would have selected ranges and then pasted on the active sheet. However, I am trying to re-educate myself in writing macros that do not 'select' ...
View 5 Replies
View Related
Jul 22, 2014
I got a very technical sheet which needs your experience to work.
open the attached file below to see the workbook.
It has 3 different sheets. "RESULT", "INPUT" and "SWITCH"
On "RESULT" Cell (O11) is where the person would put a Reference.
This reference is then checked on the "INPUT" sheet column A.
From the first cell it appears in Column A, until the last cell it appears in Column A should be the Row Range (Blanks included).
But I want that range (Column A to G) to be copied to "Switch" sheet. Pasted in Cell A1 downwards.
In the "SWITCH" sheet I have already given all the GREEN Cell formated areas a name - INFO.
I want all the data in INFO to be inserted into "RESULT" sheet between row 18 and 19.
I have colour coded most of the areas for you.. And added a button from which the Macro / VBA should run when clicked.
View 1 Replies
View Related
Mar 11, 2008
This seems really basic, but I can't seem to find it...
Using Visual Basic, if I have a named range, lets say like "NAMEDRANGE", how can I refer to cells in that range by their position in the range? For example, if I want to refer to the cell in the 2nd row and 3rd column of the range.
Also is their an easy way to refer to the first(top-left) cell in a named range?
View 3 Replies
View Related
Aug 13, 2008
I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem. I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column.
Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.
View 2 Replies
View Related
Dec 15, 2007
I am attempting to use a named range as my Validation data source but have two questions:
1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range
2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.
View 9 Replies
View Related
Aug 16, 2014
Let'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.
View 5 Replies
View Related
Sep 4, 2007
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select
i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop
View 8 Replies
View Related
Nov 11, 2012
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
View 1 Replies
View Related
Oct 11, 2012
I have the following VBA code which created a unique list of values in a data column. After list is created, I want to assign it to a named range ReferTo parameter. Currently I am getting compiler error. How can I assign this unique list to a named range?
VB:
Dim UList As New Collection
Dim rCell As Range
....
If TypeName(Selection) = "Range" Then
[Code].....
View 4 Replies
View Related
Feb 4, 2014
I am trying to work up a referral tracker for my wife's employer. I have Named Ranges on sheet 2, a running log on sheet 1. I also have a userform that is being populated from the ranges on sheet 2. I have the project mostly functional, except for a feature I would like to add in. I am interested in adding to the named range that is being accessed if the entry is not in list, via the combobox1 on the userform. Also, if possible, I would like the list to re-sort behind the scenes so the added entry is properly located in A-Z format for next time. After searching the web a bit, I found some code that might work with some tinkering, but currently I am having issues with it. Here is the bit I am trying to use.
[Code] ....
I have been getting 424 Object Errors and a few others as I continue to mess with this. I am also attaching the project if someone may see a better way of getting the task accomplished. The overall scope of this is to log all referral sources so monthly and yearly reports could be made.
Attached File : Copy of Referal Tracker.xlsm
View 3 Replies
View Related
Dec 4, 2007
I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.
Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.
View 9 Replies
View Related
May 29, 2006
I have been trying to make a combobox for which the list is a named range. However, this range needs to be transposed. ListFillRange doesn't seem to let me transpose the list first. I've tried transposing the list somewhere else first and then adding it, but it seems to want a range as opposed to a reference to a range. I'm so confused now. This is the basic code that I wish would work.
ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=253, Top:=472, Width:=117, Height:=20). _
Select
Selection.ListFillRange = "=transpose(Stream_Data!StreamList)"
While I'm at it, could someone explain to be how to refer to a combobox. ie. when you create it, you don't name it so how can you refer to it. That's why I have used the selection tool above to add the list.
View 3 Replies
View Related
Oct 17, 2006
I have a worksheet where a number of the cells have been assigned names. I want to copy these cell names & create a list on another part of the worksheet. Does anyone have some code I could use to achieve this?
View 8 Replies
View Related
Dec 5, 2007
I'm creating a sheet that takes three Validation Lists into account. The first two lists are based on named regions. The third list uses the first two lists to narrow a search down so that the user can pick what they want from a smaller selection. I'm using the code from Contextures.com to allow the user to double click the validation list to pop up a Combo Box. This allows me to show more than 8 selections at a time and also gives a little freedom on font and font size.
http://www.contextures.com/xlDataVal11.html
Since the third list is not based on a named region but rather code to compare the two previous lists, when I double click the third validation list a blank combo box pops up. it possible to pop up what ever lists are in the validation boxes.
This is the code that I have in the third validation box which States "The Source Currently evaluates to an error" but works fine for some reason. =OFFSET(CategoryStart, MATCH(B4, CategoryColumn, 0) - 1, IF(A4 = "Description", 2, IF(A4 = "ExtDescription", 3, IF(A4 = "PartNumber",4))), COUNTIF(CategoryColumn,B4), 1)
I tried making a named region that was based on the code, so that the list could change as the user selected different options from the first two lists. This gave me the same problems as I had before.
View 2 Replies
View Related
Oct 31, 2008
I have the following codes in which I am to refer a file and folder to do some further processes.
myfilename = "C:Documents and settingsacsMy DocumentsEntrymyfile1.xls"
folderPath = "C:Documents and settingsacsMy DocumentsEntry"
I want to enter these two paths in a sheet in my excel report file (For example, I am running the report from Report1.xls, in which there is a sheet name "Filepaths". In this,
in B5, I would like to enter the Filepath (B5 named as "FILEPATH")
in B6, I would like to enter the Folderpath (B6 named as "FOLDERPATH")
So that, if I am copying the folder to another area, I no need to change the code every time in the VBA editor. I can do changes in these cells and it will be taken as the path to run the code further.
View 2 Replies
View Related
Feb 25, 2009
I've got a named range which defined using a custom function. Lets call this named range [range1] which is defined by:
View 14 Replies
View Related
Oct 30, 2012
I have been tasked with creating a data validation list. My data is contained on a separate sheet from my data validation list. I am using a named range to use the data for my data validation list. However, I am looking to add a criteria to my data validation list so that it only is populated with values based on another column on my data sheet.
For example, if I have a data sheet called "Employee" and I have two columns. Column A is a list of employee names and I have named a range in column A as "EmployeeName". Column B is used to define the category of employee. This value is a letter, for example, "A", "B", "C", or "D". This column does not have a named range as I don't know if I need one to get this to work.
I would like the data validation on one sheet to use a list of employees of a particular class from another sheet. Straight forward validation is easy using a validation list with a formula =EmployeeName. But how do I only list employees that have a category "A"? For some reason, I cannot get some of the formulas I have found while searching this message board to work.
View 7 Replies
View Related
