Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Referencing a named range on another sheet

I have forgotten how to reference a named range on another sheet in my
workbook. I have a YTD Summary sheet that I want to pull data into from my
monthly sheets in the same workbook. Monthly sheets are named Jan, Feb, Mar,
etc. Summary sheet in named Summary. Each of the monthly sheets has a cell
named Bills. The data on each monthly sheet is the same as the month before
it until changes for the germane month are entered. So, on the summary sheet
I don't want to see the data for that month until I'm in that month. Formula
for February should be something like the formula below, but I can't remember
how to do it!! Please help.
IF(MONTH(DATE)<=2,'Filename.xls'!sheetname[named range],0)
--
Jan B
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Referencing a named range on another sheet

Hi Jan,

To reference a named range on another sheet in your workbook, you can use the following formula:

Formula:
=SheetName!NamedRange 
In your case, if you want to reference the Bills named range on the Jan sheet, the formula would be:

Formula:
=Jan!Bills 
To make this formula dynamic so that it only shows data for the current month, you can use the MONTH and TODAY functions. Here's an example formula for the Bills named range on the Jan sheet that will only show data if the current month is January or February:
  1. Formula:
    =IF(MONTH(TODAY())<=2,Jan!Bills,0

You can then copy this formula to the other monthly sheets and change the sheet name as needed.

On your YTD Summary sheet, you can use a similar formula to sum up the Bills data from each monthly sheet. Here's an example formula for the Bills YTD total that will sum up the Bills data from January to the current month:
  1. Formula:
    =SUM(Jan!Bills:INDIRECT(TEXT(TODAY(),"mmm")&"!Bills")) 

This formula uses the INDIRECT function to dynamically reference the named range on the current month's sheet based on the current date. The TEXT function is used to convert the current date to the month name, which is then concatenated with the !Bills named range to create the full reference.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Referencing a named range on another sheet

The syntax is:

'[filename.xls]sheetname'!named_range

so you would need something like:

=IF(MONTH(date_cell)<=2,'[filename.xls]Feb'!Bills,0)

Hope this helps.

Pete

On Jan 24, 12:56*am, Jan B wrote:
I have forgotten how to reference a named range on another sheet in my
workbook. I have a YTD Summary sheet that I want to pull data into from my
monthly sheets in the same workbook. Monthly sheets are named Jan, Feb, Mar,
etc. Summary sheet in named Summary. Each of the monthly sheets has a cell
named Bills. The data on each monthly sheet is the same as the month before
it until changes for the germane month are entered. So, on the summary sheet
I don't want to see the data for that month until I'm in that month. Formula
for February should be something like the formula below, but I can't remember
how to do it!! Please help.
IF(MONTH(DATE)<=2,'Filename.xls'!sheetname[named range],0)
--
Jan B


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,510
Default Referencing a named range on another sheet

Hi Jan,

A little extra info. When you want a range or cell reference in a formula,
when you get to the point in the formula where you want to insert it, simply
change to the workbook and worksheet and select the cell or range and Excel
looks after the syntax for you. If the cell or range is named then Excel uses
the name in lieu of the address in the formula.

--
Regards,

OssieMac


"Pete_UK" wrote:

The syntax is:

'[filename.xls]sheetname'!named_range

so you would need something like:

=IF(MONTH(date_cell)<=2,'[filename.xls]Feb'!Bills,0)

Hope this helps.

Pete

On Jan 24, 12:56 am, Jan B wrote:
I have forgotten how to reference a named range on another sheet in my
workbook. I have a YTD Summary sheet that I want to pull data into from my
monthly sheets in the same workbook. Monthly sheets are named Jan, Feb, Mar,
etc. Summary sheet in named Summary. Each of the monthly sheets has a cell
named Bills. The data on each monthly sheet is the same as the month before
it until changes for the germane month are entered. So, on the summary sheet
I don't want to see the data for that month until I'm in that month. Formula
for February should be something like the formula below, but I can't remember
how to do it!! Please help.
IF(MONTH(DATE)<=2,'Filename.xls'!sheetname[named range],0)
--
Jan B



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Referencing a named range on another sheet

Hi,

Actually the question is a little more complicated! If you really have a
cell on each sheet named Bills and you try to write the formula:

=SUM(Sheet2:Book2!Bill)

You will get a #Name? error message. You can scope the range names to the
sheet level but you can't use 3-D references with those. You would need to
write the formula as

=Sheet2!Bill+Sheet3!Bill and so on.

To reference a single range name on a separate sheet you don't need to
reference the File Name unless the formulas are in a different workbook. If
the range names are unique for the workbook you don't need to include the
sheet reference in your formula. In most cases it is a good idea not to use
sheet level names for exactly that reason. Instead you might name the ranges
JanBills, FebBills and so on.

This formula
IF(MONTH(DATE)<=2,'Filename.xls'!sheetname[named range],0)

=IF(MONTH(TODAY())=2,Sheet2A1,0)
or
=IF(MONTH(TODAY())=2,FebBills,0)
or
=IF(MONTH(TODAY())=2,Feb!Bills,0)

Now if DATE is a range name for a cell containing a date you can replace the
TODAY() with DATE. However, keep in mind that Excel has a spreadsheet
function called DATE. Although that won't result in an error it might lead
to confusion by other people viewing your spreadsheet.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Jan B" wrote:

I have forgotten how to reference a named range on another sheet in my
workbook. I have a YTD Summary sheet that I want to pull data into from my
monthly sheets in the same workbook. Monthly sheets are named Jan, Feb, Mar,
etc. Summary sheet in named Summary. Each of the monthly sheets has a cell
named Bills. The data on each monthly sheet is the same as the month before
it until changes for the germane month are entered. So, on the summary sheet
I don't want to see the data for that month until I'm in that month. Formula
for February should be something like the formula below, but I can't remember
how to do it!! Please help.
IF(MONTH(DATE)<=2,'Filename.xls'!sheetname[named range],0)
--
Jan B



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Referencing a named range on another sheet

Thanks, Shane. You hit the nail on the head - I was getting the #Name? error.
I will take your advice and create unique names.
Jan B


"Shane Devenshire" wrote:

Hi,

Actually the question is a little more complicated! If you really have a
cell on each sheet named Bills and you try to write the formula:

=SUM(Sheet2:Book2!Bill)

You will get a #Name? error message. You can scope the range names to the
sheet level but you can't use 3-D references with those. You would need to
write the formula as

=Sheet2!Bill+Sheet3!Bill and so on.

To reference a single range name on a separate sheet you don't need to
reference the File Name unless the formulas are in a different workbook. If
the range names are unique for the workbook you don't need to include the
sheet reference in your formula. In most cases it is a good idea not to use
sheet level names for exactly that reason. Instead you might name the ranges
JanBills, FebBills and so on.

This formula
IF(MONTH(DATE)<=2,'Filename.xls'!sheetname[named range],0)

=IF(MONTH(TODAY())=2,Sheet2A1,0)
or
=IF(MONTH(TODAY())=2,FebBills,0)
or
=IF(MONTH(TODAY())=2,Feb!Bills,0)

Now if DATE is a range name for a cell containing a date you can replace the
TODAY() with DATE. However, keep in mind that Excel has a spreadsheet
function called DATE. Although that won't result in an error it might lead
to confusion by other people viewing your spreadsheet.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Jan B" wrote:

I have forgotten how to reference a named range on another sheet in my
workbook. I have a YTD Summary sheet that I want to pull data into from my
monthly sheets in the same workbook. Monthly sheets are named Jan, Feb, Mar,
etc. Summary sheet in named Summary. Each of the monthly sheets has a cell
named Bills. The data on each monthly sheet is the same as the month before
it until changes for the germane month are entered. So, on the summary sheet
I don't want to see the data for that month until I'm in that month. Formula
for February should be something like the formula below, but I can't remember
how to do it!! Please help.
IF(MONTH(DATE)<=2,'Filename.xls'!sheetname[named range],0)
--
Jan B

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Named Range: Same Names, Multiple Workbooks with Same Sheet Name BEEJAY Excel Discussion (Misc queries) 4 November 7th 08 04:19 PM
referencing a 3D Named Range Robert H Excel Worksheet Functions 3 January 14th 08 06:46 PM
Referencing a named range based upon Range name entry in cell Barb Reinhardt Excel Worksheet Functions 14 June 20th 07 07:19 PM
Refer to Named Range on another sheet for IF function David Excel Worksheet Functions 3 August 26th 06 04:12 AM
referencing a sheet named in a cell then using data from that sheet gbeard Excel Worksheet Functions 4 April 15th 05 08:42 AM


All times are GMT +1. The time now is 03:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"