Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding words up in a range | Excel Discussion (Misc queries) | |||
Adding Range Names | Excel Worksheet Functions | |||
bar code help, adding asterix to reference | Excel Discussion (Misc queries) | |||
How do I reference and sort a range of numbers in Excel 97? | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |