using the same name reference in multiple worksheets

[ericx]

Hi all,

Dumb question, but I can't seem to get it to work...

2 spreadsheets ...

In spreadsheet 1 (Year 2001), in formulas within that spreadsheet
refer to, say, "Total" as ='Year 2001'!$10:$10.

In spreadsheet 2 (Year 2002), in formulas I refer to "Total" ='Yea
2002'!$3:$3.

But I can't seem to use the "Insert/Name/Define" function properly s
that 2 ranges in separate worksheets refer to the same 'name' (if the
were the same line, ie. both line 10, I would be fine).

Any ideas, arghhh...

 

[Bob Phillips]

Call the name 'Year 2001'!Total, and 'Year 2002'!Total when defining the
names.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ericx]

Hi Bob,

Thanks for responding, much appreciated.

Your solution is definitely an option.
I wanted ...

1. common NAME definitions across spreadsheets (i.e. the same "Total
name definition in different spreadsheets)
AND
2. common formulas across spreadsheets (i.e. A1=Total + Subtotal)

but it looks like I only get the choice between 1 or 2, not both.

You are saying use
A1 (cell in spreadsheet 2001) = 'Year 2001'!Total + 'Yea
2001'!Subtotal
and
A1 (cell in spreadsheet 2002) = 'Year 2002'!Total + 'Yea
2002'!Subtotal

so unique formulas, same NAME definitions...

Correct?

Eri

 

[Bob Phillips]

No, I am saying that if you qualify each name with the sheet name as well as
qualifying the RefersTo value with the sheet name, you would then use

You are saying use
A1 (cell in spreadsheet 2001) = Total + Subtotal
and
A1 (cell in spreadsheet 2002) = Total + Subtotal

the definitions will assume the sheet names.

--

HTH

RP
(remove nothere from the email address if mailing direct)