Last 50 Posts on Chandoo.org

Chandoo on 2020-07-03 19:46:12
@Mayur.. Thank you. I didn't quit India. I consider India and NZ to be my two homes. I disagree with the view that a country can be poorer because her people leave. Thanks to technology, all the world is one big place. I still collaborate with my Indian techie friends and create content that whole world can enjoy. :)

Mayur on 2020-07-03 17:18:51
Congrats Chandoo! I also wanted to know why you quit India and moved to NZ. India is certainly poorer without good and intelligent people like you. Unfortunate that our best and brightest tend to live abroad. Best wishes to you and to your family.

SS on 2020-07-03 16:36:01
Hi Slsuser, Unfortunately, I don't know much more about VBA or pivot tables because I never really had to use them before. So, maybe one of these other much more experienced members could answer your question. Sorry I'm not too much more help!

vishy on 2020-07-03 11:51:50
Best wishes for MVP

Pothi Kalimuthu on 2020-07-03 07:36:15
Nice to know NZ is back to normal. Here in India, we have a long road ahead. Just started with another month-long (official and unofficial) lockdown (for July 2020). Mangoe season is almost over here as south-west rains started (at least in the state of TN). ???

Hyoungjin Yoon on 2020-07-03 00:18:48
Seems it's too late but.. most common usage of inner product is to solve such an Example #7 like; =SUMPRODUCT(--(LEFT(sf[Captain],1)=L24),sf[Staff count]) instead of matrix multiplication, although the main focus of this article is on INDEX function. I just wanted to say hello and thank you taking this chance.

Slsuser on 2020-07-02 13:56:15
I would be interested to know if the method similar to this would work with pivot tables. For example let’s say I have a list of customer invoice numbers which could be in the thousands with cities and or ZIP Codes in the amount of money that we have charge them. I realize you could do this with slicers but to put invoice numbers in slicer should be scrolling and scrolling and scrolling So the search while typing would be an excellent alternative for the pivot table if possible

Sharon on 2020-07-02 12:40:51
Love the tone and dialogue! Thanks for your help!

Slsuser on 2020-07-02 00:17:30
Can we make the filter as it types macro work for pivot tables?

Craig on 2020-07-01 15:58:07
This is wonderful. Very simple (after I watched your video). Not something I would have considered possible until I saw your work. I need to play with Charts more. Thank you.

bosco_yip on 2020-07-01 04:48:02
=LOOKUP("zzz",OFFSET(A1,MATCH(K3,A:A,0)-1,,,1000))

Kim on 2020-07-01 00:00:26
Awesome, I have been following your site for a few years now and this is the coolest! As end of year reports are due this is going to work a treat! Big thanks and keep up all the great work.

SS on 2020-06-30 17:54:43
With help from some new friends, I was able to accomplish the correct code necessary to have an Excel file dynamically Auto-Filter with all columns in a data table. I wanted to share the code below, in case someone like myself (no VBA experience) needed to accomplish the same task for the sake of their job as well. 1. Ensure your data table is set as a table with a name (default name is Table1). You should be able to select it in the upper, right corner below "File". 2. Create a Textbox under Insert ActiveX Controls via Developer Tab. 3. Right-click on the textbox and view the code. 4. Enter the code below and change the names of all parts of the code in CAPS, to match your Excel item names. Sub ClearAllFilters() ActiveSheet.ShowAllData End Sub Private Sub TEXTBOX1_Change() With ActiveSheet.ListObjects("TABLE2") ' Enter the name of the "Intelligent Table" in the worksheet IntTabColumn1 = .DataBodyRange.Rows(1).Cells(1, 1).Column ' First column of the Find table IntTabRow1 = .DataBodyRange.Rows(1).Cells(1, 1).Row ' Find the first row of the table IntTabRowCount = .Range.Rows.Count - 1 ' Rows of the table minus the letter IntTabColumnCount = .Range.Columns.Count ' Columns of the table IntLastRow = IntTabRow1 + IntTabRowCount - 1 'Last line IntLastColumn = IntTabColumn1 + IntTabColumnCount - 1 ' Last column End With If TextBox1.Text = "" Then Rows(IntTabRow1 & ":" & IntLastRow).EntireRow.Hidden = False Else new_filter (TextBox1.Text) End If End Sub 5. On the left-top corner in the project "tree", right-click anywhere under your project name or "Modules" and click "Insert" "Module". Enter the code below and again change the names of all parts of the code in CAPS, to match your Excel item names. Public IntTabRow1 As Integer ' Variable 1. Line Public IntTabColumn1 As Integer 'Variable 1. Column Public IntTabRowCount As Integer ' Variable number of rows Public IntTabColumnCount As Integer ' Variable number of columns Public IntLastRow As Integer 'Variable last line Public IntLastColumn As Integer ' Variable Last column Sub new_filter(critere As String) Dim col As Integer, lgn As Integer, lignes(158) As Integer, a As Integer, find As Boolean ' "158" are how many columns there are in the table. a = 0: find = False ' 'IntTabColumn1 = ActiveSheet.ListObjects("Tabneu").DataBodyRange.Rows(1).Cells(1, 1).Column 'IntTabRow1 = ActiveSheet.ListObjects("Tabneu").DataBodyRange.Rows(1).Cells(1, 1).Row ' 'IntTabRowCount = ActiveSheet.ListObjects("Tabneu").Range.Rows.Count - 1 'IntTabColumnCount = ActiveSheet.ListObjects("Tabneu").Range.Columns.Count ' 'IntLastRow = IntTabRow1 + IntTabRowCount 'IntLastColumn = IntTabColumn1 + IntTabColumnCount For lgn = IntTabRow1 To IntLastRow For col = IntTabColumn1 To IntLastColumn If LCase(Cells(lgn, col).Text) Like "*" & LCase(critere) & "*" Then Cells(lgn, col).EntireRow.Hidden = False: Exit For If Not (LCase(Cells(lgn, col)) Like "*" & LCase(critere) & "*") And col = IntLastColumn Then Cells(lgn, col).EntireRow.Hidden = True Next col Next lgn End Sub That should be everything you need to accomplish a keyword search-box to filter out all rows of data that do not include your keyword entered into the textbox! Hopefully this helps many others and saves them a lot of unnecessary, wasted time. Enjoy! :-)

DOUG GALT on 2020-06-29 16:25:25
formula error on this excel sheet

Daniel on 2020-06-29 11:28:55
Why we need the & before refering the cell in the first formula? We are already in the and statement why we need the &? It's not clear for me. Thanks.

Kisorio on 2020-06-28 20:52:35
Kindly assist me with a School Management System in Ms Excel. Thank you!

Gabi on 2020-06-28 17:58:21
Hi Chandoo! The shared drive shows a Error. Where can i download the file? Thanks!

John on 2020-06-28 16:10:40
Hi I am presently using conditional formatting to Highlight Rows and Columns from the Active cell position to set lengths. This is working fine, but there are times when I just want no Highlighting which I can switch off using a No Format and stop if true setting. What I am having a problem with is to just have either the Row or the Column Highlighted. There are times when I just want the Row Highlighted, and times when I just want the column highlighted. I have searched and have tried so many ways of doing this but to no avail. Maybe you have a way. John

Chandoo on 2020-06-27 22:26:22
You can press CTRL+G and enter. The go to box will default to previously active cell.

Silverman166 on 2020-06-27 09:24:47
Great tip! But how do I get back to the place I was originally working from when I found the need to use the named cell, please?

aMIT uTTAMRAO PATIL on 2020-06-27 07:23:20
IFS(CW530,(AG5-((AG5-AI5)/5)*(CW5-30)),CW535,(AI5-((AI5-AK5)/5)*(CW5-35)),CW525.5,(AC5-((AC5-AE5)/1.5)*(CW5-25.5)),CW527,(AE5-((AE5-AG5)/3)*(CW5-27)))

Annaso Shankarrao Patil on 2020-06-27 06:15:43
Dear alll, i am cant return to values except IFS(CU530,(AE5-((AE5-AG5)/5)*(CU5-30)) in below mentiond formula please clarify IFS(CU530,(AE5-((AE5-AG5)/5)*(CU5-30)),CU535,(AG5-((AG5-AI5)/5)*(CU5-35)),CU525.5,(AA5-((AA5-AC5)/1.5)*(CU5-25.5)),CU527,(AC5-((AC5-AE5)/3)*(CU5-27)))

Thomas Crosby on 2020-06-26 22:30:28
I am trying to use the Removing Duplicates from Ranking and not having success. I am using named ranges versus defined rows. Does that make a difference?

Chandoo on 2020-06-26 19:08:27
Hi Larry... Thanks for the comments. Yes, please go ahead :)

Larry Mandelkehr on 2020-06-26 15:44:03
Thank you - this is outstanding. Can I share these example with my class - providing all credit and directing students to your website.

Pratyaksh ganguly on 2020-06-26 11:47:16
very nice game

Robert H. Gascon on 2020-06-26 09:11:19
My formula is the same as that of David, except that I used "" instead of LEN, like this: =LOOKUP(2,1/ (INDEX(B4:E13,MATCH(K3,A4:A13,0),0)""), INDEX(B4:E13,MATCH(K3,A4:A13,0),0))

Sriram on 2020-06-23 02:49:32
Hi Chandoo, The raw data has a column whose values are of a datetime format. Ex: 1/8/20 2:56:22 PM. How to show only date values (mm/dd/yy format) in pivot table without adding new columns in raw data?

Abhishek Shukla on 2020-06-21 05:59:22
Thanks, it worked great! :D

Gaurav P on 2020-06-21 01:28:16
Hello Guys, i downloaded Mutual fund Excel file and trying to refresh all, but its not allow me to do that. My Q is this Excell sheet will work and do i trust on this NAV. pls guide me and if u ve any more option pls share with me