• 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

    Hui...

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

    How to find out which paper has been received and which paper has not been received.

    Another option like this: 1] In B3, formula copied across right and down: =IFERROR(IF(COUNTIF(INDIRECT("'"&$A3&"'!C:C"),B$2),"✔","✘"),"") 2] Pls see attachment.
  2. B

    Sumifs based on short date format

    Try 1] Replaced all text value cells in Column C Column D and Column H with actual date by Press "Ctrt+H", Fin what : "." ; Replace with: "/" >> click "Replace all" 2] In J9, formula copied down: =SUMIFS(E:E,B:B,G9,C:C,H9,D:D,"<="&H9)
  3. B

    Tab Name Indirect automate instead of YEAR

    Hi Stephan, 1] As you mentioned in #2, you are using Excel2021 so my formula and attachment is designed for Excel 2021 only. 2] In your Excel2021, you need saved my attachment in xlsm type Excel Macro-Enabled Worksheet, otherwise show no data. 3] Yes, you can use Index+Match instead of Xlookup...
  4. B

    Tab Name Indirect automate instead of YEAR

    Or using your original file without changing the sheet name. Try this formula method as suit with your Excel 2021, by using of XLOOKUP function, the old Excel versions can used Index+Match instead. 1] All header description in result "sheet TOTAL" must be as same as the source data sheet (I...
  5. B

    Tab Name Indirect automate instead of YEAR

    To solve your problem I only changed your 3rd sheet name from "EV2041" into "2041", the other "No Change"! Result please see:
  6. B

    (U - - - - t ) if multiple conditions then return value

    Try, [C2] =IFERROR(IF((DATEDIF(B1,B2,"M")>=1)*(DATEDIF(B1,B2,"M")<=11),1,""),"") [D2] =IF(DATEDIF(MIN(B2,B3),MAX(B2,B3),"M")>=11,1,"") All copied down.
  7. B

    COUNTIF for MAX Array formula alternative method

    May be try this short formula, The result as same as your example output. In G3, formula down: =IF(C3<>C2,E3,"")
  8. B

    when i enter 1-9 then excel say 001-009

    Try this formula way. 1] All source data must be in Text format. (To avoid data auto change to Date format as per AlanSidman mentioned in #4) Then 2] In B1, formula copied down: 2.1] By split number+Text format: (Text format as per GraH - Guido mentioned in #5)...
  9. B

    Extractin numbers within a cell that have been entered per line in cell.

    What is your Excel version using now? Regards
  10. B

    Extract only text

    Open a new thread for your new question, This thread is closed.
  11. B

    Fetching values from one sheet to another sheet

    Try, 1] All Criteria Table header D3:N3, must as same as the Source Table header "Dynamic "sheet A1:Y1 2] I revised "FNO" sheet F4 &L4, from "VOL" to "Volume" for demonstration purpose. 3] Then, In D5 copied across right to H5 and all down...
  12. B

    CAGR

    I removed the OP file linked address, please see & open my below attachment.
  13. B

    Extract only text

    @Peter Bartholomew Agree, and we all learn in each other.
  14. B

    Extract only text

    Hi Peter Bartholomew, You are correct, to extract only number the Text format code is "0;;0;" So, here is my formula as in: 1] Extract only number: =CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1),1),1),"0;;0;")) 2] Extract only text: =CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1),1),1),";;")) Hi, Deepak, Thank...
  15. B

    Cost Split based on Allocation and FTE

    In order to avoid misunderstanding, please open a new post for your new question. Thank you.
  16. B

    How to get the number of hour between 2 date and time

    As per the forum rule: Please forward us a sample file together with your desired result. And, please see the forum rule: Site Rules - New Users - Please Read | Chandoo.org Excel Forums - Become Awesome in Excel Regards
  17. B

    Cost Split based on Allocation and FTE

    Try, to revise as per highlighted, In "Answer" N9, formula copied across right to AC9, and all copied down: =INDEX($F9:$I9,INT((COLUMN(A$1)-1)/4)+1)*INDEX($J9:$M9,MOD(COLUMN(A$1)-1,4)+1)/SUM($F9:$I9) or, this In N9, copied down...
  18. B

    Cost Split based on Allocation and FTE

    Please give us your expected results.
  19. B

    Cost Split based on Allocation and FTE

    Try. In "Answer" N9, formula copied across right to AC9, and all copied down: =INDEX($F9:$I9,INT((COLUMN(A$1)-1)/4)+1)*INDEX($J9:$M9,MOD(COLUMN(A$1)-1,4)+1)
  20. B

    Vlookup help for stock data with uneven date ranges

    Or try this single VLOOKUP function solution. In B4, formula copied across right and down: =IFERROR(VLOOKUP($A4,INDIRECT("Sheet1!"&CHAR(63+COLUMN(A$1)*2)&":$T"),2,0),"")
  21. B

    Create Random Team based on Skill Level & Presence

    Try this formula way, In N5 enter formula: =IFERROR(INDEX(FILTER($B$5:$B$27,($F$5:$F$27=N$4)*($G$5:$G$27="X")),SEQUENCE($B$2,1)),"") Then copied formula across right to R5.
  22. B

    Extract only text

    B1: =SUBSTITUTE(CONCAT(TEXT(MID(A1,ROW($1:$99),1),";;")),",",) or, =SUBSTITUTE(CONCAT(TEXT(MID(A1,SEQUENCE(LEN(A1),1),1),";;")),",",) Array entry depends on your version of Excel. Regards
  23. B

    Sumifs based on Date

    Or, Try this copied down formula, In L2 formula copied down: =SUMIFS(Sales_Data3[TotalPrice],Sales_Data3[OrderDate],">="&0+("1/"&SUBSTITUTE(MID(J2,17,20),":",)),Sales_Data3[OrderDate],"<="&EOMONTH(SUBSTITUTE(MID(J2,17,20),":",),0))
  24. B

    Index and match for a single output from data across multiple pages

    Welcome to the board. Please enclosed a sample file as above mentioned to us, and please tell us, what your criteria is? and what the expected result is? Regards
  25. B

    Inconsistent chartview

    You are welcome. Blessings
Back
Top