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]])"
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 RelatedI 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).
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
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.
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.
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 RelatedIn 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.
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.
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.
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.
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?
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.
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.
why I'm getting an error with this code. I have a data set that becomes larger each time I run the report, so by selecting all the cells and naming the range, I don't have to hard-code the cells involved. Then, I want to be able to create a table from that named range. The error comes on the last line of code.
VB:
ActiveSheet.Range("A1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Name = "TestRange"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("TestRange"), , xlYes).Name = "Table2"
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.
My Userform with combobox entries for product checks against the rowsource and if not on the list, is added to the list. the list is a named range in a Table that is used in a data model.
No matter which way I try I am regularly getting a corruption in the table that is home to the named range which is the rowsource... after one or two entries from the userform.
The rowsource is not bound to the worksheet, it is populated at userform_initialize procedure. (There are 4 combobox drawing from same rouwsource hence the loop through to check each)
debug points to the line at which conbobox value is being entered into the rowsource, though not always - it sometimes just crashes the whole file and everything needs restarting, upon which the table is fine and entry is evident.
I have tried resize, addrow to bottom of table variations and all result in the same core problem of a table corruption that crashes the file.
I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.
Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.
In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.
Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...
'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem
[Code]....
I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.
My code thus far:
Code:
Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
[Code]....
emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").
I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.
When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:
For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False
When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.
Here's the corresponding code for that:
For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True
Next
I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).
I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.
(Using Excel 2010, Windows 7.)
I am trying to create a pivot table on a new sheet names as 4x4
I am getting following error:
Error : 1004
Reference Not Valid
I am not sure where is the problem. I have generated this code by using macro recorder and just changed the source and destination of the pivot generation code.
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.
I 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'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.
if I can use a named criteria as well as a named range. In essence what I am looking to do is count certain cells that meet the criteria in a certain named named range,
View 9 Replies View RelatedMerge two columns into one list in excel
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
Here's the formula from the example:
Code:
=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))
I've played around with it, but could not come with any that worked.
See attached for an example. I'm trying to use the SumIF funtion with a named reference as the criteria. Here is my formula. =SUMIF(B10:F10,"<MinReq",B10:F10) It keeps returning 0 even though there is valid data.
View 3 Replies View RelatedI have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies View RelatedI have the following formula which i'm using to indirectly reference a worksheet. The worksheet i'm referencing includes named ranges that i'd like to call on but i can't get the syntax right.
Code:
=SUMPRODUCT(--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")>=D5),--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")
Change "refer to" in Named Range. I have a named range myrange referring to A1:A5. I have a condition for B1, if B1= annual then myrange should refer to C1:C5 and not A1:A5..
View 2 Replies View RelatedI'm trying to use a countif formula with a named reference but the formula is not calculated correctly. My example would be: countif(A1:A5,<max) with max being the named reference. I also tried various options using quotes around the named reference (ex. "<max") but that doesn't work either
View 3 Replies View Related