Named ranges and Table

[tiredofit]

I have a named ranged on Sheet1 called MyNamedRange which refers to cells A1 to A3.

If I add a value to cell A4, the named range does NOT get updated - fine.

However, if I convert the cells A1 to A3 to a Table (by clicking Ctrl+T), now when I add a value to cell A4, MyNamedRange now refers to the range of cells A1 to A4.

Isn't that dangerous?

 

[Fluff]

That's exactly what I would expect to happen. Therefore (IMO) it's not dangerous.

 

[tiredofit]

That's exactly what I would expect to happen. Therefore (IMO) it's not dangerous.

I wasn't aware this was possible.

I prefer to consciously change named ranges via code or the Name Manager, rather than allow users to do it by adding records to a range converted to a Table.

 

[Fluff]

One of the main reasons I've converted data (especially lists) into a table is so that the named ranges automatically expand to encompass new data.

 

[tiredofit]

One of the main reasons I've converted data (especially lists) into a table is so that the named ranges automatically expand to encompass new data.

I agree it's handy.

It's just that I was unaware of it.

The reason for this thread originally was my named range, MyNamedRange was set to Sheet1 scope.

Then in Sheet2, I tried to add a Data Validation into a cell by choosing List then refer to MyNamedRange, like:

Sheet1!MyNamedRange

but got an error message.

Is that because the scope of MyNamedRange was set to Sheet1 and not Workbook?

 

[Fluff]

I think that to be used in DV it needs to be workbook scope.

 

[tiredofit]

I think that to be used in DV it needs to be workbook scope.

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.