• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recent content by shrivallabha

  1. shrivallabha

    How long does Excel keep "Enable Content" Notification?

    Thanks for clarification! I mistook it as "duration" based prompt. However, as you have rightly pointed out it is more connected to user behavior and trigger lies with user's subsequent actions. If user chooses to ignore the warning that "Macros are not enabled" and perform some operations then...
  2. shrivallabha

    How long does Excel keep "Enable Content" Notification?

    This question arose out of curiosity. Another discussion on StackOverflow led me to this question. Reference to this discussion can be found here: https://stackoverflow.com/questions/65746707/why-would-my-excel-2010-udf-quit-working-with-a-name-error This applies to users who have not changed...
  3. shrivallabha

    To Number Required

    If you are okay with helper columns then simpler approach can be used. Helper#1 Calculate cumulative running total for pick orders =SUMIF($B$3:B3,B3,$C$3:C3) Helper#2 Calculate cumulative running total for scanning template =SUMIF($G$4:G4,G4,$H$4:H4) Helper#3 Based on Helper#2 Qty...
  4. shrivallabha

    Format User ID by Date in a proper format

    The attached file is corrupt. Please reupload a new sample.
  5. shrivallabha

    Week Number is incorrect when I enter 01/01/2021

    If you add 53 weeks to a date in 2021, you are bound to end up in 2022! :) =IF(E4="","",E4-WEEKDAY(E4,2)+1) Use this formula which can give you desired results. Edit: @GraH - Guido beat me to it.
  6. shrivallabha

    Need help - Copy Excel Data and paste to notepad

    Hi Kavish VBA open file method does not have any option for setting encoding so it will (probably) pick the default encoding on the system it is being run. If you want to enforce it then you will have to use different technique. See if below thread helps you with it...
  7. shrivallabha

    Array Formula CTRL+Shift+Enter Not working

    Hello Zenfa Welcome to Chanoo Org forums. Please create new thread for posting your query. You can leave a reference to this thread if required. Also as a new user, it will be good to go through instructions for new users; https://chandoo.org/forum/forums/new-users-please-start-here.14/
  8. shrivallabha

    IF with multiple criteria - if <22, +1

    Probably you can add one more IF like below (untested) =IF($S49<22, INDEX('FY21 Award Pay Rates'!$D$29:$D$50,MATCH($M49&"¬"&IF($S49<22,$S49+1,$S49),'FY21 Award Pay Rates'!$B$29:$B$50&"¬"&'FY21 Award Pay Rates'!$C$29:$C$50,0))*SUM(VLOOKUP($N49,'FY21 Award Pay Rates'!$A:$F,6,0))...
  9. shrivallabha

    Unmerge bunch of 10 digit numbers in excel

    The other route is to key in 'CTRL+J'.
  10. shrivallabha

    Unmerge bunch of 10 digit numbers in excel

    Another formula approach using FILTERXML can be =IFERROR(FILTERXML("<t><s>"&SUBSTITUTE($A2,CHAR(10),"</s><s>")&"</s></t>","//s["&COLUMNS($A$1:A$1)&"]"),"")
  11. shrivallabha

    Extract Word from a Sentence

    Here's a variation based on @Peter Bartholomew's suggestion. =LOOKUP(2^15,SEARCH(" "&TEXT(DATE(2020,1,1)+ROW($A$1:$A$12)*29,"mmmm")&" ",A2,1),TEXT(DATE(2020,1,1)+ROW($A$1:$A$12)*29,"mmmm"))
  12. shrivallabha

    Vba code for Amount to Words.

    If you google then links like below come up: https://support.microsoft.com/en-us/office/convert-numbers-into-words-a0d166fb-e1ea-4090-95c8-69442cd55d98
  13. shrivallabha

    Set the code to get the textbox values in Userform

    Following change works for me: Me.TextBox2.Value = Me.TextBox1.Value * WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("a2:d20"), 2, 0) Me.TextBox3.Value = Me.TextBox1.Value * WorksheetFunction.VLookup(Me.ComboBox1.Value, ws.Range("a2:d20"), 3, 0) Me.TextBox4.Value = Me.TextBox1.Value *...
  14. shrivallabha

    Put a string or row of numbers in order, no duplicates

    If UNIQUE function is available to you then you can use below approach. =IFERROR(SMALL(UNIQUE(("0"&Sheet1!$H2:$AK2)+0,TRUE,FALSE),COLUMNS($A$1:A$1)),"") I have just tested against posted sample. See attached file. Depending on your version you may have to use CSE.
  15. shrivallabha

    Execute Shell command (File Name having a space)

    Add pair of quotes to deal with it like below. StartAdobe = Shell("" & AdobeExecutable & " """ & AdobeFile & """", 0)