• 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.

Search results

  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)
  16. shrivallabha

    help required how to split cell

    This will be possible using VBA macro as this will require inserting rows.
  17. shrivallabha

    Consecutive number with Letter in Front

    @RDEXCEL2020 Your requirement is still unclear to me (maybe many...) If you could provide a clear rule. Single digit --> S01 or S1 Double digit --> S099 or S99 Triple digit --> S0999 or S999 Quadruple digit --> S09999 or S9999 and so on then it will be much easier to understand. The...
  18. shrivallabha

    Consecutive number with Letter in Front

    Depends on who the end user really is ;) Sometimes, the sheets are being made by the end-users themselves. People come up with all sorts of requirements which are "unclear/unsaid" in their initial posts. Just when you think that the thread is solved the pole shifts. e.g. in this case it is...
  19. shrivallabha

    Consecutive number with Letter in Front

    If your requirement is limited to displaying alone then you can continue to use your formula by applying a custom cell formatting as ""S"General" (bold portion without quotes)
  20. shrivallabha

    Count empty numbers and cells

    If TEXTJOIN function is available to you then you can try below formula as well. In cell AC2: =LEN(TRIM(MID(SUBSTITUTE(" "&TRIM(TEXTJOIN("",TRUE,IF($C2:$AA2<>"",1," ")))," ",REPT(" ",99)),99*COLUMNS($A$1:A$1),99))) Copy down and across. In cell AP2: =LEN(TRIM(MID(SUBSTITUTE("...
  21. shrivallabha

    Get Part of Work Sheet Name

    Your aim and code do not align. Your code should be: Range("K2").Value = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") - 1) Syntax you have written Len(ActiveSheet.Name) - InStr(ActiveSheet.Name, "_") is more useful to take remainder part of the string after "_".
  22. shrivallabha

    Help to extract sub groups from original data

    If you are okay with adding a column to your layout then you can use Pivot table. See attached file.
  23. shrivallabha

    How to split a number in different cells, excel macro

    Cross-posted : https://stackoverflow.com/questions/62910974/how-to-split-a-number-in-different-cells-excel-macro In case of cross-posting just make sure that you inform about it. Since you are a new user will recommend you to go through forum rules...
  24. shrivallabha

    100 Seprator formula in excel

    If it is only representation you care about then you can try: =LEFT(TEXT(ROUND(K7,0)*10,"#,##,#"),LEN(TEXT(ROUND(K7,0)*10,"#,##,#0"))-1)
  25. shrivallabha

    I am unable to write formulas in the attached .csv file?

    CSV is flat file format so in principle you need to save the file in native Excel format (.xls, xlsx, xlsm, xlsb etc).