Define Name - same name for multiple cells on multiple sheets

  • Thread starter Thread starter Thomas
  • Start date Start date
T

Thomas

Hi community:

I've been searching for a while to find an answer to my problem but
couldn't, so here it is:

A B
1 1001 Food Sales Lunch
2 1002 Food Sales Dinner
3 1003 Food Sales Catering
...

I have the same struture of data on another 40 sheets in the same workbook.
I want to name each of the cells A1, A2, A3, ... 'ACCOUNT'.
the same for Sheet2, Sheet3, .... sort of a relative rather than absolute
reference.

So, when using ACCOUNT in a formula in e.g. Sheet1, cell C1, ACCOUNT refers
to cell A1, when using the same name in line 3, it refers to A3 and so on.

thanks a lot in advance!
Thomas
 
This will create a *relative*named*formula*, which will reference Column A,
on the row that it's used in.

It should work in all existing Sheets, PLUS, any additional sheets added
thereafter.

From the Menu Bar of *any* sheet in the WB:
<Insert> <Name> <Define>,

In the "Names In Workbook" box, type:
account

CHANGE whatever's in the "Refers To" box to:
=INDIRECT("A"&ROW())

Then <OK>

And you should be done!

Enter a value in Column A.

In any other column, on the same row, enter:
=account
And you'll see that value, or text data.

And you can use it in a formula:
=10*account
=Sum(T5:T12,U13:U20,account,B10)
 
This will create a *relative*named*formula*, which will reference Column A,
on the row that it's used in.

It should work in all existing Sheets, PLUS, any additional sheets added
thereafter.

From the Menu Bar of *any* sheet in the WB:
<Insert> <Name> <Define>,

In the "Names In Workbook" box, type:
account

CHANGE whatever's in the "Refers To" box to:
=INDIRECT("A"&ROW())

Then <OK>

And you should be done!

Enter a value in Column A.

In any other column, on the same row, enter:
=account
And you'll see that value, or text data.

And you can use it in a formula:
=10*account
=Sum(T5:T12,U13:U20,account,B10)
 
that worked like a charm! thx a bunch!!

RagDyer said:
This will create a *relative*named*formula*, which will reference Column A,
on the row that it's used in.

It should work in all existing Sheets, PLUS, any additional sheets added
thereafter.

From the Menu Bar of *any* sheet in the WB:
<Insert> <Name> <Define>,

In the "Names In Workbook" box, type:
account

CHANGE whatever's in the "Refers To" box to:
=INDIRECT("A"&ROW())

Then <OK>

And you should be done!

Enter a value in Column A.

In any other column, on the same row, enter:
=account
And you'll see that value, or text data.

And you can use it in a formula:
=10*account
=Sum(T5:T12,U13:U20,account,B10)
Click to expand...
 
that worked like a charm! thx a bunch!!

RagDyer said:
This will create a *relative*named*formula*, which will reference Column A,
on the row that it's used in.

It should work in all existing Sheets, PLUS, any additional sheets added
thereafter.

From the Menu Bar of *any* sheet in the WB:
<Insert> <Name> <Define>,

In the "Names In Workbook" box, type:
account

CHANGE whatever's in the "Refers To" box to:
=INDIRECT("A"&ROW())

Then <OK>

And you should be done!

Enter a value in Column A.

In any other column, on the same row, enter:
=account
And you'll see that value, or text data.

And you can use it in a formula:
=10*account
=Sum(T5:T12,U13:U20,account,B10)
Click to expand...
 
Back
Top