Cell Value as Named Range Reference

Little bit of a quirky question...

Trying to use a cell value as a reference in a formula, where that
cell value is the name of a named range.

So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2.

I want to get the correlation vale for A1:A3 and B1:B3

So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the
formula: =correl(D1,D2). But I get an error. Have also tried using
Indirect to no avail.

Any help would be hugely appreciated. Thank you.
7/28/2008 3:34:24 PM
excel 39879 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 7


http://www.wimgielis.be = Excel/VBA, soccer and music

"stephen.h.dow@gmail.com" wrote:

> Little bit of a quirky question...
> Trying to use a cell value as a reference in a formula, where that
> cell value is the name of a named range.
> So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2.
> I want to get the correlation vale for A1:A3 and B1:B3
> So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the
> formula: =correl(D1,D2). But I get an error. Have also tried using
> Indirect to no avail.
> Any help would be hugely appreciated. Thank you.
Wigi (97)
7/28/2008 5:58:18 PM

Similar Artilces:

how copy worksheets into workbook where worksheet makes reference to there?
Thanks for any help. I have a workbook that copies some worksheets from another workbook into this workbook (macro code at end of this message). My code is such that it copies the worksheet to right after the old version, deletes the old version, then renames the new version the same name as the old version. I.E.-copies in "Forecase", deletes "MF", then renames "Forecast" to "MF" I also have some worksheets in this workbook that reference to the worksheets being copied (updated) in. So there is a worksheet "SL" that has references to &quo...

count number of cells
What is the formula to count the number of cells that start with a particular character? What I really want to do is count how the number of cells in a column that start with a through e. I have tried several things but can't quite come up with the magic formula. thanks for your help. =COUNTIF(A1:A20,"A*")+COUNTIF(A1:A20,"B*")+COUNTIF(A1:A20,"C*")+COUNTIF(A1:A20,"D*")+COUNTIF(A1:A20,"E*") -- David Biddulph "tagout" <tagout@discussions.microsoft.com> wrote in message news:2B624B5C-E73A-4481-A3E7-C9207F72DAB...

Displaying cell contents
I have a spreadsheet that has a formula in it in cells C1:J50. The cells are displaying 0 because there is nothing to calculate. How can I keep my formula for calculating when actual numbers are entered but the display will show an empty cell, not 0. Thanks, Blue Angel In your formulas you do some variation of this: =if(a1="","",yourformula) Regards, Fred "BlueAngel" <BlueAngel@discussions.microsoft.com> wrote in message news:87342869-89DD-4829-9EA0-0B077964B5DF@microsoft.com... >I have a spreadsheet that has a formula in it in ...

Checking for currency value between range
I have two txtboxes (txtWeeklyAllowanceLow, txtWeeklyAllowanceHigh) on a search form that I would like to use to find all weekly allowances on a table that fall within the range specified. Also if only a low amount is entered, then I would like all allowances greater than the amount to display, or if only a high amount then all allowances less than what is given. Any help with how I should code this would be great. thanks! Ok. Update. I seem to have the filter working for the high as well as the between, but now the low seems to not work. Here is the relevant code so far.. 'If W...

Wacky Default Values
I have a picklist in both my Contact and Account records with a default value set. If I create a new Contact or Account, this default value works properly. However, when I convert a Lead to either a Contact or Account, this value appears in the record when it is pulled up, but it is displaying a blank in all my views. Has anyone experienced this situation and/or have any suggestions? ...

Copy Partial Cell Contents in Excel ? #2
I will definitely work through those responses and give them a try! Los this thread for a little while, but FOUND it again! Thank you very muc for your help Sirs! Best, Ma -- MacDubhga ----------------------------------------------------------------------- MacDubhgal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1424 View this thread: http://www.excelforum.com/showthread.php?threadid=25899 ...

Replacing Contents of 1 Cell to Another.
My Spreadsheet looks like below. A B C 207 MDIM3030 3 TRUE 208 MDIM3030 FALSE 209 MDIM3034 1 TRUE 210 MDIM3034 FALSE 211 MDIM3038 6 FALSE 211 MDIM3039 4 FALSE I have sorted by Column "A" and done an EXACT on them. Now I need to know how to, for example on cell, B:207 (3) and move i to the blank cell below and so on through the Column. There ar hundreds of lines on the column, so I am looking for a...

How to get drive letter from USB device name
Hi all , Is it possible to get a drive letter from Physical device name (USB) ? Thanks in advance Define "physical device name". There are several interpretations of this. You might also post this question in the kernel newsgroup. joe On Mon, 05 Nov 2007 11:44:59 -0000, jklioe <ranu2006@gmail.com> wrote: >Hi all , > > > >Is it possible to get a drive letter from Physical device name (USB) ? > > >Thanks in advance Joseph M. Newcomer [MVP] email: newcomer@flounder.com Web: http://www.flounder.com MVP Tips: http://www.flounder.com/mvp_tips.ht...

Color cells that match on two sheet
Hi Everyone Using XL2003 I'm new to programming and this is my first try beside a couple of Userform. I've got a Vacation Planner on one sheet for 17 People Second sheet is a global view showing workdays for the year for all 17 people. Each cell is numbered to match with Julian date in Calctable sheet Third sheet is my Calculation table. Taking Start Date End date and listing them, Then converting those dates in to Julian dates without the year. I would like to colour the cells on the sheet "Globalview" that match the holiday This is a sample of my code "...

splitting contents of a cell #2
Hi all, I have a list of addresses split over five or so columns. One of the columns has the format "# street name". Is it possible to write a formula that lifts the street name out of that cell but not the number? so from a cell with "25 Hight Street" I would want just "High Street". hope that makes sense, thanks, luc if cell A1 has 25 HIGH STREET, the following formula in B1: =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) will extract HIGH STREET. -- icestationzbra ------------------------------------------------------------------------ icestationzb...

Edit name on W2
Is there a way to change the name on a W2? Other than through SQL? I have a client who used commas in the name and accuwage rejected them. I know I can do it with SQL, but I'd like it if she could fix it herself-and she can't do the SQL thing. Thanks for any help. Tracey D ...

Display Negative Time Values on Y-Axis
I'm plotting timed events against a set schedule (actual lap times vs predicted times) and am calculating the difference and plotting the results. In Excel 2003, I would use the 1904 Date System (Tools, Options, Calculation tab) to display negative time values and then force the chart to link to the source data to determine the format. It would display positive and negative time values on the y-axis In Excel 2007, changing to 1904 date system will show negative time values in the spreadsheet AND will plot negative points on a chart, but WILL NOT DISPLAY NEGATIVE TIME VALUES ON THE Y-AXI...

Merge Text From Two Cells
I would like to merge texts from two different cells into the third cell. Example:- Cell A1 - Tom Cell A2 - Jones Cell A3 - Tom Jones I am using this formula in cell A3 :- =A1&""&B1 But I get TomJones (combined into one word) instead of Tom Jones (two words). Can someone help me to modify the code/formula so that I get Tom Jones (two words) Warmest regards, Salza Hi Salza! Use: =A1&" "&B1 Note the space between the double quotation marks -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Functio...

named cell referenced in header
I would like to have a cell (company_name) on the first worksheet be referenced within all the headers in the workbook. How do I do this? Using code, you would paste this in a workbook module: Code: -------------------- Sub setHeader() Dim mySht As Variant Dim i As Integer For Each mySht In Worksheets mySht.PageSetup.LeftHeader = _ Format(Worksheets("SetUp").Range("A1").Value) Next End Sub -------------------- When run what this will do is paste whatever value is on a sheet named "Setup" in cell A1 to the left header of all the ...

how do i arrange column A (last name) in alphabetical order? t.
my phone book is in excel. how can i alphabetize by last name (column A)? thanks Click on any one last name. Then, Look for the Sort Ascending Button on the Standard Toolbar. It looks like a Blue A above a Red Z. Click that button once. --or-- Click on any cell in the phone list (such as a last name). Go to Data | Sort. Make sure it is selecting the Last Name Column in the Sort by position. Click Okay. tj "hershy" wrote: > my phone book is in excel. how can i alphabetize by last name (column A)? > > thanks I don't trust excel to guess the range to be sorted. I...

Create Checkbox and link to relative Cell
Hi, I have a script that I am using that creates a checkbox in a range but am having problems trying to create this for the relative cell and linking this to a relative row cell, i.e. if the current cell is R16 create a checkbox and link the value to S16. Sub CellCheckbox() Range("R15").Offset(1, 0).Select For i = 1 To 50 'add the checkbox ActiveSheet.CheckBoxes.Add(646.5, 203.25, 24, 17.25).Select ActiveSheet.Shapes("Check Box 1212").Select Selection.Characters.Text = "" With S...

Colouring any cell in worksheet with negative value
Hi all, I have written the below code for making the cells from column to A to BB fill with red colour, if they have a negative value in them. I can't seem to make it work. Any help will be appreciated! Thanks very much! Shivam ****************************** Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const ColumnsToCheck As String = "A:BB" If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then If Target.Value < 0 Then Target.Value.Select Selection.Interior.ColorIndex = 3 End If End If End Sub...

formula to gather non-empty cell info
Hi, I have 20 rows of cells which some have text info and some empty. I need a formula to use to add-up these text info separeted with commas excluding the blank cells. Say A1 contains "this", E1 contains "is", and I1 contains "it!". All the remaining cells till AA1 are blank (but can contain info). I need to use a formula in cell AA1 that will gather non empty cells info in row1 and display "this, is, it". The same story applies for row2, row3, ...etc. What should be the formula in AA1:AA20 Thanks J_J "J_J" <jj@msn.com> wrote in news:...

Advance Filter can be based on cell Color in Microsoft Excel
In MicroSoft Excel, Advance Filter (Data->Filter->Advance Filter) feature can be enhanced. New feature that we can introduce is to do Advance Filter based on Font/Highlighted Color. e.g. If in excel I have 10 Rows and 3 rows font color is RED and 7 rows font color is GREEN then using Advance Filter option we can apply filter based on Font Color or Highlighted Color. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you ...

Pivot table calculated field
I am using a Pivot table to get the total amount of orders to customer from a database. I have built the pivot table with a calculated field since the total amount for each order is missing in the database. The calculated field = no of products * price per product For each specific order Excel calculates the row correct. For the total of each customer and for the pivot table Grand total i seems as Excel is calculating like this: (sum of all prices) * (sum of all numbers of products) = Total pe customer (& Grand total) Is there a way to change the way Excel calculates the totals? If ...

Replace cell info help
I am trying to make a sheet with replace values on it, for example if put a value of 18 into A1 it changes the value in b2 it is not a mat function so i do not know where to start. it is a just a easy numbe replacement, what i really need to do is have a a value of 3 to 18 ma and then that would change the value of another slot. Example; if a1=3 then b1=35 if a1=4 then b1=45 if a1=18 then b1=225 thats what i need to do but have no idea how to do it, any help woul be great. Simo -- Message posted from http://www.ExcelForum.com Hi enter the following formula in B1 =IF(A1=3,35,IF(A1=4,45,I...

Updating column values only if value is not null
Hi, I have 2 tables. I have a query that updates the values of some columns from table A with values from some columns of table B based on a given condition. Something like this: Update T1 Set T1.C1 = T2.C1, T1.C2 = T2.C2, T1.C3 = T2.C3 FROM A T1 INNER JOIN B T2 ON <my condition> That's simple to do, but I was wondering how I can only update the columns where the value of the column of table B is NOT NULL. The reason is because I don't want to overwrite not null values in table A with a NULL value. In that case I want to leave as it is. Thanks Upda...

getting cell color to allow alternating blocks of like value == like color cells
I have seen this asked a few places and some answers, but nothing that works. I want to simply alternate the color of a col so blocks of identical values have the same color. Thus A black A black B blue C black C black A blue B black C blue C blue C blue If you could get the cell color in a formula you could do it painfully as in a formula for black and a formula for blue applied to all but the first row. So the formulas would be along the lines of if x!=above cell and above cell is blue or x==above and above cell is black -> format black if x!=above cell and above cell is black or ...

locking data values from links
-------------------------------------------------------------------------------- I am working on a spreadsheet with links. A value is placed in the master sheet and is then linked back into the sub sheet. Everytime a value is placed in master sheet, the sub sheet is updated. However, Im trying to put a new column in the sub sheet, and make the master sheet update this new column. However I want the old column to have the last updated data before the new column was put in. How do i do this? and does anyone have ne VB codes to achieve this? -- ashcrusher ------------------------------------...

Re: Separating addresses into multiple cells
I have been trying to separate addresses from 1 cell to multiple cells. I have 500 to do, is there a quick way to go about it. Example: 1234 S. West Street Indianapolis IN 46224 I need it to have the street name in 1 cell, City in another, State in another, and Zip in another. Thanks for your help. -- Rebecca ------------------------------------------------------------------------ Rebecca's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30751 View this thread: http://www.excelforum.com/showthread.php?threadid=504191 On Mon, 23 Jan 2006 15:22:23 -0600, Reb...