Displaying the content of a dynamic named range

[caladanbrood]

Hi all. I'm a first-time poster, long-time browser of this awesome site, it has definitely helped me become the first point of call for all excel queries in my office (not sure if this is a good or bad thing).

I have come across a bit of a sticky situation which I can't find my way around, and I would very much appreciate any help you can offer.

I am creating a document which will be used to plan events (it isn't actually for events, but this is the best analogy I can think of, as the data being used is confidential). Data entered on the front sheet will determine what sub-events are available, using dynamic named ranges for data validation.

For example, you select the region, this gives you a drop-down with departments, and this in turn gives you a drop-down of possible events.

I have one tab which contains a list of every department, and the sub-events relevant to those departments within each event. This is where the named range is generated from - the range generates correctly, and although I'm sure the syntax is a bit clumsy, I'm happy with it. The issue I am facing is that I want the sub-events to display in cells automatically, not just to be available in a drop-down list.

I have tried just to write "=subevents" (the name of the range) in a cell and drag this down, but I have discovered a rather odd phenomenon - the named range will only start to display in the same row number as it is stored in my data sheet. Eg. The sub-events available for Department 3 in Event 7 are in cells C14:C20, and when I try and display these on a different sheet, they only show up in rows 14:20.

Do you know of anyway to display them wherever I need? For example, I need the list to start displaying at row 17. Note that the size of the range changes by department/event, it could have anything between 1 and 30 items.



I hope that is sufficiently clear, please let me know if I haven't explained myself very well!!

 

[NARAYANK991]

Hi ,

Can you post the formula which you have used to create this named range ? i.e. what is the formula you have entered in the RefersTo box ?

Secondly , if the named range , subevents in your case , is used as a drop-down list , it is an array , and as such cannot be entered and dragged down ; you should be selecting a large enough single-column range and array entering into this range.

Narayan

 

[caladanbrood]

Hi Narayank991, the formula used to define the named range is as follows:

=OFFSET(Events!$C$10,VLOOKUP('Event Plan'!$C$7,Events!$A$3:$B$7,2,FALSE),HLOOKUP('Event Plan'!$C$6,Events!$C$1:$AI$2,2,FALSE),INDEX(Events!$A$1:$AI$7,MATCH('Event Plan'!$C$7,Events!$A$1:$A$7,0),MATCH('Event Plan'!$C$6,Events!$C$1:$AI$1,0)+2),1)

There are a number of helper rows involved, in A1:AI7, referencing how many sub-events are in each event for each department.

I'm sure the formula could be significantly more elegant, but it does work

 

[caladanbrood]

I have attached an edited copy of the "Events" sheet so you can see what I am babbling on about. As you can see, I have the subevents appearing as a drop-down box without any problem, it's displaying them in any other way I can't get my head around....

 

[NARAYANK991]

Hi ,

I do not know why your named range should behave the way you mention ; all said and done , the OFFSET function has numbers for all parameters other than the first one. For instance , suppose you array enter the formula :

=subevents

in a cell , say A33 , place the cursor in cell A33 and click on the Evaluate Formula button , you should be able to step through the entire process and at some stage , you should see the OFFSET function as follows :

=OFFSET(Events!$C$10,4,7,7,1)

This formula cannot depend on the cell in which it is entered ; whether you enter it in A33 or in XL1 , the result should be the same.

Narayan

 

[caladanbrood]

That is what I thought, which is why I don't understand it.

Having selected Department 6 and Event 2, for example, I have entered =subevents into cell D12 on "Event Plan", it returns a #VALUE! error until you drag it down to cells D42 : D52 (which are the rows (but not the column, bizarrely) that the named range refers to on the "Events" sheet).

Again, I've attached the sheet to show you what I mean.

 

[NARAYANK991]

Hi ,

I am not able to understand what your problem is ; I selected a range of cells , say J3 through J12 on the Event Plan sheet tab , and array entered the formula ( using CTRL SHIFT ENTER ) :

=subevents

Now , as I change the drop-downs , the list of subevents in J3 through J12 changes correctly.

I am not able to see any Event 7 , since the number of events is only 5.

Narayan

 

[NARAYANK991]

Hi ,

Because the OFFSET function uses a height parameter ( the 4th parameter ) , which is more than 1 , you need to array enter it when you enter it in a worksheet range ; you need to select a multi-cell range and then array enter it , using CTRL SHIFT ENTER ; when you enter it in the RefersTo box , Excel implicitly array enters it.

Narayan

 

[caladanbrood]

Ah, maybe the error I am making is not using CTRL + SHIFT + ENTER!

When I use this method, the subevents show where I want them, thank you very much Narayan.

One issue that this does then bring up is a load of ugly #N/A errors, and for some reason if I use {=IFERROR(subevents,"")} it still shows the error. Is conditional formatting my only option or is there a nice way to hide these error cells?

 

[NARAYANK991]

Hi ,

The #N/A are unavoidable , since they are appearing because the range of cells over which you have array entered the formula is more than the number of valid entries available in the named range.

Using IFERROR will not stop them from appearing ; your only way is to use conditional formatting.

Of course , what you can do is array enter ( using CTRL SHIFT ENTER ) the formula :

=subevents

in just a single cell. When ever you wish to see the list of subevents , place the cursor in that cell , and press F2 and F9 ; you should see an array of events such as :

={"Sub 1";"Sub 2";"Sub 3"}

After you have seen this list press the ESC key to exit from Edit mode , retaining the formula in the cell.

Narayan

 

[caladanbrood]

Events with only one sub-event are now repeating constantly when selected, but I suppose this is just another result of using an array formula?

Unfortunately the document will be used regularly by colleagues who are amazed by working out averages in excel, so anything more complicated than point and click is not going to help them! I can work out the formatting from this point to hide the duplicates and errors, it needs to tie in with other areas to show some dynamic boxes depending on content etc but nothing too complicated.

Thanks for the help!