Search and see only the searched data

[dirty moose]

Hey everyone, first off happy New Year! Second thanks to everyone that helped me out last time.

I've included the file I'm working on.

What I'm trying to do is add a search function or filter function for any of the columns. I want that search to give me just the results for the that search. Not sure it that makes sense. So I filled in a little sample data. Line 29 is where all my totals are. So let's say I want to sort it out by track, I want to see just the results for AQ. How would I do that? Or in the I column, I want to look up all the 3SL and see just the results that have 3SL in row 29.

I hope I've giving enough information to answer my question, please post if you need more info and what info you would need.

Records.xlsx

Thank you so much everyone!

Matt

[FDibbins]

Not sure if this is what you want, but take a look at using filters.

On the Home tab, click Sort and Filter, and then select Filter.
You will see that each column now has a small drop-down. Click the Track 1, uncheck All and check what you want to see

[Richard Buttrey]

Hi Matt,

Change your SUM() fuctions.

To see the count of the filtered items use

Please Login or Register  to view this content.

To see the sum use

Please Login or Register  to view this content.

[dirty moose]

Hey Rich,

I think that's what I was looking for. I'm going to play around with it later today.

Thanks a lot!

[dirty moose]

Hey Rich,

Just an update and follow up question.

This worked great!

Now I'm wondering if there is a way see just the sub totals when filtered.
So when I filter, I see the filtered stuff but I still see the sub totals of everything.
Is there a way to see just the sub totals of the filtered?

Thanks
Matt

[dirty moose]

Can anyone out there help me out? Or do I need to be more specific?

Thanks a lot guys.

Matt

[FDibbins]

Do you have an updated version of your file?

[dirty moose]

Yes Ford, here you go. Thank you.

[FDibbins]

OK so what would your expected outcome look like?

[dirty moose]

Ford,

I like to feel out certain things and see just the results on the bottom. So say I want to filter out all the sprints in the S/R column. Now on the bottom where all the results are I'd like to see just the results for routes.

Or in the type column I want to see just the bottom line for ALW, how could I do that?

Does that make sense?
Matt

[xladept]

Hi Dirty,

Try this - it works on a double click in the non-numeric columns up to G - it will filter for any value you double click upon in those columns and if you doubleclick anywhere on the sheet it will restore the data to its unfiltered state:Dirty.xlsm

[dirty moose]

I downloaded and tried to open that file. Says it trying to open in protected view and never actually opens.

[dirty moose]

Ok I got it working.

Now when I double click, it works like you say it would. Now how can I see the totals of just the stuff I double click?

[xladept]

I'll have to see whether I can accommodate you tomorrow

[dirty moose]

Thank you very much!

[xladept]

Sorry Dirty, I need another day

[dirty moose]

No worries Take your time.

[xladept]

Hey Dirty,

Where should I put the "Totals"??

[dirty moose]

At the bottom would be good, I guess.

Thank you so much for the help.

[xladept]

Hi Dirty,

I interpreted "the bottom" to mean row 378 for this sample - see how you like it Dirty.xlsm

[xladept]

Hi Dirty,

You may want to change the code to what I have in red here:

Please Login or Register  to view this content.

[dirty moose]

Downloading now! Thank you so much!

Also I have to be honest. I have no idea what that means.The code part.

[dirty moose]

DUDE! That's amazing!

[dirty moose]

So now that you have shown me, can you teach me? How could I add that code from "G" onward?

Also how can I compensate you? That's was exactly what I was looking for!

[xladept]

Hi Dirty,

To change the code - with the spreadsheet up hit Alt + F11 and you'll see the code in front of you - for the fix above just change the:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

i.e. just delete the Contents part.

To go on from Column G will screw up the id feature wherein you can see right in the Totals bar what term was filtered, I need to know what else you want to see in order to make that possible.

To reward me, just click on the little star at the bottom left of any of my posts and award me reputation points

[dirty moose]

Reputation added! Thank you so much.

I'd like the formula to go all the way over the the column that says "odds" . I don't have the sheet open in front of me but I think it's the R column....

[xladept]

Hi Dirty,

Thanks for the rep and the compliment!

Replace the whole code with this - I think it's what you want - I put the filter description for H and above in the G-Slot.

Please Login or Register  to view this content.

[dirty moose]

Yes that worked with one exception, I think, this is a little nit picky but how could I change the format on some cells upon double clicking? Example when I double click column T; I want to see the format changed to currency. Is this possible?

[xladept]

I can Format T as Currency permanently??

[dirty moose]

I'd like to format E as number, 1 decimal place. T,U,V,W as currency $ with 2 decimal places please.

Again thank you so much!

[xladept]

Replace the whole code or just add the red code

Please Login or Register  to view this content.

[dirty moose]

You're amazing! That is all I can say.
I wish I could give you more rep. or better compensate you for your work.

Thank you again.

Matt

[xladept]

You're welcome again - perhaps you can write some tweaks with what I've shown you?