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

    Copy all visible cells excluding blank and zeros

    Since you've already got a filter applied in a way (you're checking for visible cell), then I'd just add another criteria to the filter to hide the cells with 0 and blank. Then it's a simpler copy & paste.
  2. Luke M

    macros for vlookup in dynamic range with two worksheets

    Hi trividha, You'll get more responses if you start a new thread rather than tacking onto an older one (especially one from 2015).
  3. Luke M

    How to create a list from a list?

    It acts as a counter. The COLUMNS function returns a number indicating how many columns are in the given range. So, at first, this evaluates to 1. When you copy one cell to the right, the range becomes $A1:B1, which then evaluates to 2, then 3, etc. This is so that the SMALL function is being...
  4. Luke M

    How to create a list from a list?

    Put this array formula in AC6 =IFERROR(INDEX(4:4,SMALL(IF(COUNTIF($C$7:$Q$7,$C$4:$AA$4)=0,COLUMN($C$4:$AA$4)),COLUMNS($A$1:A$1))),"") NOTE: Confirm as an array using Ctrl+Shift+Enter, not just Enter. Copy cell to the right as desired.
  5. Luke M


    A quick search of our forums: https://chandoo.org/forum/threads/remove-password.23208/
  6. Luke M

    Need help to export used ranges of sheets to pdf

    @zrs As Alan has discovered, please remember in the future to post links to cross posts, as it saves everyone some time.
  7. Luke M

    Using dynamic reference with structured references in formula

    If 'Alfie' was in B1, you can INDEX the column you need via something like =INDEX(emp_table, , MATCH($B$1, emp_table[#Headers], 0)) Plugging that into your original formula =sumifs(INDEX(emp_table, , MATCH($B$1, emp_table[#Headers], 0)),emp_table[[Weeknum]:[Weeknum]],$A2)
  8. Luke M

    Hello, How I can extract unique data from table based on a criteria from another sheet and put the result on the same sheet where the criteria

    I'd suggest running an Adv Filter. It's designed so that you have a data table, criteria range, and output range. You can also sepcify unique records only. It's easy enough to do manually, but if you want to use VB: https://www.contextures.com/exceladvancedfiltervba.html
  9. Luke M

    Test thread

    Rollover chart with two series
  10. Luke M

    Enter cell value if between start and end date

    Formula in D3: =IF(MEDIAN(D$8,$A3,$B3)=D$8,$C3,"No Estimate")
  11. Luke M

    Drill down by click or double click on a specific column of the chart

    As the only way to do this would be via VBA, I'll move the thread.
  12. Luke M

    Indirect formula help

    Your setup is confusing. It looks like you're trying to use the labels in col A to figure out which sheet to grab, but the sheets have the non-descriptivate labels of "Data entry (7)" rather than "Data entry QBQ". IMO, I'd change the workbook design. First, get all the raw data on a single...
  13. Luke M

    Hi @kabanda . You'll be more likely to find assistance by posting a question in the regular...

    Hi @kabanda . You'll be more likely to find assistance by posting a question in the regular forum, rather than here, which is just my personal profile page. https://chandoo.org/forum/forums/ask-an-excel-question/post-thread
  14. Luke M

    Autopopulate column after dropdown?

    In the future, please remember to post links to your cross-posts. https://www.mrexcel.com/forum/excel-questions/1093994-autopopulate-column-after-dropdown.html
  15. Luke M

    Status Color

    Hi @SharadSalvi Welcome to our forum. However, you'll get more help if you start your own thread, rather than tacking on to someone else's previously solved thread.
  16. Luke M


    Welcome to the forum! Your attached file is hard to understand as it only has one sheet with info. Is that in the desired output, or how the input looks? It currently looks like the layout of a PivotTable. Please clarify what you have vs. what you want.
  17. Luke M

    Need help to avoid popup msgbox while sending an outlook email thru excel

    Note that that message is specifically there to avoid people using malicious code to send email w/o users knowing. Bypassing it could be considered annoying to some users. That said, you could get around it by either a) Just having the email be Show, rather than Send, and let user manually...
  18. Luke M

    Query on Advanced filter

    Missing commas between the arguments of AdvancedFilter. Also, line breaks need a " _" after them to show the code continues. Finally, when listing out argument names, you use ":=", not just "=". Cleaned code: Sub FilterDataraja() Sheets("Sheet1").Range("A1:L15").AdvancedFilter _...
  19. Luke M

    I want to make custom looking chart through Pivot & Slicer

    As Khalid alluded to, be aware of time differences. For my time zone, you posted your question after business hours on a Friday, and I don't check back in until Monday. Here's a start to your file. Chart created as pictured, and slicers made.
  20. Luke M

    Filter the specific column on basis of criteria and populate the few column in a new excel workbook

    Duplicate thread here: https://chandoo.org/forum/threads/filter-the-specific-column-on-basis-of-criteria-and-populate-columns-in-new-excel-template.40933/ This thread is now closed
  21. Luke M

    Bilinear interpolation EXCEL

    @thavil Please note our forum rules (and most forums have a similar rule) stating that you should indicate when you have cross-posted a question. Cross-posting is certainly encouraged, we just want to stay informed so if it's been solved elsewhere we don't waste time trying to "resolve" the...
  22. Luke M

    Add array to loop

    Not a problem, just list out the phrase you want Call ChgTxtColor(Range("A25:A100"), "a string of words", 3)
  23. Luke M

    Add array to loop

    Let's make one sub that takes arguments, and then we can call it multiple times. Public Sub MainCall() Application.ScreenUpdating = False 'What are all the things to find? Call ChgTxtColor(Range("A25:A100"), "late", 3) Call ChgTxtColor(Range("A25:A100"), "start", 3) Call...
  24. Luke M


    Can you do a quick test using different browser(s)? If still no go, it'll be helpful to whoever has to make the fix to note Browser (kind and version) Operating System
  25. Luke M


    I believe you can use the magnifying glass symbol in top right of banner now. Clicking on that opened a search field for me.