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

  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.