Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Philip J Smith
 
Posts: n/a
Default Can Named ranges be used in file link formulae?

Hi.

I have an expression in my main Management accounts workbook.

=GESTEP(CurrentPeriodNumber,AE$451)*
ROUND(SUMIF(FigaroTBMaster.xls!FigeroYTDNominal,"= "&$A34,
FigaroTBMaster.xls!FigeroYTD04),0)

Were "CurrentPeriodNumber" =4,
AE$451 =4,
$A34 ='99999
"FigaroTBMaster.xls" is the workbook containing the source data,
"FigeroYTDNominal" and "FigeroYTD04" are named ranges within the source file.

The expression works when the source file is open, but not when it is closed.

Can named ranges be used in formulae which link workbooks?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Can Named ranges be used in file link formulae?

I have no idea what GESTEP is, but SUMIF doesn't like closed workbooks, so
try

=GESTEP(CurrentPeriodNumber,AE$451)*
ROUND(SUMPRODUCT(--(FigaroTBMaster.xls!FigeroYTDNominal=$A34),
FigaroTBMaster.xls!FigeroYTD04),0)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Philip J Smith" wrote in message
...
Hi.

I have an expression in my main Management accounts workbook.

=GESTEP(CurrentPeriodNumber,AE$451)*
ROUND(SUMIF(FigaroTBMaster.xls!FigeroYTDNominal,"= "&$A34,
FigaroTBMaster.xls!FigeroYTD04),0)

Were "CurrentPeriodNumber" =4,
AE$451 =4,
$A34 ='99999
"FigaroTBMaster.xls" is the workbook containing the source data,
"FigeroYTDNominal" and "FigeroYTD04" are named ranges within the source

file.

The expression works when the source file is open, but not when it is

closed.

Can named ranges be used in formulae which link workbooks?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Philip J Smith
 
Posts: n/a
Default Can Named ranges be used in file link formulae?

Hi Bob.

Thanks for the alternative syntax, it worked first time!

What does the "--" do?

GESTEP is an engineering function. e.g. GESTEP(A,B)= 1 if A=B I use it
instead of IF(A=B,1,0) to avoid nested if functions when a binary (0,1)
result is required

Thanks once again.
Phil Smith

"Bob Phillips" wrote:

I have no idea what GESTEP is, but SUMIF doesn't like closed workbooks, so
try

=GESTEP(CurrentPeriodNumber,AE$451)*
ROUND(SUMPRODUCT(--(FigaroTBMaster.xls!FigeroYTDNominal=$A34),
FigaroTBMaster.xls!FigeroYTD04),0)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Philip J Smith" wrote in message
...
Hi.

I have an expression in my main Management accounts workbook.

=GESTEP(CurrentPeriodNumber,AE$451)*
ROUND(SUMIF(FigaroTBMaster.xls!FigeroYTDNominal,"= "&$A34,
FigaroTBMaster.xls!FigeroYTD04),0)

Were "CurrentPeriodNumber" =4,
AE$451 =4,
$A34 ='99999
"FigaroTBMaster.xls" is the workbook containing the source data,
"FigeroYTDNominal" and "FigeroYTD04" are named ranges within the source

file.

The expression works when the source file is open, but not when it is

closed.

Can named ranges be used in formulae which link workbooks?




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Can Named ranges be used in file link formulae?



"Philip J Smith" wrote in message
...
Hi Bob.

Thanks for the alternative syntax, it worked first time!

What does the "--" do?


See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.


GESTEP is an engineering function. e.g. GESTEP(A,B)= 1 if A=B I use it
instead of IF(A=B,1,0) to avoid nested if functions when a binary (0,1)
result is required


So it is. I have never seen it before, didn't know it was a built-in. I
assumed it was a UDF <g. I'll remember that one.


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 Ranges Joe Gieder Excel Worksheet Functions 2 February 16th 06 02:31 AM
Insert & link a graphic file in Excel 2003 -- updatable pictures! ODI Excel Discussion (Misc queries) 7 November 14th 05 03:36 PM
INDIRECT and Named Ranges referencing closed workbook gpie Excel Worksheet Functions 9 October 6th 05 11:24 PM
Named Cell Ranges Blackcat Excel Discussion (Misc queries) 7 December 9th 04 02:59 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 04:35 PM.

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"