Adding to a range in a reference?

  • Thread starter Thread starter LilaDuncan
  • Start date Start date
L

LilaDuncan

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.
 
JE said:
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
Click to expand...

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.
Click to expand...

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
 
Back
Top