Showing list of range names available for formulas??

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

I know this MUST be possible in Excel...but I can't seem to find it.  I have named ranges.  I am entering a formula and want to refer to a range name.  I don't remember exactly what I called it, or it has a long name that I don't want to have to type.  Can't I use some sort of function key to just show a list of the available named ranges, so that I can point to the one I want?  In excel for windows, we used a function key (can't recall which one).   <br>
Thanks,  <br>
hjd
0
hdublisky
4/9/2010 4:20:53 AM
mac.office.excel 1146 articles. 0 followers. Follow

4 Replies
1484 Views

Similar Articles

[PageSpeed] 38

Reveal the Formula bar.

The left-end of the formula bar contains a field showing the range names: it
has drop-down arrows on it you can use to find the range names.

To enter one, simply begin to type its name: when you get to the third or
fourth character, a popup will appear showing the names that match.  Make
sure you have this switched on in your Excel Autocomplete preferences.

If the name is really bad, you can also use Edit>Goto, choose the range
name, and hit Enter.  Excel will drop the name into the formula for you.

Hope this helps


On 9/04/10 2:20 PM, in article 59bb6ba7.-1@webcrossing.JaKIaxP2ac0,
"hdublisky@officeformac.com" <hdublisky@officeformac.com> wrote:

> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
> I know this MUST be possible in Excel...but I can't seem to find it.  I have
> named ranges.  I am entering a formula and want to refer to a range name.  I
> don't remember exactly what I called it, or it has a long name that I don't
> want to have to type.  Can't I use some sort of function key to just show a
> list of the available named ranges, so that I can point to the one I want?  In
> excel for windows, we used a function key (can't recall which one).
> Thanks,  
> hjd

 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name


0
John
4/9/2010 7:10:41 AM
Here's one that even John overlooked :-)

In the Insert> Name menu you'll find a Paste... command which produces a
dialog containing your named ranges (similar but not the same as the Go To
dialog). AFAIK, It doesn't have a default keystroke but you can assign one.

Truth be told, I prefer this feature to either of the other methods. I've
not found the Name Box at the left end of the Formula Bar to be helpful
while editing a formula because it automatically converts to a list of
functions once you type an = into the cell... It doesn't revert back to your
list of range names until you finish the entry.

AutoComplete doesn't seem to work well for me, either, because it doesn't
appear when within parens of a function. IOW, if you have a range named
'range1' the list of functions with named ranges will appear if you begin to
type =r but it will *not* appear if you type =sum(r -- which is when I seem
to need it the most :-)

HTH |:>) 
Bob Jones 
[MVP] Office:Mac



On 4/9/10 3:10 AM, in article C7E51011.8787%john@mcghie.name, "John McGhie"
<john@mcghie.name> wrote:

> Reveal the Formula bar.
> 
> The left-end of the formula bar contains a field showing the range names: it
> has drop-down arrows on it you can use to find the range names.
> 
> To enter one, simply begin to type its name: when you get to the third or
> fourth character, a popup will appear showing the names that match.  Make
> sure you have this switched on in your Excel Autocomplete preferences.
> 
> If the name is really bad, you can also use Edit>Goto, choose the range
> name, and hit Enter.  Excel will drop the name into the formula for you.
> 
> Hope this helps
> 
> 
> On 9/04/10 2:20 PM, in article 59bb6ba7.-1@webcrossing.JaKIaxP2ac0,
> "hdublisky@officeformac.com" <hdublisky@officeformac.com> wrote:
> 
>> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
>> I know this MUST be possible in Excel...but I can't seem to find it.  I have
>> named ranges.  I am entering a formula and want to refer to a range name.  I
>> don't remember exactly what I called it, or it has a long name that I don't
>> want to have to type.  Can't I use some sort of function key to just show a
>> list of the available named ranges, so that I can point to the one I want?
>> In
>> excel for windows, we used a function key (can't recall which one).
>> Thanks,  
>> hjd
> 
>  --
> 
> The email below is my business email -- Please do not email me about forum
> matters unless I ask you to; or unless you intend to pay!
> 
> John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
> McGhie Information Engineering Pty Ltd
> Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name
> 
> 

0
CyberTaz
4/9/2010 1:24:58 PM
Both answered the question, thanks Bob and John! <br><br>Okay, Bob, I am new to the forum (just converted to a Mac).  Can you help me out with some conventions ... what is AFAIK and IOW (I suspect IOW might be In Other Words??) <br><br>I was really stuck on the fact that the box in the formula bar always had a function in it ... and now I know that this happens when you enter an = in the cell.  So thanks all around! <br><br>hjd
0
hdublisky
4/9/2010 2:46:33 PM
AFAIK = "As Far As I Know" & your interpretation of IOW is right on :-)

Regards |:>)
Bob Jones 
[MVP] Office:Mac



On 4/9/10 10:46 AM, in article 59bb6ba7.2@webcrossing.JaKIaxP2ac0,
"hdublisky@officeformac.com" <hdublisky@officeformac.com> wrote:

> Both answered the question, thanks Bob and John!
> 
> Okay, Bob, I am new to the forum (just converted to a Mac).  Can you help me
> out with some conventions ... what is AFAIK and IOW (I suspect IOW might be In
> Other Words??) 
> 
> I was really stuck on the fact that the box in the formula bar always had a
> function in it ... and now I know that this happens when you enter an = in the
> cell.  So thanks all around!
> 
> hjd

0
CyberTaz
4/9/2010 3:53:51 PM
Reply:

Similar Artilces:

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

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...

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...

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 ...

Sorting Attendee Lists in Outlook 2003
Is there any way to sort attendees by either name or attendee status in Outlook 2003? I don't know how Microsoft could be so oblivious to the need for this feature. You can't even click on the column headers to sort, let alone print out a list of people sorted by accept and decline. Microsoft's online help system recommends Alt + PrntScreen???? How archaic is that? Is there anyone out there that can help me??? Thanks! ...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Importing spam list
Hi, I have a long list of spam email addresses that I want to import into outlook. How do I do this? Do they have to be separated by commas? Thaks, Paul Where is this spam list generated? What version of Outlook? Where do you propose to import them? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Paul Ellis asked: | Hi, | I have a long list of spam email addresses...

Trouble doing a formula for excel
Hi All I have a spreadsheet with the following A1: z:\data/pc32/tsheets\unsorder00039.csv I would like to add 1 too the number to make unsorder00040.csv and so I have try mid,right,left i can't seem to do it Cheers "Jason" <Jason@discussions.microsoft.com> wrote in message news:53AAB904-8595-499F-BF38-8BE00826101C@microsoft.com... > Hi All > > I have a spreadsheet with the following > A1: z:\data/pc32/tsheets\unsorder00039.csv > > I would like to add 1 too the number to make unsorder00040.csv and so > I have try mid,right,left i can't see...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

manufacturing scrap not calculating on pick list and serial #'s no
I have entered my percentage of scrap on a MFG BOM and is seems to be calculating properly in the setup. When I release the components and add them to a pick list the scrap is not calculating. I also choose serial numbers and they do not carry thorugh and print on the pick list. Is there some setting that I have missed? -- Paula ...

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

Messenger emoticons
I have changed laptops and I did grab the old laptops custom emoticons folder (all in dt2 and id2 file endings.) But when i copy everything in the folder and add it to my new laptops custom emoticons folder... they get added (i.e. show up in the folder) but the images/gifs or names dont show up on the actual msn... *what gives*? Do I have to change the dt2 endings to gif or jpeg and go to "create" in msn for each of them to add them in? (I tried with one and it worked) Only problem is i have alot, like 203 dt2 files so changing the ending to .gif and adding each singu...

Excel formula #24
What is the formula that brings back a zero for an empty cell instead of 0 0 #DIV/0! Try =if(iserror(formula),0,formula) ************ Anne Troy www.OfficeArticles.com "Dave" <Dave@discussions.microsoft.com> wrote in message news:8392DE7F-0B65-4DEE-87F4-985133BB1976@microsoft.com... > What is the formula that brings back a zero for an empty cell instead of > 0 0 #DIV/0! > ...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

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 ...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

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 ...

Problems with MFC list controls
I have a large list control with many inventory control items displayed within. When someone single clicks on an item, it displays information about that item in another part of the dialog. I have a three-fold problem, the master list has multiple columns in it, in order to make the selection work properly, only column 0 can be selected, is there a way to make a valid row selection if you select an item using any of the other columns? Second issue is the highlight of the selection. Only column 0 in the list control will highlight, is there a way to make the entire row highlight when an...

IF statement based on True/False that activates a certain list box
I've been asked to create a formula that will react to either "yes" or "no" entered into a cell which will show options in another cell based on a certain list. Example: Cells D3:D5 contain the values Monday, Tuesday, Wednesday (list named BegWeek) Cells E3:E5 contain the values Thursday, Friday, Saturday (list named EndWeek) Cell A3 accepts either "yes" or "no" entry only from a list If A3 = "Yes" then B3 will = drop down list BegWeek If A3 = "No" then B3 will = drop down list End Week I don't even know if thi...

A student figuring a formula for wages
Hello, I am beginner student trying to figure out a formula that calculates how to pay people for the number of hours they work, and at the same time figure out any overtime they may have. The wage is in cell B4, and the # of hours is in cell C4. Overtime is figured at 1.5 of the wage in B4. I must put the end result in F4. The instructor rushed through his presentation, and said to use the "if function". The assignment is due on tuesday and any help with this is greatly appreciated. -Thanks in advance ------------------------------------------------ ~~ Message posted fro...

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...

I Need an answer for this Formula
I am using excell 2007 & this formula works {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} When i upload this workbook to a 2003 version this formula does not work I get {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this Then in the cell with this formula has a NAME error WHY & HOW could i fix The IFERROR function can only be used in Excel 2007. Try this array formula** : =LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))...