Can Named ranges be used in file link formulae? in Excel Worksheet Functions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Philip J Smith 4/10/2006 4:51 AM PST
  Question
  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?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Bob Phillips 4/10/2006 5:05 AM PST
  Answer
  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" <PhilipJSmith@discussions.microsoft.com> wrote in message
news:C3229272-0C4D-47EA-AFAB-1352BA514E11@microsoft.com...
> 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?
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Philip J Smith 4/10/2006 5:20 AM PST
   
  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" <PhilipJSmith@discussions.microsoft.com> wrote in message
> news:C3229272-0C4D-47EA-AFAB-1352BA514E11@microsoft.com...
> > 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?
> >
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Bob Phillips 4/10/2006 5:29 AM PST
   
 

"Philip J Smith" <PhilipJSmith@discussions.microsoft.com> wrote in message
news:F5ABBA06-B8BC-4E82-9B9D-2EB33B5D7347@microsoft.com...
> 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.


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies