• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I define a name for the same range in several worksheets?

K

krislopmar

New Member
Hello! I've been subscribed to your newsletter for a few months now and I think is awesome! thank you so much for your wisdom :) It has really helped me impress some bosses around here...


I need to define the same name for the same range (A2:AF12) in 5 diferent sheets. How can I do that? Im using Excel 2003. Thanks!
 
Hi ,


Can you say specifically why you "need" to define the same name for different ranges ? They are different because they refer to different locations. What is the real objective behind this "requirement" ?


Probably that requirement can be met without having to do this.


Narayan
 
Hi Narayank991,


I have a document 12 diferent tables in 12 different sheets, each for 1 month. I also have a extra sheet with 12 graphs that get the info from this sheets. In each sheet the data is pasted in range A2:AF12, the same range for each month, and then rearrange (with subtotals, sums, etc) below in each sheet. I would like to name this range (A2:AF12) with the same name in every sheet so if it has to change in the future I only have to modify the range the name refers to and so then it's modified in all 12 sheets. I dont know if Im explaining myself. I use Excel in Spanish and sometimes is difficult to explain certain things in English. I hope you can help.
 
Hi ,


No problem in your English ; I think I have understood the reason why. But I am still not very clear about why the ranges have to be named. If you are using the range A2:AF12 in various formulae , surely if the range itself changes , due to addition / deletion of rows or columns , then the formulae will anyway automatically change.


Unless you want to ensure that through all the modifications , the range address remains A2:AF12 , without any change. In such a case , you might have to use the INDIRECT function in all your formulae.


Is it possible to upload a sample worksheet , and post the access link here , and give an idea of how you expect the range will change in future , from say A2:AF12 to B7:AG23 , for example ?


Narayan
 
I think I have exposed this the wrong way from the beginning. I know there are lots of ways around so I dont need to name the range: insert rows before copying so the range changes automatically, replace the old range for the new one in the whole sheet... I just wondered if there was a way of naming a certain range in different sheets with the same name, just A2:AF12 instead of May!A2:AF12 so they work in every month's sheet. Do you know if it can be done? Ive read something about it but when I try in my workbook it doesnt work... I would put it here, but its in spanish and since I dont really understand how it works I cant translate it into english...
 
Hi, krislopmar!

I use Excel in spanish, why don't you send me the link you've found and I give a look?

I don't know if it's part of the netiquette of this website to write only in english, I haven't read too much but I didn't find anything, but I think it should be. Even if it were allowed to write in other languages, for consideration to the whole community I'll suggest to write in english in the post as much as you can, and if you wish upload a file in spanish (and better if in english too) where you can explain in native language the details of your problem.

Regards!

PS: for uploading, read this.

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi SirJB7


This is what I found:


Crear un nombre para una referencia 3D


En el grupo Nombres definidos de la ficha Fórmulas, haga clic en Definir nombre.


En el cuadro de diálogo Nombre nuevo, en el cuadro Nombre, escriba el nombre que desea utilizar para la referencia. Los nombres pueden tener una longitud máxima de 255 caracteres.

En el cuadro Se refiere a, seleccione el signo igual (=) y la referencia; a continuación, presione RETROCESO.

Haga clic en la ficha de la primera hoja de cálculo a la que va a hacerse referencia.

Mantenga presionada la tecla MAYÚS y haga clic en la ficha de la última hoja de cálculo a la que va a hacerse referencia.

Seleccione la celda o el rango de celdas al que va a hacerse referencia.


But when I try to apply this into my workbook, it doesnt work... any ideas? maybe works in 2007 but not 2003?
 
Hi, krislopmar!

Download this file:

http://www.2shared.com/file/tRRW_DKF/How_can_I_define_a_name_for_th.html

It has two versions of the same workbook, one in .xlsx (2007 & 2010) and another in .xls (97 thru 2003).

Try what's described and let me know how it goes.

Regards and Feliz Navidad!
 
Hi there!


Thank you for the post, really helpful. Only where you say that workbook is there by default and than you can change it into worksheet so you can apply the same name to the same range in different sheets (what I would need), I dont see that option in Excel 2003... There are only 2 fields in that Define name window: the Name and the range it refers to. Any ideas?

thanks!
 
Oh! I managed to get a peak at Windows 2007 and indeed, this option is only available there, not in 2003. So much for nothing..., 2003 is what I have at work :-(

Anyway, thanks all for your help! Always learning.
 
Hi, krislopmar!

Maybe you can ask the IT guys at your work to install 2007/2010. I uploaded the two versions, because I didn't remember if the workbook/worksheet scope was available in 2003.

You know how to contact me for anything about Excel in spanish.

Regards!

PS: Hasta cuando quieras!
 
OK 2007 has your answer, but you're stuck in 2003... workarounds?


The ease of using named ranges is so that you don't have to use helper columns. OPTION 1 - set up a helper table in your final sheet. it can be hidden behind the charts


OPTION 2 - use the INDIRECT(...) function. here you can define a range, e.g. = INDIRECT("sheet1!A2:AF12") or to the next level, =INDIRECT(A1&A2) where A1="sheet1!" & A2 = "A2:AF12" or = "BF7:BF15" [Note these arrays are different sizes 1xn and nx1]
 
Hi ,


I do not know whether this question has been answered , but I came across this today , and found it amazing !


Created by Bob Umlas and David Hager


To define a global range name in Excel, go to Insert, Name, Define and,

as an example, in the Names in Workbook box type "cellA1" and in the Refers

To box type this formula: =OFFSET(!$A$1,,,,). Now, type =cellA1 in a cell

on any worksheet in the workbook and it will return the value in A1 for

that worksheet.


Note : the range can be named anything e.g. Global_Range_Name


The formula can contain a valid range address e.g. =OFFSET(!$H$137:$Q$234,,,,)


The link for this is :


http://j-walk.com/ss/excel/eee/eee001.txt


Narayan
 
You must log in or register to reply here.
Back
Top