Видео

Mail to offtooffice@gmail.com ur sheet with requirement

Which version of Excel r u using?If the version is Office365, there already is a n inbuilt function viz. FILTER to achieve this kind of dynamic filtering. But if that's not the case an you r using a version lower than this, you will have to resort to this workaround, be in the same tab or another tab. Again referring within tab witnesses some slight improvement as you move into higher versions of Excel. In case there is still something that u want to be assisted kindly mail to offtooffice@gmail.com

This is available with genuine Excel 2019 version or above version like office 365

Glad you found it useful.

@@offtooffice8742 very useful information sir ji..this type information generally not found easy.

This is probably because u r not using MS365 version, the latest one. As a matter of fact the referencing feature varies or say keep getting better with every newer version of excel. Do let me know what version if excel r u using?

@@offtooffice8742 i am using excel 2016 and it seems it does work only in MS365

Try Control and F3 together or else directly type the defined name. However Control F3 should work. Do let me know.

I will come back to you shortly. If u r okay with sending the file u can send it to offtooffice@gmail.com. or alternatively u can send me the screenshot of the relevant portion. Meanwhile I will have to go through the formula used by me in my sheet.

@@offtooffice8742 thank you for your reply, i'll send the modified file to you...

Glad u liked.

Normal drop down list is the one explained first and of course that was not searchable. The one created later is searchable which filters the relevant data only. Aa regards to its visibility,especially from aesthetic point of view, u can customise the formatting which will change the way the data there is displayed. So say if I have keyed in p and still I want to hide p. U simply go to the cell formatting of the cell where we enter keywords by pressing control 1. set a format as under General;general; general; This way u can hide the display of p or k in that cell. The task u want to achieve can exactpy be obtained by writing an even subroutine which is initiated the moment u select that cell and the monent is is selected u delete thae content of the cell. It will be one liner code Sub Range("d5").Delete End

Glad you found useful.

Thanks.

Glad u liked it.

Your r very welcome. Glad u like it.

Sure,it's already there in the description below. I mean the google drive link for the worksheet.

You're welcome.

subscribed to your channel! your videos are amazing! :)

I won't say it a shortcut. It's just a way of navigation through the menu with alt key. Alt A V V This will lead u directly to the the the data validation option where u r supposed to tell excel the source of drop down list.

@@offtooffice8742 I was thinking of more like assigning a keyboard press like ctrl+enter or shift+enter or functionkey to activate the dropdown?

@@offtooffice8742 Alt A V V is the shortcut key to open the validation window setting. I was talking about the activating drop down though a keypress.

Thank you, this was exactly what I wanted. But know that I have my perfect dropdown I wanted to protect formules on sheet that also has the dropdown by cell properties protect on formules and protect sheet. And know the dropdown doesn't work anymore. Is there a workaround? Is it the Matrix Array the culprit?

Glad u found it useful. Yeah definitely. I will come up with a dedicated videos on this function. However this mmult works exactly like matrix multiplication rules of mathematics.

@@offtooffice8742 Thank you so much Sir.i have problem understanding this function but when you used in combination with transpose or match etc I want to be able to say when i will have a situation at my work - ok in this situation i can used mmult.Once again thank you so much for all your videos

Glad it helped u.

@@offtooffice8742 it's my pleasure sir ji..🤗🤗

Do u mean you want to have the drop down list to cells below ? There is a short video to overcome this problem ruclips.net/video/Gn9pwgpsweM/видео.html

Yeah it is possible. I won't say exactly but it's a kind of workaround. I would let u know tomorrow. preparing altogether a video for it as others too have queried about it.

@@offtooffice8742 Thank you

Here is Your solution. ruclips.net/video/Gn9pwgpsweM/видео.html

@@offtooffice8742 Amazing, thank you so much for such a quick response. This is what I needed.

@@offtooffice8742 But that video is for "Date Functions"...

Glad u found it useful. Be with the channel for such videos.

Which version of excel are u using? May be u r in MS 365 which is natively programmed to handle arrays not requiring control shift enter.

Sure I will and let u know.

nothing

Kindly check. As desired, the sheet has been put in the description box.

Median formula is just for checking if a no lies between 2 other no. Besides there is approximate lookup function also explained which u should also refer to. U can share ur sheet or relevant sample to offtooffice@gmail.com.

Median will actually return the middle most data. So if ur range is say 5-10 and u r trying to check for a no 12. Median (lower limit,upper limit,12) will return 10 which is not equal to input no I.e 12 . This confirms the no doesn't lie and we will say "not available" or whatever. =Ifs(above test, result desired,.....) Anyways u can always share some relevant part of your sheeet.

Kindly Check ur mail for solution.

By the way I could not get u what do u wanna ask?

Nothing I only want to know

Reading and experimenting with the formula. Once u get hang of boolean behaviour of logical tests u will be able to handle Complex scenarios in Excel.and of course google for ur queries. Sources of Information are galore.

@@offtooffice8742 Ok thanks

Glad u liked it.

For month of today. =Month (today()) To compare this with other date obviously the range should have dates and not the text looking like dates. Convert them to date in 1 or 2 step with text to columns. Select such dates. Go to text to columns. Set delimiter to nothing. Select dmy if the figures in the text would have represented date in the same manner. Finish and u r done. Even If date in text is like 2 feb 20 it will be converted. Below is the link of that video. ruclips.net/video/KI1LLMy0Cuk/видео.html

I have a strange problem When i use =month(a1) for instance and a1 is 7/3/2021 it returns 7 which is correct. But when i change the format to date 14-Mar ( day in number-month in letter) i get 7-Jan and when i use custom format MMM i get Jan. Please let me know what i am doing wrong.

I tried doing myself and not experiencing any problem as such.i fail to understand why should it behave Like that. U can mail sheet or part of sheet to offtooffice@gmail.com for further assistance.

Please check the mail for solution.

Glad you found it useful. Subscribe and share for benefits to all.

Sure sir

Glad you found it useful. The more u use excel the more u explore.

Yeah, find and replace is an absolute time saver.