using the same name reference in multiple worksheets
 

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 I
refer to, say, "Total" as ='Year 2001'!$10:$10.

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

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

Any ideas, arghhh....


--
ericx
------------------------------------------------------------------------
ericx's Profile: http://www.excelforum.com/member.php...o&userid=16860
View this thread: http://www.excelforum.com/showthread...hreadid=320456

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" wrote in message
...

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 I
refer to, say, "Total" as ='Year 2001'!$10:$10.

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

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

Any ideas, arghhh....


--
ericx
------------------------------------------------------------------------
ericx's Profile:
http://www.excelforum.com/member.php...o&userid=16860
View this thread: http://www.excelforum.com/showthread...hreadid=320456

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 + 'Year
2001'!Subtotal
and
A1 (cell in spreadsheet 2002) = 'Year 2002'!Total + 'Year
2002'!Subtotal

so unique formulas, same NAME definitions...

Correct?

Eric


--
ericx
------------------------------------------------------------------------
ericx's Profile: http://www.excelforum.com/member.php...o&userid=16860
View this thread: http://www.excelforum.com/showthread...hreadid=320456

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)


"ericx" wrote in message
...

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 + 'Year
2001'!Subtotal
and
A1 (cell in spreadsheet 2002) = 'Year 2002'!Total + 'Year
2002'!Subtotal

so unique formulas, same NAME definitions...

Correct?

Eric


--
ericx
------------------------------------------------------------------------
ericx's Profile:
http://www.excelforum.com/member.php...o&userid=16860
View this thread: http://www.excelforum.com/showthread...hreadid=320456