User form to query (up to) 8 worksheets

[Mel Smith]

I have a workbook containing 8 identical sets of data, all laid out with the same persons occupying the same row and the same data occupying the same columns.

My question is, is it possible for a userform to ‘pull’ data from all 8 sheets? What I’m trying to achieve is to search on one name and the userform to then populate with all 8 sets of data in respect of that person.

Thanks for your help.

Mel

 

[My Aswer Is This]

Normally I would suggest using a Combobox to select the value you want to search for.
We load all the names in column A into the Combobox when userform opens and then user selects the name in the Combobox that way they do not enter values into the TextBox that may not be exactly correct.

 

[Mel Smith]

Sorry. If I put in 26, 37, 41 etc. into the search box none of these worked. The only positive searches came from the Alphanumeric entries of existing personnel in cells A4:A28. No entry from cells 29:A63 returned a positive response.

 

[Mel Smith]

Is it worth considering changing the search box to a combo box or referencing a dynamic drop down box?

 

[My Aswer Is This]

So you have a player with a name which is:
26, 37, 41
That's a odd name.

You said:
The only positive searches came from the Alphanumeric entries of existing personnel in cells A4:A28. No entry from cells 29:A63 returned a positive response.

of existing personnel well if the name is not in column A it will return a error.
How would you expect it to work if the value entered in the search box is not found in column A

And the ranges in column A does not matter. the script searches the entire column A
On all 8 sheets

 

[Mel Smith]

I think we are talking at cross purposes. The list of names (which has not changed since we started on this project) are in every sheet in the workbook in cell range A4:A63. The first 25 cells contain alphanumeric names of current players. The next 35 cells contain numbers instead of names (as the names are not yet known) 26 to 60.

In the search box the alphabetic names work, the numeric names do not.

Would changing the current search box to a combobox, referencing sheet 1 A4:A63 improve things?

 

[My Aswer Is This]

Another question I asked that you did not answer was:
If you enter Alpha in the search Box do you have Alpha in column A of all 8 sheets.

 

[My Aswer Is This]

You said:
The next 35 cells contain numbers instead of names (as the names are not yet known) 26 to 60.

So are these numbers 26 to 60 in column A of all 8 sheets.

 

[Mel Smith]

The entries are identical in all sheets. The cells in sheet 1 (named Championship Averages) A4:A63 are populated from another workbook. The cells in the remaining sheets of the workbook take their data from the range in sheet one (named Championship Averages)

To answer you last question, the data within the range is alphanumeric and numeric. If I enter an alphanumeric name of a listed player everything works. If I enter an "listed" number e.g. 31 the error message is displayed.
Mel

 

[Mel Smith]

Yes, these number are in all sheets as per my previous reply.

 

[Mel Smith]

On my test file, i have added a Combobox (ComboBox1) and given the range as A4:A63 which shows all entries within the range as you would expect. Is it possible to replace the original search box for this ComboBox or somehow refer to the comboBox within the code so that there is no mis-typing etc?