Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges | Excel Worksheet Functions | |||
Insert & link a graphic file in Excel 2003 -- updatable pictures! | Excel Discussion (Misc queries) | |||
INDIRECT and Named Ranges referencing closed workbook | Excel Worksheet Functions | |||
Named Cell Ranges | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |