Reference Non-Contiguous Named Range In Function
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
ADVERTISEMENT
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
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
Jan 20, 2010
I'm trying to do a vlookup on a non contiguous named range.
I have a non contiguous named Range called "temp".
View 6 Replies
View Related
Jul 14, 2012
I have as the two logical operators for an IF function the following COUNTA calculation which checks how many blanks are in the range GrdMtx7 and subtracts this from the value in E5 and adds the suffix "Module(s)".
Code:
(E5-COUNTA(GrdMtx7)-1)&" Module(s)",(E5-COUNTA(GrdMtx7)&" Module(s)")))
GrdMtx7 is a non-contiguous range consisting of the following cell references on a second sheet:
Code:
='Mod Schedule'!$E$7,'Mod Schedule'!$H$7,'Mod Schedule'!$K$7,'Mod Schedule'!$N$7,'Mod Schedule'!$Q$7,'Mod Schedule'!$T$7,'Mod Schedule'!$W$7,'Mod Schedule'!$Z$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AF$7,'Mod Schedule'!$AI$7,'Mod Schedule'!$AL$7,'Mod Schedule'!$AO$7,'Mod Schedule'!$AR$7,'Mod Schedule'!$AU$7,'Mod Schedule'!$AX$7
I need for the formula of the IF function to copy down so that it refers to F5, G5, H5, I5...etc.
I also need for the non-contiguous named range GrdMtx7 to refer to the same non-contiguous range shifted down one row intact for the COUNTA function to operate on the next row down, such that COUNTA is checking for blanks in the range:
Code:
='Mod Schedule'!$E$8,'Mod Schedule'!$H$8,'Mod Schedule'!$K$8,'Mod Schedule'!$N$8,'Mod Schedule'!$Q$8,'Mod Schedule'!$T$8,'Mod Schedule'!$W$8,'Mod Schedule'!$Z$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AF$8,'Mod Schedule'!$AI$8,'Mod Schedule'!$AL$8,'Mod Schedule'!$AO$8,'Mod Schedule'!$AR$8,'Mod Schedule'!$AU$8,'Mod Schedule'!$AX$8
I can't get got to work at all using OFFSET and would rather not create dozens or hundreds of named ranges incrementing one at a time - is there a way to achieve this, preferably with a formula, but vba if absolutely necessary.
My best effort doesn't work:
Code:
(E6-COUNTA(OFFSET(GrdMtx7,1,))-1)&" Module(s)",(E6-COUNTA(OFFSET(GrdMtx7,1,)))&" Module(s)")
View 2 Replies
View Related
Dec 1, 2012
I'm trying to write a 'ReImport' macro to copy data from one model into another. I'll call these models 'Master' and 'Split'. 'Master' contains about 50 departments' worth of financial data, and the 'Split' files contain a Division's (between 1 and 20 departments') worth of data. Structurally, the 'Split' files are just copies of the Master with the unneeded departments hidden. They are sent to various Divisions for completion, then the ReImport process takes data from the Split files and copies it back into the Master. Every Split File contains the full 50 departments from the Master, and it is only the 'Visible Row' distinction plus a 'ReImport key' that should determine what gets ReImported.
Each 'Split' range has a range name that controls what Departments are hidden or visible, but the range name is not contiguous. In other words, Division A might show Departments 1, 4-8 and 23. (Reordering the Depts is not an option.)
In a perfect world, I want to evaluate ONLY rows contained within the Division range name for the ReImport key. But the non-contiguous aspect of the named range is throwing me off. So in the above example, JUST Departments 1, 4-8 and 23.
My second-best solution would be to search through Departments 1-23, though that would be much slower...
View 5 Replies
View Related
Jun 29, 2012
I have several tables on one sheet, each of which has a Grand Total value at the bottom right of the summed data range
I have manually selected (using ctrl + left mouse click) each Grand Total cell and defined all these cells as a Named Range called Grand_Totals
I thought it would be possible to reference each of the cells in the Grand_Totals range individually.
The =Index function works fine if the data is contiguous but I can't figure out how to get anything other than the first element of the Grand_Totals range
So for example
Lets say the cells of the Grand_Totals non-contiguous named range are F10,N10,F30,N30,F50,N50
How would I reference the third element/cell (F30) in this range. I should point out that I actually need to reference all the cells in the Grand_Totals range and the range has a lot more than 6 cells
View 5 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 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
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
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
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
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
View Related
Oct 12, 2007
i have a bit of a problem with the sum() array function.
im trying to get the sum of an array based on 2 conditions.
my excel sheet looks as follows; i have 10 columns, which define also the names of these columns.
AccountLink - AccountLevel - Master_Sub_Account - Debit - Credit - Account_Type - Period - Project - ProjectCode - ProjectName
I want to sum for an AccountType the debit, where project is 3.
the formula i use is:
=SUM(IF(Account_Type=L24,IF(Project=M24,Debit)))
(with {} brackets of course, and CSE'd).
BUT, it is giving me a #NUM error.
why? (when i replace the column names with the actual ranges, i.e. F1:F211 for AccountType, it works)... could this be due to the fact that the named ranges are composed of the full columns or something? And, is there a way to solve it that it will work with these named ranges...?
View 9 Replies
View Related
Jun 22, 2006
In the formula below the AND function cannot recognise the named range "KolomFFundering". I need the named range because when I insert a row the range must also change. Is there any way to rewrite this formula without losing it's function? The range of "KolomFFundering" is F13:F23.
=IF(AND(J12<>"";KolomFFundering<>"");"verwijder getal uit kolom 'hoeveelheid'";IF(KolomFFundering<>"";SUM(KolomFFundering);IF(P12<>"";P12/L$227;"")))
View 10 Replies
View Related
Oct 7, 2008
is it possible to change the named range used in a formula based on the value selected in a dropdown. I have 4 named ranges NR1, NR2, NR3 and NR4. I would like to have a VLOOKUP that uses the named range that has been selected in a drop down. So if I change the drop down option I would get a different result as it will be looking at a different range.
It is not possible to put all of the values together as one named range as the intention is that I would select the range on a hidden and protected sheet so people would only see what they need to but I only have to manage one master sheet instead of having to create a new sheet for each range.
View 2 Replies
View Related
May 1, 2008
I'm struggling to find a way to reference a non-contiguous range of cells in the IRR function. I've got the initial cost in one cell (b17), and the annual cash flows in a single row (c12:w12), separate from the cell containing cost.
View 9 Replies
View Related
Apr 2, 2009
I have several named ranges with 4 column each. The named ranges have all the same names but with the tow last digits ranging from 00 to 50 by increments of 5 (ie 00, 05, 10, 15 etc.). The 4 columns for each of the named ranges have the following headings; 10, 100, 50 ,70. I have two cells with drop down lists with the list of the suffixes of the named ranges in cell e5 (ie 00, 05, 10, etc) and in g5 a drop down list with the 4 headings for the columns (10, 100, 50, 70)
I want to refer to these 2 values so that in column B, I can get the values of the column chosen for the given named range. For example, the column "100" for the named range THRESHOLD_10. In cell C1, I entered the following formula ="THRESHOLD_"& E5 which gives me the named range name. I entered the following function in the first cell of column B = index(C1,2,G5) but get a #REF! answer. If I write the following formula =index(THRESHOLD_10,2,G5) I get the right value.
how to get about it, and indirect(C1), but still gets a #REF! message. When I evaluate the formula, the named range appears with quotes "" which might be the reason that it cannot work. I am nearly there...but for the syntax and cannot get that working!
View 3 Replies
View Related
Dec 4, 2012
Passing Named Range into User Defined Function
MrExcel.com | Excel Resources | Excel Seminars | Excel Products mcm91201
Depending on time of day and computer I am sitting in front of I am using:
WinXP Pro SP2 with Excel 2003
Win7 Pro SP2 Excel 2007
Win7 Pro SP2 Excel 2010 on PC
Win7 Pro SP2 Excel 2010 on Mac Mini running Boot Camp
OSX Excel for Mac 2011
I have only tried this on Win7 Pro SP2 Excel 2007 but need it to work on all.
I enter the values 0, 1, 2 ... 89, 90 in cells A1 to A91
I select A1:A91 and name the range 'angle'
I create a user defined function:
Public function sindeg(value As Double) as Double
sindeg = sin(worksheetfunction.radians(value))
end
I want 'value' for the function in a cell to be replaced by the corresponding value in the same row (or column) in the named range 'angle'. For example (using commas as column separators). This works for Excel functions like sin, cos, radians, etc.
********** Worksheet Contents **********
A1 = 00, B1 = sin(radians(0)), C1 = sin(radians(A1)), D1 = sin(radians(angle)), E1 = sindeg(0), F1 = sindeg(A1), G1 = sindeg(angle)
A2 = 01, B2 = sin(radians(1)), C2 = sin(radians(A2)), D2 = sin(radians(angle)), E2 = sindeg(1), F2 = sindeg(A2), G2 = sindeg(angle)
A3 = 02, B3 = sin(radians(2)), C3 = sin(radians(A3)), D3 = sin(radians(angle)), E3 = sindeg(2), F3 = sindeg(A3), G3 = sindeg(angle)
......
A91 = 90, B91 = sin(radians(90)), C91 = sin(radians(A91)), D91 = sin(radians(angle)), E91 = sindeg(90), F91 = sindeg(A91), G91 = sindeg(angle)
Column A = input. Columns B, C, D, E and F all calculate the same value by row. Column G fails with a #VALUE. In row 1 the value of angle[1] = 0 therefore column D = C = B = sin(0) = 0. In row 2 angle[2] = 1 therefore B = C = D = 0.017452
How can I get the user defined function sindeg(value) in column G to accept the named range variable 'angle' like the Excel function radians(value) accepted it in column D?
This functionality should work horizontally as well as vertically. For example enter 'angle' A1 to CM1 then have sindeg(angle) filled from A2 to CM2. It should also work in the case where the named range 'angle' is a single cell.
I am sure that this is a simple variable type definition problem in my user defined function: should the input variable be defined as type Range? Or something more exotic?
The brute force approach is to have the function determine the input value by passing in the named range, working out dimensions, calculating offset between the cell the function is in and top (left) of named range, then counting down (right) to pick the correct value. However I cannot see adding all that code to EVERY function. Occam's Razor says there has to be an easier way since Excel built in functions seem to do it readily.
View 8 Replies
View Related
Apr 20, 2007
I have seen one of your posts in which you make a graph update based on dynamic named ranges. Here is my problem, I already defined the name of my ranges as:
ChurnDiario =OFFSET(Clientes!$E$25,0,0,1, COUNTA(Clientes!$E$25:$AH$25))
Ejex =OFFSET(Clientes!$E$8,0,0,1,COUNTA(Clientes!$E$8:$AH$8))
The issue is that when I tried to enter the values and category (x) axis with:
Values: =offset(ChurnDiario,0,0)
Category (X) axis labels: = offset(Ejex,0,0)
On both, excel replies “That function is not valid”. I’ve tried, but still can’t make it work.
View 2 Replies
View Related
Dec 5, 2013
I am trying to use the AVERAGEIF function to give calculate averages based on dynamic data. The formula I want to work is:
[Code].....
but this returns the "#DIV/0!" error.
However,
[Code] ....
returns the correct value.
I want to be able to calculate averages of only the cells that are greater than the value of cell D6. Is there a way to do this?
View 3 Replies
View Related
Feb 28, 2008
Is there a way to start match from a relative position?
Say a match I have in column "A" returns 344. Is it possible to start a new match for column "B" from that spot, instead of having it return a match starting at the top?
View 5 Replies
View Related
Dec 31, 2007
I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 =
=LINEST(K8:K50,M8:N50,TRUE,TRUE) .
Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE)
Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0!
I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.
View 9 Replies
View Related
Aug 2, 2006
I am working with several columns of data, and each column's range is stored in a range array. My program needs to use a specififc combination of the columns as the second argument sent to the regress function. In certain cases, the columns I am referencing are not contiguous, so the function won't work. Here's an example:
Suppose we're working with 3 independent variables (a simple case) whose data is stored in three contiguous columns named x1, x2, and x3. The program I've written will call the regress function 6 times: once using x1, once using x2, once using x3, once using the union of x1 and x2, once using the union of x1 and x3, once using the union of x2 and x3, and once using the union of x1, x2, and x3. Suppose the range for x1 is d5:d18, the range for x2 is e5:e18, and the range for x3 is f5:f18. Also, each union of ranges is stored as one element in an array called Combos, which is sent to the regress function.
The regress function returns an error when prompted to calculate regression stats for columns x1 and x3 because they aren't contiguous. I need to know if there is some sort of way around this. write a procedure that will detect if the ranges being input to the regress function are contiguous, and if they are not, copy them to a new worksheet so they will be contiguous and then send the new range references to the regress function for calculation purposes? Is there an easier way around this issue? If I can't get this to work, my entire program is wasted.
View 3 Replies
View Related
