Define Name - same name for multiple cells on multiple sheets

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
 
R

RagDyer

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)
 
T

Thomas

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top