#N/A error when copying some formulas but not others

hvalcourt

New Member
Joined
Dec 22, 2010
Messages
18
Hi ,
I have this workbook where I have formulas of the type :
Code:
=SUMIF( DATA!keycurrent; G12& " " &$H12 & " " &$J12& " " &$F12; DATA!rev )
in my column. When i try to copy this sheet, i get #N/A errors where the formula should be.
This is strange since i have another workbook where the following formula gets copied without any problem :

Code:
=iF(  ISBLANK($H6); ""; IF(ISNA( VLOOKUP($H6; Macro_modele_renouv.xls!tablesic; 2; FALSE)); ""; VLOOKUP(  $H6; Macro_modele_renouv.xls!tablesic; 2; FALSE)))

Basically, in both formulas i'm referencing names. Could it be that in the 2nd formula i'm refering to a workbook level named range and in the 1st a worksheet level named range ?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

hvalcourt

New Member
Joined
Dec 22, 2010
Messages
18
in this code,
Code:
=SUMIF( DATA!keycurrent; G12& " " &$H12 & " " &$J12& " " &$F12; DATA!rev )

If i replace DATA!keycurrent by the actual range DATA!$K$2:$K$999 the formula gets copied correctly. But why can't i freely use named ranges ?
It'd be very inconvenient for me to give up on the named ranges.
It seems to me that I've never hard to care about this before.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top