Clone a dropdown list

[jules meredith]

Hi
I have a spreadsheet with football results for multiple seasons - each seasons results are on individual sheets.

To access each season I have a dropdown list which populates a new sheet with the selected season data using an array of vlookup's. This data is then used to populate further sheets which display final league placements, form, "team a" v's "team b", etc. etc.

If I am looking at the league table and I want to change to another season, I have to navigate back to the sheet with the dropdown list and then navigate back again once the correct season is selected. Is it possible to duplicate/clone the original dropdown list and have it on the multiple sheets but linked to the original - so they all display the same selection?

Any help on this would be very much appreciated

Jules

 

[Peter Bartholomew]

I assume you are attempting to use a validation dropdown for this purpose?
If so, a better strategy might be to use a Combo Box from the form controls section on the Developer Ribbon tab. Multiple controls can be linked to the same 'master' cell.

ps. The array of vlookups sounds horrendous. I prefer fewer look-ups but returning larger data arrays with each.

 

[jules meredith]

Thanks for your reply Peter, I do believe the ComboBox with a sprinkling of vba is the only viable route for my query and I am currently trying to get my head around this - I might have to come back here if i get stuck.

I am intrigued with your comment about my array of vlookups! If their is a "healthier" way of populating this data, please do explain this idea further.

 

[Peter Bartholomew]

the ComboBox with a sprinkling of vba

The vba maybe useful but is not essential for Form Controls. The cell link is a property of Format Control. The Linked Cell is often chosen to be the cell hidden under the control. In use, the combo box shows the user selection (2017 from a list {2016;2017;2018}, say) but the linked cell contains the index (2 in this case).

I cannot say, without looking at your data and how it is laid out, whether other forms of access are preferable but one option is to use some generic names scoped as local to a template sheet. Then, when the template is copied, a set of local names is created for each sheet.

A selected block of data can for a given then be brought back to the master sheet using a Named Formula 'selectedResults' that refers to
= IF( ISBLANK( CHOOSE( selected, '2016'!results, '2017'!results, '2018'!results ) ), "", CHOOSE( selected, '2016'!results, '2017'!results, '2018'!results ) )

The array formula
{= selectedResults}
could then access data for the relevant season, hopefully simplifying the ongoing analysis and presentation.

The attached may give some indication of the sort of tricks that can be pulled

 

[jules meredith]

Thank you Peter, you have been a massive help - I was over complicating things with the combobox and vba. Your example was an eye opener and I really appreciate your support and time.

 

[Peter Bartholomew]

Jules, I am pleased some of the ideas are of interest to you.

Looking back, I think I broke the calculation at the wrong point. It would be better to leave the Named Range 'selectedResults' to be
= CHOOSE( selected, '2016'!results, '2017'!results, '2018'!results )
and then to display the blanks as spaces using the worksheet formula
= IF( ISBLANK(selectedResults), "", selectedResults )
In this case, if you use F5 and then type selectedResults, Excel will regonise the range and take you to the currently selected sheet and highlight the range.

I have seen the entire Used Range for the sheets within a complex workbook returned by a single array formula but I prefer to work object by object.