Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
LilaDuncan
 
Posts: n/a
Default 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

  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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 .com,
"LilaDuncan" 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?

  #3   Report Post  
LilaDuncan
 
Posts: n/a
Default

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, DataValidationSettingsAllow:ListSource:=Choice s,=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 .com,
"LilaDuncan" 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?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding words up in a range Chris01623 Excel Discussion (Misc queries) 3 February 3rd 05 06:54 PM
Adding Range Names John Excel Worksheet Functions 1 January 18th 05 12:56 PM
bar code help, adding asterix to reference R D S Excel Discussion (Misc queries) 1 January 17th 05 07:05 PM
How do I reference and sort a range of numbers in Excel 97? Old Northern Excel Worksheet Functions 3 December 14th 04 04:15 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"