Adding to a range in a reference?

I'm modifying an existing workbook. One of my drop down lists has its
text options provided by means of a 'Defined Name': Choices.
The Name 'Choices' refers to the text items in a group of adjacent
cells, in a row, on another worksheet called 'Info'. The reference is a
range: ='Info'!$D$7:$H$7

I want to add an extra item of text to increase the number of options
in the drop down list. So as not to disturb the location of cells
defined by other references, I tried to append the new cell downwards
from the group in the row.
I tried this: ='Info'!$D$7:$H$7,$D$8

It didn't work so I went for the range extending
option:='Info'!$D$7:$I$7
This worked OK but it's using the 'I' column which was a spacer between
another block of data. So, if I want to add more options later I'll
have to go elsewhere to avoid messing up other absolute references.

Please can anyone explain the syntax used for adding a remote cell
location to the original range:='Info'!$D$7:$H$7?
TIA
Lila Duncan

You can use the union operator "," to add to a named range, but it
should be fully qualified:

='Info'!$D$7:$H$7,'Info'!$D$8

That won't help you, though, since range references used in validation
dropdowns must refer to a single row or column.




In article <[email protected]>,
"LilaDuncan" <[email protected]> wrote:

> I'm modifying an existing workbook. One of my drop down lists has its
> text options provided by means of a 'Defined Name': Choices.
> The Name 'Choices' refers to the text items in a group of adjacent
> cells, in a row, on another worksheet called 'Info'. The reference is a
> range: ='Info'!$D$7:$H$7
>
> I want to add an extra item of text to increase the number of options
> in the drop down list. So as not to disturb the location of cells
> defined by other references, I tried to append the new cell downwards
> from the group in the row.
> I tried this: ='Info'!$D$7:$H$7,$D$8
>
> It didn't work so I went for the range extending
> option:='Info'!$D$7:$I$7
> This worked OK but it's using the 'I' column which was a spacer between
> another block of data. So, if I want to add more options later I'll
> have to go elsewhere to avoid messing up other absolute references.
>
> Please can anyone explain the syntax used for adding a remote cell
> location to the original range:='Info'!$D$7:$H$7?

JE McGimpsey wrote:
> You can use the union operator "," to add to a named range, but it
> should be fully qualified:
>
> ='Info'!$D$7:$H$7,'Info'!$D$8

Thanks, I'll remember that.

> That won't help you, though, since range references used in
> validation dropdowns must refer to a single row or column.

I was *almost* on the right track then. :-)

So, Data>Validation>Settings>Allow:List>Source:=Choices,=Choices2
where 'Choices2' provides the information from the additional remote
cell, wouldn't work either?

Perhaps I should learn to add rows and columns where needed in
workbooks with lots of pre-existing absolute references (it may be
easier than I currently envisage).

Thanks very much for the reply. I do have some manuals and other
documentation but I'm at the early stage where I still don't know the
appropriate keywords to do an effective index search.

Regards and best wishes,

Lila Duncan



> In article <[email protected]>,
> "LilaDuncan" <[email protected]> wrote:
>
> > I'm modifying an existing workbook. One of my drop down lists has
its
> > text options provided by means of a 'Defined Name': Choices.
> > The Name 'Choices' refers to the text items in a group of adjacent
> > cells, in a row, on another worksheet called 'Info'. The reference
is a
> > range: ='Info'!$D$7:$H$7
> >
> > I want to add an extra item of text to increase the number of
options
> > in the drop down list. So as not to disturb the location of cells
> > defined by other references, I tried to append the new cell
downwards
> > from the group in the row.
> > I tried this: ='Info'!$D$7:$H$7,$D$8
> >
> > It didn't work so I went for the range extending
> > option:='Info'!$D$7:$I$7
> > This worked OK but it's using the 'I' column which was a spacer
between
> > another block of data. So, if I want to add more options later I'll
> > have to go elsewhere to avoid messing up other absolute references.
> >
> > Please can anyone explain the syntax used for adding a remote cell
> > location to the original range:='Info'!$D$7:$H$7?