Creating a search across multiple worksheets using checkbox criteria

urz2006

New Member
Joined
Jan 9, 2019
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hello all. First post and an excel novice so sorry in advance if my questions are daft! I have tried to educate myself but starting from a point of zero knowledge I can only get so far, and now I'm lost in a world of terms I don't really understand ! Apologies if I have used incorrect terminology anywhere.

I am creating a spreadsheet of hotel/lodge information so I can see at a glance info pertinent to that property. I have created a number of checkboxes to show easily (in my mind) what each property has/hasn't got. Screen shot below. Not sure why the check boxes aren't showing here, but basically columns C, I, N, R and T have checkboxes in them. I believe I have done the right thing by linking the cell immediately to the right of each to the check box, hence all the TRUE/FALSE returns which I've tested to make sure are working correctly and independently of each other. I would plan to hide these true/false returns columns when this is displayed to other users.

So this worksheet is for a property called "Batonka". I would also have this property name displayed in cell B1. I will have multiple worksheets, all based on the same layout so each cell reference etc on each sheet is identical. I envisage up to 50 worksheets at this stage as there will be one for each property/hotel, so what I would really like to do is interrogate the entire workbook.

I realise this is more of a database function, but I have no knowledge of Access and cannot get it on this work machine either, so that is not an option sorry.

What I would ideally like is a "SEARCH" worksheet, that has all these checkboxes unticked on a blank sheet in effect. I would like to somehow set a query/filter so that I could tick the boxes I want and generate a list of property names which match the criteria I specify. So for example, say I would like to find which properties match all the following criteria:

Contemporary : cell D10 = TRUE
Has a kids club : cell J9 = TRUE
Where helicopter trips are possible : cell O13 = TRUE
is good for Cheetah sightings : cell S13 = TRUE

Cell H11 is a number field - in an ideal world I would like the search form to be able to choose an age and the results to display anything which is "greater than or equal to" my search parameter - but this is not vital at all and can be left out if over-complicating

In my head I could, from this Search worksheet, tick the boxes I want, press Go (!) and it checks each worksheet in turn to see which one fulfils these criteria, then displays a simple list of the property names which match all these criteria as TRUE - names to come from cell B1 in each case, or the name of the worksheet if that was somehow easier.
I would also have a "reset" button which cleared the search form (doesn't need to store the data anywhere) ready for another search.

1) Is this even possible or is it too complex?
2) If it is possible, can anyone give me a clue where to start please. I'm happy to try and play around but I'm completely in the dark in terms of how to start interrogating excel in this way.

Many thanks in advance if anyone fancies a challenge!

ABCDEFGHIJKLMNOPQRSTU
1Property :Website :Activities:Animals:Good for:Not found:
2
3Location :Access point :BikingTRUEBuffaloTRUEFALSE
4National Park?TRUEMin child age (if applicable)11Camel treksTRUEElephantTRUEFALSE
5Concession?FALSELast refurb?Canoe / KayakingFALSELeopardTRUEFALSE
6USP's:Cultural visits / toursTRUELionFALSEFALSE
7(not covered elsewhere)DivingTRUERhinoTRUEFALSE
8Padi option?TRUE
9Styling :BoutiqueFALSEFacilities :Kids ClubFALSEFishingTRUEBirdingFALSETRUE
10ContemporaryFALSESpaFALSEGame drivesTRUE
11ColonialFALSEPoolFALSEGolfTRUEAardvarkTRUEFALSE
12Romantic / honeymoonFALSEStarbed / sleepoutFALSEGymTRUEAardwolfTRUEFALSE
13FamilyFALSEPrivate vehicle optionTRUEHelicopter tripsTRUECheetahTRUETRUE
14Secluded / R & RFALSELow / no Single supp?FALSEHikingTRUEGiraffeTRUEFALSE
15Notes re wheelchairs / accessibility:Horse ridingTRUEHyaenaFALSETRUE
16e.g. Very steep , 1 wchr access room #5Hot Air BallooningTRUEPangolinTRUETRUE
17Total Number of rooms & suites:14MokoroTRUEWild DogTRUETRUE
18Room types:Suites & Family rooms:Motorised boatingTRUEPlains gameTRUETRUE
19(edit room names as applicable and free-format pertinent info i.e interconnecting options to "specific notes" section)Night drivesTRUE
20StandardPlunge poolFALSE1 bed SuitePlunge poolFALSEPhotographyTRUE
21Extra bed poss?FALSEExtra bed poss?FALSEHide?TRUEChimpsTRUETRUE
22Balcony / terraceFALSEBalcony / terraceFALSEQuad bikesTRUEGorillaTRUETRUE
23Living roomFALSELiving roomFALSESnorkellingTRUEMonkeysTRUETRUE
24Specific notesSpecific notesWalking safariTRUE
25WatersportsTRUE
26LuxuryPlunge poolFALSEFamily chaletPlunge poolFALSEYogaTRUE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Batonka
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top