Changing named range reference depending on a cell's content

Where to start?!

I've got the following formula pulling data in from a secon
spreadsheet within the same workbook:

=IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0)

We have 8 different locations ("MICH" being one of them) that we nee
to be able to access.  I can write a nested IF formula that looks a
cell I7 (that contains a list of all 8 locations) and, depending o
I7's content, brings back the desired values.

I was hoping someone in the forum could help me write a simpler formul
that would not have 7 IF statements embedded in it.

Any help will be greatly appreciated.

Thanks,
Crist

--
cparask
-----------------------------------------------------------------------
cparaske's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=379
View this thread: http://www.excelforum.com/showthread.php?threadid=39130

0
7/29/2005 3:41:17 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
457 Views

Similar Articles

[PageSpeed] 46

If anyone has a similar question, the best way to approach it is with
the following formula:
=INDEX(INDIRECT($I$7),MATCH($D7,LOB,0),MATCH($F$5, Month,0))

INDIRECT looks at I7 and uses it as the named range (since this is the
place it occupies in the INDEX statement). There will be an error if I7
does not equal a named range, so you can limit input by having a list
(through data validation)


-- 
cparaske
------------------------------------------------------------------------
cparaske's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3793
View this thread: http://www.excelforum.com/showthread.php?threadid=391309

0
7/29/2005 6:06:09 PM
Reply:

Similar Artilces:

how do i change tick marks of excel graph from vb.net
I'm trying to change using VB.NET the tick marks from their default outside position and place them inside the graph. How can this be done? ...

Hyperlink via indirect cell reference
Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

Exchange 2000 DST Changes
How is one supposed to address the DST changes for Exchange 2000 if they do not have extended support to get the patch? Will Microsoft be providing a workaround like they did for Windows 2000? I'm referencing the following article: http://support.microsoft.com/kb/930879 It states : What to do before you run the Exchange tool Install DST updates Before you run the Exchange tool, make sure that client and server computers are updated correctly with the operating system and application DST updates. These updates must be installed in the following order: 1. Install the Windows DST upda...

Excel, how do I change the column headings from letters to number
I have a spreadsheet that has numbered columns as opposed to the standard letters. How can I change this back to letters? Go to the Tools menu, choose Options, then the General tab. There, uncheck the R1C1 reference style setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "lazybee" <lazybee@discussions.microsoft.com> wrote in message news:030962A3-A111-4780-93C0-1D28003F1F20@microsoft.com... >I have a spreadsheet that has numbered columns as opposed to the >standard > letters. How can I change this ...

Set a range from a variable location
I am using a loop function that will eventually select and activate a specific cell known as "TheCurrentCell." Once I reach that location I want to use that coordinate as the upper left area of a new range. Supposing that the end cell of my range is D1000, the Range selection would be something like Range(TheCurrentCell:D1000).select, however, I don't know the correct syntax to make this happen. Please help. Thank you. Use something like Range(Selection.Address & ":D1000").Select. "Troubled User" wrote: > I am using a loop function that will...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

Naming charts on own sheet
Hi. I have a series of charts (which are all contained on their own sheets). I need to name each of the charts (as they will be used by someone else in a macro). I have tried clicking on them and also pressing shift before clicking on them, and I am not able to change the name in the name combo box. Can anyone advise me of how I can change the names. Thanks for your help. Hi, If you have chart sheets you can change the name by simply changing the sheet tab name. What you described is the method used on chartobjects, which are usually on a worksheet. Cheers Andy -- Andy Pope, Mi...

Hiding sheet tab names
I created an automated workbook where I need to keep the sheet tab name hidden from the user. I went into Tools-Options-View and unchecke Sheet Tabs. Then I protected the workbook and the sheet yet the use can still go into Tools-Options-View and re-check the Sheet Tabs t view them again. How do I lock the user out of viewing the sheet tabs? :confused -- Message posted from http://www.ExcelForum.com You could use the "very hidden" property that prevents users from viewing hidden worksheets without using VBA: Dim ws2 As Worksheet For Each ws2 In ThisWorkbook.Worksheets If w...

Rename Cell
How can I rename column A to read "bills" instead of the letter A? You can't. The closest you will get is to hide column headings, via Excel Option, and then create your own. -- Regards Dave Hawley www.ozgrid.com "shoe" <shoe@discussions.microsoft.com> wrote in message news:DBA970DF-D928-41EE-9565-4639E7D49BCE@microsoft.com... > How can I rename column A to read "bills" instead of the letter A? you cant change the headers or row labels but you can define you data as a list (or table) and the headings can then be used to refer...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Changing Item #'s
Is it possible to change the item #'s after the item has already been entered? TMM: No there isn't in the "base" product, but MBS does have a tool that you can use called Item Modifier - this will allow you to change an Item number from one value to another. You can email this address below and they can answer any questions you have about the tool and can tell you what the cost of it is. mbsprofessionalservices@microsoft.com Hope that helps you out, JG "TMM" wrote: > Is it possible to change the item #'s after the item has already been entered? ...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

How to set dependencies on project
Hello all, I have created a separate build on an existing project and copied settings from the existing project (Build->Configurations->Add). When I build the new project, it does not pickup any of the dependencies from sub-projects (ie I get link errors for all of them). Can someone tell me where to set these for the new project? The Project->Dependencies dialog list all dependencies for the project and are checked. Thanks mike wrote: > Hello all, > I have created a separate build on an existing project and copied settings > from the existing project (Build->Con...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...

re post range check
hi i have a worksheet with a lot of data on it column A contains the date column B contains an area column C contains the fault column D contain a quantity A B C D 1/4/04 stores damaged 10 7/4/04 bins rust 60 -- -- --- -- -- --- - -- -- 31-12-04 floor ...

Average of logic cells
I used a logic test to determine some levels from raw scores. For EG >120 =5, 119-110 = 4, etc. I now want to dtermine an average score of several of the the results from the logic tests but it doesnt seem to work. (AVG does not recognise cells with logic tests) Can anyone help, please? -- ckdkvk ------------------------------------------------------------------------ ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245 View this thread: http://www.excelforum.com/showthread.php?threadid=489704 hi, ckdkvk ! > I used a logic test to determine so...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Select All Names
We have a form with a combo box and that combo box executes a query to find all of the records matching the user's name. The query derives that value from a form based parameter called vName. This is how the process works. 1. The user starts by selecting their name via a drop down list combo box on a dialog form. 2. That selection becomes the value of vName. 3. The user clicks OK and then moves to the data entry form. 4. On the data entry form, the user then clicks on another combo box which runs the aforementined query, which in turn uses the value of the variable vName to find ...

Cell References..
I have a 12 month rolling report with a seperate worksheet within th workbook which refers to the column containing current month's Numbers When I "Cut" Column C (which contains the oldest Month) and "insert column C between N & O it shifts my cells left and all I need to do i input all of the current Month Data into Column N. The formulas al remain intact and everything is peachy. Until I goto the Workshee that refers to the Current Month on the 12 month rolling report. My problem is that when I shift the columns on the "Report" workshee it chages the cell...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Changing named range reference depending on a cell's content
Where to start?! I've got the following formula pulling data in from a secon spreadsheet within the same workbook: =IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0) We have 8 different locations ("MICH" being one of them) that we nee to be able to access. I can write a nested IF formula that looks a cell I7 (that contains a list of all 8 locations) and, depending o I7's content, brings back the desired values. I was hoping someone in the forum could help me write a simpler formul that would not have 7 IF statements embedded in it. Any help w...

Range Names with Charts
I would like to use rangenames to specify the data sources for a chart, but Excel does not seem to like this. I have tried entering the rangename in the following formats: rangename =rangename ={rangename] ={"rangename"} .... and various other formats. Surely there is a way to do this ....? Art Hi, try either depending on whether the named range is local to the sheet. =sheetname!rangename =bookname.xls!rangename cheers Andy ArthurJ wrote: > I would like to use rangenames to specify the data sources for a chart, but > Excel does not seem to like this. I have tried en...

Compare each value in a range to each value in another range
I am looking for a macro that will return a comparison of each cell in a range to each cell in another range. example. the first range would have vales of 1,2,3 and the second range would have values of 5,6,7 Thus the macro should return 9 possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 & 7, Can anybody help me with this?? Just hazarding some thoughts here .. Perhaps using formulas would suffice ? Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then Compare 1st range against 2nd range I...