Same Range and Name, different sheets? :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Same Range and Name, different sheets?
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

starl
TheOfficeExperts


Joined: 17 Aug 2002
Posts: 276

Flag: Blank

Status: Offline

 Reply with quote  

Same Range and Name, different sheets?

I have several sheets will data set up identically.
Is there a way of setting up named ranges only once but just have it apply to the active sheet?
more info: each sheet has a chart based on the data on its sheet. The chart is dynamic, so I'm setting it up with named ranges. Right now, I'm having to set up the named ranges for each sheet.. tedious! I want to setup one name range that refers to the same area of different sheets.
Possible?

Post Fri Sep 19, 2003 9:15 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Same Range and Name, different sheets?

Hi starl:

Let us say your range name is ChartRange and you want it to be the same cells B2:B4 in each worksheet where the cell values could be different -- in that case, I would set up my range name with INSERT|Name|Define >> ChartRange and then in the Refersto TextBox, I would enter

!$B$2:$B$4

You may set up the cell references to suit your needs.
_________________
Regards!
Yogi Anand

Post Fri Sep 19, 2003 9:26 pm 
 View user's profile Send private message

starl
TheOfficeExperts


Joined: 17 Aug 2002
Posts: 276

Flag: Blank

Status: Offline

 Reply with quote  

Re: Same Range and Name, different sheets?

won't let me.. says it's an invalid external reference... when I try and use it in the chart.

Post Fri Sep 19, 2003 9:32 pm 
 View user's profile Send private message

Juan Pablo González
MrExcel MVP


Joined: 09 Feb 2002
Posts: 8580
Location: Marion, IL
Flag: Colombia

Status: Offline

 Reply with quote  

Re: Same Range and Name, different sheets?

Hey Tracy..

Just tested this on Excel XP.

Name: Test

Refers to:

=!$A$1

and it worked ok...
_________________
Regards,

Juan Pablo González
MrExcel.com Consulting

Read the Articles List and check out our Recommended links and Add-Ins

Post Fri Sep 19, 2003 9:37 pm 
 View user's profile Send private message Visit poster's website

starl
TheOfficeExperts


Joined: 17 Aug 2002
Posts: 276

Flag: Blank

Status: Offline

 Reply with quote  

Re: Same Range and Name, different sheets?

excel 2k. and I can create the name just fine.. it's getting the chart to accept it is the problem.

Post Fri Sep 19, 2003 9:39 pm 
 View user's profile Send private message

bjwillingham
Board Master


Joined: 17 Jun 2003
Posts: 110
Location: Houston
Flag: Usa

Status: Offline

 Reply with quote  

Re: Same Range and Name, different sheets?

Define your name "MyChartData" and in the refers to box put the following:

=OFFSET(!$A$1,1,2,2,2)

change the 2nd and 3rd parameters to reference the upper left corner of the data

Change the 4th and 5th parameters to reflect the number of rows and columns.

It works in Excel 2000
_________________
Brad

Post Fri Sep 19, 2003 9:48 pm 
 View user's profile Send private message

Juan Pablo González
MrExcel MVP


Joined: 09 Feb 2002
Posts: 8580
Location: Marion, IL
Flag: Colombia

Status: Offline

 Reply with quote  

Re: Same Range and Name, different sheets?

Actually, the problem isn't creating the named range, the problem is having the chart accept it !

BTW, an INDEX/MATCH approach should be better than an OFFSET/COUNTA or OFFSET/COUNT approach.
_________________
Regards,

Juan Pablo González
MrExcel.com Consulting

Read the Articles List and check out our Recommended links and Add-Ins

Post Fri Sep 19, 2003 9:57 pm 
 View user's profile Send private message Visit poster's website
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.