Search

Hi all,

Bit of a novice on Excel so asking for any help to my problem.


I have a spreadsheet i have created listing all the UK beers i have tasted and i want to create a front page that i can have a search box for "Brewery" and also "Beer" so i can search with both options.

The sheet is kept in dropbox so if i am out and about i can look at the sheet to see quickly if i have had the beer before or not.


Can anyone help with me creating a search page.


Thanks in advance


Mike

Can you give a mock-up of how you want the Search page to look? If you select a brewery would you want to display all beers from that brewery, and if you select a beer would you want to list the brewery as well?

It's a bit awkward the way you have laid out your data (but not insurmountable), so presumably you would like to enter the first letter of the brewery (eg B) and then be presented with a list of breweries that start with B. This would not work with the beers, however.

Hope this helps.

Pete

Pete,

Firstly thanks for your reply.

I think all i want is a box that can have a brewery name entered into it then it brings all the beers i have had from that brewery up in the search, So if i search for "Cross Bay" (Brewery) it will bring up the 3 or 4 beers i have had.


Not really bothered what the first search page looks like so you could maybe show off your talents and suprise me.


Thanks again!


mike

Would prefer entering the brewery name not just "B" as it will bring too many results up!


Mike

Originally Posted by Jack81007

... so you could maybe show off your talents and suprise me.

Hi Mike,

Can I also re-arrange your data into one sheet? It will make it so much easier to search on the beers. You could still have your individual sheets for each letter if you wish, though with the ability to search this might not be so important to you - any new beers would be added to the bottom of the composite list, but would still be listed in the appropriate place on the search sheet.

If you were to enter "Bitter" for the beer, would you like to list all bitters (with their brewery)?

Pete

Just replied and not sure if you got it as it has not posted.

Anyway

I want to keep the alphabetical sheets if possible (Unless you can come up with a better option).

Searching the beers is not usually a problem as each brewery doesn't do more than 10ish anyway,so the results of the search need to contain Brewery name (as searched) and the beers listed i have had from the said brewery.

I also need the capability on the sheet to add new brewery's in and beers.


Give it a go and change as you please as i have the beers backed up here.

Really appreciate you help your a star.


mike

Okay, I'm working on this off-and-on, together with changing some lights in the kitchen (which means decorating behind where the old lights used to be). It's taking a bit longer to set everything up than I thought, but I'm plodding on.

It strikes me that you just want to search by brewery, and then pull up all the beers for the selected brewery.

Yes, I'll build in a facility for you to add new beers/breweries - I'm setting up dynamic named ranges as I write this.

Instead of 24 alphabetical sheets, I thought of giving you one on which you can select the letter and the data will automatically get displayed.

Time to see if the paint is dry ...

Pete

Thanks Pete

Hi Mike,

I've attached the file I've been working on, which will give you the search facilities you asked for. You will notice that I have removed all the individual sheets and combined all the data into one main sheet, Data, which lists all the breweries and the beers. There is also a column for comments, as there was one beer where you had a comment. You could also have other columns in this sheet, like where you tasted the beer, the date etc. I have also put a formula in column A which assists the searching and which has been copied down to row 2000 - I've hidden this column as you don't need to access it. If you come across any new beers then just jump down to the bottom of the list (End followed by Down-arrow) and add the brewery and the name of the beer.

There is a sheet called Ref_data where I have set up various named ranges for the breweries - if you come across a new brewery just add it to the bottom of the list under the appropriate initial letter.

In the Search sheet there are two cells coloured yellow where you can select the brewery you are interested in. If B6 is empty then you can put the initial letter in B8 (or select it from the drop-down) and all the breweries and beers will be listed for you - this does away with the need for the individual sheets that you had before. However, if you put an initial letter in B6, then in B8 you will have a drop-down that lists all the breweries which begin with that letter (drawn from the Ref_data sheet), and you can just select an individual one from the drop-down if you wish - in this case, just the beers will be shown.

Happy drinking !!

Hope this helps.

Pete

Thankyou it's great! Way better than my effort.

One problem i have found is an error when you enter a letter in B6 it does not bring up the drop down breweries in B8? Might be me not sure,It also says" The value you entered is not valid.A user has restricted values that can be entered into this cell"



Mike

Sorry about that - the attached file rectifies it.

I changed all the named ranges to dynamic last night (so they automatically adjust to the amount of data), but I didn't test it thoroughly. I've now made them static, but with room for up to 5 extras at the end (shown by the pale yellow areas in the Ref_data sheet). Everything works as described above, now.

Hope this helps.

Pete

Pete,

Everything is fine now.

Would i be right in thinking that if i add my new brewery into te Ref Data that i will then see it appear in the Data sheet and then i can add the beer fro that brewery into place?


I am raelly gonna push my luck now and ask if it's possible on the from Search sheet to add in 2 things,Total Brewery's and Total Beers and get them to add up automatically every time i input the data.


Thanks



Mike

The answer to your first question is no, so you have to add the brewery name to the Ref_data sheet for a new brewery, and both the brewery name and the name of the beer to the Data sheet for either a new brewery or beer.

Do you mean Total for those displayed (i.e. all the breweries which begin with A if that is what is selected in B8), or overall totals?

Pete

P.S. I'm going in the garden now, so I'll get back to you later on, after you have clarified the point about totals.

Hi Mike,

as I got rained off I carried on and have given you both the overall totals and the displayed totals for breweries and beers in the attached file. I've also made a few background corrections, so this is the file to use.

Pete

Pete,

It's fantastic just what i was looking for.You deserve a pat on the back for this and if you were local i would stand you a couple of pints.

Would i be right in thinking that if i add my new brewery into te Ref Data that i will then see it appear in the Data sheet and then i can add the beer fro that brewery into place?
Cheers

Mike

Only a couple ??

Next time you are out sampling and using this file, use the Search sheet to see if you have already entered the beer you are drinking into your database. If not, and it is therefore a new beer, then enter both the brewery and the beer at the bottom of the Data sheet. IF it is a new brewery as well, then enter the name of the brewery at the bottom of the appropriate alphabetical list in the Ref_data sheet - it will then appear in the drop-down list in B8 when you next use that.

Just a couple of general points to wrap this up: please mark the thread as Solved if you consider it to be so (the FAQ gives details if you are unsure how). Also, you can pass on thanks more directly to a poster that has helped you by clicking on the "star" icon in the bottom left corner of any post that you have found to be helpful - this also adds to the reputation of the poster (the green bars on the profile).

Carry on sampling !!

Pete