Worksheet Specific Range Name

How do I define a range name on a worksheet that is specific to that
worksheet? i.e. I want to use the same range name on several
worksheets.

Regards,

0
10/19/2007 2:53:00 PM
excel 39879 articles. 2 followers. Follow

5 Replies
557 Views

Similar Articles

[PageSpeed] 52

On Oct 19, 10:53 am, Alan1 <alan.do...@virgin.net> wrote:
> How do I define a range name on a worksheet that is specific to that
> worksheet? i.e. I want to use the same range name on several
> worksheets.
>
> Regards,

1 Select the range of cells that you want to name.
2 Choose Name from the Insert menu, then choose Define. Excel displays
the Define Name dialog box.
3 In the Names in Workbook box, enter the name of the current
worksheet, an exclamation mark, and the name you want to define, as in
Sheet1!MyRange.
4 Click on Add.

-Andrew

0
givensna (3)
10/19/2007 2:59:13 PM
One way:

In the name box, include the sheet name:

    Sheet1!MyName


In article <1192805580.462883.132540@q3g2000prf.googlegroups.com>,
 Alan1 <alan.dowen@virgin.net> wrote:

> How do I define a range name on a worksheet that is specific to that
> worksheet? i.e. I want to use the same range name on several
> worksheets.
> 
> Regards,
0
jemcgimpsey (6723)
10/19/2007 3:04:03 PM
And if your sheetname requires apostrophes:

'Sheet 1'!yournamehere

Since you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It has a feature that allows you to convert global names to local and local to
global.  (workbook level to worksheet level and vice versa.)

Alan1 wrote:
> 
> How do I define a range name on a worksheet that is specific to that
> worksheet? i.e. I want to use the same range name on several
> worksheets.
> 
> Regards,

-- 

Dave Peterson
0
petersod (12005)
10/19/2007 3:10:58 PM
Thanks guys, it is all clear now.

Alan


0
10/19/2007 3:47:42 PM
On Oct 19, 10:53 am, Alan1 <alan.do...@virgin.net> wrote:
> How do I define a range name on a worksheet that is specific to that
> worksheet? i.e. I want to use the same range name on several
> worksheets.
>
> Regards,

1 Select the range of cells that you want to name.
2 Choose Name from the Insert menu, then choose Define. Excel displays
the Define Name dialog box.
3 In the Names in Workbook box, enter the name of the current
worksheet, an exclamation mark, and the name you want to define, as in
Sheet1!MyRange.
4 Click on Add.

-Andrew

0
givensna (3)
10/19/2007 4:27:18 PM
Reply:

Similar Artilces:

Naming Ranges #4
Hi, When I name a Range on Sheet1, then make a Copy of Sheet1 to say Sheet2, then access the Define Names dialogbox, I can see the named Range listed twice. One is noted as belonging specifically to Sheet2 and the other appears without a sheet reference. When assiging a name to a Range, can I specify that it belongs to a certain sheet? Also, how would I change this code to assign the sheet name aswell? ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:="=Sheet1!R1C1:R10C1" I am trying to assign a the name 'Column_Header_Range' to each sheet in my work...

suggest names (outlook 2002)
in the autocomplete function, is there a way to access the database? I've got old addresses in there that I used once a year ago causing a drop-down list when I've only got one matching entry in my address book... so what I'd like to do is either clear it out completely or at least delete the obsolete entries, I've looked in the registry and it isn't there... any suggestions would be appreciated, thanks! --John Remove individual addresses from the autocompletion cache by highlighting the entry when presented in the suggested names list (use your arrow key to migrate to...

Move worksheet to new book
I have 14 worksheets within a workbook. In the middle are worksheets named Sheet1, Sheet2....Sheet10 Is it possible using a macro to see if there is anything written in cell A1 on Sheet1, and if there is, then move it to a new book, and then check Sheet2 and so on? And if there is no information on the worksheet I would like to delete it. TIA Try this against a copy (since it destroys worksheets): Option Explicit Sub testme01() Dim wks As Worksheet Dim iCtr As Long Dim wkbk As Workbook Set wkbk = ActiveWorkbook For iCtr = 1 To 10 'sheet1 throu...

Hyperlink from HTML to a specific Excel workshhet
We have html files with links in them that open an Excel document and goes to a specific worksheet. Since upgrading from Office 2003 to Office 2007 we now find that the link will only open the workbook to its last saved active worksheet and not the worksheet required. Does anyone know how to get around this? HTML Syntax is <a href="c:\temp\book1.xlsx#Sheet3!A1"> LableName </a> Using 2007 I created a tiny workbook called master.xlsx The only thing in master is a hyperlink to another workbook, specific sheet, specific cell. I saved master as html. ...

Worksheet Filtering by UserName
I have an excel workbook with 60 tabs (worksheets) that contain user production data and I need a way that when someone opens up the workbook they only see the (worksheet) tab with their information only and not the entire workbook. Is there a way to do this in excel? Thank you! Probably the best way to do this is to create a sheet and name it something like 'Passwords' and put a command button in that sheet and point it to this code: Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password to Unhide Sheet", &qu...

Corporate Name Change
How does one change the name of a stock (and symbol) when a company (such as AOL) decides to do so Wade: Select AOL from your portfolio, right click and choose "see details" You can rename it and change the symbol there. Kevin "Wade" <anonymous@discussions.microsoft.com> wrote in message news:023701c395d4$c6129da0$a401280a@phx.gbl... > How does one change the name of a stock (and symbol) when > a company (such as AOL) decides to do so Thanks ... I get an error stating the ticker is already in use. How can I get around this (is there a global ticker li...

get network login name
I have the following code to get the network login name: Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX <> 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = vbNullString End If E...

named ranges in other workbooks
i have a range of cells whose values are validated from a list determined by a name the name is determined by a formula which references a range of cells in a different workbook (i'm on a office network...) the problem is: if the other workbook is open (on my desktop?) the name is o.k. and the validation is fine... but: if the other workbook is not open, the name results in an error i didn't think that this is the way it's suppose to work... AURGHH Mark, Do you use a full reference such as ='C:\Documents and Settings\Owner\My Documents\Book1.xls'!profit Bernar...

How to CreateTournament Worksheet
I'm not an experienced user of excel but want to create a worksheet sheet to work in a tournament format with picture, have two picture next to each other click on one and it moves it to the next round. I have around 20 pictures of teams and which need to gradually reduce to a winner in the final. Can anyone help? Is there a template to get hold of. Thought someone will have already done some thing like this for football teams? -- Jarvis ------------------------------------------------------------------------ Jarvis's Profile: http://www.excelforum.com/member.php?action=getinfo&...

how do I find a list of databases with correpsponding company name
Hello, I need to know how in GP to pull up a list of our companies with their corresponding SQL database names. I've done this a long time ago and remember it being easy but cannot remember where to do this. SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500 "Tim Quiggle" <Tim Quiggle@discussions.microsoft.com> wrote in message news:D0ECF5C5-74F9-494E-8C2E-B37EE12C8D0C@microsoft.com... > Hello, > > I need to know how in GP to pull up a list of our companies with their > corresponding SQL database names. I've done this a long time ago and > rem...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...

Excel Question: How to Delete All Blank Rows from a worksheet?
Hi, I'm brand new, and that is my question. I just spent 2 hour laboriously mousing along deleting row by row, every other row thinkin there's gotta be a better way. (I could've done it of course if the were adjacent, but they were interspersed with my good data). I'm little behind the times: agile with keystrokes but less so with mous [think I was quicker in Lotus DOS! (actually "TWIN")]. But I do play mean piano -- Message posted from http://www.ExcelForum.com Check out: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows "DaveThePianoGuy >...

Copying specific data from Word to Excel
What I need to do is 1) Extract the monetary amount (always after the 2nd asterisk (*) after the letters BPR. 2) Extract the number (always after the 2nd asterisk (*) after the letters TRN. 3) Place the numbers in the same row on an Excel spreadsheet. 4) Repeat to the end of the document. Sample data BPR*I*925.79*C*ACH*CCP*01*xxxxxxxxxxxx*DA*xxxxxxxxxxxxxxx*xxxxxxxxxxxxxxx**01*xxxxxxxxxxxx*DA*xxxxxxxxxxxxxxx*20091126~TRN*1*999999999*xxxxxxxxxxxx The file I need to extract from is always text. Is this better done from a Word macro or an Excel macro. Or can it be done at al...

How do I sort a proctected worksheet
Unprotect it. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Audrey" <Audrey@discussions.microsoft.com> wrote in message news:1E1AC0AE-67F0-46B1-A741-B32D4DF69D43@microsoft.com... > That is a given, but I have columns that I don't want sorted. I have locked those columns in hopes they would not sort and the others would. Any suggestions? Thanks for the help. "Chip Pearson" wrote: > Unprotect it. > > > -- > Cordially, > Chip Pearson > Microsoft MVP - Excel > Pearson...

Attaching a worksheet to an e-mail
Hi! I am working with excel 2003 I would like to attach a worksheet (not the entire workbook) to a e-mail. I would like to do it as an excel worksheet if possible but can make it work as a body of text as well. What would the code loo like for this. The user is accessing my workbook file (read only) through a websit and when they fill-out the form they click a button and the macr e-mails the form worksheet to my e-mail address. This is an attempt a doing "On-Line" warranty claims. Any help or suggestions would be greatly appriciated -- Brian Matlac --------------------------------...

Worksheet Help req PLZ
Hey All, I have a small issue where I have created a worksheet called data and in Cell A3 I have a linked cell to another worksheet and cell "WK48!A2" In Cell A4 in the "data" worksheet I need a link to "WK49!A2". I would like to link all cells in Column A to consecutively "WKxx" worksheet once I add them to the workbook. I thought I could drag them mouse down but al I get is links to consecutive cells in "WK48" work sheet. Please help mag()() Try =INDIRECT("'WK"&ROW(48:48)&"'!A2") -- Reg...

Outlook does not recognize one or more names
When trying to send mail with outlook this is what my response is, "oultlook does not recognize one or more names. I can send mail when using my verizon mail program but not with outlook. Any ideas? have you verified that both you imcomming and outgoing mail servers are set correctly? I believe that verizon has several outgoing servers. You may want to contact their technical support to get the correct configuration for your account. You also did not note if you are using a router or firewall program on your computer. This can also create additional issues with your configurat...

Entering a company name on the tab
Hi I am trying to create a tab that when I open Excel and click File New I will be able to see / General / Spreadsheet Solutions / Ashfield / I am using Excel 2000 - I know I did this before on 97 but can't remember how. I find it useful for customers who would like to store their own templates in their own area. Thanks a million for any help in advance Ann Ann What do you consider to be the "Tab"? A worksheet Tab name or the Caption you see in the Title Bar? For the former, just create a workbook with that sheet name and save as a Template. For the latter, you...

Names of workbooks
One thing that drives me crazy is that I want to work on files at home, but when I bring them from the office to home and work on them, when I get back to the office, the full path names of the sheets I've worked on are saved. For example, if I have a workbook Houston.xls, and I refer to cells in the another file called Scorecard.xls, all references are to the full paths, and I have to spend time deleting all the full pathnames that were valid at home to the full path at work (on a server naturally, so the path names are really long. This is in Excel '97 (ancient I know, but the co...

Re: Suggest Name Now Not Working
"Karl Burrows" <kfb1@spambellsouth.net> wrote in message news:... > My bad, it's the automatic name checking I use, not the suggest name. I get > confused between the two. Name checking is the one you get the red or green > underline squiggle to right-click and complete the email address. > > It's been a long week! > > PS Just got your Outlook Programming book. > > "Sue Mosher [MVP-Outlook]" <suemvp@outlookcode.com> wrote in message > news:OIBsc8XgEHA.3864@TK2MSFTNGP10.phx.gbl... > > Did you check your setting in T...

Generating a list dependent on specific criteria
Hi, I am working on a large register of equipment and have a problem. I need to create a list automatically based on the contents in one cell. I have a equipment register on one sheet (Sheet 1) and wish a list to be automatically generated via the info in a cell on Sheet 2, ive tried to show what I mean below. Sheet 1 data: A B C D 1 Drill Yellow Parent Blue 2 Drill Yellow Child Blue 3 Drill Yellow Child ...

Get Report Name
I have a report selection form that allows the user to select multiple reports to preview. They have the ability to check a box that automatically sends each report to a separate pdf with a predefined name and path, using the CreateReportToPDF code by Stephen Lebans. What I want to do now is allow the user to preview multiple reports but selectively send reports to pdf. I have a custom reports tool bar, I’ve added a button but can’t figure out how to determine the name of the report the user is viewing. Thanks for your help, Jim Not sure but, I imagine you could loop through the R...

Named Ranges
I have a file with over 100 named ranges. I would like to expand the range of the name on all of these names. Is there a way to use the replace command or is manually the only way to do this? [This followup was posted to microsoft.public.excel.charting with an email copy to keith. Please use the newsgroup for further discussion.] If the names refer to hardcoded ranges, i.e., Name1 =Sheet1!$A$2:$A$5 then you will have to either fix them by hand or write a VBA program that will make the change(s) you need. Alternatively, you use named formulas that adjust as needed. Suppose you have ...

range color from date and database
newbie done some homework on excel i would like to have a range change color based on date and time and a name from a database--or any suggestion for example if it is thursday and the time is betweeen 1 and 3 pm i want the range to be say light red if out of tinme range i want it to be light green also the cell above the range as long as time constraints are met to have a name from a datasource any suggestions or ideas apprciated i need to have data from either a database or from say another sheet where user can enter a form the data from the datasource would fill range values a...

Worksheet size #3
I need to create a year planner in Excel but the worksheet simply isn't wide enough - does anybody know how to increase the (width) size? Thanks Avril Hi Avril Excel is limited to 256 columns. This can not be changed. Cheers JulieD "Avril" <Avril@discussions.microsoft.com> wrote in message news:56C0900E-D321-4914-BE89-B75A028C2D94@microsoft.com... >I need to create a year planner in Excel but the worksheet simply isn't >wide > enough - does anybody know how to increase the (width) size? > > Thanks > > Avril Hi so you want more than 256...