Quicker way of chaning Cell ref in Define Name

B

BAKWAN

Hi,

I have excel files which using define names. There are a
lot of define names, each referencing to colum A to W with
different row number.

Columns C1 - W1 represent the date (working date only
exclude Saturday and Sunday) for the month of June
For example,

A1 = Category 1 Name
B1 = Category 2 Name
C1 = Jun 01, 2004
D1 = Jun 02, 2004
E1 = Jun 03, 2004
.... W1 = Jun 30,2004

I may call A1-W1= Bonus
then A2-W2= Rebates
etc.

For July we have 1 extra days, being 31 days, so I have to
add a column (Column X) for July 31, 2004.

In order for the Name reference to be correct, I have to
change the "Reference in the Refers to" to include column
X. Because I have about 20-30 Names to change, I would
like to know if there is a quick way of doing it instead
of doing it one by one?

Any help would be much appreciated.

BAKWAN
 
A

Aladin Akyurek

Bonus:

=x!$A$1:INDEX(x!$1:$1,MATCH(9.99999999999999E+307,x!$1:$1))

Substitute the correct sheet name for x in the above formula.

The lookup value in MATCH(), that is, 9.99999999999999E+307, is used with
numerical references. If the reference is text, change the lookup value to
REPT("z",255).
 
Top