Formula works in spreadsheet but not in Name Manager

[sparky2205]

Hi folks,
this formula ...

="Sheet1!$B$2:" & "Sheet1!" & ADDRESS(2,COLUMNS(DataTable),1)

... works in a spreadsheet but doesn't work when I use it in Name Manager as the range for a Named Range.

If more detail is required I can provide it but I'm assuming this is either not possible or I have made a mistake in my syntax.

I'm trying to create a dynamic Named Range based on rows that will change as columns are added to a table.

 

[arthurbr]

Wouldn't it be easier to make your range an Excel Table?

 

[sparky2205]

It is a table. I use this a lot to dynamically set named ranges when extra rows are added.
However, it doesn't appear to behave the same when adding columns.

 

[arthurbr]

I use this a lot to dynamically set named ranges when extra rows are added.

Does "this" mean an Excel table? Named ranges are not necessary when using Tables as they also are named?

 

[rakesh seebaruth]

try the below formula

=INDEX(Sheet1!$B:$B,2):INDEX(Sheet1!$B:$B,ROWS(Sheet1!$B:$B),COLUMNS(DataTable))

 

[sparky2205]

Maybe a little more detail.

Joe Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week20	Week 21
2	Person1	Man Utd	Spurs	Man Utd	Liverpool		West Ham
3	Person2	Man Utd	West Ham
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2:K3	Custom	=COUNTIF(INDIRECT($A2),B2)=1

Row 3 has a Custom Data Validation rule: =COUNTIF(INDIRECT($A3),B3)=1
If you try to enter a duplicate value in row 3 the Data Validation rule will kick in and prevent it.
But the spreadsheet will expand both horizontally (columns) and vertically (rows).
What I am trying to do is have the named ranges (rows) automatically expand to incorporate newly added columns. I came up with:

="Sheet1!$B$2:" & "Sheet1!" & ADDRESS(2,COLUMNS(DataTable),1)

which worked as a formula in the spreadsheet; result for Row 3: Sheet1!$B$2:Sheet1!$M$2. If I add a column this expands to: Sheet1!$B$2:Sheet1!$N$2.
Then the Custom Data Validation rule would pick the Named Range from A3 (see Custom Data Validation rule above).

In this way it would be a simple matter of creating a Named Range for each persons name in column A. When adding a new row to the table the Data Validation will automatically be created in the new row. Similarly for the addition of a new column.

The problem I am having is defining the Named Range. I want it to be dynamic. i.e. the Named Range "Person1" could be B3:H3 or B3:I3 or B3:J3 etc. It's not working for me.

@arthurbr; maybe I am over complicating this and there is an easier way to utilise the table structure to accomplish what I want?
@rakesh seebaruth; the INDEX formula did not work for me but it's possible my setup elsewhere is incorrect for this approach. Maybe the detail above will clarify?

 

[RoryA]

Change it to:

="Sheet1!$B$2:" & ADDRESS(2,COLUMNS(DataTable),1)

 

[sparky2205]

@RoryA that doesn't appear to work.

="Sheet1!$B$2:" & ADDRESS(2,COLUMNS(DataTable),1) as the reference for the Named Range along with =COUNTIF(INDIRECT($A2),C2)=1 as the Data Validation rule prevents the addition of ANY text into the cells in row 2.
The "Value" isn't populated in the "Name Manager" when entering the formula above. Not sure if this is significant.

Also, when I enter the "Data Validation" rule I get the message: "The formula currently evaluates to an error. Do you want to continue?"
Again, not sure if this is significant.

 

[sparky2205]

Does anyone have any further thoughts on this?
I feel it's either impossible to achieve or not too far away.
But after trying numerous different approaches I still can't get it to work.

 

[RoryA]

Looking closer at your setup, INDIRECT is not going to work (Person1 returns text, not a range).

You could create a name called TableRange and assign your table to it. Then use the following in the data validation formula:

=COUNTIF(INDEX(TableRange,ROW(B2)-ROW(TableRange)+1,0),B2)=1

Make sure you uncheck the 'Ignore blank' option.